bind

[fmart8]

+## Driver version or file name
+Please tell us what the PHP driver version or file name is.
+5.2.0RC1

+## SQL Server version
+Please tell us what the SQL Server version is.

• SQL Server 2012 x64

+## Client operating system
+Please tell us what oprating system the client program is running on.
+Windows 10 64 bits

+## PHP version
+Please tell us which version of PHP you are running.
+7.2.2 64bits Non Thread Safe

+## Microsoft ODBC Driver version
+Please tell us which version of the Microsoft ODBC Driver you are using.
+13

+## Table schema
+Please tell us the table schema
+Might not be related to a specific table. Please see script.

+## Problem description
+Please share more details with us.
+Can't get output params values from a stored prcedure; the routine returns garbage or
the script hangs.
I tried narrowing the problem down and it seams that it only happend when the procedure:

• Has a BEGIN TRY, no matter where;
• Has an INSERT INTO SELECT clause that queries a table (it doesn't happen with non-table queries, like SELECT 'value' )

Tested under:

• Windows 10;
• SQL Server 2012 x64;
• PHP 7.2.2 64 bits.
  And:
• Windows Server 2012 R2;
• SQL Server 2012 x64;
• PHP 7.2.2 64 bits.

It did work on this machine, that runs PHP7.1:

• Windows Server 2008 Small Business Server Service Pack 2;
• SQL Server 2008;
• PHP 7.1.5 64 bits.

+## Expected behavior and actual behavior
+Please tell us what should happen and what happened instead

• $outParam should return a non-zero integer.
  Instead, it throws an error saying "An error occurred translating string for an output param to UTF-8: O parametro está incorreto." (incorrect parameter).

+## Repro code
+Please share repro code with us, or tell us how to reproduce the issue.
Please try the following script:

'...', 'ConnectionPooling' => 1, 'CharacterSet' => 'UTF-8', 'UID' => '...', 'PWD' => '...', ]; $conn = sqlsrv_connect($instance, $cfg); if( $conn === false ) { echo "Could not connect.\n"; die( print_r( sqlsrv_errors(), true)); } echo "Connected...\n"; ################################################################################ // Recreate tables and routines. sqlsrv_query( $conn, 'DROP PROCEDURE temp__exp2'); sqlsrv_query( $conn, 'DROP TABLE temp'); sqlsrv_query( $conn, 'DROP TABLE temp2'); sqlsrv_query( $conn, 'CREATE TABLE temp (correio_electronico NVARCHAR(50), nome NVARCHAR(50), telefones NVARCHAR(15), id_entidade INT)'); sqlsrv_query( $conn, 'CREATE TABLE temp2 (estado TINYINT NOT NULL DEFAULT 0)'); echo "Create routine...\n"; $sql = " CREATE PROCEDURE temp__exp2 @outParam INT OUTPUT AS BEGIN SET @outParam = 100; INSERT INTO temp (correio_electronico, nome, telefones, id_entidade) SELECT '[email protected]', 'Teste', 'xxx', 1 FROM temp2 CC WHERE CC.estado = 100 ; BEGIN TRY SET @outParam = 123; END TRY BEGIN CATCH END CATCH END"; $stmt2 = sqlsrv_query($conn, $sql); if ($stmt2 === false) { echo "Error in executing statement 2.\n"; die( print_r( sqlsrv_errors(), true)); } ################################################################################ // Call routine. echo "Call routine...\n"; $tsql_callSP = "{call temp__exp2(?, ?)}"; $outParam = 1; $params = array( array(&$outParam, SQLSRV_PARAM_OUT) ); $stmt3 = sqlsrv_query( $conn, $tsql_callSP, $params); if( $stmt3 === false ) { echo "Error in executing statement 3.\n"; print_r(sqlsrv_errors()); die("\n** ERROR! **\n"); } // Read all. while($res = sqlsrv_next_result($stmt3)); ################################################################################ // Print out param. echo "\$outParam: $outParam.\n"; // Free handles. sqlsrv_free_stmt( $stmt2); sqlsrv_free_stmt( $stmt3); sqlsrv_close( $conn); echo "Done.\n";

[fmart8]

Update: if we omit "CharacterSet' => 'UTF-8'" on the script's connection options, there's no error but returns $outParams as zero, which is wrong. Further analysis showed that it does not change it's value.

[yitam]

Hi @fmart8
Please clarify what you want to do in your stored procedure. Looks like you're trying to create some error situation?
Also, your repro code is very difficult to read. This is my attempt to reconstruct your script:

$connectionOptions = array("Database"=>$database, "UID"=>$uid, "PWD"=>$pwd, "CharacterSet"=> "UTF-8", "ConnectionPooling"=>1);
$conn = sqlsrv_connect($server, $connectionOptions); 
if( $conn === false ) { 
    echo "Could not connect.\n"; 
    die( print_r( sqlsrv_errors(), true)); 
} 
echo "Connected...\n"; 

