SQLJ connection technique 3: JDBC DataSource interface

SQLJ connection technique 3 uses the JDBC DataSource as the underlying means for creating the connection.

Procedure

To use SQLJ connection technique 3, follow these steps:

Execute an SQLJ connection declaration clause.
Doing this generates a connection context class. The simplest form of the connection declaration clause is:
```
#sql context context-class-name;
```
The name of the generated connection context class is context-class-name.
If your system administrator created a DataSource object in a different program, follow these steps. Otherwise, create a DataSource object and assign properties to it.
1. Obtain the logical name of the data source to which you need to connect.
2. Create a context to use in the next step.
3. In your application program, use the Java™ Naming and Directory Interface (JNDI) to get the DataSource object that is associated with the logical data source name.
Invoke the JDBC DataSource.getConnection method.
Doing this creates a JDBC connection object for the connection to the data source. You can use one of the following forms of getConnection:
```
getConnection();
getConnection(user, password);
```
The meanings of the user and password parameters are:

user and password

Specify a user ID and password for connection to the data source, if the data source to which you are connecting requires them.
If the default autocommit mode is not appropriate, invoke the JDBC Connection.setAutoCommit method.
Doing this indicates whether you want the database manager to issue a COMMIT after every statement. The form of this method is:
```
setAutoCommit(boolean autocommit);
```
Invoke the constructor for the connection context class that you created in step 1.
Doing this creates a connection context object that you specify in each SQL statement that you execute at the associated data source. The constructor invocation statement needs to be in the following form:
```
connection-context-class connection-context-object=
  new connection-context-class(Connection JDBC-connection-object);
```
The JDBC-connection-object parameter is the Connection object that you created in step 3.

Example

The following code uses connection technique 3 to create a connection to a location with logical name jdbc/sampledb. This example assumes that the system administrator created and deployed a DataSource object that is available through JNDI lookup. The numbers to the right of selected statements correspond to the previously-described steps.

Figure 1. Using connection technique 3 to connect to a data source

import java.sql.*; 
import javax.naming.*; 
import javax.sql.*;
…
#sql context CtxSqlj;       // Create connection context class CtxSqlj   1 
Context ctx=new InitialContext();                                        2b 
DataSource ds=(DataSource)ctx.lookup("jdbc/sampledb");                   2c 
Connection con=ds.getConnection();                                       3 
String empname;             // Declare a host variable
…
con.setAutoCommit(false);   // Do not autocommit                         4 
CtxSqlj myConnCtx=new CtxSqlj(con);                                      5 
                            // Create connection context object myConnCtx
#sql [myConnCtx] {SELECT LASTNAME INTO :empname FROM EMPLOYEE 
   WHERE EMPNO='000010'};  
                            // Use myConnCtx for executing an SQL statement