<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=Content-Type content="text/html; charset=us-ascii">
<META content="MSHTML 6.00.6000.16544" name=GENERATOR></HEAD>
<BODY>
<DIV><SPAN class=052164621-21022008><FONT face=Arial color=#0000ff size=2>I also
forgot to mention that this...</FONT></SPAN></DIV>
<DIV><SPAN class=052164621-21022008><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=052164621-21022008><FONT face=Arial color=#0000ff size=2>import
pymssql</FONT></SPAN></DIV>
<DIV><FONT face=Arial color=#0000ff size=2></FONT> </DIV>
<DIV><SPAN class=052164621-21022008><FONT face=Arial color=#0000ff size=2>TestDB
=
pymssql.connect(host='Test',user='test',password='test',database='test')<BR>cursor
= TestDB.cursor()<BR>query = """INSERT INTO test.dbo.test (test) VALUES
('%s');"""<BR>cursor.execute(query,(None))</FONT></SPAN></DIV>
<DIV><FONT face=Arial color=#0000ff size=2></FONT> </DIV>
<DIV><SPAN class=052164621-21022008><FONT face=Arial color=#0000ff size=2>works.
While </FONT></SPAN></DIV>
<DIV><SPAN class=052164621-21022008><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=052164621-21022008><FONT face=Arial color=#0000ff size=2>import
pymssql</FONT></SPAN></DIV>
<DIV> </DIV>
<DIV><SPAN class=052164621-21022008><FONT face=Arial color=#0000ff size=2>TestDB
=
pymssql.connect(host='Test',user='test',password='test',database='test')<BR>cursor
= TestDB.cursor()<BR>query = """INSERT INTO test.dbo.test (inttest) VALUES
('%d');"""<BR>cursor.execute(query,(None))</FONT></SPAN></DIV>
<DIV><SPAN class=052164621-21022008><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=052164621-21022008><FONT face=Arial color=#0000ff
size=2>doesn't work and returns</FONT></SPAN></DIV>
<DIV><SPAN class=052164621-21022008><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=052164621-21022008><FONT face=Arial color=#0000ff
size=2>Traceback (most recent call last):<BR> File
"C:\Test\db_info\test.py", line 6, in <module><BR>
cursor.execute(query,(None))<BR> File
"C:\Python25\lib\site-packages\pymssql.py", line 126, in
execute<BR> self.executemany(operation, (params,))<BR>
File "C:\Python25\lib\site-packages\pymssql.py", line 152, in
executemany<BR> raise DatabaseError, "internal error: %s" %
self.__source.errmsg()<BR>pymssql.DatabaseError: internal error: SQL Server
message 245, severity 16, state 1, line 1:<BR>Conversion failed when converting
the varchar value '%d' to data type int.<BR>DB-Lib error message 10007, severity
5:<BR>General SQL Server error: Check messages from the SQL
Server.</FONT></SPAN></DIV>
<DIV><SPAN class=052164621-21022008><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=052164621-21022008><FONT face=Arial color=#0000ff
size=2>Jayson</FONT></SPAN></DIV><BR>
<DIV class=OutlookMessageHeader lang=en-us dir=ltr align=left>
<HR tabIndex=-1>
<FONT face=Tahoma size=2><B>From:</B>
python-list-bounces+jayson.barley=webtrends.com@python.org
[mailto:python-list-bounces+jayson.barley=webtrends.com@python.org] <B>On Behalf
Of </B>Jayson Barley<BR><B>Sent:</B> Thursday, February 21, 2008 1:20
PM<BR><B>To:</B> python-list@python.org<BR><B>Subject:</B> Inserting NULL values
with pymssql<BR></FONT><BR></DIV>
<DIV></DIV>
<DIV><FONT face=Arial><FONT size=2>I am attempting to insert NULL values into a
database. I have tried to do this in multiple ways without any success, see
below, and haven't been able to find anything through Google to help out. I am
hoping that I am just overlooking something or that it is a rookie mistake.<SPAN
class=896212219-21022008> Below is a test I came up with to prove this. I am on
Windows XP using Python 2.5 and pymssql-0.8.0-py2.5.</SPAN></FONT></FONT></DIV>
<DIV><FONT face=Arial size=2></FONT><FONT face=Arial size=2></FONT><BR><FONT
face=Arial size=2>CREATE TABLE [dbo].[Test](<BR> [test] [varchar](50)
NULL,<BR> [inttest] [int] NULL<BR>) ON [PRIMARY]</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><SPAN class=896212219-21022008><FONT face=Arial
size=2>1.</FONT></SPAN></DIV>
<DIV><SPAN class=896212219-21022008><FONT face=Arial size=2>import
pymssql</FONT></SPAN></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><SPAN class=896212219-21022008><FONT face=Arial size=2>TestDB =
pymssql.connect(host='Test',user='test',password='test',database='test')<BR>cursor
= TestDB.cursor()<BR>query = """INSERT INTO test.dbo.test VALUES (?,
?);"""<BR>cursor.execute(query,('',''))<BR></FONT></SPAN></DIV>
<DIV><SPAN class=896212219-21022008><FONT face=Arial
size=2>Returns</FONT></SPAN></DIV>
<DIV><SPAN class=896212219-21022008><FONT face=Arial
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=896212219-21022008><FONT face=Arial size=2>Traceback (most
recent call last):<BR> File "C:\Test\db_info\test.py", line 6, in
<module><BR> cursor.execute(query,('',''))<BR>
File "C:\Python25\lib\site-packages\pymssql.py", line 126, in
execute<BR> self.executemany(operation, (params,))<BR>
File "C:\Python25\lib\site-packages\pymssql.py", line 152, in
executemany<BR> raise DatabaseError, "internal error: %s" %
self.__source.errmsg()<BR>pymssql.DatabaseError: internal error:
None</FONT></SPAN></DIV>
<DIV><SPAN class=896212219-21022008><FONT face=Arial
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=896212219-21022008><FONT face=Arial
size=2>2.</FONT></SPAN></DIV>
<DIV><SPAN class=896212219-21022008><FONT face=Arial size=2>import
pymssql</FONT></SPAN></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><SPAN class=896212219-21022008><FONT face=Arial size=2>TestDB =
pymssql.connect(host='Test',user='test',password='test',database='test')<BR>cursor
= TestDB.cursor()<BR>query = """INSERT INTO test.dbo.test VALUES (?,
?);"""<BR>cursor.execute(query,('',None))</FONT></SPAN></DIV>
<DIV><SPAN class=896212219-21022008><FONT face=Arial
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=896212219-21022008><FONT face=Arial
size=2>Returns</FONT></SPAN></DIV>
<DIV><SPAN class=896212219-21022008><FONT face=Arial
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=896212219-21022008><FONT face=Arial size=2>Traceback (most
recent call last):<BR> File "C:\Test\db_info\test.py", line 8, in
<module><BR> cursor.execute(query,('',None))<BR>
File "C:\Python25\lib\site-packages\pymssql.py", line 126, in
execute<BR> self.executemany(operation, (params,))<BR>
File "C:\Python25\lib\site-packages\pymssql.py", line 152, in
executemany<BR> raise DatabaseError, "internal error: %s" %
self.__source.errmsg()<BR>pymssql.DatabaseError: internal error:
None</FONT></SPAN></DIV>
<DIV><SPAN class=896212219-21022008><FONT face=Arial
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=896212219-21022008><FONT face=Arial
size=2>3.</FONT></SPAN></DIV>
<DIV><SPAN class=896212219-21022008><FONT face=Arial size=2>import
pymssql</FONT></SPAN></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><SPAN class=896212219-21022008><FONT face=Arial size=2>TestDB =
pymssql.connect(host='Test',user='test',password='test',database='test')<BR>cursor
= TestDB.cursor()<BR>query = """INSERT INTO test.dbo.test VALUES (?,
?);"""<BR>cursor.execute(query,('','NULL'))<BR></FONT></SPAN></DIV>
<DIV><SPAN class=896212219-21022008><FONT face=Arial
size=2>Returns</FONT></SPAN></DIV>
<DIV><SPAN class=896212219-21022008><FONT face=Arial
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=896212219-21022008><FONT face=Arial size=2>Traceback (most
recent call last):<BR> File "C:\Test\db_info\test.py", line 6, in
<module><BR> cursor.execute(query,('','NULL'))<BR>
File "C:\Python25\lib\site-packages\pymssql.py", line 126, in
execute<BR> self.executemany(operation, (params,))<BR>
File "C:\Python25\lib\site-packages\pymssql.py", line 152, in
executemany<BR> raise DatabaseError, "internal error: %s" %
self.__source.errmsg()<BR>pymssql.DatabaseError: internal error:
None</FONT></SPAN></DIV>
<DIV><SPAN class=896212219-21022008><FONT face=Arial
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=896212219-21022008><FONT face=Arial size=2>I am wondering what
I am missing that is preventing me from inserting a NULL. I can perform the
INSERT in Server Management Studio the problem only exists in the Python script.
If anyone can point me to a resource that I may have missed or a flaw in my
script I would really appreciate it.</FONT></SPAN></DIV>
<DIV><SPAN class=896212219-21022008><FONT face=Arial
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=896212219-21022008><FONT face=Arial
size=2>Jayson</DIV></FONT></SPAN></BODY></HTML>