Versions: DBI 1.20 and DBD::CSV 0.1027
I've discovered that the dbish has a problem with parsing the SQL for
DBD::CSV databases because the parser uses a different dialect of SQL.
In particular, the quote escaping mechanisms are incompatible with ANSI SQL.
What this means in real-world terms is that the same dbish "script"
cannot be reliably used between different (CSV and non-CSV) databases.
I discovered this because I use it to create SQL that will be executed
against the database of choice, and then redirect that into the dbish
perl script.
In practice, statements like:
INSERT INTO table_foo VALUES ('key', 'mis''quoted value')
will work against Oracle and other DBD drivers that use ANSI SQL parsers.
The DBD::CSV driver, however, uses a different parser and expects quotes to
be escaped with a backslash, not doubled quote characters.
So I fixed the problem. My patch basically converts doubled quotes ('')
into backslash-quote (\') and _very simply_ avoiding 'empty' fields -
doubled quotes bounded by commas. That should catch most of the cases
without resorting to exotic quote-matching regexen. YMMV.
Here's the patch:
diff Shell.pm DBI-1.20/lib/DBI/Shell.pm
*** Shell.pm Thu Nov 29 13:44:42 2001
--- DBI-1.20/lib/DBI/Shell.pm Fri Aug 24 17:11:05 2001
***************
*** 360,365 ****
--- 360,367 ----
/x) {
my ($stmt, $cmd, $args_string, $output) = ($1, $2, $3, $4||'');
+ $stmt =~ s/([^,]\s*)''(\s*[^,])/$1\\'$2/g
+ if $sh->{dbh}->{Driver}->{Name} eq 'CSV';
$sh->{current_buffer} .= "$stmt\n" if length $stmt;
$cmd = 'go' if $cmd eq '';
---------------------------------------------
Valuenet Web Mail.
http://www.valuenet.net/
