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

[Harri Pesonen]

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.

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


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