Problem with odbc and Sql Server

F

Frank Millman

Hi all

I have found a problem using MS Sql Server connecting via the odbc
module from python-win32.

I am liaising with Mark Hammond, and he is trying to help, but he is
very busy, and I cannot be certain whether the problem originates with
the odbc module, with the ODBC Driver, or with Sql Server itself.

If anyone can help me to pinpoint this, I will be very grateful.

Assume a table 't1' with a column 'c1' of type varchar(10).
From Python, set c1 to an empty string -
cur.execute("UPDATE t1 SET c1 = ?",[''])

The result is that c1 is actually set to a string of 10 spaces.

If I execute the command without using parameters -
cur.execute("UPDATE t1 SET c1 = ''")

it works - c1 is set to an empty string.

I am using Windows Server 2003 (SP1), Sql Server 2000 (SP4), and ODBC
Driver 2000.86.1830.00.

I tried it on Sql Server 2005, also using Windows Server 2003. It gives
the same problem.

Mark has a test suite which creates an MS Access database on the fly,
and allows you to run any odbc command against it. If I try the above
command with this setup, it works correctly. If I modify the test suite
to connect to my installed Sql Server, it fails. This seems to suggest
that the problem is not coming from the odbc module.

I have googled the MS Sql Server newsgroup, searching for 'empty
string'. I found several posts, but they were all to do with the fact
that, in an earlier version of Sql Server, trying to set a varchar
column to an empty string resulted in it being set to a single space. I
would have thought that, if my problem comes from the MS side, I would
find some reference to it, but I could not.

One way of proving it would be to execute the command from some other
tool that allows you to pass paramaterised commands via the ODBC driver
through to Sql Server. I seem to remember reading that you can do this
from VB and from MS Access, but I have no idea how to do this. I have
MS Access installed on my machine, so if anyone can talk me through the
steps required, I can give it a try myself. Alternatively, if anyone
has a setup where they can test this, I would be very interested to
hear the result.

Any suggestions will be much appreciated.

Thanks

Frank Millman
 
A

Andrew MacIntyre

Frank said:
Assume a table 't1' with a column 'c1' of type varchar(10).
From Python, set c1 to an empty string -
cur.execute("UPDATE t1 SET c1 = ?",[''])

The result is that c1 is actually set to a string of 10 spaces.

If I execute the command without using parameters -
cur.execute("UPDATE t1 SET c1 = ''")

it works - c1 is set to an empty string.

I am using Windows Server 2003 (SP1), Sql Server 2000 (SP4), and ODBC
Driver 2000.86.1830.00.

You might want to test with mxODBC, to see whether the problem is with
the odbc module or something further down the stack. Given that it works
with the MS-Jet (Access) ODBC provider (odbc interface), but not with
the SQL Server provider, I'd say that the problem is more probably outside
the control of the odbc module.

I've not had much to do with SQL Server, but I wonder whether there is
some configuration setting that might be affecting this behaviour.
 
D

Dennis Lee Bieber

One way of proving it would be to execute the command from some other
tool that allows you to pass paramaterised commands via the ODBC driver
through to Sql Server. I seem to remember reading that you can do this
from VB and from MS Access, but I have no idea how to do this. I have
MS Access installed on my machine, so if anyone can talk me through the
steps required, I can give it a try myself. Alternatively, if anyone
has a setup where they can test this, I would be very interested to
hear the result.
The newer versions of M$ Access allow for the use of SQL Server/MSDE
as a backend in place of JET. This is enabled by creating an "Access
Data Project" rather than a normal Access Database (it may still create
an MDB file to hold forms, reports, and code, but the data will be
stored in SQL Server).
Any suggestions will be much appreciated.
A few random hits:

from
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_03_7mch.asp
The varchar data type is a variable-length data type. Values shorter
than the size of the column are not right-padded to the size of the
column. If the ANSI_PADDING option was set to OFF when the column was
created, any trailing blanks are truncated from character values stored
in the column. If ANSI_PADDING was set ON when the column was created,
trailing blanks are not truncated.

Check that mode on the server side. I'm presuming a command line
tool is /showing/ the spaces were actually stored. It may be that
somewhere in the ODBC process (most likely in the formatting pass of
..execute()) it may be formatting output strings, even VARCHAR, to some
minimum width, expecting the server to truncate down.

VARCHAR trailing space handling seems to be a mystical art. I think
I've seen references to various RDBMs that perform any of the following:

truncate on input, no action on output
truncate on input, pad on output
no truncate on input, no action on output
no truncate on input, pad on output

My MySQL book (the black/brown one, I have newer, but this one sits
next to the computer) indicates the first behavior but comments that
this is NOT the ANSI SQL defined behavior. OTOH, MySQL CHAR type is "pad
on input, truncate on output"

Since your interest is in SQL Server I won't bother digging up my
books for Firebird or PostgreSQL. <G>

If there is a server log that captures the command, as received, how
does it display there?



Thanks

Frank Millman
--
Wulfraed Dennis Lee Bieber KD6MOG
(e-mail address removed) (e-mail address removed)
HTTP://wlfraed.home.netcom.com/
(Bestiaria Support Staff: (e-mail address removed))
HTTP://www.bestiaria.com/
 
F

Frank Millman

Frank said:
Hi all

I have found a problem using MS Sql Server connecting via the odbc
module from python-win32.

Assume a table 't1' with a column 'c1' of type varchar(10).
From Python, set c1 to an empty string -
cur.execute("UPDATE t1 SET c1 = ?",[''])

The result is that c1 is actually set to a string of 10 spaces.

If I execute the command without using parameters -
cur.execute("UPDATE t1 SET c1 = ''")

it works - c1 is set to an empty string.

Thanks for the replies, Andrew and Dennis.

I looked at Access Data Object, but I see that it uses OLE-DB to
connect to the database, and this would not be a valid comparison, so I
did not pursue that route.

Then I followed Andrew's suggestion of trying mx.ODBC - I should have
thought of that in the first place.

Using exactly the same connection string, and exactly the same sql
commands, it works correctly, where win32 odbc does not work correctly.
That seems to indicate that the problem lies with win32. I will advise
Mark of the result.

Thanks again

Frank
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,744
Messages
2,569,483
Members
44,903
Latest member
orderPeak8CBDGummies

Latest Threads

Top