Error inserting floats with NaN in first row

[lumpi101]

We experience an Error when inserting float / decimal Data using executemany() if the first line(s) contains NaN, converted to None. The error does not occur with NaNs in other rows, if the frst row contains a number. Also working without error is the insertion of NaNs only for the numeric column.

Example:

# set up
import ceODBC

connection_string = f"DRIVER={driver};SERVER={server},{port};DATABASE={database};UID={user};PWD={password};"
connection = ceODBC.connect(connection_string, autocommit=True)

create_sql = "create table TEST (ID varchar(32), NUMBER numeric(12,3))"
insert_sql = "insert into TEST (ID, NUMBER) VALUES (?,?)"

curs = connection.cursor()
curs.execute(create_sql)
curs.close()

# this code works:
to_insert = [('abc123', 1.234), ('bcd234', None), ('cde345', 1234.1)]
curs = connection.cursor()
curs.executemany(insert_sql, to_insert)
curs.close()

# this code does not work:
to_insert = [('abc123', None), ('bcd234', 1.234), ('cde345', 1234.1)]
curs = connection.cursor()
curs.executemany(insert_sql, to_insert)
curs.close()

# exception...
src\ceODBC\cursor.pyx:443: in ceODBC.driver.Cursor.executemany
    ???
src\ceODBC\cursor.pyx:67: in ceODBC.driver.Cursor._bind_parameters
    ???
src\ceODBC\cursor.pyx:311: in ceODBC.driver.Cursor._get_bind_var
    ???
src\ceODBC\cursor.pyx:307: in ceODBC.driver.Cursor._get_bind_var
    ???
src\ceODBC\var.pyx:116: in ceODBC.driver.Var._set_value
    ???
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

>   ???
E   TypeError: expecting string

This is tested with ceODBC version 3.0 and a mssql database. At first glance it seems that the datatypes are inferenced by the first row of the insert values and None/NULL is interpreted as string and subsequent values of other types cause exceptions. So this problem may also exist for types other than numeric.

[anthony-tuininga]

Thanks for the report and apologies for the lengthy delay in getting back to you. I have just pushed changes that ensure that type assignment is deferred until the last row of data that is being processed and added a relevant test case.

[lumpi101]

Thanks. I cannot test it anymore on my side but I guess it is solved now. So I close this issue. If there are still problems for anyone regarding this issue, feel free to reopen.