Re: Can't write to DB using stored procedure

Got your point, James. Thanks.

Digging somewhat deeper, I came across the following lines in
freetds.log.  They pertain to fetching the user ID that the stored
procedure should return after inserting a new user:

<freetds.log>
ct.c:1490:ct_fetch()
ct.c:1100:ct_results()
token.c:495:tds_process_tokens(0x9cde7e8, 0xbfef2668, 0xbfef266c, 0x6914)
util.c:162:Changed query state from PENDING to READING
token.c:510:processing result tokens.  marker is  fe(DONEPROC)
token.c:2201:tds_process_end: more_results = 0
                was_cancelled = 0
                error = 0
                done_count_valid = 0
token.c:2217:tds_process_end() state set to TDS_IDLE
util.c:162:Changed query state from READING to IDLE
token.c:2232:                rows_affected = 0
util.c:110:logic error: cannot change query state from IDLE to PENDING
ct.c:1157:ct_results() process_result_tokens returned 1 (type 4053)
</freetds.log>

So there's a "logic error". Lines 2216-2221 indicate that the state is
changed from READING to IDLE on certain conditions:

<token.c>
	if (was_cancelled || (!more_results && !tds->in_cancel)) {
                tdsdump_log(TDS_DBG_FUNC, "tds_process_end() state set to TDS_IDLE\n");
                /* reset of in_cancel should must done before setting IDLE */
		tds->in_cancel = 0;
                tds_set_state(tds, TDS_IDLE);
        }
</token.c>

The remainder of the debug output, however, does not mention a cancel
packet being sent.

Any ideas on that?

Warmest,

Bodo

> Bodo Schulze wrote:
> > 
> > But while DBD::Sybase throws no error, execute() invariably returns -1,
> > meaning that the number of rows affected is unknown.
> > 
> > My client confirms that there is no problem with the underlying stored
> > procedure (to which I have no access).
> 
> I think you need another way to detect how many rows were affected.  I
> tried this:
> 
> $ TDSDUMP=dump sqsh -S$S -U$U -P$P <<< "$(printf 'create table #t(t int)
> insert #t values (1)\ngo\ncreate proc #tp as insert #t values (3)\ngo\n
> exec #tp\ngo\nselect * from #t\ngo\n')"
> (1 row affected)
> (return status = 0)
>  t          
>  -----------
>            1
>            3
> 
> (2 rows affected)
> 
> As you can see above, sqsh prints "rows affected" only for open SQL. 
> Let's look at what the server returned:
> 
> $ grep valid dump
> done_count_valid = 0 # changed databasse
> done_count_valid = 0 # create table
> done_count_valid = 1 # insert
> done_count_valid = 0 # create proc
> done_count_valid = 1 # proc's insert
> done_count_valid = 0 # return status
> done_count_valid = 1 # select
> 
> Without looking too deeply at DBD::Sybase, let's just say there's a real
> challenge -- and not much point -- in trying to return to the application
> the number of rows affected by a stored procedure.  A challenge, because
> it's in not the final DONE packet returned by the server.  Not worthwhile,
> because a stored procedure can contain many SQL statements.  What "rows
> affected" number would you like to see?   The first, last, total, or
> something else?  
> 
> ct-lib does produce a special row for return status, and IIRC DBD::Sybase
> supports both that and output parameters.   
> 
> HTH.  
> 
> --jkl