Edit report at https://bugs.php.net/bug.php?id=44049&edit=1
ID: 44049
Updated by: kalle@php.net
Reported by: morne dot olderwagen at lexisnexis dot co dot za
Summary: Stored proc query fails on NULL input
-Status: Open
+Status: Wont fix
Type: Bug
Package: MSSQL related
Operating System: windows 2003 server
PHP Version: 5.2.5
Block user comment: N
Private report: N
New Comment:
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.
Previous Comments:
------------------------------------------------------------------------
[2012-04-26 20:19:56] 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, ......)
------------------------------------------------------------------------
[2012-04-25 15:04:53] 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.
------------------------------------------------------------------------
[2008-02-05 12:57:21] 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
------------------------------------------------------------------------
--
Edit this bug report at https://bugs.php.net/bug.php?id=44049&edit=1