[egenix-users] errors using mxODBC with unixODBC to connect to MSSQL

[M.-A. Lemburg]

Erik Myllymaki wrote:
> I am using a script that's worked for me in the past on Windows, but
> now that i've moved it to a Linux machine it is not. The trouble seems
> to be when trying to insert escaped characters into a ntext field
> (\n \r ,etc.).
> 
> ----------------------------------------------------------------------------- 
> 
> # works on Windows using this connect method
> # conn = mx.ODBC.WINDOWS.DriverConnect('DSN=myDSN;UID=sa;PWD=pwd')
> 
> conn = mx.ODBC.unixODBC.DriverConnect('DSN=myDSN;UID=sa;PWD=pwd')
> curr = conn.cursor()
> 
> # These strings do not work:
> # mystring = "Some text \n and some other text"
> # mystring = "Some text \t and some other text"
> # mystring = """Some text
> and some other text"""
> 
> # This string works just fine:
> mystring = "Some text and some other text"
> 
> sql_insert = "insert into DEV..msg(message_id,body) values(?,?)"
> 
> curr.execute(sql_insert, (1,mystring))
> curr.close()
> conn.commit()
> ----------------------------------------------------------------------------- 
> 
> 
> Here's the error message:
> 
> Error Type: OperationalError
> Error Value: ('', 8179, '[unixODBC][FreeTDS][SQL Server]Could not find
> prepared statement with handle 0.', 6083)
> 
> Any ideas greatly appreciated.

This sounds a lot like a bug in the FreeTDS ODBC driver. It is
possible that it tries to embed the string value in the
SQL command and stumbles over the non-text parts of the
string, e.g. forgets to quote them properly.

A look at the odbc call trace would help find out.

In order to enable logging at the ODBC driver manager level,
please follow these instructions:


1. edit the odbc.ini file that defines the data source
    you are using (usually /etc/odbc.ini or ~/.odbc.ini)

2. add two trace options to the data source in question:

[MyDataSourceName]
Trace = On
TraceFile = /tmp/odbc.log
...

3. restart Python and run a query on the connection

You should then see the /tmp/odbc.log file fill up with
ODBC call trace messages. Error messages from the ODBC
driver should also appear in this file.

In some cases you also have to edit the file
/etc/odbcinst.ini (or ~/.odbcinst.ini) and add a section:

[ODBC]
Trace    = On
TraceFile= /tmp/odbc.log

-- 
Marc-Andre Lemburg
eGenix.com

Professional Python Services directly from the Source  (#1, Apr 03 2006)
 >>> Python/Zope Consulting and Support ...        http://www.egenix.com/
 >>> mxODBC.Zope.Database.Adapter ...             http://zope.egenix.com/
 >>> mxODBC, mxDateTime, mxTextTools ...        http://python.egenix.com/
________________________________________________________________________

::: Try mxODBC.Zope.DA for Windows,Linux,Solaris,FreeBSD for free ! ::::