Re: [Sqlrelay-discussion] [sqlrelay] propagating placeholders (question marks) with DBD::SQLRelay

[David M. <dav...@fi...>]

A long time ago most of the DB's that SQL Relay supported either used
colon-delimited bind variables (oracle) or didn't support bind variables
at all (mysql, postgresql, msql, sqlite, and others).  For DB's that
didn't natively support binds, SQL Relay would fake binds by rewriting
the query, but required colon-delimited bind variables to do so.  So, at
that time, colon-delimited bind values were required for all cases.

As such, the DBI driver would convert ?'s to colon-delimited bind
variables and pass the rewritten query to SQL Relay.

Since then mysql and postgresql have started supporting bind variables
natively and support for DB's that don't use colons natively was added. 
For those DB's, SQL Relay passes the query directly to the DB.  But the
DBI driver was never updated, it rewrites the query, then SQL Relay
passes a bad query to the DB.  Ideally what it should do is detect the
backend db type and convert the ?'s (or leave them alone) as
appropriate.  The PHP Pear DB driver does this.

In the short term, the only easy solution is to add fakebinds=yes to the
db connect string.  For example:

    <connection connectionid="mysqltest"
string="user=blah;password=blah;db=blah;fakebinds=yes" metric="1"/>

In this case, the ?'s will be converted to colon-delimited variables but
SQL Relay will rewrite the query rather than pass the binds directly to
the db.  It's less efficient, but may not be too bad.

Alternatively, you might be able to comment out the line:

    $statement =~ s/\?/":" . ++$count/eg;

in the installed SQLRelay.pm file, which should be line around 135. 
This will disable conversion of ?'s to colon-delimited variables.  The
query should be passed directly into the DB as-is with ?'s.

Give these a shot, let me know if they work for you or not.

David Muse
dav...@fi...

b-r...@ro... wrote:
> (Hmm. It's been three weeks since my last posts and there have been
> absolutely no responses. I'll try one more time with something new...)
>
> I'm using DBD::SQLRelay as it purports to be directly compatible with Perl
> DBI. This means doing my database accesses the same way as always,
> supposedly.
>
> I attempted to enter (insert or update) column values that include
> question marks ('?'). This fails, with each question mark being translated
> to :1 :2 :3 (etc). I tried every method of escaping and quoting I could
> think of, to no avail. I was using $dbh->do() directly, with and without
> placeholders.
>
> I next went back to basics: Create a simple table and a short script to
> use placeholders and insert a column that contained a '?'.
>
> If I used 'DBI' for a direct connection to the MySQL database, it worked.
>
> If I used DBD::SQLRelay (to take advantage of connection pooling), MySQL
> rejected the SQL statement due to the '?' being translated to ':1' - bad
> syntax.
>
> What gives? How can I properly use placeholders with the DBD::SQLRelay?
>
> Thanks.
>
>