12. Applications with Multiple Database Connections#

Overview#

APRE supports the authoring of applications that establish more than one database connection. This chapter describes multiple connections and explains how to establish and maintain them.

The Need for Multiple Connections#

Here are some of the circumstances in which it might be desirable to implement multiple database connections within a single application:

When it is necessary to access multiple database servers from the same application
When multiple users need to access a database server using the same application
In multi-threaded applications. For more information about multi-threaded applications, please refer to Chapter 13: Multithreaded Applications.

Connection Name#

In an application with multiple database connections, the connections are distinguished from one another using the connection names. The name of each connection must be unique within the application. Only one connection that does not have a connection name can exist within an application. This connection is called the "default connection".

The name of each connection is set when the connection with a database server is established. To use this connection in subsequently executed embedded SQL statements, the connection name must be specified. If an attempt is made to establish a connection using the name of a connection that has already been established, an error will be raised, indicating that an established connection with that name already exists.

Each connection name must start with an alphabetic character (a ~ z, A ~ Z), the underscore character ("_"), or a dollar sign ("$"), and must not be longer than 50 bytes

Syntax#

To specify a desired connection when executing an embedded SQL statement, use the AT option, as shown below:

EXEC SQL [ AT <conn_name | :conn_name> ] …

Arguments#

Both hard-coded strings and host variables can be used to specify the connection name. If using a host variable, it does not need to be declared in the host variable declaration section.

<conn_name>: This is the name of the connection in the form of a string literal.
<:conn_name>: This is the name of the connection, stored in a host variable.

Steps for Authoring a Multiple-Connection Application#

The process of authoring an application that uses multiple database connections is not much different from the process of authoring an application that uses only one database connection. The steps to follow when writing an application with multiple database connections are as follows:

Establish a connection to the database server. The name of the connection is defined at this time.
Execute embedded SQL statements using the name of the established connection.
Terminate the established connection.

SQL Statements for Multi-Connections#

The method of using embedded SQL statements in applications with multiple database connections is not very different from when using embedded SQL statements in other applications. The basic syntax of each command is the same; all that is additionally required is to specify the name of the connection using the AT clause. The following describes how to use embedded SQL statements in applications with multiple database connections.

CONNECT#

This statement is used to establish a connection to the database server and define a name for the connection.

Syntax#

EXEC SQL [ AT <conn_name | :conn_name> ] 
CONNECT <:user> IDENTIFIED BY <:passwd> 
[ USING <:conn_opt1> [ , <:conn_opt2> ] ];

Arguments#

<conn_name>: This is the name to use for the connection in the form of a string literal.
<:conn_name>: cThis is the name to use for the connection, stored in a host variable.
<:user>: This is the name of the user with which to connect to the database server.
<:passwd>: This is the password for the user with which the connection to the database server is established.
<:conn_opt1> : Please refer to Chapter 6: Embedded SQL Statements.
<:conn_opt2> : Please refer to Chapter 6: Embedded SQL Statements.

Description#

When establishing more than one connection in one application, it is necessary to specify a name for every connection except the default connection. The name of each connection must be unique within the application. After the connection has been established, the name of the connection to use in subsequently executed embedded SQL statements must be specified using the AT clause.

Examples#

The following two examples illustrate how to specify the name of a connection when establishing a connection.

[Example 1] In the following example, a connection is established with a database server, and the connection is identified using the provided string literal. Thus, "CONN1" is the name of the established connection.

< Sample Program: mc1.sc >

EXEC SQL BEGIN DECLARE SECTION;
char usr[10];
char pwd[10];
EXEC SQL END DECLARE SECTION;

/* set username */
strcpy(usr, "SYS");
/* set password */
strcpy(pwd, "MANAGER");

/* connect to altibase server with CONN1 */
EXEC SQL AT CONN1 CONNECT :usr IDENTIFIED BY :pwd;

[Example 2] In this example, a connection is established with a database server, and the connection is identified using the value of the host variable. Thus, "CONN2" is the name of the established connection.

< Sample Program : mc2.sc >

EXEC SQL BEGIN DECLARE SECTION;
char usr[10];
char pwd[10];
char conn_name2[10];
EXEC SQL END DECLARE SECTION;

/* set username */
strcpy(usr, "ALTITEST");
/* set password */
strcpy(pwd, "ALTITEST");
/* set connname */
strcpy(conn_name2, "CONN2");

/* connect to altibase server with :conn_name2 */
EXEC SQL AT :conn_name2 CONNECT :usr IDENTIFIED BY :pwd;

DISCONNECT#

This statement is used to terminate the database server connection identified by the specified connection name.

Syntax#

EXEC SQL [ AT <conn_name | :conn_name> ]
DISCONNECT;

Arguments#

<conn_name>: This is the name of the connection to terminate in the form of a string literal.
<:conn_name>: This is the name of the connection to terminate, stored in a host variable.

Examples#

The following two examples illustrate how to terminate a connection.

[Example 1] In the following example, a connection with a database server, identified using a string literal, is terminated. The name of the connection to be terminated is "CONN1".

