| Subject | Re: [Firebird-Java] Problem with casted columns in SP |
|---|---|
| Author | Rodolfo M. Raya |
| Post date | 2002-06-11T12:57:19Z |
Hi,
Did you create your database as Dialect 1?
Dialect 1 databases have TIMESTAMP data types, not DATE. If you try to cast a TIMESTAMP field to VARCHAR(20) you can't get the right result. Even you get a STRING FIELD TRUNCATION EXCEPTION or you get nothing at all. However, you can cast it as VARCHAR(50) to let the TIME portion fit in the string.
HTH,
Rodolfo
Did you create your database as Dialect 1?
Dialect 1 databases have TIMESTAMP data types, not DATE. If you try to cast a TIMESTAMP field to VARCHAR(20) you can't get the right result. Even you get a STRING FIELD TRUNCATION EXCEPTION or you get nothing at all. However, you can cast it as VARCHAR(50) to let the TIME portion fit in the string.
HTH,
Rodolfo
On Tue, 2002-06-11 at 07:59, Thomas Viohl wrote:Ok, Helen, your advices are all ok. I simplified my procedure for this newsgroup ... But: this does not solve my problem !! If I user the CAST function on the dates I get no result in that columns. When I do not use CAST , firebird is implicitely casting the dates and I get correct values in the resulting Varchars. Sample: select CAST (myDate as varchar(20)) -->> no returning values select myDate -->> correct values regards Thomas Helen Borrie wrote: >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/ >_______________________________________________________ > > > >To unsubscribe from this group, send an email to: >Firebird-Java-unsubscribe@yahoogroups.com > > > >Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/ > > > > -- Thomas Viohl oraise GmbH Am Wall 149/150 D-28195 Bremen (Germany) ------------------------ eMail: t.viohl@... Fon:++49+421-335533 Fax:++49+421-3355355 ------------------------ ------------------------ Yahoo! Groups Sponsor ---------------------~--> Will You Find True Love? Will You Meet the One? Free Love Reading by phone! http://us.click.yahoo.com/Deo18C/zDLEAA/Ey.GAA/saFolB/TM ---------------------------------------------------------------------~-> To unsubscribe from this group, send an email to: Firebird-Java-unsubscribe@yahoogroups.com Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/