Bug #36003 for DBD-ODBC: select on date field gives "Numeric value out of range" with Timesten

Mon May 19 08:32:44 2008 libithashanmugam [...] yahoo.com - Ticket created

Subject:

select on date field gives "Numeric value out of range" with Timesten

Hi, I use TimesTen 7.0.3 on RHEL 4.1 64 bit. I have a perl program which uses DBD::ODBC to access TimesTen. I have a table which has varchar fields and a date field. When I do a select on the date field, I get the following error DBD::ODBC::st fetchrow_arrayref failed: [unixODBC][TimesTen][TimesTen 7.0.3.0.0 CLIENT]Numeric value out of range (SQL-22003) The same program works well with 32 bit TimesTen client and perl installation on the same box. The issue is only with 64 bit installation. I did a DBI trace and noted only difference between the two: field type recognition 32 bit prog (working) col 1: TIMESTAMP (93) len= 16 disp= 21, prec= 20 scale=0 col 1: 'CHANGED_TIME' sqltype=TIMESTAMP, ctype=SQL_C_CHAR, maxlen=21, (dp = 0, cp = 0) 64 bit prog (erroring out) now using col 1: type = unknown (0), len = 16, display size = 16, prec = 20, scale = 0 Can someone help in solving the issue? Thanks

Mon May 19 09:33:56 2008 bohica [...] ntlworld.com - Correspondence added

From:

martin.evans [...] easysoft.com

On Mon May 19 08:32:44 2008, lisha wrote: Show quoted text

> Hi, > I use TimesTen 7.0.3 on RHEL 4.1 64 bit. I have a perl program which > uses DBD::ODBC to access TimesTen. > > I have a table which has varchar fields and a date field. When I do a > select on the date field, I get the following error > > DBD::ODBC::st fetchrow_arrayref failed: [unixODBC][TimesTen][TimesTen > 7.0.3.0.0 CLIENT]Numeric value out of range (SQL-22003) > > > The same program works well with 32 bit TimesTen client and perl > installation on the same box. The issue is only with 64 bit > installation. I did a DBI trace and noted only difference between the > two: field type recognition > > 32 bit prog (working) > col 1: TIMESTAMP (93) len= 16 disp= 21, prec= 20 scale=0 > col 1: 'CHANGED_TIME' sqltype=TIMESTAMP, ctype=SQL_C_CHAR, maxlen=21, > (dp = 0, cp = 0) > > 64 bit prog (erroring out) > now using col 1: type = unknown (0), len = 16, display size = 16, prec = > 20, scale = 0 > > Can someone help in solving the issue? > > Thanks

You need to verify: o 64bit timesten driver was built with the same compile line as the unixODBC driver you are using as there are various ways of building ODBC for 64bit platforms. o you have the latest unixODBC driver manager (best advice for this is on the unixodbc lists where I see you posted this problem first). o that timesten is returning the right column types for your query in 64bit mode. You can do this with unixODBC's isql and "help mytable". There are lots of compatibility issues with 64bit platforms and ODBC because Microsoft changed their mind in their ODBC headers when they introduced a SQLLEN type. If you get isql running in 64bit mode and run the help mytable command it will output what your driver thinks the column types are and if you post that here and on the unixODBC list we might be able to help further. However, at this point, reporting this as a problem in DBD::ODBC is a little premature. Martin -- Martin J. Evans Wetherby, UK

Mon May 19 09:33:57 2008 The RT System itself - Status changed from 'new' to 'open'

Tue May 20 03:20:17 2008 libithashanmugam [...] yahoo.com - Correspondence added

From:

libithashanmugam [...] yahoo.com

On Mon May 19 09:33:56 2008, MJEVANS wrote: Show quoted text

> On Mon May 19 08:32:44 2008, lisha wrote:

