Trap exceptions from SQL Server

[GoogleCodeExporter]

I'm having trouble trapping exceptions returned from pyodbc cursor
execute calls. Maybe I am referencing the wrong exceptions ???? I have
googled around quite a bit and it seems my code should
work...Basically, I have tried trapping all the error codes, but none
of them work. I'm connected to MS SQL Server database.

conn = pyodbc.connect( 'dsn=MyDSN')
cur = conn.cursor()
try:
     cur.execute ( 'SelectJJJJJ * from Test')
except pyodbc.Error:
     print 'Error exc'
except pyodbc.DataError:
     print 'Data Error'
except pyodbc.DatabaseError:
     print 'Database error'
..... catch rest of the errors......
except:
     print 'Why did I get here!!!!! %s' % sys.exc_info()

I always end up with 'Why did I get here!!!!!!!'
(<type 'instance'>, <pyodbc.ProgrammingError instance at 0xb7d0e32c>,
<traceback object at 0xb7d0fb1c>) 

python 2.4.3
RedHat 2.6.18-128.elxen
pyodbc 2.1.4
easySoft ODBC driver for SQL Server 1.1.4

Thanks

Original issue reported on code.google.com by yeeg...@gmail.com on 6 Mar 2009 at 11:28

[GoogleCodeExporter]

It looks like you've answered the question already: catch 
pyodbc.ProgrammingError

pyodbc uses the errors as defined by the DB API: 
http://www.python.org/peps/pep-0249.html

It uses SQLSTATE codes to determine which exception class to raise, as shown in 
the
table on the Errors page: http://code.google.com/p/pyodbc/wiki/Errors

In your case the error is a 24xxx, 25xxx, or 42xxx error.  You can find the 
SQLSTATEs
for each ODBC function (SQLExecute or SQLExecDirect in your case) in the 
Microsoft
documentation.

Original comment by mkleehammer on 9 Mar 2009 at 6:56

• Changed state: Invalid

[GoogleCodeExporter]

[deleted comment]

[GoogleCodeExporter]

In my example it looks like it raises a pyodbc.ProgrammingError....but why 
can't I
trap it, is there something wrong with my code ???......I think I'm still 
missing
something....My example below should output 'Error Programming Error'

conn = pyodbc.connect( 'dsn=MyDSN')
cur = conn.cursor()
try:
     cur.execute ( 'SelectJJJJJ * from Test')
except pyodbc.ProgrammingError:
     print 'Error Programming Error'

Original comment by yeeg...@gmail.com on 9 Mar 2009 at 8:31

[GoogleCodeExporter]

This is related to an error reported on the 2.0.x branch that actually has 
something
to do with raising an instance vs. class on Python 2.4.

Your test program works fine in Python 2.5 and you can even catch pyodbc.Error 
since
it is the base class for all pyodbc errors.

On Python 2.4, I get:
Traceback (most recent call last):
  File "test.py", line 20, in ?
    print 'args:', ex.args
SystemError: 'finally' pops bad exception

I have notes on the issue, though it wasn't completely resolved satisfactorily. 
 I'm
pretty sure I can fix this soon.

Original comment by mkleehammer on 9 Mar 2009 at 10:22

• Changed state: Investigating

[GoogleCodeExporter]

Thanks for the quick response!!!!
I think you are on the right about the instance versus class

Original comment by yeeg...@gmail.com on 10 Mar 2009 at 4:48

[GoogleCodeExporter]

Fixed in f98eac65ce01ccac56711293ade0c30d4c4fa39d, which will be in 2.1.5 as 
soon as
I release it.

Apparently *neither* solution was correct by itself -- you have to sometimes use
in_class and sometimes the type.  Python 2.6 has PyExceptionInstance_Class 
exactly
for this.  Since we also build for 2.4 I simply did the same thing the macro 
does.

Original comment by mkleehammer on 10 Mar 2009 at 6:23

• Changed state: Fixed

[GoogleCodeExporter]

Original comment by mkleehammer on 21 Nov 2010 at 4:44

• Changed state: Complete