Connecting to Your Database
Working with Native Database Interfaces

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.

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.

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.

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.

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.

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

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.

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.

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.

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.

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.

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

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: