FreeTDS Development Group

["James K. Lowden" <jklowden AT schemamania.org>]

On Wed, 24 Mar 2004, "Polyanovskyi, Sasha" <sasha.polyanovskyi AT oa.com.au>
wrote:
> Sorry, another question :-)

Hey, it's OK, really.  Precise bug reports help us find problems and
provide motivation to fix them.  I know a thing or two about db-lib, and I
want ours to work correctly.  :-)  

> For some reason the following two procedures work differently... The
> first one needs TWO calls to dbresults() to process columns, return
> values, and return status, whilst the second one -- needs THREE calls to
> dbresults(). 

Well, they should work the same, according to the docs.  It looks like you
found another bug.  That's two so far.  :-/

> In the second case I need to do TWO
> calls to dbresults() at the beginning (instead of the expected ONE
> call), then process columns, then do another call to dbresults(), then
> process return values...

What does each call to dbresults() return?  And dbnextrow()?  

Here's Sybase's recommended call sequence, from the dbretdata() reference
page:

while ( (result_code = dbresults(dbproc)
             != NO_MORE_RESULTS)
 {
    if (result_code == SUCCEED)
    {
        ... bind rows here ...
        while ((row_code = dbnextrow(dbproc))
                  != NO_MORE_ROWS)
        {
           ... process rows here ...
        }
        /* Now check for a return status */
        if (dbhasretstat(dbproc) == TRUE)
        {
           printf("(return status %d)\n",
                    dbretstatus(dbproc));
        }
        /* Now check for return parameter values */
        if (dbnumrets(dbproc) > 0)
        {
           ... retrieve output parameters here ...
        }
    } /* if result_code */
} /* while dbresults */

Your procedure returns only one result set, so your program should need to
call dbresults() twice: once to set up the results for processing with
dbnextrow(), and once to get NO_MORE_ROWS.  Processing the return status
and output parameters occurs unconditionally inside the dbresults() loop. 


I'm happy to try to fix this, and happier if you beat me to it.  

I'll be working with CVS HEAD, which you'll get a copy of if you use the
nightly snapshot.  

--jkl

> The difference between the procedures is the order of the first two
> statements in the procedure body
> 
> _________________________________________
> 
> CREATE PROCEDURE myProc
> (
>       @total int OUTPUT
> )
> AS
> BEGIN
>      select * from indial
> 
>      SELECT @total = COUNT(*) from service
> 
>      declare @sum int
>      set @sum = @total + 5
>      return @sum
> END
> 
> _________________________________________
> 
> 
> CREATE PROCEDURE myProc
> (
>       @total int OUTPUT
> )
> AS
> BEGIN
>      SELECT @total = COUNT(*) from service
> 
>      select * from indial
> 
>      declare @sum int
>      set @sum = @total + 5
>      return @sum
> END