RE: New Era/ODBC/Oracle

Hi Shaun, The type of the field on the client must match the type of the field being retrieved. Why not make the Newera field a datetime? Failing that, use the server to convert it to whatever format you need on the client. Oracle must have functions which convert datetime fields to strings or dates? If not, then ODBC has grammar which can force type conversions. To do any of these, you must modify the SELECT statement being sent through ODBC. e.g. If you're using Oracle, your SELECT statement should look something like SELECT to_char ( dateField ) FROM MyTable. where to_char is some kind of conversion function. Regards, Ciaran Kelly -- RTE Technology Division Donnybrook Dublin 4 Ireland Email: Ciaran.Kelly@rte.ie ---------- From: ShaunCampbell Sent: Monday, June 07, 1999 9:25 PM To: informix-list@iiug.org Subject: New Era/ODBC/Oracle I am trying to port a New Era 3.11 application to run against an Oracle database. >From my limited knowledge of Oracle I understand it does not have date data types but only datetime data types. I am performing a simple query to retrieve records from the Oracle database using an Merant ODBC driver. I can retrieve records from the particular table, and can read and display numerical fields and character fields, however, I cannot retrieve any dates even though there is data in the field. This is the first time I have used ODBC and am not sure what to expect. If I getInfo on the column type it reports it to be an SQL_datetime any ideas what I am doing wrong? If anyone else had written a New Era application to run against Oracle I would be pleased to here of your experiences. Regards Shaun Campbell

Ciaran Kelly wrote: > Hi Shaun, > > The type of the field on the client must match the type of the field being > retrieved. Why not make the Newera field a datetime? Failing that, use > the server to convert it to whatever format you need on the client. Oracle > must have functions which convert datetime fields to strings or dates? If > not, then ODBC has grammar which can force type conversions. To do any of > these, you must modify the SELECT statement being sent through ODBC. e.g. > If you're using Oracle, your SELECT statement should look something like > > SELECT to_char ( dateField ) FROM MyTable. > > where to_char is some kind of conversion function. > > Regards, > > Ciaran Kelly > > -- > RTE > Technology Division > Donnybrook > Dublin 4 > Ireland > > Email: Ciaran.Kelly@rte.ie > ---------- > From: ShaunCampbell > Sent: Monday, June 07, 1999 9:25 PM > To: informix-list@iiug.org > Subject: New Era/ODBC/Oracle > > I am trying to port a New Era 3.11 application to run against an Oracle > database. > > >From my limited knowledge of Oracle I understand it does not have date > data types but only datetime data types. > > I am performing a simple query to retrieve records from the Oracle > database using an Merant ODBC driver. I can retrieve records from the > particular table, and can read and display numerical fields and > character fields, however, I cannot retrieve any dates even though there > is data in the field. > > This is the first time I have used ODBC and am not sure what to expect. > If I getInfo on the column type it reports it to be an SQL_datetime any > ideas what I am doing wrong? > > If anyone else had written a New Era application to run against Oracle I > would be pleased to here of your experiences. > > Regards > > Shaun Campbell Hi Guys! It's been a couple of years since I last worked with ORACLE but as far as I can remember, ORACLE only has a "DATE" data type and their DATE type is equivalent to INFORMIX's DATETIME type with a resolution - I think - of YEAR TO SECOND. When selecting a DATE type column from an ORACLE database table, the default format is something like MM/DD/YY. Again, I'm only going from memory and it's been a long time. ORACLE does have a TO_CHAR function where you can convert a DATE column to a VARCHAR string in a format you specify. Syntax should be something like the following: TO_CHAR(date_col,'format string') where the format string is fairly intuitive. I think something like YYYY/MM/DD HH24:MI:SS or combinations thereof. Hope this has been of some assistance. Avi. -- /\\ \\ /| Avi Abrami, Analyst/Programmer, Telegate Ltd. /__\\ \\ / | 7 Haplada Street, Or-Yehuda, ISRAEL / \\ \\/ | Phone:+972-3-5388717 Fax:+972-3-5335877 eMail:avia@telegate.co.il