Informix Guide to SQL: Syntax

Informix Guide to SQL: Syntax
Chapter 1: SQL Statements

Home Contents Index Master Index New Book

SQL Statements

COMMIT WORK

Use the COMMIT WORK statement to commit all modifications made to the database from the beginning of a transaction.

CONNECT

Use the CONNECT statement to connect to a database environment.

Syntax

Element Purpose Restrictions Syntax
connection name
Quoted string that assigns a name to the connection
If your application makes multiple connections to the same database environment, you must specify a unique connection name for each connection.
Quoted String, p. 1-1014

conn_nm variable
Host variable that holds the value of connection name
Variable must be a fixed-length character data type.
Variable name must conform to language-specific rules for variable names.

Usage

The CONNECT statement connects an application to a database environment. The database environment can be a database, a database server, or a database and a database server. If the application successfully connects to the specified database environment, the connection becomes the current connection for the application. SQL statements fail if no current connection exists between an application and a database server. If you specify a database name, the database server opens the database.You cannot use the CONNECT statement in a PREPARE statement.

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. The only restriction on this is that an application can establish only one connection to each local server that uses the shared-memory connection mechanism. To find out whether a local server uses the shared memory connection mechanism or the local loopback connection mechanism, examine the $INFORMIXDIR/etc/sqlhosts file. (See the INFORMIX-Universal Server Administrator's Guide for more information.)

Only one connection is current at any time; other connections are dormant. The application cannot interact with a database through a dormant connection. When an application establishes a new connection, that connection becomes current, and the previous current transaction becomes dormant. You can make a dormant connection current with the SET CONNECTION statement. See "SET CONNECTION".

Privileges for Executing the CONNECT Statement

The current user, or PUBLIC, must have the Connect database privilege on the database specified in the CONNECT statement.

The user who executes the CONNECT statement cannot have the same user name as an existing role in the database.

For information on using the USER clause to specify an alternate user name when the CONNECT statement connects to a database server on a remote host, see "USER Clause".

Connection Identifiers

The optional connection name is a unique identifier that an application can use to refer to a connection in subsequent SET CONNECTION and DISCONNECT statements. If the application does not provide connection name (or a conn_nm host variable), it can refer to the connection using the database environment. If the application makes more than one connection to the same database environment, however, each connection must have a unique connection name.

After you associate a connection name with a connection, you can refer to the connection using only that connection name.

The value of connection name is case sensitive.

Connection Context

Each connection encompasses a set of information that is called the connection context. The connection context includes the name of the current user, the information that the database environment associates with this name, and information on the state of the connection (such as whether an active transaction is associated with the connection). The connection context is saved when an application becomes dormant, and this context is restored when the application becomes current again. (For more information on dormant connections, see "Making a Dormant Connection the Current Connection".)

DEFAULT Option

Use the DEFAULT option to request a connection to a default database server, called a default connection. The default database server can be local or remote. To designate the default database server, set its name in the environment variable INFORMIXSERVER. This form of the CONNECT statement does not open a database.

If you select the DEFAULT option for the CONNECT statement, you must use the DATABASE statement, the CREATE DATABASE statement, or the START DATABASE statement to open or create a database in the default database environment.

Implicit Connection with DATABASE Statements

If you do not execute a CONNECT statement in your application, the first SQL statement must be one of the following database statements (or a single statement PREPARE for one of the following statements):

DATABASE
CREATE DATABASE
DROP DATABASE

If one of these database statements is the first SQL statement in an application, the statement establishes a connection to a server, which is known as an implicit connection. If the database statement specifies only a database name, the database server name is obtained from the DBPATH environment variable. This situation is described in "Locating the Database".

An application that makes an implicit connection can establish other connections explicitly (using the CONNECT statement) but cannot establish another implicit connection unless the original implicit connection is disconnected. An application can terminate an implicit connection using the DISCONNECT statement.

After any implicit connection is made, that connection is considered to be the default connection, regardless of whether the server is the default specified by the INFORMIXSERVER environment variable. This default allows the application to refer to the implicit connection if additional explicit connections are made, because the implicit connection does not have an identifier. For example, if you establish an implicit connection followed by an explicit connection, you can make the implicit connection current by issuing the SET CONNECTION DEFAULT statement. This means, however, that once you establish an implicit connection, you cannot use the CONNECT DEFAULT command because the implicit connection is considered to be the default
connection.

The database statements can always be used to open a database or create a new database on the current database server.

WITH CONCURRENT TRANSACTION Option

The WITH CONCURRENT TRANSACTION clause lets you switch to a different connection while a transaction is active in the current connection. If the current connection was not established using the WITH CONCURRENT TRANSACTION clause, you cannot switch to a different connection if a transaction is active. The CONNECT or SET CONNECTION statement fails, returning an error, and the transaction in the current connection continues to be active. In this case, the application must commit or roll back the active transaction in the current connection before it switches to a different connection.

The WITH CONCURRENT TRANSACTION clause supports the concept of multiple concurrent transactions, where each connection can have its own transaction and the COMMIT WORK and ROLLBACK WORK statements affect only the current connection.The WITH CONCURRENT TRANSACTION clause does not support global transactions in which a single transaction spans databases over multiple connections. The COMMIT WORK and ROLLBACK WORK statements do not act on databases across multiple connections.

The following example illustrates how to use the WITH CONCURRRENT TRANSACTION clause:

main()

{

EXEC SQL connect to 'a@srv1' as 'A';

EXEC SQL connect to 'b@srv2' as 'B' with concurrent transaction;

EXEC SQL connect to 'c@srv3' as 'C' with concurrent transaction;



/*

 	Execute SQL statements in connection 'C' , starting a

 	transaction

*/



EXEC SQL set connection 'B'; -- switch to connection 'B'

 

/* 

	Execute SQL statements starting a transaction in 'B'.

	Now there are two active transactions, one each in 'B' 

	and 'C'.

*/



EXEC SQL set connection 'A'; -- switch to connection 'A'



/* 

	Execute SQL statements starting a transaction in 'A'.

	Now there are three active transactions, one each in 'A', 

	'B' and 'C'.

*/



EXEC SQL set connection 'C'; -- ERROR, transaction active in 'A'





/*

	SET CONNECTION 'C' fails (current connection is still 'A')

	The transaction in 'A' must be committed/rolled back since

	connection 'A' was started without the CONCURRENT TRANSACTION 

	clause.

*/



EXEC SQL commit work;	-- commit tx in current connection ('A')



/*

	Now, there are two active transactions, in 'B' and in 'C',

	which must be committed/rolled back separately

*/



EXEC SQL set connection 'B'; -- switch to connection 'B'

EXEC SQL commit work;        -- commit tx in current connection ('B')



EXEC SQL set connection 'C'; -- go back to connection 'C'

EXEC SQL commit work;	      -- commit tx in current connection ('C')



EXEC SQL disconnect all;

}

Warning: When an application uses the WITH CONCURRENT TRANSACTION clause to establish multiple connections to the same database environment, a deadlock condition can occur. A deadlock condition occurs when one transaction obtains a lock on a table, and a concurrent transaction tries to obtain a lock on the same table, resulting in the application waiting for itself to release the lock.

Database Environment

Element Purpose Restrictions Syntax
db_env variable
Host variable that contains a value representing a database environment
Variable must be a fixed-length character data type. The value stored in this host variable must have one of the database- environment formats listed in the syntax diagram.
Variable name must conform to language-specific rules for variable names.

dbname
Quoted string that identifies the name of the database to which a connection is made
Specified database must already exist. If you previously set the DELIMIDENT environment variable, surrounding quotes must be single. If the DELIMIDENT environment variable has not been previously set, surrounding quotes can be single or double.
Quoted String, p. 1-1014

dbservername
Quoted string that identifies the name of the database server to which a connection is made
Specified database server must match the name of a server in the sqlhosts file. If you previously set the DELIMIDENT environment variable, surrounding quotes must be single. If the DELIMIDENT environment variable has not been previously set, surrounding quotes can be single or double.
Quoted String, p. 1-1014

dbname@ dbservername
Quoted string that identifies the name of the database and database server to which a connection is made
Specified database must already exist. Specified database server must match the name of a server in the sqlhosts file. If you previously set the DELIMIDENT environment variable, surrounding quotes must be single. If the DELIMIDENT environment variable has not been previously set, surrounding quotes can be single or double.
Quoted String, p. 1-1014

Specifying the Database Environment

Using the options shown in the syntax diagram, you can specify either a server and a database, a database server only, or a database only.

Specifying a Database Server Only

The @dbservername option establishes a connection to the named database server only; it does not open a database. When you use this option, you must subsequently use the DATABASE or CREATE DATABASE statement (or a PREPARE statement for one of these statements and an EXECUTE statement) to open a database.

Specifying a Database Only

The dbname option establishes connections to the default server or to another database server in the DBPATH environment variable. It also locates and opens the named database. The same is true of the db_env variable option if it specifies only a database name. See "Locating the Database" for the order in which an application connects to different database servers to locate a database.

Locating the Database

How a database is located and opened depends on whether you specify a database server name in the database environment expression

Database Server and Database Specified

If you specify both a database server and a database in the CONNECT statement, your application connects to the database server, which locates and opens the database. For the Universal Server database server, it uses parameters that are specified in the ONCONFIG configuration file to locate the database.

If the database server that you specify is not on-line, you get an error.

Only Database Specified

If you specify only a database in your CONNECT statement, not a database server, the application obtains the name of a database server from the DBPATH environment variable. The database server in the INFORMIXSERVER environment variable is always added in front of the DBPATH value specified by the user. Set environment variables as the following example shows:

setenv INFORMIXSERVER srvA

setenv DBPATH //srvB://srvC

The resulting DBPATH used by your application is shown in the following example:

//srvA://srvB://srvC

The application first establishes a connection to the database server specified by INFORMIXSERVER. For the Universal Server database server, it uses parameters that are specified in the configuration file to locate the database.

If the database does not reside on the default database server, or if the default database server is not on-line, the application connects to the next database server in DBPATH. In the previous example, this server would be srvB.

If a directory in DBPATH is an NFS-mounted directory, it is expanded to contain the host name of the NFS computer and the complete pathname of the directory on the NFS host. In this case, the host name must be listed in your sqlhosts file as a dbservername, and an sqlexecd daemon must be running on the NFS host.

USER Clause

Element Purpose Restrictions Syntax
auth variable
Host variable that holds the valid password for the login name specified in user identifier or user_id variable
Variable must be a fixed-length character data type. The password stored in this variable must exist in the /etc/passwd file. If the application connects to a remote database server, the password must exist in this file on both the local and remote database servers.
Variable name must conform to language-specific rules for variable names.

user_id variable
The name of an ESQL/C host variable that holds the value of user identifier
Variable must be a fixed-length character data type. The login name stored in this variable is subject to the same restrictions as the user identifier variable.
Variable name must conform to language-specific rules for variable names.

user identifier
Quoted string that is a valid login name for the application
Specified login name must exist in the /etc/passwd file. If the application connects to a remote server, the login name must exist in this file on both the local and remote database servers.
Quoted String, p. 1-1014

The User clause specifies information that is used to determine whether the application can access the target computer when the CONNECT statement connects to the database server on a remote host. Subsequent to the CONNECT statement, all database operations on the remote host use the specified user name.

The connection is rejected if the following conditions occur:

The specified user lacks the privileges to access the database named in the database environment.
The specified user does not have the required permissions to connect to the remote host.
You supply a USER clause but do not include the USING auth variable phrase.

In compliance with the X/Open specification for the CONNECT statement, the ESQL/C preprocessor allows a CONNECT statement that has a USER clause without the USING auth variable phrase. The connection is rejected at runtime by Informix database servers, however, if the auth variable is not present.

If you do not supply the USER clause, the connection is attempted using the default user ID. The default Informix user ID is the login name of the user running the application. In this case, network permissions are obtained using the standard UNIX authorization procedures (for example, checking the /etc/hosts.equiv file).

Connecting to INFORMIX-OnLine Dynamic Server Before Version 6.0

The CONNECT statement syntax described in this chapter is valid for a Version 6.0 or later application connecting to database servers earlier than Version 6.0. As with Version 6.0 or later database servers, an implicit connection can be made to a database server earlier than Version 6.0, provided that no existing implicit connections exist and no implicit connections have been previously terminated.

Connections to pre-Version 6.0 OnLine database servers differ from connections to Version 6.0 or later OnLine and Universal Server in the following respects:

The CLOSE DATABASE statement causes a connection to a pre-Version 6.0 database server to be dropped. The same statement, applied to a connection to a Version 6.0 or later database server, causes the database to close, but the connection remains.
If an application makes a connection to a pre-Version 6.0 database server without using the WITH CONCURRENT TRANSACTION clause, you must close the database (effectively dropping the connection) before you switch to a different connection. Otherwise, Version 6.0 and later OnLine and Universal Server return error message -1800.

References

See the DISCONNECT, SET CONNECTION, DATABASE, START DATABASE, and CREATE DATABASE statements in this manual.

For information on the contents of the sqlhosts file, refer to the INFORMIX-Universal Server Administrator's Guide.

Informix Guide to SQL: SyntaxChapter 1: SQL Statements Home Contents Index Master Index New Book

SQL Statements

COMMIT WORK

Usage

Usage

Database Environment

References

Informix Guide to SQL: Syntax
Chapter 1: SQL Statements

Home Contents Index Master Index New Book