SQL error using ifxoledbc
JTG could not run SELECT DATE('01/01/'||YEAR(onset_date)+1) ... GROUP BY 1,2 through the Informix OLE DB provider (2.90.TC3, DS 9.40.FC7), error -2147217913, though it worked over ODBC. Carsten Haese suggested MDY(1,1,YEAR(onset_date)+1), which worked; the string form depends on client and server date formats (DBDATE, GL_DATE, CLIENT_LOCALE).
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
ifxoledbc.dll version: 2.90.TC3 DB Version: DS 9.40.FC7 I'm unable to get the following SQL to run using the Informix OLEDB provider: SELECT DATE('01/01/'||YEAR(onset_date)+1) dt1, birthdate as dt2 FROM customer WHERE custID = 403682 AND MONTH(birthdate) < 12 GROUP BY 1,2 When I run this (using ADODB), I get error#: -2147217913, but no description. This exact same SQL works if I connect using my ODBC driver to the same DB. In playing with it, it seems it doesn't like the combination of DATE() and GROUP BY 1. If I remove either one, the SQL works. Is this a know bug? I've played around with using CAST() but get the same error. -jtg
On Wed, 2008-01-30 at 11:56 -0800, JTG wrote: > ifxoledbc.dll version: 2.90.TC3 > DB Version: DS 9.40.FC7 > > I'm unable to get the following SQL to run using the Informix OLEDB > provider: > > SELECT DATE('01/01/'||YEAR(onset_date)+1) dt1, birthdate as dt2 > FROM customer > WHERE custID = 403682 > AND MONTH(birthdate) < 12 > GROUP BY 1,2 > > > When I run this (using ADODB), I get error#: -2147217913, but no > description. -2147217913 translates to hex 80040e07, which according to Google might mean "wrong syntax in Date expression". I'm not sure whether that is the actual problem, but representing a date as a string is prone to breakage in case the client and server disagree on the format. I'd suggest using a date-format-agnostic method of building dt1. Try using the following expression for dt1: MDY(1,1,YEAR(onset_date)+1). Hope this helps. -- Carsten Haese http://informixdb.sourceforge.net
On Jan 30, 3:47 pm, Carsten Haese <cars...@uniqsys.com> wrote: > On Wed, 2008-01-30 at 11:56 -0800, JTG wrote: > > ifxoledbc.dll version: 2.90.TC3 > > DB Version: DS 9.40.FC7 > > > I'm unable to get the following SQL to run using the Informix OLEDB > > provider: > > > SELECT DATE('01/01/'||YEAR(onset_date)+1) dt1, birthdate as dt2 > > FROM customer > > WHERE custID = 403682 > > AND MONTH(birthdate) < 12 > > GROUP BY 1,2 > > > When I run this (using ADODB), I get error#: -2147217913, but no > > description. > > -2147217913 translates to hex 80040e07, which according to Google might > mean "wrong syntax in Date expression". I'm not sure whether that is the > actual problem, but representing a date as a string is prone to breakage > in case the client and server disagree on the format. I'd suggest using > a date-format-agnostic method of building dt1. Try using the following > expression for dt1: MDY(1,1,YEAR(onset_date)+1). > > Hope this helps. > > -- > Carsten Haesehttp://informixdb.sourceforge.net- Hide quoted text - > > - Show quoted text - Thanks. That acutally worked. I'm confused as to WHY it works - don't DATE() and MDY() both return a DATE datatype?
On Thu, 2008-01-31 at 04:58 -0800, JTG wrote: > Thanks. That acutally worked. I'm confused as to WHY it works - > don't DATE() and MDY() both return a DATE datatype? Yes, they do, but that's not the point. The input to DATE() is a character string that represents a date using a format that is determined by some mechanism that I won't pretend to understand, but it involves environment variables called DBDATE, GL_DATE, and/or CLIENT_LOCALE. You build a date in mm/dd/yyyy format, but apparently the server is expecting some other date format, which causes the error. With MDY(), you eliminate this point of failure because the date is conveyed by passing three integer numbers in a defined order. -- Carsten Haese http://informixdb.sourceforge.net