Hi,
> > I came across this problem that seems to be with DBD::Pg's quote
> > function that is easily reproducible. This test script:
> >
> > my $dbh = DBI->connect("DBI:Pg:dbname=test", "postgres", "");
> > my $val = $dbh->quote(q!\'?:!);
> > print "val: $val\n";
> > my $sth = $dbh->prepare("INSERT INTO foo (a) values ($val)");
> > $sth->execute;
> >
> > produces:
> >
> > DBD::Pg::st execute failed: parameter unknown at mytest.pl line 6.
> >
> > If I use mysql, or Oracle, as a driver, it works as expected. I searched
> > through the list, and the only answer seemed to be to use placeholders,
> > which isn't really an option in my situation.
>
> Quote function is not supposed to quote ? character.
>
> You must do as follows:
> > my $dbh = DBI->connect("DBI:Pg:dbname=test", "postgres", "");
> > my $val = q!\'?:!
> > my $sth = $dbh->prepare("INSERT INTO foo (a) values (?)");
> > $sth->execute($val);
>
> (And no explicit calls to quote are necessary)
Placeholders isn't really an option in my case.
I realize quote should not quote the ? character, but the driver should
understand that question marks inside of quotes is not to be used as
placeholders, but is rather a question mark. i.e. the following works
of course:
my $sth = $dbh->prepare ('INSERT INTO foo (a) VALUES ("hello?")');
as the driver realizes that ? is not a placeholder. It's the strange
mix above that causes a problem (oddly, if I remove the :, it works
fine).
Does anyone more familiar with the DBD::Pg internals know what the
sequens is to look for that will catch this? If I remove the : or the \
from the insert above, it works fine.
Cheers,
Alex