|
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
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 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 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
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
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
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
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.
|
