Summary:
See also: Transactions, Static SQL, Dynamic SQL, Result Sets, SQL Errors, Programs.
A Database Connection is a session of work, opened by the program to communicate with a specific database server, in order to execute SQL statements as a specific user.
The database user can be identified explicitly for each connection. Usually, the user is identified by a login and a password, or by using the authentication mechanism of the operating system (or even from a tier security system).
The database connection instructions can not be prepared as Dynamic SQL statements; you must use static SQL statements.
There are two kind of connection modes: unique-session and multi-session mode. When using the DATABASE and CLOSE DATABASE instructions, you are in unique-session mode. When using the CONNECT, SET CONNECTION and DISCONNECT instructions you are in multi-session mode. The modes are not compatible. It is strongly recommended that you choose the session mode and not mix both kinds of instructions.
In unique-session mode, you simply connect with the DATABASE instruction; that creates a current session. You disconnect from the current session with the CLOSE DATABASE instruction, or when another DATABASE instruction is executed, or when the program ends.
In multi-session mode, you open a session with the CONNECT instruction; that creates a current session. You can open other connections with subsequent CONNECT instructions. To switch to a specific session, use the SET CONNECTION instruction; this suspends other opened connections. Finally, you disconnect from the current, from a specific, or from all sessions with the DISCONNECT instruction. The end of the program disconnects all sessions automatically.
Once connected to a database server, you have a current database session. Any subsequent SQL statement is executed in the context of the current database session.
The Database Specification identifies the data source (the database and database server) to which you want to connect. There are different ways to identify the data source, depending on the database interface architecture used by the runtime system.
Two database interface architectures are provided with the runtime system: the SDI (Standard Database Interface) and the ODI (Open Database Interface). These architectures manage database specification in different ways, because of database vendor connection rules and APIs constraints. For example, when you connect to Oracle, you cannot specify the database server as you do with Informix by using the 'dbname@dbserver' notation.
For portability reasons, it is not recommended that you use database vendor specific syntax in the database specification (like 'dbname@dbserver'). We recommend using a simple symbol instead, and configuring the connection parameters in external resource files. The ODI architecture allows this indirect database specification using the FGLPROFILE configuration file.
When using a native Informix database driver based on the Standard Database Interface architecture (i.e. runners linked with the libix* libraries), you can use the following syntax for the database specification:
| Database Specification | Description |
| dbname | Connects to the database server identified by the Informix environment (for example, with the INFORMIXSERVER environment variable) and opens the database dbname. |
| @dbserver | Connects to the database server identified by dbserver. This database specification does not select any database, the program is only connected to the database server. |
| dbname@dbserver | Connects to the database server identified by dbserver and opens the database dbname. |
Informix environment variables on Windows platforms
On Windows platforms, in a C console application, the Informix environment variables must be set with a call to ifx_putenv(). See INFORMIX ESQL/C documentation for more details about environment settings.
By default, the database driver automatically calls ifx_putenv() for all standard Informix environment variables such as INFORMIXDIR with the current value set in the console environment. You can specify additional environment variables to be set with the following FGLPROFILE entries:
dbi.stdifx.environment.count = max
dbi.stdifx.environment.index = "variable"
When using a database driver based on the Open Database Interface architecture (i.e. runners linked with the libifx*, libora*, libdb2* libraries), you can use either Direct Database Specification or Indirect Database Specification.
To connect to a database server, additional connection parameters are often required. Most database engines require a name to identify the server, a name to identify the database entity, a user name and a password. Some parameters might be omitted: For example, when using Oracle, the server can be implicitly defined by the ORACLE_SID environment variable if the program and the database server run on the same system. The ODI architecture allows you to define these parameters indirectly in the FGLPROFILE configuration file.
In the Direct Database Specification method, the database name used in the program is not used in FGLPROFILE to define the data source with a 'dbi.database.dbname.source' entry. In this case, the database specification used in the connection instruction is used as the datasource.
This method is well known for standard Informix drivers, where you directly specify the database name and, if needed, the Informix server:
01MAIN02DATABASE stores@orion03...04END MAIN
In the next example, the database server is PostgreSQL. The string used in the connection instruction defines the PostgreSQL database (stock), the host (localhost), and the TCP service (5432) the postmaster is listening to. As PostgreSQL syntax is not allowed in standard BDL, a CHAR variable must be used:
01MAIN02DEFINE db CHAR(50)03LET db = "stock@localhost:5432"04DATABASE db05...06END MAIN
In Indirect Database Specification method, the database specification used in the connection instruction corresponds to a 'dbi.database.dbname.source' entry defined in the FGLPROFILE configuration file. In this case, the database specification is considered a key to read the connection information from the configuration file:
Program:
01MAIN02DATABASE stores03...04END MAIN
FGLPROFILE file:
dbi.database.stores.source = "stock@localhost:5432" dbi.database.stores.driver = "dbmpgs721" dbi.database.stores.username = "postgres"
This technique is flexible: The database name in programs is a kind of alias used to define the real database. Using this method, you can develop your application with the database name "stores" and connect to the real database "stores1" in a production environment.
In FGLPROFILE, the entries starting with 'dbi.database' group information defining data sources by name:
dbi.database.dsname.source = "value" dbi.database.dsname.schema = "value" dbi.database.dsname.driver = "value" dbi.database.dsname.username = "value" dbi.database.dsname.password = "value"
The "source" entry identifies the database vendor data source name. The following table shows some examples of this parameter for some well-known database vendors:
| Database Server | Value of "source" entry | Description |
| Genero DB | datasource | ODBC Data Source |
| Generic ODBC | datasource | ODBC Data Source |
| IBM DB2 | dsname | DB2 Catalogued Database |
| INFORMIX | dbname[@dbserver] | Database Name @ Informix Server |
| MySQL | dbname[@host[:port]] or dbname[@localhost~socket] |
Database Name @ Host Name :
TCP Port or Database Name @ Local host ~ Unix socket file |
| MySQL | dbname[@host[:port]] | Database Name @ Host Name : TCP Port |
| ORACLE | tnsname | Oracle TNS Service name |
| PostgreSQL | dbname[@host[:port]] | Database Name @ Host Name : TCP Port |
| SQL Server | datasource | ODBC Data Source |
| Sybase ASA | dbname[@engine] | Database Name @ Engine Name |
If the "source" entry is defined with an empty value (""), the database interface connects to the default database server, which is usually the local server. If this entry is not present in FGLPROFILE, the Direct Database Specification method takes place (see above for more details).
The "schema" entry can be used to specify a database schema name to be selected when connection is established. This is useful for example with Oracle or DB2 databases, where tables are not visible to the user if the table prefix is not specified (schemaname.tablename) and the current schema is not the schema of the table.
The "driver" entry identifies the shared library or DLL to be used if the runner was created with the 'dynamic' database interface. The driver definition is mandatory when using runners created with dynamic database interface. Driver file names do not have to be specified with a file extension.
If you have a lot of databases, you can define a default driver with the Default Database Driver entry.
Database drivers shared libraries are located in FGLDIR/bin. You can create a database driver shared library by using the fglmksdl tool. Some drivers may not be available on a specific platform (for example if the target database client does not exist). Contact your support if you do not find the driver you are looking for.
The following table defines the database driver names according to the database client type:
| Database Type | Driver library prefix | Example |
| Genero DB | dbmads | dbmads3x.so |
| Generic ODBC | dbmodc | dbmodc3x.dll |
| Informix | dbmifx | dbmifx9xx.so |
| IBM DB2 | dbmdb2 | dbmdb28x.so |
| MySQL | dbmmys | dbmmys41x.so |
| ORACLE | dbmora | dbmora92x.so |
| PostgreSQL | dbmpgs | dbmpgs80x.so |
| SQL Server | dbmmsv | dbmmsv80.dll |
| Sybase ASA | dbmasa | dbmasa8x.so |
The "username" and "password" entries define the default database user, when the program uses the DATABASE instruction or the CONNECT TO instruction without the USER clause.
The "username" and "password" entries take effect based on the connection instruction as described in the following table:
| Connection Instruction | FGLPROFILE | Effect |
| DATABASE
dbname or CONNECT TO "dbname" |
No default user definition
|
No user specification is provided to the database server. Usually, the Operating System authentication takes place. |
| DATABASE
dbname or CONNECT TO "dbname" |
With
default user definition
|
The FGLPROFILE user name and password are used to connect to the database server. |
| CONNECT
TO "dbname" USER "username" USING "password" |
N/A
|
The user information of the CONNECT instruction are used to identify the actual user |
Some database vendor specific connection parameters can be configured by using FGLPROFILE entries with the following syntax:
dbi.database.dsname.dbtype.param.[.subparam] = "value"
The table below describes all database vendor specific parameters supported:
| Database Server | Parameters |
| IBM DB2 | |
| dbi.database.dsname.db2.prepare.deferred True/False Boolean to enable/disable deferred prepare. Example: dbi.database.stores.db2.prepare.deferred=true |
|
| INFORMIX | |
| Warning: For Windows platforms only. dbi.database.dsname.ifx.environment.count = max dbi.database.dsname.ifx.environment.index = "variable" By default, the database driver automatically calls ifx_putenv() for all standard Informix environment variables such as INFORMIXDIR with the current value set in the console environment. You can specify additional environment variables to be set with these entries. See INFORMIX ESQL/C documentation for more details about environment settings. Example: dbi.database.stores.ifx.environment.count = 2 dbi.database.stores.ifx.environment.1 = "SOMEIFXENVVAR" dbi.database.stores.ifx.environment.2 = "ANOTHERIFXENVVAR" |
|
| ORACLE | |
| dbi.database.dsname.ora.prefetch.rows Maximum number of rows to be pre-fetched. Example: dbi.database.stores.ora.prefetch.rows=500 |
|
| dbi.database.dsname.ora.prefetch.memory Maximum buffer size pre-fetching (in bytes). Example: dbi.database.stores.ora.prefetch.memory=4096 |
|
| SQL Server | |
| dbi.database.dsname.msv.logintime Connection timeout (in seconds). Example: dbi.database.stores.msv.logintime=5 |
|
| dbi.database.dsname.msv.prefetch.rows Maximum number of rows to be pre-fetched. Example: dbi.database.stores.msv.prefetch.rows = 50 Usage: You can use this parameter to increase performance. However, the bigger this parameter is, the more memory is used by each program. The default is 10 rows. |
|
| dbi.database.dsname.msv.logintime Connection timeout (in seconds). Example: dbi.database.stores.msv.logintime=5 |
|
| Sybase ASA | |
| dbi.database.dsname.asa.logintime Connection timeout (in seconds). Example: dbi.database.stores.asa.logintime=10 |
Although this is not recommended for abstract programming reasons, you can specify connection parameters in the string used by the connection instructions.
This behavior is enabled when you use a plus sign in the connection string:
dbname+property='value'[,...]
In this syntax, property can be one of the data source definition parameters (driver, source, username) described in Indirect Database Specification, plus the 'resource' property:
resource='resourcename'
The 'resource' property can be used to specify which 'dbi.database' entries have to be read from the FGLPROFILE configuration file. When this property is not used, the database interface reads dbi.database.resourcename entries, as when using the Data Source Configuration method.
In the following example, driver, source and resource are specified in the connection string:
01MAIN02DEFINE db CHAR(50)03LET db = "stores+driver='dbmora',source='orcl',resource='ora'"04DATABASE db05...06END MAIN
To simplify the migration process to other databases, the database interface and drivers can emulate some Informix-specific features like SERIAL columns and temporary tables; the drivers can also do some SQL syntax translation.
Emulations can be controlled with FGLPROFILE parameters. You can disable all possible switches step- by- step, in order to test your programs for SQL compatibility.
Global switch to enable or disable Informix emulations:
dbi.database.dbname.ifxemul = { true | false }
Feature-specific switches:
The 'ifxemul.datatype' switches define if the specified data type must be converted to a native type (for example, when creating a table):
dbi.database.dbname.ifxemul.datatype.type = { true | false }
Here, type can be one of: char, varchar, datetime, decimal, momey, float, real, integer, smallint, serial, text, byte.
To control SERIAL generation type, you can use the following switch:
dbi.database.dbname.ifxemul.datatype.serial.emulation = { "native" | "regtable" | "trigseq" }
When using "native", the database driver creates a native sequence generator - it is fast, but not fully compatible with Informix SERIAL. When using "regtable", you must have the SERIALREG table created - it is slower than the "native" emulation, but compatible to Informix SERIAL. The serial emulation "trigseq", can be used by some database drivers, to use triggers with native sequence generators. See ODI guides for more details.
The 'temptables' switch can be used to control temporary table emulation:
dbi.database.dbname.ifxemul.temptables = { true | false }
The 'dblquotes' switch can be used to define whether double quoted strings must be converted to single quoted strings:
dbi.database.dbname.ifxemul.dblquotes = { true | false }
If this emulation is enabled, all double quoted strings are converted, including database object names.
The 'outers' switch can be used to control Informix OUTER specification:
dbi.database.dbname.ifxemul.outers = { true | false }
It is better to use standard ISO outer joins in your SQL statements.
The 'today' switch can be used to convert the TODAY keyword to a native expression returning the current date:
dbi.database.dbname.ifxemul.today = { true | false }
The 'current' switch can be used to convert the CURRENT X TO Y expressions to a native expression returning the current time:
dbi.database.dbname.ifxemul.current = { true | false }
The 'selectunique' switch can be used to convert the SELECT UNIQUE to SELECT DISTINCT:
dbi.database.dbname.ifxemul.selectunique = { true | false }
It is better to replace all UNIQUE keywords by DISTINCT.
The 'colsubs' switch can be used to control column sub-strings expressions (col[x,y]) to native sub-string expressions:
dbi.database.dbname.ifxemul.colsubs = { true | false }
The 'matches' switch can be used to define if MATCHES expressions must be converted to LIKE expressions:
dbi.database.dbname.ifxemul.matches = { true | false }
It is better to use the LIKE operator in your SQL statements.
The 'length' switch can be used to define if LENGTH function names have to be converted to the native equivalent:
dbi.database.dbname.ifxemul.length = { true | false }
The 'rowid' switch can be used to define if ROWID keywords have to be converted to the native equivalent:
dbi.database.dbname.ifxemul.rowid = { true | false }
It is better to use primary keys instead.
The 'listupdate' switch can be used to convert the UPDATE statements using non-ANSI syntax:
dbi.database.dbname.ifxemul.listupdate = { true | false }
Some database vendor specific connection parameters can be configured by using FGLPROFILE entries with the following syntax:
dbi.database.dsname.dbtype.param.[.subparam] = "value"
With the following entry, you can define a default driver identifying the shared library or DLL to be used to connect to the database:
dbi.default.driver = "value"
When you write a program using Static SQL, the runtime system prepares and executes the statements each time the program line is reached:
01FOR i=1 TO 100002INSERT INTO tab VALUES (i,"xxxx")03END FOR
Normally you should optimize such code with Dynamic SQL. However, the execution of static SQL statements can be optimized with the following FGLPROFILE entry:
dbi.sql.static.optimization.cache.size = max
The value of this entry defines the maximum number of static SQL statements that can be prepared and kept in a cache, to avoid re-preparing the statement each time.
This entry is set to zero (no cache) by default.
When using Informix on Windows platforms, a client program must set the Informix environment variables with the ifx_putenv() function, otherwise it cannot connect to the database engine.
If you have created an Informix runner (with the fglmkrun option -d ifx930 for example), the Informix variables are automatically set with the ifx_putenv() function.
To connect to a database server, the FGL programs must be executed in the correct database client environment. The database client software is usually included in the database server software, so you do not need to install it when your programs are executed on the same machine as the database server. However, you may need to install the database client software in three-tier configurations, where applications and database servers run on different systems.
This section describes basic configuration elements of the database client environment for some well known database servers.
You can set the FGLSQLDEBUG environment variable to get debug information on SQL instructions. This variable can be set to an integer value from 0 to 10, according to the debugging details you want to see. The debug messages are sent to the standard error stream. If needed, you can redirect the standard error output into a file.
Unix (shell) example:
FGLSQLDEBUG=3
export FGLSQLDEBUG
fglrun myprog 2>sqldbg.txt
The SQLCA variable is a predefined record containing information about the execution of an SQL statement.
SQLCA
DEFINE SQLCA RECORD SQLCODE INTEGER, SQLERRM CHAR(71), SQLERRP CHAR(8), SQLERRD ARRAY[6] OF INTEGER, SQLAWARN CHAR(8) END RECORD
01MAIN02WHENEVER ERROR CONTINUE02DATABASE stores03SELECT COUNT(*) FROM foo -- Table should not exist!04DISPLAY SQLCA.SQLCODE, SQLCA.SQLERRD[2]05END MAIN
If an error occurs during an SQL statement execution, you can get the error description in the STATUS, SQLCA.SQLCODE, SQLSTATE and SQLERRMESSAGE built-in registers.
STATUS is the global language error code register, set for any kind of error (even non-SQL). When an SQL error occurs, the Informix SQL error code held by SQLCA.SQLCODE is copied into STATUS. The register SQLCA.SQLCODE returns the Informix error code. SQLSTATE returns the standard ANSI error code and SQLERRMESSAGE returns the database specific error message.
See also: STATUS, SQLCA, SQLSTATE, SQLERRMESSAGE, Exceptions.
OPTIONS SQL INTERRUPT { ON | OFF }
Typical BDL programs control the interrupt signals, by using the following instructions:
If the database server supports SQL interruption, the runtime system can enable interruption of long SQL queries when you set the SQL INTERRUPT program option. When the program gets an interrupt signal from the system, the running SQL statement is stopped and the INT_FLAG global variable is set to TRUE.
01MAIN02DATABASE stock03OPTIONS SQL INTERRUPT ON04LET INT_FLAG = FALSE05SELECT COUNT(*) FROM items WHERE items_value > 10006IF INT_FLAG THEN07DISPLAY "Query was interrupted by user"08END IF09END MAIN
Opens a new database connection in unique-session mode.
DATABASE { dbname[@dbserver] | variable
| string } [EXCLUSIVE]
Tips:
01MAIN02DATABASE stores03SELECT COUNT(*) FROM customer04END MAIN
01MAIN02DEFINE dbname VARCHAR(100)03LET dbname = arg_val(1)04DATABASE dbname05SELECT COUNT(*) FROM customer06END MAIN
Closes the current database connection when in unique-session mode.
CLOSE DATABASE
01MAIN02DATABASE stores103CLOSE DATABASE04DATABASE stores205CLOSE DATABASE06END MAIN
Opens a new database session in multi-session mode.
CONNECT TO dbname [ AS session ]
[ USER username USING password ]
[ WITH CONCURRENT TRANSACTION ]
01MAIN02CONNECT TO "stores1" -- Session name is "stores1"03CONNECT TO "stores1" AS "SA" -- Session name is "SA"04CONNECT TO "stores2" AS "SB" USER "scott" USING "tiger"05END MAIN
Selects the current session when in multi-session mode.
SET CONNECTION { session [DORMANT] | CURRENT DORMANT }
01MAIN02CONNECT TO "stores1"03CONNECT TO "stores1" AS "SA"04CONNECT TO "stores2" AS "SB"05SET CONNECTION "stores1" -- Select first session06SET CONNECTION "SA" -- Select second session07SET CONNECTION "stores1" -- Select first session again08END MAIN
Terminates database sessions when in multi-session mode.
DISCONNECT { ALL | CURRENT | session }
01MAIN02CONNECT TO "stores1"03CONNECT TO "stores1" AS "SA"04CONNECT TO "stores2" AS "SB" USER "scott" USING "tiger"05-- SB is the current database session06DISCONNECT "stores1" -- Continue with SB07DISCONNECT "SB" -- SB is no longer the current session08SET CONNECTION "SA" -- Select second session09END MAIN