[egenix-users] Newlines and VARBINARY

[M.-A. Lemburg]

On 2008-05-14 08:37, Alexandre Pigolkine wrote:
> Hello all,
> 
> I try to use mx.ODBC in the following environment:
> mx.ODBC, Linux, iODBC, FreeTDS-Library, Microsoft SQL Server.
> 
> I get an error message when I try to insert a string with
> new lines into a table with a TEXT field:
> 
> import mx.ODBC
> con = mx.ODBC.iODBC.DriverConnect("DRIVER={SQL Server};SERVER=<server_ip>;DATABASE=<dbname>;UID=<user>;PWD=<pwd>")

Just a note: it is usually better to define an ODBC data source
in /etc/odbc.ini with all the connection details and then use
"DSN=datasourcename;UID=uid;PWD=pwd" in your application.

> cur = con.cursor()
> cur.execute("CREATE TABLE #test (t text)")
> cur.close()
> cur = con.cursor()
> cur.execute("INSERT INTO #test(t) VALUES(?)", ('test\n',))
> cur.close()
> con.close()
> 
> /etc/odbcinst.ini:
> [ODBC Drivers]
> TDS MSSQL = installed
> 
> [SQL Server]
> Description = TDS MSSQL ODBC Driver v 4.2
> Driver = /usr/local/lib/libtdsodbc.so

Did you also configure TDS to use the right TDS version for
talking to the server ?

BTW: Which versions of iODBC, FreeTDS and SQL Server are
you using ?

> after the line cur.execute("INSERT ... ") I get an error:
>>>> cur.execute("INSERT INTO #test(t) VALUES(?)", ('test\n',))
> Traceback (most recent call last):
>   File "<stdin>", line 1, in ?
> mx.ODBC.Error.ProgrammingError: ('22018', 206, '[FreeTDS][SQL Server]Operand type clash: varbinary is incompatible with text', 7028)

Some background:

FreeTDS uses the Python type binding of mxODBC where it looks
at the types you pass in to bind them to the ODBC driver (as
opposed to the SQL type binding where mxODBC uses the type
information from the ODBC driver).

In order to check whether to bind a variable as character or
binary data, mxODBC scans the data and then decides based on
its findings whether to use VARCHAR or VARBINARY.

Now, according to the MS docs, character data is either:

* Uppercase and lowercase characters such as a, b, and C.
* Numerals such as 1, 2, and 3.
* Special characters such as the "at" sign (@), ampersand (&), and exclamation point (!).

(see http://msdn.microsoft.com/en-us/library/ms175055.aspx)

The test we are applying therefore checks for character data
to be in the range [0x20 ... 0x7f].

The newline character 0x10 is not included in that range, which
is why mxODBC sends such data as VARBINARY.

> Is there a way to avoid such behavior and force mxODBC to always pass 
> strings as a varchar-type?

Please check whether you see the same problem when using longer
strings, e.g. ones with 500 bytes.

We'll investigate this a bit more and if needed issue a patch
release to address the problem.

Thanks,
-- 
Marc-Andre Lemburg
eGenix.com

Professional Python Services directly from the Source  (#1, May 14 2008)
 >>> 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,MacOSX for free ! ::::


    eGenix.com Software, Skills and Services GmbH  Pastor-Loeh-Str.48
     D-40764 Langenfeld, Germany. CEO Dipl.-Math. Marc-Andre Lemburg
            Registered at Amtsgericht Duesseldorf: HRB 46611