Re: Re: Perl with MS Access Weirdness

Indeed you need to use Text::CSV, but you also need to set CSV_XS to do binary interpretation. That will take care of embeded CR/LF's and other such things. Beware that some versions of Access will not export more than 65,000 rows as CSV, and there is NO warning. It just silently discards the remaining rows!

use Text::CSV_XS;
my $csv = Text::CSV_XS->new(binary=>1);
...
[download]

Building SQL through interpolation is fine as long as you escape the values first. Most, if not all, DBD drivers provide a quote function that will escape strings for you. Binding placeholders actually does the quoting for you so most people find that easier. I prefer to escape stuff myself.

$dbh->quote($some_string);
my $sql = "update some_table where some_attr = '$some_string'";
[download]

Take your pick.

Finally a cheap plug: use PostgreSQL

Comment on Re: Re: Perl with MS Access Weirdness Select or Download Code

Replies are listed 'Best First'.
Re: Re: Re: Perl with MS Access Weirdness by Jenda (Abbot) on Jan 28, 2003 at 12:59 UTC
You are right. I forgot to mention the `binary=>1` If you insist on quoting you might like this: `use Interpolation "'" => sub {"'".$db->quote($_)}; my $sql = "update some_table where some_attr = $'{$some_string}'";` [download] Actually your code should look like this: `$some_string = $dbh->quote($some_string); my $sql = "update some_table where some_attr = '$some_string'";` [download] There are two reasons for using placeholders. First is the quoting, second is speed. If you execute the same SQL command, just with different values, it's much quicker if you prepare the SQL and then just execute it with the values. The ->prepare() allows the SQL server to parse the query/command, prepare the execution plan and so forth. So by using prepare()&execute() you keep the server from having to reparse&recompile the SQL each time. Jenda P.S.: Yes, there are most probably databases that do not really support prepare(). The DBD for those will just remember the SQL, and quote and interpolate variables each time. Then there will be no preformance difference between prepare()&execute() and do().	[reply] [d/l] [select]

Replies are listed 'Best First'.

Re: Re: Re: Perl with MS Access Weirdness
by Jenda (Abbot) on Jan 28, 2003 at 12:59 UTC

You are right. I forgot to mention the binary=>1

If you insist on quoting you might like this:

use Interpolation "'" => sub {"'".$db->quote($_)};
my $sql = "update some_table where some_attr = $'{$some_string}'";
[download]

$some_string = $dbh->quote($some_string);
my $sql = "update some_table where some_attr = '$some_string'";
[download]

There are two reasons for using placeholders. First is the quoting, second is speed. If you execute the same SQL command, just with different values, it's much quicker if you prepare the SQL and then just execute it with the values. The ->prepare() allows the SQL server to parse the query/command, prepare the execution plan and so forth. So by using prepare()&execute() you keep the server from having to reparse&recompile the SQL each time.

Jenda

P.S.: Yes, there are most probably databases that do not really support prepare(). The DBD for those will just remember the SQL, and quote and interpolate variables each time. Then there will be no preformance difference between prepare()&execute() and do().

[reply]
[d/l]
[select]