python-sybase-misc

[Python-sybase-misc] pymssql connection problem

[VV W <vv...@gm...>]

Hello,

I modified my script and tested again with

 con=pymssql.connect(dsn='sybase-sever.mycompany.com', user='sa',
password='',host="12.12.12.12",database='db-name')

and now I got


 File "/var/lib/python-support/python2.5/pymssql.py", line 328, in connect
    con = _mssql.connect(dbhost, dbuser, dbpasswd)
_mssql.error: DB-Lib error message 20009, severity 9:
Unable to connect: Adaptive Server is unavailable or does not exist
Net-Lib error during Operation now in progress
Error 115 - Operation now in progress

this is the problem that I got yesterday. It's really frustrating.

Anybody can give me a clue?

Thanks so much.

[Python-sybase-misc] pymssql: version problem?

[VV W <vv...@gm...>]

hello experts,

Thanks for all of your replies during the past three days. I installed "
pymssql-0.8.0" instead of "python-sybase-0.39".

>From my test results I see that I could connect to Sybase with "tsql" on
prompt no matter if I use "freetds.conf" or not. However, if I use Python
script as follows

import pymssql
con=pymssql.connect(dbhost, user, password)

the I got
"_mssql.error: Could not connect to MS SQL Server".

I know that there are three "freetds.conf" files. One is in
"/home/my_dir/freetds-0.82/". The second one is in /etc/ and the third one
is in /etc/freetds/. They are all the same. In global section "tds version =
4.2" and in SybaseServer section "tds version = 5.0".   When I installed
"freetds-0.82" I indicted that with-tdsver=4.2.

Why does this Python script try to connect MS SQL Server? Is there any other
place besides freetds that indicates MS-SQL or Sybase? And "tsql" works
correctly on prompt with freestd file.

Any clues how to fix this problem?

Thanks so much. I appreciate your help.

Re: [Python-sybase-misc] Sybase installation directory

[Lloyd K. <py...@ve...>]

On Tue, 2009-09-08 at 11:22 -0700, VV W wrote:
> Now that I use
> 
> python setup.py build_ext -D HAVE_FREETDS -U WANT_BULKCOPY
> 
> to install, I still get
> 
> "Please define the Sybase installation directory in the SYBASE
> environment
> variable."
> 
> What did I miss? any more suggestions?

export SYBASE=/usr
python setup.py build_ext -D HAVE_FREETDS -U WANT_BULKCOPY -I/usr/include/freetds

This worked for me.  



Steering you towards freetds may have been a mistake.  I had no idea
that client software from sybase.com was an option.

My Ubuntu (9.4) provides python-pymssql which claims to work with both
sybase and mssql servers.  (I'm using it for mssql so I can't vouch for
sybase effectiveness.

> 
> Thanks again.
-- 
Lloyd Kvam
Venix Corp
DLSLUG/GNHLUG library
http://dlslug.org/library.html
http://www.librarything.com/catalog/dlslug
http://www.librarything.com/rsshtml/recent/dlslug
http://www.librarything.com/rss/recent/dlslug

[Python-sybase-misc] Sybase installation directory

[VV W <vv...@gm...>]

Hi experts,

Thanks for your replies. Can you help me again please?

I have installed FreeTDS and all seems ok. I modified
/etc/freetds/freetds.conf in the following way.

# A typical Sybase server

[egServer50]

host = sybaseserver.mycompany.com

port = 5000

tds version = 8.0
I changed "tds version" from 5.0 to 0.82. I did not touch the "[gloabl]" and
"[egServer70]".


Now that I use

python setup.py build_ext -D HAVE_FREETDS -U WANT_BULKCOPY

to install, I still get

"Please define the Sybase installation directory in the SYBASE environment
variable."

What did I miss? any more suggestions?

Thanks again.

Re: [Python-sybase-misc] Passing SQL NULL to a stored procedure - throws an exception

[Sébastien S. <sa...@us...>]

Hi Jeffrey,

this problem is specific to Sybase >= 15.0.2 and has been reported in a 
previous bug tracker issue:

http://sourceforge.net/tracker/?func=detail&aid=1771757&group_id=184050&atid=907701

There is no easy solution at the module level. A workaround has been 
proposed:

"""
The workaround to this is a bit problematic. Usually you just pass in a
value and the module figures out the type from the value. But None has no
value and no type. So you were defaulting to sending it as a CS_INT. Now
the user has to construct and provide a DataBuf object as the parameter
value and they have to explicitly construct a CS_DATAFMT for that DataBuf
if they want to pass a NULL value.
"""

regards

--
Sébastien Sablé

Jeffrey Zelt a écrit :
> Hi,
> 
>  
> 
> I need to pass SQL NULL for one of the input parameters to a stored 
> procedure.  This stored procedure is called via the usual:
> 
>  
> 
>     cursor.callproc(‘storedprocname’, parameterdictionary)
> 
>  
> 
> The usual way to pass SQL NULL using the Python DB-API is to pass None 
> instead.  However, this causes python-sybase to throw an exception, as 
> shown here:
> 
>  
> 
>     Traceback (most recent call last):
> 
>       File 
> "/home/jeffreyz/workspace-3.5/generate-client-test-data/src/generate_data_tests.py", 
> line 100, in <module>
> 
>         '@op_ClientNumber': Sybase.OUTPUT(1)})
> 
>       File "build/bdist.linux-i686/egg/Sybase.py", line 426, in callproc
> 
>       File "build/bdist.linux-i686/egg/Sybase.py", line 703, in _start
> 
>       File "build/bdist.linux-i686/egg/Sybase.py", line 717, in _mainloop
> 
>       File "build/bdist.linux-i686/egg/Sybase.py", line 761, in _raise_error
> 
>     Sybase.DatabaseError: Msg 257, Level 16, State 1, Procedure 
> qp_GUI_ClientInsert
> 
>     Implicit conversion from datatype 'INT' to 'UNIVARCHAR' is not 
> allowed.  Use the CONVERT function to run this query.
> 
>  
> 
> For some reason, it triggers an “Implicit conversion from datatype 'INT' 
> to 'UNIVARCHAR' is not allowed” exception.  For this particular case, 
> the input parameter is of type UNIVARCHAR, but this problem is not 
> particular to input parameters of only this type.  For this particular 
> case, SQL NULL is a legal value to pass to the stored procedure.
> 
>  
> 
> I have found one other reference to this issue here:
> 
>  
> 
>      
> http://www.archivum.info/comp.databases.sybase/2007-08/00008/15.0.2_upgrade_issue_wrt_passing_null_parameters_to_procedure
> 
>  
> 
> Is this a known problem?  Is there a simple fix?  Or am I doing 
> something stupid?
> 
>  
> 
> Regards,
> 
> Jeffrey
> 
>  
> 
> 
> ------------------------------------------------------------------------
> 
> ------------------------------------------------------------------------------
> Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day 
> trial. Simplify your report design, integration and deployment - and focus on 
> what you do best, core application coding. Discover what's new with 
> Crystal Reports now.  http://p.sf.net/sfu/bobj-july
> 
> 
> ------------------------------------------------------------------------
> 
> _______________________________________________
> Python-sybase-misc mailing list
> Pyt...@li...
> https://lists.sourceforge.net/lists/listinfo/python-sybase-misc

Re: [Python-sybase-misc] Python-Sybase module install questions

[Lloyd K. <py...@ve...>]

On Tue, 2009-09-08 at 09:05 -0700, VV W wrote:
> Hello, 
>  
> I searched the Python-Sybase Archives however I did not find what I
> need. So I post my question here. 
>  
> My Sybase database sits on another computer in a different place. I
> know the server's name for the computer. My qestions are 
> (1.) How to give Sybase directory in the "setup.py"?
> (3.) Any information or webpage links that you can provide to me for
> such a remote datababse case?

