|  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: 2016-10-15 23:12 UTC
Avg. Score:2.8 ± 1.9
Reproduced:9 of 11 (81.8%)
Same Version:3 (33.3%)
Same OS:3 (33.3%)
From: morne dot olderwagen at lexisnexis dot co dot za Assigned:
Status: Wont fix Package: MSSQL related
PHP Version: 5.2.5 OS: windows 2003 server
Private report: No CVE-ID: None
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
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 {

/* 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:

Actual result:


Add a Patch

Pull Requests

Add a Pull Request


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 
 [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, ......)
 [2016-10-15 23:12 UTC]
-Status: Open +Status: Wont fix
 [2016-10-15 23:12 UTC]
With MSSQL being removed from PHP as of PHP7.0, and ext/mssql not having a maintainer, I'm gonna close this report as a Won't fix, until maybe one day it will find a new maintainer.

Alternatively you can use sqlsrv from Microsoft if you are on Windows, or pdo_dblib if you are on Unix.
PHP Copyright © 2001-2022 The PHP Group
All rights reserved.
Last updated: Sat Jun 25 14:05:46 2022 UTC