Re: Perl with MS Access Weirdness

You should use Text::CSV_XS to generate your CSV. No need to strip newlines then (assuming the other side will parse the CSV properly. Using Text::CSV_XS on both ends would seem easiest.)

You should get used to using placeholders! Interpolating variables into SQL is dangerous! You should use something like

$upd = $dbh->prepare( 'UPDATE Titoli SET Descriz = ? WHERE IDTitolo = 
+?' );
while (...) {
  ...
  $upd->execute($descriz, $title_id);
}
[download]

Jenda

Comment on Re: Perl with MS Access Weirdness Download Code

Replies are listed 'Best First'.
Re: Re: Perl with MS Access Weirdness by Cabrion (Friar) on Jan 28, 2003 at 02:07 UTC
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	[reply] [d/l] [select]
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]