If you are using freetds, you need to update your
        /etc/freetds/freetds.conf
file.  The samples from the end of the file are:

# A typical Sybase server
[egServer50]
	host = symachine.domain.com
	port = 5000
	tds version = 5.0

# A typical Microsoft server
[egServer70]
	host = ntmachine.domain.com
	port = 1433
	tds version = 7.0

Your Python code would look like
self.conn = Sybase.connect( egServer70, .....
where you reference the name you used in the freetds.conf file.

>  
> I work on Ubuntu. 
>  
> Thanks so much 
> ------------------------------------------------------------------------------
> Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day 
> trial. Simplify your report design, integration and deployment - and focus on 
> what you do best, core application coding. Discover what's new with 
> Crystal Reports now.  http://p.sf.net/sfu/bobj-july
> _______________________________________________ Python-sybase-misc mailing list Pyt...@li... https://lists.sourceforge.net/lists/listinfo/python-sybase-misc
-- 
Lloyd Kvam
Venix Corp
DLSLUG/GNHLUG library
http://dlslug.org/library.html
http://www.librarything.com/catalog/dlslug
http://www.librarything.com/rsshtml/recent/dlslug
http://www.librarything.com/rss/recent/dlslug

Re: [Python-sybase-misc] Python-Sybase module install questions

[Sébastien S. <sa...@us...>]

Hi "VV",

if your database server is on another computer, you still need to 
install Sybase client libraries on the client computer in order to 
compile and use python-sybase.

* the official version provided by sybase is called "open client":
http://www.sybase.com/products/allproductsa-z/softwaredeveloperkit/openclient

* there is an open source implementation called FreeTDS:
http://sourceforge.net/projects/freetds/
some people have reported that it works OK, though it may lake some 
features of the official client libraries

regards

--
Sébastien Sablé

VV W a écrit :
> Hello,
>  
> I searched the Python-Sybase Archives however I did not find what I 
> need. So I post my question here.
>  
> My Sybase database sits on another computer in a different place. I know 
> the server's name for the computer. My qestions are
> (1.) How to give Sybase directory in the "setup.py"?
> (3.) Any information or webpage links that you can provide to me for 
> such a remote datababse case?
>  
> I work on Ubuntu.
>  
> Thanks so much
> 
> 
> ------------------------------------------------------------------------
> 
> ------------------------------------------------------------------------------
> Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day 
> trial. Simplify your report design, integration and deployment - and focus on 
> what you do best, core application coding. Discover what's new with 
> Crystal Reports now.  http://p.sf.net/sfu/bobj-july
> 
> 
> ------------------------------------------------------------------------
> 
> _______________________________________________
> Python-sybase-misc mailing list
> Pyt...@li...
> https://lists.sourceforge.net/lists/listinfo/python-sybase-misc

[Python-sybase-misc] Python-Sybase module install questions

[VV W <vv...@gm...>]

Hello,

I searched the Python-Sybase Archives however I did not find what I need. So
I post my question here.

My Sybase database sits on another computer in a different place. I know the
server's name for the computer. My qestions are
(1.) How to give Sybase directory in the "setup.py"?
(3.) Any information or webpage links that you can provide to me for such a
remote datababse case?

I work on Ubuntu.

Thanks so much

[Python-sybase-misc] Passing SQL NULL to a stored procedure - throws an exception

[Jeffrey Z. <Jef...@q-...>]

Hi,

 

I need to pass SQL NULL for one of the input parameters to a stored
procedure.  This stored procedure is called via the usual:

 

    cursor.callproc('storedprocname', parameterdictionary)

 

The usual way to pass SQL NULL using the Python DB-API is to pass None
instead.  However, this causes python-sybase to throw an exception, as
shown here:

 

    Traceback (most recent call last):

      File
"/home/jeffreyz/workspace-3.5/generate-client-test-data/src/generate_dat
a_tests.py", line 100, in <module>

        '@op_ClientNumber': Sybase.OUTPUT(1)})

      File "build/bdist.linux-i686/egg/Sybase.py", line 426, in callproc

      File "build/bdist.linux-i686/egg/Sybase.py", line 703, in _start

      File "build/bdist.linux-i686/egg/Sybase.py", line 717, in
_mainloop

      File "build/bdist.linux-i686/egg/Sybase.py", line 761, in
_raise_error

    Sybase.DatabaseError: Msg 257, Level 16, State 1, Procedure
qp_GUI_ClientInsert

    Implicit conversion from datatype 'INT' to 'UNIVARCHAR' is not
allowed.  Use the CONVERT function to run this query.

 

For some reason, it triggers an "Implicit conversion from datatype 'INT'
to 'UNIVARCHAR' is not allowed" exception.  For this particular case,
the input parameter is of type UNIVARCHAR, but this problem is not
particular to input parameters of only this type.  For this particular
case, SQL NULL is a legal value to pass to the stored procedure.

 

I have found one other reference to this issue here:

 

 
http://www.archivum.info/comp.databases.sybase/2007-08/00008/15.0.2_upgr
ade_issue_wrt_passing_null_parameters_to_procedure

 

Is this a known problem?  Is there a simple fix?  Or am I doing
something stupid?

 

Regards,

Jeffrey

 

Re: [Python-sybase-misc] 30-character limit for identifiers with ASE 15.x?

[Sébastien S. <sa...@us...>]

Hi Zsolt,

yes you are right; I missed this file.

regards

