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 |