processing datetime with Dynamic SQL
Wen Liqin asked how to read a datetime or interval column from an sqlda into a C host variable, as dttofmtasc on sqldata failed. Art Kagel advised changing the sqltype to char (100), fixchar (108) or string (109) and pointing sqldata at a buffer before the fetch. Jonathan Leffler added that dtime_t is better for manipulation, citing SQLCMD's describe.ec. Wen thanked them.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
How to obtain a Datetime/Interval column from the sqlda fields into a c hostvar in dynamic SQL ? dttofmtasc( (dtime_t *) col->sqldata,str,sizeof( str ), "%Y%m%d %H:%M:%S") ; doesn't work. data at col->sqldata is not a valid datime value. Any help would be very appreciated.
If you are using an sqlda structure, the easiest way to do this is to change the coltype of the col->sqltype to 100 (char), 108 (fixchar), or 109 (string) as appropriate to the type of string you want returned and set col->sqldata = str BEFORE the fetch, then IDS will perform the conversion to string for you automatically. Art S. Kagel ----- Original Message ----- From: Wen Liqin <ids@iiug.org> At: 1/12 10:08 How to obtain a Datetime/Interval column from the sqlda fields into a c hostvar in dynamic SQL ? dttofmtasc( (dtime_t *) col->sqldata,str,sizeof( str ), "%Y%m%d %H:%M:%S") ; doesn't work. data at col->sqldata is not a valid datime value. Any help would be very appreciated. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
thanks
On 1/12/06, WEN LIQIN <wenlq@sohu.com> wrote: > How to obtain a Datetime/Interval column from the sqlda fields into a c > hostvar in dynamic SQL ? > > dttofmtasc( (dtime_t *) col->sqldata,str,sizeof( str ), "%Y%m%d %H:%M:%S") ; > doesn't work. > > data at col->sqldata is not a valid datime value. > > Any help would be very appreciated. Art gave you a very reasonable solution if the main thing you're going to do is print the data. If you need to do other manipulations on it, you may be better off getting the dtime_t or intrvl_t structure. The code in SQLCMD handles that -- see describe.ec. Note that you have to allocate (somehow - not necessarily via malloc() et al) the space where ESQL/C will save the value for you. Unless your col->sqldata was set up to point somewhere stable, your example code would not work. The code in describe.ec is derived from an ancient ESQL/C manual (circa verson 2.10.00). I'm not convinced the type changing it does (SQLDTIME -> CDTIMETYPE) is really necessary. OTOH, it works. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2005.02 -- http://dbi.perl.org/