Cserna, Zsolt a écrit :
> Sorry if I generate some noise, but for me it was required to change the line 173 in sybasect.c as well:
> 
> Original:
> 
>   171 static PyObject *sybasect_cs_ctx_alloc(PyObject *module, PyObject *args)
>   172 {
>   173     int version = CS_VERSION_100;
>   174
>   175     if (!PyArg_ParseTuple(args, "|i", &version))
>   176         return NULL;
>   177     return ctx_alloc(version);
>   178 }
> 
> 
> It was required to gain access to wide tables (eg. tables containing varchar / char fields sized more than 255 chars).
> 
> Zsolt
>   
> 
>> -----Original Message-----
>> From: Sébastien Sablé [mailto:sa...@us...] 
>> Sent: Thursday, August 27, 2009 18:26
>> To: Jeffrey Zelt
>> Cc: pyt...@li...
>> Subject: Re: [Python-sybase-misc] 30-character limit for 
>> identifiers with ASE 15.x?
>>
>> Hi Jeffrey,
>>
>> I think this may be the same problem which was recently 
>> reported in other emails and which is also reported in this 
>> bug tracker:
>>
>> http://sourceforge.net/tracker/?func=detail&aid=2809032&group_
> id=184050&atid=907701
>>
>> python-sybase is currently specifying at initialization to 
>> Sybase that it wants sybase 10.0 capabilities.
>>
>> You need to modify python-sybase in this way if you want to 
>> use capabilities of Sybase 15 :
>>
>> Index: ctx.c
>> ===================================================================
>> --- ctx.c	(révision 443)
>> +++ ctx.c	(copie de travail)
>> @@ -1083,7 +1083,7 @@
>>   	return NULL;
>>       }
>>
>> -    version = CS_VERSION_100;
>> +    version = CS_VERSION_150;
>>       if (!PyArg_ParseTuple(args, "|i", &version))
>>   	return NULL;
>>
>>
>> It should be easy to write a small patch so that the maximum 
>> available version of CS_VERSION_* is detected at compilation 
>> time, so that python-sybase always use the capability of the 
>> version of Sybase it is compiled with. I will add that for 
>> python-sybase 0.40.
>>
>> regards
>>
>> --
>> Sébastien Sablé
>>
>> Jeffrey Zelt a écrit :
>>> I have a problem where a Sybase.DatabaseError exception is 
>> thrown by 
>>> the python-sybase module when I execute a Sybase ASE 15.0.3 stored 
>>> procedure that contains a local variable with a name longer 
>> than 30-characters.
>>>  
>>>
>>> Here is exact error text:
>>>
>>>  
>>>
>>> Traceback (most recent call last):
>>>
>>>   File
>>>
>> "/home/jeffreyz/workspace-3.5/generate-client-test-data/src/generate_d
>>> ata_run.py",
>>> line 11, in <module>
>>>
>>>     '--logging-stderr-on', '--logging-stderr-level', 
>>> str(logging.DEBUG),
>>>
>>>   File
>>>
>> "/home/jeffreyz/workspace-3.5/generate-client-test-data/src/generate_d
>>> ata.py",
>>> line 415, in main
>>>
>>>     generate_client(cursor, options)
>>>
>>>   File
>>>
>> "/home/jeffreyz/workspace-3.5/generate-client-test-data/src/generate_d
>>> ata.py",
>>> line 271, in generate_client
>>>
>>>     '@op_AccountNumber': Sybase.OUTPUT(1)})
>>>
>>>   File "build/bdist.linux-i686/egg/Sybase.py", line 418, in callproc
>>>
>>>   File "build/bdist.linux-i686/egg/Sybase.py", line 695, in _start
>>>
>>>   File "build/bdist.linux-i686/egg/Sybase.py", line 709, in 
>> _mainloop
>>>   File "build/bdist.linux-i686/egg/Sybase.py", line 753, in 
>>> _raise_error
>>>
>>> Sybase.DatabaseError: Msg 103, Level 15, State 205, Line 13
>>>
>>> The identifier that starts with '@tmp_HasFixedInitialCreditLimi' is 
>>> too long. Maximum length is 30.
>>>
>>> Msg 103, Level 15, State 205, Line 14
>>>
>>> The identifier that starts with '@tmp_HasFixedClearingCreditLim' is 
>>> too long. Maximum length is 30.
>>>
>>> Msg 137, Level 15, State 1, Line 14
>>>
>>> Must declare variable '@tmp_HasFixedClearingCreditLimi'.
>>>
>>>  
>>>
>>> Notice that it is complaining that the name of the local variable 
>>> '@tmp_HasFixedClearingCreditLimit' is too long.
>>>
>>>  
>>>
>>> This was a known problem with ASE 12.x.  However, I compiled the 
>>> python-sybase module on Ubuntu 9.04 against:
>>>
>>>  
>>>
>>>     Linux Express Edition for ASE 15.0.3
>>>
>>>  
>>>
>>> I have never installed a version of ASE 12.x, so I could not have 
>>> accidentally compiled against old libraries.  The server on 
>> which the 
>>> stack trace above was created was running ASE 15.0.3 on 
>> Fedora Linux.
>>>  
>>>
>>> Does anyone know why I triggered an exception for identifiers over 
>>> 30-characters when all libraries involved seem to be for 
>> Sybase ASE 15.x?
>>>  
>>>
>>> Is there a way to fix this problem so that no exception is thrown?
>>>
>>>  
>>>
>>> Regards,
>>>
>>> Jeffrey
>>>
>>>  
>>>
>>>
>>>
>> ----------------------------------------------------------------------
>>> --
>>>
>>>
>> ----------------------------------------------------------------------
>>> -------- Let Crystal Reports handle the reporting - Free Crystal 
>>> Reports 2008 30-Day trial. Simplify your report design, integration 
>>> and deployment - and focus on what you do best, core application 
>>> coding. Discover what's new with Crystal Reports now.  
>>> http://p.sf.net/sfu/bobj-july
>>>
>>>
>>>
>> ----------------------------------------------------------------------
>>> --
>>>
>>> _______________________________________________
>>> Python-sybase-misc mailing list
>>> Pyt...@li...
>>> https://lists.sourceforge.net/lists/listinfo/python-sybase-misc
>>
>> --------------------------------------------------------------
>> ----------------
>> Let Crystal Reports handle the reporting - Free Crystal 
>> Reports 2008 30-Day trial. Simplify your report design, 
>> integration and deployment - and focus on what you do best, 
>> core application coding. Discover what's new with Crystal 
>> Reports now.  http://p.sf.net/sfu/bobj-july 
>> _______________________________________________
>> Python-sybase-misc mailing list
>> Pyt...@li...
>> https://lists.sourceforge.net/lists/listinfo/python-sybase-misc
>>
> --------------------------------------------------------------------------
> NOTICE: If received in error, please destroy, and notify sender. Sender does not intend to waive confidentiality or privilege. Use of this email is prohibited when received in error. We may monitor and store emails to the extent permitted by applicable law.

Re: [Python-sybase-misc] Exception thrown executing "reorg rebuild"

[Sébastien S. <sa...@us...>]

Hi Jeffrey,

According to the following page:
http://manuals.sybase.com/onlinebooks/group-as/asg1250e/svrtsg/@Generic__BookTextView/77787;pt=62077/*

"""This is an informational message. Unless otherwise specified, no 
action is required. Contact your System Administrator about any warning 
issued in the message."""

So I think the following patch should correct your problem:

Index: Sybase.py
===================================================================
--- Sybase.py	(révision 443)
+++ Sybase.py	(copie de travail)
@@ -258,13 +258,15 @@
          raise IntegrityError(msg)
      elif mn == 2812: ## Procedure not found
          raise StoredProcedureError(msg)
-    elif mn in (0, 5701, 5703, 5704) or ((mn >= 6200) and (mn < 6300)):
+    elif mn in (0, 1918, 5701, 5703, 5704, 11932) or ((mn >= 6200) and 
(mn < 6300)):
          # Non-errors:
          #    0      PRINT
+        # 1918      Non-clustered index is being rebuilt.
          # 5701      Changed db context
          # 5703      Changed language
          # 5704      Changed character set (Sybase)
          # 6200-6299 SHOWPLAN output (Sybase)
+        # 11932     Beginning REORG REBUILD of table
          hook = _output_hooks.get(conn)
          if hook:
              hook(conn, msg)


regards

--
Sébastien Sablé


Jeffrey Zelt a écrit :
> I would like to execute “reorg rebuild” on the server.  Whenever I do 
> this, python-sybase throws a Sybase.DatabaseError exception as shown here:
> 
>  
> 
> Traceback (most recent call last):
> 
>   File 
> "/home/jeffreyz/workspace-3.5/sybase-tests/src/connection_tests.py", 
> line 41, in <module>
> 
>     cursor.execute('reorg rebuild Client')       
> 
>   File "build/bdist.linux-i686/egg/Sybase.py", line 546, in execute
> 
>   File "build/bdist.linux-i686/egg/Sybase.py", line 695, in _start
> 
>   File "build/bdist.linux-i686/egg/Sybase.py", line 709, in _mainloop
> 
>   File "build/bdist.linux-i686/egg/Sybase.py", line 753, in _raise_error
> 
> Sybase.DatabaseError: Msg 11932, Level 10, State 1, Line 1
> 
> Beginning REORG REBUILD of table 'Client'.
> 
> Msg 1918, Level 10, State 1, Line 1
> 
> Non-clustered index (index id = 2) is being rebuilt.
> 
>  
> 
> Does anyone know how this can be avoided?
> 
>  
> 
> I have just patched python-sybase to use ASE 15.x capabilities (to allow 
> identifiers longer than 30 characters).  However, I still throw the 
> Sybase.DatabaseError exception shown above when executing “reorg rebuild”. 
> 
>  
> 
> The same exception was also thrown _/before/_ I patched python-sybase to 
> use ASE 15.x capabilities.
> 
>  
> 
> Does anyone know if this exception can be avoided somehow?  Thanks in 
> advance.
> 
>  
> 
> Regards,
> 
> Jeffrey
> 
> 
> ------------------------------------------------------------------------
> 
> ------------------------------------------------------------------------------
> Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day 
> trial. Simplify your report design, integration and deployment - and focus on 
> what you do best, core application coding. Discover what's new with 
> Crystal Reports now.  http://p.sf.net/sfu/bobj-july
> 
> 
> ------------------------------------------------------------------------
> 
> _______________________________________________
> Python-sybase-misc mailing list
> Pyt...@li...
> https://lists.sourceforge.net/lists/listinfo/python-sybase-misc

