Informix "load" command is not working in Python pyodbc

Question

We are writing a utility to load data into Informix DB.

Our utility is written in Python and we are using pyodbc package to connect and execute the SQLs.

The select and insert queries are working fine.

However, the sql

"load from input_file delimiter '|' insert into table"

is failing with below error.

pyodbc.ProgrammingError: ('42000', '[42000] [Informix][Informix ODBC Driver][Informix]A syntax error has occurred. (-201) (SQLExecDirectW)') 

Whether pyodbc is not supporting "load" command in Informix?

Appreciate the help in advance.

Thanks,

Answer 1

Unfortunately, 'LOAD from ...' (and 'UNLOAD TO ...') only works with DBACCESS and ISQL. None of the client APIs (ODBC/JDBC/.NET) support those commands.

Answer 2

As J_S said, the LOAD command is a pseudo-SQL command implemented by front-end tools; it is not implemented by the database server.

There are a number of other related commands with the same property:

• LOAD
• UNLOAD
• OUTPUT
• INFO

One of the complicating factors for LOAD is that the file name is specified by the client on the local machine but the contents of the file may have to be transferred over the network to where the database server resides. That is not an insuperable problem; similar issues can arise with BLOB and CLOB files, and have been overcome.

These operations are all implemented by ISQL and DB-Access (and by tools such as my SQLCMD — you may have to register to download it, but registration is free and the email load is not onerous).

Answer 3

How about using external tables to load data ? INSERT INTO target1 SELECT * FROM ext1;

https://www.ibm.com/support/knowledgecenter/en/SSGU8G_12.1.0/com.ibm.sqls.doc/ids_sqs_2068.htm