LO Base SQL Insert in Macro - 2

[sw8999]

Hi,

I am trying to do a similar thing to the previous thread (LO Base SQL Insert in Macro)… and am starting with the code that’s already posted there, and made some tweaks…

I get an runtime error message at the line “Stmt.executeUpdate(strSQL)”. I’m on HDSQLDB embedded, MacOS Mojave 10.14.6 and LibreOffice 6.4.5 (mac 64-bit). Will appreciate the help.

Thanks!
-Steph

Error Message:

BASIC runtime error. An exception
occurred Type:
com.sun.star.sdbc.SQLException
Message: Wrong data type:
java.lang.IllegalArgumentException.

---

The code i am working with:

Sub Insert2TblOrders 
Dim context 
Dim DB 
Dim Conn 
Dim Stmt 
Dim Result 
Dim TodaysDate as String 
Dim strSQL As String
    Context=CreateUnoService("com.sun.star.sdb.DatabaseContext")
DB=Context.getByName("IKK_V7") '<-
Conn = DB.getConnection("","")
    Stmt=Conn.createStatement()
    TodaysDate =Format(Now(),"dd/mm/yyyy")
    strSQL=
"INSERT INTO "&"""INVOICES"""& _
    "( " & """WARGANO""" & _
     ", INVOICEDAMOUNT"& _
    ", INVOICEDATE"& _
    ", INVOICEID"& _
    ", STATUSID"& _
    ", ORDER_ID)"& _   
"(Select "& _
    "WARGANO"& _
    ", sum(AMOUNT) AS INVOICEDAMOUNT"& _
    ", "&"'"& TodaysDate &"'" & " AS INVOICEDATE"& _
    ", concat('INV',""WARGANO"") AS INVOICEID"&_
    ", '103' AS STATUSID"& _
    ", ORDER_ID"& _
    " From "&"""ORDERS"""&"  group by    WARGANO,INVOICEID,STATUSID,ORDER_ID);"
Stmt.executeUpdate(strSQL) 
Conn.close() 
End Sub

(edit: codified code)

[Ratslinger]

Hello,

You have not stated what database you are using - HDSQLDB embedded, MySQL, PostgreSQL, etc. This is important in answering. Also base upon the error, it appears an incorrect data type is being used for one or more fields (such as trying to insert text into a numeric field). Need the definition of the table field types used.

Please do NOT use an Answer to respond. Either edit question or add a comment.

[Ratslinger]

Have given a second look at your SQL. It appears you have not understood its construction in a macro, The example you used is not normal. Here is (as best I can determine) the statement you want to use in a macro:

INSERT INTO INVOICES ( WARGANO, INVOICEDAMOUNT, INVOICEDATE, INVOICEID, STATUSID, ORDER_ID) (Select "WARGANO", sum(AMOUNT) AS INVOICEDAMOUNT, TodaysDate AS INVOICEDATE, concat('INV',WARGANO) AS INVOICEID, '103' AS STATUSID, ORDER_ID From ORDERS group by WARGANO,INVOICEID,STATUSID,ORDER_ID);

Please verify this is wanted statement (or note corrected version) and insure it works before posting (can execute in main Base menu Tools->SQL). You should do your testing with a backup copy of the Base file & database.

Also, my answer in this post → Operation in a sql in a macro shows steps in the construction of the SQL string in a macro.

[sw8999]

@Ratslinger Thanks for the response! I checked out the post you referred me, eliminated lots of quotes and tested the SQL statement. Really helpful tips! I got a working code now. Thank you

[sw8999]

I got the Date logic wrong. After eliminating “TodaysDate =Format(Now(),“dd/mm/yyyy”)”, and use “NOW()” as the Date, the code runs fine. Thanks @Ratslinger

Sub Insert2TblOrders  
Dim Context 
Dim DB 
Dim Conn 
Dim Stmt 
Dim Result
Dim strSQL As String

Context=CreateUnoService("com.sun.star.sdb.DatabaseContext")
DB=Context.getByName("IKK_V7")  Conn =
DB.getConnection("","")
Stmt=Conn.createStatement()
strSQL=
"INSERT INTO "&"INVOICES"& _
     "( " & "WARGANO" & _
     ", INVOICEDAMOUNT"& _
     ", INVOICEID"& _
     ", STATUSID"& _
     ", ORDER_ID" &_
     ", INVOICEDATE" & ")" &_
"(" & "SELECT " & _
     "WARGANO"& _
     ", sum(AMOUNT) AS INVOICEDAMOUNT"& _
     ", concat('INV',WARGANO) AS INVOICEID"&_
     ", '103' AS STATUSID"& _
     ", ORDER_ID" &_
     ", **NOW() AS INVOICEDATE"** & _
" From ORDERS  group by WARGANO,INVOICEID,STATUSID,ORDER_ID);"
 
Stmt.executeUpdate(strSQL) 
 
Conn.close() 

End Sub

[Ratslinger]

Edited for clarity - use preformatted text icon on toolbar.

[Ratslinger]

@sw8999,

You can still do better. For example:

", INVOICEDATE" & ")" &_

should be:

", INVOICEDATE)" &_

Cleaning it up will make it easier to read in the future when changes may be needed.

[sw8999]

nice! thanks for the tips @ratslinger