DBD:DB2 data type? problem

shemp has asked for the wisdom of the Perl Monks concerning the following question:

Hey all, I (my boss actually) has run across a problem using perl with DB2 that i have no good answer for. Running a SELECT query thats grabs some data that includes a column defined as DECIMAL(14). When the $handle->fetchrow_array() occurs, we get the following error:

DBD::DB2::st fetchrow_array failed: IBM CLI Driver DB2/SUN64 SQL0413N Overflow occured during numeric data type conversion. SQLSTATE=22003

My initial guess was that the 14 digit decimal is too big for perl, but i dont think so.

I have never encountered dealing with data retrieval errors, just with execute() errors. Any insight into dealing with this would help. I've also had some (seemingly) inconsistent behaviour with toggling the RaiseError flag on the DB connection, and trying to trap errors with eval{}.

Sorry if this sounds a bit disoriented, i feel a bit disoriented about this. If you need more info, please ask.
I have a lot of experience with DBD::MySQL, but have almost no experience with DBD::DB2 so i'm a bit lost.
Regardless, that may have nothing to do with this.

Update: Reading O'Reilly's Programming the Perl DBI, i see in Appendix B, section DBD::DB2 (page 246 in my copy), the stateemnt notice that DB2 supports numbers outside the typicalvalid range for perl numbers. This isn't a major problem because DBD::DB2 always returns all numbers as strings.
Comment on DBD:DB2 data type? problem

Replies are listed 'Best First'.
Re: DBD:DB2 data type? problem by runrig (Abbot) on Jun 17, 2004 at 22:09 UTC
I ran into a similar problem using DBD::ODBC with an Informix database. When you select, e.g., a DECIMAL(12) column, you don't get an error, but you get the wrong number. A "12.3" comes back as something like "0.000000000123" (and there is no way to know where the decimal was). The solution for me was to explicitly format the column in the SQL statement with the desired number of decimal places (in Informix it was with the TRUNC function, e.g. "select trunc(some_column, 2) from...", I don't know about DB2).	[reply]
Re: DBD:DB2 data type? problem by mpeppler (Vicar) on Jun 18, 2004 at 05:42 UTC
My guess is that this is a bug in the DBD::DB2 driver. I would post this to the dbi-users@perl.org mailing list to see what the author might have to say. Michael	[reply]