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 80] [Forward to Tip 82] [Archives]
Breck Carter
Last modified: November 24, 1998
mail to: bcarter@bcarter.com



Tip 81: Java In The Database

Getting Started With Sybase Adaptive Server Anywhere
Version 6

See also:
Nested Object Columns
Object Indexes
JDBC and jConnect
Cross-Server Database I/O

Once upon a time I tried to learn C. Actually, it was once upon three times I tried to learn C, and C++, and gave up each time: "If this is supposed to be a language for professionals then let me remain an amateur!"

Now the hot language is Java, and this marks the third time I've tried to learn it, too. Maybe I'll be lucky and stick with it this time. If so, it will be thanks to the fact that Java is available within Sybase Adaptive Server Anywhere Version 6. WATCOM SQL is a fine language as databases go but SQL ain't a "real" programming language by any stretch of the imagination. Java might not be pretty, or productive, but it's definitely real and as such it adds a huge amount of power to ASA 6.

Java in ASA 6 offers the following:

  • Java fields and even whole objects can appear as columns in your tables, in SELECT lists, and in WHERE, ORDER BY and other clauses.

  • Java methods can be invoked from SQL just like built-in or user-defined functions.

  • The COMPUTE ( expression ) constraint lets you define read-only columns based on Java expressions, and use those columns in indexes.

  • Java can be used instead of WATCOM SQL to write stored procedures, and they can perform database I/O via JDBC.

  • Java can also be used to write standalone applications, and they too can perform database I/O via JDBC and jConnect.
This tip is going to stick to the first point, "Java objects as columns", because the secret to True Happiness is to have reasonable expectations. And because adding your first Java column isn't just a matter of typing "CREATE TABLE".


Here's what you have to do to get started:

Step 1: Install ASA 6 Including the Java Bits

In the "Select Components" window of the Adaptive Server Anywhere 6.0 Install process, check "Adaptive Server Anywhere" and click on the "Change" button.

This will display the "Select Sub-components" window. Be sure to check "Java in the Database" and "Java Samples", and proceed with the installation. In addition to all the ASA software this will install the following bits and pieces you'll need to learn and use Java:

  • The full ASA Help file, including Chapter 16 Using Java in the Database:

    \Program Files\Sybase\Adaptive Server Anywhere 6.0\win32\dbmaen6.hlp
  • "Thinking in Java" by Bruce Eckel:

    \Program Files\Sybase\Adaptive Server Anywhere 6.0\win32\tjava.pdf
  • The Java examples directory:

    \Program Files\Sybase\Adaptive Server Anywhere 6.0\jxmp
  • The Sybase runtime Java classes, including the Sybase version of Sun's JDK 1.1.6 classes:

    \Program Files\Sybase\Adaptive Server Anywhere 6.0\java

Figure 1 shows how to find Java in the Help. In particular, it shows where to find instructions for installing the Java examples into the ASA demonstration database. This process is separate from the usual SETUP.EXE installation discussed above.

Figure 1: Installing The Java Examples Figure 1: Installing The Java Examples


Step 2: Get The Missing Bits

If you want to read the book "Thinking in Java" by Bruce Eckel that comes with ASA 6 you'll need a copy of the Adobe Acrobat Reader. You can download that for free from Adobe:
http://www.adobe.com/prodindex/acrobat/readstep.html

In order to compile your .java source files into .class files which can be loaded into a database, you'll need some kind of Java compiler. The cheapest such compiler is javac.exe included in the free JDK 1.1.6 download available from Sun:

http://java.sun.com/products/jdk/1.1.6/index.html

Here are some other useful links:


Step 3: Create a Java-Enabled Database

Figure 2 shows how to create a Java-enabled database via DBINIT.EXE, and Figure 3 shows DBINIT in action. The -n parameter specifies that no .log file is to be created, and -p 4096 sets the page size to 4K.
(Note: The sample commands show both drive letters C and E. Before running these samples on your computer make sure the drives and paths are correct.)

Figure 2: Create a Java Enabled Database
"E:\Program Files\Sybase\Adaptive Server Anywhere 6.0
  \win32\dbinit.exe" 
  -n -p 4096 c:\amisc\asajava\asajava.db

Figure 3: DBINIT in Action Figure 3: DBINIT in Action

Figure 4 shows how to start the ASAJava database via DBSRV6, and Figure 5 shows the server in action. The -x NONE parameter speeds startup by suppressing all communications links except for the Shared Memory link. The -c 12m parameter specifies a database cache large enough to allow meaningful operations involving Java.

Figure 4: Start the ASAJava Database
"E:\Program Files\Sybase\Adaptive Server Anywhere 6.0
  \win32\dbsrv6.exe" 
  -x NONE -c 12m c:\amisc\asajava\asajava.db

