Using parameters in Embedded SQL queries

[BillL1]

Using simple Embedded SQL Update command using parameter as follows:
UPDATE “T01-RawTransaction”
SET “StatementDate” = :ParamDate
WHERE “Recorded” = TRUE
AND “StatementDate” IS NULL

“StatementDate” is of type Date and “Recorded” is of type Boolean.

Error is: Assert failed: S0000 Direct execute with param count > 0java.lang.Exception
at org.hsqldb.Trace.getStackTrace(Unknown Source)
at org.hsqldb.Trace.doAssert(Unknown Source)
at org.hsqldb.DatabaseCommandInterpreter.executePart(Unknown Source)

If I change the parameter to a specificvalue I get a different error complaining about the data type being wrong.

If I use a similar parameter in an INSERT command, the statement executes correctly when I use “Show output of ‘select’ statement” but when I choose “Run SQL command directly” the resulting parameter-replaced field is blank.

What am I doing wrong?

[RobertG]

Queries with parameter will work in the GUI. There will be asked for the parameter by the GUI and after the dialog ends this values will be filled in the query and the query will be send as SQL-code without parameter to the database.

I never tried to use parameters in Tools → SQL. Tested it and there wont be asked for any content for this parameters.

[BillL1]

Trouble is I didn’t think you could use INSERT and UPDATE using the GUI,and they are the commands I need.
I’ve used parameters successfully (GUI and SQL) in normal queries.

[RobertG]

GUI is only available for input queries - no updates, no deletes, no inserts. All has to start with SELECT.

If you will often use the other commands you will need macro code instead.

[BillL1]

Thanks for that. I’ll move on to that chapter of the book.
However, HSQLDB uses ‘Standard’ SQL so Update and Insert should be usable, with parameters.

[RobertG]

Parameters as described by :ParamDate are offered from the GUI. This has nothing to do with HSQLDB or any other databases you use.

In German Base Handbuch is described how parameters will work using macros. It is all called “prepared statements.”

[cpb]

@BillL1,

PARAMETER queries are a function of BASE and NOT the back-end database therefore they do not work when in direct mode.
.
UPDATE, INSERT, DELETE are database functions and should be executed using direct mode i.e. menu:>Tools>SQL.
.
you cannot use :PARAM and UPDATE in the same SQL statement.
.
if using hsqldb and “StatementDate” is of type DATE then executing this code via menu:>Tools>SQL will do the job.
NOTE: ‘2024-01-15’ is equivalent to 15 Jan 2024, change as desired.

UPDATE "T01-RawTransaction"
SET "StatementDate" = '2024-01-15'
WHERE "Recorded" = TRUE
AND "StatementDate" IS NULL

[BillL1]

Thanks. I’ll follow that up.