CODEWITHSUNDEEP
odbcpyodbcinformix
J. Skinner
J. Skinner

Reputation: 319

Querying Informix table using pyodbc produces ODBC SQL type -103 is not yet supported error

I'm trying to query a table from an Informix database using pyodbc and write the results to a CSV file with the below code:

import pyodbc
import pandas as pd

server = '***.***.***.***'
dsn= 'informixdb'
username = 'user'
password = 'user123'
csvFile = r"C:\temp\bookings.csv"

conn = pyodbc.connect(dsn=f'{dsn}', uid=f'{username}', pwd=f'{password}')
cursor = conn.cursor()

sql = "SELECT FIRST 5 * from bookingsd"

df = pd.read_sql_query(sql, conn)
df.to_csv(csvFile, index=False)

I've also tried the following:

import pyodbc, csv
    
server = '***.***.***.***'
dsn= 'informixdb'
username = 'user'
password = 'user123'
csvFile = r"C:\temp\bookings.csv"
    
conn = pyodbc.connect(dsn=f'{dsn}', uid=f'{username}', pwd=f'{password}')
cursor = conn.cursor()
    
sql = "SELECT FIRST 5 * from bookingsd"

rows = cursor.execute(sql)
with open(csvFile, 'w', newline='') as csvfile:
        writer = csv.writer(csvfile)
        writer.writerow([x[0] for x in cursor.description])  # column headers
        for row in rows:
           writer.writerow(row)

Both produce the same error:

ODBC SQL type -103 is not yet supported. column-index=136 type=-103', 'HY106'

It appears the error is occurring for the pyodbc cursor object (i.e. rows). Does anyone know what this error is referring to?

Upvotes: 3

Views: 3384

Answers (1)

jsagrera
jsagrera

Reputation: 2013

Because the Informix datatype CLOB are BLOB are not standard ODBC types, you may need to tell the ODBC driver to automatically handle those types.

You do that enabling the "Report standard ODBC types" feature within the ODBC driver by setting the "SQL_INFX_ATTR_ODBC_TYPES_ONLY" ODBC attribute or by adding a "NeedODBCTypesOnly=1" to the connection string.

https://www.ibm.com/docs/en/informix-servers/14.10?topic=types-report-standard-odbc

Upvotes: 3

Related Questions