// Recreate tables and routines. 
sqlsrv_query( $conn, 'DROP PROCEDURE temp__exp2'); 
sqlsrv_query( $conn, 'DROP TABLE temp'); 
sqlsrv_query( $conn, 'DROP TABLE temp2'); 

sqlsrv_query( $conn, 'CREATE TABLE temp (correio_electronico NVARCHAR(50), nome NVARCHAR(50), telefones NVARCHAR(15), id_entidade INT)'); 

sqlsrv_query( $conn, 'CREATE TABLE temp2 (estado TINYINT NOT NULL DEFAULT 0)'); 

echo "Create routine...\n"; 

$sql = " CREATE PROCEDURE temp__exp2 @outparam INT OUTPUT AS BEGIN SET @outparam = 100; INSERT INTO temp (correio_electronico, nome, telefones, id_entidade) SELECT '[email protected]', 'Teste', 'xxx', 1 FROM temp2 CC WHERE CC.estado = 100 ; BEGIN TRY SET @outparam = 123; END TRY BEGIN CATCH END CATCH END"; 

$stmt2 = sqlsrv_query($conn, $sql); 
if ($stmt2 === false) { 
    echo "Error in executing statement 2.\n"; 
    die( print_r( sqlsrv_errors(), true)); 
} 

// Call routine. 
echo "Call routine...\n"; 
// $tsql_callSP = "{call temp__exp2(?, ?)}"; 
$tsql_callSP = "{call temp__exp2(?)}";  // <-- did you mean this?
$outParam = 1; 
$params = array( array(&$outParam, SQLSRV_PARAM_OUT) ); 
$stmt3 = sqlsrv_query( $conn, $tsql_callSP, $params); 
if( $stmt3 === false ) { 
    echo "Error in executing statement 3.\n"; 
    print_r(sqlsrv_errors()); 
    die("\n** ERROR! **\n"); 
} 

// Read all. 
while($res = sqlsrv_next_result($stmt3)); 

// Print out param. 
echo "\$outParam: $outParam.\n"; 

// Free handles. 
sqlsrv_free_stmt( $stmt2); 
sqlsrv_free_stmt( $stmt3); 
sqlsrv_close( $conn); 
echo "Done.\n";

You provided two placeholders when calling your stored procedure, but you only expect one output parameter.

Perhaps, you meant to do this $tsql_callSP = "{call temp__exp2(?)}"; (See my question above inside the script.)

[fmart8]

Hello @yitam,
Thank you for responding.

Yes the procedure call should have a single placeholder. I started with an example from Microsoft (that, incidently, placed no "&" before the bound variable) and started adding things to try to replicate the error. The extra "?" was a mistake I made when doing minor modifications before uploading the script.
Please note that, in my system, the problem remains even with your version of the script.

About what I'm trying to do, just some store procedure that inserts a record and returns a value in a OUT variable. The only thing a little less straightforward, IMO, is that it uses a TRY CATCH block to detect duplicate records.
But the actual script I uploaded is just a simulation, in which I dropped everything except what was necessary to make the error happen.

Can you test the script on a system similar to mine?

[yitam]

Hi @fmart8

Please let me know if I have misinterpreted your stored procedure, which I've reconstructed based on your input (see below). It seems that you want to attempt the following:
(1) set @outparam = 100
(2) insert into table [temp] from a certain record in table [temp2] (I assume this record exists? Or not?)
(3) set @outparam = 123

My first response to you above was whether you intentionally made this call to procedure fail, because temp2 is supposedly empty. That's why I don't understand what you were trying to do. Is your goal to set @outparam within the try catch mechanism?

CREATE PROCEDURE temp__exp2 @outparam INT OUTPUT 
AS 
BEGIN 
    SET @outparam = 100; 
    INSERT INTO temp (correio_electronico, nome, telefones, id_entidade) 
    SELECT '[email protected]', 'Teste', 'xxx', 1 
    FROM temp2 CC 
    WHERE CC.estado = 100 ; 
    BEGIN TRY 
        SET @outparam = 123; 
    END TRY 
    BEGIN CATCH 
    END CATCH 
END

[fmart8]

@yitam,
Yes, your reconstruction is correct.
I put an updated script at:
@ http://maisqi.com/outros/bugs/bind-error-test.php.txt

Note that this is not actual production code. It's just a script to nail down the circumstances of the error.
If you read the script from the beginning you'll see that the tables and the procedures are redefined at every run. So they are always empty. But that's not the point.

The point is that that script fails in two Windows systems with PHP7.2.2 and SQL Server 2012, no matter the contents of the "temp" tables.

Thanks for you help.

