[freetds] dblib and output parameters of stored procedures

[LacaK]

Hi list,
I am trying work with output parameters of stored procedures using
FreeTDS dblib
I am calling stored procedure using:
"declare @p int; EXEC pokus 1, @p OUTPUT"
after that call I am trying read count of output parametres using:
dbnumrets()
but it still returns 0
(same example tested with Microsoft ntwdblib returns 1)

Please see attached log file.
As you can see there is line:
dblib.c:4742:..\..\src\dblib\dblib.c 4743: logic error:
tds_process_tokens result_type 4043

Deos it explain reason why it does not work?

Thanks
-Laco.

write.c:140:tds_put_string converting 39 bytes of "declare @p int; EXEC
pokus 1, @p OUTPUT"
write.c:168:tds_put_string wrote 78 bytes
util.c:156:Changed query state from QUERYING to PENDING
net.c:741:Sending packet
0000 01 01 00 56 00 00 01 00-64 00 65 00 63 00 6c 00 |...V.... d.e.c.l.|
0010 61 00 72 00 65 00 20 00-40 00 70 00 20 00 69 00 |a.r.e. . @.p. .i.|
0020 6e 00 74 00 3b 00 20 00-45 00 58 00 45 00 43 00 |n.t.;. . E.X.E.C.|
0030 20 00 70 00 6f 00 6b 00-75 00 73 00 20 00 31 00 | .p.o.k. u.s. .1.|
0040 2c 00 20 00 40 00 70 00-20 00 4f 00 55 00 54 00 |,. . at .p. .O.U.T.|
0050 50 00 55 00 54 00 - |P.U.T.|

dblib.c:4642:dbsqlok(026B0640)
dblib.c:4672:dbsqlok() not done, calling tds_process_tokens()
token.c:540:tds_process_tokens(026B08D0, 0176F144, 0176F14C, 0x6914)
util.c:156:Changed query state from PENDING to READING
net.c:555:Received header
0000 04 01 00 1f 00 3a 01 00- |.....:..|

net.c:609:Received packet
0000 04 01 00 1f 00 3a 01 00-ff 11 00 c1 00 01 00 00 |.....:.. ........|
0010 00 79 00 00 00 00 fe 00-00 e0 00 01 00 00 00 |.y...... .......|

token.c:555:processing result tokens. marker is ff(DONEINPROC)
token.c:2339:tds_process_end: more_results = 1
was_cancelled = 0
error = 0
done_count_valid = 1
token.c:2370: rows_affected = 1
util.c:156:Changed query state from READING to PENDING
dblib.c:4672:dbsqlok() not done, calling tds_process_tokens()
token.c:540:tds_process_tokens(026B08D0, 0176F144, 0176F14C, 0x6914)
util.c:156:Changed query state from PENDING to READING
token.c:555:processing result tokens. marker is 79(RETURNSTATUS)
token.c:690:tds_process_tokens: return status is 0
util.c:156:Changed query state from READING to PENDING
dblib.c:4742:..\..\src\dblib\dblib.c 4743: logic error:
tds_process_tokens result_type 4043
dblib.c:4672:dbsqlok() not done, calling tds_process_tokens()
token.c:540:tds_process_tokens(026B08D0, 0176F144, 0176F14C, 0x6914)
util.c:156:Changed query state from PENDING to READING
token.c:555:processing result tokens. marker is fe(DONEPROC)
token.c:2339:tds_process_end: more_results = 0
was_cancelled = 0
error = 0
done_count_valid = 0
token.c:2355:tds_process_end() state set to TDS_IDLE
util.c:156:Changed query state from READING to IDLE
token.c:2370: rows_affected = 1
util.c:104:logic error: cannot change query state from IDLE to PENDING
dblib.c:4710:dbsqlok() end status is SUCCEED
dblib.c:4721:dbsqlok() end status was success
dblib.c:1671:dbresults(026B0640)
dblib.c:1677:dbresults: dbresults_state is 5 (_DB_RES_SUCCEED)
dblib.c:1660:dbresults returning 1 (SUCCEED)
dblib.c:4066:dbcmdrow(026B0640)
dblib.c:2764:dbcount(026B0640)
dblib.c:2744:dbiscount(026B0640)
dblib.c:4515:dbnumrets(026B0640)
dblib.c:4520:dbnumrets() finds 0 columns
token.c:540:tds_process_tokens(026B08D0, 0176F16C, 00000000, 0x2282a)
token.c:543:tds_process_tokens() state is COMPLETED
dblib.c:2021:dbnextrow(026B0640)
dblib.c:2034:dbnextrow() dbresults_state = 3 (_DB_RES_NEXT_RESULT)
dblib.c:2039:leaving dbnextrow() returning -2 (NO_MORE_ROWS)
dblib.c:4515:dbnumrets(026B0640)
dblib.c:4520:dbnumrets() finds 0 columns
token.c:540:tds_process_tokens(026B08D0, 0176F16C, 00000000, 0x2282a)
token.c:543:tds_process_tokens() state is COMPLETED
dblib.c:1671:dbresults(026B0640)
dblib.c:1677:dbresults: dbresults_state is 3 (_DB_RES_NEXT_RESULT)
token.c:540:tds_process_tokens(026B08D0, 0176F158, 0176F15C, 0x6914)
token.c:543:tds_process_tokens() state is COMPLETED
dblib.c:1698:dbresults() tds_process_tokens returned 2
(TDS_NO_MORE_RESULTS),
result_type TDS_DONE_RESULT
dblib.c:1660:dbresults returning 2 (NO_MORE_RESULTS)
dblib.c:4515:dbnumrets(026B0640)
dblib.c:4520:dbnumrets() finds 0 columns
token.c:540:tds_process_tokens(026B08D0, 0176F16C, 00000000, 0x2282a)
token.c:543:tds_process_tokens() state is COMPLETED
dblib.c:4515:dbnumrets(026B0640)
dblib.c:4520:dbnumrets() finds 0 columns
token.c:540:tds_process_tokens(026B08D0, 0176F14C, 00000000, 0x2282a)
token.c:543:tds_process_tokens() state is COMPLETED
dblib.c:1446:dbclose(026B17E0)
dblib.c:258:dblib_del_connection(10032B88, 026B1A70)
mem.c:615:tds_free_all_results()
util.c:156:Changed query state from IDLE to DEAD
dblib.c:305:dblib_release_tds_ctx(1)
dblib.c:5885:dbfreebuf(026B17E0)
dblib.c:739:dbloginfree(026B16F0)
dblib.c:1446:dbclose(026B0640)
dblib.c:258:dblib_del_connection(10032B88, 026B08D0)
mem.c:615:tds_free_all_results()
util.c:156:Changed query state from IDLE to DEAD
dblib.c:305:dblib_release_tds_ctx(1)
dblib.c:5885:dbfreebuf(026B0640)
dblib.c:739:dbloginfree(026B0550)
dblib.c:1515:dbexit(void)
dblib.c:305:dblib_release_tds_ctx(1)

