headers
Alexandre Pigolkine | 14 May 08:34

Newlines and VARBINARY


Hello all,

I try to use mx.ODBC in the following environment:
mx.ODBC, Linux, iODBC, FreeTDS-Library, Microsoft SQL Server.

I get an error message when I try to insert a string with
new lines into a table with a TEXT field:

import mx.ODBC
con = mx.ODBC.iODBC.DriverConnect("DRIVER={SQL Server};SERVER=<server_ip>;DATABASE=<dbname>;UID=<user>;PWD=<pwd>")
cur = con.cursor()
cur.execute("CREATE TABLE #test (t text)")
cur.close()
cur = con.cursor()
cur.execute("INSERT INTO #test(t) VALUES(?)", ('test\n',))
cur.close()
con.close()

/etc/odbcinst.ini:
[ODBC Drivers]
TDS MSSQL = installed

[SQL Server]
Description = TDS MSSQL ODBC Driver v 4.2
Driver = /usr/local/lib/libtdsodbc.so

after the line cur.execute("INSERT ... ") I get an error:
>>> cur.execute("INSERT INTO #test(t) VALUES(?)", ('test\n',))
Traceback (most recent call last):
(Continue reading)

Permalink | Reply |
headers
M.-A. Lemburg | 14 May 11:36
Favicon

Re: Newlines and VARBINARY

On 2008-05-14 08:37, Alexandre Pigolkine wrote:
> Hello all,
> 
> I try to use mx.ODBC in the following environment:
> mx.ODBC, Linux, iODBC, FreeTDS-Library, Microsoft SQL Server.
> 
> I get an error message when I try to insert a string with
> new lines into a table with a TEXT field:
> 
> import mx.ODBC
> con = mx.ODBC.iODBC.DriverConnect("DRIVER={SQL Server};SERVER=<server_ip>;DATABASE=<dbname>;UID=<user>;PWD=<pwd>")

Just a note: it is usually better to define an ODBC data source
in /etc/odbc.ini with all the connection details and then use
"DSN=datasourcename;UID=uid;PWD=pwd" in your application.

> cur = con.cursor()
> cur.execute("CREATE TABLE #test (t text)")
> cur.close()
> cur = con.cursor()
> cur.execute("INSERT INTO #test(t) VALUES(?)", ('test\n',))
> cur.close()
> con.close()
> 
> /etc/odbcinst.ini:
> [ODBC Drivers]
> TDS MSSQL = installed
> 
> [SQL Server]
> Description = TDS MSSQL ODBC Driver v 4.2
(Continue reading)

Permalink | Reply |
headers
Alexandre Pigolkine | 14 May 12:41

Re: Newlines and VARBINARY


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:
(Continue reading)

Permalink | Reply |
headers
M.-A. Lemburg | 14 May 12:48
Favicon

Re: Newlines and VARBINARY

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))
(Continue reading)

Permalink | Reply |
headers
M.-A. Lemburg | 14 May 16:15
Favicon

Re: Newlines and VARBINARY

On 2008-05-14 12:48, M.-A. Lemburg wrote:
> On 2008-05-14 12:41, Alexandre Pigolkine wrote:
>>> 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).

We'll issue a patch release to address this problem.

Could you tell us which version of mxODBC you've downloaded
(the filename which includes all the necessary details) ?

We'll them upload a release candidate, so that you can test
whether our fix solves you problem.

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/
________________________________________________________________________
(Continue reading)

Permalink | Reply |
headers
Alexandre Pigolkine | 15 May 15:35

Re: Newlines and VARBINARY


I downloaded this file:

egenix-mxodbc-3.0.1.linux-x86_64-py2.4_ucs4.prebuilt.zip

Best regards,
Alexander Pigolkin

-------- Original-Nachricht --------
> Datum: Wed, 14 May 2008 16:15:04 +0200
> Von: "M.-A. Lemburg" <mal@...>
> An: Alexandre Pigolkine <pigolkine@...>
> CC: egenix-users@...
> Betreff: Re: [egenix-users] Newlines and VARBINARY

