Special Cloudscape Programming
Page 2 of 7

Programming Database-Side JDBC Methods

Overview

Methods invoked within an application are called application-side methods. Methods invoked within Cloudscape are called database-side methods.

An application-side method can be exactly the same as a database-side method. The only difference is where you invoke them. You write the method only once. Where you invoke the method--within the application or within an SQL-J statement--determines whether it is an "application-side" or a "database-side" method.

Methods invoked within an application that interact with a Cloudscape database are called application-side JDBC methods; methods invoked within Cloudscape that interact with a Cloudscape database are called database-side JDBC methods. Methods in this last category most closely resemble stored procedures; these are methods you call from an SQL-J statement that execute SQL-J statements themselves.

NOTE: For tips on how to make your database-side methods look and feel more like "stored procedures," if those are what you are used to working with, see Getting Methods to Look and Work More Like Stored Procedures.

NOTE: Cloudscape allows you to create triggers. Because of the power of Java, your triggers can be turbo-triggers; a trigger can be a simple SQL-J statement or any Java method call. For more info, see Programming Trigger Actions.

Database-Side JDBC Methods and Nested Connections

Most database-side JDBC methods need to share the same transaction space as the statements that called them for the following reasons:

  • to avoid blocking and deadlocks
  • to ensure that any updates done from within the method are atomic with the outer transaction

In order to use the same transaction, the method must use the same connection as the parent SQL-J statement in which the method was executed. Connections re-used in this way are called nested connections.

Cloudscape provides three mechanisms for a method to get a nested connection:

  • getting a Connection object as a parameter using the GETCURRENTCONNECTION method (when you invoke this method in the application, you simply pass in the Connection)

    (the preferred mechanism)

  • using the current=true attribute form of the URL to re-use the current Connection
  • using the database connection URL jdbc:default:connection to re-use the current Connection

GETCURRENTCONNECTION Built-In Method

Some methods take a connection parameter. In an application, pass in the application's Connection object so that the method can use the current connection. Within an SQL-J statement, call the built-in method GETCURRENTCONNECTION, which supplies the Connection object of the outer statement to the method being invoked within the statement. An example:

SELECT city
FROM Cities WHERE
city.isTropical() AND
city.getDistanceFrom( GETCURRENTCONNECTION(), 35) < 3000

For more information, see GETCURRENTCONNECTION() in the Cloudscape Reference Manual.

current=true attribute

A special URL allows a Java method to get the Connection of the SQL-J statement that called it. The method would get a Connection like this:

Connection conn = DriverManager.getConnection(
    "jdbc:cloudscape:;current=true");

For more information, see current=true of the Cloudscape Reference Manual.

Requirements for Database-Side JDBC Methods Using Nested Connections

In order to preserve transactional atomicity, database-side JDBC methods that use nested connections must follow the rules listed in this section.

  • Unless called within a CALL or VALUES statement, must not issue a commit or rollback.

    Enforced by an exception.

    Commits are permitted when the method is called within a CALL or VALUES statement, but not from within a CALL WORK statement.

  • Must not issue a commit or rollback implicitly, which happens when you are in auto-commit mode. In the current release, Cloudscape silently turns off auto-commit to execute these methods (see Using Auto-Commit). Changing the transaction isolation also implicitly commits the transaction.
  • Must not change connection attributes such as auto-commit.

    Enforced by an exception.

  • Must not modify the data in a table used by the parent statement that called the method, using INSERT, DELETE, or UPDATE. For example, if a SELECT statement using table T calls method changeTables, changeTables cannot modify data in table T.

    Not explicitly prohibited, but do not do this!

  • Must not drop a table used by the statement that called the method.

    Not explicitly prohibited, but do not do this!

  • Must not be in a class whose static initializer executes DDL statements.

    Enforced by an exception.

Nested Connections and Work Units

In a Cloudscape synchronization system, you must use a nested connection when calling a work unit. Cloudscape recommends that you use the GETCURRENTCONNECTION built-in method as a parameter to the method call. See the Cloudscape Synchronization Guide for more information.

Example Method

/*This example queries the City table and returns a City object.
This method is a static method in the City class.*/
public static City findCity(Connection conn,
    String acity, 
    String acountry) throws SQLException {
    City mycity;
    mycity = new City();
    PreparedStatement ptstmt = conn.prepareStatement(
        "SELECT city FROM Cities " +
        "WHERE city.getName() = ? AND city.getDisplayCountry() = ?");
    ptstmt.setString(1, acity);
    ptstmt.setString(2, acountry);
    ResultSet rs = ptstmt.executeQuery();
    while (rs.next())
        {
        mycity = (City) rs.getObject(1);
        }
    //close the result set 
    ptstmt.close();
    return mycity;
/* Do not commit the transaction or close the connection*/
}

Invoking the Example Method

In an application, invoke the method like this:

City.findCity(Conn, "Santiago", "Chile");

