[egenix-users] Two bugs in mxODBC: storing empty string and unicode string

Harri Pesonen harri.pesonen at wicom.com
Mon Aug 13 16:19:09 CEST 2007


I tested with mxODBC 3, and 1) has been fixed. In mxODBC 2, it used
char(0) datatype internally, which was converted to char(8000) by SQL
Server ODBC driver. In mxODBC 3, it uses varchar(1) with empty string,
which works correctly. It does not add any extra spaces.

2) is still a problem in mxODBC3. It still uses char instead of varchar
for non-empty strings, so that I get "@P5 nchar(40)" and data
"N'CustomerAgentRedial1                    '". If mxODBC would use
nvarchar(40), then SQL Server ODBC driver would not add spaces to the
end. Of course this bug could be fixed by using the correct character
count in the first place.

So both problems happened because char was used instead of varchar. Char
requires that there will be spaces in the end, there have to be exactly
as many characters in the string. With empty string, char(0) was
converted to char(8000) by the SQL Server driver because there is no
such data type as char(0).

Using
connection.bindmethod = mx.ODBC.Windows.BIND_USING_PYTHONTYPE
does not seem to have any effect.

Thanks, Harri

-----Original Message-----
From: M.-A. Lemburg [mailto:mal at egenix.com] 
Sent: 13. elokuuta 2007 14:57
To: Harri Pesonen
Cc: egenix-users at lists.egenix.com
Subject: Re: [egenix-users] Two bugs in mxODBC: storing empty string and
unicode string

On 2007-08-13 11:38, Harri Pesonen wrote:
> We are currently using version 2 of mxODBC, but there are a couple of
problems:
>  
> 1) Empty string "" is converted to maximum length string
(varchar(8000) or
> nvarchar(4000)) when data is stored into database.

mxODBC itself does not do any such conversion. It is possible, however,
that the ODBC driver you are using applies such an conversion.

Note that on SQL Server char columns are padded with spaces up to the
size given in the column definition. This should happen with varchar
columns.

> 2) Unicode string u"abc" is converted to u"abc   ", so that there are
equal
> number of extra spaces in the end, when data is stored into database.
>  
> We are using SQL Server 2005 and Python 2.4.4 (and Windows XP). Both
of these
> problems must be worked around somehow, but the solution is not easy.
>  
> 1) We use string with 1 character (space) or None (NULL). The problem
is because
> there is no such datatype as char(0). varchar(0) would work.
> 2) Workaround not yet found. Except not to use unicode. It is clear
that mxODBC
> calculates first the number of bytes, and then uses that as number of
> characters, which is twice too much.
>  
> I don't know if these problems are fixed in the latest release, mxODBC
3.

We'll send you an eval license so that you can check this.

Please note that you should always try to use the latest available ODBC
driver for SQL Server. Because mxODBC uses SQL type binding mode
whenever possible to achieve best possible performance (the ODBC tells
mxODBC which data type to use), the quality of the ODBC matters a lot.

You can also force Python type binding mode by setting

connection.bindmethod = mx.ODBC.Windows.BIND_USING_PYTHONTYPE

The Python type then determines how mxODBC interfaces data to the
ODBC driver.

-- 
Marc-Andre Lemburg
eGenix.com

Professional Python Services directly from the Source  (#1, Aug 13 2007)
>>> 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



More information about the egenix-users mailing list