[freetds] Error when using nocount, insert and print statements together.

ZIGLIO, Frediano, VF-IT Frediano.Ziglio at vodafone.com
Tue Oct 25 05:37:48 EDT 2005


> 
> > From: M A
> > Sent: Friday, October 21, 2005 2:40 PM
> > 
> > The following error is reported when a certain executing a certain
> > set of commands in a stored procedure. On the previous version it
> > would not execute all the statements but on version 0.63 it reports
> > the following error.
> >  
> >  Attempt to initiate a new SQL Server operation with 
> results pending.
> >  
> > This is using the stable releaese of freetds: version 0.63
> > 
> > To reproduce the error create the table and stored procedure on
> > SQL server using the following code:
> >  
> > create table test_api([updated_date] datetime, [id] int)
> > 
> > create proc testProc
> > AS
> > BEGIN
> > set nocount on
> > insert into test_api values(getDate(), 1)
> > insert into test_api values(getDate(), 2)
> > print 'About to call select'
> > select updated_date,id from test_api
> > print 'Did the select'
> > END
> >  
> > Now call the testProc from freetds using 
> > dbcmd("exec testProc")
> > 
> > Now call the testProc from freetds using 
> >  dbcmd("exec testProc")
> >  
> > and the error will show up.
> 
> Well done.  Thank you for that problem report.  
> 
> The error is somewhere in db-lib.  sqsh (which uses ct-lib) 
> does fine.  
> 
> I have reproduced the error with this script:
> 
> <snip>
> #! /bin/sh
> PWD=${HOME}/projects/freetds.cvs/freetds/build/PWD
> BSQLDB=/usr/local/bin/bsqldb
> 
> DROP="		drop   proc testProc"
> CREATE="	create proc testProc
> 		AS
> 		BEGIN
> 		create table #test_api([updated_date] datetime, 
> 		                       [id] int)
> 		set nocount on
> 		insert into #test_api values(getDate(), 1)
> 		insert into #test_api values(getDate(), 2)
> 		print 'About to call select'
> 		select updated_date,id from #test_api
> 		print 'Did the select'
> 		END"
> EXEC='exec testProc'
> 
> S=$(grep ^SRV ${PWD} | awk -F'=' '{print $2}')
> U=$(grep ^UID ${PWD} | awk -F'=' '{print $2}')
> P=$(grep ^PWD ${PWD} | awk -F'=' '{print $2}')
> D=$(grep ^DB  ${PWD} | awk -F'=' '{print $2}')
> 
> echo ${BSQLDB} -S $S -U $U -P $P -D testdb $@
> 
> printf "%s\nGO\n%s\nGO\n%s\n" "${DROP}" "${CREATE}" "${EXEC}" \
> | ${BSQLDB} -S $S -U $U -P $P -D testdb $@
> </snip>
> 
> As you can see from the output below, dbresults() appears not 
> to set up
> the resultset metadata.  I haven't yet been able to 
> investigate further.
> 
> 
> $ ./nocount.sh -v
> ...
> bsqldb:212: Query:
>         exec testProc
> bsqldb:186: dbsqlsend(): OK
> Msg 0, Level 0, State 1
> Server 'AC2KAMA0848', Procedure 'testProc', Line 8
>         About to call select
> bsqldb:194: dbsqlok(): OK
> bsqldb:289: calling dbresults: OK
> Result set 1
> Freeing prior allocations
> Allocating buffers
> Allocating compute buffers
> Metadata
> col     name [...]
> ------  -----[...]
> 
> Data
> Retrieving return status... none
> @@rowcount not available
> Retrieving output parameters... none
> bsqldb: Msg 20019, Level 7
> Attempt to initiate a new Adaptive Server operation with 
> results pending
> 
> --jkl
> 

Try with this patch (works for me)

diff -u -1 -0 -r1.243 dblib.c
--- src/dblib/dblib.c   3 Oct 2005 02:52:56 -0000       1.243
+++ src/dblib/dblib.c   25 Oct 2005 09:25:49 -0000
@@ -4046,23 +4046,26 @@
                        return SUCCEED;
                        break;

                case TDS_FAIL:
                        return FAIL;
                        break;

                case TDS_SUCCEED:
                        switch (result_type) {
                        case TDS_ROWFMT_RESULT:
+                               buffer_free(&dbproc->row_buf);
+                               buffer_alloc(dbproc);
+                       case TDS_COMPUTEFMT_RESULT:
+                               dbproc->dbresults_state =
_DB_RES_RESULTSET_EMPTY;
                        case TDS_COMPUTE_RESULT:
                        case TDS_ROW_RESULT:
-                       case TDS_COMPUTEFMT_RESULT:
                                tdsdump_log(TDS_DBG_FUNC, "dbsqlok()
found result token\n");
                                return SUCCEED;
                                break;
                        case TDS_DONE_RESULT:
                        case TDS_DONEPROC_RESULT:
                                if (done_flags & TDS_DONE_ERROR) {
                                        tdsdump_log(TDS_DBG_FUNC,
"dbsqlok() end status was error\n");

                                        if (done_flags &
TDS_DONE_MORE_RESULTS) {
                                                dbproc->dbresults_state
= _DB_RES_NEXT_RESULT;


Problem is that if nocount is on dbsqlok get result format but leave
state to _DB_RES_INIT do dbresults go to next result set.

freddy77


More information about the FreeTDS mailing list