--- In firebird-support@yahoogroups.com, "Marvin" <gallagher_98@...>
wrote:

>
> Hey guys, been cracking my head trying to solve this problem.
> Wondering if anyone can help me shed some light into this.
>
> -[The Situation]-
>
> I'm developing in Java and using firebird 1.5. I have a prepared
> statement with the sql set to sth like :
>
> 1: select table1.documentno,
> 2: table2.refno,
> 3: table2.chequestatus
> 4: from table1,table2
> 5: where table2.tid = table1.tid
> 6: and table1.documentno = ?
> 7: and table2.refno = ?
>
> the prepared statement is then fill with parameters like so:
>
> pstmt.setString(1,variable1);
> pstmt.setString(2,variable2);
>
> The problem here happens when, sometimes, variable2 is null. when i
> execute the prepared statement, it would not return any resultset. I
> noticed that to get resultsets, i'd have to use the following SQL in
> line 7:
>
> " and table2.refno is null "
>
> I tried using
>
> pstmt.setNull(2,Types.VARCHAR);
>
> if variable2 is null, but it still returns no resultset.
>
>
> -[The Question]-
>
> My question here is, is there a String or character in firebird that
> represents NULL for the type varchar such that i am able to do a '='
> operation to evaluate to a NULL value, instead of using 'is' operator?
> If there isn't, anyone have a better idea instead of creating 2
> prepared statements (1 more to cater for variable2 = null) ???
>

Check on the firebird-java list. With Delphi, I can do the following:

qry.SQL.Text :=
'select table1.documentno,
table2.refno,
table2.chequestatus
from table1,table2
where table2.tid = table1.tid
and table1.documentno = :documentno
and table2.refno = :refno';

qry.ParamByName('documentno').AsInteger = 5;
qry.ParamByName('refno').Clear;
qry.Open;

The interface does the translation for me.

On a side note, you may find the following syntax easier to read:

select table1.documentno,
table2.refno,
table2.chequestatus
from table1
join table2 on (table2.tid = table1.tid)
where table1.documentno = :documentno
and table2.refno = :refno

It keeps the real 'conditions' of the query separate from the join
conditions. You also don't need to select the first two fields because
they are passed in as parameters.

Adam