LO Base SQL Insert in Macro - 2
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)
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.
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:
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.
@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