Using Informix

About this chapter

This chapter describes how to use the native IBM Informix database interfaces in PowerBuilder.

Supported versions for Informix

You can access the IBM Informix Dynamic Server (IDS) database version 10.x/12.x using the PowerBuilder I10 native Informix database interfaces. You can also access Informix OnLine and Informix Standard Engine (SE) databases.

The I10 interface in PBI10.dll requires the Informix Client SDK 2.9 or later for Informix application development and Informix Connect 2.9 or later for runtime deployment.

Supported Informix datatypes

The Informix database interfaces support the Informix datatypes listed in the following table in DataWindow objects and embedded SQL.

Blob

LVarChar

Boolean

Money

Byte (a maximum of 2^31 bytes)

NChar

Char

NVarChar

Clob

Real

Date

Serial

DateTime

Serial8

Decimal

SmallInt (2 bytes)

Float

Text (a maximum of 2^31 bytes)

Int8

Time

Integer (4 bytes)

VarChar (1 to 255 bytes)

Interval

 


Datatype conversion

When you retrieve or update columns, PowerBuilder converts data appropriately between the Informix datatype and the PowerScript datatype. Keep in mind, however, that similarly or identically named Informix and PowerScript datatypes do not necessarily have the same definitions.

For information about the definitions of PowerScript datatypes, see the section called “Datatypes” in PowerScript Reference.

Informix DateTime datatype

The DateTime datatype is a contiguous sequence of boxes. Each box represents a component of time that you want to record. The syntax is:

DATETIME largest_qualifier TO smallest_qualifier

PowerBuilder defaults to Year TO Fraction(5).

For a list of qualifiers, see your Informix documentation.

To create your own variation of the DateTime datatype:

  1. In the Database painter, create a table with a DateTime column.

    For instructions on creating a table, see the section called “Working with tables” in Users Guide.

  2. In the Columns view, select Pending Syntax from the Objects or pop-up menu.

    The Columns view displays the pending changes to the table definition. These changes execute only when you click the Save button to save the table definition.

  3. Select Copy from the Edit or pop-up menu or click the Copy button.

    The SQL syntax (or the portion you selected) is copied to the clipboard.

  4. In the ISQL view, modify the DateTime syntax and execute the CREATE TABLE statement.

    For instructions on using the ISQL view, see Users Guide.

Informix Time datatype

The Informix database interfaces also support a time datatype. The time datatype is a subset of the DateTime datatype. The time datatype uses only the time qualifier boxes.

Informix Interval datatype

The interval datatype is one value or a sequence of values that represent a component of time. The syntax is:

INTERVAL largest_qualifier TO smallest_qualifier

PowerBuilder defaults to Day(3) TO Day. For more about interval datatypes, see your Informix documentation.

Features supported by the I10 interface

The interface of I10 supports several features that are not available when you use the IN9 interface. Some of these features require a specific version of the Informix Dynamic Server database.

Accessing Unicode data

PowerBuilder can connect, save, and retrieve data in ANSI/DBCS databases using the IN9 interface, but the IN9 interface does not support Unicode databases. The Informix I10 interface supports ANSI/DBCS and Unicode databases.

The I10 native interface uses the Informix GLS (Global Language Support) API for global language support. The native interface uses three DBParms to help you set up the locale used in the current connection:

These parameters are available on the Regional Settings tab page in the Database Profile Setup dialog box.

Client_Locale

Client_Locale specifies the value of the Informix environment variable CLIENT_LOCALE. The format is language_territory.codeset. For example:

Client_Locale='en_us.1252'
Client_Locale='en_us.utf8'

The I10 interface uses this setting to access string data in an Informix database and to process SQL statements. If you do not set the DBParm, the default locale value is based on the OS locale.

DB_Locale

DB_Locale specifies the value of the Informix environment variable DB_LOCALE. The format is language_territory.codeset. For example:

DB_Locale='en_us.1252'
DB_Locale='en_us.utf8'

DB_LOCALE specifies the language, territory, and code set that the database server needs to correctly interpret locale-sensitive datatypes such as NChar and NVarChar in a specific database. The code set specified in DB_LOCALE determines which characters are valid in any character column, as well as in the names of database objects such as databases, tables, columns, and views. If you do not set the DBParm, the I10 interface assumes that the DB_LOCALE value is the same as the CLIENT_LOCALE value.

You can set the CLIENT_LOCALE and DB_LOCALE environment variables directly using the Informix Setnet32 utility, available in the Utilities folder for the Informix database interfaces in the Objects view in the Database painter or the Database Profiles dialog box.

