I think that's a DBD::CSV issue, not a DBI::Shell one.
Tim.
On Fri, Nov 30, 2001 at 09:40:42AM +0000, [EMAIL PROTECTED] wrote:
> 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/
>
>