IBM Informix Data Source

This article includes information about how to install the Informix JDBC driver, how to connect to an Informix database, and a list of the Informix data types that  supports. With the JDBC driver installed, the Informix data source is available as an option in the connection wizard when creating an API.
lac52
This article includes information about how to install the Informix JDBC driver, how to connect to an Informix database, and a list of the Informix data types that 
CA Live API Creator
 supports. With the JDBC driver installed, the Informix data source is available as an option in the connection wizard when creating an API.
In this article:
 
 
Install the Informix JDBC Driver
Copy the 
ifxjdbc.jar
 JAR file (the JDBC driver) into the directory that is based on the Java container on which you have installed 
CA Live API Creator
:
The self-contained, single-user version of Live API Creator that is based on Jetty
Copy the files into the 
%JETTY_HOME%/caliveapicreator/lib/ext
 directory.
Apache Tomcat
Copy the files into the 
${CATALINA_HOME}/lib
 directory.
Oracle WebLogic
Copy the files into the 
<Your WebLogic Domain Directory>/lib
 directory.
JBoss/WildFly
For more information, see Install on JBoss.
IBM WebSphere
Add the JDBC driver into your Web application server.
Using the WebSphere administrative console, create a JDBC provider for the driver in the server.
For more information about how to create a JDBC provider, see the IBM WebSphere documentation
For more information about the versions of the JDBC drivers that 
CA Live API Creator
 supports, see Installation Requirements and Supported Platforms.
Connect to an Informix Database
You can connect to your Informix database using the following methods:
  • (If you have not created your API yet) When you create your API.
    For more information about how to create an API, see Creating APIs.
  • (If you have an existing API) By adding a connection to an Informix data source.
    For more information about how to add a connection to a data source, see Database Connectivity
