[egenix-users] Problem with mxodbc 3.1 and Sybase 15.5
M.-A. Lemburg
mal at egenix.com
Tue Jan 4 22:54:54 CET 2011
Hi Dale,
could you try enabling the ODBC tracing function in the Windows
ODBC manager and send us the ODBC log ?
Here's the documentation on how to enable tracing in the
MS ODBC Manager:
http://support.microsoft.com/kb/274551
If standard tracing doesn't work, you could try the
Visual Studio Analyzer:
http://msdn.microsoft.com/en-us/library/aa234848.aspx
Thanks,
--
Marc-Andre Lemburg
eGenix.com
Professional Python Services directly from the Source (#1, Jan 04 2011)
>>> 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 our new mxODBC.Connect Python Database Interface 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
http://www.egenix.com/company/contact/
Dale Arntson wrote:
> Hi All,
>
> I haven't heard back from anyone yet, so I thought I would give any
> Sybase 15 users out there some code that would make it easier to
> replicate my problem by cutting and pasting. Just replace the 'xxx's
> with the proper dsn info.
>
> For this demo, I am using a Sybase ASE ODBC driver (version 15.00.00.50,
> 32 bit) on Windows XP against a Sybase 15.5 ASE database.
>
>
> import mx.ODBC.Windows
> import traceback
>
>
> class db_class:
> def __init__(self):
> datasource = 'xxx'
> user = 'xxx'
> password = 'xxx'
> self.dsn = "dsn=%s;uid=%s;pwd=%s" % (datasource, user, password)
> self.con = None
> self.cur = None
>
> def open(self):
> self.con = mx.ODBC.Windows.DriverConnect(self.dsn)
> self.con.bindmethod = mx.ODBC.Windows.BIND_USING_SQLTYPE
> self.cur = self.con.cursor()
> self.cur.executedirect("set chained off")
>
> def close(self):
> self.con.commit()
> self.cur.close()
> self.con.close()
>
>
> def create_table(db):
> db.cur.tables(table='friend')
> if db.cur.fetchone():
> db.cur.executedirect('drop table friend')
> sql = "create table friend ( " \
> "name varchar(20) not null, " \
> "age smallint null, " \
> "birthdate datetime null, " \
> "favorite_beer varchar(20) null)"
> db.cur.executedirect(sql)
>
>
> def insert_data(db):
> rows = [['Stephanie', 28, '06/09/1982', 'Budwiser'],
> ['Raphael', 60, '01/16/50', 'Corona'],
> ['Johanna', 50, '09/23/1960', 'Pilsner']]
> sql = "insert into friend values (?,?,?,?)"
> for r in rows:
> print "Inserting %s" % r[0]
> db.cur.executedirect(sql,r)
> db.con.commit()
>
>
> def update_data(db):
> rows = [[28, '06/09/1982', None, 'Stephanie'],
> [60, None, 'Corona', 'Raphael'],
> [None, '09/23/1960', 'Pilsner', 'Johanna']]
> sql = "Update friend set age = ?, birthdate = ?, " \
> "favorite_beer = ? where name = ?"
> for r in rows:
> print "Updating %s" % r[3]
> db.cur.executedirect(sql,r)
> db.con.commit()
>
>
> Now, using the above code, as expected, I can do the following:
>
>>>> db = db_class()
>>>> db.open()
>>>> create_table(db)
>>>> insert_data(db)
> Inserting Stephanie
> Inserting Raphael
> Inserting Johanna
>
> However, when I try to update my table by nullifying some of the
> columns, I get an error. I can nullify a varchar, and a datetime column
> just fine. But, when I try to nullify a smallint column, I get an error:
>
>>>> update_data(db)
> Updating Stephanie
> Updating Raphael
> Updating Johanna
> Traceback (most recent call last):
> File "<stdin>", line 1, in <module>
> File "c:\dale\dev\gbs\test\code\grin\python-49AGef.py", line 57, in
> update_data
> mx.ODBC.Error.ProgrammingError: ('42000', 257, "[Sybase][ODBC
> Driver][Adaptive Server Enterprise]Implicit conversion from datatype
> 'CHAR' to 'SMALLINT' is not allowed. Use the CONVERT function to run
> this query.\n", 8396)
>
> The Sybase odbc driver seems to think that age = None is a char, when,
> of course, it should be a null.
>
> I get this error using the following cursor.execute*() methods and
> connection.bindmethod values:
>
> .executedirect() and BIND_USING_SQLTYPE (as in the above code)
> .executedirect() and BIND_USING_PYTHONTYPE
> .execute() and BIND_USING_PYTHONTYPE
>
> However, when I change the above code to use:
>
> .execute() and BIND_USING_SQLTYPE
>
> to do the update, I get a different error:
>
>>>> update_data(db)
> Updating Stephanie
> Updating Raphael
> Traceback (most recent call last):
> File "<stdin>", line 1, in <module>
> File "c:\dale\dev\gbs\test\code\grin\python-49ATol.py", line 57, in
> update_data
> mx.ODBC.Error.ProgrammingError: ('ZZZZZ', 3814, '[Sybase][ODBC
> Driver][Adaptive Server Enterprise]The parameter of type 61 did not have
> a valid value.\n', 8396)
>
> The first row is updated fine. However, for all subsequent rows the
> driver seems to loose track of the fact that the parameter to .execute()
> is a list (or a tuple).
>
> I believe that mxodbc's inability to update a Sybase 15 smallint or int
> with a null (using None) was present in the previous release of mxodbc.
> However, the inability to update successive rows of a table using
> .execute() (with BIND_USING_SQLTYPE) seems to be new with mxodbc 3.1.
>
> Any ideas? Thanks for the help.
>
> -dale
>
>
> On 12/23/2010 11:58 PM, Dale Arntson wrote:
>> The syntax error is my fault. I was trying to pare down some real code.
>> Here is a test example that illustrates the problem, two problems really.
>>
>> I want to update this table:
>>
>> CREATE TABLE friend (
>> name varchar(20) NOT NULL,
>> age smallint NULL,
>> birthdate datetime NULL,
>> favorite_beer varchar(20) NULL
>> )
>>
>> Using these rows:
>>
>> rows = [[28, '06/09/1982', None, 'Stephanie'],
>> [60, None, 'Corona', 'Raphael'],
>> [None, '09/23/1960', 'Pilsner', 'Johanna']]
>>
>> With this function:
>>
>> def load_data(m):
>> sql = "Update friend set age = ?, birthdate = ?, " \
>> "favorite_beer = ? where name = ?"
>> for r in rows:
>> print "Updating %s" % r[3]
>> m.db.cur.executedirect(sql,r)
>> m.db.con.commit()
>>
>> Using the above, I get the following output:
>>
>> >>> load_data(m)
>> Updating Stephanie
>> Updating Raphael
>> Updating Johanna
>> Traceback (most recent call last):
>> File "<stdin>", line 1, in <module>
>> File "c:\dale\dev\gbs\test\code\grin\python-49A3Dj.py", line 84, in
>> load_data
>> mx.ODBC.Error.ProgrammingError: ('42000', 257, "[Sybase][ODBC
>> Driver][Adaptive Server Enterprise]Implicit conversion from datatype
>> 'CHAR' to 'SMALLINT' is not allowed. Use the CONVERT function to run
>> this query.\n", 8396)
>>
>> The odbc driver thinks that age = None is a char. I get the same output
>> using:
>>
>> BIND_USING_PYTHONTYPE and .executedirect()
>> BIND_USING_PYTHONTYPE and .execute()
>> BIND_USING_SQLTYPE and .executedirect()
>>
>> However, if I use:
>>
>> BIND_USING_SQLTYPE and .execute()
>>
>> I get this output. The code processes the first row fine, then seems to
>> barf on trying to use the cached sql the second time around.
>>
>> >>> load_data(m)
>> Updating Stephanie
>> Updating Raphael
>> Traceback (most recent call last):
>> File "<stdin>", line 1, in <module>
>> File "c:\dale\dev\gbs\test\code\grin\python-49AEcR.py", line 84, in
>> load_data
>> mx.ODBC.Error.ProgrammingError: ('ZZZZZ', 3814, '[Sybase][ODBC
>> Driver][Adaptive Server Enterprise]The parameter of type 61 did not have
>> a valid value.\n', 8396)
>>
>> I hope this example is a little clearer.
>>
>> -dale
>>
>>
>>
>> On 12/23/2010 2:20 PM, M.-A. Lemburg wrote:
>>> Dale Arntson wrote:
>>>> Hi All,
>>>>
>>>> If I execute the following function using mxodbc 3.1 against a Sybase
>>>> 15.5 database,
>>>>
>>>> def load_data(m):
>>>> count = 0
>>>> sql = "update uc_gbs_pick_list2 " \
>>>> "set state = ?, viewability = ?, conditions = ?, \
>>>> "where barcode = ?"
>>>
>>> Shouldn't the above read:
>>>
>>> sql = ("update uc_gbs_pick_list2 "
>>> "set state = ?, viewability = ?, conditions = ? "
>>> "where barcode = ?")
>>>
>>> ?
>>>
>>> I get a SyntaxError for your version.
>>>
>>>> for r in m.rows:
>>>> m.db.cur.execute(sql,r)
>>>> count += 1
>>>> if count % 10000 == 0: m.db.con.commit()
>>>> m.db.con.commit()
>>>>
>>>> I get this error:
>>>>
>>>> Traceback (most recent call last):
>>>> File "c:\dale\dev\gbs\test\code\grin\python-49AQec.py", line 48, in
>>>> main
>>>> File "c:\dale\dev\gbs\test\code\grin\python-49AQec.py", line 166, in
>>>> load_data
>>>> InterfaceError: ('HY010', 30102, '[Sybase][ODBC Driver]Function
>>>> sequence
>>>> error', 8148)
>>>>
>>>> It updates the first row correctly, but throws an error on the second
>>>> row. If I use: .executedirect instead of .execute in the above code, I
>>>> get the following error on the first row:
>>>>
>>>> Traceback (most recent call last):
>>>> File "c:\dale\dev\gbs\test\code\grin\python-49Adoi.py", line 48, in
>>>> main
>>>> File "c:\dale\dev\gbs\test\code\grin\python-49Adoi.py", line 166, in
>>>> load_data
>>>> ProgrammingError: ('42000', 257, "[Sybase][ODBC Driver][Adaptive Server
>>>> Enterprise]Implicit conversion from datatype 'CHAR' to 'SMALLINT' is
>>>> not
>>>> allowed. Use the CONVERT function to run this query.\n", 8396)
>>>>
>>>> With .executedirect, but not with .execute, Sybase thinks that a None
>>>> value in python used in an sql smallint column is a char, and will not
>>>> save it as a sql NULL. This is the case whether or not I use the
>>>> following with .executedirect:
>>>>
>>>> m.db.con.bindmethod = mx.ODBC.Windows.BIND_USING_SQLTYPE or
>>>> m.db.con.bindmethod = mx.ODBC.Windows.BIND_USING_PYTHONTYPE
>>>>
>>>> Any ideas or workarounds?
>>>
>>> Please try the above corrected sql line. Note that I have removed
>>> the trailing comman after the last set part as well.
>>>
>>> If you still get the errors you mentioned, please provide a data
>>> extract as well. Otherwise, it's difficult to tell what is
>>> getting converted and how.
>>>
>>> Normally, .executedirect() will pass the SQL and data verbatim
>>> to the server and let it do the binding. The operation then
>>> always maps to a Python type binding. With .execute(), the binding
>>> happens on the client side, based on the type information the client
>>> received from the server.
>>>
>>> With some drivers, .executedirect() will also bind on the client
>>> side, but then use the Python types as basis for the binding
>>> and pass the resulting SQL string to the server for execution.
>>>
>>> This is why you sometimes see different behavior with .executedirect()
>>> and .execute().
>>>
>>
>>
>> _______________________________________________________________________
>> eGenix.com User Mailing List http://www.egenix.com/
>> https://www.egenix.com/mailman/listinfo/egenix-users
>
>
> _______________________________________________________________________
> eGenix.com User Mailing List http://www.egenix.com/
> https://www.egenix.com/mailman/listinfo/egenix-users
More information about the egenix-users
mailing list