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

[M.-A. Lemburg]

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