Chapter 5 SQLJ Functions and Stored Procedures

Java in Adaptive Server Enterprise

Chapter 4 Data Access Using JDBC

Chapter 4

Data Access Using JDBC

This chapter describes how to use Java Database Connectivity (JDBC) to access data.

Overview

JDBC provides a SQL interface for Java applications. If you want to access relational data from Java, you must use JDBC calls.

You can use JDBC with the Adaptive Server SQL interface in either of two ways:

JDBC on the client - Java client applications can make JDBC calls to Adaptive Server using the Sybase jConnect JDBC driver.
JDBC on the server - Java classes installed in the database can make JDBC calls to the database using the JDBC driver native to Adaptive Server.

The use of JDBC calls to perform SQL operations is essentially the same in both contexts.

This chapter provides sample classes and methods that describe how you might perform SQL operations using JDBC. These classes and methods are not intended to serve as templates, but as general guidelines.

JDBC concepts and terminology

JDBC is a Java API and a standard part of the Java class libraries that control basic functions for Java application development. The SQL capabilities that JDBC provides are similar to those of ODBC and dynamic SQL.

The following sequence of events is typical of a JDBC application:

Create a Connection object - call the getConnection( ) static method of the DriverManager class to create a Connection object. This establishes a database connection.
Generate a Statement object - use the Connection object to generate a Statement object.
Pass a SQL statement to the Statement object - if the statement is a query, this action returns a ResultSet object.
The ResultSet object contains the data returned from the SQL statement, but provides it one row at a time (similar to the way a cursor works).
Loop over the rows of the results set - call the next( ) method of the ResultSet object to:
- Advance the current row (the row in the result set that is being exposed through the ResultSet object) by one row.
- Return a Boolean value (true/false) to indicate whether there is a row to advance to.
For each row, retrieve the values for columns in the ResultSet object - use the getInt( ), getString( ), or similar method to identify either the name or position of the column.

Differences between client- and server-side JDBC

The difference between JDBC on the client and in the database server is in how a connection is established with the database environment.

When you use client-side or server-side JDBC, you call the Drivermanager.getConnection() method to establish a connection to the server.

For client-side JDBC, you use the Sybase jConnect JDBC driver, and call the Drivermanager.getConnection() method with the identification of the server. This establishes a connection to the designated server.
For server-side JDBC, you use the Adaptive Server native JDBC driver, and call the Drivermanager.getConnection() method with one of the following values:
- jdbc:default:connection
- jdbc:sybase:ase
- jdbc:default
- empty string
This establishes a connection to the current server. Only the first call to the getConnection() method creates a new connection to the current server. Subsequent calls return a wrapper of that connection with all connection properties unchanged.

You can write JDBC classes to run at both the client and the server by using a conditional statement to set the URL.

Permissions

Java execution permissions - like all Java classes in the database, classes containing JDBC statements can be accessed by any user. There is no equivalent of the grant execute statement that grants permission to execute procedures in Java methods, and there is no need to qualify the name of a class with the name of its owner.
SQL execution permissions - Java classes are executed with the permissions of the connection executing them. This behavior is different from that of stored procedures, which execute with granted permission by the database owner.

Using JDBC to access data

This section describes how you can use JDBC to perform the typical operations of a SQL application. The examples are extracted from the class JDBCExamples, which is described in "The JDBCExamples class" and in $SYBASE/$SYBASE_ASE/sample/JavaSql (UNIX) or %SYBASE%\Ase-12_5\sample\JavaSql (Windows NT).

JDBCExamples illustrates the basics of a user interface and shows the internal coding techniques for SQL operations.

Overview of the JDBCExamples class

The JDBCExamples class uses the Address class shown in "Sample Java classes ". To execute these examples on your machine, install the Address class on the server and include it in the Java CLASSPATH of the jConnect client.

You can call the methods of JDBCExamples from either a jConnect client or Adaptive Server.

You must create or drop stored procedures from the jConnect client. The Adaptive Server native driver does not support create procedure and drop procedure statements.

JDBCExamples static methods perform the following SQL operations:

Create and drop an example table, xmp:

          create table xmp (id int, name varchar(50), home Address)

Create and drop a sample stored procedure, inoutproc:

create procedure inoutproc @id int, @newname varchar(50), 
             @newhome Address, @oldname varchar(50) output, @oldhome
             Address output as

select @oldname = name, @oldhome = home from xmp 
             where id=@id

update xmp set name=@newname, home = @newhome 
             where id=@id

Insert a row into the xmp table.
Select a row from the xmp table.
Update a row of the xmp table.
Call the stored procedure inoutproc, which has both input parameters and output parameters of datatypes java.lang.String and Address.

