| Message: 97742 |
 |
BY: Nathan Wiebe (nwiebe)
DATE: 2012-04-09 19:05
SUBJECT: Quoting CURRENT TIMESTAMP in prepared stmt Hi, while upgrading a rails app to v3.1 and ibm_db 2.5.9 I ran into some trouble with some modifications that I usually make to ibm_db.
We have rails app that runs against an existing database which contains tables in several different schema's, so I need to modify ibm_db to allow models to specify a table name as SCHEMA.TABLE. This is working fine, but it would be handy for me if this was officially supported.
I also modify ibm_db to support values like CURRENT TIMESTAMP for timestamp columns. I'm having trouble getting this to work in a rails 3.1 app. Rails 3.1 switched to using prepared statements, and ibm_db handles quoting for prepared statements differently than for dynamically prepared statements. Pre rails 3.1, I modified IBM_DBAdapter#quote(valume, column=nil) to allow values starting with CURRENT for timestamp columns to be passed unmodified to DB2, and it worked fine.
Since rails 3.1 uses prepared statements, a rails 3.1 app ends up calling IBM_DBAdapter#quote_value_for_pstmt(values, column = nil) instead of IBM_DBAdapter#quote. The same change I used to make to support CURRENT TIMESTAMP in IBM_DBAdapter#quote doesn't work in IBM_DBAdapter#quote_value_for_pstmt. I can get ibm_db to pass CURRENT TIMESTAMP through to DB2, but DB2 gives an error on the resulting statement:
warning: Statement Execute Failed: [IBM][CLI Driver] CLI0113E SQLSTATE 22007: An invalid datetime format was detected; that is, an invalid string representation or value was specified. SQLSTATE=22007 SQLCODE=-99999
I noticed something odd about this too, if I deliberately misspell CURRENT TIMESTAMP, as CURRENT BADSTAMP (or whatever), DB2 executes the statement without error and inserts a NULL for the invalid timestamp.
So prepared statements seem to require some different quoting for CURRENT TIMESTAMP values. I've done a bunch of googeling, but can't find any hints on how to quote these values in prepared statements. Can anyone point me in the right direction?
I'm using DB2 9.7.5, both client and server, on Linux.
--
Nathan Wiebe
| |
Start New Thread
Quoting CURRENT TIMESTAMP in prepared stmt