[Python-sybase-misc] Exception thrown executing "reorg rebuild"

[Jeffrey Z. <Jef...@q-...>]

I would like to execute "reorg rebuild" on the server.  Whenever I do
this, python-sybase throws a Sybase.DatabaseError exception as shown
here:

 

Traceback (most recent call last):

  File
"/home/jeffreyz/workspace-3.5/sybase-tests/src/connection_tests.py",
line 41, in <module>

    cursor.execute('reorg rebuild Client')        

  File "build/bdist.linux-i686/egg/Sybase.py", line 546, in execute

  File "build/bdist.linux-i686/egg/Sybase.py", line 695, in _start

  File "build/bdist.linux-i686/egg/Sybase.py", line 709, in _mainloop

  File "build/bdist.linux-i686/egg/Sybase.py", line 753, in _raise_error

Sybase.DatabaseError: Msg 11932, Level 10, State 1, Line 1

Beginning REORG REBUILD of table 'Client'.

Msg 1918, Level 10, State 1, Line 1

Non-clustered index (index id = 2) is being rebuilt.

 

Does anyone know how this can be avoided?

 

I have just patched python-sybase to use ASE 15.x capabilities (to allow
identifiers longer than 30 characters).  However, I still throw the
Sybase.DatabaseError exception shown above when executing "reorg
rebuild".  

 

The same exception was also thrown _before_ I patched python-sybase to
use ASE 15.x capabilities.

 

Does anyone know if this exception can be avoided somehow?  Thanks in
advance.

 

Regards,

Jeffrey 

Re: [Python-sybase-misc] 30-character limit for identifiers with ASE 15.x?

[Jeffrey Z. <Jef...@q-...>]

Thank you Sébastien,

I changed CS_VERSION_100 to CS_VERSION_150 in both:

    ctx.c
    sybasect.c

and then recompiled.  This fixed my problem.  There are no exceptions thrown now when executing a stored procedure that contains a local variable name longer than 30 characters.

Regards,
Jeffrey



============================================================
Hi Jeffrey,

I think this may be the same problem which was recently reported in 
other emails and which is also reported in this bug tracker:

http://sourceforge.net/tracker/?func=detail&aid=2809032&group_id=184050&atid=907701


python-sybase is currently specifying at initialization to Sybase that 
it wants sybase 10.0 capabilities.

You need to modify python-sybase in this way if you want to use 
capabilities of Sybase 15 :

Index: ctx.c
===================================================================
--- ctx.c	(révision 443)
+++ ctx.c	(copie de travail)
@@ -1083,7 +1083,7 @@
  	return NULL;
      }

-    version = CS_VERSION_100;
+    version = CS_VERSION_150;
      if (!PyArg_ParseTuple(args, "|i", &version))
  	return NULL;


It should be easy to write a small patch so that the maximum available 
version of CS_VERSION_* is detected at compilation time, so that 
python-sybase always use the capability of the version of Sybase it is 
compiled with. I will add that for python-sybase 0.40.

regards

--
Sébastien Sablé

Jeffrey Zelt a écrit :
> I have a problem where a Sybase.DatabaseError exception is thrown by the 
> python-sybase module when I execute a Sybase ASE 15.0.3 stored procedure 
> that contains a local variable with a name longer than 30-characters.
> 
>  
> 
> Here is exact error text:
> 
>  
> 
> Traceback (most recent call last):
> 
>   File 
> "/home/jeffreyz/workspace-3.5/generate-client-test-data/src/generate_data_run.py", 
> line 11, in <module>
> 
>     '--logging-stderr-on', '--logging-stderr-level', str(logging.DEBUG),
> 
>   File 
> "/home/jeffreyz/workspace-3.5/generate-client-test-data/src/generate_data.py", 
> line 415, in main
> 
>     generate_client(cursor, options)
> 
>   File 
> "/home/jeffreyz/workspace-3.5/generate-client-test-data/src/generate_data.py", 
> line 271, in generate_client
> 
>     '@op_AccountNumber': Sybase.OUTPUT(1)})
> 
>   File "build/bdist.linux-i686/egg/Sybase.py", line 418, in callproc
> 
>   File "build/bdist.linux-i686/egg/Sybase.py", line 695, in _start
> 
>   File "build/bdist.linux-i686/egg/Sybase.py", line 709, in _mainloop
> 
>   File "build/bdist.linux-i686/egg/Sybase.py", line 753, in _raise_error
> 
> Sybase.DatabaseError: Msg 103, Level 15, State 205, Line 13
> 
> The identifier that starts with '@tmp_HasFixedInitialCreditLimi' is too 
> long. Maximum length is 30.
> 
> Msg 103, Level 15, State 205, Line 14
> 
> The identifier that starts with '@tmp_HasFixedClearingCreditLim' is too 
> long. Maximum length is 30.
> 
> Msg 137, Level 15, State 1, Line 14
> 
> Must declare variable '@tmp_HasFixedClearingCreditLimi'.
> 
>  
> 
> Notice that it is complaining that the name of the local variable 
> '@tmp_HasFixedClearingCreditLimit' is too long.
> 
>  
> 
> This was a known problem with ASE 12.x.  However, I compiled the 
> python-sybase module on Ubuntu 9.04 against:
> 
>  
> 
>     Linux Express Edition for ASE 15.0.3
> 
>  
> 
> I have never installed a version of ASE 12.x, so I could not have 
> accidentally compiled against old libraries.  The server on which the 
> stack trace above was created was running ASE 15.0.3 on Fedora Linux.
> 
>  
> 
> Does anyone know why I triggered an exception for identifiers over 
> 30-characters when all libraries involved seem to be for Sybase ASE 15.x?
> 
>  
> 
> Is there a way to fix this problem so that no exception is thrown?
> 
>  
> 
> Regards,
> 
> Jeffrey
> 
>  
> 
> 
> ------------------------------------------------------------------------
> 
> ------------------------------------------------------------------------------
> Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day 
> trial. Simplify your report design, integration and deployment - and focus on 
> what you do best, core application coding. Discover what's new with 
> Crystal Reports now.  http://p.sf.net/sfu/bobj-july
> 
> 
> ------------------------------------------------------------------------
> 
> _______________________________________________
> Python-sybase-misc mailing list
> Pyt...@li...
> https://lists.sourceforge.net/lists/listinfo/python-sybase-misc

Re: [Python-sybase-misc] 30-character limit for identifiers with ASE 15.x?

[Cserna, Z. <Zso...@Mo...>]

Sorry if I generate some noise, but for me it was required to change the line 173 in sybasect.c as well:

Original:

  171 static PyObject *sybasect_cs_ctx_alloc(PyObject *module, PyObject *args)
  172 {
  173     int version = CS_VERSION_100;
  174
  175     if (!PyArg_ParseTuple(args, "|i", &version))
  176         return NULL;
  177     return ctx_alloc(version);
  178 }


It was required to gain access to wide tables (eg. tables containing varchar / char fields sized more than 255 chars).

Zsolt
  

