[Bug] [Microsoft][ODBC Driver 17 for SQL Server]Invalid character value for cast specification

[thsmrtone1]

PHP version 8.0.28
PHP SQLSRV version 5.10.0 and 5.11.0
Microsoft ODBC Driver version msodbcsql17 -- 17.8.1.1
SQL Server version Azure SQL Database
Client operating system Mac OS 10.15.7

Table schema

create table test (
    testCol nvarchar(10)
);

Problem description

When attempting to use a statement to insert two records into a table, and values have different data types, an error is produced.

Expected behavior and actual behavior

actual: [Microsoft][ODBC Driver 17 for SQL Server]Invalid character value for cast specification
expected: (success)

Repro code or steps to reproduce

The following reproduces consistently when running sqlsrv v5.10.0 and v5.11.0

$conn = sqlsrv_connect('XXXX.database.windows.net,1433', [
    'Database' => 'XXXX',
    'CharacterSet' => 'UTF-8',
    'UID' => 'XXXX',
    'PWD' => 'XXXX',
    'ReturnDatesAsStrings' => 1,
]);

$v0 = 1000;
$stmt = sqlsrv_prepare($conn, 'INSERT INTO [test] (testCol) VALUES (?);', [&$v0]);
sqlsrv_execute($stmt); // 1000 is inserted
$v0 = 'abcd';
sqlsrv_execute($stmt);

$error = sqlsrv_errors(SQLSRV_ERR_ERRORS);
echo $error[0][2] ?? ''; // [Microsoft][ODBC Driver 17 for SQL Server]Invalid character value for cast specification

!important! Please note that the above snippet DOES NOT produce an error in sqlsrv v5.9.0. Bug must have been introduced in v5.10.0

[v-makouz]

I can reproduce this, so I'll look into what might be causing it

[v-makouz]

Update: I haven't fully figured out the exact change that caused this bug, still looking, but the reason is that it on the second insert the driver doesn't try to detect the type of the variable and uses the setting from the first insert. So '1000' is detected as BIGINT, everything is fine, then tries to insert 'abcd' as BIGINT, which doesn't work.

I suspect it binds the variable and doesn't rebind when it's a different type. I'm trying to find the change that is the culprit.

[absci]

This issue has been fixed in dev branch now.
But I suggest changing the script and passing the right type of variable. For example, quote v0, $v0 = "1000"; because the driver won't know the column type.