> > Hi, > > I use TimesTen 7.0.3 on RHEL 4.1 64 bit. I have a perl program which > > uses DBD::ODBC to access TimesTen. > > > > I have a table which has varchar fields and a date field. When I do a > > select on the date field, I get the following error > > > > DBD::ODBC::st fetchrow_arrayref failed: [unixODBC][TimesTen][TimesTen > > 7.0.3.0.0 CLIENT]Numeric value out of range (SQL-22003) > > > > > > The same program works well with 32 bit TimesTen client and perl > > installation on the same box. The issue is only with 64 bit > > installation. I did a DBI trace and noted only difference between the > > two: field type recognition > > > > 32 bit prog (working) > > col 1: TIMESTAMP (93) len= 16 disp= 21, prec= 20 scale=0 > > col 1: 'CHANGED_TIME' sqltype=TIMESTAMP, ctype=SQL_C_CHAR, maxlen=21, > > (dp = 0, cp = 0) > > > > 64 bit prog (erroring out) > > now using col 1: type = unknown (0), len = 16, display size = 16, prec = > > 20, scale = 0 > > > > Can someone help in solving the issue? > > > > Thanks

> > You need to verify: > > o 64bit timesten driver was built with the same compile line as the > unixODBC driver you are using as there are various ways of building ODBC > for 64bit platforms. > > o you have the latest unixODBC driver manager (best advice for this is > on the unixodbc lists where I see you posted this problem first). > > o that timesten is returning the right column types for your query in > 64bit mode. You can do this with unixODBC's isql and "help mytable". > > There are lots of compatibility issues with 64bit platforms and ODBC > because Microsoft changed their mind in their ODBC headers when they > introduced a SQLLEN type. If you get isql running in 64bit mode and run > the help mytable command it will output what your driver thinks the > column types are and if you post that here and on the unixODBC list we > might be able to help further. However, at this point, reporting this as > a problem in DBD::ODBC is a little premature. > > Martin

Hi Martin, I first contacted TimesTen team and they replied saying this could be an issue with DBD::ODBC. They said DBD::ODBC might work fine on 32 bit but not updated to work fine on 64 bit. C data types have different size between 32 and 64 bit which could lead to issues with handling date field. So, I opened this bug.

Tue May 20 05:51:04 2008 bohica [...] ntlworld.com - Correspondence added

From:

martin.evans [...] easysoft.com

On Tue May 20 03:20:17 2008, lisha wrote: Show quoted text

> On Mon May 19 09:33:56 2008, MJEVANS wrote:

> > On Mon May 19 08:32:44 2008, lisha wrote:

> > > Hi, > > > I use TimesTen 7.0.3 on RHEL 4.1 64 bit. I have a perl program which > > > uses DBD::ODBC to access TimesTen. > > > > > > I have a table which has varchar fields and a date field. When I do a > > > select on the date field, I get the following error > > > > > > DBD::ODBC::st fetchrow_arrayref failed: [unixODBC][TimesTen][TimesTen > > > 7.0.3.0.0 CLIENT]Numeric value out of range (SQL-22003) > > > > > > > > > The same program works well with 32 bit TimesTen client and perl > > > installation on the same box. The issue is only with 64 bit > > > installation. I did a DBI trace and noted only difference between the > > > two: field type recognition > > > > > > 32 bit prog (working) > > > col 1: TIMESTAMP (93) len= 16 disp= 21, prec= 20 scale=0 > > > col 1: 'CHANGED_TIME' sqltype=TIMESTAMP, ctype=SQL_C_CHAR, maxlen=21, > > > (dp = 0, cp = 0) > > > > > > 64 bit prog (erroring out) > > > now using col 1: type = unknown (0), len = 16, display size = 16,

prec = Show quoted text

> > > 20, scale = 0 > > > > > > Can someone help in solving the issue? > > > > > > Thanks

> > > > You need to verify: > > > > o 64bit timesten driver was built with the same compile line as the > > unixODBC driver you are using as there are various ways of building ODBC > > for 64bit platforms. > > > > o you have the latest unixODBC driver manager (best advice for this is > > on the unixodbc lists where I see you posted this problem first). > > > > o that timesten is returning the right column types for your query in > > 64bit mode. You can do this with unixODBC's isql and "help mytable". > > > > There are lots of compatibility issues with 64bit platforms and ODBC > > because Microsoft changed their mind in their ODBC headers when they > > introduced a SQLLEN type. If you get isql running in 64bit mode and run > > the help mytable command it will output what your driver thinks the > > column types are and if you post that here and on the unixODBC list we > > might be able to help further. However, at this point, reporting this as > > a problem in DBD::ODBC is a little premature. > > > > Martin

