Skip to Content.
Sympa Menu

freetds - RE: [freetds] mssql_query() returns boolean when storedprocedurereturns empty result set

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: "Michael Sims" <michaels AT crye-leike.com>
  • To: "FreeTDS Development Group" <freetds AT lists.ibiblio.org>
  • Subject: RE: [freetds] mssql_query() returns boolean when storedprocedurereturns empty result set
  • Date: Thu, 1 Apr 2004 11:15:39 -0600

Michael Sims wrote:
> It might be hard to come up with a concise test script for this
> particular problem, since it depends on a stored procecure that
> returns zero results. I suppose it's possible that one of the
> built-in stored procedures might be coerced into returning such a
> result set, but I'm not that much of an MS SQL guru to say. I'll
> check with my DBA here and see if I can come up with a minimal script
> to test this behavior.

Ok, this is getting really strange.

I've come up with a minimal PHP test script to exhibit the mssql_query()
return value behavior. This script requires the use of the "sa" account,
and uses a stored procedure that is built into the master database
(sp_tables). It also uses the CLI SAPI, which is normally installed in
"/usr/local/bin/php". Here is the test script:

-----BEGIN TEST SCRIPT-----
#!/usr/local/bin/php
<?
putenv('TDSDUMP=/tmp/tdsdump');

$server = 'xxx.xxx.xxx.xxx';
$username = 'sa';
$password = 'xxxxxx';
$db_name = 'master';

$dbh = mssql_connect($server, $username, $password);
mssql_select_db($db_name, $dbh);

$query = "sp_tables @table_name = 'nonexistant'";
//$query = "sp_tables @table_name = 'syscolumns'";
$result = mssql_query($query, $dbh);

echo "\$result is of type ".gettype($result)." and is $result.\n";

if (is_resource($result)) {
while ($row = mssql_fetch_object($result)) {
print_r($row);
}
}
?>
-----END TEST SCRIPT-----

Above I am using "sp_tables @table_name = 'nonexistant'" as my query, which
should return 0 rows, unless you've created a table in the master database
with the name "nonexistant". When I run the above test script, using
FreeTDS 0.62.1, and PHP 4.3.5 compiled --with-sybase, I get the following
output:

<quote>
$result is of type boolean and is 1.
</quote>

If I run the same script, again with FreeTDS 0.62.1, but with PHP 4.3.5
compiled --with-mssql, I get the following:

<quote>
$result is of type resource and is Resource id #5.
</quote>

That is consistent with what I've been experiencing. But here's where I'm
noticing more strangeness. Let's say I change the query in my test script
to one that will return a result that isn't empty, such as "sp_tables
@table_name = 'syscolumns'". When run using PHP --with-mssql, I get what I
would expect:

<quote>
$result is of type resource and is Resource id #5.
stdClass Object
(
[TABLE_QUALIFIER] => master
[TABLE_OWNER] => dbo
[TABLE_NAME] => syscolumns
[TABLE_TYPE] => SYSTEM TABLE
[REMARKS] =>
)
</quote>

When run using PHP --with-sybase, I get this, which is NOT expected:

<quote>
$result is of type boolean and is 1.
</quote>

So, in this case, even though the stored procedure returns 1 row, I'm still
getting a boolean TRUE and not a result resource.

Here are the tdsdump files for this last case (the query that returns a
NON-empty result set):

http://dev.crye-leike.com/tdsdump/tdsdump.mssql
http://dev.crye-leike.com/tdsdump/tdsdump.sybase

For kicks, I changed the query in my test script to one that I created, that
does return rows, and I got this output --with-sybase:

<quote>
$result is of type integer and is 5.
</quote>

Integer!? Using --with-mssql returns the correct results.

Hopefully this information is useful, and I haven't totally confused the
issue. If there's anything you guys want me to try let me know. I think I
will report this as a bug on bugs.php.net and see what I get, since I now
have a simple enough test case to illustrate the problem.

___________________________________________
Michael Sims
Project Analyst - Information Technology
Crye-Leike Realtors
Office: (901)758-5648 Pager: (901)769-3722
___________________________________________




  • RE: [freetds] mssql_query() returns boolean when storedprocedurereturns empty result set, Michael Sims, 04/01/2004

Archive powered by MHonArc 2.6.24.

Top of Page