So here is my setup: running apache with PHP 8.2.10 with a MariaDB 10.11.4 and trying to upgrade from adodb 5.20.14 to 5.22.6 (the newest) since the old adodb is not fully compatible with the newest PHP:
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. Old/new adodb:
[sql] => SELECT * FROM `Room` WHERE `ID` = 186
[sql] => SELECT * FROM `Room` WHERE `ID` = ?
i.e. the newer adodb object does 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.
Here is the raw adodb object for the execute command: Old adodb:
ADORecordSet_mysqli Object
(
[databaseType] => mysqli
[canSeek] => 1
[dataProvider] => native
[fields] => Array
(
[0] => 186
[ID] => 186
[1] => Agora XXXX XXX XXX
[Name] => Agora XXXX XXX XXX
[2] => 52.24337
[Latitude] => 52.24337
[3] => 6.853513
[Longitude] => 6.853513
[4] => Building Vrijhof - Theatre Agora
[Description] => Building Vrijhof - Theatre Agora
[5] =>
[PhoneNumber] =>
[6] => 26
[RoomTypeID] => 26
)
[blobSize] => 100
[sql] => SELECT * FROM `Room` WHERE `ID` = 186
[EOF] =>
[emptyTimeStamp] =>
[emptyDate] =>
[debug] =>
[timeCreated] => 0
[bind] =>
[fetchMode] => 3
[connection] => ADODB_mysqli Object
(
[databaseType] => mysqli
[dataProvider] => mysql
[hasInsertID] => 1
[hasAffectedRows] => 1
[metaTablesSQL] => SELECT
TABLE_NAME,
CASE WHEN TABLE_TYPE = 'VIEW' THEN 'V' ELSE 'T' END
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA=
[metaColumnsSQL] => SHOW COLUMNS FROM `%s`
[fmtTimeStamp] => 'Y-m-d H:i:s'
[hasLimit] => 1
[hasMoveFirst] => 1
[hasGenID] => 1
[isoDates] => 1
[sysDate] => CURDATE()
[sysTimeStamp] => NOW()
[hasTransactions] => 1
[forceNewConnect] =>
[poorAffectedRows] => 1
[clientFlags] => 0
[substr] => substring
[port] => 3306
[socket] =>
[_bindInputArray] =>
[nameQuote] => `
[optionFlags] => Array
(
[0] => Array
(
[0] => 5
[1] => 0
)
)
[arrayClass] => ADORecordSet_array_mysqli
[multiQuery] =>
[_genIDSQL] => update %s set id=LAST_INSERT_ID(id+1);
[_genSeqSQL] => create table if not exists %s (id int not null)
[_genSeqCountSQL] => select count(*) from %s
[_genSeq2SQL] => insert into %s values (%s)
[_dropSeqSQL] => drop table if exists %s
[database] => PoFDB
[host] => [redacted]
[user] => pofDBuser
[password] => not stored
[debug] => 1
[maxblobsize] => 262144
[concat_operator] => +
[length] => length
[random] => rand()
[upperCase] => upper
[fmtDate] => 'Y-m-d'
[true] => 1
[false] => 0
[replaceQuote] => \'
[charSet] =>
[metaDatabasesSQL] =>
[uniqueOrderBy] =>
[emptyDate] =>
[emptyTimeStamp] =>
[lastInsID] =>
[hasTop] =>
[readOnly] =>
[genID] => 0
[raiseErrorFn] =>
[cacheSecs] => 3600
[memCache] =>
[memCacheHost] =>
[memCachePort] => 11211
[memCacheCompress] =>
[sysUTimeStamp] =>
[noNullStrings] =>
[numCacheHits] => 0
[numCacheMisses] => 0
[pageExecuteCountRows] => 1
[uniqueSort] =>
[leftOuter] =>
[rightOuter] =>
[ansiOuter] =>
[autoRollback] =>
[fnExecute] =>
[fnCacheExecute] =>
[blobEncodeType] =>
[rsPrefix] => ADORecordSet_
[autoCommit] => 1
[transOff] => 0
[transCnt] => 0
[fetchMode] =>
[null2null] => null
[bulkBind] =>
[_oldRaiseFn] =>
[_transOK] =>
[_connectionID] => mysqli Object
(
[affected_rows] => 1
[client_info] => mysqlnd 8.0.30
[client_version] => 80030
[connect_errno] => 0
[connect_error] =>
[errno] => 0
[error] =>
[error_list] => Array
(
)
[field_count] => 7
[host_info] => [redacted]
[info] =>
[insert_id] => 0
[server_info] => 10.11.4-MariaDB-1:10.11.4+maria~ubu2204
[server_version] => 101104
[sqlstate] => 00000
[protocol_version] => 10
[thread_id] => 459819
[warning_count] => 0
)
[_errorMsg] =>
[_errorCode] =>
[_queryID] => mysqli_result Object
(
[current_field] => 0
[field_count] => 7
[lengths] => Array
(
[0] => 3
[1] => 20
[2] => 8
[3] => 8
[4] => 32
[5] => 0
[6] => 2
)
[num_rows] => 1
[type] => 0
)
[_isPersistentConnection] =>
[_evalAll] =>
[_affected] =>
[_logsql] =>
[_transmode] =>
[connectionParameters:protected] => Array
(
)
[databaseName] => PoFDB
)
[_numOfRows] => 1
[_numOfFields] => 7
[_queryID] => mysqli_result Object
(
[current_field] => 0
[field_count] => 7
[lengths] => Array
(
[0] => 3
[1] => 20
[2] => 8
[3] => 8
[4] => 32
[5] => 0
[6] => 2
)
[num_rows] => 1
[type] => 0
)
[_currentRow] => 0
[_closed] =>
[_inited] => 1
[_obj] =>
[_names] =>
[_currentPage] => -1
[_atFirstPage] =>
[_atLastPage] =>
[_lastPageNo] => -1
[_maxRecordCount] => 0
[datetime] =>
[adodbFetchMode] => 0
)
New adodb:
ADORecordSet_mysqli Object
(
[databaseType] => mysqli
[canSeek] => 1
[connection] => ADODB_mysqli Object
(
[databaseType] => mysqli
[dataProvider] => mysql
[hasInsertID] => 1
[hasAffectedRows] => 1
[metaTablesSQL] => SELECT
TABLE_NAME,
CASE WHEN TABLE_TYPE = 'VIEW' THEN 'V' ELSE 'T' END
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA=
[metaColumnsSQL] => SHOW COLUMNS FROM `%s`
[fmtTimeStamp] => 'Y-m-d H:i:s'
[hasLimit] => 1
[hasMoveFirst] => 1
[hasGenID] => 1
[isoDates] => 1
[sysDate] => CURDATE()
[sysTimeStamp] => NOW()
[hasTransactions] => 1
[forceNewConnect] =>
[poorAffectedRows] => 1
[clientFlags] => 0
[substr] => substring
[port] => 3306
[socket] =>
[_bindInputArray] =>
[nameQuote] => `
[optionFlags] => Array
(
)
[arrayClass] => ADORecordSet_array_mysqli
[multiQuery] =>
[ssl_key] =>
[ssl_cert] =>
[ssl_ca] =>
[ssl_capath] =>
[ssl_cipher] =>
[_connectionID] => mysqli Object
(
[affected_rows] => -1
[client_info] => mysqlnd 8.0.30
[client_version] => 80030
[connect_errno] => 0
[connect_error] =>
[errno] => 0
[error] =>
[error_list] => Array
(
)
[field_count] => 7
[host_info] => [redacted]
[info] =>
[insert_id] => 0
[server_info] => 10.11.4-MariaDB-1:10.11.4+maria~ubu2204
[server_version] => 101104
[sqlstate] => 00000
[protocol_version] => 10
[thread_id] => 463195
[warning_count] => 0
)
[usePreparedStatement:ADODB_mysqli:private] => 1
[useLastInsertStatement:ADODB_mysqli:private] =>
[usingBoundVariables:ADODB_mysqli:private] => 1
[statementAffectedRows:ADODB_mysqli:private] => -1
[isSelectStatement:ADODB_mysqli:private] => 1
[_genIDSQL] => update %s set id=LAST_INSERT_ID(id+1);
[_genSeqSQL] => create table if not exists %s (id int not null)
[_genSeqCountSQL] => select count(*) from %s
[_genSeq2SQL] => insert into %s values (%s)
[_dropSeqSQL] => drop table if exists %s
[database] => PoFDB
[dsnType] =>
[host] => [redacted]
[user] => pofDBuser
[password] => not stored
[debug] => 1
[maxblobsize] => 262144
[concat_operator] => +
[length] => length
[random] => rand()
[upperCase] => upper
[fmtDate] => 'Y-m-d'
[true] => 1
[false] => 0
[replaceQuote] => \'
[leftBracket] => [
[rightBracket] => ]
[charSet] =>
[metaDatabasesSQL] =>
[identitySQL] =>
[uniqueOrderBy] =>
[emptyDate] =>
[emptyTimeStamp] =>
[lastInsID] =>
[hasTop] =>
[readOnly] =>
[genID] => 0
[raiseErrorFn] =>
[cacheSecs] => 3600
[memCache] =>
[memCacheHost] =>
[memCachePort] => 11211
[memCacheCompress] =>
[memCacheOptions] => Array
(
)
[sysUTimeStamp] =>
[noNullStrings] =>
[numCacheHits] => 0
[numCacheMisses] => 0
[pageExecuteCountRows] => 1
[uniqueSort] =>
[leftOuter] =>
[rightOuter] =>
[ansiOuter] =>
[autoRollback] =>
[fnExecute] =>
[fnCacheExecute] =>
[blobEncodeType] =>
[rsPrefix] => ADORecordSet_
[autoCommit] => 1
[transOff] => 0
[transCnt] => 0
[fetchMode] =>
[null2null] => null
[bulkBind] =>
[connectStmt] =>
[_oldRaiseFn] =>
[_transOK] =>
[_errorMsg] =>
[_errorCode] => 0
[_queryID] => mysqli_result Object
(
[current_field] => 0
[field_count] => 7
[lengths] => Array
(
[0] => 0
[1] => 20
[2] => 0
[3] => 0
[4] => 32
[5] => 0
[6] => 0
)
[num_rows] => 1
[type] => 0
)
[_isPersistentConnection] =>
[evalAll] =>
[_affected] =>
[_logsql] =>
[_transmode] =>
[connectionParameters:protected] => Array
(
)
[customActualTypes] => Array
(
)
[customMetaTypes] => Array
(
)
[_metars] =>
[locale] =>
)
[_queryID] => mysqli_result Object
(
[current_field] => 0
[field_count] => 7
[lengths] => Array
(
[0] => 0
[1] => 20
[2] => 0
[3] => 0
[4] => 32
[5] => 0
[6] => 0
)
[num_rows] => 1
[type] => 0
)
[dataProvider] => native
[tableName] =>
[fields] => Array
(
[0] => 186
[ID] => 186
[1] => Agora XXXX XXX XXX
[Name] => Agora XXXX XXX XXX
[2] => 52.24337
[Latitude] => 52.24337
[3] => 6.853513
[Longitude] => 6.853513
[4] => Building Vrijhof - Theatre Agora
[Description] => Building Vrijhof - Theatre Agora
[5] =>
[PhoneNumber] =>
[6] => 26
[RoomTypeID] => 26
)
[blobSize] => 100
[sql] => SELECT * FROM `Room` WHERE `ID` = ?
[EOF] =>
[emptyTimeStamp] =>
[emptyDate] =>
[debug] =>
[timeCreated] => 0
[bind] =>
[fetchMode] => 3
[_numOfRows] => 1
[_numOfFields] => 7
[_currentRow] => 0
[_closed] =>
[_inited] => 1
[_obj] =>
[_names] =>
[rowsPerPage] =>
[_currentPage] => -1
[_atFirstPage] =>
[_atLastPage] =>
[_lastPageNo] => -1
[_maxRecordCount] => 0
[datetime] =>
[customActualTypes] =>
[customMetaTypes] =>
[insertSig] =>
[fieldObjectsCache:protected] =>
[adodbFetchMode] => 0
)