SQLJ connection technique 4: JDBC DataSource interface

SQLJ connection technique 4 uses the JDBC DataSource as the underlying means for creating the connection. This technique requires that the DataSource is registered with JNDI.

Procedure

To use SQLJ connection technique 4, follow these steps:

From your system administrator, obtain the logical name of the data source to which you need to connect.
Execute an SQLJ connection declaration clause.
For this type of connection, the connection declaration clause needs to be of this form:
```
#sql public static context context-class-name
 with (dataSource="logical-name");
```
The connection context must be declared as public and static. logical-name is the data source name that you obtained in step 1.
Invoke the constructor for the connection context class that you created in step 2.
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 one of the following forms:
```
connection-context-class connection-context-object=
  new connection-context-class();

connection-context-class connection-context-object=
  new connection-context-class (String user, 
    String 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.

Example

The following code uses connection technique 4 to create a connection to a location with logical name jdbc/sampledb. The connection requires a user ID and password.

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

#sql public static context Ctx 
  with (dataSource="jdbc/sampledb");                                    2 
                               // Create connection context class Ctx
String userid="dbadm";         // Declare variables for user ID and password
String password="dbadm";

String empname;                // Declare a host variable
…
Ctx myConnCtx=new Ctx(userid, password);                                3 
                               // Create connection context object myConnCtx
                               // for the connection to jdbc/sampledb
#sql [myConnCtx] {SELECT LASTNAME INTO :empname FROM EMPLOYEE 
   WHERE EMPNO='000010'};  
                               // Use myConnCtx for executing an SQL statement