At 08:54 AM 11-06-02 +0200, you wrote:

>Hi there,
>I am using the Firebird jdbc driver and I have following problem:
>Inside a stored procedure I do a cast on date columns to varchars. When
>I query this procedure the casted columns are always NULL. In IBConsole
>I got the correct values.
>
>Sample

Some amendments to your SQL syntax are necessary to eliminate ambiguity:

>...
>begin
>/* select CAST(login_valid_from as varchar(10)) as col1, */

select CAST(m.login_valid_from as varchar(10)) as col1,

>/* CAST(login_invalid_after as varchar(10)) as col2 */

CAST(m.login_invalid_after as varchar(10)) as col2

> from USER1 m, userprops u
> where m.userid = u.userid
> /* and userid=:in_userid */

and m.userid=:in_userid

> into :col1,:col2;
>suspend;
>end
>...

Even better, is to avoid the SQL-89 implicit join syntax entirely and use
the more up-to-date SQL-92 syntax, which makes "warm-bodied scanner
parsing" (a.k.a. eyeball QA) a lot easier:

begin
select CAST(m.login_valid_from as varchar(10)) as col1,
CAST(m.login_invalid_after as varchar(10)) as col2
from USER1 m
join userprops u
on m.userid = u.userid /* join criterion */
where m.userid=:in_userid /* search criterion */
into :col1,:col2;
suspend;
end

cheers,
Helen


All for Open and Open for All
Firebird Open SQL Database · http://firebirdsql.org ·
http://users.tpg.com.au/helebor/
_______________________________________________________