GetUpdateSQL not giving a valid query for adodb-PHP 5.22.6

[sghuisman]

Environment

• ADOdb version: 5.20.14 upgrading to 5.22.6

• Driver or Module: mysqli

• Database type and version: maria-db 10.11.4

• PHP version: 8.2.10

• Platform: linux

• [x] I have tested that the problem is reproducible in the latest release / master branch

Steps to reproduce

I'm upgrading from adodb 5.20.14 to 5.22.6, so for both versions of adodb the used php code is as follows (simplified):

$db = \DB\openDB();
$record = $db->execute("SELECT * FROM `Room` WHERE `ID` = ?", array(186));
$newrecord = array('Name' => "newname"); 
$updateSQL = $db->GetUpdateSQL($record, $newrecord); 

$record in both cases does not give an error but there is a slight difference in the output, the ADORecordSet_mysqli Object has a key called sql which is different: comparing output for the old/new adodb:

SELECT * FROM `Room` WHERE `ID` = 186
SELECT * FROM `Room` WHERE `ID` = ?

i.e. the newer adodb object seems to contain the 'raw' sql, wheras the old adodb processes the bind variables. I think this might influence the GetUpdateSQL command into giving the wrong sql (old/new):

UPDATE `Room` SET NAME='newname' WHERE `ID` = 186
UPDATE `Room` SET NAME='newname' WHERE `ID` = ?

If i change the execute command to not have bound variables by hardcoding:

$rs = $db->execute("SELECT * FROM `Room` WHERE `ID` = 186");

Then GetUpdateSQL gives the same (correct) updating sql:

UPDATE `Room` SET NAME='newname' WHERE `ID` = 186

Is this some new bug in adodb, or is there some functionality deprecated? Even with $db->debug = true activated it doesn't spawn any errors, and also no php/apache errors are logged for both versions.

Expected behavior

i would expect that GetUpdateSQL gives the same SQL query as before in the older version (without ?).

UPDATE `Room` SET NAME='newname' WHERE `ID` = 186

[dregad]

This is most likely a consequence of / regression introduced by #655.

the newer adodb object seems to contain the 'raw' sql, wheras the old adodb processes the bind variables. I think this might influence the GetUpdateSQL command into giving the wrong sql

I don't have time to test right now but you're probably right.

[mnewnham]

I don't actually see a bug here. Here's my example:

## ADOdb included
$db->debug = true;

$p1 = $db->param('p1');
$bind = array('p1'=>2);
$SQL = "SELECT * FROM table_1 WHERE recid=$p1";

$result = $db->execute($SQL,$bind);

$arr = array(
    'number_1'=>30
);

$SQL = $db->getUpdateSQL($result,$arr,true);
$db->execute($SQL,$bind);

And here's the result:

-----<hr>
(mysqli-): SELECT * FROM table_1 WHERE recid=? 
   [ (0=>'i') (1=>'2')  ]
-----<hr>
-----<hr>
(mysqli-): UPDATE table_1 SET NUMBER_1=30 WHERE recid=? 
   [ (0=>'i') (1=>'2')  ]
-----<hr>

What's important here is that the SQL statement generated by getUpdateSQL is executed with the same bind variables as the original select. For getUpdateSQL() to work with other databases, this is required behavior.

I think that because MySQL used to emulate bound variables, it was possible to execute the original statement with bound variables. then execute the subsequent generated statement without. This is because the original statement filled in the bind placeholders with the actual values. This is no longer possible, but it's just bringing MySQL in line with the other databases.

[dregad]

@sghuisman you have not provided any feedback. Based on @mnewnham's response I think this actually works as intenteded so I'm re-qualifying this as "no change required" and will close this issue. Feel free to reopen to discuss if you disagree.