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
(http://www.easysoft.com/products/data_access/odbc_odbc_bridge/performance_white_paper.html)
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. 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.
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.
Martin
