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

[M.-A. Lemburg]

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

-- 
Marc-Andre Lemburg
eGenix.com

Professional Python Services directly from the Source  (#1, Dec 23 2010)
>>> 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/