[Sqlrelay-discussion] MySQL and BLOBs

[Devananda <kar...@ya...>]

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