Can't run generated code from deprecated functionality to load data for an Informix connection

You receive a "DatabaseError: Execution failed on sql: ... unable to rollback" when running the code that is generated to load data from an Informix database when using the deprecated functionality and you try to query a table with an upper-case name.

Cause

The Informix database is configured for case-sensitive identifiers and you need to update the generated code.

Resolving the problem

If you can, change to using the code that is generated using the Flight service to load data from an Informix connection . If you want to continue using the code that is generated by the deprecated functionality, you need to edit the generated code as follows:

For Python, add the connection property 'DELIMIDENT=Y' to the connection and surround the upper-case identifier with double-quotes (""). Replace the following lines:

Informix_connection = jaydebeapi.connect('com.informix.jdbc.IfxDriver',
'{}://{}:{}/{}:user={};password={};'.format('jdbc:informix-sqli',
Informix_metadata['host'],
Informix_metadata['port'],
Informix_metadata['database'],
Informix_metadata['username'],
Informix_metadata['password']),
[Informix_metadata['username'],
Informix_metadata['password']])

query = 'SELECT * FROM informix.FVT_EMPLOYEE'

With:

Informix_connection = jaydebeapi.connect('com.informix.jdbc.IfxDriver',
'{}://{}:{}/{}:user={};password={};DELIMIDENT=Y;'.format('jdbc:informix-sqli',
Informix_metadata['host'],
Informix_metadata['port'],
Informix_metadata['database'],
Informix_metadata['username'],
Informix_metadata['password']),
[Informix_metadata['username'],
Informix_metadata['password']])

query = 'SELECT * FROM informix."FVT_EMPLOYEE"'

For Spark with Python, replace:

data_df_0 = sparkSession.read.format('jdbc') \
  .option('url', 'jdbc:informix-sqli://{}:{}/{}'.format(Informix_metadata['host'],Informix_metadata['port'],Informix_metadata['database'])) \
  .option('dbtable', 'informix.FVT_EMPLOYEE') \
  .option('user', Informix_metadata['username']) \
  .option('password', Informix_metadata['password']).load()
data_df_0.show(5)

With:

data_df_0 = sparkSession.read.format('jdbc') \
  .option('url', 'jdbc:informix-sqli://{}:{}/{}'.format(Informix_metadata['host'],Informix_metadata['port'],Informix_metadata['database'])) \
  .option('dbtable', 'informix."FVT_EMPLOYEE"') \
  .option('DELIMIDENT', 'Y') \
  .option('user', Informix_metadata['username']) \
  .option('password', Informix_metadata['password']).load()
data_df_0.show(5)

For R, add the connection property 'DELIMIDENT=Y' to the connection and surround all upper case names with double-quotes (""). Replace the following lines:

paste("jdbc:informix-sqli://", Informix_credentials[][["host"]], ":", Informix_credentials[][["port"]],
"/", Informix_credentials[][["database"]],
":user=", Informix_credentials[][["username"]],
";password=", Informix_credentials[][["password"]], ";", sep=""),
...
query <- "SELECT * FROM myschema.MY_TABLE"

With:

paste("jdbc:informix-sqli://", Informix_credentials[][["host"]], ":", Informix_credentials[][["port"]],
"/", Informix_credentials[][["database"]],
":user=", Informix_credentials[][["username"]],
";password=", Informix_credentials[][["password"]],";DELIMIDENT=Y", ";", sep=""),
...
query <- "SELECT * FROM myschema.\"MY_TABLE\""

Parent topic: Troubleshooting the Watson Studio service