> -----Original Message-----
> From: Sébastien Sablé [mailto:sa...@us...] 
> Sent: Thursday, August 27, 2009 18:26
> To: Jeffrey Zelt
> Cc: pyt...@li...
> Subject: Re: [Python-sybase-misc] 30-character limit for 
> identifiers with ASE 15.x?
> 
> Hi Jeffrey,
> 
> I think this may be the same problem which was recently 
> reported in other emails and which is also reported in this 
> bug tracker:
> 
> http://sourceforge.net/tracker/?func=detail&aid=2809032&group_
id=184050&atid=907701
> 
> 
> python-sybase is currently specifying at initialization to 
> Sybase that it wants sybase 10.0 capabilities.
> 
> You need to modify python-sybase in this way if you want to 
> use capabilities of Sybase 15 :
> 
> Index: ctx.c
> ===================================================================
> --- ctx.c	(révision 443)
> +++ ctx.c	(copie de travail)
> @@ -1083,7 +1083,7 @@
>   	return NULL;
>       }
> 
> -    version = CS_VERSION_100;
> +    version = CS_VERSION_150;
>       if (!PyArg_ParseTuple(args, "|i", &version))
>   	return NULL;
> 
> 
> It should be easy to write a small patch so that the maximum 
> available version of CS_VERSION_* is detected at compilation 
> time, so that python-sybase always use the capability of the 
> version of Sybase it is compiled with. I will add that for 
> python-sybase 0.40.
> 
> regards
> 
> --
> Sébastien Sablé
> 
> Jeffrey Zelt a écrit :
> > I have a problem where a Sybase.DatabaseError exception is 
> thrown by 
> > the python-sybase module when I execute a Sybase ASE 15.0.3 stored 
> > procedure that contains a local variable with a name longer 
> than 30-characters.
> > 
> >  
> > 
> > Here is exact error text:
> > 
> >  
> > 
> > Traceback (most recent call last):
> > 
> >   File
> > 
> "/home/jeffreyz/workspace-3.5/generate-client-test-data/src/generate_d
> > ata_run.py",
> > line 11, in <module>
> > 
> >     '--logging-stderr-on', '--logging-stderr-level', 
> > str(logging.DEBUG),
> > 
> >   File
> > 
> "/home/jeffreyz/workspace-3.5/generate-client-test-data/src/generate_d
> > ata.py",
> > line 415, in main
> > 
> >     generate_client(cursor, options)
> > 
> >   File
> > 
> "/home/jeffreyz/workspace-3.5/generate-client-test-data/src/generate_d
> > ata.py",
> > line 271, in generate_client
> > 
> >     '@op_AccountNumber': Sybase.OUTPUT(1)})
> > 
> >   File "build/bdist.linux-i686/egg/Sybase.py", line 418, in callproc
> > 
> >   File "build/bdist.linux-i686/egg/Sybase.py", line 695, in _start
> > 
> >   File "build/bdist.linux-i686/egg/Sybase.py", line 709, in 
> _mainloop
> > 
> >   File "build/bdist.linux-i686/egg/Sybase.py", line 753, in 
> > _raise_error
> > 
> > Sybase.DatabaseError: Msg 103, Level 15, State 205, Line 13
> > 
> > The identifier that starts with '@tmp_HasFixedInitialCreditLimi' is 
> > too long. Maximum length is 30.
> > 
> > Msg 103, Level 15, State 205, Line 14
> > 
> > The identifier that starts with '@tmp_HasFixedClearingCreditLim' is 
> > too long. Maximum length is 30.
> > 
> > Msg 137, Level 15, State 1, Line 14
> > 
> > Must declare variable '@tmp_HasFixedClearingCreditLimi'.
> > 
> >  
> > 
> > Notice that it is complaining that the name of the local variable 
> > '@tmp_HasFixedClearingCreditLimit' is too long.
> > 
> >  
> > 
> > This was a known problem with ASE 12.x.  However, I compiled the 
> > python-sybase module on Ubuntu 9.04 against:
> > 
> >  
> > 
> >     Linux Express Edition for ASE 15.0.3
> > 
> >  
> > 
> > I have never installed a version of ASE 12.x, so I could not have 
> > accidentally compiled against old libraries.  The server on 
> which the 
> > stack trace above was created was running ASE 15.0.3 on 
> Fedora Linux.
> > 
> >  
> > 
> > Does anyone know why I triggered an exception for identifiers over 
> > 30-characters when all libraries involved seem to be for 
> Sybase ASE 15.x?
> > 
> >  
> > 
> > Is there a way to fix this problem so that no exception is thrown?
> > 
> >  
> > 
> > Regards,
> > 
> > Jeffrey
> > 
> >  
> > 
> > 
> > 
> ----------------------------------------------------------------------
> > --
> > 
> > 
> ----------------------------------------------------------------------
> > -------- Let Crystal Reports handle the reporting - Free Crystal 
> > Reports 2008 30-Day trial. Simplify your report design, integration 
> > and deployment - and focus on what you do best, core application 
> > coding. Discover what's new with Crystal Reports now.  
> > http://p.sf.net/sfu/bobj-july
> > 
> > 
> > 
> ----------------------------------------------------------------------
> > --
> > 
> > _______________________________________________
> > Python-sybase-misc mailing list
> > Pyt...@li...
> > https://lists.sourceforge.net/lists/listinfo/python-sybase-misc
> 
> 
> --------------------------------------------------------------
> ----------------
> Let Crystal Reports handle the reporting - Free Crystal 
> Reports 2008 30-Day trial. Simplify your report design, 
> integration and deployment - and focus on what you do best, 
> core application coding. Discover what's new with Crystal 
> Reports now.  http://p.sf.net/sfu/bobj-july 
> _______________________________________________
> Python-sybase-misc mailing list
> Pyt...@li...
> https://lists.sourceforge.net/lists/listinfo/python-sybase-misc
> 
--------------------------------------------------------------------------
NOTICE: If received in error, please destroy, and notify sender. Sender does not intend to waive confidentiality or privilege. Use of this email is prohibited when received in error. We may monitor and store emails to the extent permitted by applicable law.

Re: [Python-sybase-misc] 30-character limit for identifiers with ASE 15.x?

[Sébastien S. <sa...@us...>]

Hi Jeffrey,

I think this may be the same problem which was recently reported in 
other emails and which is also reported in this bug tracker:

http://sourceforge.net/tracker/?func=detail&aid=2809032&group_id=184050&atid=907701


python-sybase is currently specifying at initialization to Sybase that 
it wants sybase 10.0 capabilities.

You need to modify python-sybase in this way if you want to use 
capabilities of Sybase 15 :

Index: ctx.c
===================================================================
--- ctx.c	(révision 443)
+++ ctx.c	(copie de travail)
@@ -1083,7 +1083,7 @@
  	return NULL;
      }

-    version = CS_VERSION_100;
+    version = CS_VERSION_150;
      if (!PyArg_ParseTuple(args, "|i", &version))
  	return NULL;


It should be easy to write a small patch so that the maximum available 
version of CS_VERSION_* is detected at compilation time, so that 
python-sybase always use the capability of the version of Sybase it is 
compiled with. I will add that for python-sybase 0.40.

regards

--
Sébastien Sablé