[yitam]

@fmart8 I suppose you closed this issue by accident?

Thank you for the updated script, so much clearer now.

I will investigate more, but in the meantime, if I change your stored procedure as shown below, the outparam is set to 123.

Is 123 what you expected?
Or did you want to show me that if something goes wrong within the stored procedure (like in your case, select something from an empty table) then the output param is not returned correctly?

CREATE PROCEDURE temp__exp2 @outparam INT OUTPUT 
AS 
BEGIN 
SET @outparam = 100; 
INSERT INTO temp (correio_electronico, nome, telefones, id_entidade) 
SELECT '[email protected]', 'Teste', 'xxx', 1 ; 
BEGIN TRY 
    SET @outparam = 123; 
END TRY 
BEGIN CATCH 
END CATCH 
END"; 

[fmart8]

@yitam,
Yes, I closed it by accident. Does it show that it's my first issue on Github? :-)

The result should always be 123 on $outParam. But this error occurs:
"An error occurred translating string for an output param to UTF-8: O parametro está incorreto." [incorrect parameter].

The change you made defeats the purpose of the script. Those two tables (temp and temp2) and that particular INSERT SELECT are there expressly to expose the bug.
The error, as I found it, occurs in a particular combination of details. I suspect there's some kind of buffer overflow somewhere.

I'm sorry for not having being as clear as I could. I thought that the script would make spotting the bug (on affected systems) straightforward, but I sure could have done better.

Thanks for your concern, yitam.

[yitam]

Yes @fmart8 I understand what you're trying to prove now. I'll do some investigation and get back to you. Thanks for your patience.

[yitam]

Hi @fmart8

Upon some brief investigation, it's something to do with encoding, as you have already pointed out. If I changed this line

$params = array( array(&$outParam, SQLSRV_PARAM_OUT) );
to
$params = array( array(&$outParam, SQLSRV_PARAM_OUT, SQLSRV_PHPTYPE_INT) );

This is my output:

Connected...
Create routine...
Call routine...
$outParam: 1.
Done.

I want to know if this is what you expected. If not, please let me know what you expect. Thanks!

[fmart8]

@yitam,
If you look at the code, you can see that the output should always be 123 or, if something really strange happens, 100.
"1" is nowhere in the code.

Thanks.

[yitam]

Thanks @fmart8
I asked because in your script you did set $outParam = 1; before invoking your stored procedure. Since an error occurred, the initial value was not altered.
Also, did you try my suggested workaround?

[fmart8]

I updated the test script (http://maisqi.com/outros/bugs/bind-error-test.php.txt) so that it initializes $outParam to 321 before calling the stored procedure.
I ran it on one of my affected systems and it returns 0 with ConnectionPooling=UTF-8 (it isn't throwing an error right now!) and 321 without the "ConnectionPooling" connection parameter.
In neither situation the return value is to be expected.

In a nutshell, the procedure should return 100 or 123 and no other (unless the INSERT INTO fails but that's about impossible because it always inserts zero records).

[yitam]

@fmart8 I think you meant CharacterSet not ConnectionPooling :)

The problem probably lies in this line SELECT '[email protected]', 'Teste', 'xxx', 1 FROM temp2 CC WHERE CC.estado = 100; (because table temp2 is empty)

Anyway, I see your point and will continue my investigation.

[fmart8]

Hello @yitam.
Retested the script with PHP 7.2.3 and the problem remains. It really seams to be a bug on the SQL driver.
Thanks.

[david-puglielli]

@fmart8 Upon investigation, it looks like you need to specify the SQL type of the output parameter when calling sqlsrv_query(). Please change

$params = array( array(&$outParam, SQLSRV_PARAM_OUT, SQLSRV_PHPTYPE_INT) );

to

$params = array( array(&$outParam, SQLSRV_PARAM_OUT, SQLSRV_PHPTYPE_INT, SQLSRV_SQLTYPE_INT) );

and let us know if this fixes the problem. Thanks!

[fmart8]

@david-puglielli,
It works but I consider that there's still a bug. For three reasons:

1. The documentation does not state that we should always specify the types;
2. Previous versions did infer the type in this same script (remember I tested in several machines);
3. Without the types, the script fails with a rather loud error («An error occurred translating string for an output param to UTF-8: the parameter is incorrect»), not with a humble «Can't infer type».

Anyway, I updated the script to reflect your suggestion:
http://maisqi.com/outros/bugs/bind-error-test.php.txt

But please do not mark this as solved for the reasons I exposed above.

[david-puglielli]

Thank you @fmart8, you are right that it is a bug. We will continue investigating and keep you updated. Please let us know if you run into this bug again, especially if the workaround fails in any circumstance.

[yitam]

Fixed in 5.2.1-preview. Please reopen the issue if otherwise, @fmart8.