LO Base SQL Insert in Macro

English
, , , ,
#1

I have a Linux Mint OS on an AMD system with a MySql db direct connected to LO Base. I am working on a business Point of Service app using desktop software. Specifically the Basic Macro / SQL query I am working on will be to add tasks to a customer record. The tasks are made up of the labor and the parts. I am trying to run a workable query in a macro as LO Base doesn’t allow “Insert Into” processes. I found a macro code example for OO, but it doesn’t work for my query as I get an error at the first or second ". This query does also work within Base Tools/SQL. Below is the snippet I adopted and am working with:

REM  *****  BASIC  *****
Sub Insert2TblInvoice
REM INSERT RECORDS INTO DATABASE TABLE
Dim Context
Dim DB
Dim Conn
Dim Stmt
Dim Result
Dim strSQL As String
Context=CreateUnoService("com.sun.star.sdb.DatabaseContext") '< get  the database context
DB=Context.getByName("autopos.ActivInv") '<- change database name
Conn=DB.getConnection("","") '<- username & password pair - HSQL default blank

Stmt=Conn.createStatement()
strSQL="INSERT INTO "autopos"."TblInvoices"
    (InvoiceNo
    , `Group`
    , TaskDscrp
    , LabrTime
    , TaskRetail
    , StockNo
    , PartNo
    , PartDscrp
    , PartsQty
    , Retail
    , TParts
    , GTotal)
(Select
	InvoiceNo
    , t1.`Group`
    , t1.TaskDscrp
    , t1.LabrTime
    , (t1.LabrTime * 60.00) as TaskRetail
    , t1.StockNo
    , t1.PartNo
    , t1.PartDscrp
    , t1.PartsQty
    , t1.Retail
    , (t1.PartsQty * t1.Retail) as TParts
    , (t1.PartsQty * t1.Retail) + (t1.LabrTime * 60.00) as GTotal
    From autopos. ActiveInv t1
    )"										 '<- change tablename, column name/s and data to be inserted


Stmt.executeUpdate(strSQL) '<- update the inserted data

Conn.close() '<- close the db connection
End Sub

The SQL code above is the working SQL. I have removed my botched versions where I tried “” on the table and vars. None of which worked. I have read the LO 6 Guide for Macro as well as the “AndrewMacro” document, which none address SQL in a Macro. I have exhausted my resources and this is the first time I have asked for help on anything computer related since 1989. Assistance would be appreciated.

#2

Hello,

Let me first explain quotes in strings.

When using quotes in a string it needs to be double quoted. For example this simple SQL statement:

Select "myName" from "myTable"

works from query. However in a macro and defining a string, the string itself is surrounded by quotes:

myString = "This is a string"

So simply inserting the SQL into the quoted area:

myString = "Select "myName" from "myTable""

produces an error. This is because the string actually ended with the second quote and now the interpreter is trying to make sense of myName and it can’t. This is proper:

myString = "Select ""myName"" from ""myTable"""

Another example of this can be seen in my answer on this post → Operation in a sql in a macro

The reason you would need to clarify the table and field names is because they use mixed case. Depending upon the DB used it may recognize all upper or all lower without clarification - no quoting needed.

Since you are using MySQL, you cannot use quotes as this will error, and since macros are in a pass through state (no intrepreter), you can use back ticks to surround mixed case field and table names. So the above example would be:

myString = "Select `myName` from `myTable`"

Edit:

Thought I recalled this correctly, you can create without using anything:

oSql.executeupdate("UPDATE Members SET PrintSel = False")

Just tested with above UPDATE statement in a macro and had no problem. With:

oSql.executeupdate("UPDATE members SET PrintSel = False")

it did produce an error as the table name was incorrect.

Edit 2019-12-11:

I am very sorry. Although your original problem with quotes was a problem and not needing back ticks is also true, I did not actually do any live testing. This was my fault and caused me to overlook the fact you are continuing a string on new lines. In order to do that you need to let the interpreter know with the ampersand to add to the string and the continuation character & _. Also each line needs surrounding with quotes.

Whether you continue to have problems or not, you may want to add an error routine. Here would be your original code with SQL string correction and an error handler:

