Establishing JDBC connections

User's Guide
PART 3. Java in the Database
CHAPTER 18. Data Access Using JDBC

Establishing JDBC connections

This section presents classes that establish a JDBC database connection from a Java application.

Connecting from a JDBC client application

If you wish to access database system tables (database metadata) from a JDBC application, you must add a set of jConnect system objects to your database. If you do not need to access the system tables, you do not need to add these objects.

For information about adding the jConnect system objects to a database, see Using the Sybase jConnect JDBC driver.

The following complete Java application is a command-line application that connects to a running database, prints a set of information to your command line, and terminates.

Establishing a connection is the first step any JDBC application must take when working with database data.

This example illustrates an external connection, which is a regular client/server connection. For information on how to create an internal connection, from Java classes running inside the database server, see Establishing a connection from a server-side JDBC class.

External connection example code

The following is the source code for the methods used to make a connection. The source code can be found in the main method and the ASAConnect method of the file JDBCExamples.java in the jxmp directory under your Adaptive Server Anywhere installation directory:

// Import the necessary classes
import java.sql.*;             // JDBC
import com.sybase.jdbc.*;      // Sybase jConnect
import java.util.Properties;   // Properties
import sybase.sql.*;           // Sybase utilities
import asademo.*;              // Example classes

private static Connection conn;
public static void main(String args[])  {

    conn = null;
    String machineName;
    if ( args.length != 1 ) {
      machineName = "localhost";
    } else {
      machineName = new String( args[0] );
    }

    ASAConnect( "dba", "sql", machineName );
    if( conn!=null ) {
        System.out.println( "Connection successful" );
    }else{
        System.out.println( "Connection failed" );
    }

    try{
      serializeVariable();
      serializeColumn();   
      serializeColumnCastClass();
    }
    catch( Exception e ) {
      System.out.println( "Error: " + e.getMessage() );
      e.printStackTrace();
    }  
  }
}
private static void ASAConnect( String UserID, 
                              String Password, 
                              String Machinename ) {
    // uses global Connection variable
    
    String _coninfo = new String( Machinename );
    
    Properties _props = new Properties();
    _props.put("user", UserID );
    _props.put("password", Password );
    
    // Load the Sybase Driver
    try {
      Class.forName("com.sybase.jdbc.SybDriver").newInstance();
      
      StringBuffer temp = new StringBuffer();
      // Use the Sybase jConnect driver...
      temp.append("jdbc:sybase:Tds:"); 
      // to connect to the supplied machine name...
      temp.append(_coninfo);
      // on the default port number for ASA...
      temp.append(":2638");
      // and connect.
      System.out.println(temp.toString());
      conn = DriverManager.getConnection( temp.toString() , _props );
    }
    catch ( Exception e ) {
      System.out.println("Error: " + e.getMessage());
      e.printStackTrace();
    }   
  }

How the external connection example works

The external connection example is a Java command-line application.

Importing packages

The application requires several libraries, which are imported in the first lines of JDBCExamples.java:

The Sun Microsystems JDBC classes are contained in the java.sql package, and are required for all JDBC applications. It is contained in the classes.zip file in your Java subdirectory.
The Sybase jConnect JDBC driver is imported from com.sybase.jdbc. This is required for all applications that connect using jConnect. It is contained in the jdbcdrv.zip file in your Java subdirectory.
The application uses a property list. The java.util.Properties class is required to handle property lists. It is contained in the classes.zip file in your Java subdirectory.
The sybase.sql package contains utilities used for serialization. It is contained in the asajdbc.zip file in your Java subdirectory.
The asademo package contains example classes used in some examples. It is contained in the asademo.jar file in your jxmp subdirectory.

The main method

Each Java application requires a class with a method named main, which is the method invoked when the program is started. In this simple example, JDBCExamples.main is the only method in the application.

The JDBCExamples.main method carries out the following tasks:

Processes the command-line argument. If a machine name is supplied, then this is used. By default, the machine name is set to localhost, which is appropriate for the personal database server.
Calls the ASAConnect method to establish a connection.
Executes several methods that scroll data to your command line.

The ASAConnect method

The JDBCExamples.ASAConnet method carries out the following tasks:

Connects to the default running database using Sybase jConnect.
- Class.forName loads jConnect. Using the newInstance method works around issues in some browsers.
- The StringBuffer statements build up a connection string from the literal strings and the supplied machine name provided on the command line.
- DriverManager.getConnection establishes a connection using the connection string.
Returns control to the calling method.

Running the external connection example

This section describes how to run the external connection example

To create and execute the external connection example application:

From a system command prompt, change to the Adaptive Server Anywhere installation directory. Then change to the jxmp subdirectory
Ensure that your CLASSPATH environment variable is set to include the current directory (.) and the zip files that are imported. For example, from a command prompt (the following should be entered all on one line):
```
set classpath=..\java\jdbcdrv.zip;.;..\java\asajdbc.zip;asademo.jar
```
The default zip file name for Java is classes.zip. For classes in any file named classes.zip, you only need the directory name in the CLASSPATH variable, not the zip-file name itself. For classes in files with other names, you must supply the zip file name.

