[python-win32] adodbapi returns None for text field in first row

[Halgrim, Scott]

Thanks, Vernon.

I am using Windows XP, Python 2.7, and adodbapi version 2.2.6 from pywin32 v214.

The difference between what I'm running and your attempt at replication is that the problem is occurring with a column of type text, not of type varchar.  (In fact, I've currently created a workaround to the problem by creating a duplicate table where the column is varchar(8000).)

Here's my verbose output per your request (on the table with the text column).  Note rows[0] has None in its second column, but that's not what's in that row/column in the database.  Also note that rows[1] returns what is actually in that row.

>>> conn = adodbapi.connect(connectstring)
adodbapi v2.2.6  attempting: "[connectstring]"
adodbapi New connection at 1347FD0
>>> c = conn.cursor()
adodbapi New cursor at 1387C30 on conn 1347FD0
>>> query = 'SELECT fword, text, cui FROM TABLE'
>>> c.execute(query)
>>> print 'description=',repr(c.description)
description= [(u'fword', 200, 12, 80, 255, 255, False), (u'text', 201, 29, 2147483647, 255, 255, True), (u'cui', 200, 8, 8, 255, 255, False)]
>>> rows = c.fetchall()
Converting type_code=200, val=u'infiltrating'
conversion function= <function identity at 0x012D71F0>
                     output=u'infiltrating'
Converting type_code=200, val=u'infitrating'
conversion function= <function identity at 0x012D71F0>
                     output=u'infitrating'
...see attachment for remaining...
>>> print repr(rows[0])
(u'infiltrating', None, u'C0007124')
>>> print repr(rows[1])
(u'infitrating', u'infitrating ductal carcinoma', u'C0007124')
>>> c.close()
>>> conn.close()
adodbapi Closed connection at 1347FD0

Thanks again,

Scott

________________________________
From: Vernon Cole [mailto:vernondcole at gmail.com]
Sent: Thursday, December 30, 2010 9:58 PM
To: Halgrim, Scott
Cc: python-win32 at python.org
Subject: Re: [python-win32] adodbapi returns None for text field in first row

Scott:
  Something is indeed fishy.  What os, version of python, and version of pywin32 are you using?
As an attempt to duplicate your result, I built a table "tblTemp" on my Windows Vista laptop's SQL express 2008 with three VarChar fields
and loaded two rows of data. I defined an ODBC DSN to point to my local server, since you are running in ODBC mode.

I added some debug details to your sample, corrected a typo, altered the database info,  and ran the following...

<python program x.py>
#Note: odbcTest is a DSN defined using "Control Panel"->"Administrative Tools"->"Data Sources (ODBC)"
connStrSQLServer = 'Data Source=odbcTest;Initial Catalog=Northwind;' + 'Trusted_Connection=true;'
import sys
print sys.version
import adodbapi
print 'adodbapi version=',adodbapi.version
adodbapi.adodbapi.verbose = 4

cnctn = adodbapi.connect(connStrSQLServer)
c = cnctn.cursor() # Note: corrected from crsr = cnctn.cursor()
query = 'SELECT fword, text, cui FROM TblTemp'
c.execute(query)
print 'description=',repr(c.description)
rows = c.fetchall()
print repr(rows[0])
print repr(rows[1])
c.close()
cnctn.close()
</python>

I got this result...

<console dump>
C:\hg\adodbapi\test>c:\python26\python.exe x.py
2.6.4 (r264:75708, Oct 26 2009, 08:23:19) [MSC v.1500 32 bit (Intel)]
adodbapi version= adodbapi v2.4.0.2
adodbapi v2.4.0.2 attempting: "Data Source=odbcTest;Initial Catalog=Northwind;Tusted_Connection=true;"
adodbapi New connection at 2C70450
adodbapi New cursor at 2C70BF0 on conn 2C70450
Executing command="SELECT fword, text, cui FROM TblTemp"
with parameters= []
description= [(u'fword', 200, 6, 10, 255, 255, True), (u'text', 200, 15, 30, 255, 255, True), (u'cui', 200, 8, 12, 255, 255, True)]
<SQLrow={fword:u'breast', text:u'what goes here?', cui:u'C0006142'}>
<SQLrow={fword:u'breast', text:u'breast carcinoma', cui:u'C0006142'}>
adodbapi Closed connection at 2C70450

</console dump>

Try again with verbose=4 all of the "print repr(..." lines added, and see if the additional information helps.
--
Vernon
On Thu, Dec 30, 2010 at 4:07 PM, Halgrim, Scott <halgrim.s at ghc.org<mailto:halgrim.s at ghc.org>> wrote:
Has anybody ever had an issue like this?

I submit a query to a table and everything works out fine, except for one column in one row.  The column of type text in the first row returned is always None.  I've modified the query by adding a WHERE clause so that a different row was the first one returned, and the same thing happens where the first row returned has a None in that column.

I'm running adodbapi 2.4 and querying SQL Server 2008.  Here's my code.

>>> from adodbapi import connect
>>> cnctn = connect('Data Source=InstanceName;Initial Catalog=DbName;' + 'Trusted_Connection=true;')
>>> crsr = cnctn.cursor()
>>> query = 'SELECT fword, text, cui FROM TABLE'
>>> c.execute(query)
>>> rows = c.fetchall()
>>> print rows[0], rows[1]
(u'breast', None, u'C0006142') (u'breast', u'breast carcinoma', u'C0006142')

Any help is appreciated.

Thanks,

Scott


  ________________________________
GHC Confidentiality Statement

This message and any attached files might contain confidential information protected by federal and state law. The information is intended only for the use of the individual(s) or entities originally named as addressees. The improper disclosure of such information may be subject to civil or criminal penalties. If this message reached you in error, please contact the sender and destroy this message. Disclosing, copying, forwarding, or distributing the information by unauthorized individuals or entities is strictly prohibited by law.

_______________________________________________
python-win32 mailing list
python-win32 at python.org<mailto:python-win32 at python.org>
http://mail.python.org/mailman/listinfo/python-win32

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.python.org/pipermail/python-win32/attachments/20110103/a2057523/attachment-0001.html>
-------------- next part --------------
An embedded and charset-unspecified text was scrubbed...
Name: verboseconversions.txt
URL: <http://mail.python.org/pipermail/python-win32/attachments/20110103/a2057523/attachment-0001.txt>