For more information about the Informix CLIENT_LOCALE and DB_LOCALE environment variables, see the IBM Informix GLS User's Guide, currently available at the Informix library website at http://publib.boulder.ibm.com/infocenter/idshelp/v111/index.jsp?topic=/com.ibm.glsug.doc/glsug.htm.

StrByCharset

The StrByCharset DBParm specifies how to convert string data between PowerBuilder Unicode strings and Informix client multibyte strings. By default, string conversion for UTF-8 code sets is based on the UTF-8 code set, and string conversion for non-UTF-8 code sets is based on the current OS code page. If StrByCharset is set to 1 (true), string conversion is based on the code set specified in the DBParm Client_Locale.

Assigning an owner to the PowerBuilder catalog tables

When you use the I10 interface, you can use the PBCatalogOwner DBParm on the System tab page to assign a nondefault owner to the extended attribute system tables. For ANSI-compliant databases, the owner name that you specify must be unique but the table name does not have to be unique. You can create multiple sets of catalog tables prefaced with different user names. However, if the database is not ANSI-compliant, the table name must be unique, so that only one set of catalog tables can be created with an assigned owner name.

Support for long object names

The I10 interface supports Informix long object names with up to 128 characters.

Renaming an index

With IDS 9.2.1 and later, you can change the name of an index in the Database painter when you are connected using the I10 interface. The I10 interface uses the IDS RENAME INDEX statement to change the name of the index. You need only drop and recreate the index if you want to make other changes.

SQL statement caching

In IDS 9.2.1 and later, the database server uses the SQL statement cache (SSC) to store SQL statements across user sessions. When any user executes a statement already stored in the SQL statement cache, the database server does not parse and optimize the statement again, resulting in improved performance. The statement must be a SELECT, UPDATE, DELETE, or INSERT statement, and it cannot contain user-defined routines.

There are several ways to configure caching on the server. The SET STATEMENT CACHE statement takes precedence over the STMT_CACHE environment variable and the STMT_CACHE configuration parameter. You must enable the SQL statement cache, either by setting the STMT_CACHE configuration parameter or by using the Informix onmode utility, before the SET STATEMENT CACHE statement can execute successfully.

You can set the StmtCache DBParm on the System tab page in the Database Profile Setup dialog box for I10 connections to turn SQL statement caching on or off on the client. However, the server must be configured to support SQL statement caching before you can access the cache from the client.

For more information about Informix SQL statement caching, see the IBM Informix Dynamic Server Performance Guide at http://publib.boulder.ibm.com/infocenter/idshelp/v111/index.jsp?topic=/com.ibm.glsug.doc/glsug.htm.

Creating and dropping indexes without locking

In IDS 10.0 and later, the SQL syntax of CREATE INDEX and DROP INDEX supports the ONLINE keyword to create or drop an index in an online environment where the database and its tables are continuously available. When you use the ONLINE keyword to create or drop an index, data definition language (DDL) operations execute without applying an exclusive lock on the table on which the specified index is defined.

If you use CREATE INDEX ONLINE to create an index on a table that other users are accessing, the index is not available until no users are updating the table.

If you issue DROP INDEX ONLINE to drop an index, no users can reference the index, but concurrent data manipulation language (DML) operations can use the index until the operations terminate. Dropping the index is deferred until no users are using the index.

You can set the OnlineIndex static DBParm on the System tab page in the Database Profile Setup dialog box for I10 connections to specify that the Database painter should use the ONLINE keyword when you create or drop an index.

Clustered index not supported

You cannot create a clustered index using online mode because it is not supported by IDS.

Column-level encryption

In IDS 10.0 and later, the SQL statement SET ENCRYPTION PASSWORD can improve the confidentiality of data and support data integrity by defining or resetting a password for encryption and decryption of data at the column level.

You can set the EncryptionPass and Hint static DBParms on the System tab page in the Database Profile Setup dialog box for I10 connections to specify a password and a hint to help you remember the password. The application uses built-in Informix functions to encrypt and decrypt character data.

Using multiple OUT parameters in user-defined routines

In a user-defined routine (UDR), an OUT parameter corresponds to a value returned through a pointer. Before IDS version 9.4, IDS supported no more than one OUT parameter in a UDR, and any OUT parameter was required to appear as the last item in the parameter list. IDS version 9.4 drops these restrictions, supporting multiple OUT parameters anywhere in the parameter list of the UDR. This feature is available when you use the I10 interface. It provides greater flexibility in defining UDRs, and removes the need to return collection variables in contexts where multiple returned values are required.

To return OUT parameters from a UDR, you must use statement local variables (SLVs).

