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

[Dale Arntson]

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().
>