[egenix-users] SQL Server 2000, mxODBC and unicode problem

[M.-A. Lemburg]

Marcin wrote:
> Hi,
> 
> I recently found mxODBC lib and I decided to use it with MS SQL
> Server. It worked fine until i tried to use ntext fields and short
> INSERT syntax (INSERT INTO tab VALUES(val1, ...)).
> The following script:
> <CODE>
> from mx.ODBC.Windows import *
> 
> db = connect('DSN', 'login', 'pass')
> db.stringformat = UNICODE_STRINGFORMAT
> c = db.cursor()
> 
> c.execute('create table zxcvb (x ntext)')
> c.execute('insert into zxcvb values(?)', (None,))
> c.execute('insert into zxcvb values(?)', (u'anything',))
> </CODE>
> 
> raises exception in last line and returns:
> 
> Traceback (most recent call last):
>   File "test.py", line 15, in ?
>     c.execute('insert into zxcvb values(?)', (u'anything',))
> mxODBC.ProgrammingError: ('37000', 257, '[Microsoft][ODBC SQL Server Driver][SQL Server]Implicit conversion from data type ntext to char is not allowed. Use the CONVERT function to run this query.', 4579)
> 
> But it's enough to change last line to:
> c.execute('insert into zxcvb (x) values(?)', (u'anything',))
> or use other cursor than used with None insert, but this sucks.
> Is it my, server, driver or mODBC bug?

This sounds a lot like a driver/server issue.

Could you try to execute the two inserts on two different
cursors and see whether that makes a difference ? (ie.
simply insert c = db.cursor() between the two .execute()
calls)

It is possible that the driver is doing some sort of
caching of the column types based on what you pass in
as value. Since None is passed in as NULL it is possible
that the caching mechanism defaults to the wrong type.

Note that ntext touches two areas in ODBC (and other
DB interface mechanisms) that often cause problems:

1. it is a long text field capable of storing huge
    amounts of data and thus needs special treatment

2. it uses Unicode for storage which is rather new
    in the database world (although support is getting
    a lot better recently)

> Client specs:
> Windows XP Home, Python 2.4, mx-base 2.0.6, mxODBC 2.0.7, MS SQL
> Server ODBC Driver v.2000.81.9042.00 (2003-10-27)
> 
> Server specs:
> OS: Windows XP Home
> SQL: MS SQL Server 2000 Trial v.8.00.194
> 

-- 
Marc-Andre Lemburg
eGenix.com

Professional Python Services directly from the Source  (#1, Jan 04 2005)
 >>> Python/Zope Consulting and Support ...        http://www.egenix.com/
 >>> mxODBC.Zope.Database.Adapter ...             http://zope.egenix.com/
 >>> mxODBC, mxDateTime, mxTextTools ...        http://python.egenix.com/
________________________________________________________________________
2004-12-06: Released eGenix mx Extensions for Python 2.4

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