On Thu, 28 Feb 2013 10:23:17 -0400, Kurt Fitzner <kurt.fitzner@...>
wrote:

> On 28/02/2013 5:45 AM, Poul Dige wrote:
>>
>> I don't know if this will solve your problem but usually you use
>> variable names as parameters, e.g.
>>
>>
>> select col1 from table 1 where col1 = :myparameter
>>
> Thanks for the suggestion. Very few ODBC drivers support named
> parameters. Most of them require a question mark as a placeholder. The
> Firebird driver is no exception.
>
> I have determined that the problem isn't with the use of parameters.
> It's with testing a parameter against NULL. For example, the following
> statements work:
>
> select col1 from table1 where col1 = ?
> select col1 from table1 where (col1 = ? and ? = 1)
> select col1 from table1 where (col1 = ? and ? = 'test')
>
> But what I really want, doesn't work:
>
> select col1 from table1 where (col 1 = ? and ? is not null)
> select col1 from table1 where (col 1 = ? or ? is null)

Are you using Firebird 2.5? If not it won't work, as SQL_NULL as a
parameter datatype was only introduced in Firebird 2.5.
You may want to check if your client library is up-to-date and if the
Firebird ODBC Driver supports the SQL_NULL type for parameters.
In all other cases the workaround IIRC is to cast the parameter to a
specific type, for example:

select col1 from table1 where (col 1 = ? or CAST(? AS CHAR(1)) is null)

> This really has me stumped, and I'm wondering if it's a bug in the ODBC
> driver, as I cannot for the life of me see why it would be a problem.
> The Jaybird JDBC works just fine on the same query. It's so sluggish,
> though, I really need ODBC to work.

Could you define sluggish, what is the size of the dataset? The problem
with Base is that it expects a specific behavior that is not defined in
JDBC, and is generally discouraged and especially with Jaybird can be
detrimental (for example expecting holdable resultsets over commits which
essentially means 'keep everything in memory').

Mark