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

Thompson, Bill D (London) bill_d_thompson at ml.com
Thu Apr 1 07:30:42 EST 2004


James, Michael,

I've found the problem and, alas, it is in freetds.

basically the issue is nothing to do with the emptiness of the result set.
Its to do with the fact that you're executing a stored procedure, and our
handling of (yes, you guessed it james, DONEINPROC tokens)

dbresults used to (and should) ignore DONEINPROC tokens.
our current version of dbresults returns SUCCEED/FAIL when it hits a
DONEINPROC token.

here's the fix, along with a similar change to dbsqlok() which I mentioned
in passing a little while ago.

All dblib unit tests passed Solaris 2.6  SQL2K and Sybase 11.9.2

 <<dblib.diff.gz>> 
If you want to do a quick test Michael - try this:

in src/dblib/dblib.c
in function dbresults_r :
remove the offending line...

        case TDS_DONE_RESULT:
        case TDS_DONEPROC_RESULT:
-       case TDS_DONEINPROC_RESULT:
            if (!(done_flags & TDS_DONE_ERROR)) {
                if (dbproc->dbresults_state == DBRESINIT) {
                    done = 1;

regards,

Bill
> -----Original Message-----
> From:	James K. Lowden [SMTP:jklowden at schemamania.org]
> Sent:	01 April 2004 07:42
> To:	FreeTDS Development Group
> Subject:	Re: [freetds] mssql_query() returns boolean when stored
> procedure returns empty result set
> 
> On Wed, 31 Mar 2004, "Michael Sims" <michaels at crye-leike.com> wrote:
> > > 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?
> ...
> > <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>
> 
> Well, well.  That's a little bit weird.  
> 
> On the PHP side, usually one calls dbnumcols() before dbnextrow().  I
> don't think it's wrong to do it in the other order (since they're setting
> up a do-while loop).  But it's unconventional.  
> 
> (The idea is that dbresults() sets up the metadata, so you can ask about
> the columns count, their names, datatypes.  You bind your columns and ask
> if there are any rows to bother fething.  Then use dbnextrow() to fetch
> them.)
> 
> Next up is "num_fields<=0".  That's never supposed to happen, according to
> Sybase or Microsoft (and, hence, according to FreeTDS).  dbnumcols()
> should return 0 or more columns.  So, that test should never evaluate to
> true.  
> 
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dblibc/db
> c_pdc04d_3c6r.asp
> 
> I don't have time to look into it further right now, but here's our
> implementation of dbnumcols():
> 
> int
> dbnumcols(DBPROCESS * dbproc)
> {
> 	TDSRESULTINFO *resinfo;
> 	TDSSOCKET *tds;
> 
> 	tds = (TDSSOCKET *) dbproc->tds_socket;
> 	resinfo = tds->res_info;
> 	if (resinfo)
> 		return resinfo->num_cols;
> 	return 0;
> }
> 
> IOW, we return whatever's in dbproc->tds_socket->res_info->num_cols.  It's
> possible that the lower layers set that puppy to -1 at some point, and
> this code doesn't clear it.  For now, you could try changing it to:
> 
> int
> dbnumcols(DBPROCESS * dbproc)
> {
> 	TDSRESULTINFO *resinfo;
> 	TDSSOCKET *tds;
> 
> 	tds = (TDSSOCKET *) dbproc->tds_socket;
> 	resinfo = tds->res_info;
> 	if (resinfo)
> 		return (resinfo->num_cols < 0)? 0 : resinfo->num_cols;
> 	return 0;
> }
> 
> I touched on some of the underlying issues recently.  When we get CVS HEAD
> linkable for you again, we'll be in a better position to find a real fix. 
> 
> 
> HTH.
> 
> --jkl
> _______________________________________________
> FreeTDS mailing list
> FreeTDS at lists.ibiblio.org
> http://lists.ibiblio.org/mailman/listinfo/freetds

==============================================================================

If you are not an intended recipient of this e-mail, please notify
the sender, delete it and do not read, act upon, print, disclose,
copy, retain or redistribute it.

Click here for important additional terms relating to this e-mail.
     <http://www.ml.com/email_terms/>

==============================================================================
-------------- next part --------------
A non-text attachment was scrubbed...
Name: dblib.diff.gz
Type: application/octet-stream
Size: 546 bytes
Desc: not available
Url : http://lists.ibiblio.org/pipermail/freetds/attachments/20040401/060e9a53/attachment.obj 


More information about the FreeTDS mailing list