Trouble with SQL INSERT in macro in OOBase

[jrolland]

I have looked at many examples of how to execute SQL commands within a macro, but can’t get this to work.

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

Below is the code:

'create "connection and sql statement" to allow sql commands to be executed

DIM oStatement AS OBJECT
DIM sSQL AS STRING

oStatement = oForm.ActiveConnection.createStatement() 'Create an SQL statement object

'create, and format SQL INSERT command with "variables" as VALUES

sSQL = "INSERT INTO ""tbl_TREATMENTS"" (""fromWELLS_ID"", ""treatment_record"", ""record_date"", ""serviced_by_name"") VALUES ('" & stIDvalue & "', '" & stRemarksValue & "', '" & stDateValue & "', '" & stProvidersValue & "')" 

PRINT sSQL

oStatement.ExecuteUpdate(sSQL) <----- this line generates the error

Any ideas? Is it a SQL formatting issue?

My variables all contain, and print their contents correctly.

Help, and thanks in advance.

Edit info [Opaque]: Put code into preformatted text

[Ratslinger]

Hello @jrolland,

You have a print statement for sSQL. Could you provide the result of that statement? On the surface, the date field may be a part of the problem. Dates must be in the format of YYYY-MM-DD. There may be other things but it is not known what field types are in the table or how the variables are constructed.

Please do not use an answer to respond. Either edit original question or add a comment.

[jrolland]

The variable(s) contents print to my screen as follows:
stIDvalue = ‘280’
stProvidersValue = ‘Rig #2’
stDateValue = ‘20192909’
stRemarksValue = ‘test this now’
I included the hyphens because when I print the INSERT line they are there. If I just print the variables alone the hyphens are not printed.

My table, “tbl_TREATMENTS” field list is as follows:
treatment_ID (integer, auto increment)
fromWELLS_ID (integer)
treatment_record (text varchar)
record_date (date)
serviced_by_name (text varchar)

[Ratslinger]

Hello,

Based upon the data in the comments the problem is in the date field. It is in the incorrect format. Locale does not matter here. It must be in YYYY-MM-DD format including the hyphens. Your date is YYYYDDMM without the hyphens.

Tested in Tools->SQL:

Status #2 was using your date; status #3 is with corrected date format.

[Ratslinger]

Just a note. With or without hyphens, your date produces the same error.

[jrolland]

I tried setting the “date” VALUE in my SQL statement to a ‘literal’ formatted as 2019-09-29.

Now I get the error:
Type: com.sun.star.sdbc.SQLException
Message: Column Not Found: 2019-09-29

What can be happening? Is there any other info that I can send that will help figure this out?

Thanks, again

[Ratslinger]

@jrolland,

If you set the date to a literal, did you remove the quotes? The error seems to think you have designated a field in the statement. Try:

sSQL = "INSERT INTO ""tbl_TREATMENTS"" (""fromWELLS_ID"", ""treatment_record"", ""record_date"", ""serviced_by_name"") VALUES ('" & stIDvalue & "', '" & stRemarksValue & "', '2019-09-29' , '" & stProvidersValue & "')"

Edit: If you still have further problems, post a sample Base file in your edited question. Be sure to remove any personal or confidential information.

[jrolland]

OK. Using the “literal” value with proper syntax inserted the record correctly. Thank you!

Can you point me in the right direction to “format” my date variable value from YYYYMMDD to YYYY-MM-DD? I’ve looked and looked but find almost nothing about setting up formatting for strings.

A little nudge/direction would be appreciated.

Thanks so much, your expertise was invaluable.

[Ratslinger]

Where are you getting the date from? What is the original format? If something like DD/MM/YY (or YYYY) or MM/DD/YY then you can use FORMAT directly:

sSQL = "INSERT INTO ""tbl_TREATMENTS"" (""fromWELLS_ID"", ""treatment_record"", ""record_date"", ""serviced_by_name"") VALUES ('" & stIDvalue & "', '" & stRemarksValue & "', " & Format(YOUR_DATE_FIELD, "'YYYY-MM-DD'") & " , '" & stProvidersValue & "')"

[Ratslinger]

Here is a little more crude of a method using your YYYYMMDD value:

reformDate = Left(stDateValue,4) & "-" & Right(Left(stDateValue,6),2) & "-" & Right(stDateValue,2)

then use reformDate in SQL.

If this answers your question please tick the (upper left area of answer). It helps others to know there was an accepted answer.