Skip to Content.
Sympa Menu

freetds - Re: [freetds] dbresults() behavior different between RH7.3 and CentOS4

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: "James K. Lowden" <jklowden AT freetds.org>
  • To: FreeTDS Development Group <freetds AT lists.ibiblio.org>
  • Subject: Re: [freetds] dbresults() behavior different between RH7.3 and CentOS4
  • Date: Sat, 21 Oct 2006 00:44:40 -0400

David Chang wrote:
>
> I build a batch (e.g. 100) of stored procedure calls that simply insert
> (or update) one row of data into a table using dbfcmd(). Then, I
> submit this batch to SQL Server using dbsqlexec(). I was under the
> impression that calling dbresults() after a dbsqlexec() would iterate
> through one 'exec' at a time and tell me if that stored procedure ran
> OK. Then, after the last one in the batch finished, dbresults() would
> return NO_MORE_RESULTS (2). At least that's how Sybase DB-Library
> worked. Instead, what I see is that the first call to dbresults()
> returns NO_MORE_RESULTS (and all 100 stored procedures ran OK). If
> that's the case, how can I tell which stored procedure failed (e.g.
> unique constraint violation) out of the 100 that ran?

Hello David,

Oh, no. Not resultset processing again!

I'm sorry to say you're right. Back in July we had a long discussion on a
related topic [cf.
https://lists.ibiblio.org/sympa/arc/freetds/2006q3/020266.html]. Your
question got me thinking. How *can* he fetch the return statuses?

Reading Sybase's docs yet again
[http://manuals.sybase.com/onlinebooks/group-cnarc/cng1110e/dblib/@Generi
c__BookTextView/32257;pt=32257;uf=0] I'm convinced our implementation
isn't correct. Specifically, Sybase says:

"dbresults must also be called at least once for any stored procedure
executed in a command batch, whether or not the stored procedure returns
rows."

As a test, I wrote a little stored procedure:

create procedure i
as begin
create table #i (i int)
insert #i values (9)
end

Then I tried bsqldb on a Microsoft server:

echo exec i exec i | bsqldb [...]

and examined the dump:

$ grep dblib dump | sed -ne '/dbresults/,$p'
dblib.c:1309:dbresults(0x8055000)
dblib.c:1317:dbresults: dbresults_state is 0 (_DB_RES_INIT)
dblib.c:1339:dbresults() tds_process_tokens returned 1 (TDS_SUCCEED),
dblib.c:1339:dbresults() tds_process_tokens returned 1 (TDS_SUCCEED),
dblib.c:1366:dbresults(): dbresults_state is 0 (_DB_RES_INIT)
dblib.c:1339:dbresults() tds_process_tokens returned 1 (TDS_SUCCEED),
dblib.c:1339:dbresults() tds_process_tokens returned 1 (TDS_SUCCEED),
dblib.c:1339:dbresults() tds_process_tokens returned 1 (TDS_SUCCEED),
dblib.c:1339:dbresults() tds_process_tokens returned 1 (TDS_SUCCEED),
dblib.c:1366:dbresults(): dbresults_state is 3 (_DB_RES_NEXT_RESULT)
dblib.c:1339:dbresults() tds_process_tokens returned 2
(TDS_NO_MORE_RESULTS),
dblib.c:1297:dbresults returning 2 (NO_MORE_RESULTS)

As you can see (and I'm sure you're not suprised) dbresults returns
NO_MORE_RESULTS the first time.

The sequence in this test currently is:

dbsqlok()
dbresults() returns NO_MORE_RESULTS

It should be:

dbsqlok()
dbresults() returns SUCCEED
dbnextrow() returns NO_MORE_ROWS
dbhasretstat() returns TRUE
dbretstatus() returns the result status of invocation #1
dbresults() returns SUCCEED
dbnextrow() returns NO_MORE_ROWS
dbhasretstat() returns TRUE
dbretstatus() returns the result status of invocation #2
dbresults() returns NO_MORE_RESULTS

That means dbresults() can SUCCEED even if dbnumcols() then returns 0.
That is, there may be no resultset, no rows, not even columns. But there
will be a return status, and inside the dbresults() loop is the only place
to retrieve it. (Well, the last result status can be retrieved after
dbresults() returns NO_MORE_RESULTS.)

For the record, this applies only to batches that call multiple stored
procedures. It does *not* change how we handle rowcounts, and it does not
apply to batches with more than one SQL statement. For a batch of 100
insert statements, dbresults() will return NO_MORE_RESULTS the first time
it's called.

Thanks for the problem report. You wouldn't have a patch handy, would
you?

Regards,

--jkl




Archive powered by MHonArc 2.6.24.

Top of Page