Menu
▾
▴
python-sybase-misc
|
From: Bradley F. <br...@re...> - 2005-03-18 15:32:08
|
I tried the code Dave (and Skip) suggested, but I still can't seem to = find the value of the returned parameters, even through fetchall/nextset = calls. Also, using a debugger I don't see anything being passed back to the buf object. I'm wondering if the issue has something to do that I'm using Sybase = 0.36 module with FreeTDS 0.62 and MS SQL Server 2000. I'm assuming that it = all works on Sybase 9.0 and that it's Microsoft munging everything up. So, I tried to upgrade to a later version of FreeTDS (0.63 or higher)-- = I read somewhere that later version is handling output parameters better. Everything is now built straight from source. When I fire up python and = try to "import Sybase" I get an error saying "Import Error: libct.so.2 - = cannot open shared object file" Upon further investigation I see that FreeTDS 0.63+ does not produce a libct.so.2 -- only a libct.so.3. So, is there something that can be easily fixed within Sybase module to = link with libct.so.3 instead of libct.so.2? BTW, Here's a test I performed to see if the new FreeTDS and MS SQL = Server are communicating properly: -------------------------------- FreeTDS Version 0.64.dev.20050317 Using tsql with the following simple example: 1>DECLARE @ReturnValue int 2>SELECT @ReturnValue =3D 3 3>EXEC sp_TestSproc 12345, @ReturnValue OUTPUT 4>SELECT @ReturnValue 5>go (return status =3D 0) 55 --------------------------- So that proves that FreeTDS and tsql are passing and returning = parameters correctly to/from MS SQL Server 2000 because the value of @ReturnValue = is changed from 3 to 55 upon execution of the Stored Procedure. All I need now is instructions for how to see the return value using the Python Sybase 0.36 module and I'm good to go! Any ideas from anyone? *Bradley |
|
From: Bradley F. <br...@re...> - 2005-03-18 15:58:17
|
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? Thanks in advance, *Bradley |
|
From: Dave C. <dj...@ob...> - 2005-03-19 00:05:48
|
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
|
|
From: Dave C. <dj...@ob...> - 2005-03-19 00:41:19
|
Dave Cole <dj...@ob...>Dave Cole
<dj...@ob...>Dave Cole wrote:
> 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.
I just installed FreeTDS 0.61 (Debian sarge) and the test program fails
to run. The ct_param() functions fail. What I did discover was that
you can fudge it like this:
print db.execute('''
declare @result int
exec sp_test_output 12345, @result output
select @result
''')
It prints the following:
[[], [(12345,)]]
I am now downloading the current cvs snapshot for FreeTDS to see if they
have fixed parameter passing.
- Dave
--
http://www.object-craft.com.au
|
|
From: Dave C. <dj...@ob...> - 2005-03-19 00:59:16
|
Dave Cole wrote:
> Dave Cole <dj...@ob...>Dave Cole
> <dj...@ob...>Dave Cole wrote:
>
>> 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.
>
>
> I just installed FreeTDS 0.61 (Debian sarge) and the test program fails
> to run. The ct_param() functions fail. What I did discover was that
> you can fudge it like this:
>
> print db.execute('''
> declare @result int
> exec sp_test_output 12345, @result output
> select @result
> ''')
>
> It prints the following:
> [[], [(12345,)]]
>
> I am now downloading the current cvs snapshot for FreeTDS to see if they
> have fixed parameter passing.
I just downloaded and installed the CVS snapshot. With the original
test program it appears to send the parameters to the server but fails
with a server message.
Looking at the debug log:
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
So it appears that parameters are being accepted by FreeTDS but from the
next debug lines, they are not being sent correctly.
ct_send(cmd3) -> CS_SUCCEED
servermsg_cb
Msg 245, Level 16, State 1, Procedure sp_test_output
Domain error during implicit conversion of DECIMAL value '' to a INT field.
servermsg_cb
Msg 245, Level 16, State 1, Procedure sp_test_output
Domain error during implicit conversion of DECIMAL value '' to a INT field.
ct_results(cmd3, &result) -> CS_SUCCEED, CS_STATUS_RESULT
ct_cancel(conn0, NULL, CS_CANCEL_ALL) -> CS_SUCCEED
Traceback (most recent call last):
File "a.py", line 21, in ?
r = c.callproc('sp_test_output', {'@num': 12345, '@result':
Sybase.OUTPUT(1)})
File "/usr/lib/python2.3/site-packages/Sybase.py", line 718, in callproc
self.description = fetcher.start(self.arraysize, out_params)
File "/usr/lib/python2.3/site-packages/Sybase.py", line 391, in start
return _FetchNow.start(self, arraysize)
File "/usr/lib/python2.3/site-packages/Sybase.py", line 315, in start
raise e
Sybase.DatabaseError: Msg 245, Level 16, State 1, Procedure sp_test_output
Domain error during implicit conversion of DECIMAL value '' to a INT field.
Msg 245, Level 16, State 1, Procedure sp_test_output
Domain error during implicit conversion of DECIMAL value '' to a INT field.
Even more worrying is that the fudge in the previous message no longer
works. I get crap back.
Oh well, looks like I can wait for another couple of FreeTDS releases
before trying again...
- Dave
--
http://www.object-craft.com.au
|
|
From: Bradley F. <br...@re...> - 2005-03-19 01:09:22
|
What version of FreeTDS are you using?
I'm not seeing any errors like that at all. I'm using FreeTDS 0.62.4. =
See
below:
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=3D[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=3D[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=3D[name:"" =
type:CS_INT_TYPE
status:CS_FALSE format:CS_FMT_UNUSED count:1 maxlength:4 scale:0
precision:0]
ct_bind(cmd3, 1, &datafmt0->fmt=3D[name:"" type:CS_INT_TYPE =
status:CS_FALSE
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=3D[name:"@result"
type:CS_INT_TYPE status:CS_FALSE format:CS_FMT_UNUSED count:1 =
maxlength:4
scale:0 precision:0]
ct_bind(cmd3, 1, &datafmt1->fmt=3D[name:"@result" type:CS_INT_TYPE
status:CS_FALSE 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': <DataBufType object at 0x2a95596770>}
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=3D[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=3D[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=3D[name:"" =
type:CS_INT_TYPE
status:CS_FALSE format:CS_FMT_UNUSED count:1 maxlength:4 scale:0
precision:0]
ct_bind(cmd4, 1, &datafmt2->fmt=3D[name:"" type:CS_INT_TYPE =
status:CS_FALSE
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=3D[name:"" =
type:CS_INT_TYPE
status:CS_FALSE format:CS_FMT_UNUSED count:1 maxlength:4 scale:0
precision:0]
ct_bind(cmd4, 1, &datafmt3->fmt=3D[name:"" type:CS_INT_TYPE =
status:CS_FALSE
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, <DataBufType object at 0x2a95596940>]
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_OPT_STATS_IO) -> CS_SUCCEED
ct_con_drop(conn0) -> CS_SUCCEED
-----Original Message-----
From: pyt...@ww...
[mailto:pyt...@ww...] On Behalf Of Dave =
Cole
Sent: Thursday, March 17, 2005 10:00 PM
To: pyt...@ww...
Subject: Re: [python-sybase] RE: Sybase Module 0.36 Output Parameter
Handling
Dave Cole wrote:
> Dave Cole <dj...@ob...>Dave Cole
> <dj...@ob...>Dave Cole wrote:
>=20
>> 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=20
>> only when you use a sequence to pass parameters. Dictionary=20
>> 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.
>=20
>=20
> I just installed FreeTDS 0.61 (Debian sarge) and the test program=20
> fails
> to run. The ct_param() functions fail. What I did discover was that=20
> you can fudge it like this:
>=20
> print db.execute('''
> declare @result int
> exec sp_test_output 12345, @result output
> select @result
> ''')
>=20
> It prints the following:
> [[], [(12345,)]]
>=20
> I am now downloading the current cvs snapshot for FreeTDS to see if=20
> they
> have fixed parameter passing.
I just downloaded and installed the CVS snapshot. With the original=20
test program it appears to send the parameters to the server but fails=20
with a server message.
Looking at the debug log:
Cursor.callproc
ct_cmd_alloc(conn0, &cmd) -> CS_SUCCEED, cmd3
ct_command(cmd3, CS_RPC_CMD, "sp_test_output", CS_NULLTERM, CS_UNUSED)=20
-> CS_SUCCEED
ct_param(cmd3, &databuf0->fmt=3D[name:"@result" type:CS_INT_TYPE=20
status:CS_RETURN format:CS_FMT_UNUSED count:1 maxlength:4 scale:0=20
precision:0], databuf0->buff, 4, 0) -> CS_SUCCEED ct_param(cmd3,
&databuf1->fmt=3D[name:"@num" type:CS_INT_TYPE=20
status:CS_INPUTVALUE format:CS_FMT_UNUSED count:1 maxlength:4 scale:0=20
precision:0], databuf1->buff, 4, 0) -> CS_SUCCEED
So it appears that parameters are being accepted by FreeTDS but from the =
next debug lines, they are not being sent correctly.
ct_send(cmd3) -> CS_SUCCEED
servermsg_cb
Msg 245, Level 16, State 1, Procedure sp_test_output
Domain error during implicit conversion of DECIMAL value '' to a INT =
field.
servermsg_cb Msg 245, Level 16, State 1, Procedure sp_test_output Domain
error during implicit conversion of DECIMAL value '' to a INT field.
ct_results(cmd3, &result) -> CS_SUCCEED, CS_STATUS_RESULT =
ct_cancel(conn0,
NULL, CS_CANCEL_ALL) -> CS_SUCCEED Traceback (most recent call last):
File "a.py", line 21, in ?
r =3D c.callproc('sp_test_output', {'@num': 12345, '@result':=20
Sybase.OUTPUT(1)})
File "/usr/lib/python2.3/site-packages/Sybase.py", line 718, in =
callproc
self.description =3D fetcher.start(self.arraysize, out_params)
File "/usr/lib/python2.3/site-packages/Sybase.py", line 391, in start
return _FetchNow.start(self, arraysize)
File "/usr/lib/python2.3/site-packages/Sybase.py", line 315, in start
raise e
Sybase.DatabaseError: Msg 245, Level 16, State 1, Procedure =
sp_test_output
Domain error during implicit conversion of DECIMAL value '' to a INT =
field.
Msg 245, Level 16, State 1, Procedure sp_test_output Domain error during
implicit conversion of DECIMAL value '' to a INT field.
Even more worrying is that the fudge in the previous message no longer=20
works. I get crap back.
Oh well, looks like I can wait for another couple of FreeTDS releases=20
before trying again...
- Dave
--=20
http://www.object-craft.com.au
_______________________________________________
Python-sybase mailing list Pyt...@ww...
https://www.object-craft.com.au/cgi-bin/mailman/listinfo/python-sybase
|
|
From: Dave C. <dj...@ob...> - 2005-03-19 01:40:05
|
Bradley Feldman wrote: > What version of FreeTDS are you using? I originally tried it using 0.61 as I am using Debian sarge. That failed in the attempt to pass a parameter. I then downloaded the cvs snapshot freetds-0.64.dev.20050317. It failed in the interesting way described. Maybe the small change in the previously posted message might work around the problem in 0.62.4. - Dave -- http://www.object-craft.com.au |
|
From: Dave C. <dj...@ob...> - 2005-03-19 01:46:59
|
Dave Cole wrote: > Bradley Feldman wrote: > >> What version of FreeTDS are you using? > > > I originally tried it using 0.61 as I am using Debian sarge. That > failed in the attempt to pass a parameter. I then downloaded the cvs > snapshot freetds-0.64.dev.20050317. It failed in the interesting way > described. > > Maybe the small change in the previously posted message might work > around the problem in 0.62.4. I just installed FreeTDS 0.62.4 and tried with the small change to _FetchNowParams._param_result(). The test program now works. - Dave -- http://www.object-craft.com.au |
|
From: Dave C. <dj...@ob...> - 2005-03-19 01:49:09
|
Dave Cole wrote: > Dave Cole wrote: > >> Bradley Feldman wrote: >> >>> What version of FreeTDS are you using? >> >> >> >> I originally tried it using 0.61 as I am using Debian sarge. That >> failed in the attempt to pass a parameter. I then downloaded the cvs >> snapshot freetds-0.64.dev.20050317. It failed in the interesting way >> described. >> >> Maybe the small change in the previously posted message might work >> around the problem in 0.62.4. > > > I just installed FreeTDS 0.62.4 and tried with the small change to > _FetchNowParams._param_result(). > > The test program now works. Forgot to add: I have to go home now. I will put together a new release of the module over the weekend. It appears that the 0.64 FreeTDS is going to break this again however. :-( - Dave -- http://www.object-craft.com.au |
|
From: Bradley F. <br...@re...> - 2005-03-19 01:51:44
|
FreeTDS 0.64 isn't a stable release, yet.... 0.62.4 is the last true stable release. I'm using it on Gentoo. *Bradley -----Original Message----- From: Dave Cole [mailto:dj...@ob...] Sent: Thursday, March 17, 2005 10:50 PM To: pyt...@ww... Cc: Bradley Feldman Subject: Re: [python-sybase] RE: Sybase Module 0.36 Output Parameter Handling Dave Cole wrote: > Dave Cole wrote: > >> Bradley Feldman wrote: >> >>> What version of FreeTDS are you using? >> >> >> >> I originally tried it using 0.61 as I am using Debian sarge. That >> failed in the attempt to pass a parameter. I then downloaded the cvs >> snapshot freetds-0.64.dev.20050317. It failed in the interesting way >> described. >> >> Maybe the small change in the previously posted message might work >> around the problem in 0.62.4. > > > I just installed FreeTDS 0.62.4 and tried with the small change to > _FetchNowParams._param_result(). > > The test program now works. Forgot to add: I have to go home now. I will put together a new release of the module over the weekend. It appears that the 0.64 FreeTDS is going to break this again however. :-( - Dave -- http://www.object-craft.com.au |
Thanks for helping keep SourceForge clean.
X