< Sample Program: mc1.sc >

EXEC SQL AT CONN1 DISCONNECT;

[Example 2] In the following example, a connection with a database server, identified using a host variable, is terminated. The name of the connection to be terminated is "CONN2".

< Sample Program: mc2.sc >

EXEC SQL BEGIN DECLARE SECTION;
char conn_name2[10];
EXEC SQL END DECLARE SECTION;

strcpy(conn_name2, "CONN2");
EXEC SQL AT :conn_name2 DISCONNECT;

Executing Basic SQL Statements#

The method of specifying the connection when executing DML statements such as SELECT and UPDATE and DDL statements such as CREATE and DROP is explained in this section.

Syntax#

EXEC SQL [ AT <conn_name | :conn_name> ] 
[ SELECT | UPDATE | INSERT | DELETE | CREATE ] …

Arguments#

<conn_name> : This is the name of the connection in the form of a string literal.
<:conn_name> : This is the name of the connection, stored in a host) variable.

Cursor Statements#

The method of specifying the connection when executing cursor-related embedded SQL statements is explained in this section.

Syntax#

EXEC SQL [ AT <conn_name | :conn_name> ] 
[ DECLARE | OPEN | FETCH | CLOSE ]  <cursor_name> …

Arguments#

<conn_name>: This is the name of the connection in the form of a string literal.
<:conn_name>: This is the name of the connection, stored in a host variable.
<cursor_name>: The cursor name

Dynamic SQL Statements#

The method of specifying the connection when executing dynamic SQL statements is explained in this section.

Syntax#

Method 1#

EXEC SQL [ AT <conn_name | :conn_name> ] 
EXECUTE IMMEDIATE …

Method 2#

EXEC SQL [ AT <conn_name | :conn_name> ] PREPARE … 
EXEC SQL [ AT <conn_name | :conn_name> ] EXECUTE …

Method 3#

EXEC SQL [ AT <conn_name | :conn_name> ] PREPARE … 
EXEC SQL [ AT <conn_name | :conn_name> ] DECLARE …
EXEC SQL [ AT <conn_name | :conn_name> ] OPEN …
EXEC SQL [ AT <conn_name | :conn_name> ] FETCH …
EXEC SQL [ AT <conn_name | :conn_name> ] CLOSE …

Arguments#

<conn_name>: This is the name of the connection in the form of a string literal.
<:conn_name>: This is the name of the connection, stored in a host variable.

Other SQL statements#

The method of specifying the connection when executing other SQL statements, such as those for setting the autocommit mode and committing and rolling back transactions, is explained in this section.

Syntax#

EXEC SQL [ AT <conn_name | :conn_name> ] 
AUTOCOMMIT { ON | OFF };
EXEC SQL [ AT <conn_name | :conn_name> ]  
COMMIT;
EXEC SQL [ AT <conn_name | :conn_name> ]  
SAVEPOINT <savepoint_name>;
EXEC SQL [ AT <conn_name | :conn_name> ] 
ROLLBACK [ TO SAVEPOINT <savepoint_name> ];
EXEC SQL [ AT <conn_name | :conn_name> ]  
BATCH;

Arguments#

<conn_name> : This is the name of the connection in the form of a string literal.
<:conn_name> : This is the name of the connection, stored in a host variable.
<savepoint_name> : This is the name of a savepoint.

Exceptions#

The AT clause is never used with the following embedded SQL statements, even in applications in which multiple database connections have been established:

Syntax#

EXEC SQL INCLUDE …
EXEC SQL OPTION …
EXEX SQL WHENEVER …

Using Stored Procedures in Multiple-Connection Applications#

SQL statements for managing stored procedures can also be used in applications with multiple database connections. The syntax for these statements is the same as was described in Chapter 12: Using Stored Procedures in C/C++, with the exception that the AT clause is additionally provided.

CREATE#

This statement is used to create a stored procedure or stored function in an application with multiple active database connections.

Syntax#

EXEC SQL [ AT <conn_name | :conn_name> ] 
CREATE [ OR REPLACE ] <PROCEDURE | FUNCTION>
…
END 
[ <procedure_name | function_name> ] ; 
END-EXEC;

Arguments#

<conn_name> : This is the name of the connection in the form of a string literal.
<:conn_name> : This is the name of the connection, stored in a host variable.
<procedure_name> : This is the name of the stored procedure to create.
<function_name> : This is the name of the stored function to create.

ALTER#

This statement is used to recompile a stored procedure or stored function in an application with multiple active database connections.

Syntax#

EXEC SQL [ AT <conn_name | :conn_name> ] 
ALTER PROCEDURE 
<procedure_name | function_name> COMPILE;

Arguments#

<conn_name> : This is the name of the connection in the form of a string literal.
<:conn_name> : This is the name of the connection, stored in a host variable.
<procedure_name> : This is the name of the stored procedure to recompile.
<function_name> : This is the name of the stored function to recompile.

DROP#

This statement is used to drop a stored procedure or stored function in an application with multiple active database connections.