Sub Insert2TblInvoice
    REM INSERT RECORDS INTO DATABASE TABLE
    Dim Context
    Dim DB
    Dim Conn
    Dim Stmt
    Dim Result
    Dim strSQL As String
    Context=CreateUnoService("com.sun.star.sdb.DatabaseContext") '< get  the database context
    DB=Context.getByName("autopos.ActivInv") '<- change database name
    Conn=DB.getConnection("","") '<- username & password pair - HSQL default blank
    On Local Error GoTo CloseConn
    Stmt=Conn.createStatement()
  strSQL="INSERT INTO autopos.TblInvoices " & _
    "(InvoiceNo" & _
    ", Group" & _
    ", TaskDscrp" & _
    ", LabrTime" & _
    ", TaskRetail" & _
    ", StockNo" & _
    ", PartNo" & _
    ", PartDscrp" & _
    ", PartsQty" & _
    ", Retail" & _
    ", TParts" & _
    ", GTotal) " & _
  "(Select " & _
    "InvoiceNo" & _
    ", t1.Group" & _
    ", t1.TaskDscrp" & _
    ", t1.LabrTime " & _
    ", (t1.LabrTime * 60.00) as TaskRetail" & _
    ", t1.StockNo" & _
    ", t1.PartNo" & _
    ", t1.PartDscrp" & _
    ", t1.PartsQty" & _
    ", t1.Retail" & _
    ", (t1.PartsQty * t1.Retail) as TParts" & _
    ", (t1.PartsQty * t1.Retail) + (t1.LabrTime * 60.00) as GTotal " & _
    "From autopos.ActiveInv t1" & _
    ")"    '<- change tablename, column name/s and data to be inserted

    Stmt.executeUpdate(strSQL) '<- update the inserted data

    Conn.close() '<- close the db connection
    Exit Sub
CloseConn:
    MsgBox "Error " & Err & ": " & Error$ & " (line : " & Erl & ")"
    Conn.close() '<- close the db connection
End Sub

For a bit more on error processing see links in my answer on this post → CalcBASIC+Non-registred HSQLDB : How to deal with .odb.lck ?

Base|Firebird: CONCATENATE & COALESCE
#3

Please note this was done using Ubuntu 18.04 Mate with MySQL v5.7.28 and LO v 6.3.3.2 with JDBC connector mysql-connector-java-5.1.45.jar

Have experienced select problems with different connection types and versions.

Edit: Did test with native connector (direct) and had no problems. In newer LO releases this is a MariaDB connector.

#4

I am still having issues with the macro running It stops with an error at the first " still. I tried back ticks on the statement and table name, but then stops at the word INTO". Perhaps I misinterpreted the use of the update or left something out the working query string symbology. Here is the snippet I can submit:

Dim strSQL As String
oSql.executeupdate("UPDATE Members SET PrintSel = False")
Context=CreateUnoService("com.sun.star.sdb.DatabaseContext") '< get  the database context
DB=Context.getByName("autopos.ActivInv") '<- change database name
Conn=DB.getConnection("","") '<- username & password pair - HSQL default blank

Stmt=Conn.createStatement()

strSQL="`INSERT INTO` `autopos`.`TblInvoices`
    (InvoiceNo
    , `Group`
    , TaskDscrp
    , LabrTime
    , TaskRetail
    , StockNo
    , PartNo
    , PartDscrp
    , PartsQty
    , Retail
    , TParts
    , GTotal)
