[python-sybase] RE: Sybase Module 0.36 Output Parameter Handling
Dave Cole
djc at object-craft.com.au
Fri, 18 Mar 2005 16:06:58 +1100
This is a multi-part message in MIME format.
--------------000709000604080908010902
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Bradley Feldman wrote:
> On a hunch, I reverted back to the original FreeTDS (0.62.4) I was using
> that works with the Sybase 0.63 module. I then tried the same test within
> tsql:
>
> 1>DECLARE @ReturnValue int
> 2>SELECT @ReturnValue = 3
> 3>EXEC sp_TestSproc 12345, @ReturnValue OUTPUT
> 4>SELECT @ReturnValue
> 5>go
> (return status = 0)
>
> 55
> ---------------------------
>
> I got the correct result, @ReturnValue of 55. So there's something wrong
> with the handling of output parameters within the Sybase 0.36 module,
> because tsql through FreeTDS is seeing everything correctly.
>
> Anyone have any other ideas on where/how to retrieve the value of output
> parameters using callproc in the Sybase 0.36 module?
I just installed ASE 12.5.2 on my machine so I could do some testing.
It appears there is a problem with the Cursor.callproc() method, but
only when you use a sequence to pass parameters. Dictionary parameters
are fine.
The attached sp_test.py program shows my testing.
This program produces the following output (after applying the attached
callproc.patch to fix sequence argument passing).
{'@num': 12345, '@result': 12345}
[54321, 54321]
If I remove the comment character from the code that enables the
debugging I get the output contained in the attached sp_test.log.
So if it is not working with FreeTDS then they are doing something
slightly differently that is either confusing my code or is incorrect.
- Dave
P.S. All username and password details have been removed.
--
http://www.object-craft.com.au
--------------000709000604080908010902
Content-Type: application/x-python;
name="sp_test.py"
Content-Transfer-Encoding: base64
Content-Disposition: inline;
filename="sp_test.py"
I2ltcG9ydCBzeXMKaW1wb3J0IFN5YmFzZQojU3liYXNlLnNldF9kZWJ1ZyhzeXMuc3Rkb3V0
KQojU3liYXNlLl9jdHguZGVidWcgID0gMQoKZGIgPSBTeWJhc2UuY29ubmVjdCguLi4pCmRi
LmV4ZWN1dGUoJ3NldCBjaGFpbmVkIG9mZicpCmRiLmV4ZWN1dGUoJycnCmlmIGV4aXN0cyAo
c2VsZWN0IG5hbWUgZnJvbSBzeXNvYmplY3RzIHdoZXJlIG5hbWUgPSAic3BfdGVzdF9vdXRw
dXQiKQpiZWdpbgogICAgZHJvcCBwcm9jZWR1cmUgc3BfdGVzdF9vdXRwdXQKZW5kCicnJykK
ZGIuZXhlY3V0ZSgnJycKY3JlYXRlIHByb2NlZHVyZSBzcF90ZXN0X291dHB1dAogICAgQG51
bSBpbnQsIEByZXN1bHQgaW50IG91dHB1dAphcwogICAgc2VsZWN0IEByZXN1bHQgPSBAbnVt
CicnJykKYyA9IGRiLmN1cnNvcigpCnIgPSBjLmNhbGxwcm9jKCdzcF90ZXN0X291dHB1dCcs
IHsnQG51bSc6IDEyMzQ1LCAnQHJlc3VsdCc6IFN5YmFzZS5PVVRQVVQoMSl9KQpwcmludCBy
CnIgPSBjLmNhbGxwcm9jKCdzcF90ZXN0X291dHB1dCcsICg1NDMyMSwgU3liYXNlLk9VVFBV
VCgxKSkpCnByaW50IHIK
--------------000709000604080908010902
Content-Type: text/x-patch;
name="callproc.patch"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline;
filename="callproc.patch"
? MANIFEST
? Sybase.patch
? Sybase.pyc
? a.out
? build
? callproc.patch
? createtable.sql
? droptable.sql
? error.patch
? freetds-log
? setup.py-patch
? small-stress.py
? stress.py
? sybase-0.36pre2.patch
? sybase-log
? sybase.diff
? sybinit.err
? test.c
? test.py
? test_freetds.py
? test_locking-1.py
? test_locking.py
? testsybase.py
? doc/announce
? doc/sybase
? doc/sybase-booklet.ps
? doc/sybase.l2h
? doc/sybase.pdf
? doc/sybase.ps
? doc/sybase.tex2
? examples/example.pyc
Index: Sybase.py
===================================================================
RCS file: /usr/local/cvsroot/object-craft/sybase/Sybase.py,v
retrieving revision 1.65
diff -u -r1.65 Sybase.py
--- Sybase.py 15 Nov 2004 23:56:36 -0000 1.65
+++ Sybase.py 18 Mar 2005 04:49:59 -0000
@@ -400,12 +400,19 @@
break
elif status in (CS_ROW_FAIL, CS_FAIL, CS_CANCELED):
self._raise_error(Error, 'ct_fetch')
+ pos = -1
for buf in bufs:
if buf.status & CS_RETURN:
if type(self._params) is type({}):
self._params[buf.name] = _column_value(buf[0])
else:
- self._params.append(_column_value(buf[0]))
+ while 1:
+ pos += 1
+ param = self._params[pos]
+ if (type(param) is DataBufType
+ and param.status & CS_RETURN):
+ break
+ self._params[pos] = _column_value(buf[0])
_LAZY_IDLE = 0 # prepared command
--------------000709000604080908010902
Content-Type: text/x-log;
name="sp_test.log"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline;
filename="sp_test.log"
ct_con_alloc(ctx0, &conn) -> CS_SUCCEED, conn0
ct_con_props(conn0, CS_SET, CS_USERNAME, "...", CS_NULLTERM, NULL) -> CS_SUCCEED
ct_con_props(conn0, CS_SET, CS_PASSWORD, "...", CS_NULLTERM, NULL) -> CS_SUCCEED
servermsg_cb
ct_connect(conn0, "...", CS_NULLTERM) -> CS_SUCCEED
ct_options(conn0, CS_SET, CS_OPT_CHAINXACTS, 1, CS_UNUSED, NULL) -> CS_SUCCEED
ct_cmd_alloc(conn0, &cmd) -> CS_SUCCEED, cmd0
ct_command(cmd0, CS_LANG_CMD, "set chained off", CS_NULLTERM, CS_UNUSED) -> CS_SUCCEED
ct_send(cmd0) -> CS_SUCCEED
ct_results(cmd0, &result) -> CS_SUCCEED, CS_CMD_SUCCEED
ct_results(cmd0, &result) -> CS_SUCCEED, CS_CMD_DONE
ct_results(cmd0, &result) -> CS_END_RESULTS, CS_FALSE
ct_cmd_drop(cmd0) -> CS_SUCCEED
ct_cmd_alloc(conn0, &cmd) -> CS_SUCCEED, cmd1
ct_command(cmd1, CS_LANG_CMD, "
if exists (select name from sysobjects where name = "sp_test_output")
begin
drop procedure sp_test_output
end
", CS_NULLTERM, CS_UNUSED) -> CS_SUCCEED
ct_send(cmd1) -> CS_SUCCEED
ct_results(cmd1, &result) -> CS_SUCCEED, CS_CMD_SUCCEED
ct_results(cmd1, &result) -> CS_SUCCEED, CS_CMD_DONE
ct_results(cmd1, &result) -> CS_SUCCEED, CS_CMD_SUCCEED
ct_results(cmd1, &result) -> CS_SUCCEED, CS_CMD_DONE
ct_results(cmd1, &result) -> CS_SUCCEED, CS_CMD_SUCCEED
ct_results(cmd1, &result) -> CS_SUCCEED, CS_CMD_DONE
ct_results(cmd1, &result) -> CS_END_RESULTS, CS_FALSE
ct_cmd_drop(cmd1) -> CS_SUCCEED
ct_cmd_alloc(conn0, &cmd) -> CS_SUCCEED, cmd2
ct_command(cmd2, CS_LANG_CMD, "
create procedure sp_test_output
@num int, @result int output
as
select @result = @num
", CS_NULLTERM, CS_UNUSED) -> CS_SUCCEED
ct_send(cmd2) -> CS_SUCCEED
ct_results(cmd2, &result) -> CS_SUCCEED, CS_CMD_SUCCEED
ct_results(cmd2, &result) -> CS_SUCCEED, CS_CMD_DONE
ct_results(cmd2, &result) -> CS_END_RESULTS, CS_FALSE
ct_cmd_drop(cmd2) -> CS_SUCCEED
Cursor.callproc
ct_cmd_alloc(conn0, &cmd) -> CS_SUCCEED, cmd3
ct_command(cmd3, CS_RPC_CMD, "sp_test_output", CS_NULLTERM, CS_UNUSED) -> CS_SUCCEED
ct_param(cmd3, &databuf0->fmt=[name:"@result" type:CS_INT_TYPE status:CS_RETURN format:CS_FMT_UNUSED count:1 maxlength:4 scale:0 precision:0], databuf0->buff, 4, 0) -> CS_SUCCEED
ct_param(cmd3, &databuf1->fmt=[name:"@num" type:CS_INT_TYPE status:CS_INPUTVALUE format:CS_FMT_UNUSED count:1 maxlength:4 scale:0 precision:0], databuf1->buff, 4, 0) -> CS_SUCCEED
ct_send(cmd3) -> CS_SUCCEED
ct_results(cmd3, &result) -> CS_SUCCEED, CS_STATUS_RESULT
ct_res_info(cmd3, CS_NUMDATA, &value, CS_UNUSED, NULL) -> CS_SUCCEED, 1
ct_describe(cmd3, 1, &fmt) -> CS_SUCCEED, datafmt0=[name:"" type:CS_INT_TYPE status:CS_DATES_SHORT format:CS_FMT_UNUSED count:0 maxlength:4 scale:0 precision:0]
ct_bind(cmd3, 1, &datafmt0->fmt=[name:"" type:CS_INT_TYPE status:CS_DATES_SHORT format:CS_FMT_UNUSED count:1 maxlength:4 scale:0 precision:0], databuf2->buff, databuf2->copied, databuf2->indicator) -> CS_SUCCEED, databuf2
_fetch_rows
ct_fetch(cmd3, CS_UNUSED, CS_UNUSED, CS_UNUSED, &rows_read) -> CS_SUCCEED, 1
_fetch_rows
ct_fetch(cmd3, CS_UNUSED, CS_UNUSED, CS_UNUSED, &rows_read) -> CS_END_DATA, 0
ct_results(cmd3, &result) -> CS_SUCCEED, CS_PARAM_RESULT
ct_res_info(cmd3, CS_NUMDATA, &value, CS_UNUSED, NULL) -> CS_SUCCEED, 1
ct_describe(cmd3, 1, &fmt) -> CS_SUCCEED, datafmt1=[name:"@result" type:CS_INT_TYPE status:CS_RETURN format:CS_FMT_UNUSED count:0 maxlength:4 scale:0 precision:0]
ct_bind(cmd3, 1, &datafmt1->fmt=[name:"@result" type:CS_INT_TYPE status:CS_RETURN format:CS_FMT_UNUSED count:1 maxlength:4 scale:0 precision:0], databuf3->buff, databuf3->copied, databuf3->indicator) -> CS_SUCCEED, databuf3
ct_fetch(cmd3, CS_UNUSED, CS_UNUSED, CS_UNUSED, &rows_read) -> CS_SUCCEED, 1
ct_fetch(cmd3, CS_UNUSED, CS_UNUSED, CS_UNUSED, &rows_read) -> CS_END_DATA, 0
ct_results(cmd3, &result) -> CS_SUCCEED, CS_CMD_SUCCEED
ct_results(cmd3, &result) -> CS_SUCCEED, CS_CMD_DONE
ct_results(cmd3, &result) -> CS_END_RESULTS, CS_FALSE
{'@num': 12345, '@result': 12345}
Cursor.callproc
ct_cmd_drop(cmd3) -> CS_SUCCEED
ct_cmd_alloc(conn0, &cmd) -> CS_SUCCEED, cmd4
ct_command(cmd4, CS_RPC_CMD, "sp_test_output", CS_NULLTERM, CS_UNUSED) -> CS_SUCCEED
ct_param(cmd4, &databuf5->fmt=[name:"" type:CS_INT_TYPE status:CS_INPUTVALUE format:CS_FMT_UNUSED count:1 maxlength:4 scale:0 precision:0], databuf5->buff, 4, 0) -> CS_SUCCEED
ct_param(cmd4, &databuf4->fmt=[name:"" type:CS_INT_TYPE status:CS_RETURN format:CS_FMT_UNUSED count:1 maxlength:4 scale:0 precision:0], databuf4->buff, 4, 0) -> CS_SUCCEED
ct_send(cmd4) -> CS_SUCCEED
ct_results(cmd4, &result) -> CS_SUCCEED, CS_STATUS_RESULT
ct_res_info(cmd4, CS_NUMDATA, &value, CS_UNUSED, NULL) -> CS_SUCCEED, 1
ct_describe(cmd4, 1, &fmt) -> CS_SUCCEED, datafmt2=[name:"" type:CS_INT_TYPE status:CS_DATES_SHORT format:CS_FMT_UNUSED count:0 maxlength:4 scale:0 precision:0]
ct_bind(cmd4, 1, &datafmt2->fmt=[name:"" type:CS_INT_TYPE status:CS_DATES_SHORT format:CS_FMT_UNUSED count:1 maxlength:4 scale:0 precision:0], databuf6->buff, databuf6->copied, databuf6->indicator) -> CS_SUCCEED, databuf6
_fetch_rows
ct_fetch(cmd4, CS_UNUSED, CS_UNUSED, CS_UNUSED, &rows_read) -> CS_SUCCEED, 1
_fetch_rows
ct_fetch(cmd4, CS_UNUSED, CS_UNUSED, CS_UNUSED, &rows_read) -> CS_END_DATA, 0
ct_results(cmd4, &result) -> CS_SUCCEED, CS_PARAM_RESULT
ct_res_info(cmd4, CS_NUMDATA, &value, CS_UNUSED, NULL) -> CS_SUCCEED, 1
ct_describe(cmd4, 1, &fmt) -> CS_SUCCEED, datafmt3=[name:"" type:CS_INT_TYPE status:CS_RETURN format:CS_FMT_UNUSED count:0 maxlength:4 scale:0 precision:0]
ct_bind(cmd4, 1, &datafmt3->fmt=[name:"" type:CS_INT_TYPE status:CS_RETURN format:CS_FMT_UNUSED count:1 maxlength:4 scale:0 precision:0], databuf7->buff, databuf7->copied, databuf7->indicator) -> CS_SUCCEED, databuf7
ct_fetch(cmd4, CS_UNUSED, CS_UNUSED, CS_UNUSED, &rows_read) -> CS_SUCCEED, 1
ct_fetch(cmd4, CS_UNUSED, CS_UNUSED, CS_UNUSED, &rows_read) -> CS_END_DATA, 0
ct_results(cmd4, &result) -> CS_SUCCEED, CS_CMD_SUCCEED
ct_results(cmd4, &result) -> CS_SUCCEED, CS_CMD_DONE
ct_results(cmd4, &result) -> CS_END_RESULTS, CS_FALSE
[54321, 54321]
ct_cmd_drop(cmd4) -> CS_SUCCEED
ct_con_props(conn0, CS_GET, CS_CON_STATUS, &value, CS_UNUSED, NULL) -> CS_SUCCEED, CS_CONSTAT_CONNECTED
ct_close(conn0, CS_FORCE_CLOSE) -> CS_SUCCEED
ct_con_drop(conn0) -> CS_SUCCEED
--------------000709000604080908010902--