Python - Informix - Character to numeric conversion error (-1213)

While trying to update an Informix table via a Python script I am getting the error:

    Character to numeric conversion error (-1213)

The db request:

    cursor.execute("update sales set pid = ? where s开发者_运维技巧id = '1'", price)

Table sales:

    sid | oid | price | pid

The column I want to update has the data type money(10,2)

I tried inserting as a Decimal value a String and a Float...which one would be appropriate?

I am using Informix 10.00 TC6 on Win 2003 Server -

    CLIENT_LOCALE=en_us.819 - DB_LOCALE=en_us.819

To connect to Informix I am using pyodbc.

Nothing about money in the supported data types so not sure if it works at all (http://code.google.com/p/pyodbc/wiki/DataTypes)

Your DB driver is converting your code to SQL. You can use the Informix debugger to see what SQL it produces. I know Informix doesn't like thousand separators in its SQL.

There are two possible string-to-numeric conversions in the SQL you show.

1. Converting '1' to the same type as sid.
2. Converting the value passed as the placeholder (variable price which is substituted for ?).

What is the value in price? What does Python print its value as?

Informix is pretty flexible about doing conversions for you, so the first conversion should be fine. Normally, you'd only supply single quotes if sid is a string column, but it should convert fine.

So, the other issue is: what is PyODBC doing with price?

You might be able to see what happens if you set the environment variable SQLIDEBUG=2:xyz and run the program. It will generate a file with a prefix xyz_ which can be processed with sqliprint, assuming it is available to you. That will tell you what is sent between the client (Python) and the data server. Depending on what you find, it may point the finger at the client side code or the server side code.