MySQL, placeholders and data corruption

csxdje

I can\'t for the life of me work out what\'s going on here. I have a query with placeholders but when I prepare and execute it the data I get back is garbage. If I take the placeholder out and replace it with the literal \'id\' everything works fine! Here\'s an example:

$params=array(78); // Primary key
$sh=odbc_prepare($db,\"SELECT id,forename,surname,dob FROM person WHERE id=?\");
odbc_execute($sh,$params);

while(odbc_fetch_into($sh,$row)) {
print \"<pre>\";
var_dump($row);
print \"</pre>\";
}

Our intranet server has the following setup:
Red Hat 7.2
Apache 1.3.20 (binary distrib)
MySQL 3.23.49a (binary distrib)
MyODBC 2.50.39 (uncustomized local build)
PHP 4.1.2 (uncustomized local build)
unixODBC 2.2.0 (uncustomized local build)

The above code should (and does) fetch 1 row. When I dump the array however I get the following:

array(4) {
[0]=>
&string(2) \"o_\"
[1]=>
string(6) \"lefsd\"
[2]=>
&string(8) \"0)\'@ro%\"
[3]=>
&string(10) \"1917-11-21\"
}

The string lengths are correct but the content isn\'t. With every refresh of the page the data changes (kinda looks like a string printed from C with a broken pointer). If I replace the placeholder with the value 78 I get the following:

array(4) {
[0]=>
&string(2) \"78\"
[1]=>
string(6) \"STEVEN\"
[2]=>
string(8) \"STEPHENS\"
[3]=>
string(10) \"1917-11-21\"
}

Everything works as it should. If I run the same code on a Windows server (same setup as intranet server) but connect to the same database the problem repeats itself. If, however, I change the MyODBC driver on the Windows box to version 3.50.02 the problem goes away. But, just to confuse the problem further, if I compile version 3.50.02 of the MyODBC driver on the Linux box, the problem remains. Eh?

It\'s not the database because I\'ve tried other instances and all the tables checkout OK.

Anyone got any bright ideas cos this one\'s bakin\' my noodle.

Dan.

edwardsbc

Have your read the comments on the PHP Manual for odbc_execute?
<http://www.php.net/manual/en/function.odbc-execute.php>

From my reading of the comments I would first try this:

odbc_execute($sh,&$params);

Note the amphersand.

It seems there may some problem with parameterized queries. Do you actually need to "prepare" the statement? Have you tried something like this:

$param=78;
$query="SELECT id,forename,surname,dob FROM person WHERE id=$param";
$sh=odbc_exec($db,$query);

csxdje

Yeah, I've read all the notes, examples and everything else I can find but I couldn't find anything of any use. I posted here as a last resort on the off chance anyone else had had similar problems. The example I gave in my original post was a simplified version of what I'm trying to do but the problem is still the same. I'm using a master query to drive a detail query, thats why I'm using placeholders. This way I only have to prepare the query once rather than having the overhead on every iteration of the master query (doing N odbc_execs is slower than doing 1 odbc_prepare and N odbc_executes).

I can work around the problem using odbc_exec but the question is why does using placeholders corrupt the result?

Something I forgot to mention in my original post was that the query does actually return the correct record because the dob field (a date) is always correct. It never gets scrambled. It only seems to affect integers and varchars (or other string types). Tiz all a bit bizarre.

Dan.

edwardsbc

Well we have exhausted my knowledge on this subject. Should you ever find the problem, please post it here for future reference. I know many people do use the ODBC functions and would be interested if there is ever a fix or better work around for this problem.