 |
Forums |
Admin  Start New Thread
By: Praveen Devarao
 RE: Quoting CURRENT TIMESTAMP in prepared stmt [ reply ]
2012-04-25 09:30
|
Hi Nathan,
>> Is there a way to turn off prepared statements across all of Rails? <<
AFAIK I am not aware of any method/ flag that will disable the prepared statement across Rails.
>>I see from the documenation in ibm_db_adapter.rb that there's a 'parameterized' parameter that can be set in config/database.yml, but since it's set to false by default, and I haven't turned it back on, I'm doubtful that it will turn off useage of prepared statements.<<
This parameter is suggested to be turned on only when you are on Rails 2.3.x and want the prepared statement support. This feature of using prepared statements was supported by only ibm_db on Rails-2.3.x, given that Rails came up with prepared statement support this parameter will not have any affect with Rails-3 and it is mandatory that it is set to the default value of false when on Rails-3.
>> I also see that AbstractAdapter (activerecord-3.1.4.lib/active_record/connection_adapters/abstract/database_statements.rb) defines a supports_statement_cache method that returns false. Sqlite, postgres and mysql all override that method to return true, but I can't see that ibm_db does so anywhere. Where does ibm_db turn on prepared statement support? <<
This needs to be set to true only if the database adapter is caching the prepared statement handles. ibm_db adapter does not cache any statment handles hence it is left to default implementation of returning false.
Let me know if you need more clarification or further info.
Thanks
Praveen
|
By: Nathan Wiebe
RE: Quoting CURRENT TIMESTAMP in prepared stmt [ reply ]
2012-04-24 02:45
|
Ok, I didn't realize that registers like CURRENT TIMESTAMP needed to part of the SQL statment and can't be passed to a prepared statement in a bind variable. That explains why I couldn't find any documentation on how to quote such values inside a prepared statement.
Is there a way to turn off prepared statements across all of Rails? I see from the documenation in ibm_db_adapter.rb that there's a 'parameterized' parameter that can be set in config/database.yml, but since it's set to false by default, and I haven't turned it back on, I'm doubtful that it will turn off useage of prepared statements. I've seen some discussions about disabeling prepared statements, such as https://gist.github.com/rails/rails/issues/5297, but setting "prepared_statements: false" in config/database.yml didn't make any difference.
I also see that AbstractAdapter (activerecord-3.1.4.lib/active_record/connection_adapters/abstract/database_statements.rb) defines a supports_statement_cache method that returns false. Sqlite, postgres and mysql all override that method to return true, but I can't see that ibm_db does so anywhere. Where does ibm_db turn on prepared statement support?
Sorry for taking so long to respond, and thanks for looking into multiple schema support.
Nathan
|
By: Praveen Devarao
RE: Quoting CURRENT TIMESTAMP in prepared stmt [ reply ]
2012-04-10 08:01
|
Hi Nathan,
>>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 was going through one of your old posts from 2008 on this. Will try finding some time and see if I could come up with a solution to handle 2 part table names. The processing on dot to split schema name and table name looks too much of code and will also need to check if it will have any other affects.
>>
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.
<<
Could you give me a use case how are you trying to get it through DB2.
Irrespective of I spell it correctly or not I see a nil in the quote_value_pstmt method passed from Activerecord and same is inserted into the database. Further we will not be able to point to CURRENT TIMESTAMP register in prepared statements (else you will see the error as you have pasted). The value (register) will need to be part of the SQL statement itself [insert into tab values(?,?,?,CURRENT TIMESTAMP)]. This will be difficult to handle in the adapter as the column data processing is done by AR.
One solution I can think of is to patch AR where the column data processing takes place to avoid conversion to nil when value is CURRENT TIMESTAMP and column type is timestamp [check method type_cast under ActiveRecord::ConnectionAdapters::Column {connection_adapters/column.rb}]. Once this is done we will need to allow addition of this value as part of the SQL and not make it a bind value for prepared statement in the module where SQL building takes place in AR [I am not sure exactly which place this happens, will need to check].
With this solution we should be good to insert special register CURRENT TIMESTAMP value too.
Let me know if this helps.
Thanks
Praveen
|
By: Nathan Wiebe
Quoting CURRENT TIMESTAMP in prepared stmt [ reply ]
2012-04-09 19:05
|
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
|
|
|
