6.2 Designing Server-Based SQLJ Programs
The JServer JVM is integrated with the Oracle8i database, and as you might imagine, developing a SQLJ program to be run by the JServer JVM is somewhat different from developing the SQLJ programs you have seen so far. The following list highlights the issues unique to SQLJ programs designed to run within the JServer JVM:
-
The SQLJ runtime packages are available to the JServer JVM.
-
A connection to the database does not have to be made by your program because the JServer JVM already has a connection to the database.
-
The connection between your program and the database is permanent and therefore cannot be closed: attempts to close the connection are ignored.
-
There is no auto-commit functionality, so your program should commit or roll back the results of your SQL DML statements explicitly.
-
By default, Java programs running in the JServer JVM send their output to the database trace file. However, this output can be routed to the screen.
In the following sections, I discuss each of these issues in detail.
6.2.1 SQLJ Runtime Packages
SQLJ programming depends on a number of Java packages. When you write a SQLJ program for a JVM that is external to the database, you usually import SQLJ runtime packages such as oracle.sqlj.runtime.Oracle. However, when your target is the JServer JVM, which is integrated with the database, these packages are already available.
6.2.2 Database Connections
When your programs run in the JServer JVM, you do not need to explicitly create a connection to the database; the connection between your program and the database is implicit and mediated through the JServer JVM. This implicit connection to the database is made through the JDBC KPRB (Kernel Program Bundled) driver, which is specifically designed for server programs. As a result, your program doesn't need to make an explicit connection to the database using a call to the Oracle.connect( ) method, as in the previous programs you have seen. Because this database connection is implicit, your program cannot close it.
6.2.3 Auto-commit
Back in Chapter 3, you learned about SQLJ's auto-commit functionality. Enabling auto-commit causes your SQLJ program to follow each successfully executed SQL statement with an implicit COMMIT statement. You enable auto-commit by passing the Boolean true value as the fourth parameter to a connect call. For example:
Oracle.connect( "jdbc:oracle:thin:@localhost:1521:orcl", "username", "password", true );
Auto-commit is not available when your program is running inside the JServer JVM. Instead, transactions are managed in the traditional Oracle manner: they begin with the first SQL statement that you execute and end when one of the following occurs:
-
You execute a COMMIT statement.
-
You execute a ROLLBACK statement.
-
You execute a Data Definition Language (DDL) statement.
Once one transaction ends, the next SQL statement begins another transaction.
6.2.4 Output
Because the JServer JVM is integrated with the database, it has no display screen directly associated with it. Java output, such as that from System.out.println( ) calls in your SQLJ programs, must therefore go somewhere besides the screen. The destination for such output must also be accessible by JServer through the database. By default, JServer writes any output from your program to the database trace file. Database trace files are primarily used by the Oracle database software to write log messages, such as those indicating when the database was started, shut down, and so forth. Trace files are contained in the directory specified by the init.ora database configuration file parameter user_dump_dest. There may be more than one trace file in this directory, because once a trace file gets to the size specified by the max_dump_file_size parameter, a new trace file is created.
If you are invoking your programs via SQL*Plus, there are two ways that you can get the output from the SQLJ program to be displayed on your screen:
-
Use the PL/SQL dbms_java.set_ouput( ) procedure to set up a buffer to accept standard output from the program.
-
Use the PL/SQL dbms_output.enable( ) procedure to set a up a buffer to accept output from the PL/SQL dbms_output.put_line( ) procedure.
In both cases, your output ends up in a buffer that SQL*Plus will then read and display. To see the output in SQL*Plus, you have to issue the SET SERVEROUTPUT ON command. The dbms_java.set_output( ) procedure accepts one argument: an integer specifying the buffer size in bytes. For example, the following call allocates a 1000-byte buffer:
CALL dbms_java.set_output(1000);
Once you've called the dbms_java.set_output( ) procedure, any output from the program in the form of System.out.println( ) calls is redirected to the buffer and later displayed by SQL*Plus. It's important that you size the buffer large enough to contain all the output you expect from the program. Output from your program is buffered while the program runs, and displayed only after the program completes. Once the buffer fills, further output is lost.
As an alternative to using dbms_java.set_output( ) and System.out.println( ), you can use the PL/SQL dbms_output package to buffer output for display using the following steps:
-
Use the PL/SQL dbms_output.enable( ) procedure to set up a buffer. This procedure accepts an integer that specifies the buffer size in bytes.
-
Use the PL/SQL dbms_output.put_line( ) procedure to write output to the buffer created in Step 1.
The following examples show how both text and numeric data can be written to a buffer using the dbms_output package:
#sql { CALL dbms_output.enable(1000) }; #sql { CALL dbms_output.put_line('A line of text') }; #sql { CALL dbms_output.put_line(1000) }; As is the case when you use dbms_java.set_output( ), all output from your program is buffered until the program ends. Only then does SQL*Plus read the buffer and display the contents on the screen for you to see.
6.2.5 Example Program: ServerExample1.sqlj
This section contains a complete program that is designed to run in the JServer JVM and that you will later load into the fundamental_user schema. The program is named ServerExample1.sqlj and is shown in Example 6-1. It contains the following four methods:
- testMessage( )
-
Shows the various ways of sending output from a server-based program to the database trace file and to the buffers.
- addCustomer( )
-
Adds a row to the customers table and writes a line containing the new id for the row to a Java buffer.
- displayCustomer( )
-
Retrieves a row from the customers table and writes several lines to a Java buffer in order to display the customer ID, first name, and last name.
- countCustomers( )
-
Counts the total number of rows in the customers table and returns the total as the method's result.
Example 6-1. ServerExample1.sqlj
/* The program ServerExample1.sqlj is designed to run in the Oracle JServer Java Virtual Machine. The program can add, display and count rows from the customers table in the fundamental_user schema. */ import java.sql.*; public class ServerExample1 { public static void testMessage( ) { try { // the following line writes output to the database trace file System.out.println("This line of output from ServerExample1.sqlj " + "appears in the database trace file"); // use the PL/SQL procedure dbms_output.enable( ) to // set the output buffer to 1000 bytes #sql { CALL dbms_output.enable(1000) }; // use the PL/SQL procedure dbms_output.put_line( ) // to write the output to the buffer, which is eventually // displayed on the client computer screen #sql { CALL dbms_output.put_line( 'Displayed on the screen from dbms_output.put_line( )' ) }; // use the PL/SQL procedure dbms_java.set_output( ) to // set the output buffer to 1000 bytes, this causes // output from System.out.println( ) calls to be buffered and // eventually displayed on the client computer screen #sql { CALL dbms_java.set_output(1000) }; System.out.println("Displayed on the screen " + "from System.out.println( )"); } catch (SQLException e) { System.out.println("SQLException " + e); System.exit(1); } } // end of testMessage( ) public static void addCustomer(String first_name, String last_name) { // variable used to store the next id int id = 0; try { #sql { CALL dbms_java.set_output(1000) }; // get the next id #sql { SELECT MAX(id) + 1 INTO :id FROM customers }; #sql { INSERT INTO customers (id, first_name, last_name) VALUES (:id, :first_name, :last_name) }; System.out.println("Customer has been assigned an id of " + id); } catch (SQLException e) { System.out.println("SQLException " + e); System.exit(1); } } // end of addCustomer( ) public static void displayCustomer(int id) { try { #sql { CALL dbms_java.set_output(1000) }; String first_name = null; String last_name = null; #sql { SELECT first_name, last_name INTO :first_name, :last_name FROM customers WHERE id = :id }; // Display the details System.out.println("Customer " + id + " has the following details:"); System.out.println("First name: " + first_name); System.out.println("Last name: " + last_name); } catch (SQLException e) { System.out.println("SQLException " + e); System.exit(1); } } // end of displayCustomer( ) public static int countCustomers( ) { int cust_count = 0; try { #sql { CALL dbms_java.set_output(1000) }; #sql { SELECT COUNT(*) INTO :cust_count FROM customers }; } catch (SQLException e) { System.out.println("SQLException " + e); System.exit(1); } return cust_count; } // end of countCustomers( ) } The program ServerExample1.sqlj will be used in the examples in the following sections on how to deploy and run SQLJ programs inside the database JServer. Before you can actually invoke the methods implemented by ServerExample1.sqlj in programs that you write, you must perform the following three steps:
-
Translate your SQLJ program.
-
Load the resulting Java class files into the database.
-
Create PL/SQL procedures, functions, or packages that invoke the methods in your SQLJ program. These are known as PL/SQL wrappers , because they wrap around your SQLJ program. The action of creating such wrappers is known as publishing your SQLJ program.
Once you have completed these three steps, you can then call the PL/SQL wrappers to invoke your SQLJ program's methods. The following sections describe each of these steps in detail.