Syntax#

EXEC SQL [ AT <conn_name | :conn_name> ] 
DROP PROCEDURE <procedure_name | function_name>;

Arguments#

<conn_name> : This is the name of the connection in the form of a string literal.
<:conn_name> : This is the name of the connection, stored in a host variable.
<procedure_name> : This is the name of the stored procedure to drop.
<function_name> : This is the name of the stored function to drop.

EXECUTE#

This statement is used to execute a stored procedure or stored function in an application with multiple active database connections.

Syntax#

EXEC SQL [ AT <conn_name | :conn_name> ] 
EXECUTE BEGIN 
/* Stored procedure block here */
END;
END-EXEC;

Arguments#

<conn_name>: This is the name of the connection in the form of a string literal.
<:conn_name>: This is the name of the connection, stored in a host variable.
<procedure_name>: This is the name of the stored procedure to execute.
<function_name>: This is the name of the stored function to execute.
/* Stored procedure block here */: Please refer to the Stored Procedures Manual.

Sample Programs#

mc1.sc#

This sample application can be found at $ALTIBASE_HOME/sample/APRE/mc1.sc.

Result of Execution#

$ is -f schema/schema.sql
$ make mc1
$ ./mc1
<MULTI CONNECTION 1>
-----------------------------------------------------------
[Declare Cursor With CONN1                                       ]
-----------------------------------------------------------
Success declare cursor with CONN1

-----------------------------------------------------------
[Open Cursor With CONN1]
-----------------------------------------------------------
Success open cursor with CONN1

-----------------------------------------------------------
[Fetch Cursor With CONN1 -> Insert With CONN2]                    
-----------------------------------------------------------
30 rows inserted

-----------------------------------------------------------
[Close Cursor With CONN1]                                         
-----------------------------------------------------------
Success close cursor with CONN1

mc2.sc#

This sample application can be found at $ALTIBASE_HOME/sample/APRE/mc2.sc.

Result of Execution#

$ is -f schema/schema.sql
$ make mc2
$ ./mc2
<MULTI CONNECTION 2>
-----------------------------------------------------------
[Dynamic SQL Method 1 With :conn_name2]                           
-----------------------------------------------------------
Success dynamic sql method 1 with :conn_name2

-----------------------------------------------------------
[Dynamic SQL Method 2 (PREPARE) With :conn_name2]                 
-----------------------------------------------------------
Success dynamic sql method 2 (prepare) with :conn_name2

-----------------------------------------------------------
[Dynamic SQL Method 3 (PREPARE) With :conn_name1]                 
-----------------------------------------------------------
Success dynamic sql method 3 (prepare) with :conn_name1

-----------------------------------------------------------
[Dynamic SQL Method 3 (DECLARE CURSOR) With :conn_name1]          
-----------------------------------------------------------
Success dynamic sql method 3 (declare cursor) with :conn_name1

-----------------------------------------------------------
[Dynamic SQL Method 3 (OPEN CURSOR) With :conn_name1]             
-----------------------------------------------------------
Success dynamic sql method 3 (open cursor) with :conn_name1

-----------------------------------------------------------
[Dynamic SQL Method 3 (FETCH CURSOR) With :conn_name1             
 -> Dynamic SQL Method 2 (EXECUTE-INSERT) With :conn_name2]       
-----------------------------------------------------------
20 rows inserted

-----------------------------------------------------------
[Dynamic SQL Method 3 (CLOSE CURSOR) With :conn_name1]            
-----------------------------------------------------------
Success dynamic sql method 3 (close cursor) with :conn_name1

mc3.sc#

This sample application can be found at $ALTIBASE_HOME/sample/APRE/mc3.sc

Result of Execution#

$ is -f schema/schema.sql
$ make mc3
$ ./mc3
<MULTI CONNECTION 3>
-----------------------------------------------------------
[Autocommit Off With CONN1]                                       
-----------------------------------------------------------
Autocommit mode of CONN1 session modified false

-----------------------------------------------------------
[Autocommit Off With CONN2]                                       
-----------------------------------------------------------
Autocommit mode of CONN2 session modified false

-----------------------------------------------------------
[Create Procedure With CONN1]                                     
-----------------------------------------------------------
Success create procedure

-----------------------------------------------------------
[Create Procedure With CONN2]                                     
-----------------------------------------------------------
Success create procedure

-----------------------------------------------------------
[Execute Procedure With CONN1]                                    
-----------------------------------------------------------
Success execute procedure

-----------------------------------------------------------
[Execute Procedure With CONN2]                                    
-----------------------------------------------------------
Success execute procedure

-----------------------------------------------------------
[Commit With CONN1]                                               
-----------------------------------------------------------
Success commit

-----------------------------------------------------------
[Commit With CONN2]                                               
-----------------------------------------------------------
Success commit

-----------------------------------------------------------
[Drop Procedure With CONN1]                                       
-----------------------------------------------------------
Success drop procedure

----------------------------------------------------------- 
[Drop Procedure With CONN2]                                       
-----------------------------------------------------------
Success drop procedure