Re: Quotes and apostrophes assistance

On 29 Oct 2007 at 10:55, Paul Lalli wrote:

On Oct 29, 12:31 pm, rvtol+n...@xxxxxxxxxxxx (Dr.Ruud) wrote:
"Beginner" schreef:

I am trying to insert a lots file paths into an SQLite table and am
having trouble with filenames with apostrophes in.

Use placeholders,

Good advice.

so prepare/execute.

irrelevant advice, nothing to do with the problem at hand. There is
no reason to alter the OP's method of using selectrow_array.

my ($val) = $dbh->selectrow_array("SELECT foo FROM bar WHERE id = ?",
undef, q{stuff'with'quotes'});

http://search.cpan.org/~timb/DBI-1.601/DBI.pm#selectrow_array

Being new to DBI (and not much of expert in perl anyway) so I can't
add much to the debate.

What I can say is that this always dies

my $statement = sprintf("SELECT foo FROM bar where path=%s",$dbh-
quote($name));
my $rows = $dbh->selectrow_array($statement) or
die "Can't selectrow_array statement=$statement: ",$DBI::errstr,"
NO:",$DBI::err,"$DBI::state\n";


or this:

my ($rows) = $dbh->selectrow_array("SELECT foo FROM bar where
path=?",undef,q($name)) or die ....snip


whereas if I am do

my $statement = sprintf("SELECT foo FROM bar where path=%s",
$dbh->quote($name));
my $sth = $dbh->prepare($statement) or
die "PREPARE:statement=$statement: ",$DBI::errstr,"\n";
$sth->execute or die "EXECUTE: statement=$statement:
",$DBI::errstr," NO:",$DBI::err,"\n";
my @rows;
while (defined(my $i = $sth->fetchrow_array)) {
push(@rows,$i);
}


It will process. What I want is to check if a fully qualified file
path exists before attempting to insert a new record so I opted for
the shorthand notation.

I didn't understand the 2 argument list in Paul's selectrow_array
either.

I am currently fighting with
$dbh->last_insert_id(undef,undef,undef,undef)

which constantly returns 0 so I can't get any further with my
original query except to say the first one always returns a code to
indicate a problem whereas the more verbose latter method will work.

BTW: SQLite 3.5.1
DBI: 1.6.1.

Any further advice appreciated.
Dp.

.

Relevant Pages