Foxhound is the better* Database Monitor for SQL Anywhere.
*better: More thorough, more relevant, more effective.
...more Alerts, more All Clears, more details, more control in your hands.


[Home] [Back to Tip 84] [Forward to Tip 86] [Archives]
Breck Carter
Last modified: December 3, 1998
mail to: bcarter@bcarter.com



Tip 85: Java In The Database (5)
Cross-Server Database I/O

Question: Can Java code running in one database connect to a different database?

Yes, via JDBC and jConnect as the following crude "cross-server query" illustrates. Figure 1 shows a result set where product id and unit sales were retrieved from one database, and the matching title was retrieved from another database on a different computer, all with a single SELECT statement run via DBISQL.

Figure 1: Cross-Server Query Figure 1: Cross-Server Query

Let's not kid ourselves, it's not a simple SELECT by any stretch of the imagination. It looks weird, and there's some very strange code running under the covers. But it works, and works quite quickly, so even though no one in their right mind would write production code that looks exactly like this it's worth examining to see how it works.

Figure 2 shows the pseudo-code for the SELECT in Figure 1. Here's the situation:

  • The product_sales table exists on the current database but only contains id and unit_sales columns.

  • The product table exists on the other computer. It doesn't contain unit_sales, but it does have matching id values plus all the descriptive information (product name, description, size and so on).
What we want to do is gather data from both tables, joined by matching product id values.

Figure 2: Simple Pseudo-Code For The Cross-Database Query 
SELECT product_sales.id,
       [matching product title from the other database],
       product_sales.unit_sales
  FROM product_sales
 ORDER BY product_sales.unit_sales DESC;

Figure 3 shows the same pseudo-code, expanded with what looks like a correlated subquery.

Figure 3: Expanded Pseudo-Code For The Cross-Database Query 
SELECT product_sales.id,
       ( SELECT STRING ( product.name, ' - ', 
                         product.description, ' - ', 
                         product.size ) 
           FROM [product on the other database]
          WHERE product.id = product_sales.id ),
       product_sales.unit_sales
  FROM product_sales
 ORDER BY product_sales.unit_sales DESC;

Unlike Adaptive Server Enterprise, ASA SQL always executes within the context of a single database. You cannot code "FROM database2.DBA.product" in ASA, and that means no cross-database joins and no cross-database subqueries...

...unless... unless we code the subquery as a call to a Java method, and let the Java code make the connection and execute the subquery. That's what the ASAConnection class is for, with its method selectSingleString as described in Figure 4.

Figure 4: selectSingleString Method [ASAConnection]
public String selectSingleString ( String stringColumnName, String sqlSelect )

Executes a simple SELECT and returns the named string column value from the first row in the result set.

Parameters

stringColumnName
the name of the string column
sqlSelect
the SQL SELECT statement

Return

The column value.

The ASAConnection class also encapsulates the connection to the other database, plus a method that actually makes the connection via jConnect. This allows the user to make a single connection followed by repeated queries rather than having to endure the delay caused by repeated connections.

Figure 5 shows the SQL required to create an object of type ASAConnection and actually make the connection to the other database. This is actual SQL, not pseudo-code. You run it once via DBISQL before running the SELECT shown in Figure 1.

Figure 5: Making A Cross-Database Connection 
CREATE VARIABLE asaConnection ASAConnection;

SET asaConnection = NEW ASAConnection();

CALL asaConnection.setConnection 
  ( '192.168.0.1', '2639', 'dba', 'sql' );

The first statement in Figure 5 creates an SQL variable of type ASAConnection called asaConnection. This variable will last as long as the connection to the current database lasts. Note that there are two connections being discussed here: DBISQL's connection to the current database, and the Java code's connection to the other database.

The second statement in Figure 5 creates a new object of type ASAConnection and assigns it to the variable asaConnection. The default constructor ASAConnection() is used to do this.

The last statement in Figure 5 makes a call to the setConnection method, passing it the machine id, port number, user id and password. This call will make the actual connection to the other server.

Figure 6 shows the complete Java code for the ASAConnection class. Each section of code is numbered 1, 2, 3, .., and explained in the text that follows Figure 6. You can click on the highlighted links to jump back and forth between Figure 6 and the explanations.

Figure 6: ASAConnection.java 
import java.sql.*;             // JDBC
import com.sybase.jdbc.*;      // Sybase jConnect
import java.util.Properties;   // Properties
import sybase.sql.*;           // Sybase utilities

public class ASAConnection implements java.io.Serializable {

  private Connection connection;

  ASAConnection() { // constructor

    // 1. Load the jConnect driver.

    connection = null;
    try {
      Class.forName ( "com.sybase.jdbc.SybDriver" )
        .newInstance();}
    catch ( Exception exception ) {
      System.out.println 
        ( "Error in com.sybase.jdbc.SybDriver in ASAConnection()..." 
        + "\n" + exception.getMessage() );
      exception.printStackTrace();
    }

  } // ASAConnection() constructor

