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

James K. Lowden jklowden at freetds.org
Sat Oct 21 00:44:40 EDT 2006


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.
http://lists.ibiblio.org/pipermail/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



More information about the FreeTDS mailing list