Ask Your Question
0

LO Base SQL Insert in Macro - 2

asked 2020-07-24 06:35:21 +0100

sw8999 gravatar image

updated 2020-08-06 13:25:07 +0100

Alex Kemp gravatar image

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)

edit retag flag offensive close merge delete

Comments

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 gravatar imageRatslinger ( 2020-07-24 18:01:19 +0100 )edit

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.

Ratslinger gravatar imageRatslinger ( 2020-07-24 21:08:47 +0100 )edit

@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 gravatar imagesw8999 ( 2020-07-25 07:45:43 +0100 )edit

1 Answer

Sort by » oldest newest most voted
1

answered 2020-07-25 07:59:42 +0100

sw8999 gravatar image

updated 2020-07-25 18:03:05 +0100

Ratslinger gravatar image

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
edit flag offensive delete link more

Comments

Edited for clarity - use preformatted text icon on toolbar.

Ratslinger gravatar imageRatslinger ( 2020-07-25 18:03:39 +0100 )edit

@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.

Ratslinger gravatar imageRatslinger ( 2020-07-25 18:06:52 +0100 )edit

nice! thanks for the tips @Ratslinger

sw8999 gravatar imagesw8999 ( 2020-07-26 02:31:27 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2020-07-24 06:35:21 +0100

Seen: 89 times

Last updated: Aug 06