In an SQL-J statement, invoke the method like this:

VALUES (CLASS JBMSTours.serializabletypes.City).findCity(GETCURRENTCONNECTION(),
    'Santiago', 'Chile')

Or, if there is a class alias for JBMSTours.serializabletypes.City, like this:

VALUES City.findCity(GETCURRENTCONNECTION(),
    'Santiago', 'Chile')

Or, since the method belongs to a class that has instances of it stored in the database (in column called City), you can invoke the method off one of those instances:

SELECT City.findCity(GETCURRENTCONNECTION(), 'Santiago',
    'Chile')
FROM Cities
WHERE city_id = 1

You would not use the CALL method syntax for this method, because the method returns a value.

Database-Side JDBC Methods Using Non-Nested Connections

A database-side JDBC method can create a new connection instead of using a nested connection. Statements executed in the method will be part of a different transaction, and so can issue commits and rollbacks.

Such methods can connect to a database different from the one to which the parent SQL-J statement that called it is connected. The method does not use the same transaction or Connection. It establishes a new Connection and transaction.

NOTE: If database-side JDBC methods do not use nested connections, this means that they are operating outside of the normal DBMS transaction control, so it is not good practice to use them indiscriminately.

Example Method Using New (not Nested) Connection

For an example of a method that uses new rather than nested connections, see the methods archiveRecords and archiveRecordsVTI in the class JBMSTours.serializabletypes.HotelStay in the sample application.

Invoking the Example Method Using the CALL Command

If a method does not return a value, you can have Cloudscape execute the method using the SQL-J CALL command. You can invoke the method in an SQL-J statement such as the following:

CALL HotelStay.archiveRecords(DATE'1998-02-08')

NOTE: You could not roll back this statement, because commits happen within the method itself. Methods that use nested connections, on the other hand, are not permitted to commit or roll back and can therefore be rolled back after the calling statement.

You can also use the CALL command to execute a method that does return a value, but you will not be able to access the value.

Database-Side JDBC Methods and SQLExceptions

It is possible to code database-side methods, like application-side methods, to catch SQLExceptions. SQLExceptions that are caught within a database-side method are hidden from the calling application code. When such SQLExceptions are of transaction severity (such as deadlocks), this "hiding" of the exception causes unexpected problems.

This is because errors of transaction severity roll back work already done by a transaction (not just the piece executed by the called method) and silently begin a new transaction. When the method execution is complete, Cloudscape detects that the outer statement was invalidated by a deadlock and rolls back any work done in the new transaction as well. This is the expected behavior, because all the statements in between explicit commits should be treated atomically; the new transaction implicitly begun by Cloudscape's rollback was not intended by the application designer.

However, this is not the same behavior that would happen if the method were invoked in the application. In that situation, Cloudscape would roll back the work done by the transaction and silently begin a new transaction. Work in the new transaction would not be rolled back when the method returned. However, coding the application in that way means that the transaction did not end where you expected it to and is probably a programming mistake, and is most certainly bad programming practice.

A method that catches a deadlock exception and then continues is probably making a mistake. Errors of transaction severity should be caught not by nested code, but only by the outermost application code. That is the only way to ensure that transactions begin and end where you expect them to.

Not all database vendors handle nested deadlocks the same way. For this and other reasons, it is not possible to write portable SQL-invoking methods. However, it is possible to write SQL-invoking methods that behave identically regardless of whether you invoke them application- or database-side.

In order to ensure identical application- and database-side handling of nested errors, code try-catch blocks to check for the severity of exceptions as follows:

try {
    preparedStatement.execute();
} catch (SQLException se ) {
    checkJBMSSeverity(se);
    // if the SQLException is not of transaction severity,
    // okay to catch exception and continue
    String SQLState = se.getSQLState();
    if ( SQLState.equals( "23500" ) ) 
        { correctDuplicateKey(); }
    else if ( SQLState.equals( "22003" ) ) {    
        correctArithmeticOverflow(); }
    else { throw se; }
}

where checkJBMSSeverity is a static method in the Cloudscape-provided class COM.cloudscape.database.JBMSException.

public static void checkJBMSSeverity( SQLException se )
    throws SQLException
{
    if (se.getErrorCode() > JBMSExceptionSeverity.STATEMENT_SEVERITY )
        { throw se; }
}

Of course, users also have the choice of not wrapping SQL statements in try-catch blocks within methods. In that case, SQLExceptions are caught higher up in their applications, which is the desired behavior.

User-Defined SQLExceptions

When the execution of a database-side method raises an error, Cloudscape wraps that exception in an SQLException with an SQLState of 38000. You can avoid having Cloudscape wrap the exception if:

  • the exception is an SQLException
  • the range of the SQLState is 38001-38999

(This conforms to the SQL99 and the SQLJ standards.)

The most compelling example of when it would make sense to do that is when the method is called from a trigger. See Example of User-Defined SQLException.