Re: [Sqlrelay-discussion] MySQL and BLOBs

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

Try the 0.38pre2 pre-release, it supports native mysql binds and should
support blobs as well.  I also added a fix for non-native blob binds to
escape null's, so that should be fixed too.

Dave
dav...@fi...

On Mon, 2006-06-12 at 15:24 -0700, Devananda wrote:
> Dear list,
> 
> A project I am currently working on requires that uploaded files be 
> stored in MySQL for various purposes. Our application servers will be 
> using SQL Relay to connect to MySQL 5.0. However, in testing, I have 
> been unable to get SQL Relay to bind binary values to a prepared statement.
> 
> I've been combing through the SQL Relay source, and it looks like there 
> _should_ be definitions for the ::inputBind* functions in 
> src/connections/mysql/mysqlconnection.C (since there are definitions for 
> those functions in the oracle and postgres connectors), but there are none.
> 
> In any case, here's a simple test case which fails for me:
> 
> -------
> 
> mysql> CREATE TABLE bin_test (id int(11) NOT NULL auto_increment, val 
> mediumblob NOT NULL, PRIMARY KEY  (id) ) ;
> 
> -------
> 
> #!/usr/local/bin/php
> <?php
> 
> $data="ab\x00de\x00";
> 
> # acquire a connection handle
> $CON = 
> sqlrcon_alloc("localhost",'','/tmp/sqlr-HMS','sqlruser','sqlrpass',0,1);
> if (!$CON) {
>     print "failed to get connection handle!\n";
>     exit(1);
> }
> 
> # acquire a cursor
> $CUR = sqlrcur_alloc($CON);
> if (!$CUR) {
>     print "failed to get cursor!\n";
>     exit(1);
> }
> 
> sqlrcur_prepareQuery($CUR, 'INSERT INTO test.bin_test (val) VALUES 
> (:data)');
> if (!sqlrcur_inputBindBlob($CUR, 'data', $data, strlen($data))) {
>     print "inputBindBlob failed\n";
>     exit(1);
> }
> 
> if (!sqlrcur_executeQuery($CUR)) {
>     print "execute failed. Error: " . sqlrcur_errorMessage($CUR) . "\n";
>     exit(1);
> }
> 
> sqlrcur_free($CUR);
> sqlrcon_free($CON);
> 
> exit(0);
> 
> ?>
> 
> ---------
> 
> When run, I get the following error message: "execute failed. Error: 
> Column count doesn't match value count at row 1". Having started 
> sqlrelay in debug mode, I see the following in the connection's log file 
> (extra whitespace removed)
> > new query
> > handling query...
> > getting query...
> > querylength:
> > 46
> > query:
> > INSERT INTO test.bin_test (val) VALUES (:data)
> > getting query succeeded
> > getting input binds...
> > :data
> > BLOB
> > done getting input binds
> > getting output binds...
> > done getting output binds
> > getting send column info...
> > send column info
> > done getting send column info...
> > processing query...
> > preparing/executing...
> > commit or rollback check...
> > done with commit or rollback check
> > processing query failed
> > done processing query
> > handling error...
> > returning error...
> > failed to handle query: error
> > done returning error
> > done handling error...
> 
> When I alter the PHP code to call "sqlrcur_inputBind" instead of 
> "sqlrcur_inputBindBlob", the query is successfully run, but the string 
> is terminated at the first \x00 -- as expected for a C string.
> 
> Have I done something obviously wrong, or is the MySQL connector lacking 
> this functionality?
> 
> 
> Thanks in advance,
> Devananda vdv
> 
> 
> _______________________________________________
> Sqlrelay-discussion mailing list
> Sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion
>