You need the current directory in the CLASSPATH in order to run the example.
Ensure that the database is loaded onto a database server running TCP/IP. You can start such as server on your local machine using the following command (from the jxmp subdirectory):
```
start dbeng6 -c 8M  ..\asademo
```
Run the example by entering the following at the command prompt:
```
java JDBCExamples
```
If you wish to try this against a server running on another machine, you must enter the correct name of that machine. The default is localhost, which is as an alias for the current machine name.
Confirm that a list of people and products is displayed at your command prompt.

If the attempt to connect fails, an error message is displayed instead. Confirm that you have executed all the steps as required. The most common mistake is to have an incorrect CLASSPATH, which results in a failure to locate a class.

For Info For more information about using jConnect, see Using the Sybase jConnect JDBC driver and the online documentation for jConnect.

Establishing a connection from a server-side JDBC class

SQL statements in JDBC are built using the createStatement method of a Connection object. Even classes running inside the server need to establish a connection in order to create a Connection object.

Establishing a connection from a server-side JDBC class is more straightforward than establishing an external connection. Because the server-side class is executed by a user already connected, the class simply uses the current connection.

Server-side connection example code

The following is the source code for the example. The source code can be found in the InternalConnect method of JDBCExamples.java in the jxmp directory under your Adaptive Server Anywhere installation directory:

public static void InternalConnect() {
  try {
      conn = DriverManager.getConnection("jdbc:default:connection");
      System.out.println("Hello World");
    }
    catch ( Exception e ) {
      System.out.println("Error: " + e.getMessage());
      e.printStackTrace();
    }
  }
}

How the server-side connection example works

In this simple example, InternalConnect() is the only method used in the application.

The application requires only one of the libraries (JDBC) imported in the first line of the JDBCExamples.java class. The others are required for external connections. The JDBC classes are contained in the package named java.sql.

The InternalConnect() method carries out the following tasks:

Connects to the default running database using the current connection:
- DriverManager.getConnection establishes a connection using a connection string of jdbc:default:connection.
Prints Hello World to the current standard output, which is the server window. System.out.println carries out the printing.
If there is an error in the attempt to connect, an error message is printed to the server window, together with the place where the error occurred.

The try and catch instructions provide the framework for the error handling.
The class terminates.

Running the server-side connection example

This section describes how to run the server-side connection example.

To create and execute the internal connection example application:

If you have not already done so, compile the JDBCExamples.java file. If you are using the JDK, you can do the following in the jxmp directory from a command prompt:
```
javac JDBCExamples.java
```
Start a database server using the sample database. You can start such as server on your local machine using the following command (from the jxmp subdirectory):
```
start dbeng -c 8M ..\asademo
```
The TCP/IP network protocol is not required in this case, as jConnect is not being used. However, you must have at least 8 Mb of cache available to use Java classes in the database.
Install the class into the sample database. Once connected to the sample database, you can do this from Interactive SQL using the following command:
```
INSTALL JAVA NEW 
FROM FILE 'path\jxmp\JDBCExamples.class'
```
where path is the path to your installation directory.

You can also install the class using Sybase Central. While connected to the sample database, open the Java Objects folder and double click Add Class. Then follow the instructions in the wizard.
You can now call the InternalConnect method of this class just as you would an stored procedure:
```
CALL JDBCExamples>>InternalConnect()
```
The first time a Java class is called in a session, the internal Java virtual machine must be loaded. This can take a few seconds.
Confirm that the message Hello World is printed on the server screen.

Notes on JDBC connections

Autocommit behavior The JDBC specification requires that, by default, a COMMIT is performed after each data modification statement. Currently, the server-side JDBC behavior is not to commit. You can control this behavior using a statement such as the following:
```
conn.setAutoCommit( false ) ;
```
where conn is the current connection object.
Connection defaults From server-side JDBC, only the first call to getConnection( "jdbc:default:connection" ) creates a new connection with the default values. Subsequent calls return a wrapper of the current connection with the all connection properties unchanged. If you set AutoCommit to OFF in your initial connection, any subsequent getConnection calls within the same Java code return a connection with AutoCommit set to OFF.

You may wish to ensure that connection properties are reset to their default values when a connection is closed, so that subsequent connections are obtained with standard JDBC values. The following type of code achieves this:
```
Connection conn = DriverManager.getConnection("");
boolean oldAutoCommit = conn.getAutoCommit();
try {
     // do code here    
}
finally {
    conn.setAutoCommit( oldAutoCommit );
}
```
This discussion applies not only to AutoCommit, but also to other connection properties such as TransactionIsolation and isReadOnly.