The following procedure details how to connect to your Informix database when creating your API.
Prerequisites: 
You know your JDBC connection information.
Follow these steps:
 
  1. In API Creator, on the APIs page, click 
    Create New API
    .
    The connection wizard opens.
  2. Click 
    Database First
     as the approach you want to use to create your API.
  3. Select 
    Informix
     as the data source.
  4. Complete the 
    Host
    Port
    , and 
    Password
     fields, including the following fields, and then click 
    Continue
    :
    Database
     
    The database name. You can use double quotes ("), for example 
    "Foo"
    , for the database name to accept the value of mixed-case (non-standard) database names.
    Schema
     
    The schema name for this database. You can use double quotes (") for the schema name to accept the value of mixed-case (non-standard) schema names.
    Example:
     
    "informix"
     
    Username
     
    The username that is used to connect to this database. You can use double quotes (") for the username to accept the value of mixed-case (non-standard) usernames.
You are connected to the Informix database.
The Informix data source URL contains the parameter 
DELIMIDENT=y
. This parameter is required by  
CA Live API Creator
 to pass column names using single quote (') in the SQL to the database.
Informix Supported Data Types
When you are setting up a connection to an external data source, 
CA Live API Creator
 must map the data types in the data source to data types in 
CA Live API Creator
. The following table lists the different data type mappings applicable when working with an Informix database.
CA Live API Creator
 supports a subset of the full range of Informix data types.
For more information about the Informix data types, see the Informix documentation.
CA Live API Creator
 supports read and write the following Informix data types:
Data type
Modeled as...
Notes
bigint
number
bigserial
number
JSON and BSON
JsonType
byte
binary
char(n)
string
character varying
string
date
date
datetime year to fraction(n)
IntervalTimePiecesType
date
time
timestamp
"datetime year to year": { "year": 1 },
"datetime year to month": { "year": 1, "month": 1 },
"datetime year to hour": { "year": 1, "month": 1, "day": 1, "hour": 0 },
"datetime year to minute": { "year": 1, "month": 1, "day": 1, "hour": 0, "minute": 0 },
DATE
"datetime year to day": "0001-01-01",
TIMESTAMP
"datetime year to second": "9999-12-31T23:59:59", "datetime year to fraction": "9999-12-31T23:59:59.999", "datetime year to fraction(1)": "9999-12-31T23:59:59.9", "datetime year to fraction(2)": "9999-12-31T23:59:59.99", "datetime year to fraction(3)": "9999-12-31T23:59:59.999", "datetime year to fraction(4)": "9999-12-31T23:59:59.9999", "datetime year to fraction(5)": "9999-12-31T23:59:59.99999",
TIME
"datetime hour to second": "18:23:38", "datetime hour to fraction": "18:23:38",
decimal
bigdecimal
double
number
float
number
int, int8, integer
number
interval year to month
IntervalYearToMonthType
Stores a span of time (or level of effort) in units of 
years
 and 
months
.
interval year to year": {
"positive": true,
"years": 0
}
"interval year to month": {
"positive": true,
"years": 9999,
"months": 11
}
interval day to fraction(n)
IntervalDaysHoursMinutesSecondsType
Stores a span of time in a contiguous set of units of 
days
hours
minutes
seconds
, and 
fractions
 
of a second
.
"interval day to fraction(5)": { "positive": true, "days": 99, "hours": 1, "minutes": 10, "seconds": 30, "picos": 990000000000 }
There is a known issue with the JDBC driver handling fractions greater than -999999999-11
money
number
nchar
string
numeric
number
nvarchar
string
serial
number
smallfloat
number
smallint
number
text
string
varchar
string
boolean
boolean
blob/clob
binary
byte
binary
lvarchar
string
row
object
set(e)
collection
list(e)
collection
multiset(e)
collection
Manage Database Functions and Stored Procedures
When you connect to Informix database, 
CA Live API Creator
 scans the available stored procedures and database functions and expose them as RESTful endpoints.
 
CA Live API Creator
 exposes the stored procedures that are defined as mode "O"-owner and "R"-restricted. You can define schema filters to filter out the stored procedures that you do not want to expose as REST endpoints. 
For more information about how to define schema filters, see Define Schema Filters.
View Database Functions and Stored Procedures
You can view the stored procedures and database functions on the Schema page in API Creator. 
For more information about how to view your stored procedures, including how to invoke them, see Manage Stored Procedures.
Identify and Reference Database Functions and Stored Procedures
Database functions and stored procedures with unique names appear as the REST endpoint.
The following example shows how you can identify or reference a stored procedure in 
CA Live API Creator
:
prefix:procname
The following example shows how you can identify or reference a database function in 
CA Live API Creator
:
prefix:functionname
Use Overloaded Database Functions and Stored Procedures
Your Informix database can include overloaded database functions and stored procedures. The name for the overloaded database function or stored procedure appears as part of the REST endpoint. For example, it has the same name but different IN parameters. 
CA Live API Creator
 models the overloaded database function or stored procedure names by using the database function or stored procedure name, followed by a period-separated list using the argument data type.
The following examples show how you can use the 
functionname
 overloaded database function using the specific name argument data types:
prefix:functionname.functionname-int
prefix:functionname.functionname-lvarchar
The following examples show how you can use the 
procname
 overloaded stored procedure using the specific name argument data types:
prefix:procname.procname-int
prefix:procname.procname-lvarchar
Test Database Functions and Stored Procedures
Test your database functions and stored procedures in the REST Lab. Issue a POST request while passing the 
IN
 argument values.
For more information:
Use Sequences in Informix Data Sources
Informix typically handles auto-generated primary keys using sequences, which are database objects that the sequence generator in the database uses to generate sequential values. When you connect to an Informix data source, 
CA Live API Creator
 detects the sequences that are defined in your schema.
For more information about the approaches to generating sequential values, see Associate Entities to Sequences.
Informix Data Source Limitations
The following are known limitations in Informix data sources.
Permission Denied Error When Accessing BLOB Types
You can get a permission denied error when you try to access BLOB types. To resolve this issue, consider appending the 
LOBCACHE=-1
 property to the end of URL in the 
URL
 field on the Connection page.
For more information about this field, see Database Connectivity.
Informix JDBC Driver Installed with Other JDBC Drivers
The Informix JDBC driver (the JAR file) and the MongoDB JDBC driver (the JAR file) reference different versions of the BSON library. These references conflict while running in 
CA Live API Creator
. You can install the Informix JDBC driver or the MongoDB JDBC driver, but you cannot have them installed simultaneously.
For more information about this known issue, see Release Notes.