Skip to Content.
Sympa Menu

freetds - Re: [freetds] Can't write to DB using stored procedure

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: Bodo Schulze <bobesch AT letras.net>
  • To: FreeTDS Development Group <freetds AT lists.ibiblio.org>
  • Subject: Re: [freetds] Can't write to DB using stored procedure
  • Date: Mon, 18 May 2009 11:51:51 +0000

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




Archive powered by MHonArc 2.6.24.

Top of Page