Why can't a output variable be set to null?

Question

Hi,

Why is this "Variables that are initialized or updated to null , DateTime , or stream types cannot be used as output parameters." not possible? In particular, updated to null.

Answer 1

After some tests, it looks like an MSSQL int (or tinyint) output parameter can be null and the php variable will be set to 0. If the paramter is a varchar and is either not set in the SP or is set to null in the SP, then php crashes (CLI tests) (It crashes badly too, couple of beeps too and tons of funny encoding thrown out). Did not crash on Money type, but a null became 4.2439915819305E-314.

All my params set in php before the query call where setup to define the in/out, php type, sql type.

Any ideas/comments?

G.

Answer 2

In general, output parameters can be set to null. There some restrictions though with parameters declared as INOUT, because they should match the store proc requirements for input parameters. Please post a sample code and we can discuss on a concrete example.

Thank you,
Serban

---

Serban Iliescu - MSFT

Answer 3

Hi Serban,

Ok, lets discuss over some example code...

Case 1: Testing general IN and then OUT, and the stored prcedure sets the OUT param to NULL.

PHP Code

	//Execute a stored procedure
	$sql = "{call TestSqlSrv(?,?)}";
	$variables = array(
		'In' => 'one',
		'Out' => '',
	);
	$params = array(
		array(&$variables['In'], SQLSRV_PARAM_IN, SQLSRV_PHPTYPE_STRING(SQLSRV_ENC_BINARY), SQLSRV_SQLTYPE_VARCHAR(100)),
		array(&$variables['Out'], SQLSRV_PARAM_OUT, SQLSRV_PHPTYPE_STRING(SQLSRV_ENC_BINARY), SQLSRV_SQLTYPE_VARCHAR(100)),
	);
	
	//display before variables
	echo "SP Variables Defined.\n" . print_r($variables, true)."\n";	
	
	//run query
	$stmt = sqlsrv_query($conn, $sql, $params);
	if ($stmt) {
		echo "Query executed: $sql\n";
	} else {
		echo "Query failed\n" . print_r(sqlsrv_errors(), true)."\n";
	}
	
	//print the variables
	echo "SP Variables Returned.\n" . print_r($variables, true)."\n";

Sql Stored Procedure

USE [testdb]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[TestSqlSrv]
@In varchar(100), @Out varchar(100) OUTPUT
AS
BEGIN
SET NOCOUNT ON
	SET @OUT = NULL
END
GO

CLI Result:

Connected to 127.0.0.1,1433
SP Variables Defined.
Array
(
    [In] => one
    [Out] =>
)

Query executed: {call TestSqlSrv(?,?)}

You will notice the output did not print the variables from after the query. This is because CLI crashed when it tried to access them. If you set the @Out param to a varchar value, it all works normally.

Answer 4

Case 2: Same as case 1, but SP has a forced error and a try catch

PHP Code: Same as above

SQL Procedure:

USE [testdb]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[TestSqlSrv]
@In varchar(100), @Out varchar(100) OUTPUT
AS
BEGIN TRY
	INSERT INTO Temp ([Name],[Data]) VALUES (null,null);
	SET @OUT = 'Test'
END TRY
BEGIN CATCH
	SET @OUT = 'Failed'
END CATCH
GO

SQL SB Table:

USE [testdb]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Temp](
	[Name] [varchar](100) NOT NULL,
	[Data] [text] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF

CLI Output:

Connected to 127.0.0.1,1433
SP Variables Defined.
Array
(
    [In] => one
    [Out] =>
)

Query executed: {call TestSqlSrv(?,?)}
SP Variables Returned.
Array
(
    [In] => one
    [Out] =>☻♀+
                             ☺       d   ▬   ☺   ☺        ☺               <   ∟
  A
)

Notice the variables after the query has a very strange string value for Out. I have also had it randomly set a similar output variable in our stored procedures to contain the value of my php_include path! In these cases, the output variable is also always set to 100 length and sometimes contains the value as set in the SP, but still also filled to a length of 100 with random junk.

G

Answer 5

With the old mssql_* functions, the above example was always safe and the values returned where correct. Though, I do believe that nulls for strings where set to '', and for int's, set to 0.

Answer 6

I might have misunderstood your initial question. The driver handles deficiently output parameters set to NULL in a stored proc. This is a known issue that we plan to address in the near future.

Thank you,

Serban

---

Serban Iliescu - MSFT

Answer 7

Hi Serban,

Ok, do you have any idea when this is going to happen?

Also, there are two test cases above. the second deals with a none null issue. In fact the second is rather critical as most of all our Stored Procedures have the try catch.

G.

Answer 8

The version 1.1 of the driver (including the fix) will be released within the next two weeks.

 

Thank you,

Serban

---

Serban Iliescu - MSFT

Answer 9

Excellent... can't wait!

Answer 10

Please check the released version of v1.1 driver at:

 

http://www.microsoft.com/downloads/details.aspx?FamilyID=ccdf728b-1ea0-48a8-a84a-5052214caad9&displaylang=en

 

It is supposed to fix the problem reported.

 

Thank you,

Serban

---

Serban Iliescu - MSFT

Answer 11

Hi Serban,

Test Case no passes fine. Thank you.

Unfortunately test case 2 (TRY CATCH), the OUTPUT varchar still gets filled with stuff. At least the character set changed ;)

Connected to 127.0.0.1,1433

SP Variables Defined.

Array

(

    [In] => one

    [Out] =>

)

Query executed: {call TestSqlSrv(?,?)}

SP Variables Returned.

Array

(

    [In] => one

    [Out] =>  ┐┐ô☻╠+♣             °☺              ►       ♦       ☺   ♠   ☺

  á╧ô☻♥   ☺   ♠       ↔   T

)

Answer 12

Hi Serban,

My apologies, i forgot about the SET NOCOUNT ON which solves the problem with the try catch.

The new Stored Procedure is:

USE [testdb]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[TestSqlSrv]
@In varchar(100), @Out varchar(100) OUTPUT
AS
SET NOCOUNT ON
BEGIN TRY
	INSERT INTO Temp ([Name],[Data]) VALUES (null,null);
	SET @Out = 'Test'
END TRY
BEGIN CATCH
	SET @Out = 'Failed'
END CATCH
GO