Java Stored Procedures
OpenBase SQL makes it easy to write server-side Java stored procedures (Java SP) that take advantage of existing Java solutions to add functionality to applications and integrate business processes.
Video: Java Stored Procedures
Support for Java SP enables any Java code, object, .class or .jar file to be turned into a stored procedure running in the OpenBase SQL database. By centralizing business logic inside the database, OpenBase SQL enables any type of database client (web and client/server) to access and use the same processes, significantly reducing code duplication, complexity and time-to-deploy.
OpenBase SQL support for Java SP makes it possible to use a variety of interfaces and objects not normally available through a database. This reduces the time, cost and complexity involved to connect your business processes with the outside world. Some examples of possible business processes that could be created using stored procedures include:
- Checking a customer's credit history
- Processing a credit card payment and recording it in the database
- Entering an order
- Notifying all customers via e-mail when their licenses are about to expire
- Sending an automated email thanking someone for visiting you website
With OpenBase SQL and Java SP you only have to write and compile a plug-in once. It will run everywhere the OpenBase SQL database is deployed, regardless of the destination server operating system.
The OpenBase SQL Java SP Mechanism
Using the OpenBase SQL Java SP mechanism is simple. Here is how to create a Stored Procedure:
1. Import the OBStoredProcedure.jar file located in /Library/OpenBase/ Java/Extensions into your project
2. Create a new java class that subclasses the abstract OpenBaseProcedure class defined in the OBStoredProcedure jar file
3. Implement all the abstract methods defined in OpenBaseProcedure.class
After compiling, the resulting .class file (example: myStoredProcedure.class) becomes your stored procedure plug-in which you install into one of the Plug-in directories on your server.
It is very important to note that only a .class file can be used in a Java SP plug-in. However you can reference in myStoredProcedure.class any objects from any .class or any .jar files as long as you add those files to the java CLASSPATH on your computer system.
Note: On Mac OS X, any .jar files you add to /Library/OpenBase/Java/Extensions are automatically available to your plug-in class.
Java SP Code Structure
A Java SP is a Java .class file that contains the compiled code necessary to perform a task and returns a result to the client. Each Java SP .class file must respond to a series of methods which give the calling program information about the IN and OUT parameters of the procedure represented by the class.
Before writing your own stored procedure, we recommend that you copy the HelloWorld or MoviesFetch example located in /Library/OpenBase/Developer/Examples/StoredProcedures and read through the code for a complete description.
Java SP Description
Each stored procedure must provide a description which allows OpenBase to populate the database system tables accurately and register the stored procedure in the database. This section summarizes these stored procedure routines with examples taken from MoviesFetch.
public boolean needsToBeRegistered()
Should always return true. This function confirms that the class is a stored procedure and should be registered with the database.
Example:
public boolean needsToBeRegistered(){ return true;
}
public String getProcedureType()
Returns procedureResultUnknown, procedureNoResult, procedureReturnsResult values depending on what is returned. These values correspond exactly to the JDBC specification for stored procedure types.
Example:
public String getProcedureType(){ return procedureReturnsResult; }
public String getProcedureName()
The stored procedure name is exactly what people will call when they invoke this procedure. The name is case sensitive.
Example:
public String getProcedureName(){ return "MoviesFetch"; }
public int getNumberOfParameters()
Returns the total number of parameters stored procedure has (both IN and OUT parameters). OUT parameters may include multiple result columns. IN parameters are passed into the procedure between the parenthesis.
Example:
public int getNumberOfParameters(){ return 5;
public int getNumberOfInParameters()
Example:
public int getNumberOfInParameters(){
// We only have 1 IN parameter (movie primary key) return 1;
}
public String getUsageDescription()
Returns the usage description of this stored procedure. This will be stored in REMARKS in the _SYS_PROCEDURES table.
Example:
public String getUsageDescription(){ return "call MoviesFetch('movieID')"; }
Important: BEGINNING WITH OPENBASE 7.0.2 YOU NEED TO USE defineParamDescription() INSTEAD OF getParamDescription(). See the examples for more details.
public void defineParamDescription()
Returns a list of parameter descriptions. This list must contain a number of descriptions equal to the value returned by getNumberOfParameters(). Each parameter's description is stored using calls to addParamDescription().
Example:
public void defineParamDescription(){
// Make Hashtable & store movie primary key IN param. desc. addParamDescription("movieID", "This parameter represents the primary key of the movie row we want to fetch.");
// Make Hashtable to store movie's title (OUT param. desc.) addParamDescription("TITLE",
"This is the movie's title.", DatabaseMetaData.procedureColumnResult);
// Make Hashtable to store rating (OUT param. desc.) addParamDescription("RATED",
"This is the movie's rating.", DatabaseMetaData.procedureColumnResult);
// Make Hashtable to store release date (OUT param. desc.) addParamDescription("RELEASED_DATE",
"This is the movie's released date.", DatabaseMetaData.procedureColumnResult,
"date");
// Make Hashtable to store category (OUT param. desc.) addParamDescription("CATEGORY",
"This is the movie's category.", DatabaseMetaData.procedureColumnResult);
public boolean requiresDatabaseConnection()
This method tells whether the procedure requires an open connection to the database. If your stored procedure does not need a connection to the database, you should return false.
Example:
public boolean requiresDatabaseConnection(){ return true;
}
The Stored Procedure
public Vector executeProcedureWithParameters (Vector params, Connection connection) throws Exception
The executeProcedureWithParameters() method does all the work inside your stored procedure. This is where you put your business logic and return results to the caller.
Example:
/**
* Execute code this stored procedure needs to perform
@param an array of IN parameters
@param a connected JDBC Connection or null if this procedure doesn't need a database connection
@return a Vector containing the output for this method
*/
public Vector executeProcedureWithParameters(Vector params, Connection connection) throws Exception{
// Create the Vector object that will contain // the output for this stored procedure Vector result = new Vector();
// First get the movie primary key value. This
// valued is stored in the params Vector String movieID = (String)params.elementAt(0); Statement s = connection.createStatement(); ResultSet rs = s.executeQuery("SELECT TITLE, RATE D,
DATE_RELEASED, CATEGORY FROM MOVIE WHERE MOVIE_ID = " + movieID);
if (rs.next () ){
String title = rs.getString(1);
String rating = rs.getString(2);
java.sql.Date date = rs.getDate(3);
String category = rs.getString(4);
// We need to put to output the results result.addElement(title);
result.addElement(rating);
result.addElement(date);
result.addElement(category);
}
// Finally return the result return result; }
Where Do Java SP's Go?
With OpenBase SQL, you can define stored procedures that are globally accessible as well as local to a single database.
For example, you can put stored procedures specific to a particular database inside the database bundle's Procedures/Plugins folder. For the pubs database, this would be /Library/OpenBase/Databases/pubs.db/ Procedures/Plugins. However, if the stored procedure is a more generic function, such as clearing a credit card or sending an email, it could be located inside /Library/OpenBase/Procedures/Plugins.
This would make it accessible from all the databases on your computer. Local Java stored procedures go in:
/Library/OpenBase/Databases/YOURDB.db/Procedures/Plugins
Global Java stored procedures go in:
/Library/OpenBase/Procedures/Plugins
Meta-Data for Stored Procedures
The stored procedure definitions and parameter descriptions are loaded into two database system tables: _SYS_PROCEDURES and _SYS_PROCEDURE_PARAMS that can be accessed from your programs.
As a standard practice, we include an example of how to call the procedure in the REMARKS column.
The following is an example of what a sample _SYS_PROCEDURES table looks like:
openbase 1> select PROCEDURE_NAME, NUM_PARAMS, REMARKS from SYS_PROCEDURES_
openbase 2> go
Data returned... calculating column widths
PROCEDURE_NAME NUM_PARAMS REMARKS
---------------------------------------------------------
MoviesFetch 1 call MoviesFetch('movieID')
ReplicateDatabase 0 call ReplicateDatabase()
CleanupDatabase 0 call CleanupDatabase()
HelloWorld 1 call HelloWorld('firstName')
--------------------------------------------------------
4 rows returned - 0.004 seconds (printed in 0.005 seconds) openbase 1>
_SYS_PROCEDURE_PARAMS describes in detail all the inputs and outputs related to stored procedures.
openbase 1> select * from _SYS_PROCEDURE_PARAMS openbase 2> go
Data returned... calculating column widths
rowid PROCEDURE NAME COL NAME PARAM IDX TYPE REMARKS -----------------------------------------------------------------
1 HelloWorld firstName 1 1charThis parameter represents the
firstName.
2 HelloWorld result 1 3char This is the only OUT parameter of
HelloWorld stored
procedure.
3 MoviesFetchmovieID 1 1 charThis parameter
represents the
primary key of the
movie row we want
to fetch.
4 MoviesFetchTITLE 1 3charThis is the
movie's title.
5 MoviesFetchRATED 2 3char This is the movie's rating.
6 MoviesFetchREL_DATE33dateThis is the movie's released
date.
7 MoviesFetch TITLE4 3char This is the movie's title.
----------------------------------------------------------------
7 rows returned - 0.004 seconds (printed in 0.011 seconds) openbase 1>
Note: COLUMN_TYPE above corresponds to the COLUMN_TYPE parameter in the JDBC specification. A COLUMN_TYPE of 1 specifies an input parameter. A COLUMN_TYPE of 3 specifies an output parameter.
Calling a Stored Procedure
A stored procedure call can be invoked exactly like any other SQL command. To call a stored procedure, simply follow the example in the REMARKS column of _SYSPROCEDURES table.
Return values are returned exactly the same way as fetching the results of a select statement. Here are some examples that pass in parameters to a stored procedure:
call MoviesFetch(113)
call MyTestProcedure("Alan", "Smith")
Parameters can be of any type. However, they are converted to string representations before being passed to stored procedures. Parameter values have a maximum length of 4,096 characters. If you want to pass something bigger than 4,096 characters, you can store it as a BLOB and then let the stored procedure retrieve the BLOB using a primary key.
You can also call stored procedures from any other stored procedure. However, with transactions, you need to be very careful that you do not create a deadlock. There can be instances in which the stored procedure mechanism flies beneath the radar of the built-in OpenBase deadlock avoidance system. To avoid a deadlock, it is recommended that you do not call any other stored procedures while inside a transaction, unless the sub-procedure does not access the database.
Frequently Asked Questions
Why create a stored procedure in Java?
There are several advantages. With Java you only have to compile your plug-in once to run everywhere OpenBase is deployed. You can write plug-ins without worrying about the destination server operating system. And you can use virtually any Java code, object, .class or .jar file allowing you to develop interfaces with just about anything.
Can I create a Java SP as a .jar file?
Currently, only .class files are supported in the 'Plug-ins' folder. This doesn't mean that you can't call or instantiate objects that are part of .jar files from within your Java SP .class file, just as long as the external .jar file is placed in the OpenBase/Java/Extensions directory.
I just copied a stored procedure class file in a 'Plug-ins' folder but OpenBase does not seem to register it.
After you put a stored procedure in the 'Plug-ins' folder, you need to restart the OpenBase server to register the new stored procedure. Also be sure that the 'needstoberegistered()' in your stored procedure code returns true.
Additional Resources
This document is designed to provide you with a starting point on understanding how to write and use Java stored procedures in the OpenBase SQL database. You can find additional information and advice in the following resources:
If you think the question appears to be a bug in OpenBase, please send it to the OpenBase support team ( moc.esabnepo|troppus#moc.esabnepo|troppus )
You may also want to join the OpenBase Developer mailing list. You can do this by visiting the support section of the OpenBase website ( www.openbase.com ).
