Skip to Content.
Sympa Menu

freetds - RE: [freetds] mssql_query() returns boolean when stored procedurereturns 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 stored procedurereturns empty result set
  • Date: Wed, 31 Mar 2004 22:25:33 -0600

Hi,

Thanks for the prompt reply...

James K. Lowden wrote:
>> I've compiled the current stable version of FreeTDS (0.62.1) [1],
>> along with PHP 4.3.3 using the --with-sybase option [2]. I'm
>> noticing a problem with the return value of PHP's mssql_query()
>> function when the query calls a stored procedure that returns an
>> empty result set (0 rows). Normally the return value of this
>> function should be a result resource, but in this case the function
>> is returning a boolean true.
>
> Based on what you found, and on my knowledge of our db-lib
> implementation, it's very hard to see how (or if) this is caused by
> FreeTDS. The --with-mssql extension resolves to the very same db-lib
> call, and works correctly. Perhaps the older with-sybase handling
> expects dbresults() to behave a little differently?

Ok, I know next to nothing about C, but here's what I can figure out by
digging into the differences between ext/mssql/php_mssql.c and
ext/sybase/php_sybase_db.c. Both of them call dbcmd(), then dbsqlexec(),
then dbresults(). After that they differ slightly:

<quote php_mssql.c's mssql_query()>
/* Skip results not returning any columns */
while ((num_fields = dbnumcols(mssql_ptr->link)) <= 0 && retvalue ==
SUCCEED) {
retvalue = dbresults(mssql_ptr->link);
}
if ((num_fields = dbnumcols(mssql_ptr->link)) <= 0) {
RETURN_TRUE;
}
</quote>

<quote php_sybase_db.c's sybase_query()>
/* The following is more or less the equivalent of mysql_store_result().
* fetch all rows from the server into the row buffer, thus:
* 1) Being able to fire up another query without explicitly reading all
rows
* 2) Having numrows accessible
*/

retvalue=dbnextrow(sybase_ptr->link);

if (retvalue==FAIL) {
RETURN_FALSE;
}

num_fields = dbnumcols(sybase_ptr->link);
if (num_fields<=0) {
RETURN_TRUE;
}
</quote>

It seems to me that in the sybase extension the RETURN_TRUE macro is being
called while it is being skipped in the mssql extension. I'm sure there's a
subtle distinction here, but it's beyond my ability to grasp. Hopefully
I've snipped out the correct sections.

I believe I failed to mention this in my original post, but this behavior
(the boolean true return value) only occurs if the query is a stored
procedure that returns zero rows. If it is a plain old select statement it
behaves normally...

> You will find 0.62.2 in our current directory (next to the snapshot).
> I haven't officially announced it yet, but as no one's complained, I
> guess it will become official soon.

FWIW, I tested that version as well and it yielded the same results.

> I don't have any opinion about PHP, because I don't use it. I can say
> that our ct-lib is much further along, almost unrecognizably changed,
> since 0.53. It's used by DBD::Sybase (Perl) with pretty good success.

It looks like PHP's sybase-ct extension is getting a lot of attention too
(the latest 4.3.5 includes some enhancements), so I definitely feel that's a
viable option know that I have a little more information.

> I agree. I'm not saying it's a PHP bug; I wouldn't be surprised if it
> turned out to be some way in which what we're doing is slightly
> different than what Sybase's db-lib does. Someone will have to look
> at the PHP code to see why it thinks it should return a boolean when
> there are no results.

These links may help (both from PHP 4.3.5, which I just verified still has
this issue). The first one is the Sybase extension, which has the
unexpected behavior, and the second one is the MS-SQL extension, which works
as expected:

http://cvs.php.net/co.php/php-src/ext/sybase/php_sybase_db.c?r=1.38.2.15
http://cvs.php.net/co.php/php-src/ext/mssql/php_mssql.c?r=1.86.2.29

>> So, does anyone have an recommendations on what I should do?
>
> I'm going to leave that to the PHP experts here, except to tell you
> that many people successfully use the msssql extension.

Good to hear, I'm strongly leaning towards using it instead.

>> I can also provide additional information, including
>> sample scripts, if required.
>
> If you want to add a sample script to our samples/ directory, or
> extend what's there, I'll be happy to include it. Eventually, we
> really should have a PHP regression test.

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.

>> [1] Configured
>> with: --with-tdsver=4.2 --enable-msdblib --enable-dbmfix
>> --with-gnu-ld --ena ble-shared --enable-static
>
> --enable-dbmfix has no effect anymore.

Thanks, noted.

> Nice to hear from a long time happy user. Too bad you had to write.
> ;-)

Nice to get a response back so quickly. I had two helpful responses within
an hour of my original post. That's much better than any of the other
mailing lists I'm on. :) Thanks again...

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





Archive powered by MHonArc 2.6.24.

Top of Page