Figure 5: DBSRV6 in Action Figure 5: DBSRV6 in Action

Figure 6 shows how to connect to the ASAJava database via DBISQL, and Figure 7 shows DBISQL in action.

Figure 6: Connect to ASAJava with DBISQL
"E:\Program Files\Sybase\Adaptive Server Anywhere 6.0
  \win32\dbisql.exe" 
  -c "UID=dba;PWD=sql"

Figure 7: DBISQL in Action Figure 7: DBISQL in Action


Step 4: Write a Java Class

Figure 8 shows the .java source file for a simple "Hello, World" class that includes the following:
  • Two Java strings and a Java integer, called hello, world and counter, are fields inside HelloWorld that are visible outside the class.

  • The default constructor HelloWorld creates a new HelloWorld object with default values in the three fields. This is the constructor that's used when NEW HelloWorld() is specified in a SQL INSERT ... VALUES list.

  • A second constructor also called HelloWorld is provided for use when specific values are given for all three fields. This is the constructor that's used when NEW HelloWorld ( 'Good', 'Bye', 0 ) is specified in a SQL INSERT ... VALUES list. Even though these two constructors have the same name they really are different. That's because their argument lists are different; this is called function overloading... Java sure ain't SQL, and we're definitely not in Kansas any more.

  • The toString method converts the object as a whole to a string value. It's important to provide a toString method for all your ASA 6 Java objects: it's used when you specify an object in a SQL SELECT list (as opposed to specific fields within the object), and it's used in the CompareTo method shown below.

    This particular toString simply converts the counter field to a string and concatenates it together with hello and world.

  • Even though the three fields are public, the common Java coding practice is to use methods to read and write all fields. That's what these 6 methods are for: setHello, getHello, setWorld, getWorld, setCounter and getCounter. Isn't that precious? Now you know how to feel "productive" in Java... just count the lines of code in all your get and put methods. Actually, this practice isn't confined to Java, it's a bizarre and generally pointless artifact of OO programming in general.

  • The compareTo method compares this object to some other object of type HelloWorld that is passed as an argument. It's important to provide a compareTo method for all your ASA 6 Java objects: it's used when the object as a whole is referenced in a primary key, index, unique constraint, ORDER BY clause, GROUP BY clause, SELECT DISTINCT list, or function involving comparisons such as MAX and MIN.

    This particular compareTo method first checks the two counter values, and if those are identical, it uses a combination of HelloWorld.toString and the Java String.compareTo functions to make the final determination. In other words, it converts the two objects to strings and compares those. The final return value from your compareTo methods should always be +1 for "greater than", -1 for "less than" and zero for "equal".

Figure 8: The HelloWorld.java File
public class HelloWorld {
  
  // public fields
  public String hello ;
  public String world ;
  public java.lang.Integer counter ;

  // Default constructor
  HelloWorld () {
    hello = "Hello";
    world = "World";
    counter = new java.lang.Integer ( 1 );
  }

  // Constructor using all available arguments
  HelloWorld ( String inHello,
               String inWorld,
               int inCounter
             ) {
    hello = inHello;
    world = inWorld;
    counter = new java.lang.Integer ( inCounter );
  }

  public String toString() {
    return hello + " " + world + ": " + counter.toString();
  }

  public boolean setHello ( String newHello ) {
    hello = newHello;
    return true;
  }

  public String getHello() {
    return hello ;
  }

  public boolean setWorld ( String newWorld ) {
    world = newWorld;
    return true;
  }

  public String getWorld() {
    return world ;
  }

  public boolean setCounter ( java.lang.Integer newCounter ) {
    counter = newCounter;
    return true;
  }

  public java.lang.Integer getCounter() {
    return counter ;
  }

  public int compareTo( HelloWorld anotherHelloWorld ) {

    // Compare first on the basis of counter
    // and then on the basis of toString()

    java.lang.Integer lVal = counter;
    java.lang.Integer rVal = anotherHelloWorld.counter;

    if ( lVal.intValue() > rVal.intValue() ) {
      return 1;
    }
    else if (lVal.intValue() < rVal.intValue() ) {
      return -1;
    }
    else {
      return toString().compareTo( anotherHelloWorld.toString() );{
      }
    }
  }
}


Step 5: Compile the Java Class

Figure 9 shows a javac_HelloWorld.bat file you can use to compile HelloWorld.java into HelloWorld.class. The CLASSPATH environment variable is used to point to the Sybase version of the JDK 1.1.6 and other Java classes that must be used with ASA 6.

Figure 9: javac_HelloWorld.bat Runs JAVAC.EXE
SET CLASSPATH="E:\Program Files\Sybase\Adaptive Server 
  Anywhere 6.0\java"
