Problem with odbc and Sql Server

[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

[Andrew MacIntyre]

Frank Millman wrote:

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.

-------------------------------------------------------------------------
Andrew I MacIntyre "These thoughts are mine alone..."
E-mail: an*****@bullseye.apana.org.au (pref) | Snail: PO Box 370
an*****@pcug.org.au (alt) | Belconnen ACT 2616
Web: http://www.andymac.org/ | Australia

[Frank Millman]

Frank Millman wrote:

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