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; they must be 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 TO, 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 TO instruction; that creates a current session. You can open other connections with subsequent CONNECT TO 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) you want to connect to.
There are different ways to identify the data source, depending on the database type. 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.
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 following parameters:
| Parameter | Description |
| resource | Specifies which 'dbi.database'
entries have to be read from the FGLPROFILE configuration
file. When this property is set, the database interface reads dbi.database.name.* entries, where name is the value specified for the resource parameter. |
| driver | Defines the database driver library to be loaded (filename without extension). |
| source | Specifies the data source of the database (for example, Oracle's TNS name). |
| username | Defines the name of the database user. |
| password | Defines the password of the database user. Warning: Should not be used in production! |
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
You can use a string variable with the DATABASE or CONNECT TO statement, in order to specify the database source at runtime (to be loaded from your own configuration file or from an environment variable). This solution gives you the best flexibility.
01MAIN02DEFINE db, us, pwd CHAR(50)03LET db = arg_val(1)03LET us = arg_val(2)03LET pwd = arg_val(3)04CONNECT TO db USER us USING pwd05...06END MAIN
When using an Informix database driver, 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. |
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"
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.
The Direct Database Specification method takes place when 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 data source.
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
Indirect Database Specification method takes place when 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:
dbi.database.stores.source = "stock@localhost:5432" dbi.database.stores.driver = "dbmpgs721"
This technique is flexible: The database name in programs is a kind of alias used to define the real database. Using this method, your 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.driver = "value" dbi.database.dsname.username = "value" dbi.database.dsname.password = "value" -- Warning: not encrypted, do not use in production!
The "source" entry identifies the data source name. The following table describes the meaning of this parameter for the supported databases:
| Database Type | Value of "source" entry | Description |
| Genero DB | datasource | ODBC Data Source |
| Generic ODBC | datasource | ODBC Data Source |
| Informix | dbname[@dbserver] | Informix database specification |
| IBM DB2 | dsname | DB2 Catalogued Database |
| MySQL | dbname[@host[:port]] or dbname[@localhost~socket] |
Database Name @ Host Name :
TCP Port or Database Name @ Local host ~ Unix socket file |
| 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 "driver" entry identifies the shared library or DLL to be used. 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/dbdrivers on both UNIX and Windows platforms. 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 (MDAC) | dbmmsv | dbmmsv80.dll |
| SQL Server (Native Client) | dbmsnc | dbmsnc90.dll |
| SQL Server (FreeTDS) | dbmftm | dbmftm90.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.
Warning: The "username" and "password" entries are not encrypted. These parameters are provided to simplify migration and should not be used in production. You better use CONNECT TO with a USER / USING clause to avoid any security hole, or OS user authentication. Example of database servers supporting OS user authentication: Informix, Oracle, SQL Server and Genero db.
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 TO instruction are used to identify the actual user |
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.
Warning: Avoid using Informix emulations; write portable SQL code instead as described in SQL Programming. Informix emulations are only provided to help you in the migration process. Disabling Informix emulations improves performance, because SQL statements do not have to be parsed to search for Informix-specific syntax.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 whether 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, money, 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 to 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.
The 'temptables' switch can be used to control temporary table emulation:
dbi.database.dbname.ifxemul.temptables = { true | false }
The 'temptables.emulation' switch can be used to specify what type of tables must be used to emulate temporary tables:
dbi.database.dbname.ifxemul.temptables.emulation = { "default" | "global" }
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 whether 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 whether 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 whether ROWID keywords have to be converted to 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 }
The 'extend' switch can be used to convert simple EXTEND() expressions to native date/time expressions:
dbi.database.dbname.ifxemul.extend = { 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"
The table below describes all database vendor specific parameters supported:
| Database Server | Parameters |
| Genero DB | |
| dbi.database.dsname.ads.schema Name of the database schema to be selected after connection is established. Example: dbi.database.stores.ads.schema = "store2" Usage: Set this parameter to a specific schema in order to share the same table with all users. |
|
| IBM DB2 | |
| dbi.database.dsname.db2.schema Name of the database schema to be selected after connection is established. Example: dbi.database.stores.db2.schema = "store2" Usage: Set this parameter to a specific schema in order to share the same table with all users. |
|
| dbi.database.dsname.db2.prepare.deferred True/False Boolean to enable/disable deferred prepare. Example: dbi.database.stores.db2.prepare.deferred = true Usage: Set this parameter to true if you do not need to get SQL errors during PREPARE statements: SQL statements will be sent to the server when executing the statement (OPEN or EXECUTE). The default is false (SQL statements are sent to the server when doing the PREPARE). |
|
| ORACLE | |
| dbi.database.dsname.ora.schema Name of the database schema to be selected after connection is established. Example: dbi.database.stores.ora.schema = "store2" Usage: Set this parameter to a specific schema in order to share the same table with all users. |
|
| dbi.database.dsname.ora.prefetch.rows Maximum number of rows to be pre-fetched. Example: dbi.database.stores.ora.prefetch.rows = 50 Usage: You can use this parameter to increase performance by defining the maximum number of rows to be fetched automatically. However, the bigger this parameter is, the more memory is used by each program. This parameter applies to all cursors in the application. The default is 10 rows. |
|
| dbi.database.dsname.ora.prefetch.memory Maximum buffer size for pre-fetching (in bytes). Example: dbi.database.stores.ora.prefetch.memory = 4096 Usage: This parameter is equivalent to prefetch.rows, but here you can specify the memory size instead of the number of rows. As prefetch.rows, this parameter applies to all cursors in the application. The default is 0, which means that memory size is not included in computing the number of rows to pre-fetch. |
|
| dbi.database.dsname.ora.sid.command SQL command (SELECT) to generate a unique session id (used for temp table names). Example: dbi.database.stores.ora.sid.command = "SELECT TO_CHAR(SID)||'_'||TO_CHAR(SERIAL#) FROM V$SESSION WHERE AUDSID=USERENV('SESSIONID')" Usage: By default the driver uses "SELECT USERENV('SESSIONID') FROM DUAL". This is the standard session identifier in Oracle, but it can become a very large number and can't be reset. This parameter gives you the freedom to provide your own way to generate a session id. The SELECT statement must return a single row with one single column. Value can be an integer or an identifier. |
|
| SQL Server (MDAC) | |
| dbi.database.dsname.msv.logintime Connection timeout (in seconds). Example: dbi.database.stores.msv.logintime = 5 Usage: Set this parameter to raise an SQL error if the connection can not be established after the given number of seconds. The default is 5 seconds. |
|
| 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. |
|
| SQL Server (NCLI) | |
| dbi.database.dsname.snc.logintime Connection timeout (in seconds). Example: dbi.database.stores.snc.logintime = 5 Usage: Set this parameter to raise an SQL error if the connection can not be established after the given number of seconds. The default is 5 seconds. |
|
| dbi.database.dsname.snc.prefetch.rows Maximum number of rows to be pre-fetched. Example: dbi.database.stores.snc.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. |
|
| SQL Server (FreeTDS) | |
| dbi.database.dsname.ftm.logintime Connection timeout (in seconds). Example: dbi.database.stores.ftm.logintime = 5 Usage: Set this parameter to raise an SQL error if the connection can not be established after the given number of seconds. The default is 5 seconds. |
|
| dbi.database.dsname.ftm.prefetch.rows Maximum number of rows to be pre-fetched. Example: dbi.database.stores.ftm.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. |
|
| Sybase ASA | |
| dbi.database.dsname.asa.logintime Connection timeout (in seconds). Example: dbi.database.stores.asa.logintime = 10 Usage: Set this parameter to raise an SQL error if the connection can not be established after the given number of seconds. The default is 5 seconds. |
Connecting to a database server is not just specifying a database name. Informix 4gl programmers are used to write "DATABASE dbname" to get connected. But this is only possible when the database server is configured to trust Operating System users. Only a few database server support OS authentication. Database users are usually defined in the database server and must be explicitly identified by a user name and password. Note also that some database servers support external authentication methods, which can be used with Genero. See DB specific documentation for more details.
In order to specify a user name and password, you must use the USER/USING clause of the CONNECT instruction:
01MAIN02CONNECT TO "orc1fox+driver='dbmoraA2x'" USER "scott" USING "tiger"03...04END MAIN
User name and login could be specified with FGLPROFILE entries but we strongly discourage you to do this for security reasons.
Informix users are operating system users with database connection privileges, if the client program resides on the same machine as the database server, you typically use OS authentication and don't need to provide a user name and password.
However, you need to specify a user name and password if you want to connect to a remove server that does not have trusted connection configured.
Oracle users can be authenticated in different manner: as DB users, as OS users or with another external authentication method like Kerberos.
If you don't specify the USER/USING clause, OS authentication takes place.
An Oracle connection can also be established as SYSDBA or SYSOPER users. This is possible with Genero by specifying the following strings after the user name in the USER clause of the CONNECT instruction:
| String passed to USER clause after user name | Effect as Oracle connection |
| /SYSDBA | Connection will be established as SYSDBA user. |
| /SYSOPER | Connection will be established as SYSOPER user. |
Note that you must specify the user login before the /SYSDBA or /SYSOPER strings:
01 CONNECT TO "orc1fox+driver='dbmoraA2x'" USER "orauser/SYSDBA" USING "fourjs"SQL Server users can be authenticated as DB users or with the Windows users.
If you don't specify the USER/USING clause, OS authentication takes place.
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"
To connect to a database server, the BDL 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.
Use SQLCA.SQLCODE for SQL error management, and STATUS for 4gl errors.
See also: STATUS, SQLCA, SQLSTATE, SQLERRMESSAGE, Exceptions.
OPTIONS SQL INTERRUPT { ON | OFF }
Typical FGL 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.
01MAIN02DEFER INTERRUPT03DEFER QUIT04DATABASE stock05OPTIONS SQL INTERRUPT ON06LET INT_FLAG = FALSE07SELECT COUNT(*) FROM items WHERE items_value > 10008IF INT_FLAG THEN09DISPLAY "Query was interrupted by user"10END IF11END MAIN
Opens a new database connection in unique-session mode.
DATABASE { dbname[@dbserver] | variable
| string } [EXCLUSIVE]
The DATABASE instruction opens a connection to the database server, like CONNECT TO, but without user and password specification.
By default the database user is identified by the current operating system user, but it can be authenticated according to database specification parameters.The EXCLUSIVE keyword can be used to open an Informix database in exclusive mode to prevent access by anyone but the current user. Informix only!
If a current connection exists, it is automatically closed before connecting to the new database.If the connection could not be established, the instruction raises an exception. For example, if you specify a database that the runtime system cannot locate, or cannot open, or for which the user of your program does not have access privileges, an exception is raised.
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 | DEFAULT } [ AS session ]
[ USER username USING password ]
[ WITH CONCURRENT TRANSACTION ]
The CONNECT TO instruction opens a database connection. If the instruction successfully connects to the database environment, the connection becomes the current database session for the application.
An application can connect to several database environments at the same time, and it can establish multiple connections to the same database environment, provided each connection has a unique connection name. If you need only one connexion to a database, you can use the DATABASE instruction.
With Informix database servers, when using the DEFAULT keyword, you connect to the default Informix database server, identified by the INFORMIXSERVER environment variable, without any database selection.
By default the database user is identified by the current operating system user, but it can be authenticated according to database specification parameters.
When the USER username USING password clause is specified, the database user is identified by username and password, ignoring all other settings defined by the database specification. See also Database user authentication.The WITH CONCURRENT TRANSACTION clause allows a program to open several transactions concurrently in different database sessions.
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 | DEFAULT } [DORMANT] | CURRENT DORMANT }
When using the DEFAULT keyword, it identifies the default database server connection established with a CONNECT TO DEFAULT or a DATABASE instruction. Informix only!
To make the current connection dormant, use CURRENT DORMANT keyword. Informix only!
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 }
When using the DEFAULT keyword, it identifies the default database server connection established with a CONNECT TO DEFAULT or a DATABASE instruction. Informix only!
Use the ALL keyword to terminate all opened connections. From that point, you must establish a new connection to execute SQL statements.Use the CURRENT keyword to terminate the current connection only. From that point, you must select another connection or establish a new connection to execute SQL statements.
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