[James K. Lowden]

On Thu, 27 Mar 2014 13:09:15 +0100

Post by LacaK
I am trying work with output parameters of stored procedures using
FreeTDS dblib
dbnumrets()
but it still returns 0
(same example tested with Microsoft ntwdblib returns 1)

Check the FAQ; you need to use the rpc functions to get the server to
return output parameters.

ntwdblib uses TDS 4.2. I guess the server still answers the old way on
the old protocol.

HTH.

--jkl

[LacaK]

Post by James K. Lowden

Post by LacaK
I am trying work with output parameters of stored procedures using
FreeTDS dblib
dbnumrets()
but it still returns 0
(same example tested with Microsoft ntwdblib returns 1)

Check the FAQ;

Thank you very much for response.

Yes I see it now. I have checked before these places:
http://www.freetds.org/reference/a00285.html
http://www.freetds.org/userguide/samplecode.htm#SAMPLECODE
And there is not mentioned that Microsoft no more supports these dbret*
functions. So I get impression, that it should work as documented.

Interesting is that dbretstatus() still return return status of SP, but
output parameters are not returned ;-)

Post by James K. Lowden
you need to use the rpc functions to get the server to
return output parameters.
ntwdblib uses TDS 4.2. I guess the server still answers the old way on
the old protocol.

Yes, when I set TDS 4.2 with FreeTDS I get output parameters back

BTW what means message in tdsdump:
dblib.c:4742:..\..\src\dblib\dblib.c 4743: logic error:
tds_process_tokens result_type 4043

Thanks
-Laco.

[James K. Lowden]

On Mon, 31 Mar 2014 08:15:29 +0200

Post by LacaK
tds_process_tokens result_type 4043

That means result_type 4043 for TDS_SUCCESS code isn't handled. That
result_type is an output of tds_process_default_tokens representing a
TDS_STATUS_RESULT for a TDS_RETURNSTATUS packet.

Without looking further, the message may indicate that the return
status from a stored procedure was not correctly reported.

Post by LacaK
I have checked before these places ...

Yes. It's one thing to document how a library works. It's something
else again to document how a server will respond. Or, in this case,
how it will *not* respond. :-(

It's tempting to sprinkle the documentation with warnings, but that
would undermine another goal: to keep them brief and to the point. In
this case the caveat appears on
http://www.freetds.org/reference/a00285.html#ga7, in the REMARKS for
dbrpcinit().

Happy hacking.

--jkl

[LacaK]

Post by James K. Lowden
On Mon, 31 Mar 2014 08:15:29 +0200

Post by LacaK
tds_process_tokens result_type 4043

That means result_type 4043 for TDS_SUCCESS code isn't handled. That
result_type is an output of tds_process_default_tokens representing a
TDS_STATUS_RESULT for a TDS_RETURNSTATUS packet.
Without looking further, the message may indicate that the return
status from a stored procedure was not correctly reported.

I do not know, but I can read correct "return status" using
dbretstatus() function
(tested in MS SQL 2000 and 2012 both return same message: logic error:
tds_process_tokens result_type 4043)

Thank you for assistance.
I attach trivial patch for win32/dblib.def Can you accept it please?
-Laco

-------------- next part --------------
An embedded and charset-unspecified text was scrubbed...
Name: dblib.def.diff
URL: <http://lists.ibiblio.org/pipermail/freetds/attachments/20140401/5ad298ad/attachment.ksh>