Parameterized Queries with OpenOffice Base

[Bruce Alspaugh]

I get SQL syntax errors whenever I attempt to use H2 parameterized
queries with OpenOffice Base. For example:

SELECT "DESCRIPTION" AS "DESCRIPTION", "ID" AS "ID" FROM
"TOPAZ"."PUBLIC"."COLOR" AS "COLOR" WHERE "ID" = :COLOR_ID

When I run the query, OpenOffice correctly displays a dialog allowing me
to enter a value for the COLOR_ID parameter. However, I then get a SQL
syntax error:

SQL Status: 42001
Error code: 42001

Syntax error in SQL statement "SELECT ""DESCRIPTION"" AS
""DESCRIPTION"", ""ID"" AS ""ID"" FROM ""TOPAZ"".""PUBLIC"".""COLOR"" AS
""COLOR"" WHERE ""ID"" = :[*]COLOR_ID"; expected "ALL, ANY, SOME,
SELECT, FROM"; SQL statement:
SELECT "DESCRIPTION" AS "DESCRIPTION", "ID" AS "ID" FROM
"TOPAZ"."PUBLIC"."COLOR" AS "COLOR" WHERE "ID" = :COLOR_ID [42001-135]

If I replace the COLOR_ID parameter with a hardcoded value, the query
runs correctly. Parameterized queries work for me when I use the
embedded HSQLDB that comes with OpenOffice.

Any suggestions?

Bruce

[Bruce Alspaugh]

I think I found a way to correct the problem. In OpenOffice Base, go to
Edit->Database->Advanced Settings. On the "Special Settings" tab, select
the option, "Replace named parameters with '?'".

Bruce

[Thomas Mueller]

Hi,

Do you know if the named parameters syntax (":COLOR_ID") is supported
by any database (using JDBC)?

Regards,
Thomas