JDBCExamples operates only on the xmp table and inoutproc procedure.

The main( ) and serverMain( ) methods

JDBCExamples has two primary methods:

main( ) - is invoked from the command line of the jConnect client.
serverMain( ) - performs the same actions as main( ), but is invoked within Adaptive Server.

All actions of the JDBCExamples class are invoked by calling one of these methods, using a parameter to indicate the action to be performed.

Using main( )

You can invoke the main( ) method from a jConnect command line as follows:

java JDBCExamples  
    "server-name:port-number?user=user-name&password=password" action

You can determine server-name and port-number from your interfaces file, using the dsedit tool. user-name and password are your user name and password. If you omit &password=password, the default is the empty password. Here are two examples:

"antibes:4000?user=smith&password=1x2x3"

"antibes:4000?user=sa"

Make sure that you enclose the parameter in quotation marks.

The action parameter can be create table, create procedure, insert, select, update, or call. It is case insensitive.

You can invoke JDBCExamples from a jConnect command line to create the table xmp and the stored procedure inoutproc as follows:

java JDBCExamples "antibes:4000?user=sa" CreateTable

java JDBCExamples "antibes:4000?user=sa" CreateProc

You can invoke JDBCExamples for insert, select, update, and call actions as follows:

java JDBCExamples "antibes:4000?user=sa" insert

java JDBCExamples "antibes:4000?user=sa" update

java JDBCExamples "antibes:4000?user=sa" call

java JDBCExamples "antibes:4000?user=sa" select

These invocations display the message "Action performed."

To drop the table xmp and the stored procedure inoutproc, enter:

java JDBCExamples "antibes:4000?user=sa" droptable

java JDBCExamples "antibes:4000?user=sa" dropproc

Using serverMain( )

Because the server-side JDBC driver does not support create procedure or drop procedure, create the table xmp and the example stored procedure inoutproc with client-side calls of the main( ) method before executing these examples. Refer to "Overview of the JDBCExamples class".

After creating xmp and inoutproc, you can invoke the serverMain( ) method as follows:

select JDBCExamples.serverMain('insert')

go

select JDBCExamples.serverMain('select')

go

select JDBCExamples.serverMain('update')

go

select JDBCExamples.serverMain('call')

go

Server-side calls of serverMain( ) do not require a server-name:port-number parameter; Adaptive Server simply connects to itself.

Obtaining a JDBC connection: the Connecter( ) method

Both main( ) and serverMain( ) call the connecter( ) method, which returns a JDBC Connection object. The Connection object is the basis for all subsequent SQL operations.

Both main( ) and serverMain( ) call connecter( ) with a parameter that specifies the JDBC driver for the server- or client-side environment. The returned Connection object is then passed as an argument to the other methods of the JDBCExamples class. By isolating the connection actions in the connecter( ) method, JDBCExamples' other methods are independent of their server- or client-side environment.

Routing the action to other methods: the doAction( ) method

The doAction( ) method routes the call to one of the other methods, based on the action parameter.

doAction( ) has the Connection parameter, which it simply relays to the target method. It also has a parameter locale, which indicates whether the call is server- or client-side. Connection raises an exception if either create procedure or drop procedure is invoked in a server-side environment.

Executing imperative SQL operations: the doSQL( ) method

The doSQL( ) method performs SQL actions that require no input or output parameters such as create table, create procedure, drop table, and drop procedure.

doSQL( ) has two parameters: the Connection object and the SQL statement it is to perform. doSQL( ) creates a JDBC Statement object and uses it to execute the specified SQL statement.

Executing an update statement: the UpdateAction( ) method

The updateAction( ) method performs a Transact-SQL update statement. The update action is:

String sql = "update xmp set name = ?, home = ? where id = ?";

It updates the name and home columns for all rows with a given id value.

The update values for the name and home column, and the id value, are specified by parameter markers (?). updateAction( ) supplies values for these parameter markers after preparing the statement, but before executing it. The values are specified by the JDBC setString( ), setObject( ), and setInt( ) methods with these parameters:

The ordinal parameter marker to be substituted
The value to be substituted

For example:

pstmt.setString(1, name);

pstmt.setObject(2, home);

pstmt.setInt(3, id);

After making these substitutions, updateAction( ) executes the update statement.

To simplify updateAction( ), the substituted values in the example are fixed. Normally, applications compute the substituted values or obtain them as parameters.

Executing a select statement: the selectAction( ) method

The selectAction( ) method executes a Transact-SQL select statement:

String sql = "select name, home from xmp where id=?";