e:\jdk1.1.6\bin\javac.exe -d c:\amisc\ASAJava HelloWorld.java

Figure 10 shows that JAVAC's GUI leaves something to be desired; it only displays messages when there are errors to report, and then you can only view the last screen's worth.

Figure 10: JAVAC in Action Figure 10: JAVAC in Action


Step 6: Install and Use the Java Class

Figure 11 shows the SQL INSTALL command that loads HelloWorld.class into the database, and a CREATE TABLE that defines a column JHelloWorld of type HelloWorld. Figure 11 also shows examples of the DROP TABLE and REMOVE JAVA CLASS commands that will remove all references to HelloWorld.

Figure 11: INSTALL and Use HelloWorld.class
INSTALL JAVA NEW FROM FILE 
   'c:\\amisc\\ASAJava\\HelloWorld.class';

CREATE TABLE java_hello_world
   ( pkey              INTEGER NOT NULL,
     JHelloWorld       HelloWorld NOT NULL,
     PRIMARY KEY ( pkey ) );

/* 
DROP TABLE java_hello_world;

REMOVE JAVA CLASS HelloWorld;
*/

Figure 12 shows five INSERT INTO commands and a simple SELECT * command.

Figure 12: INSERT and SELECT
INSERT INTO java_hello_world ( pkey, JHelloWorld )
   VALUES ( 1, NEW HelloWorld() );

INSERT INTO java_hello_world ( pkey, JHelloWorld )
   VALUES ( 2, NEW HelloWorld ( 'Hello', 'World', 0 ) );

INSERT INTO java_hello_world ( pkey, JHelloWorld )
   VALUES ( 3, NEW HelloWorld ( 'Hello', 'World', 2 ) );

INSERT INTO java_hello_world ( pkey, JHelloWorld )
   VALUES ( 4, NEW HelloWorld ( 'Good', 'Bye', 0 ) );

INSERT INTO java_hello_world ( pkey, JHelloWorld )
   VALUES ( 5, NEW HelloWorld ( 'Good', 'Bye', 2 ) );

SELECT *
  FROM java_hello_world 
 ORDER BY pkey;

The first INSERT uses the NEW keyword and the default HelloWorld constructor to fill the fields in the JHelloWorld column. The source code in Figure 7 shows that the following code will be executed.:

  // Default constructor
  HelloWorld () {
    hello = "Hello";
    world = "World";
    counter = new java.lang.Integer ( 1 );
  }

The other 4 INSERTs use the second constructor to assign specific values to each field inside JHelloWorld:

  // Constructor using all available arguments
  HelloWorld ( String inHello,
               String inWorld,
               int inCounter
             ) {
    hello = inHello;
    world = inWorld;
    counter = new java.lang.Integer ( inCounter );
  }

Figure 13 shows what the final SELECT looks like in DBISQL. The pkey column is an ordinary integer but JHelloWorld looks different. That's because it's an object and when an object as a whole is referenced in a SELECT list the toString method is used to gather the object's multiple fields into a single scalar value:

  public String toString() {
    return hello + " " + world + ": " + counter.toString();
  }

Figure 13: INSERT and SELECT Via DBISQL Figure 13: INSERT and SELECT Via DBISQL


Step 7: Query the Java Class

Figure 14 shows a more interesting SELECT command that demonstrates the following features:
  • Individual fields within an object column may be referenced via ColumnName >> fieldName.

  • You can also use dot notation ColumnName.fieldName. That looks more like Java, but it also looks like the usual SQL TableName.ColumnName and it might be confusing.

  • The WHERE clause shows how object fields can be used just about anywhere a SQL expression is allowed.

  • The ORDER BY clause shows that with reasonable implementations of the toString and compareTo methods, whole objects can also be referenced all over the place.

Figure 15 shows what the result looks like in DBISQL.

Figure 14: Query the Java Class
SELECT pkey,
       JHelloWorld >> hello as hello,
       JHelloWorld.world as world,
       JHelloWorld >> counter as counter,
       JHelloWorld
  FROM java_hello_world 
 WHERE JHelloWorld >> counter > 0
 ORDER BY JHelloWorld 

Figure 15: Query the Java Class Figure 15: Query the Java Class


At this point, you (and I) are up and running with Java in ASA 6, at least as far as the usual development life cycle is concerned: Edit, Compile, Test, Reboot.

Of course, there's a lot more to cover, but it's a start.

See also:
Nested Object Columns
Object Indexes
JDBC and jConnect
Cross-Server Database I/O


[Home] [Back to Tip 80] [Forward to Tip 82] [Archives] [mail to: bcarter@bcarter.com]