php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #27204 Queries fail after sp_help
Submitted: 2004-02-10 06:23 UTC Modified: 2004-03-18 17:46 UTC
From: willy at vuboys dot nl Assigned: fmk (profile)
Status: Not a bug Package: MSSQL related
PHP Version: 4CVS-2004-02-10 OS: Windows XP SP1
Private report: No CVE-ID: None
 [2004-02-10 06:23 UTC] willy at vuboys dot nl
Description:
------------
After you run mssql_query("sp_help") all following queries fail. I am using PHP4.3.4 running as ISAPI module in IIS5 on Windows XP. SQL Server 2000, version 8.00.760.

When you uncomment both lines in the reproduce script:
while (mssql_next_result($result));
mssql_free_result($result);

the script does work.


Reproduce code:
---------------
if ( ! $connect_id = mssql_connect( $sql_host,$sql_user,$sql_pass ) )
{
	die("Could not create a MSSQL connection, please check the SQL values entered");
}
	
if ( ! mssql_select_db($sql_database, $connect_id) )
{
	die("MSSQL could not locate a database called '{$VARS['sql_database']}' please check the value entered for this");
}

$result = mssql_query("SELECT @@VERSION");
$row = mssql_fetch_row($result);
echo $row[0]."<br />";

$result = mssql_query("sp_help", $connect_id);
while ($table = mssql_fetch_array($result))
{
	if (($table[2]=="user table") && (!($table[0]=="dtproperties"))) {
		echo $table[2]." | ".$table[0]."<br />";
	}
}
// while (mssql_next_result($result));
// mssql_free_result($result);

$result = mssql_query("SELECT @@VERSION");
$row = mssql_fetch_row($result);
echo $row[0]."<br />";
mssql_close($connect_id);	
?>

Expected result:
----------------
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Personal Edition on Windows NT 5.1 (Build 2600: Service Pack 1) 
user table | table 1
...
user table | table n
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Personal Edition on Windows NT 5.1 (Build 2600: Service Pack 1) 


Actual result:
--------------
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Personal Edition on Windows NT 5.1 (Build 2600: Service Pack 1) 
user table | table 1
...
user table | table n

Warning: mssql_query(): Query failed in c:\inetpub\wwwroot\testsql.php on line 33


Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2004-02-12 10:37 UTC] sniper@php.net
I don't know mssql at all..but what might that 'sp_help' query supposedly do? (some url pointing to a document explaining it is enough..) It sounds like something you're not allowed/supposed to do from a script..


 [2004-02-12 14:39 UTC] willy at vuboys dot nl
Let me explain;

sp_help returns more the one resultset. In previous versions of PHP (4.2.3 and earlier) this function worked perfectly without freeing the result or browsing to all result sets. In this version suddenly all queries after it fail.

Again uncommenting those two lines is a workaround. But surely the connection to the database should not be lost after this query. It's running a query and not fetching the result and then running a new query. Surely in this case the connection should not be lost.

Therefore I feel it is a bug.
 [2004-03-18 17:46 UTC] fmk@php.net
I have tested your code with PHP4.3.5-dev and PHP5.0.0RC1-dev with both SQL Server 7.0 and 2000 and I dont have a problem.

If a query (or stored procedure) returns more than one result set you need to process all results or release the result before you can execute another query. This is not a limitation in the PHP extension but in SQL server. As lon as there are pending results the server will not process new queries.
 
PHP Copyright © 2001-2022 The PHP Group
All rights reserved.
Last updated: Tue May 24 00:03:50 2022 UTC