[egenix-users] Newlines and VARBINARY

[M.-A. Lemburg]

On 2008-05-14 12:41, Alexandre Pigolkine wrote:
> Thanks for the tip for connection string and freetds configurations.
> 
>> BTW: Which versions of iODBC, FreeTDS and SQL Server are
>> you using ?
>>
> iodbc-3.52.6
> freetds-0.64
> SQL Server 2005 SP 2
> 
>> Now, according to the MS docs, character data is either:
>>
>> * Uppercase and lowercase characters such as a, b, and C.
>> * Numerals such as 1, 2, and 3.
>> * Special characters such as the "at" sign (@), ampersand (&), and
>> exclamation point (!).
>>
>> (see http://msdn.microsoft.com/en-us/library/ms175055.aspx)
>>
>> The test we are applying therefore checks for character data
>> to be in the range [0x20 ... 0x7f].
>>
>> The newline character 0x10 is not included in that range, which
>> is why mxODBC sends such data as VARBINARY.
> 
> IMHO, HT,LF and CR are also special characters such as @, & etc.
> and it would be good to include them in varchar-range. For example
> following script works nicely in SQL Manager:
> 
> create table #temp(a varchar(250))
> go
> insert into #temp values ('213234
> qwewqesad	123')
> go
> select * from #temp

We've tried to find a complete list of valid characters for CHAR,
VARCHAR and TEXT in SQL Server, but apart from the above page
we couldn't find any more accurate information.

Looks like we'll have to do trial and error to find the valid set.

>>> Is there a way to avoid such behavior and force mxODBC to always pass 
>>> strings as a varchar-type?
>> Please check whether you see the same problem when using longer
>> strings, e.g. ones with 500 bytes.
> 
> I modified INSERT-line and now I get this result:
> 
> cur.execute("INSERT INTO #test(t) VALUES(?)", ('test\n1234564789012345647890123456478901234564789012345647890123456478901234564789012345647890123456478901234564789012345647890123456478901234564789012345647890123456478901234564789012345647890123456478901234564789012345647890123456478901234564789012345647890123456478901234564789012345647890123456478901234564789012345647890123456478901234564789012345647890123456478901234564789012345647890123456478901234564789012345647890123456478901234564789012345647890123456478901234564789012345647890123456478901234564789012345647890123456478901234564789012345647890',))
> Traceback (most recent call last):
>   File "<stdin>", line 1, in ?
> mx.ODBC.Error.ProgrammingError: ('22018', 206, '[FreeTDS][SQL Server]Operand type clash: image is incompatible with text', 7028)

Thanks, so that's not an option either.

>> We'll investigate this a bit more and if needed issue a patch
>> release to address the problem.

We ran a few tests and can confirm the problem using TEXT
columns.

For some reason, this problem does not occur with
VARCHAR(254) columns (which are scheduled to replace TEXT in a
future SQL Server release according to MS).

Thanks,
-- 
Marc-Andre Lemburg
eGenix.com

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