> On 2008-05-14 12:48, M.-A. Lemburg wrote:
> > On 2008-05-14 12:41, Alexandre Pigolkine wrote:
> >>> 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).
> 
> We'll issue a patch release to address this problem.
> 
> Could you tell us which version of mxODBC you've downloaded
(Continue reading)

Permalink | Reply |
headers
M.-A. Lemburg | 15 May 20:47
Favicon

Re: Newlines and VARBINARY

On 2008-05-15 15:35, Alexandre Pigolkine wrote:
> I downloaded this file:
> 
> egenix-mxodbc-3.0.1.linux-x86_64-py2.4_ucs4.prebuilt.zip

Thanks.

We've just uploaded the RC2 versions of 3.0.2:

http://downloads.egenix.com/python/egenix-mxodbc-3.0.2_rc2.linux-x86_64-py2.4_ucs2.prebuilt.zip
http://downloads.egenix.com/python/egenix-mxodbc-3.0.2_rc2.linux-x86_64-py2.4_ucs4.prebuilt.zip
http://downloads.egenix.com/python/egenix-mxodbc-3.0.2_rc2.linux-x86_64-py2.5_ucs2.prebuilt.zip
http://downloads.egenix.com/python/egenix-mxodbc-3.0.2_rc2.linux-x86_64-py2.5_ucs4.prebuilt.zip

You existing eval license should work with those as well.

The new release will lift the restrictions on TEXT, CHAR and VARCHAR
binary column data for SQL Server and a few other backends.

Regards,
--

-- 
Marc-Andre Lemburg
eGenix.com

Professional Python Services directly from the Source  (#1, May 15 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/
________________________________________________________________________
(Continue reading)

Permalink | Reply |
headers
Alexandre Pigolkine | 16 May 11:26

Re: Newlines and VARBINARY


I tested this version
egenix-mxodbc-3.0.2_rc2.linux-x86_64-py2.4_ucs4.prebuilt.zip
and it looks really good. Tabs and new lines were saved
and loaded to/from varchar, char and text fields in SQL Server database.

Thanks a lot.

Best regards,
Alexander Pigolkin

-------- Original-Nachricht --------
> Datum: Thu, 15 May 2008 20:47:09 +0200
> Von: "M.-A. Lemburg" <mal@...>
> An: Alexandre Pigolkine <pigolkine@...>
> CC: egenix-users@...
> Betreff: Re: [egenix-users] Newlines and VARBINARY

> On 2008-05-15 15:35, Alexandre Pigolkine wrote:
> > I downloaded this file:
> > 
> > egenix-mxodbc-3.0.1.linux-x86_64-py2.4_ucs4.prebuilt.zip
> 
> Thanks.
> 
> We've just uploaded the RC2 versions of 3.0.2:
> 
> http://downloads.egenix.com/python/egenix-mxodbc-3.0.2_rc2.linux-x86_64-py2.4_ucs2.prebuilt.zip
> http://downloads.egenix.com/python/egenix-mxodbc-3.0.2_rc2.linux-x86_64-py2.4_ucs4.prebuilt.zip
> http://downloads.egenix.com/python/egenix-mxodbc-3.0.2_rc2.linux-x86_64-py2.5_ucs2.prebuilt.zip
(Continue reading)

Permalink | Reply |
headers
M.-A. Lemburg | 16 May 12:27
Favicon

Re: Newlines and VARBINARY

On 2008-05-16 11:26, Alexandre Pigolkine wrote:
> I tested this version
> egenix-mxodbc-3.0.2_rc2.linux-x86_64-py2.4_ucs4.prebuilt.zip
> and it looks really good. Tabs and new lines were saved
> and loaded to/from varchar, char and text fields in SQL Server database.
> 
> Thanks a lot.

Thanks for checking.

We will the final 3.0.2 in a couple of days.

Regards,
--

-- 
Marc-Andre Lemburg
eGenix.com

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

> Best regards,
(Continue reading)

Permalink | Reply |

Gmane