The where clause uses a parameter marker (?) for the row to be selected. Using the JDBC setInt( ) method, selectAction( ) supplies a value for the parameter marker after preparing the SQL statement:

PreparedStatement pstmt =
    con.prepareStatement(sql);
pstmt.setInt(1, id);

selectAction( ) then executes the select statement:

ResultSet rs = pstmt.executeQuery();

For SQL statements that return no results, use doSQL( ) and updateAction( ). They execute SQL statements with the executeUpdate( ) method. For SQL statements that do return results, use the executeQuery( ) method, which returns a JDBC ResultSet object.

The ResultSet object is similar to a SQL cursor. Initially, it is positioned before the first row of results. Each call of the next( ) method advances the ResultSet object to the next row, until there are no more rows.

selectAction( ) requires that the ResultSet object have exactly one row. The selecter( ) method invokes the next method, and checks for the case where ResultSet has no rows or more than one row.

        if (rs.next()) {

           name = rs.getString(1);

           home = (Address)rs.getObject(2);

           if (rs.next()) {

              throw new Exception("Error:  Select returned multiple rows");

           } else { // No action

        } else { throw new Exception("Error:  Select returned no rows");

In the above code, the call of methods getString( ) and getObject( ) retrieve the two columns of the first row of the result set. The expression "(Address)rs.getObject(2)" retrieves the second column as a Java object, and then coerces that object to the Address class. If the returned object is not an Address, then an exception is raised.

selectAction( ) retrieves a single row and checks for the cases of no rows or more than one row. An application that processes a multiple row ResultSet would simply loop on the calls of the next( ) method, and process each row as for a single row.

Executing in batch mode

If you want to execute a batch of SQL statements, make sure that you use the execute( ) method. If you use executeQuery( ) for batch mode:

If the batch operation does not return a result set (contains no select statements), the batch executes without error.
If the batch operation returns one result set, all statements after the statement that returns the result are ignored. If getXXX( ) is called to get an output parameter, the remaining statements execute and the current result set is closed.
If the batch operation returns more than one result set, an exception is raised and the operation aborts.

Using execute( ) ensures that the complete batch executes for all cases.

Calling a SQL stored procedure: the callAction( ) method

The callAction( ) method calls the stored procedure inoutproc:

create proc inoutproc @id int, @newname varchar(50), @newhome Address,
         @oldname varchar(50) output,  @oldhome Address output as
 
     select @oldname = name, @oldhome = home from xmp where id=@id
     update xmp set name=@newname, home = @newhome where id=@id

This procedure has three input parameters (@id, @newname, and @newhome) and two output parameters (@oldname and @oldhome). callAction( ) sets the name and home columns of the row of table xmp with the ID value of @id to the values @newname and @newhome, and returns the former values of those columns in the output parameters @oldname and @oldhome.

The inoutproc procedure illustrates how to supply input and output parameters in a JDBC call.

callAction( ) executes the following call statement, which prepares the call statement:

CallableStatement cs = con.prepareCall("{call inoutproc (?, ?, ?, ?, ?)}");

All of the parameters of the call are specified as parameter markers (?).

callAction( ) supplies values for the input parameters using JDBC setInt( ), setString( ), and setObject( ) methods that were used in the doSQL( ), updatAction( ), and selectAction( ) methods:

        cs.setInt(1, id);

        cs.setString(2, newName);

        cs.setObject(3, newHome);

These set methods are not suitable for the output parameters. Before executing the call statement, callAction( ) specifies the datatypes expected of the output parameters using the JDBC registerOutParameter( ) method:

        cs.registerOutParameter(4, java.sql.Types.VARCHAR);

        cs.registerOutParameter(5, java.sql.Types.JAVA_OBJECT);

callAction( ) then executes the call statement and obtains the output values using the same getString( ) and getObject( ) methods that the selectAction( ) method used:

        int res = cs.executeUpdate();

        String oldName = cs.getString(4);

        Address oldHome = (Address)cs.getObject(5);

Error handling in the native JDBC driver

Sybase supports and implements all methods from the java.sql.SQLException and java.sql.SQLWarning classes. SQLException provides information on database access errors. SQLWarning extends SQLException and provides information on database access warnings.

Errors raised by Adaptive Server are numbered according to severity. Lower numbers are less severe; higher numbers are more severe. Errors are grouped according to severity:

Warnings (EX_INFO: severity 10) - are converted to SQLWarnings.
Exceptions (severity 11 to18) - are converted to SQLExceptions.
Fatal errors (severity 19 to 24) - are converted to fatal SQLExceptions.

SQLExceptions can be raised through JDBC, Adaptive Server, or the native JDBC driver. Raising a SQLException aborts the JDBC query that caused the error. Subsequent system behavior differs depending on where the error is caught:

If the error is caught in Java - a "try" block and subsequent "catch" block process the error.
Adaptive Server provides several extended JDBC driver-specific SQLException error messages. All are EX_USER (severity 16) and can always be caught. There are no driver-specific SQLWarning messages.
If the error is not caught in Java - the Java VM returns control to Adaptive Server, Adaptive Server catches the error, and an unhandled SQLException error is raised.
The raiserror command is used typically with stored procedures to raise an error and to print a user-defined error message. When a stored procedure that calls the raiserror command is executed via JDBC, the error is treated as an internal error of severity EX_USER, and a nonfatal SQLException is raised.
You cannot access extended error data using the raiserror command; the with errordata clause is not implemented for SQLException.

If an error causes a transaction to abort, the outcome depends on the transaction context in which the Java method is invoked:

If the transaction contains multiple statements - the transaction aborts and control returns to the server, which rolls back the entire transaction. The JDBC driver ceases to process queries until control returns from the server.
If the transaction contains a single statement - the transaction aborts, the SQL statement it contains rolls back, and the JDBC driver continues to process queries.

The following scenarios illustrate the different outcomes. Consider a Java method jdbcTests.Errorexample() that contains these statements:

stmt.executeUpdate("delete from parts where partno = 0");                  Q2
stmt.executeQuery("select 1/0");                                           Q3
stmt.executeUpdate("delete from parts where partno = 10");                   Q4

A transaction containing multiple statements includes these SQL commands:

begin transaction
delete from parts where partno = 8               Q1
select JDBCTests.Errorexample()

In this case, these actions result from an aborted transaction:

A divide-by-zero exception is raised in Q3.
Changes from Q1 and Q2 are rolled back.
The entire transaction aborts.

A transaction containing a single statement includes these SQL commands:

set chained off
delete from parts where partno = 8               Q1
select JDBCTests.Errorexample()

In this case:

A divide-by-zero exception is raised in Q3.
Changes from Q1 and Q2 are not rolled back
The exception is caught in "catch" and "try" blocks in JDBCTests.Errorexample.
The deletion specified in Q4 does not execute because it is handled in the same "try" and "catch" blocks as Q3.
JDBC queries outside of the current "try" and "catch" blocks can be executed.

The JDBCExamples class

// An example class illustrating the use of JDBC facilities
// with the Java in Adaptive Server feature.

//   
// The methods of this class perform a range of SQL operations.
// These methods can be invoked either from a Java client,
// using the main method, or from the SQL server, using
// the internalMain method.
//

import java.sql.*;             // JDBC

public class JDBCExamples {

The main( ) method

// The main method, to be called from a client-side command line

//

    public static void main(String args[]) {

        if (args.length!=2) {

            System.out.println("\n Usage:    "

                 + "java ExternalConnect server-name:port-number
                 action ");

            System.out.println("  The action is connect, createtable,
             " + "createproc, drop, "
             + "insert, select, update, or call \n" );

            return;

        try{

           String server = args[0];

           String action = args[1].toLowerCase();

           Connection con = connecter(server);

           String workString = doAction( action, con, client);

           System.out.println("\n" + workString + "\n");

        }  catch (Exception e) {

           System.out.println("\n Exception: ");

           e.printStackTrace();

The internalMain( ) method

// A JDBCExamples method equivalent to 'main', 
// to be called from SQL or Java in the server

    public static String internalMain(String action) {

        try {

           Connection con = connecter("default");

           String workString = doAction(action, con, server);

           return workString;

        }  catch ( Exception e ) {

             if (e.getMessage().equals(null)) {

               return "Exc: " + e.toString();

             } else {

               return "Exc - " + e.getMessage();

The connecter( ) method

// A JDBCExamples method to get a connection.  
// It can be called from the server with argument 'default',
// or from a client, with an argument that is the server name.

public static  Connection connecter(String server) 
         throws Exception, SQLException, ClassNotFoundException  {

       String forName="";

       String url="";

       if (server=="default") {  // server connection to current server
         forName = "sybase.asejdbc.ASEDriver";  
         url = "jdbc:default:connection";

       } else if (server!="default") {  //client connection to server
         forName= "com.sybase.jdbc.SybDriver";         
         url = "jdbc:sybase:Tds:"+ server;

       String user = "sa";

       String password = "";

       // Load the driver

       Class.forName(forName);

       // Get a connection

       Connection con = DriverManager.getConnection(url, 
         user, password);

       return con;

The doAction( ) method

// A JDBCExamples method to route to the 'action' to be performed

    public static String doAction(String action, Connection con,
             String locale) 
         throws Exception {

        String createProcScript =

             " create proc inoutproc @id int, @newname varchar(50), 
             @newhome Address, "

           + "   @oldname varchar(50) output,  @oldhome Address 
             output as "

           + " select @oldname = name, @oldhome = home from xmp 
             where id=@id "

           + " update xmp set name=@newname, home = @newhome 
             where id=@id ";

        String createTableScript =
         " create  table xmp (id int, name varchar(50), 
             home Address)" ;

        String dropTableScript = "drop   table xmp  ";

        String dropProcScript = "drop  proc inoutproc ";

       String insertScript = "insert  into xmp " 
         + "values (1, 'Joe Smith', new Address('987 Shore',
         '12345'))";

        String workString = "Action (" + action + ) ;

        if (action.equals("connect")) {

             workString += "performed";

        } else if (action.equals("createtable")) {

             workString += doSQL(con, createTableScript );

        } else if (action.equals("createproc")) {

             if (locale.equals(server))  {

                 throw new exception (CreateProc cannot be performed
                 in the server);

             }  else {

                 workString += doSQL(con, createProcScript );

        } else if (action.equals("droptable")) {

             workString += doSQL(con, dropTableScript );

        } else if (action.equals("dropproc")) {

             if (locale.equals(server))  {

                 throw new exception (CreateProc cannot be performed
                 in the server);

             }  else {

                 workString += doSQL(con, dropProcScript );

        } else if (action.equals("insert")) {

             workString += doSQL(con, insertScript );

        } else if (action.equals("update")) {

             workString += updateAction(con);

        } else if (action.equals("select")) {

             workString += selectAction(con);

        } else if (action.equals("call")) {

             workString += callAction(con);

        } else { return "Invalid action:  " + action ;

        return workString;

The doSQL( ) method

// A JDBCExamples method to execute an SQL statement.

    public static String doSQL (Connection con, String action)

           throws Exception {

        Statement stmt = con.createStatement();

        int res = stmt.executeUpdate(action);

        return "performed";

The updateAction( ) method

// A method that updates a certain row of the 'xmp' table.
// This method illustrates prepared statements and parameter markers.

    public static String updateAction(Connection con)

           throws Exception {

        String sql = "update xmp set name = ?, home = ? where id = ?";

        int id=1;

        Address home = new Address("123 Main", "98765");

        String name = "Sam Brown";

        PreparedStatement pstmt = con.prepareStatement(sql);

        pstmt.setString(1, name);

        pstmt.setObject(2, home);

        pstmt.setInt(3, id);

        int res = pstmt.executeUpdate();

        return "performed";

The selectAction( ) method

//  A JDBCExamples method to retrieve a certain row 
//  of the 'xmp' table.
//  This method illustrates prepared statements, parameter markers,
//  and result sets.

    public static String selectAction(Connection con)

           throws Exception {

        String sql = "select name, home from xmp where id=?";

        int id=1;

        Address home = null;

        String name = "";

        String street = "";

        String zip = "";

        PreparedStatement pstmt = con.prepareStatement(sql);

        pstmt.setInt(1, id);

        ResultSet rs = pstmt.executeQuery();

        if (rs.next()) {

           name = rs.getString(1);

           home = (Address)rs.getObject(2);

           if (rs.next()) {

              throw new Exception("Error:  Select returned
                  multiple rows");

           } else { // No action

        } else { throw new Exception("Error:  Select returned no rows");

        return "- Row with id=1:  name("+ name + )

                + " street(" + home.street + ) zip("+ home.zip + );

The callAction( ) method

// A JDBCExamples method to call a stored procedure, 
// passing input and output parameters of datatype String
 // and Address.
 // This method illustrates callable statements, parameter markers,
 // and result sets.

    public static String callAction(Connection con)

           throws Exception {

        CallableStatement cs  = con.prepareCall("{call inoutproc 
         (?, ?, ?, ?, ?)}");

        int id = 1;

        String newName = "Frank Farr";

        Address newHome = new Address("123 Farr Lane", "87654");

        cs.setInt(1, id);

        cs.setString(2, newName);

        cs.setObject(3, newHome);

        cs.registerOutParameter(4, java.sql.Types.VARCHAR);

        cs.registerOutParameter(5, java.sql.Types.JAVA_OBJECT);

        int res = cs.executeUpdate();

        String oldName = cs.getString(4);

        Address oldHome = (Address)cs.getObject(5);

        return "- Old values of row with id=1: name("+oldName+ )
           street(" + oldHome.street + ")  zip("+ oldHome.zip + );


	Expand Search