[egenix-users] unicode, sql command, mxODBC

[M.-A. Lemburg]

zhihua ouyang wrote:
> Hi everyone,
>    I need your help. I have tried for a while to make
> unicoded sql command work with mxODBC in window
> xp(Chinese Version) but failed since some unicode
> chinese characters are in it. 
> 
> Here is my case:
>   1.  A Excel file in c:\\test.xls which contains
> chinese characters.
>   2. I got the data from test.xls using the following
> code
> 
>      from win32com.client import DispatchEx
>      xApp = DispatchEx("Excel.Application")
>      xBook = xApp.Workbooks.Open('c:\\test.xls')
>      sheet = xBook.Worksheets('sheet1')
>      aaa = []
>      for i in range(10): 
>        aaa.append(sheet.Cell(5,i).Value)              
>       ......
> 
> and generate sql command dynamically with those
> chinese data. The following is an example:
> 
>      sqlCommandStr = u"INSERT INTO instruments VALUES
> ('0401', 23800.0,
> 0.0,'\u4e2d\u56fd','156','2000-1-1','\u5f20\u6167\u82ac');"
> 
> But mxODBC gives error info: "sql command must be a
> string".
> 
>    If chinese characters are inserted into the sql
> command like 
>      "INSERT INTO instruments VALUES ('0401', 23800.0,
> 0.0,'²âÊÔ','156','2000-1-1','²âÊÔ');"
>    everything is fine. But I don't know how to
> generate it in above condition.
> 
>     Does mxODBC accept the unicoded sql command?

No; SQL commands can however be encoded using the encoding
supported by the database engine (you usually have to set this
up in the database configuration). Using UTF-8 usually goes a
long way and is supported by most database backends allowing
Unicode to be stored in the database.

Another strategy is passing in Unicode as bound parameter.
This is support by mxODBC and works well with SQL Server and
Access.

You'd have to rewrite you execute command using bound
parameters:

cursor.execute('insert into tablename values (?,?,?)',
                (123, u'name', u'text'))

There's a catch though:

For backwards compatiblity reasons, mxODBC runs in 8-bit only
mode if not told otherwise. To have mxODBC connections run
in native Unicode mode you have to set them up using the
.stringformat attribute:

     connection = mx.ODBC.Windows.DriverConnect(...)
     connection.stringformat = mx.ODBC.Windows.NATIVE_UNICODE_STRINGFORMAT
     # or
     connection.stringformat = mx.ODBC.Windows.MIXED_STRINGFORMAT

For more information, have a look at the data types section
and the connection.stringformat documentation:

        http://www.egenix.com/files/python/mxODBC.html#Datatypes

-- 
Marc-Andre Lemburg
eGenix.com

Professional Python Services directly from the Source  (#1, Nov 10 2004)
 >>> 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 ! ::::