In the following statement, the OUT parameter in the UDR myfunc is defined using the SLV syntax slvname#out_param_type.

SELECT sales FROM mytable WHERE myfunc(10, sales#money) < 1000

Informix does not support invoking a UDR with OUT parameters using an EXECUTE statement, therefore multiple OUT parameters are not supported in PowerBuilder remote procedure calls and embedded SQL EXECUTE PROCEDURE commands.

Basic software components for Informix

The following figure shows the basic software components required to access an Informix database using the native Informix database interfaces.

Figure: Components of an Informix connection

Preparing to use the Informix database

Before you define the database interface and connect to an Informix database in PowerBuilder, follow these steps to prepare the database for use:

  1. Install and configure the required database server, network, and client software.

  2. Install the native Informix IN9 or I10 database interface.

  3. Verify that you can connect to the Informix server and database outside PowerBuilder.

Step 1: Install and configure the database server

You must install and configure the required database server, network, and client software for Informix.

To install and configure the required database server, network, and client software:

  1. Make sure the Informix database server software and database network software is installed and running on the server specified in your database profile.

    You must obtain the database server and database network software from Informix.

    For installation instructions, see your Informix documentation.

  2. Install the required Informix client software on each client computer on which PowerBuilder is installed.

    Install Informix Connect or the Informix Client SDK (which includes Informix Connect).

    You must obtain the Informix client software from IBM. Make sure the version of the client software you install supports all of the following:

    The operating system running on the client computer

    The version of the database that you want to access

    The version of PowerBuilder that you are running

    For installation instructions, see your Informix documentation.

  3. Make sure the Informix client software is properly configured so that you can connect to the Informix database server at your site.

    Run the SetNet32 utility to configure the client registry settings. When you configure Informix Connect client software, it creates a registry entry in HKEY_LOCAL_MACHINE\Software\Informix\SqlHosts. The registry entry contains parameters that define your network configuration, network protocol, and environment variables. If you omit these values from the database profile when you define the native Informix database interface, they default to the values specified in the registry entry.

    For instructions on configuring your Informix client software, see your Informix documentation.

  4. If required by your operating system, make sure the directory containing the Informix client software is in your system path.

Step 2: Install the database interface

If you install PowerBuilder using the PowerBuilder Installer (an online setup program), the native Informix database interface is automatically installed. If you install PowerBuilder using the downloaded installation package (an offline setup program), select Native Database Interfaces | Informix interface from the list of components.

Step 3: Verify the connection

Make sure you can connect to the Informix server and database you want to access from outside PowerBuilder.

To verify the connection, use any Windows-based utility (such as the Informix ilogin.exe program) that connects to the database. When connecting, be sure to specify the same parameters you plan to use in your PowerBuilder database profile to access the database.

For instructions on using ilogin.exe, see your Informix documentation.

Defining the Informix database interface

To define a connection through an Informix database interface, you must create a database profile by supplying values for at least the basic connection parameters in the Database Profile Setup dialog box for Informix IN9 or I10. You can then select this profile at any time to connect to your database in the development environment.

For information on how to define a database profile, see Using database profiles.

Specifying the server name

When you specify the server name value, you must use the following format to connect to the database through the Informix interfaces:

host_name@server_name

Parameter

Description

host_name

The name of the host computer running the Informix database server. This corresponds to the Informix HOSTNAME environment variable.

server_name

The name of the server containing the Informix database. This corresponds to the Informix SERVER environment variable.


For example, to use a PowerBuilder native interface to connect to an Informix database server named server01 running on a host machine named sales, do either of the following:

  • In a database profile

    Type the host name (sales) in the Host Name box and the server name (server01) in the Server box on the Connection tab in the Database Profile Setup dialog box. PowerBuilder saves this server name as sales@server01 in the database profile entry in the system registry.

  • In a PowerBuilder script

    Type the following in your PowerBuilder application script:

    SQLCA.ServerName = "sales@server01"

    Tip

    If you specify a value for Host Name and Server in your database profile, this syntax displays on the Preview tab in the Database Profile Setup dialog box. You can then copy the syntax from the Preview tab into your script.

Accessing serial values in a PowerBuilder script

If you are connecting to an Informix database from a PowerBuilder script, you can obtain the serial number of the row inserted into an Informix table by checking the value of the SQLReturnData property of the Transaction object.

After an embedded SQL INSERT statement executes, SQLReturnData contains the serial number that uniquely identifies the row inserted into the table.

PowerBuilder updates SQLReturnData following an embedded SQL statement only; it does not update it following a DataWindow operation.