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

Harri Pesonen harri.pesonen at wicom.com
Tue Aug 14 13:28:30 CEST 2007


Great! How about mxODBC 2, is it supported at all, I can't find the
download anymore. I guess that we need upgrade: "If you'd like to
upgrade your mxODBC 2.0 licenses to version 3.0, please contact our
sales team."

-- Harri 

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

Some more investigation showed that even though the type binding
was set to SQL (the ODBC driver providing the type information),
when using .executedirect() the driver does not provide the
type information. mxODBC uses Python type binding in such a case.

On 2007-08-14 08:04, Harri Pesonen wrote:
> I'm sure that you'll find out that you are using nchar(N) data type,
> where N is calculated from the number of bytes in the unicode string,
> not number of characters.

The length information to use in ODBC APIs is a weak spot in the ODBC
standard documentation... in some places it refers to byte counts,
in others to code point counts and they aren't clear at all about
which to use, see e.g.
http://msdn2.microsoft.com/en-gb/library/ms716246.aspx

> This is Microsoft SQL Server ODBC driver, so this is the standard. :-)

Well, I wouldn't be so sure about this. Especially with respect to
Unicode handling, the SQL Server's ODBC driver tends to have a
very special interpretation. Things get even more complicated on
64-bit platforms.

> One more thing: You could easily fix this problem by using nvarchar
> instead of nchar data type. Nchar(N) requires that there are exactly N
> characters, and SQL Server ODBC driver will add spaces if needed.
> Nvarchar(N) would not ever add spaces.

That's what we've done now and it fixes the problem even when
using .executedirect().

However, that's just the SQL Server ODBC driver. We'll have to
test this with other ODBC drivers as well before releasing a
patch.

Thanks,
-- 
Marc-Andre Lemburg
eGenix.com

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


> -- Harri 
> 
> -----Original Message-----
> From: M.-A. Lemburg [mailto:mal at egenix.com] 
> Sent: 13. elokuuta 2007 18:39
> To: Harri Pesonen
> Cc: egenix-users at lists.egenix.com
> Subject: Re: [egenix-users] Two bugs in mxODBC: storing empty string
and
> unicode string
> 
> Here are some preliminary findings:
> 
>  * we can replicate the problem with the SQL Server ODBC driver
>    version 03.85.1117
> 
>  * the ODBC driver appears to add min(1, len(data)) spaces to
>    all Unicode data inserted into the table - up to the length
>    limit of the column
> 
> The problem goes away if you use .execute() instead of
> .executedirect().
> 
> We have to do some further testing, but this suggests that the
> ODBC driver does not work correctly when using .executedirect().
> 
> 
> On 2007-08-13 16:48, M.-A. Lemburg wrote:
>> On 2007-08-13 15:37, Harri Pesonen wrote:
>>> Here is the script:
>>>
>>> import mx.ODBC.Windows
>>> dsn = "Driver={SQL SERVER};Server=dskpesonha;Database=master;"
>>> connection = mx.ODBC.Windows.DriverConnect("%s;APP=%s" % (dsn,
> "test"),
>>> 0)
>>> connection.stringformat = mx.ODBC.Windows.MIXED_STRINGFORMAT
>>> o = connection.cursor()
>>> o.execute("CREATE TABLE #test (A nvarchar(64))")
>>> s  = "INSERT INTO #test (A) VALUES (?)"
>>> uc = u'\u0430'
>>> c = o.executedirect
>>> rs = c(s, ("",))
>>> rs = c(s, (u"",))
>>> rs = c(s, ("a",))
>>> rs = c(s, (uc,))
>>> o.execute("SELECT * FROM #test")
>>> rs = o.fetchall()
>>> for r in rs:
>>>   print r
>>> o  = None
>>>
>>> Here is the output:
>>>
>>> (u'',)
>>> (u' ',)
>>> (u'a',)
>>> (u'\u0430 ',)
>>>
>>> So there is bug #3, empty unicode string is converted to one space.
> With
>>> mxODBC 2 the first case fails as well, you get 64 spaces.
>> Thanks.
>>
>> We'll run a few tests and then report back our findings.
>>
>>> -- Harri
>>>
>>> -----Original Message-----
>>> From: M.-A. Lemburg [mailto:mal at egenix.com] 
>>> Sent: 13. elokuuta 2007 16:08
>>> 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 14:19, Harri Pesonen wrote:
>>>> 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.
>>> Could you provide us with the following ? We will then try to
>>> recreate the problem here and see what we can do about it.
>>>
>>>  * table schema definition
>>>
>>>  * inserts / selects exhibiting the problem
>>>
>>> Thanks,
> 



More information about the egenix-users mailing list