Edit report at https://bugs.php.net/bug.php?id=44049&edit=1
ID: 44049
Comment by: acb0038 at auburn dot edu
Reported by: morne dot olderwagen at lexisnexis dot co dot za
Summary: Stored proc query fails on NULL input
Status: Open
Type: Bug
Package: MSSQL related
Operating System: windows 2003 server
PHP Version: 5.2.5
Block user comment: N
Private report: N
New Comment:
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, ......)
Previous Comments:
------------------------------------------------------------------------
[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
