SQLJ connection technique 2: JDBC DriverManager interface

SQLJ connection technique 2 uses the JDBC DriverManager interface as the underlying means for creating the connection.

Procedure

To use SQLJ connection technique 2, 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.
Load a JDBC driver by invoking the Class.forName method.
- For the IBM® Data Server Driver for JDBC and SQLJ, invoke Class.forName this way:
```
Class.forName("com.ibm.db2.jcc.DB2Driver");
```
  This step is unnecessary if you use the JDBC 4.0 driver or later.
Invoke the JDBC DriverManager.getConnection method.
Doing this creates a JDBC connection object for the connection to the data source. You can use any of the forms of getConnection that are specified in "Connect to a data source using the DriverManager interface with the IBM Data Server Driver for JDBC and SQLJ".

The meanings of the url, user, and password parameters are:

url

A string that specifies the location name that is associated with the data source. That argument has one of the forms that are specified in "Connect to a data source using the DriverManager interface with the IBM Data Server Driver for JDBC and SQLJ". The form depends on which JDBC driver you are using.

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.
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 2 to create a connection to location NEWYORK. The connection requires a user ID and password, and does not require autocommit. The numbers to the right of selected statements correspond to the previously-described steps.

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

#sql context Ctx;           // Create connection context class Ctx        1 
String userid="dbadm";      // Declare variables for user ID and password
String password="dbadm";
String empname;             // Declare a host variable
…
try {                       // Load the JDBC driver
  Class.forName("com.ibm.db2.jcc.DB2Driver");                             2 
}
catch (ClassNotFoundException e) {
   e.printStackTrace();
}
Connection jdbccon=                                                       3 
  DriverManager.getConnection("jdbc:db2://sysmvs1.stl.ibm.com:5021/NEWYORK",
    userid,password);
                              // Create JDBC connection object jdbccon
jdbccon.setAutoCommit(false); // Do not autocommit                       
Ctx myConnCtx=new Ctx(jdbccon);                                           4 
                              // Create connection context object myConnCtx
                              // for the connection to NEWYORK
#sql [myConnCtx] {SELECT LASTNAME INTO :empname FROM EMPLOYEE 
   WHERE EMPNO='000010'};  
                              // Use myConnCtx for executing an SQL statement