[DB-SIG] python connection to Velocis Database

[Susan]

Marc-Andre, good to know but given what I know about the company I'm
doing this work for, that would be my last resort.

Vernon, "a bit light" is a bit of an under statement. :)  adodbapi
looks somewhat better..but I think the problem lies with the vendor's
odbc driver and what "should work" doesn't return any errors, useful
or otherwise -- it just doesn't return any useful data where there
should have been.

On Wed, Dec 9, 2009 at 3:49 AM, Vernon Cole <vernondcole at gmail.com> wrote:
> Susan:
>
> That's not a very good error message, it is true.
>
> I suspect that your vendor's ODBC driver may be a bit light on diagnostics.
>
> To extract the most information from the exception, try something like this:
> v v v v v start Python code v v v v v
> from win32com.client import Dispatch
> import pywintypes
> conn = Dispatch(r'ADODB.Connection')
> def printADOerrors(adoConn):
>         j=adoConn.Errors.Count
>         if j:
>             print 'ADO Errors:(%i)' % j
>         for e in adoConn.Errors:
>             print 'Description: %s' % e.Description
>             print 'Number: %s ' % e.Number
>             try:
>                 print 'Number constant:' % adoErrors[e.Number]
>             except:
>                 pass
>             print 'Source: %s' %e.Source
>             print 'NativeError: %s' %e.NativeError
>             print 'SQL State: %s' %e.SQLState
> print '...Testing Sql login...'
> conn.Open('DSN=test')
> recset, affected = conn.Execute('select blah from foo')  # this should work
> print 'affected=',affected
> try:
>     recset, affected = conn.Execute('select blah from xxxx') # this should
> fail
> except pywintypes.com_error:
>     printADOerrors(conn)
> conn.Close()
> ^ ^ ^ ^ ^ ^ ^ ^ end code ^ ^ ^ ^ ^
>
> I tested this script on my system (The DSN "test" points to my MySQL server)
> and got the following:
> v v v v v v console output v v v v v
> ...Testing Sql login...
> affected= 1
> ADO Errors:(1)
> Description: [MySQL][ODBC 5.1 Driver][mysqld-5.0.75-0ubuntu10.2]Table
> 'test.xxxx' doesn't exist
> Number: -2147217865
> Source: Microsoft OLE DB Provider for ODBC Drivers
> NativeError: 1146
> SQL State: S0002
> ^ ^ ^ ^ ^ ^ ^
> which is much easier to understand.
>
> With some luck you too may get something readable.
>
> By the way, you can get the exact same results by:
> v v v v v v v v
> import adodbapi
> print '...Testing Sql login...'
> conn = adodbapi.connect('DSN=test')
> crsr = conn.cursor()
> crsr.execute('select blah from foo') # this should work
> print 'affected=',crsr.rowcount
> try:
>     crsr.execute('select blah from xxxx') # this should fail
> except conn.DatabaseError:
>     conn.printADOerrors()
> conn.close()
> ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^e
> Which is, IMHO, somewhat easier.
> --
> Vernon
>
>
>
>