  public boolean setConnection
    ( String machine,
      String port,
      String userID, 
      String password ) {

    String     url;
    Properties properties;

    // 2. Prepare arguments for getConnection().

    url        = "jdbc:sybase:Tds:" + machine + ":" + port;
    properties = new Properties();
    properties.put ( "user", userID );
    properties.put ( "password", password );

    // 3. Make the connection.

    try {
      connection = DriverManager.getConnection 
        ( url, properties );
      connection.setAutoCommit( false ) ;
    }
    catch ( Exception exception ) {
      System.out.println 
        ( "Error in ASAConnection.setConnection()..." 
        + "\n" + exception.getMessage() );
      exception.printStackTrace();
    }

    // 4. Display the connection.

    System.out.println // debugging
      ( "Connection url: '" + url + "'" );
    return true;

  } // public boolean setConnection

  public Connection getConnection() {

    return connection ;

  } // public Connection getConnection

  public String selectSingleString
    ( String stringColumnName,
      String sqlSelect ) {

    Statement         selectStatement;
    ResultSet         resultSet;
    String            returnStringColumnValue;

    returnStringColumnValue = "";

    try {

      if ( connection != null ) {

        // 5. Execute the SELECT.

        selectStatement = connection.createStatement();
        resultSet = selectStatement.executeQuery
          ( sqlSelect );
      
        // 6. Return the named column from the first row.

        if ( resultSet.next() ) {

          returnStringColumnValue 
            = resultSet.getString ( stringColumnName );

        } // if ( resultSet.next() )

      } // if ( connection != null )

    }
    catch ( Exception exception ) {
      System.out.println 
        ( "Error in ASAConnection.selectSingleString()..." 
        + "\n" + exception.getMessage() );
      exception.printStackTrace();
    }

    // 7. Display the return value.

    System.out.println // debugging
      ( "selectSingleString ( '"
      + stringColumnName + "', '"
      + sqlSelect + "' ) = '"
      + returnStringColumnValue + "'" );

    return returnStringColumnValue;

  } // public String selectSingleString

} // public class ASAConnection

1. Load the jConnect driver.

A key feature of the ASAConnection class is the private variable connection of type java.sql.Connection. When an object of type ASAConnection is created, the encapsulated connection variable is also created, and it will last as long as the ASAConnection object. That's why only one connection to the other server is required.

The default constructor initializes the private variable connection to null, and then loads the Sybase jConnect driver. Most of this code for making a connection has been copied from Tip 84 - JDBC and jConnect.

2. Prepare arguments for getConnection().

The setConnection method is called when a connection to the other server is required. It's called "setConnection" because that's what it does: sets the private variable called connection to point to the other server.

For a description of the machine, port and other parameters, see Tip 84 - JDBC and jConnect.

3. Make the connection.

The call to getConnection makes the actual connection, and setAutoCommit( false ) lets the Java code control COMMIT and ROLLBACK logic.

4. Display the connection.

For debugging purposes the setConnection method displays the connection parameters for the other computer. This output will appear in the DBSRV6 console window for the server that DBISQL's connected to directly; i.e., this computer, not the other one.

5. Execute the SELECT.

The selectSingleString method calls createStatement to create an object of type java.sql.Statement. It then calls executeQuery to fill in the SQL SELECT for the subquery, execute the statement and return a result set.

6. Return the named column from the first row.

The call to next positions the result set at the first row, and the getString call extracts the value for the named column from that row.

7. Display the return value.

For debugging purposes the selectSingleString method displays the parameters and column value. It then returns that value to the caller which is the outer SELECT statement in Figure 1.

Figure 7 shows the SELECT from Figure 1, in a nice big font. Here are the key points:

  • The asaConnection >> selectSingleString syntax is required because the parser gets confused by the dot in asaConnection.selectSingleString.

  • The second parameter to selectSingleString is the subquery to be passed to the other computer.

  • The subquery appears as a quoted 'string' literal in the outer SELECT so nested quotes are represented as two single quotes.

  • The call to selectSingleString needs a column name as the first parameter, and the subquery returns an expression, so "AS title" is used to give it a name.

  • The outer SELECT also uses "AS title" to give selectSingleString's return value a name. This is the name that appears as a column heading in the DBISQL window.

Figure 7: The Cross-Server Query 
SELECT product_sales.id,
       asaConnection >> selectSingleString
         ( 'title',
           'SELECT STRING ( product.name, '' - '', '
         + '                product.description, '' - '', '
         + '                product.size ) '
         + '           AS title '
         + '  FROM product '
         + ' WHERE product.id = '
         + STRING ( product_sales.id ) )
       AS title, 
       product_sales.unit_sales
  FROM product_sales
 ORDER BY product_sales.unit_sales DESC;

Figure 8 shows the diagnostic output produced by setConnection and selectSingleString. The blue highlighted portion shows the subquery SELECT and return value for one row.

Figure 8: DBSRV6 Console Display Figure 8: DBSRV6 Console Display

[Home] [Back to Tip 84] [Forward to Tip 86] [Archives] [mail to: bcarter@bcarter.com]