Creating SQLJ Code for Use within the Server

With few exceptions, writing SQLJ code for use within the target Oracle8i server is identical to writing SQLJ code for client-side use. The few differences are due to Oracle JDBC characteristics or general Java characteristics in the server, rather than being specific to SQLJ. There are a few things to be aware of, however:

• There is an implicit connection to the server itself.

• There are coding issues such as lack of auto-commit functionality.

• In the server, the default output device is the current trace file.

• Name resolution functions differently in the server than on a client.

• SQL names must be interpreted and processed differently from Java names.

  Note: Writing SQLJ code to connect from one server to another through the server-side Thin driver is identical to writing code for an application that uses a client-side Thin driver. The points in this discussion do not apply.

Database Connections within the Server

The concept of connecting to a server is different when your SQLJ code is running within this server itself--there is no explicit database connection. By default, an implicit channel to the database is employed for any Java program running in the server. You do not have to initialize this "connection"--it is automatically initialized for SQLJ programs. You do not have to register or specify a driver, create a connection instance, specify a default connection context, specify any connection objects for any of your #sql statements, or close the connection.

Note: In the server, setting the default connection context to null, as follows, will reinstall the default connection context (the implicit connection to the server): DefaultContext.setDefaultContext(null);

Coding Issues within the Server

There are just a few coding issues to consider when your code will run within the target server using the server-side internal driver. Note the following:

• Result sets issued by the internal driver persist across calls, and their finalizers do not release their database cursors. Because of this, it is especially important to close all iterators to avoid running out of available cursors, unless you have a particular reason for keeping an iterator open (such as when it is actually used across calls).

• The internal driver does not support auto-commit functionality--the auto-commit setting is ignored within the server. Use explicit COMMIT or ROLLBACK statements to implement or cancel your database updates:

  #sql { COMMIT };
  ...
  #sql { ROLLBACK };
  
  

  Note: If you are using any kind of XA transactions, such as Java Transaction Service (JTS) transactions, you cannot use SQLJ or JDBC COMMIT/ROLLBACK statements or methods. This applies particularly to Enterprise JavaBeans or CORBA objects, which typically use such transactions. See "Additional Vehicles for SQLJ in the Server".

For more information about server-side JDBC and the server-side internal and Thin drivers, see the Oracle8i JDBC Developer's Guide and Reference.

Default Output Device in the Server

The default standard output device in the Oracle8i Java virtual machine (JVM) is the current trace file.

If you want to reroute all standard output from a program executing in the server (output from any System.out.println() calls, for example) to a user screen, then you can execute the SET_OUTPUT() procedure of the DBMS_JAVA package, as follows (inputting the buffer size in bytes):

sqlplus> execute dbms_java.set_output(10000);

(Output exceeding the buffer size will be lost.)

If you want your code executing in the server to expressly output to the user screen, you can also use the PL/SQL DBMS_OUTPUT.PUT_LINE() procedure, instead of the Java System.out.println() method.

The PUT_LINE() procedure is overloaded, accepting either VARCHAR2, NUMBER, or DATE as input to specify what is printed.

For more information about the DBMS_OUTPUT package, see the Oracle8i Supplied PL/SQL Packages Reference.

Name Resolution in the Server

Class loading and name resolution in the server follow a very different paradigm than on a client, because the environments themselves are very different. This section gives only an overview; the topic is discussed in detail in the Oracle8i Java Developer's Guide.

Java name resolution in the Oracle8i JVM includes the following:

• class resolver specs, which are schema lists to search in resolving a class schema object (functionally equivalent to the CLASSPATH on a client)

• the resolver, which maintains mappings between class schema objects that reference each other in the server

A class schema object is said to be resolved when all of its external references to Java names are bound. In general, all the classes of a Java program should be compiled or loaded before they can be resolved. (This is because Java programs are typically written in multiple source files that can reference each other recursively.)

When all the class schema objects of a Java program in the server are resolved and none of them have been modified since being resolved, the program is effectively pre-linked and ready to run.

A class schema object must be resolved before Java objects of the class can be instantiated or methods of the class can be executed.

Note: The loadjava utility resolves references to classes, but not to resources. If you translated on the client, be careful how you load any resources into resource schema objects in the server, as discussed in "Loaded Class and Resource Schema Objects". (If you enabled the SQLJ -ser2class flag for your client-side translation, then your SQLJ profiles will be in class files, and you will typically not have any resource files. If you did not enable -ser2class, then your profiles will be in .ser resource files.)

SQL Names versus Java Names

SQL names (such as names of source, class, and resource schema objects) are not global in the way that Java names are global. The Java Language Specification directs that package names use Internet naming conventions to create globally unique names for Java programs. By contrast, a fully qualified SQL name is interpreted only with respect to the current schema and database. For example, the name SCOTT.FIZZ in one database does not necessarily denote the same program as SCOTT.FIZZ in another database. In fact, SCOTT.FIZZ in one database can even call SCOTT.FIZZ in another database.

Because of this inherent difference, SQL names must be interpreted and processed differently from Java names. SQL names are relative names and are interpreted from the point of view of the schema where a program is executed. This is central to how the program binds local data stored at that schema. Java names are global names, and the classes that they designate can be loaded at any execution site, with reasonable expectation that those classes will be classes that were used to compile the program.

---