Jeffrey Zelt a écrit :
> I have a problem where a Sybase.DatabaseError exception is thrown by the 
> python-sybase module when I execute a Sybase ASE 15.0.3 stored procedure 
> that contains a local variable with a name longer than 30-characters.
> 
>  
> 
> Here is exact error text:
> 
>  
> 
> Traceback (most recent call last):
> 
>   File 
> "/home/jeffreyz/workspace-3.5/generate-client-test-data/src/generate_data_run.py", 
> line 11, in <module>
> 
>     '--logging-stderr-on', '--logging-stderr-level', str(logging.DEBUG),
> 
>   File 
> "/home/jeffreyz/workspace-3.5/generate-client-test-data/src/generate_data.py", 
> line 415, in main
> 
>     generate_client(cursor, options)
> 
>   File 
> "/home/jeffreyz/workspace-3.5/generate-client-test-data/src/generate_data.py", 
> line 271, in generate_client
> 
>     '@op_AccountNumber': Sybase.OUTPUT(1)})
> 
>   File "build/bdist.linux-i686/egg/Sybase.py", line 418, in callproc
> 
>   File "build/bdist.linux-i686/egg/Sybase.py", line 695, in _start
> 
>   File "build/bdist.linux-i686/egg/Sybase.py", line 709, in _mainloop
> 
>   File "build/bdist.linux-i686/egg/Sybase.py", line 753, in _raise_error
> 
> Sybase.DatabaseError: Msg 103, Level 15, State 205, Line 13
> 
> The identifier that starts with '@tmp_HasFixedInitialCreditLimi' is too 
> long. Maximum length is 30.
> 
> Msg 103, Level 15, State 205, Line 14
> 
> The identifier that starts with '@tmp_HasFixedClearingCreditLim' is too 
> long. Maximum length is 30.
> 
> Msg 137, Level 15, State 1, Line 14
> 
> Must declare variable '@tmp_HasFixedClearingCreditLimi'.
> 
>  
> 
> Notice that it is complaining that the name of the local variable 
> ‘@tmp_HasFixedClearingCreditLimit’ is too long.
> 
>  
> 
> This was a known problem with ASE 12.x.  However, I compiled the 
> python-sybase module on Ubuntu 9.04 against:
> 
>  
> 
>     Linux Express Edition for ASE 15.0.3
> 
>  
> 
> I have never installed a version of ASE 12.x, so I could not have 
> accidentally compiled against old libraries.  The server on which the 
> stack trace above was created was running ASE 15.0.3 on Fedora Linux.
> 
>  
> 
> Does anyone know why I triggered an exception for identifiers over 
> 30-characters when all libraries involved seem to be for Sybase ASE 15.x?
> 
>  
> 
> Is there a way to fix this problem so that no exception is thrown?
> 
>  
> 
> Regards,
> 
> Jeffrey
> 
>  
> 
> 
> ------------------------------------------------------------------------
> 
> ------------------------------------------------------------------------------
> Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day 
> trial. Simplify your report design, integration and deployment - and focus on 
> what you do best, core application coding. Discover what's new with 
> Crystal Reports now.  http://p.sf.net/sfu/bobj-july
> 
> 
> ------------------------------------------------------------------------
> 
> _______________________________________________
> Python-sybase-misc mailing list
> Pyt...@li...
> https://lists.sourceforge.net/lists/listinfo/python-sybase-misc

[Python-sybase-misc] 30-character limit for identifiers with ASE 15.x?

[Jeffrey Z. <Jef...@q-...>]

I have a problem where a Sybase.DatabaseError exception is thrown by the
python-sybase module when I execute a Sybase ASE 15.0.3 stored procedure
that contains a local variable with a name longer than 30-characters.

 

Here is exact error text:

 

Traceback (most recent call last):

  File
"/home/jeffreyz/workspace-3.5/generate-client-test-data/src/generate_dat
a_run.py", line 11, in <module>

    '--logging-stderr-on', '--logging-stderr-level', str(logging.DEBUG),

  File
"/home/jeffreyz/workspace-3.5/generate-client-test-data/src/generate_dat
a.py", line 415, in main

    generate_client(cursor, options)

  File
"/home/jeffreyz/workspace-3.5/generate-client-test-data/src/generate_dat
a.py", line 271, in generate_client

    '@op_AccountNumber': Sybase.OUTPUT(1)})

  File "build/bdist.linux-i686/egg/Sybase.py", line 418, in callproc

  File "build/bdist.linux-i686/egg/Sybase.py", line 695, in _start

  File "build/bdist.linux-i686/egg/Sybase.py", line 709, in _mainloop

  File "build/bdist.linux-i686/egg/Sybase.py", line 753, in _raise_error

Sybase.DatabaseError: Msg 103, Level 15, State 205, Line 13

The identifier that starts with '@tmp_HasFixedInitialCreditLimi' is too
long. Maximum length is 30.

Msg 103, Level 15, State 205, Line 14

The identifier that starts with '@tmp_HasFixedClearingCreditLim' is too
long. Maximum length is 30.

Msg 137, Level 15, State 1, Line 14

Must declare variable '@tmp_HasFixedClearingCreditLimi'.

 

Notice that it is complaining that the name of the local variable
'@tmp_HasFixedClearingCreditLimit' is too long.

 

This was a known problem with ASE 12.x.  However, I compiled the
python-sybase module on Ubuntu 9.04 against:

 

    Linux Express Edition for ASE 15.0.3

 

I have never installed a version of ASE 12.x, so I could not have
accidentally compiled against old libraries.  The server on which the
stack trace above was created was running ASE 15.0.3 on Fedora Linux.

 

Does anyone know why I triggered an exception for identifiers over
30-characters when all libraries involved seem to be for Sybase ASE
15.x?

 

Is there a way to fix this problem so that no exception is thrown?

 

Regards,

Jeffrey

 

Re: [Python-sybase-misc] Performance issues when upgrading from 0.38 to 0.39

[Sébastien S. <sa...@us...>]

Hi Zsolt,

thank you for the feedback on python-sybase.

the use of ct_cursor was made as a way to make python-sybase behave more 
like dbapi bridges for other databases when doing nested requests.

I used to have plenty of "This routine cannot be called because another 
command structure has results pending" error messages when porting to 
sybase some python DBAPI compliant code that was working fine with 
oracle, postgres or mysql.

There is an example illustrating the problem here:

http://www.object-craft.com.au/pipermail/python-sybase/2006-April/000467.html

Basically with Sybase, if you want to update some row while retrieving 
some rows with a cursor, you have to either fetch all the rows 
beforehand or use a ct_cursor instead of a ct_command.

Fetching all the rows with fetchall can be very time and memory 
consuming when dealing with a huge table.

Even though this mechanism with ct_cursor makes it more transparent to 
work with Sybase in the case of nested queries, it seems to be causing 
some trouble to you and other people in other cases.

The solution for a coming 0.40 version of python-sybase should probably 
  be to deactivate this mechanism by default, and to only use ct_cursor 
when it is explicitly specified.

In the meantime, you may apply the following patch to deactivate 
ct_cursor usage:

Index: Sybase.py
===================================================================
--- Sybase.py	(révision 443)
+++ Sybase.py	(copie de travail)
@@ -457,7 +457,7 @@
              if status != CS_SUCCEED:
                  break

-    def prepare(self, sql, select = None):
+    def prepare(self, sql, select = False):
          '''Prepare to retrieve new results.
          '''
          self._lock()
@@ -477,7 +477,7 @@
          finally:
              self._unlock()

-    def execute(self, sql, params = {}, select = None):
+    def execute(self, sql, params = {}, select = False):
          '''DB-API Cursor.execute()
          '''
          self._lock()


As far as performances are concerned, I made a few improvements in the 
subversion trunk (for example reuse the same ct_command/ct_curor when 
doing repeatedly the same request).

I have other performance improvements in the pipe which have not been 
commited yet (including rewriting some parts in cython/pyrex), but I 
didn't have the time recently to finish them since I had other 
priorities. I hope to be able to dedicate a few weeks to python-sybase 
in coming months to finish this and correct a few bugs which have been 
reported.

regards

--
Sébastien Sablé



