Re: DYNAMIC SQL Select on char or varchar column

Alex Molochnikov wrote: > Everytime a dynamic sql select returns a char or varchar field that > is the full length of the column, the last character will be missing; > yet in the database itself I know that the column includes the last > character. > > For Example: > create table abc (a char(3)) > insert into table abc (a) values('one')> > select * from abc where a = 'one' > (1 row returned)> > select * from abc where a = 'on' > (0 rows returned)> > BUT if I do the following dynamic sql query > > sqlCommand = "select a from abc" > > EXEC SQL prepare sqlcursor from :sqlCommand; > EXEC SQL declare selectCursor cursor for sqlcursor; > EXEC SQL describe sqlcursor into sqldaPtr; I trust you set up the data areas for the SQLDA structure somewhere about here! If you don't, you are headed for crashes, and sooner rather than later. > EXEC SQL open selectCursor; > EXEC SQL fetch selectCursor using descriptor sqldaPtr; > > My sqldata will only have 'on' rather than 'one'. It is the same for > varchar. All my other fields are totally fine. Probably because the SQLDA data structures say that the size is 3 characters, but the system assumes you'd rather have truncated, null terminated strings. If you really want character arrays without the null terminator, you'd modify the type information in the SQLDA area to indicate that the type is CFIXCHAR instead of SQLCHAR or SQLVARCHAR. Or you'd add one to the lengths of the char fields. This is documented in the examples in the ESQL/C Programmer's Reference, if only you could find the information in the chapter on dynamic ESQL/C (which has grown uncomfortably long in recent editions). Incidentally, you might find the code in describe.ec which comes with SQLCMD useful -- see the IIUG archives (http://www.iiug.org). Yours, Jonathan Leffler (j.leffler@acm.org) #include <many-aliases.h>