Subject Re: [Firebird-Java] Select with constant value
Author Roman Rokytskyy
> Now I want to use it from jaybird, by means of a prepared statement,
> because the constant value will change in every new query.
> I tested it just replacing the constant value with the usual ? character
> without success. It seem this parameter is not considered when preparing
> the query so it fails because of some type mismatch in the subsequent
> parameters.

In fact you should get

org.firebirdsql.jdbc.FBSQLException: GDS Exception. 335544569. Dynamic SQL
SQL error code = -804
Data type unknown

This means that server does not know the type of the parameter to prepare
it. This is the same issue as in SELECT * FROM myTable WHERE ? IS NULL OR
myPk = ? that was discussed some time ago.

> Isn't this kind of parametrized query currently supported?

No, unfortunately this syntax is not supported. But if your query does not
change itself, you can create a selectable procedure that will do the same

For example, if you had

SELECT mainCol, ? AS selectParam FROM myTable WHERE someOtherCol = ?

you should create following procedure

CREATE PROCEDURE workaroundProc(
firstParam INTEGER,
secondParam VARCHAR
selectParam INTEGER
FOR SELECT myCol, :firstParam FROM myTable
WHERE someOtherCol = :secondParam
INTO :mainCol, :selectParam

Then you can use

SELECT mainCol, selectParam FROM workaroundProc(?, ?)

This will give you the same result with virtually no performance loss.