php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #44049 Stored proc query fails on NULL input
Submitted: 2008-02-05 12:57 UTC Modified: -
Votes:21
Avg. Score:2.7 ± 1.9
Reproduced:7 of 9 (77.8%)
Same Version:1 (14.3%)
Same OS:1 (14.3%)
From: morne dot olderwagen at lexisnexis dot co dot za Assigned:
Status: Open Package: MSSQL related
PHP Version: 5.2.5 OS: windows 2003 server
Private report: No CVE-ID:
Have you experienced this issue?
Rate the importance of this bug to you:

 [2008-02-05 12:57 UTC] morne dot olderwagen at lexisnexis dot co dot za
Description:
------------
I have a Stored Procedure that returns 4 result sets. The proc selects data and inserts into a temp table and then reads back, etc. The temp table is set up to allow null values, but I get this:
PHP Warning: mssql_query() [function.mssql-query]: message: Cannot insert the value NULL into column 'PhysAddressLine2', table 'tempdb.dbo.#TempRandom_____________________000000000003'; column does not allow nulls. INSERT fails. (severity 16) in ...

I have tested the proc in MSSQL Query Analyser and also tested the script in ASP.NET, both worked fine.

Reproduce code:
---------------
$sql = "exec sp_dosearch 0,2,7,2,5";
if($qsql = mssql_query($sql, $link)) {
	echo "<h3>OK</h3>";
	 /*do { 
		   while($row = mssql_fetch_row($qsql)) {
		   
		   }
	   } while (mssql_next_result($qsql)); */
} else {
	echo"<h3>OWNED</h3>";
}

/* also tried */

$query = mssql_init("sp_dosearch", $link);
$SortOrder 	= 0;
$PASelectType	= 2;
$PASelectID	= 7;
$GeoSelectType	= 2;
$GeoSelectID	= 5;

mssql_bind($query, "@SortOrder", $SortOrder, SQLINT2);
mssql_bind($query, "@PASelectType", $PASelectType, SQLINT2);
mssql_bind($query, "@PASelectID", $PASelectID, SQLINT2);
mssql_bind($query, "@GeoSelectType", $GeoSelectType, SQLINT2);
mssql_bind($query, "@GeoSelectID", $GeoSelectID, SQLINT2);

if($res = mssql_execute($query)) {
	echo "OK";
} else {
	echo "OWNED";
}

Expected result:
----------------
OK

Actual result:
--------------
OWNED

Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2012-04-25 15:04 UTC] acb0038 at auburn dot edu
I tried a lot of stuff with SET ANSI_NULLS and even explicitly defining every 
column to allow nulls, but to no avail. Really strange that I exactly say to 
allow nulls for the column, and it tells me exactly that the column does not 
allow nulls. I'm using PHP 5.3.2 on Ubuntu 4.14, Apache 2.2.14, FreeTDS for mssql 
library, connecting to SQL Server 2008 R2. The procedure works perfectly through 
command-line FreeTDS on my Mac, Visual Studio, and ASP.NET on a FrontPage server. 
It's inexplicably buggy with SquirrelSQL, which uses a JDBC driver from 
Microsoft.
 [2012-04-26 20:19 UTC] acb0038 at auburn dot edu
Update: constraining to allow NULLs on every column did work for me. I must have 
missed a couple when I tried it first. ANSI_NULLS seems to be ignored, but make 
your table like this:

CREATE TABLE #temptable (Field1 varchar(50) NULL, Field2 int NULL, Field3 bit 
NULL, ......)
 
PHP Copyright © 2001-2015 The PHP Group
All rights reserved.
Last updated: Sun May 03 22:01:34 2015 UTC