> > Hi Martin, > I first contacted TimesTen team and they replied saying this could be > an issue with DBD::ODBC. They said DBD::ODBC might work fine on 32 > bit but not updated to work fine on 64 bit.

Well I sincerely hope you are not paying good money for support from TimesTen if this is all they said and did to help you. DBD::ODBC is confirmed to work on 64 bit platforms from version 1.15. If you or TimesTen had read the Changes file you can see that. Show quoted text

> C data types have different size > between 32 and 64 bit which could lead to issues with handling date > field. So, I opened this bug.

But if you look at the output you sent it looks as though the column type was not seen correctly. Run isql to you DSN and type in "help mytable" where mytable is your table name with the column that is not recognised. Post the output here and on unixODBC list and we can look at it further. Martin -- Martin J. Evans Wetherby, UK

Tue May 20 08:12:01 2008 libithashanmugam [...] yahoo.com - Correspondence added

From:

libithashanmugam [...] yahoo.com

On Mon May 19 09:33:56 2008, MJEVANS wrote: Show quoted text

> On Mon May 19 08:32:44 2008, lisha wrote:

> > Hi, > > I use TimesTen 7.0.3 on RHEL 4.1 64 bit. I have a perl program which > > uses DBD::ODBC to access TimesTen. > > > > I have a table which has varchar fields and a date field. When I do a > > select on the date field, I get the following error > > > > DBD::ODBC::st fetchrow_arrayref failed: [unixODBC][TimesTen][TimesTen > > 7.0.3.0.0 CLIENT]Numeric value out of range (SQL-22003) > > > > > > The same program works well with 32 bit TimesTen client and perl > > installation on the same box. The issue is only with 64 bit > > installation. I did a DBI trace and noted only difference between the > > two: field type recognition > > > > 32 bit prog (working) > > col 1: TIMESTAMP (93) len= 16 disp= 21, prec= 20 scale=0 > > col 1: 'CHANGED_TIME' sqltype=TIMESTAMP, ctype=SQL_C_CHAR, maxlen=21, > > (dp = 0, cp = 0) > > > > 64 bit prog (erroring out) > > now using col 1: type = unknown (0), len = 16, display size = 16, prec = > > 20, scale = 0 > > > > Can someone help in solving the issue? > > > > Thanks

> > You need to verify:

Hi Martin, Show quoted text

> > o 64bit timesten driver was built with the same compile line as the > unixODBC driver you are using as there are various ways of building ODBC > for 64bit platforms.

I checked this. They seem to be use same compile options. Show quoted text

> o you have the latest unixODBC driver manager (best advice for this is > on the unixodbc lists where I see you posted this problem first). >

I have checked both unixODBC 2.2.11 and 2.2.12. Both give same results. Show quoted text

> o that timesten is returning the right column types for your query in > 64bit mode. You can do this with unixODBC's isql and "help mytable".

help mytable displays just the headers and nothing followed by the message "SQLRowCount returns -1". I don't get the table information. Show quoted text

> > There are lots of compatibility issues with 64bit platforms and ODBC > because Microsoft changed their mind in their ODBC headers when they > introduced a SQLLEN type. If you get isql running in 64bit mode and run > the help mytable command it will output what your driver thinks the > column types are and if you post that here and on the unixODBC list we > might be able to help further. However, at this point, reporting this as > a problem in DBD::ODBC is a little premature. >

Sorry for reporting this issue as a problem with DBD::ODBC. I opened this bug based on the reply I received from TimesTen team. Thanks

Wed May 21 04:13:34 2008 bohica [...] ntlworld.com - Correspondence added

An upgrade to unixODBC 2.2.13pre solved the problem which was the size of SQLLEN/SQLULEN.

Wed May 21 04:14:14 2008 bohica [...] ntlworld.com - Status changed from 'open' to 'resolved'