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

M.-A. Lemburg mal at egenix.com
Mon Aug 13 18:38:57 CEST 2007


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,
> 

-- 
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