IBM Support

Cloud Pak for Data -Fetching data from Informix (using native SQLI connection) in a Python notebook fails with a NumberFormatException error

Troubleshooting


Problem

You are using a native SQLI connector to access an Informix database. You have used the "insert the code" feature in your Jupyter notebook to retrieved and show the data, but running the notebook cell produces a NumberFormatError:

Caused by: java.sql.SQLException: System or internal error java.lang.NumberFormatException: For input string: "customer_num".

Diagnosing The Problem

This seems more like an issue in the data itself which is not able to convert. The Spark JDBC reader inspects a subset of the data (not the metadata) and determines the data type based on that. So the error might be in the data itself.

The stack trace suggests that the Spark JDBC reader tries to read a long value (IfxResultSet.getLong), but hits a string value, so a NumberFormatException is thrown(NumberFormatException: For input string: "customer_num").

Resolving The Problem

The solution is to add a connection property to the URL (DELIMIDENT=Y).

You can add .option('DELIMIDENT','Y') to the read method. Consider the syntax with regards to indentation and continuation ( \).

Document Location

Worldwide

[{"Line of Business":{"code":"LOB10","label":"Data and AI"},"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Product":{"code":"SSHGYS","label":"IBM Cloud Pak for Data"},"ARM Category":[{"code":"a8m50000000L1dGAAS","label":"Analyze->Models->Notebooks"}],"ARM Case Number":"TS004521036","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"All Version(s)"}]

Document Information

Modified date:
11 January 2021

UID

ibm16402493