[egenix-users] Problem with mxodbc 3.1 and Sybase 15.5

[Dale Arntson]

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