(Select
	InvoiceNo
    , t1.`Group`
    , t1.TaskDscrp
    , t1.LabrTime
    , (t1.LabrTime * 60.00) as TaskRetail
#5

Hello,

Try this:

strSQL="INSERT INTO autopos.TblInvoices
    (InvoiceNo
    , Group
    , TaskDscrp
    , LabrTime
    , TaskRetail
    , StockNo
    , PartNo
    , PartDscrp
    , PartsQty
    , Retail
    , TParts
    , GTotal)
(Select
    InvoiceNo
    , t1.Group
    , t1.TaskDscrp
    , t1.LabrTime
    , (t1.LabrTime * 60.00) as TaskRetail
    , t1.StockNo
    , t1.PartNo
    , t1.PartDscrp
    , t1.PartsQty
    , t1.Retail
    , (t1.PartsQty * t1.Retail) as TParts
    , (t1.PartsQty * t1.Retail) + (t1.LabrTime * 60.00) as GTotal
    From autopos. ActiveInv t1
    )"

I used your statement from original post. In your comment you have back ticks around INSERT INTO. This is the SQL command and you would only surround field & table names. But as stated, my test shows even this is not needed.

#6

Negative! No!, it doesn’t run. I did try the query without internal quotes and then added the mysql accepted back ticks and neither worked. I just copied the script above and pasted that back in and checked it again and No it doesn’t work. The only difference I can see is you are using maria db and I am using mysql. I keep getting the following error: BASIC syntax error. Expected: ".

I would have explained the above with the previous post, but I ran out of character space. I do appreciate your assistance, persistence, and specificity.

Thank you!

#7

@David21,

Please see edit in answer. Please use the code there as it has the error routine and will provide essential information if further errors. Will need that information.

Also just a note, with the later versions of LO you are also using a MariaDB connector if you select the MySQL native connection. That is the connector used (built in) whether connecting to MySQL or MariaDB - I am using MySQL.

#8

Ah, we are getting somewhere. I am getting an error on the update statement you provided: BASIC runtime error. Object variable not set. Is the issue the oSQL var? Do I have the update listed in the wrong place. Without the statement the SQL errors on near the final SQL line near “From ActiveInv.”

Sub Insert2TblInvoice
REM INSERT RECORDS INTO DATABASE TABLE
Dim Context
Dim DB
Dim Conn
Dim Stmt
Dim Result
Dim strSQL As String
Context=CreateUnoService(“com.sun.star.sdb.DatabaseContext”) '< get the database context
DB=Context.getByName(“Autopos”) '<- change database name
Conn=DB.getConnection("","") '<- username & password pair - HSQL default blank
oSql.executeupdate(“UPDATE Members SET PrintSel = False”)
Stmt=Conn.createStatement()

strSQL=“INSERT INTO TblInvoices”& _
“(InvoiceNo” & _
", Group"& _
“, TaskDscrp”& _
“, LabrTime”& _
“, TaskRetail”& _
“, StockNo”& _
“, PartNo”& _
“, PartDscrp”& _

#9

It appears there is confusion. The edit I wanted you to use was the last one. The statement:

oSql.executeupdate("UPDATE Members SET PrintSel = False")

is simply an example of not using quotes or back ticks that I tested with. Nothing to do with your code. Remove it.

What I am missing here is your table names. You use:

autopos.TblInvoices

to insert records into, and they are selected from table:

autopos.ActiveInv

Are you certain these are correct? And your description of the error is hazy. Exact messages are important. If you have used the code I posted in that last edit ( from 2019-12-11) then it would give a specific line number and error message. What you used is not all there but it is not what I posted.

Since this is a MySQL DB, it makes it difficult to debug what problem you are having without the source - Base and DB files. It would help if you could provide but understand if that is a problem or info is confidential.

#10

And again if not confidential data, another option is to create an HSQLDB Base file and copy the tables or even just the structure from the MySQL Base file and paste them there. This can then be posted. You do that by editing your question and attach via the paperclip icon on the toolbar - upper left of question when editing.

Just a possibility. Having a rough time working almost blindly.

Also you have never stated what you are attempting. The macro posted just copies all the records from one table to another. You can only do this once. Subsequent execution would error with duplicate records. Please explain.

#11

Hey, we got it and that works great. Thank You! Evidently REM that mistaken code out must have confused the code and caused the final issue. After deleting that mistaken statement and running - it worked great. Yes, I understand that this would have been easier if you had the db and base program, but it does contain over 15k confidential records. I will attempt to edit the question as you suggested and repost the final answer to limit confusion for others following the trail. Thank you again!

#12

This is the final code that worked, which was a result of assistance from Ratslinger’s responses. There was some points of confusion in our discussions, so I felt inclined to post the working code to assist others here:

REM  *****  BASIC  *****
Sub Insert2TblInvoice
REM INSERT RECORDS INTO DATABASE TABLE
Dim Context
Dim DB
Dim Conn
Dim Stmt
Dim Result
Dim strSQL As String
Context=CreateUnoService("com.sun.star.sdb.DatabaseContext") '< get  the database context
DB=Context.getByName("Autopos") '<- change database name
Conn=DB.getConnection("","") '<- username & password pair - HSQL default blank
Stmt=Conn.createStatement()


strSQL="INSERT INTO autopos.TblInvoices"& _
    "(InvoiceNo" & _
    ", `Group`"& _
    ", TaskDscrp"& _
    ", LabrTime"& _
    ", TaskRetail"& _
    ", StockNo"& _
    ", PartNo"& _
    ", PartDscrp"& _
    ", PartsQty"& _
    ", Retail"& _
    ", TParts"& _
    ", GTotal)"& _
  "(Select "& _
    "t1.InvoiceNo"& _
    ", t1.`Group`"& _
    ", t1.TaskDscrp"& _
    ", t1.LabrTime "& _
    ", (t1.LabrTime * 60.00) as TaskRetail"& _
    ", t1.StockNo"& _
    ", t1.PartNo"& _
    ", t1.PartDscrp"& _
    ", t1.PartsQty"& _
    ", t1.Retail"& _
    ", (t1.PartsQty * t1.Retail) as TParts"& _
    ", (t1.PartsQty *t1. Retail) + (t1.LabrTime * 60.00) as GTotal"& _
    " From autopos.ActiveInv as t1);"				 '<- change tablename, column name/s and data to be inserted

Stmt.executeUpdate(strSQL) '<- update the inserted data

Conn.close() '<- close the db connection
End Sub
#13

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.