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

Michael Sims michaels at crye-leike.com
Thu Apr 1 12:15:39 EST 2004


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
___________________________________________




More information about the FreeTDS mailing list