Cserna, Zsolt a écrit :
> Hi all,
> 
> We've found performance drop in python-sybase when upgrading from 0.38 to 0.39.
> We run it on Linux, python version 2.5.4 and python-sybase is linked to 15.0.0.7 version of sybase client library.
> 
> The problem occur when we are fetching the results of a select query from the server. The difference between 0.38 and 0.39 is huge, we found 0.39 is about 10-100 times slower than 0.38.
> 
> Please find the test script attached (rename it to .py). There's no difference in what table we select, it could contain text or number, it may have only one column or more columns.
> 
> Usually we get the following output when using 0.38:
> Took 3.626094 seconds to fetch 10000 rows: 2757.788330 rows/second
> 
> And from 0.39:
> Took 647.513509 seconds to fetch 10000 rows: 15.443693 rows/second
> 
> If I replace the line calling execute() to the following:
> cursor.execute('SELECT top %d * FROM test_table' % rows, select=False)
> 
> ...then in 0.39 it becomes fast, getting similar results as in 0.38.
> It seems to me that using ct_cursor (which can be disabled by select=False) results the performance drop in 0.39.
> Is there any reason to use ct_cursor or any drawback of not using it?
> 
> Is this problem general? Or does it occur in our enviroment? 
> 
> Thank you in advance,
> Zsolt
> 
> Zsolt Cserna
> Morgan Stanley | IDEAS Practice Areas
> Lechner Odon fasor 8 | Floor 07
> Budapest, 1095
> Phone: +36 1 881-3968
> Zso...@Mo...
> 
> --------------------------------------------------------------------------
> NOTICE: If received in error, please destroy, and notify sender. Sender does not intend to waive confidentiality or privilege. Use of this email is prohibited when received in error. We may monitor and store emails to the extent permitted by applicable law.
> 
> 
> ------------------------------------------------------------------------
> 
> ------------------------------------------------------------------------------
> Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day 
> trial. Simplify your report design, integration and deployment - and focus on 
> what you do best, core application coding. Discover what's new with 
> Crystal Reports now.  http://p.sf.net/sfu/bobj-july
> 
> 
> ------------------------------------------------------------------------
> 
> _______________________________________________
> Python-sybase-misc mailing list
> Pyt...@li...
> https://lists.sourceforge.net/lists/listinfo/python-sybase-misc

[Python-sybase-misc] Performance issues when upgrading from 0.38 to 0.39

[Cserna, Z. <Zso...@Mo...>]

Hi all,

We've found performance drop in python-sybase when upgrading from 0.38 to 0.39.
We run it on Linux, python version 2.5.4 and python-sybase is linked to 15.0.0.7 version of sybase client library.

The problem occur when we are fetching the results of a select query from the server. The difference between 0.38 and 0.39 is huge, we found 0.39 is about 10-100 times slower than 0.38.

Please find the test script attached (rename it to .py). There's no difference in what table we select, it could contain text or number, it may have only one column or more columns.

Usually we get the following output when using 0.38:
Took 3.626094 seconds to fetch 10000 rows: 2757.788330 rows/second

And from 0.39:
Took 647.513509 seconds to fetch 10000 rows: 15.443693 rows/second

If I replace the line calling execute() to the following:
cursor.execute('SELECT top %d * FROM test_table' % rows, select=False)

...then in 0.39 it becomes fast, getting similar results as in 0.38.
It seems to me that using ct_cursor (which can be disabled by select=False) results the performance drop in 0.39.
Is there any reason to use ct_cursor or any drawback of not using it?

Is this problem general? Or does it occur in our enviroment? 

Thank you in advance,
Zsolt

Zsolt Cserna
Morgan Stanley | IDEAS Practice Areas
Lechner Odon fasor 8 | Floor 07
Budapest, 1095
Phone: +36 1 881-3968
Zso...@Mo...

--------------------------------------------------------------------------
NOTICE: If received in error, please destroy, and notify sender. Sender does not intend to waive confidentiality or privilege. Use of this email is prohibited when received in error. We may monitor and store emails to the extent permitted by applicable law.

[Python-sybase-misc] return status of a stored procedure (ASE 15.x)

[Jeffrey Z. <Jef...@q-...>]

I would imagine that those who read this list have some experience with
Sybase Transact-SQL programming.  I have a *very* simple question that I
posted earlier, but could not be answered.

 

When you execute a Sybase stored procedure on Sybase ASE, the call
returns a "return status" value.  This is an integer that you can test
to see if the procedure executed successfully.  Stored procedures can
return any value you like, so the return status is used as an error code
for a stored procedure.  This is standard Sybase stuff.

 

I desperately need to obtain this result status value after I call a
stored procedure with the Python-Sybase module using:

 

    cursor.callproc('procname', ...)

 

The result status is mentioned on the page:

 

    http://python-sybase.sourceforge.net/news.html

 

where it states (for v 0.37pre1):

 

    "The return value of Cursor.callproc() is now longer returned in the
result sets. 

     It is available in the .return_status Cursor member once all
results have been consumed."

 

However, when I execute cursor.callproc() the cursor object *never* has
a "return_status" member (before or after I read the result set).
Perhaps I can access the return status in another way?

 

I am using the latest python-sybase module (v0.39) on Ubuntu 9.04 Linux.
I compiled this module using the free "ASE Express Edition for Linux".

 

It it absolutely necessary to obtain this result status value;
otherwise, there is no way to know if the stored procedure returned an
error or not.

 

If there is no way to access this value using the Python-Sybase module,
I would appreciate hearing this.  If it is a known bug, I would also
like to hear this. 

 

 I don't want to sound overly negative, but does anyone actually use
this module for calling stored procedures?  If so, how do you obtain the
result status?  It is such a simple question and I cannot find the
answer.  One could choose to signal errors in another way that does not
require that  the "return status" be known, but I am working with a
Sybase database application that has already been written.  And the
standard way to return error codes from any Sybase stored procedure is
via its return status.

 

I realize that this is the second time I have posted this question.
Please bear with me.  I hope that this time someone can help.

 

Kind regards,

Jeffrey 

 

Re: [Python-sybase-misc] How to set Sybase sets/tuples from Python?

[Deron M. <der...@gm...>]

> On Tue, Aug 18, 2009 at 10:37 AM, Nick Edds <ne...@gm...> wrote:
>> I was under the impression that repeat use of a query was for performance
>> given this in the documentation of execute:
>>
>> The prepared dynamic SQL will be reused by the cursor if the same SQL is
>> passed in the sql argument. This is most effective for algorithms where the
>> same operation is used, but different parameters are bound to it (many
>> times).

Well, this is technically a property of the specific database you're using.

I don't know that much really about Sybase or SQL Server.
In Oracle this is true, although the newest releases have made this
performance penalty much less.  In MySQL there's no practical
difference.

However, still, from my examples we have:

  SQL = "select foo from bar where baz in (@item0, @item1, @item2)"
  ARGS = {'@item0': 'A', '@item1': 'B', '@item2': 'C'}

That's still using substitution, which for those databases which
matter, will allow the prepared statement to be reused (for any
set of values with the same number of items).

True, if you had another query that had four items rather than three
that may require another prepare.  But it's still much better than
using embedded literals in which case you could never reuse the
same statement even for ('A','B','C') and ('X','Y','Z').

Though I'm not sure that any database supports substitutions on more
than individual (literal) values anyway; e.g., you can't substitute an
entire set for an IN clause.  (Any Sybase experts which to chime in?)


>> If the parameters are not being used for performance though, how is your
>> solution superior to the naive:
>> query = 'select foo from bar where baz in %s'
>> types = ('A', 'B', 'C')
>> cursor.execute(query % str(types))

For this example either way is fine.  However what you're doing
is building the SQL with embedded string literals.  What I
showed still used the substitution mechanism.

True, the string formatting is way is clearer, but it's also
quite fragile.  What if you have a one-tuple:
   types = ('A',).
Then you get the invalid SQL:
   .... IN ('A',)
or if types was say something other than a tuple, say
   types = ['A','B','C']
or
   types = set(['A','B','C'])


You also don't get the advantage of correct escaping.  What if
you had types = ('A\'s', 'B') -- then it would break (a possible
SQL injection attack vector).

