From: muratyaman at gmail dot com
Operating system: Win XP Pro SP2
PHP version: 5.1.2RC1
PHP Bug Type: MSSQL related
Bug description: mssql connection is lost after mssql_query (with bigint)
Description:
------------
Hi,
I am disappointed about the way PHP communicates with MS SQL Server and
handles queries. I costed me more than a week to find out this. Basically,
it is related to BIGINT data types, I guess.
Here is the test case:
Have a simple table including a field of type bigint.
Have a procedure reading data from it, returning an output field of type
bigint and a resultset on the fly.
Have a simple SQL statement to declare a temp variable of type bigint,
calling this procedure and getting the output.
Use this through mssql_query() function.
Have another simle select query.
I expect 2 resultsets from the 1st query (2 values output) and another
from the 2nd query.
But I have 1 resultset from the 1st query (1 value)
and a failure for a correct SQL statement.
Because it is understood that connection is lost somehow during the 1st
query without any error message.
If you change every BIGINT to INT it works fine.
However, I do NOT think this is the solution.
Reproduce code:
---------------
--sql statements to prepare database
CREATE TABLE [T1] (
[id] bigint NOT NULL,
[name] nvarchar(50) NOT NULL,
PRIMARY KEY CLUSTERED ([id])
)
GO
INSERT INTO [T1] ([id], [name])
VALUES
(1, 'abc')
GO
INSERT INTO [T1] ([id], [name])
VALUES
(2, 'def')
GO
CREATE PROCEDURE my_proc
@output1 bigint output
AS
BEGIN
--set the value
select top 1
@output1 = id
from t1
--return recordset for php
select @output1 as MY_BIG_INT_output1
END
GO
--end of sql
<?php
//PHP file:
if($dbh = mssql_connect('host\sqlserver', 'dba', 'pwd')){
echo 'Host connected<br>';
if(mssql_select_db('mydb')){
echo 'Database selected!<br>';
$input1=2;
$sql ="
DECLARE @o BIGINT
EXECUTE my_proc @o output
SELECT @o as output2
"
;
echo 'Running SQL:<br><pre>'.$sql.'</pre>';
//if there is an emp record
// there will be 3 result sets: emp, return_value, output1
//else
// there will be 2 result sets: return_value, output1
if($rs = mssql_query($sql)){
do{
while($row = mssql_fetch_assoc($rs)){
echo '<pre>'.print_r($row,true).'</pre>';
}
} while(mssql_next_result($rs));
mssql_free_result($rs);
}else{
echo 'Error: '.mssql_get_last_message().'<br>';
}
//try another query
$sql2='SELECT TOP 1 * FROM t1';
echo 'Running SQL:<br><pre>'.$sql2.'</pre>';
if($rs2 = mssql_query($sql2)){
while($row2 = mssql_fetch_assoc($rs2)){
echo '<pre>'.print_r($row2,true).'</pre>';
}
mssql_free_result($rs2);
}else{
echo 'Error: '.mssql_get_last_message().'<br>';
}
}else{
echo 'Database selection failed!';
}//end if select db
mssql_close($dbh);
}else{
echo 'Host connection failed!';
}//end if connection
//end of PHP file
?>
Expected result:
----------------
Host connected
Database selected!
Running SQL:
DECLARE @o bigint
EXECUTE my_proc @o output
SELECT @o as output2
Array
(
[MY_BIG_INT_output1] => 1
)
Array
(
[output2] => 1
)
Running SQL:
SELECT TOP 1 * FROM t1
Array
(
[id] => 1
[name] => abc
)
//// or an error message/warning if a particular datatype is not
supported, etc.
Actual result:
--------------
Host connected
Database selected!
Running SQL:
DECLARE @o bigint
EXECUTE my_proc @o output
SELECT @o as output2
Array
(
[MY_BIG_INT_output1] => 1
)
Running SQL:
SELECT TOP 1 * FROM t1
Warning: mssql_query() [function.mssql-query]: Unable to set query in
...test_proc.php on line 34
Error:
--
Edit bug report at http://bugs.php.net/?id=35884&edit=1
--
Try a CVS snapshot (PHP 4.4):
http://bugs.php.net/fix.php?id=35884&r=trysnapshot44
Try a CVS snapshot (PHP 5.1):
http://bugs.php.net/fix.php?id=35884&r=trysnapshot51
Try a CVS snapshot (PHP 6.0):
http://bugs.php.net/fix.php?id=35884&r=trysnapshot60
Fixed in CVS: http://bugs.php.net/fix.php?id=35884&r=fixedcvs
Fixed in release:
http://bugs.php.net/fix.php?id=35884&r=alreadyfixed
Need backtrace: http://bugs.php.net/fix.php?id=35884&r=needtrace
Need Reproduce Script: http://bugs.php.net/fix.php?id=35884&r=needscript
Try newer version: http://bugs.php.net/fix.php?id=35884&r=oldversion
Not developer issue: http://bugs.php.net/fix.php?id=35884&r=support
Expected behavior: http://bugs.php.net/fix.php?id=35884&r=notwrong
Not enough info:
http://bugs.php.net/fix.php?id=35884&r=notenoughinfo
Submitted twice:
http://bugs.php.net/fix.php?id=35884&r=submittedtwice
register_globals: http://bugs.php.net/fix.php?id=35884&r=globals
PHP 3 support discontinued: http://bugs.php.net/fix.php?id=35884&r=php3
Daylight Savings: http://bugs.php.net/fix.php?id=35884&r=dst
IIS Stability: http://bugs.php.net/fix.php?id=35884&r=isapi
Install GNU Sed: http://bugs.php.net/fix.php?id=35884&r=gnused
Floating point limitations: http://bugs.php.net/fix.php?id=35884&r=float
No Zend Extensions: http://bugs.php.net/fix.php?id=35884&r=nozend
MySQL Configuration Error: http://bugs.php.net/fix.php?id=35884&r=mysqlcfg
