Adding Perl DBI execute_for_fetch support to DBD::ODBC

In a rare moment (I hope) of stupidity last weekend when I was a little bored (bad cold stopped me doing what I really wanted to do) I looked through the DBD::ODBC TO_DO list and saw the "implement execute_array/execute_for_fetch" item which has been in that file for years. I know for sure implementing it will be a lot faster than using DBI's default methods (which effectively do a row at a time) mostly because I wrote an ODBC tutorial years ago showing how much faster binding arrays of parameters is (Easysoft ODBC-ODBC Bridge Performance White Paper) but I also knew it would be a PITA to write. The main problem is that DBD::ODBC has loads of workarounds for broken drivers and special cases.

Anyway, having started it I resolved to finish it and I had a working version (as far as I knew and not withstanding special workarounds). My final test was to run it through a test I wrote for execute_array/execute_for_fetch for DBD::ODBC in t/70execute_array.t and added to DBD::Oracle a while later (when I found DBD::Oracle failed quite a number of the tests). It failed quite a number of tests and all the failures were legitimate ones like not returning undef if any row failed and not calling set_err if any row failed. I've still no idea how to separate error from informationals in this. If anyone else ever contemplates adding execute_array/execute_for_fetch support to a DBD I strongly recommend you get a copy of t/70execute_array from DBD::ODBC or DBD::Oracle (the latter could be a little out of date sometimes) and use it as a test case. It does not use any complicated SQL and so should be easy to adapt for other DBDs.

This test code probably find its way into DBI at some stage but it would need checking for SQL syntax in particular. Currently it just uses:

create table a_table (a integer primary key, b char(20))
drop table a_table
delete from a_table
insert into a_table values(?,?)
select * from a_table
update a_table set a_column = ? where a_column = ?

Obviously, it isn't going to work for read-only DBDs like DBD::Sponge and DBD::Sys. On that point how do you know a DBD is read-only?

Perhaps we should have added some DBI tasks to GCI (#gci) (http://www.google-melange.com/gci/org/google/gci2011/tpf, http://wiki.enlightenedperl.org/gci2011) especially for testing.

My intention is to release a DBD::ODBC official dist to cpan in the next few days with various Unicode fixes in metadata methods and then start a new dev release including execute_for_fetch. If you are using execute_array or execute_for_fetch via DBI and DBD::ODBC I want to hear from you - please email me.

Martin