You also don't get the type conversions; e.g.,
  types = ('A', None, u'Z\u2012', datetime.date(2009,9,18))
etc....


>> If the performance gains are of significance from repeat use of a query,
>> your solution has the advantage that the cursor will reuse the prepared
>> dynamic SQL for the 2nd+ time using any particular number of items.

Well, we really need a Sybase expert to weigh in here, which is
not me.  I can tell you that on Oracle it may make some difference,
but probably not much, especially on newer versions.  On MySQL its
a wash.
-- 
Deron Meranda

Re: [Python-sybase-misc] How to set Sybase sets/tuples from Python?

[Nick E. <ne...@gm...>]

That should of course be:
cursor.execute(query % str(types))

On Tue, Aug 18, 2009 at 10:37 AM, Nick Edds <ne...@gm...> wrote:

> I was under the impression that repeat use of a query was for performance
> given this in the documentation of execute:
>
> The prepared dynamic SQL will be reused by the cursor if the same SQL is
> passed in the sql argument. This is most effective for algorithms where
> the same operation is used, but different parameters are bound to it (many
> times).
>
> If the parameters are not being used for performance though, how is your
> solution superior to the naive:
> query = 'select foo from bar where baz in %s'
> types = ('A', 'B', 'C')
> cursor.execute(query % types)
>
> which is more transparent.
>
> If the performance gains are of significance from repeat use of a query,
> your solution has the advantage that the cursor will reuse the prepared
> dynamic SQL for the 2nd+ time using any particular number of items.
>
> Nick
>
>
>
> On Tue, Aug 18, 2009 at 10:27 AM, Deron Meranda <der...@gm...>wrote:
>
>> On Tue, Aug 18, 2009 at 11:19 AM, Nick Edds<ne...@gm...> wrote:
>> > But what if later you want to run the query with items = ['A', 'B', 'C',
>> > 'D']?
>> >
>> > Then you're going to need to construct a new query because the original
>> > query can only support 3 items., so you lose the performance gain of
>> making
>> > a query that takes parameters. I can't really see a better solution
>> though.
>>
>> Constructing the query and args dict using the list comprehension
>> methods I showed will equally work for any sized list of items (>=1)
>> without changing any python code.
>>
>> As far as I'm aware the substitution mechanism in the DBI is not
>> there for performance; but is primarily there to make it easier to use,
>> to facilitate automatic type conversions, and to help prevent mistakes
>> in escaping and quoting SQL literals.
>>
>> I suspect any performance loss or gain will be negligible.
>> --
>> Deron Meranda
>>
>
>

Re: [Python-sybase-misc] How to set Sybase sets/tuples from Python?

[Nick E. <ne...@gm...>]

I was under the impression that repeat use of a query was for performance
given this in the documentation of execute:

The prepared dynamic SQL will be reused by the cursor if the same SQL is
passed in the sql argument. This is most effective for algorithms where the
same operation is used, but different parameters are bound to it (many
times).

If the parameters are not being used for performance though, how is your
solution superior to the naive:
query = 'select foo from bar where baz in %s'
types = ('A', 'B', 'C')
cursor.execute(query % types)

which is more transparent.

If the performance gains are of significance from repeat use of a query,
your solution has the advantage that the cursor will reuse the prepared
dynamic SQL for the 2nd+ time using any particular number of items.

Nick


On Tue, Aug 18, 2009 at 10:27 AM, Deron Meranda <der...@gm...>wrote:

> On Tue, Aug 18, 2009 at 11:19 AM, Nick Edds<ne...@gm...> wrote:
> > But what if later you want to run the query with items = ['A', 'B', 'C',
> > 'D']?
> >
> > Then you're going to need to construct a new query because the original
> > query can only support 3 items., so you lose the performance gain of
> making
> > a query that takes parameters. I can't really see a better solution
> though.
>
> Constructing the query and args dict using the list comprehension
> methods I showed will equally work for any sized list of items (>=1)
> without changing any python code.
>
> As far as I'm aware the substitution mechanism in the DBI is not
> there for performance; but is primarily there to make it easier to use,
> to facilitate automatic type conversions, and to help prevent mistakes
> in escaping and quoting SQL literals.
>
> I suspect any performance loss or gain will be negligible.
> --
> Deron Meranda
>

Re: [Python-sybase-misc] How to set Sybase sets/tuples from Python?

[Deron M. <der...@gm...>]

On Tue, Aug 18, 2009 at 11:19 AM, Nick Edds<ne...@gm...> wrote:
> But what if later you want to run the query with items = ['A', 'B', 'C',
> 'D']?
>
> Then you're going to need to construct a new query because the original
> query can only support 3 items., so you lose the performance gain of making
> a query that takes parameters. I can't really see a better solution though.

Constructing the query and args dict using the list comprehension
methods I showed will equally work for any sized list of items (>=1)
without changing any python code.

As far as I'm aware the substitution mechanism in the DBI is not
there for performance; but is primarily there to make it easier to use,
to facilitate automatic type conversions, and to help prevent mistakes
in escaping and quoting SQL literals.

I suspect any performance loss or gain will be negligible.
-- 
Deron Meranda

Re: [Python-sybase-misc] How to set Sybase sets/tuples from Python?

[Nick E. <ne...@gm...>]

But what if later you want to run the query with items = ['A', 'B', 'C',
'D']?

Then you're going to need to construct a new query because the original
query can only support 3 items., so you lose the performance gain of making
a query that takes parameters. I can't really see a better solution though.

Nick

On Tue, Aug 18, 2009 at 9:57 AM, Deron Meranda <der...@gm...>wrote:

> On Tue, Aug 18, 2009 at 10:51 AM, Deron Meranda<der...@gm...>
> wrote:
> >    items = ["A", "B", "C"]
> >    qitems = ", ".join( [ "@item%d" % n for n in range(len(items)) ] )
> >    query = "select foo from bar where baz in (" + qitems + ")
> >    args = dict( [ ("@item%d" % n, v) for n, v in enumerate(items) ] )
>
> Oops, I dropped a missing quote character on the query= line above.
>
> Oh, what the complicated-looking list comprehensions give you is
> SQL which looks like:
>
>   select foo from bar where baz in (@item0, @item1, @item2)
>
> and an argument dictionary that is like:
>
>   {'@item0': 'A', '@item1': 'B', '@item2': 'C'}
>
>
> Though it will automatically handle any number of members
> in the items list.
> --
> Deron Meranda
>
>
> ------------------------------------------------------------------------------
> Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day
> trial. Simplify your report design, integration and deployment - and focus
> on
> what you do best, core application coding. Discover what's new with
> Crystal Reports now.  http://p.sf.net/sfu/bobj-july
> _______________________________________________
> Python-sybase-misc mailing list
> Pyt...@li...
> https://lists.sourceforge.net/lists/listinfo/python-sybase-misc
>

Re: [Python-sybase-misc] How to set Sybase sets/tuples from Python?

[Deron M. <der...@gm...>]

On Tue, Aug 18, 2009 at 10:51 AM, Deron Meranda<der...@gm...> wrote:
>    items = ["A", "B", "C"]
>    qitems = ", ".join( [ "@item%d" % n for n in range(len(items)) ] )
>    query = "select foo from bar where baz in (" + qitems + ")
>    args = dict( [ ("@item%d" % n, v) for n, v in enumerate(items) ] )

Oops, I dropped a missing quote character on the query= line above.

Oh, what the complicated-looking list comprehensions give you is
SQL which looks like:

   select foo from bar where baz in (@item0, @item1, @item2)

and an argument dictionary that is like:

   {'@item0': 'A', '@item1': 'B', '@item2': 'C'}


Though it will automatically handle any number of members
in the items list.
-- 
Deron Meranda