php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #35884 mssql connection is lost after mssql_query (with bigint)
Submitted: 2006-01-03 16:17 UTC Modified: 2016-10-15 23:07 UTC
Votes:8
Avg. Score:4.2 ± 1.0
Reproduced:5 of 5 (100.0%)
Same Version:1 (20.0%)
Same OS:1 (20.0%)
From: muratyaman at gmail dot com Assigned:
Status: Wont fix Package: MSSQL related
PHP Version: 5.1.2RC1 OS: Win XP Pro SP2
Private report: No CVE-ID: None
View Add Comment Developer Edit
Anyone can comment on a bug. Have a simpler test case? Does it work for you on a different platform? Let us know!
Just going to say 'Me too!'? Don't clutter the database with that please — but make sure to vote on the bug!
Your email address:
MUST BE VALID
Solve the problem:
38 - 30 = ?
Subscribe to this entry?

 
 [2006-01-03 16:17 UTC] muratyaman at gmail dot com
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: 


Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2006-01-03 16:19 UTC] tony2001@php.net
Assigned to the maintainer.
 [2006-01-03 16:25 UTC] muratyaman at gmail dot com
Sorry, my comments in PHP code before running the 1st query might be misleading (forgot to modify during submission to here). Discard them and please refer to the explanation.
Thanks.
 [2006-01-03 20:37 UTC] fmk@php.net
1) BIGINT is not a known data type for MS SQL Server 6.5. Microsoft has not updated the API (used for the mssql extension) since this version of the server.

You can use the php_dblib.dll extension this is build using FreeTDS and is more up to date.

2) You should use msql_init(), mssql_bind() and mssql_execute() functions when working with storrd procedures. mssql_query() can handle multiple results but it can not return output parameters or return values.

Please make sure to use mssql_next_result() to process additional result sets.
 [2006-01-11 01:00 UTC] php-bugs at lists dot php dot net
No feedback was provided for this bug for over a week, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
 [2012-01-31 10:48 UTC] bestdragon dot it at gmail dot com
This error still exists.
I don't know how to solve it, because sometime my query work perfectly, 
sometimes it doesn't work and print the error "Warning: mssql_query() 
[function.mssql-query]: Unable to set query in /dir/apache/html/filename.php on 
line num" after too time...

The query is:
SELECT * FROM (SELECT row_number() OVER (ORDER BY col1) AS rownum, col1, col2, 
col3, col4, col5 FROM myTable WHERE col1!='') as A WHERE rownum BETWEEN (5001) 
AND (10000)

Please fix this bug earlier! I need to use this query for work.

Thank's a lot!
 [2012-01-31 10:51 UTC] aharvey@php.net
Reopened, although I believe the normal advice nowadays is to use the sqlsrv 
driver when on Windows, which you can get from http://sqlsrvphp.codeplex.com/
 [2012-01-31 10:51 UTC] aharvey@php.net
-Status: No Feedback +Status: Re-Opened -Assigned To: fmk +Assigned To:
 [2016-10-15 23:07 UTC] kalle@php.net
-Status: Re-Opened +Status: Wont fix
 [2016-10-15 23:07 UTC] kalle@php.net
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-2024 The PHP Group
All rights reserved.
Last updated: Thu Mar 28 14:01:29 2024 UTC