c-treeACE SQL Server Side Functionality with Java Stored Procedures

Blog

September 30, 2011

c-treeACE SQL Server Side Functionality with Java Stored Procedures

Note: c-treeACE became FairCom DB in October 2020.

All challenging software projects have specific needs and FairCom has always met that challenge by providing the flexibility developers require. c‑treeACE users have long known they can take advantage of the CTUSER() function to include custom functionality. The ability to include your own unique logic within the server not only provides added functionality, but in many cases, extra performance. It also protects data integrity. Common uses include:

  • Consolidate Complex Business Logic
  • Enhanced Reporting
  • Utility Functions
  • System Maintenance
  • Common Tasks

c‑treeACE SQL extends this ability with Java Stored Procedures. Easily create application specific procedures without custom compiling and worrying about matching server side shared objects (.dlls). Using the easily available Java environment substantially reduces the risk of server embedded modules. In many cases, by including the logic server side you can quickly and easily change the implementation of your business rules without changes to the underlying application. Simply change the stored procedure to include updates or improved logic.

Using stored procedures also can enhance performance. As the procedures are stored in compiled form, they execute much quicker than a corresponding SQL script.

By utilizing the cross platform Java environment for stored procedures, easily develop on your platform of choice. The Java language gives you an extended functional programming environment in which to implement complex logic. Take complete advantage of Java and package your frequently used utility functions into standalone .jar files and import them in your custom procedures. Doing so makes maintaining a common source for all developers easy to manage. You have access to nearly all available Java classes, including your own.

stored_procedure2011

Basic Structure of a c‑treeACE SQL Stored Procedure

CREATE PROCEDURE  storedproc(
	IN in_params ...
	OUT out_params ...
	INOUT inout_params
)
IMPORT java.*; com.company.financial.libs.*; com.company.security.libs.*;
BEGIN
	Java Snippet
END

c‑treeACE SQL Java Environment

c‑treeACE SQL requires location information for three important components to compile and execute Java stored procedures.

  • Location of the Java Run Time Engine jvm.dll
  • CLASSPATH locations of the Java run time environment, rt.jar, and the c‑treeACE SQL Stored Procedure classctreeSQLSP.jar.
  • Location of the Java compiler javac.exe

Configure c‑treeACE SQL to load the Java environment via several server configuration keywords:

SETENV CLASSPATH=C:\Program Files\Java\jdk1.6.0_26\jre\lib
t.jar;C:\FairCom\V9.5.0\win32\bin\ace\sql\classes\ctreeSQLSP.jar
SETENV JVM_LIB=C:\Program Files\Java\jdk1.6.0_26\jre\bin\server\jvm.dll
SETENV JAVA_COMPILER=C:\Program Files\Java\jdk1.6.0_26\bin\javac.exe

c‑treeACE SQL currently supports Java JDK/JRE 1.6

c‑treeACE SQL Java Classes

Several c‑treeACE SQL classes are available for use within your stored procedures.

  • DhSQLException – extends the general java.lang.Exception class to provide detail about errors in c‑treeACE SQL statement execution.
  • DhSQLResultSet – methods to populate a result set that the stored procedure returns to the application that called the procedure.
  • SQLCursor – methods to retrieve rows of data from a database or another stored procedure’s result set.
  • SQLIStatement – methods to provide for immediate (one-time) execution of c‑treeACE SQL statements that do not generate a result set.
  • SQLPStatement – methods to provide for prepared (repeated) execution of c‑treeSQL statements that do not generate a result set.

Refer to the c‑treeACE SQL Java Stored Procedures, Triggers and User Defined Functions Guide for complete information regarding the use of these classes.

Examples

Here are just a few examples of tasks you can quickly handle with a c‑treeACE SQL stored procedure.

  • Create Directories — Many times, it is necessary to create directory structures on the fly. This is easy to do in Java. Note that paths are relative to the server’s working directory, unless a fully qualified path is provided. (For example, a drive identifier in Windows.)

cre_dir()

call cre_dir('my/new/path');
  • Get Current Server Time — With many applications, it is important to synchronize the time between clients. Here is an easy way to retrieve the time from a c‑treeACE SQL server in .NET.

get_time()

CtreeSqlCommand hCommand = new CtreeSqlCommand(hConnection);
hCommand.CommandText = "get_time";
hCommand.CommandType = CommandType.StoredProcedure;
CtreeSqlParameter param1 = hCommand.Parameters.Add("cur_time", CtreeSqlDbType.BigInteger);
CtreeSqlDataReader hReader =  hCommand.ExecuteReader();
  • Create and Secure Passwords — Consider an application that has users and passwords, and requires a secure method of storing those. Storing only a hash of the password prevents actual passwords from traversing the network when checking authentication. The SHA1 hash is considered very secure for this use. Also, remember to “salt” the password hash to add an additional layer of protection and avoid dictionary attacks. This simple example relies on a table that stores a user name, the password hash, and the salt value used to create the hash. Only the server side function should ever know this salting value.

sha1_pass()

call sha1_pass('user', 'pass');

Get creative!

Take advantage of all Java classes available:

  • Throw your own exceptions
  • Encryption (java.security.*)
  • XML Handling (JAXP)
  • I/O (java.io.*)
  • Utility classes (java.util.*)

    UUID

    Calendars

    Zip

  • Image handling (javax.imageio.*)

    .jpg .gif .png .eps

  • Network Access

    HTML from web

c‑treeACE SQL Stored Procedures provide a flexible, general mechanism to store collections of server based functionality for greatly enhanced development integrity and productivity.

See Also

×