Re: Problems with question marks in operators (JDBC, ECPG, ...)

-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160


Andrew Dunstan wrote:
> FTR, Perl's DBD::Pg lets you do this:
> $dbh->{pg_placeholder_dollaronly} = 1; # disable ? placeholders

You can also simply escape placeholders in DBD::Pg with a backslash:

$dbh->prepare(q{SELECT * FROM mytable WHERE lseg1 \?# lseg2 AND name = ?});

Dave Cramer wrote:
> Well our solution was to use ?? but that does mean we have to do some
> extra parsing which in a perfect world wouldn't be necessary.

That's not a good solution as '??' is a perfectly valid operator. ISTR 
seeing it used somewhere in the wild, but I could be wrong.

> In that case my vote is new operators. This has been a sore point for the
> JDBC driver

Um, no, new operators is a bad idea. Question marks are used by hstore, 
json, geometry, and who knows what else. I think the onus is solely on 
JDBC to solve this problem. DBD::Pg solved it in 2008 with 
the pg_placeholder_dollaronly solution, and earlier this year by allowing 
backslashes before the question mark (because other parts of the stack were 
not able to smoothly implement pg_placeholder_dollaronly.) I recommend 
all drivers implement \? as a semi-standard workaround.

See also:
http://blog.endpoint.com/2015/01/dbdpg-escaping-placeholders-with.html

- -- 
Greg Sabino Mullane greg@turnstep.com
End Point Corporation http://www.endpoint.com/
PGP Key: 0x14964AC8 201505171212
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iEYEAREDAAYFAlVYvmQACgkQvJuQZxSWSsj8SwCdEL3f0JvSlVQERpn+KJIaILzj
GqAAni9qcZ8PLixSLmGoXEQr8tnVZ2RI
=YJfa
-----END PGP SIGNATURE-----