Java Stored Procedures, Triggers, and UDFs

The FairCom DB SQL implementation of stored procedures allows use of standard Java programming constructs instead of requiring proprietary flow-control language. To do this, the FairCom DB SQL interacts with Java in the following ways:

• When you create a stored procedure, FairCom DB SQL processes the Java code, submits it to the Java compiler, and receives the compiled results to store in the database.
• When applications call a stored procedure, FairCom DB SQL interacts with the Java virtual machine (JRE) to execute the stored procedure and receive any results.

Creating Stored Procedures

The Java source code that makes up the body of a stored procedure is not a complete program. It is a snippet that FairCom DB SQL converts into a Java class when it processes a CREATE PROCEDURE statement.

Creating a stored procedure involves the following steps:

1. Some application or tool (FairCom DB SQL Explorer, Interactive SQL utility, or an application) issues a CREATE PROCEDURE statement containing the Java snippet.
2. FairCom DB SQL adds additional supporting code to the Java snippet to create a complete Java class and submits the combined code to the Java compiler (javac).
3. Presuming there are no Java compilation errors, the Java compiler sends compiled bytecode back to FairCom DB SQL. If there are compilation errors, FairCom DB SQL passes the first error generated back to the application or tool that issued the CREATE PROCEDURE statement.
4. FairCom DB SQL stores both the Java source code and the bytecode form of the procedure in the database.

The following figure illustrates the steps in creating a stored procedure:

Calling Stored Procedures

Once a stored procedure is created and stored in the database, any application (or other stored procedure) can execute it by calling it. You can call stored procedures from ODBC, JDBC, or .NET applications, or directly from the Interactive SQL utility, isql.

For example, the following example shows an excerpt from an ODBC application that calls a stored procedure (order_parts) using the ODBC syntax:

{ call procedure_name ( param ) }.

Executing a Stored Procedure Through ODBC

SQLUINTEGER Part_num;
SQLINTEGER  Part_numInd = 0;

// Bind the parameter.
SQLBindParameter (hstmt, 1, SQL_PARAM_INPUT, SQL_C_SLONG, SQL_INTEGER, 0, 0,
                 &Part_num, 0, Part_numInd);

// Place the department number in Part_num.
Part_num = 318;

// Execute the statement.
SQLExecDirect(hstmt, "{call order_parts(?)}", SQL_NTS);

Executing a stored procedure involves the following steps:

1. The application calls the stored procedure through its native calling mechanism (for example, using the ODBC call escape sequence).
2. FairCom DB SQL retrieves the compiled bytecode-form of the procedure and submits it to the Java virtual machine for execution.
3. For every FairCom DB SQL statement in the procedure, the Java virtual machine calls FairCom DB SQL.
4. FairCom DB SQL manages interaction with the underlying database system and execution of the SQL statements, and returns any results to the Java virtual machine.
5. The Java virtual machine returns results (output parameters and result sets) of the procedure to FairCom DB SQL, which in turn passes them to the calling application.

The following figure illustrates the steps in executing a stored procedure.

LVARCHAR Fields Allowed in Stored Procedure Code

Within a Java stored procedure, it is now possible to retrieve, insert, and update the content of an LVARCHAR field. the following changes have been made:

SQLIStatement and SQLPStatement methods have been enhanced such that the SetParam method can be used to specify the content of an LVARCHAR field.

The SQLCursor GetValue method has been enhanced to retrieve the entire content of an LVARCHAR field.

The SQLCursor class has a new member getLongValue to retrieve a chunk of a LVARCHAR field (in case the field content is too large).

Function

public Object getLongValue(int field, int offset, int len)

where:

• field - the field number
• offset - the starting point (0 based) where the portion you want to retrieve starts.
• len - the length of the portion to retrieve.

Return

The function return NULL if there is no more to retrieve.

Java Stored Procedures and Triggers Require JDK V1.7 or Newer

Note: The FairCom DB SQL Java Stored Procedure and Trigger support requires a Java Development Kit (JDK) be installed on your computer. A Java Runtime Environment (JRE) is not sufficient. FairCom DB V11.5 (FairCom RTG and FairCom Edge V2.5) and later require JDK V1.7 or newer.

Note: For the tutorials to work correctly, the three Java-related “SETENV” lines in your <faircom>/config/ctsrvr.cfg file need to be active (the lines are not commented out with leading semicolons), the lines are all set to paths that are valid on your computer, and there is no whitespace before or after the "=" sign. Here are some typical examples, which will need to be adjusted for your machine:

Microsoft Windows

; JDK environment settings - Be sure to set the JDK to your version.
SETENV    CLASSPATH=C:\Program Files\Java\jdk1.7.0_75\jre\lib\rt.jar;.\classes\ctreeSQLSP.jar
SETENV    JVM_LIB=C:\Program Files\Java\jdk1.7.0_75\jre\bin\server\jvm.dll
SETENV    JAVA_COMPILER=C:\Program Files\Java\jdk1.7.0_75\bin\javac.exe

Linux

; JDK environment settings - Be sure to set the JDK to your version.
SETENV CLASSPATH=/usr/java/jdk1.7.0_75/jre/lib/rt.jar:./classes/ctreeSQLSP.jar
SETENV JAVA_COMPILER=/usr/java/jdk1.7.0_75/bin/javac
SETENV JVM_LIB=/usr/java/jdk1.7.0_75/jre/lib/amd64/server/libjvm.so

The lines above all need to correctly point to your JDK installation folder before you start the c-tree server, because changes to ctsrvr.cfg take effect only when you launch the c-tree server.

All of these Java-related lines should point at files which are in the same JDK folder (“jdk1.7.0_75” in this example), and not from a JRE installation. Violating these rules can result in problems that can be difficult to track down.

In the CLASSPATH line, the path to ctreeSQLSP.jar is relative to the “server” folder. On Linux, the entries in the CLASSPATH line should be separated with colons instead of semicolons.

The purpose of these three lines is to give the c-tree server the information it needs to compile and run Java source code. This is because the stored procedures demonstrated by this tutorial are written in Java.

If you are using FairCom RTG or FairCom Edge, adjust the path to match your product.

SQL Stored Procedures - Close cursors that were left open

In a rare situation involving a complex set of stored procedures, cursors could leak. In c-tree stored procedures, cursors need to be closed explicitly; if this is not done, the cursor is leaked.

Logic has been added to the function that starts stored procedures to identify cursors that are opened and not closed within a stored procedure and close any that are found. When Java debugging is turned on in TPESQLDBG, a message is logged in sqlserver.log to help in identifying the stored procedure and the statement that leaked the cursors.

Java Quick Tour

This QuickStart is composed of four simple tutorials designed to guide you through the basic use of the FairCom DB SQL Stored Procedures technology.

Tutorials for Java Stored Procedures can be found in the ReadMe titled FairCom Java Stored Procedures.

As with all other tutorials in the c-tree series, each of these database programming tutorials is implemented with four simple code procedures: Initialize(), Define(), Manage(), and Done().

No matter which FairCom interface language you use, FairCom follows this same high-level flow in all tutorials. This makes it easy for developers to "cross-over" from one language interface to another as these basic concepts apply to all.

	Initialize() Every language requires some form of initial "logon" or "connection" procedure to establish a session with the database. This is done in the Initialize() stage of the program.
	Define() Database definitions (DDL), Table/File schema definitions, Index definitions, Table/File creation, and Table/File open operations are all addressed in the Define() stage of the program.
	Manage() This stage of the program is where the database is operated on, as in managing your data. Adding/Reading/Updating/Deleting records/rows are handled in this stage of the program.
	Done() When the program ends, the database session should be closed. This stage handles the necessities to "de-init", by closing Tables/Files and issuing any required "logoff" or "disconnect" type procedures.

Presented here are tutorials that follow the "Initialize(), Define(), Manage(), and Done()" approach.

You can also view similar tutorials for all supported languages online.

Four SQL scripts have been included in the SQL Stored Procedures Tutorial directory, located in:

C:\<faircom>\win32\sdk\sql.stored.procs\tutorials (where C:\<faircom> is your installation directory. The default installation directory was C:\<faircom>).

The following is a conceptual overview of these tutorials:

Tutorial #1: Introductory - Simple Single Table

We wanted to keep this program as simple as possible. This program does the following:

• Initialize() - Connects to the FairCom DB database engine.
• Define() - Defines and creates a "customer master" (custmast) file/table.
• Manage() - Adds a few records/rows; Reads the records/rows back from the database and displays their content; and then deletes the records/rows.
• Done() - Disconnects from FairCom DB Database Engine.

Tutorial #2: Relational Model and Indexing

Here we now add a bit more complexity, introducing multiple tables, with related indices in order to form a simple "relational" database simulating an Order Entry system. Here is an overview of what will be created:

• Initialize() - Connects to the FairCom DB database engine.
• Define() - Defines and creates the "custmast", "custordr", "ordritem" and the "itemmast" files/tables with related indices.
• Manage() - Adds some related records/rows to all files/tables. Then queries this order database.
• Done() - Disconnects from FairCom DB Database Engine.

Tutorial #3: Locking

Here we demonstrate the enforcement of data integrity by introducing record/row "locking".

• Initialize() - Connects to the FairCom DB database engine.
• Define() - Defines and creates a "customer master" (custmast) file/table.
• Manage() - Adds a few records/rows; Reads the records/rows back from the database and displays their content. Then demonstrates an update operation under locking control, and a scenario that shows a locking conflict.
• Done() - Disconnects from FairCom DB database engine.

Tutorial #4: Transaction Processing

Here we demonstrate transaction control.

• Initialize() - Connects to the FairCom DB database engine.
• Define() - Defines and creates our four file/tables.
• Manage() - Adds records/rows to multiple tables under transaction control.
• Done() - Disconnects from FairCom DB Database Engine.

Learn More...

Try the tutorials in in the ReadMe titled FairCom Java Stored Procedures.

Throw Custom Error Message on Stored Procedure or UDF Exception

This modification provides the capability to return an error message from a stored procedure or user-defined function (UDF).

Before this modification, the only way for a stored procedure to set the return code to a value different than 0 was to throw a DhSQLException() either directly (only when using the NetBeans plugin to generate the stored procedure) by:

throw new DhSQLException(678,"my error message"); 

or through the DhSQLThrow class:

DhSQLThrow.throwSQLException(678,"my error message"); 

In both cases the error code returned was interpreted as a known SQL error code and the error message was set to the one for the specific error code returned, ignoring the message.

This modification adds a preferred way of returning an error message by calling the new fail(String message) method. This method causes the stored procedure to terminate with error -26015 and sets the error message to the string passed in. The message will be truncated if it is larger than 510 characters. For example:

 
ISQL> CREATE PROCEDURE  excp()
BEGIN
fail("my error message");
END
ISQL> call excp();
ISQL> error(-26015): my error message

Java Class Reference

Introduction

This chapter provides reference material on the FairCom DB SQL Java classes and methods. The following table lists all the methods in the FairCom DB SQL Java classes and shows which classes declare them. Following sections are arranged alphabetically and describe each class and its methods in more detail. Some methods are common to more than one class.

Methods in the FairCom DB SQL Java Classes

DhSQLException

Description

The DhSQLException class extends the general java.lang.Exception class to provide detail about errors in FairCom DB SQL statement execution. Any such errors raise an exception with an argument that is an SQLException class object. The getDiagnostics() method retrieves details of the error.

Constructors

public DhSQLException(int ecode, String errMsg)

Parameters

ecode

The error number associated with the exception condition.

errMsg

The error message associated with the exception condition.

The following example illustrates the DhSQLException() constructor to create an exception object called excep. It then throws the excep object under all conditions.

Example

CREATE PROCEDURE sp1_02()
BEGIN
     // raising exception
     DhSQLException excep = new DhSQLException(666,new String
                            ("Entered the sp1_02 procedure"));
     if (true)
     throw excep;
END

DhSQLException.getDiagnostics

Returns the requested detail about an exception.

Format

public String getDiagnostics(int diagType)

Returns

A string containing the information specified by the diagType parameter, as shown in the table below.

Parameters

diagType

One of the values shown in the following table.

Argument Values for DhSQLException.getDiagnostics

Throws

DhSQLException

Example

CREATE PROCEDURE test_proc()
BEGIN
   String errstate;
   String errmesg;
   try
   {
      SQLIStatement insert_cust = new SQLIStatement ( "INSERT
      INTO customer VALUES (1,2) ");
      insert_cust.execute();
   }
   catch (DhSQLException e)
   {
      errstate = e.getDiagnostics (
      DhSQLException.RETURNED_SQLSTATE) ;
      errmesg = e.getDiagnostics (
      DhSQLException.MESSAGE_TEXT) ;
   }
END

DhSQLException.getMessageText

Returns a textual description of the exception error.

Format

public String getMessageText()

Returns

A string containing the textual description of the exception.

Parameters

Throws

Example

CREATE PROCEDURE test_proc()
BEGIN
  Integer errstate;
  String errmesg;
  try
  {
     SQLIStatement insert_cust = new SQLIStatement ( "INSERT
     INTO customer VALUES (1,2) ");
     insert_cust.execute();
  }
  catch (DhSQLException e)
  {
     
     errstate = e.getSqlState();
     errmesg = e.getMessageText();
  }
END   

DhSQLException.getSqlState

Returns the SQL state as an integer error code.

Format

public Integer getSqlState()

Returns

An integer containing the SQL state (error code) of the exception.

Parameters

Throws

Example

CCREATE PROCEDURE test_proc()
BEGIN
  Integer errstate;
  String errmesg;
  try
  {
     SQLIStatement insert_cust = new SQLIStatement ( "INSERT
     INTO customer VALUES (1,2) ");
     insert_cust.execute();
  }
  catch (DhSQLException e)
  {
     errstate = e.getSqlState();
     errmesg = e.getMessageText() ;
  }
END  

DhSQLResultSet

Description

Methods of the DhSQLResultSet class populate a result set that the stored procedure returns to the application that called the procedure.

The Java code in a stored procedure does not explicitly create DhSQLResultSet objects. Instead, when FairCom DB SQL creates a Java class from a CREATE PROCEDURE statement that contains a RESULT clause, it implicitly instantiates an object of type DhSQLResultSet, and calls it SQLResultSet.

Procedures invoke methods of the SQLResultSet() instance to populate fields and rows of the result set.

Limit: DhSQLResultSet is limited to 50 columns.

Constructors

No explicit constructor

Parameters

None

Throws

DhSQLException

DhSQLResultSet.set

Sets the field in the currently-active row of a procedure’s result set to the specified value (a literal, procedure variable, or procedure input parameter).

Format

public void set(int field, Object val)

Returns

None

Parameters

field

An integer that specifies which field of the result-set row to set to the value specified by val (1 denotes the first field in the row, 2 denotes the second, and so on).

val

A literal or the name of a variable or input parameter that contains the value to be assigned to the field.

Throws

DhSQLException

Example

CREATE PROCEDURE get_sal2 ()
RESULT (
    empname CHAR(20),
    empsal   NUMERIC,
)
BEGIN
    StringBuffer ename = new StringBuffer (20) ;
    BigDecimal esal = new BigDecimal () ;
    SQLCursor empcursor = new SQLCursor (
        "SELECT name, sal FROM emp " ) ;
   
    empcursor.open () ;
    do
    {
        empcursor.fetch ();
        if (empcursor.found ())
        {
            ename = (StringBuffer)empcursor.getValue (1, CHAR);
            esal = (BigDecimal)empcursor.getValue (2, NUMERIC);

            SQLResultSet.set (1, ename);
            SQLResultSet.set (2, esal);
            SQLResultSet.insert ();
        }
    } while (empcursor.found ()) ;
    empcursor.close () ;
END

Note: The resultset parameter empname in the procedure above should be of type CHAR in ANSI versions of FairCom DB SQL.

DhSQLResultSet.makeNULL

Sets a field of the currently-active row in a procedure's result set to null. This method is redundant with using the DhSQLResultSet.set() method to set a procedure result-set field to null.

Format

public void makeNULL(int field)

Returns

None

Parameters

field

An integer that specifies which field of the result-set row to set to null (1 denotes the first field in the row, 2 denotes the second, and so on).

Throws

DhSQLException

Example

CREATE PROCEDURE test_makeNULL2(
    IN char_in CHAR(20))
RESULT ( res_char CHAR(20) , res_vchar VARCHAR(30))
BEGIN
    SQLResultSet.set(1,char_in);
    SQLResultSet.makeNULL(2);
END

Note: The input parameter char_in in the procedure above should be of type CHAR in ANSI versions of FairCom DB SQL.

DhSQLResultSet.insert

Inserts the currently-active row into a procedure’s result set.

Format

public void insert()

Returns

None

Parameters

None

Throws

DhSQLException

Example

CREATE PROCEDURE get_sal2 ()
RESULT (
    empname CHAR(20),
    empsal   NUMERIC,
)
IMPORT
import java.math.BigDecimal;
BEGIN
    StringBuffer ename = new StringBuffer (20) ;
    BigDecimal esal = new BigDecimal () ;
    SQLCursor empcursor = new SQLCursor (
        "SELECT name, sal FROM emp " ) ;
   
    empcursor.open () ;
    do
    {
        empcursor.fetch ();
        if (empcursor.found ())
        {
           ename = (String)empcursor.getValue (1, CHAR);
           esal = (BigDecimal)empcursor.getValue (2, NUMERIC);

            SQLResultSet.set (1, ename);
            SQLResultSet.set (2, esal);
            SQLResultSet.insert ();
        }
    } while (empcursor.found ()) ;
    empcursor.close () ;
END

 Note: The resultset parameter empname in the procedure above should be of type CHAR in ANSI versions of FairCom DB SQL.

SQLCursor

Description

Methods of the SQLCursor class retrieve rows of data from a database or another stored procedure’s result set.

Constructors

SQLCursor (String statement)

Parameters

statement

A FairCom DB SQL statement that generates a result set. Enclose the statement in double quotes. The FairCom DB SQL statement is either a SELECT or CALL statement:

• A SELECT statement queries the database and returns data that meets the criteria specified by the query expression in the SELECT statement.
• A CALL statement invokes another stored procedure that returns a result set specified by the RESULT clause of the CREATE PROCEDURE statement.

Throws

DhSQLException

The following excerpt from a stored procedure instantiates an SQLCursor object called cust_cursor that retrieves data from a database table.

Example

SQLCursor empcursor = new SQLCursor ("SELECT name, sal FROM emp " ) ;

The following excerpt from a stored procedure instantiates an SQLCursor object called cust_cursor that calls another stored procedure.

Example with Nested Stored Procedure

SQLCursor cust_cursor = new SQLCursor ("CALL get_customers (?) ") ;

SQLCursor.close

Closes the result set specified by the SELECT or CALL statement.

Format

public void close()

Returns

None

Parameters

None

Throws

DhSQLException

Example

.
.
.
        if (cust_cursor.Found ())
        {
                cust_cursor.getValue (1, INTEGER);
                cust_cursor.getValue (2, CHAR) ;
        }
        else
                break;
    }

    cust_cursor.close () ;

SQLCursor.fetch

Fetches the next record in a result set, if there is one.

Format

public void fetch()

Returns

None

Parameters

None

Throws

DhSQLException

Example

for (;;)
{
    cust_cursor.fetch ();
    if (cust_cursor.found ())
    {
            cust_cursor.getValue (1, INTEGER);
            cust_cursor.getValue (2, CHAR) ;
    }
    else
            break;
}

SQLCursor.found

Checks whether a fetch operation returned a record.

Format

public boolean found ()

Returns

True if the previous call to fetch() returned a record, false otherwise.

Parameters

None

Throws

DhSQLException

Example

for (;;)
{
    cust_cursor.fetch ();
    if (cust_cursor.found ())
    {
            cust_cursor.getValue (1, INTEGER);
            cust_cursor.getValue (2, CHAR) ;
    }
    else
            break;
}

SQLCursor.getLongValue

Retrieves a chunk of a LONG VARCHAR field (in case the field content is too large).

Format

public Object getLongValue(int field, int offset, int len)

Parameters

• field the field number
• offset the starting point (0 based) where the portion you want to retrieve starts.
• len the length of the portion to retrieve.

Returns

Returns NULL if there is no more to retrieve.

Throws

DhSQLException

SQLCursor.getParam

When a procedure is called from another procedure, this method returns the value of the specified OUT/INOUT parameter of the called procedure as a Java object. This returned object must be typecasted to the appropriate type. This method is common to the SQLCursor, SQLIStatement, and SQLPStatement classes.

Format

public Object getParam(int field, short dType)

Returns

Returns the Object specified by the field value.

Parameters

field

An integer that specifies which argument value of the called procedure is to be returned (1 denotes the first parameter, 2 denotes the second, and so on).

If the specified parameter does not exist, FairCom DB SQL returns an error:

(error(-20145): Invalid field reference.)

dType

The expected data type of the returning parameter.

Throws

DhSQLException

Example

CREATE PROCEDURE swap_proc(IN param1 INTEGER,
                           OUT param2 INTEGER,
                           INOUT param3 INTEGER
                          )
BEGIN
    param2 = param3;
    param3 = param1;
END

CREATE PROCEDURE call_swap_proc()
BEGIN
    Integer val1 = new Integer(10);
    Integer val2;
    Integer val3 = new Integer(20);
    SQLCursor tmp_cur = new SQLCursor("CALL swap_proc(?,?,?)");
    tmp_cur.registerOutParam(2, INTEGER);
    tmp_cur.registerOutParam(3, INTEGER);
    tmp_cur.setParam(1, val1);
    tmp_cur.setParam(3, val3);
    tmp_cur.open();
    val2 = (Integer)tmp_cur.getParam(2, INTEGER);
    val3 = (Integer)tmp_cur.getParam(3, INTEGER);
    // process the val2 and val3
- - -
tmp_cur.close();
END

SQLCursor.getValue

Returns the value of the specified field of the fetched row of a cursor or result set as a Java object. This returned object must be typecasted to an appropriate SQL type.

Format

public Object getValue(int field, short dType)

Returns

Returns the Object specified by the field value.

Parameters

field

An integer that specifies which field of the fetched record is of interest. getValue() retrieves the value in the currently-fetched record of the column denoted by field (1 denotes the first column of the result set, 2 denotes the second, and so on).

The value in the column denoted by field cannot be null, or FairCom DB SQL returns an error:

(error(-20144): Null value fetched.)

This means you must always check whether a value is null before attempting to assign a value in an SQL result set to a procedure variable or output parameter. The SQLCursor class provides the wasNULL() method for this purpose. See Section "SQLCursor.wasNULL" for details.

dType

The expected data type of the return Object being returned.

Throws

DhSQLException

Example

CREATE PROCEDURE test_nulls2()
RESULT (col1 INTEGER,
        col2 INTEGER,
        col3 INTEGER)
BEGIN
     Integer pvar_int1      = new Integer(0);
     Integer pvar_int2      = new Integer(0);
     Integer pvar_int3      = new Integer(0);
     SQLCursor select_t1 = new SQLCursor
     ( "SELECT c1, c2, c3 from t1" );
    
     select_t1.open();
     select_t1.fetch();
     while ( select_t1.found() )
     {
         // Assign values from the current row of the SQL result set
         // to the pvar_intx procedure variables. Must first check
         // whether the values fetched are null: if they are, must set
         // pvars explicitly to null.
      if ((select_t1.wasNULL(1)) == true)
            pvar_int1 = null;
      else
            pvar_int1 = (Integer)select_t1.getValue(1,INTEGER);
      if ((select_t1.wasNULL(2)) == true)
            pvar_int2 = null;
      else
            pvar_int2 = (Integer)select_t1.getValue(2,INTEGER);
      if ((select_t1.wasNULL(3)) == true)
            pvar_int3 = null;
      else
            pSQLResultSet.set(1,pvar_int1);
            SQLResultSet.set(2,pvar_int2);
            SQLResultSet.set(3,pvar_int3);
            SQLResultSet.insert();
            select_t1.fetch();
      }
      select_t1.close();
END

SQLCursor.makeNULL

Sets the value of a FairCom DB SQL statement’s input parameter to null. This method is common to the SQLCursor, SQLIStatement, and SQLPStatement classes. This method is redundant with using the setParam() method to set a statement’s input parameter to null.

Format

public void makeNULL(int f)

Returns

None

Parameters

f

An integer that specifies which input parameter of the FairCom DB SQL statement string to set to null (1 denotes the first input parameter in the statement, 2 denotes the second, and so on).

Throws

DhSQLException

Example

CREATE PROCEDURE sc_makeNULL()
BEGIN
     SQLCursor select_btypes = new SQLCursor (
         "SELECT small_fld from sfns where small_fld = ? ");

     select_btypes.makeNULL(1);
     select_btypes.open();
     select_btypes.fetch();
     select_btypes.close();

END

SQLCursor.open

Opens the result set specified by the SELECT or CALL statement.

Format

public void open()

Returns

None

Parameters

None

Throws

DhSQLException

Example

SQLCursor empcursor = new SQLCursor (
    "SELECT name, sal FROM emp " ) ;
    empcursor.open () ;

SQLCursor.registerOutParam

Registers the expected type of OUT and INOUT parameters. This method is common for SQLCursor, SQLIStatement, and SQLPStatement classes.

Format

public void registerOutParam(int pIndex, short dType)

Returns

None

Parameters

pindex

An integer that specifies which OUT/INOUT parameter is to be registered (1 denotes the first parameter, 2 denotes the second, and so on).

dtype

The expected type of the OUT/INOUT parameter.

Note: SQLCursor class has another form of registerOutParam(), which takes three arguments. The syntax of this method is:

public void registerOutParam(int pIndex, short dType, short scale)

pIndex and dType are the same as the two arguments in SQLCursor.registerOutParam() method. The scale specifies the number of digits after the decimal point.
This method is not implemented in FairCom DB SQL and invocation of this method results in error:

“Scale for registerOutParam not implemented”.

Throws

DhSQLException

Example

   CREATE PROCEDURE register_proc()
BEGIN
    // cust_proc is a procedure with an IN, OUT and an INOUT arguments of type
    // integer, string and numeric respectively.
    SQLCursor cust_cur = new SQLCursor("call cust_proc(?,?,?)");
    cust_cur.registerOutParam(2, CHAR);
    cust_cur.registerOutParam(3, NUMERIC);
    cust_cur.open();
    // Process results
cust_cur.close();
END

SQLCursor.rowCount

Returns the number of rows affected (selected, inserted, updated, or deleted) by the FairCom DB SQL statement. This method is common to the SQLCursor, SQLIStatement, and SQLPStatement classes.

Format

public int rowCount()

Returns

An integer indicating the number of rows.

Parameters

None

Throws

DhSQLException

The following example uses the rowCount() method of the SQLIStatement class. It nests the method invocation within SQLResultSet.set() to store the number of rows affected (1, in this case) in the procedure’s result set.

Example

CREATE PROCEDURE sis_rowCount()
RESULT ( ins_recs BIGINT )
BEGIN
     SQLIStatement insert_test103 = new SQLIStatement (
     "INSERT INTO test103 (fld1) values (17)");

     insert_test103.execute();
     SQLResultSet.set(1,new Long(insert_test103.rowCount()));
     SQLResultSet.insert();
END

SQLCursor.setParam

Sets the value of a FairCom DB SQL statement’s input parameter to the specified value (a literal, procedure variable, or procedure input parameter). This method is common to the SQLCursor, SQLIStatement, and SQLPStatement classes.

Format

public void setParam(int f, Object val)

Returns

None

Parameters

f

An integer that specifies which parameter marker in the FairCom DB SQL statement is to receive the value (1 denotes the first parameter marker, 2 denotes the second, and so on).

val

A literal or the name of a variable or input parameter that contains the value to be assigned to the parameter marker.

Throws

DhSQLException

Example

CREATE PROCEDURE sps_setParam()
BEGIN
     // Assign local variables to be used as SQL input parameter references
     Integer ins_fld_ref   = new Integer(1);
     Integer ins_small_fld = new Integer(3200);
     Integer ins_int_fld   = new Integer(21474);
     Double  ins_doub_fld  = new Double(1.797E+30);
     StringBuffer  ins_char_fld  = new StringBuffer("Athula");
     StringBuffer  ins_vchar_fld = new StringBuffer("Scientist");
     Float   ins_real_fld  = new Float(17);

     SQLPStatement insert_sfns1 = new SQLPStatement ("INSERT INTO sfns"
              + "(fld_ref,small_fld,int_fld,doub_fld,char_fld,vchar_fld)"
              + "values (?,?,?,?,?,?)" );

     insert_sfns1.setParam(1,ins_fld_ref);
     insert_sfns1.setParam(2,ins_small_fld);
     insert_sfns1.setParam(3,ins_int_fld);
     insert_sfns1.setParam(4,ins_doub_fld);
     insert_sfns1.setParam(5,ins_char_fld);
     insert_sfns1.setParam(6,ins_vchar_fld);
     insert_sfns1.execute();
END

SQLCursor.wasNULL

Checks if the value in a fetched field is null.

Format

public boolean wasNULL(int field)

Returns

True if the field is null, false otherwise.

Parameters

field

An integer that specifies which field of the fetched record is of interest (1 denotes the first column of the result set, 2 denotes the second, and so on). wasNULL() checks whether the value in the currently-fetched record of the column denoted by field is null.

Throws

DhSQLException

Example

CREATE PROCEDURE test_wasNULL()
BEGIN
     int small_sp = new Integer(0);
     SQLCursor select_btypes = new SQLCursor ("SELECT small_fld from sfns");
     select_btypes.open();
     select_btypes.fetch();
     if ((select_btypes.wasNULL(1)) == true)
            small_sp = null;
     else
            select_btypes.getValue(1,INTEGER);
     select_btypes.close();
END

SQLIStatement

Description

Methods of the SQLIStatement class provide for immediate (one-time) execution of FairCom DB SQL statements that do not generate a result set.

Note: SQLIStatement can be used to call stored procedures that do not have result sets.

Constructors

SQLIStatement (String statement)

Parameters

statement

A FairCom DB SQL statement that does not generate a result set. Enclose the statement in double quotes.

Throws

DhSQLException

Example

CREATE PROCEDURE insert_customer (
   IN  cust_number INTEGER,
   IN  cust_name   CHAR(20)
)
BEGIN
    SQLIStatement insert_cust = new SQLIStatement
           ("INSERT INTO customer VALUES (?,?) ");
    insert_cust.execute();
END

 Note: The input parameter cust_name in the procedure above should be of type CHAR in ANSI versions of FairCom DB SQL.

SQLIStatement.execute

Executes the FairCom DB SQL statement. This method is common to the SQLIStatement and SQLPStatement classes.

Format

public void execute()

Returns

None

Parameters

None

Throws

DhSQLException

Example

CREATE PROCEDURE insert_customer (
   IN  cust_number INTEGER,
   IN  cust_name   CHAR(20)
)
BEGIN
    SQLIStatement insert_cust = new SQLIStatement (
       "INSERT INTO customer VALUES (?,?) ");
    insert_cust.setParam (1, cust_number);
    insert_cust.setParam (2, cust_name);
    insert_cust.execute ();
END

SQLIStatement.getParam

When a procedure is called from another procedure, this method returns the value of the specified OUT/INOUT parameter of called procedure as a Java object. This returned object must be typecasted to the appropriate type.

Format

public Object getParam(int field, short dType)

Returns

Returns the Object specified by the field value.

Parameters

field

An integer that specifies which argument value of the called procedure is to be returned (1 denotes the first parameter, 2 denotes the second, and so on).

If the specified parameter does not exists, FairCom DB SQL returns an error:

(error(-20145): Invalid field reference.)

dType

The expected data type of the returning parameter.

Throws

DhSQLException

Example

CREATE PROCEDURE swap_proc(IN param1 INTEGER,
                           OUT param2 INTEGER,
                           INOUT param3 INTEGER
                          )
BEGIN
    param2 = param3;
    param3 = param1;
END

CREATE PROCEDURE call_swap_proc()
BEGIN
    Integer val1 = new Integer(10);
    Integer val2;
    Integer val3 = new Integer(20);
    SQLIStatement istmt = new SQLIStatement("CALL swap_proc(?,?,?)");
    istmt.registerOutParam(2, INTEGER);
    istmt.registerOutParam(3, INTEGER);
    istmt.setParam(1, val1);
    istmt.setParam(3, val3);
    istmt.execute();
    val2 = (Integer)istmt.getParam(2, INTEGER);
    val3 = (Integer)istmt.getParam(3, INTEGER);
    // process the val2 and val3
END

SQLIStatement.makeNULL

Sets the value of a FairCom DB SQL statement’s input parameter to null. This method is common to the SQLCursor, SQLIStatement, and SQLPStatement classes. This method is redundant with using the setParam() method to set a statement’s input parameter to null.

Format

public void makeNULL(int f)

Returns

None

Parameters

f

An integer that specifies which input parameter of the FairCom DB SQL statement string to set to null (1 denotes the first input parameter in the statement, 2 denotes the second, and so on).

Throws

DhSQLException

Example

CREATE PROCEDURE sis_makeNULL()
BEGIN
     SQLIStatement insert_sfns1 = new SQLIStatement ("INSERT INTO sfns"
     + "(fld_ref,small_fld,int_fld,doub_fld,char_fld,vchar_fld)"
     + "values (?,?,?,?,?,?)");
     insert_sfns1.setParam(1,new Integer(66));
     insert_sfns1.makeNULL(2);
     insert_sfns1.makeNULL(3);
     insert_sfns1.makeNULL(4);
     insert_sfns1.makeNULL(5);
     insert_sfns1.makeNULL(6);
     insert_sfns1.execute();
END

SQLIStatement.registerOutParam

This method registers the expected type of OUT and INOUT parameters. This method is common for SQLCursor, SQLIStatement, and SQLPStatement classes.

Format

public void registerOutParam(int pIndex, short dType)

Returns

None

Parameters

pIndex

An integer that specifies which OUT/INOUT parameter is to be registered (1 denotes the first parameter, 2 denotes the second, and so on).

dType

The expected type of the OUT/INOUT parameter.

Throws

DhSQLException

Example

CREATE PROCEDURE register_proc()
BEGIN
    // cust_proc is a procedure with an IN, OUT and an INOUT arguments of type
    // integer, string and numeric respectively.
    SQLIStatement istmt = new SQLIStatement("call cust_proc(?,?,?)");
    istmt.registerOutParam(2, CHAR);
    istmt.registerOutParam(3, NUMERIC);
    istmt.execute();
    // Process results
END

SQLIStatement.rowCount

Returns the number of rows affected (selected, inserted, updated, or deleted) by the FairCom DB SQL statement. This method is common to the SQLCursor, SQLIStatement, and SQLPStatement classes.

Format

public int rowCount()

Returns

An integer indicating the number of rows.

Parameters

None

Throws

DhSQLException

The following example uses the rowCount() method of the SQLIStatement class. It nests the method invocation within SQLResultSet.set() to store the number of rows affected (1, in this case) in the procedure’s result set.

Example

CREATE PROCEDURE sis_rowCount()
RESULT ( ins_recs BIGINT )
BEGIN
     SQLIStatement insert_test103 = new SQLIStatement (
     "INSERT INTO test103 (fld1) values (17)");

     insert_test103.execute();
     SQLResultSet.set(1,new Long(insert_test103.rowCount()));
     SQLResultSet.insert();
END

SQLIStatement.setParam

Sets the value of a FairCom DB SQL statement’s input parameter to the specified value (a literal, procedure variable, or procedure input parameter). This method is common to the SQLCursor, SQLIStatement, and SQLPStatement classes.

Format

public void setParam(int f, Object val)

Returns

None

Parameters

• f An integer that specifies which parameter marker in the FairCom DB SQL statement is to receive the value (1 denotes the first parameter marker, 2 denotes the second, and so on).
• val A literal or the name of a variable or input parameter that contains the value to be assigned to the parameter marker.

Throws

DhSQLException

Example

CREATE PROCEDURE sps_setParam()
BEGIN
     // Assign local variables to be used as SQL input parameter references
     Integer ins_fld_ref   = new Integer(1);
     Integer ins_small_fld = new Integer(3200);
     Integer ins_int_fld   = new Integer(21474);
     Double  ins_doub_fld  = new Double(1.797E+30);
     StringBuffer  ins_char_fld  = new StringBuffer("Athula");
     StringBuffer  ins_vchar_fld = new StringBuffer("Scientist");
     Float   ins_real_fld  = new Float(17);

     SQLPStatement insert_sfns1 = new SQLPStatement ("INSERT INTO sfns"
     + "(fld_ref,small_fld,int_fld,doub_fld,char_fld,vchar_fld)"
     + "values (?,?,?,?,?,?)" );

     insert_sfns1.setParam(1,ins_fld_ref);
     insert_sfns1.setParam(2,ins_small_fld);
     insert_sfns1.setParam(3,ins_int_fld);
     insert_sfns1.setParam(4,ins_doub_fld);
     insert_sfns1.setParam(5,ins_char_fld);
     insert_sfns1.setParam(6,ins_vchar_fld);
     insert_sfns1.execute();
END

SQLPStatement

Description

Methods of the SQLPStatement class provide for prepared (repeated) execution of c-treeSQL statements that do not generate a result set.

Note: SQLPStatement can be used to call stored procedures that do not have result sets.

Constructors

SQLPStatement (String statement)

Parameters

statement

A c-treeSQL statement that does not generate a result set. Enclose the statement in double quotes.

Throws

DhSQLException

Example

SQLPStatement pstmt = new SQLPStatement (
    "INSERT INTO T1 VALUES (?, ?) ") ;
.
.
.

SQLPStatement.execute

Executes the FairCom DB SQL statement. This method is common to the SQLIStatement and SQLPStatement classes.

Format

public void execute()

Returns

None

Parameters

None

Throws

DhSQLException

Example

CREATE PROCEDURE test_pstmt()
BEGIN
   SQLPStatement pstmt = new SQLPStatement ( "INSERT INTO T1 VALUES (?, ?) ") ;
   pstmt.setParam (1, new Integer(10));
   pstmt.setParam (2, new Integer(10));
   pstmt.execute ();
   pstmt.setParam (1, new Integer(20));
   pstmt.setParam (2, new Integer(20));
   pstmt.execute ();
END

SQLPStatement.getParam

When a procedure is called from another procedure, this method returns the value of the specified OUT/INOUT parameter of the called procedure as a Java object. This returned object must be typecasted to the appropriate type.

Format

public Object getParam(int field, short dType)

Returns

Returns the Object specified by the field value.

Parameters

field

An integer that specifies which argument value of the called procedure is to be returned (1 denotes the first parameter, 2 denotes the second, and so on).

If the specified parameter does not exists, FairCom DB SQL returns an error:

(error(-20145): Invalid field reference.)

dType

The expected data type of the returning parameter.

Throws

DhSQLException

Example

CREATE PROCEDURE swap_proc(IN param1 INTEGER,
                           OUT param2 INTEGER,
                           INOUT param3 INTEGER)
BEGIN
    param2 = param3;
    param3 = param1;
END

CREATE PROCEDURE call_swap_proc()
BEGIN
    Integer val1 = new Integer(10);
    Integer val2;
    Integer val3 = new Integer(20);
    SQLPStatement pstmt = new SQLPStatement("CALL swap_proc(?,?,?)");
    pstmt.registerOutParam(2, INTEGER);
    pstmt.registerOutParam(3, INTEGER);
    pstmt.setParam(1, val1);
    pstmt.setParam(3, val3);
    pstmt.execute();
    val2 = (Integer)pstmt.getParam(2, INTEGER);
    val3 = (Integer)pstmt.getParam(3, INTEGER);
    // process the val2 and val3
END

SQLPStatement.makeNULL

Sets the value of a FairCom DB SQL statement’s input parameter to null. This method is common to the SQLCursor, SQLIStatement, and SQLPStatement classes. This method is redundant with using the setParam() method to set a statement’s input parameter to null.

Format

public void makeNULL(int f)

Returns

None

Parameters

f

An integer that specifies which input parameter of the FairCom DB SQL statement string to set to null (1 denotes the first input parameter in the statement, 2 denotes the second, and so on).

Throws

DhSQLException

Example

CREATE PROCEDURE sps_makeNULL()
BEGIN
     SQLPStatement insert_sfns1 = new SQLPStatement ("INSERT INTO sfns"
      + "(fld_ref,small_fld,int_fld,doub_fld,char_fld,vchar_fld)"
      + "values (?,?,?,?,?,?)" );
     insert_sfns1.setParam(1,new Integer(666));
     insert_sfns1.makeNULL(2);
     insert_sfns1.makeNULL(3);
     insert_sfns1.makeNULL(4);
     insert_sfns1.makeNULL(5);
     insert_sfns1.makeNULL(6);
     insert_sfns1.execute();
END

SQLPStatement.registerOutParam

This method registers the expected Type of OUT and INOUT parameters. This method is common for SQLCursor, SQLIStatement, and SQLPStatement classes.

Format

public void registerOutParam(int pIndex, short dType)

Returns

None

Parameters

pIndex

An integer that specifies which OUT/INOUT parameter is to be registered (1 denotes the first parameter, 2 denotes the second, and so on).

dType

The expected type of the OUT/INOUT parameter.

Throws

DhSQLException

Example

CREATE PROCEDURE register_proc()
BEGIN
    // cust_proc is a procedure with an IN, OUT and an INOUT arguments of types
    // integer, string and numeric respectively.
    SQLPStatement pstmt = new SQLPStatement("call cust_proc(?,?,?)");
    pstmt.registerOutParam(2, CHAR);
    pstmt.registerOutParam(3, NUMERIC);
    pstmt.execute();
    // Process results
END

SQLPStatement.rowCount

Returns the number of rows affected (selected, inserted, updated, or deleted) by the SQL statement. This method is common to the SQLCursor, SQLIStatement, and SQLPStatement classes.

Format

public int rowCount()

Returns

An integer indicating the number of rows.

Parameters

None

Throws

DhSQLException

This example uses the rowCount() method of the SQLIStatement class. It nests the method invocation within SQLResultSet.set() to store the number of rows affected (1, in this case) in the procedure’s result set.

Example

CREATE PROCEDURE sis_rowCount()
RESULT ( ins_recs BIGINT )
BEGIN
     SQLIStatement insert_test103 = new SQLIStatement (
     "INSERT INTO test103 (fld1) values (17)");

     insert_test103.execute();
     SQLResultSet.set(1,new Long(insert_test103.rowCount()));
     SQLResultSet.insert();
END

SQLPStatement.setParam

Sets the value of a FairCom DB SQL statement’s input parameter to the specified value (a literal, procedure variable, or procedure input parameter). This method is common to the SQLCursor, SQLIStatement, and SQLPStatement classes.

Format

public void setParam(int f, Object val)

Returns

None

Parameters

f

An integer that specifies which parameter marker in the FairCom DB SQL statement is to receive the value (1 denotes the first parameter marker, 2 denotes the second, and so on).

val

A literal or the name of a variable or input parameter that contains the value to be assigned to the parameter marker.

Throws

DhSQLException

Example

CREATE PROCEDURE sps_setParam()
BEGIN
     // Assign local variables to be used as SQL input parameter references
     Integer ins_fld_ref   = new Integer(1);
     Integer ins_small_fld = new Integer(3200);
     Integer ins_int_fld   = new Integer(21474);
     Double  ins_doub_fld  = new Double(1.797E+30);
     StringBuffer  ins_char_fld  = new StringBuffer("Athula");
     StringBuffer  ins_vchar_fld = new StringBuffer("Scientist");
     Float   ins_real_fld  = new Float(17);

     SQLPStatement insert_sfns1 = new SQLPStatement ("INSERT INTO sfns"
      + "(fld_ref,small_fld,int_fld,doub_fld,char_fld,vchar_fld)"
      + "values (?,?,?,?,?,?)" );

     insert_sfns1.setParam(1,ins_fld_ref);
     insert_sfns1.setParam(2,ins_small_fld);
     insert_sfns1.setParam(3,ins_int_fld);
     insert_sfns1.setParam(4,ins_doub_fld);
     insert_sfns1.setParam(5,ins_char_fld);
     insert_sfns1.setParam(6,ins_vchar_fld);
     insert_sfns1.execute();
END

Using FairCom DB SQL Java Classes

This section describes how to use included FairCom DB SQL Java classes to issue and process FairCom DB SQL statements in Java stored procedures. FairCom DB SQL Java classes are found in the ctreeSQLSP.jar file located in the <faircom>\server\classes directory of your FairCom DB installation.

To process FairCom DB SQL statements in a stored procedure, you need to know whether the SQL statement generates results (in other words, if the statement is a query) or not. SELECT statements, for example, generate results: they retrieve data from one or more database tables and return the results as rows in a table.

Whether a statement generates such an SQL result set dictates which FairCom DB SQL Java classes you use to issue it:

• To issue FairCom DB SQL statements that do not generate results (such as INSERT, GRANT, or CREATE), use either the SQLIStatement class (for one-time execution) or the SQLPStatement class (for repeated execution).
• To issue FairCom DB SQL statements that generate results (SELECT and, in some cases, CALL), use the SQLCursor class to retrieve rows from a database or another procedure’s result set.

In either case, if you want to return a result set to the application, use the DhSQLResultSet class to store rows of data in a procedure result set. You must use DhSQLResultSet() methods to transfer data from an SQL result set to the procedure result set for the calling application to process it. You can also use DhSQLResultSet() methods to store rows of data generated internally by the procedure.

In addition, FairCom DB SQL provides the DhSQLException class so procedures can process and generate Java exceptions through the standard Java try { }, catch { }, and throw { } constructs.

Passing Values to SQL Statements

Stored procedures need to be able to pass and receive values from FairCom DB SQL statements they execute. They do this through the setParam() and getValue() methods.

The setParam Method: Pass Input Values to SQL Statements

The setParam() method sets the value of a FairCom DB SQL statement’s parameter marker to the specified value (a literal, a procedure variable, or a procedure input parameter).

The setParam() method takes two arguments:

setParam ( marker_num , value ) ;

• marker_num is an integer that specifies which parameter marker in the SQL statement is to receive the value (1 denotes the first parameter marker, 2 denotes the second, and so on).
• value is a literal or the name of a variable or input parameter that contains the value to be assigned to the parameter marker.

The following example shows an excerpt from a stored procedure that uses setParam() to assign values from two procedure variables to the parameter markers in an SQL INSERT statement. When the procedure executes, it substitutes the value of the cust_number procedure variable for the first parameter marker and the value of the cust_name variable for the second parameter marker.

setParam() and FairCom DB SQL Statement Input Parameters

        SQLIStatement insert_cust = new SQLIStatement (
            "INSERT INTO customer VALUES (?,?) ");

        insert_cust.setParam (1, cust_number);
        insert_cust.setParam (2, cust_name);
.
.
.

The getValue Method: Pass Values from SQL Result Sets to Variables

The getValue() method of the SQLCursor class assigns a single value from an SQL result set (returned by an SQL query or another stored procedure) to a procedure variable or output parameter.

The format and arguments for getValue() are as follows:

getValue ( col_num , data_type ) ;

The getValue() interface returns an object, it should be explicitly typecasted to appropriate type.

• col_num is an integer that specifies which column of the result set is of interest. getValue() retrieves the value in the currently-fetched record of the column denoted by col_num. (1 denotes the first column of the result set, 2 denotes the second, and so on).
• data_type specifies the expected FairCom DB SQL type of the returned parameter (see Implicit Data Type Conversion Between SQL and Java Types for details on how FairCom DB SQL data types map to Java data types).

The following example shows an excerpt from a stored procedure that uses getValue() to assign values from two result-set columns to procedure variables. In this example, the result set is generated by a FairCom DB SQL SELECT statement.

Using getValue() to Pass Values from Result Sets

    StringBuffer ename = new StringBuffer (20) ;
    java.math.BigDecimal esal = new java.math.BigDecimal () ;

    SQLCursor empcursor = new SQLCursor (
        "SELECT name, sal FROM emp " ) ;
   
    empcursor.open () ;
        empcursor.fetch ();
        if (empcursor.found ())
        {
            ename = (StringBuffer)empcursor.getValue (1, CHAR);
            esal = (BigDecimal)empcursor.getValue (2, DECIMAL);
.
.
.

In the SELECT statement in previous example, it was clear that the result set had two columns, name and sal. If the SELECT statement had used a wildcard in its select list (SELECT * FROM EMP) you have to know the structure of the EMP table in order to correctly specify the column numbers in the getValue() method.

Passing Values to and From Stored Procedures: Input and Output Parameters

Applications need to pass and receive values from the stored procedures they call. They do this through input and output parameters declared in the procedure specification.

Applications can pass and receive values from stored procedures using input and output parameters declared in the stored procedure specification. When it processes the CREATE PROCEDURE statement, FairCom DB SQLFairCom DB SQL declares Java variables of the same name. This means the body of the stored procedure can refer to input and output parameters as if they were Java variables declared in the body of the stored procedure.

Note: Procedure result sets are another way for applications to receive output values from a stored procedure. Procedure result sets provide output in a row-oriented tabular format. See Returning a Procedure Result Set: the RESULT Clause and DhSQLResultSet.

Parameter declarations include the parameter type (IN, OUT, or INOUT), the parameter name, and c-treeSQL data type (see Implicit Data Type Conversion Between SQL and Java Types for details of how FairCom DB SQL data types map to Java data types).

Declare input and output parameters in the specification section of a stored procedure, as shown in the following example.

Example Input, Output, and Input-Output Parameters

CREATE PROCEDURE order_entry (
    IN  cust_name    CHAR(20),
    IN  item_num     INTEGER,
    IN  quantity     INTEGER,
    OUT status_code  INTEGER,
    INOUT order_num  INTEGER
)
.
.
.

 Note: The input parameter cust_name in the procedure above should be of type CHAR in ANSI versions of FairCom DB SQL.

When the order_entry stored procedure executes, the calling application passes values for the cust_name, item_num, quantity, and order_num input parameters. The body of the procedure refers to them as Java variables. Similarly, Java code in the body of order_entry processes and returns values in the status_code and order_num output parameters. The variable order_num can be used for both passing input and receiving output value from the procedure.

Note: Procedure parameter names should always be lower case as parameter names are treated as identifiers in the FairCom DB SQL statement and are converted to lowercase by FairCom DB SQL. Parameter names used within the procedure body are not converted to lower case. Hence declaring parameters with upper case may lead to compilation errors.

Implicit Data Type Conversion Between SQL and Java Types

When FairCom DB SQL creates a stored procedure, it converts the type of any input and output parameters (see The setParam Method: Pass Input Values to SQL Statements) from the FairCom DB SQL data types in the procedure specification to Java wrapper types.

The Java.lang package defines classes for all the primitive Java types that “wrap” values of the corresponding primitive type in an object. FairCom DB SQL converts the FairCom DB SQL data types declared for input and output parameters to one of these wrapper types, described in the table below.

You must be sure to use wrapper types when declaring procedure variables to use as arguments to the getValue(), setParam(), and set methods. These methods take objects as arguments and will generate compilation errors if you pass a primitive type to them.

The following example shows two stored procedures. The first tries to use a variable declared as the Java int primitive type as an argument to the SQLResultSet.set() method, and will generate a compilation error. The second correctly declares the variable using the integer wrapper class.

Using Wrapper Types as Arguments to FairCom DB SQL Classes

CREATE PROCEDURE type_mismatch( )
RESULT (res_int INTEGER )
BEGIN
     // Create a variable as (primitive) type int
     // to test the working of set method of SQLResultSet
     int pvar_int = 4;

     // Transfer the value from the procedure variable to the result set.
     SQLResultSet.set(1,pvar_int); //Error!

     // Insert the row into the procedure result set.
     SQLResultSet.insert();
END
CREATE PROCEDURE type_okmatch( )
RESULT (res_int INTEGER )
BEGIN
     // Create a variable as (wrapper) type Integer
     Integer pvar_int = new Integer(4);

     // Transfer the value from the procedure variable to the result set.
     SQLResultSet.set(1,pvar_int); //Success!

     // Insert the row into the procedure result set.
     SQLResultSet.insert();
END

 When the SQL engine submits the Java class it creates from the stored procedure to the Java compiler, the compiler checks for data-type consistency between the converted parameters and variables you declare in the body of the stored procedure.

To avoid type mismatch errors, use the data-type mappings shown in the following table for declaring parameters and result-set fields in the procedure specification and the Java variables in the procedure body.
 

Executing an SQL Statement

If a FairCom DB SQL statement does not generate a result set, stored procedures can execute it in one of two ways:

• Immediate execution, using methods of the SQLIStatement class, executes a statement once.
• Prepared execution, using methods of the SQLPStatement class, prepares a statement so you can execute it multiple times in a procedure loop.

Both SQLIStatement and SQLPStatement classes can be used to call stored procedures that do not have a RESULT clause (that is, the JSPs that do not return any resultset). Note that values of OUT and INOUT arguments can be retrieved using the getParam() method of SQLIStatement, SQLPStatement, and SQLCursor.

The following table shows the SQL statements that do not generate result sets. You can execute these statements in a stored procedure using either the SQLIStatement or SQLPStatement class.

Executable SQL Statements

Immediate Execution

Use immediate execution when a procedure needs to execute an SQL statement only once. The following example shows an instance of immediate execution.

Immediate Execution Example

CREATE PROCEDURE insert_customer (
   IN  cust_number INTEGER,
   IN  cust_name   CHAR(20)
)
BEGIN
    SQLIStatement insert_cust = new SQLIStatement (
       "INSERT INTO customer VALUES (?,?) ");
    insert_cust.setParam (1, cust_number);
    insert_cust.setParam (2, cust_name);
    insert_cust.execute ();
END

 Note: The input parameter cust_name in the procedure above should be of type CHAR in ANSI versions of FairCom DB SQL.

This example inserts a row in a table. The constructor for SQLIStatement() takes the FairCom DB SQL INSERT statement as its only argument. In this example, the statement includes two parameter markers.

Prepared Execution

Use prepared execution when you need to execute the same SQL statement repeatedly. Prepared execution avoids the overhead of creating multiple SQLIStatement objects for a single statement.

The advantage of prepared execution comes when you have the same FairCom DB SQL statement executed from within a loop. Instead of creating an object during each pass through the loop, prepared execution creates an object once and only passes input parameters for each execution of the statement.

Once a stored procedure creates a SQLPStatement object, it can execute it multiple times, supplying different values for each execution.

The following example extends the previous example to use prepared execution.

Prepared Execution Example

CREATE PROCEDURE prepared_insert_customer ()
IMPORT
import java.math.*;
BEGIN
    SQLPStatement p_insert_cust = new SQLPStatement (
       "INSERT INTO customer VALUES (?,?) ");
    int i;
    String [] [] new_custs ={
       {"01","ABC"},
       {"02","PQR"},
       {"03","XYZ"}
    };
    for (i=0; i<new_custs.length; i++)
       {
        p_insert_cust.setParam (1, Integer.valueOf (new_custs[i] [0]));
        p_insert_cust.setParam (2, new_custs[i] [1]);
        p_insert_cust.execute ();
       }
END

Retrieving Data: the SQLCursor Class

Methods of the SQLCursor class let stored procedures retrieve rows of data.

When stored procedures create an object from the SQLCursor class, they pass as an argument a FairCom DB SQL statement that generates a result set. The FairCom DB SQL statement is either a SELECT or CALL statement:

• A SELECT statement queries the database and returns data that meets the criteria specified by the query expression in the SELECT statement.
• A CALL statement invokes another stored procedure that returns a result set specified by the RESULT clause of the CREATE PROCEDURE statement.

Either way, once the procedure creates an object from the SQLCursor class, the processing of results sets follows the same steps:

1. Open the cursor with the SQLCursor.open() method
2. Check whether there are any records in the result set with the SQLCursor.found() method
3. If there are records in the result set, loop through the result set:
   • Try to fetch a record with the SQLCursor.fetch() method
   • Check whether the fetch returned a record with the SQLCursor.found() method
   • If the fetch operation returned a record, assign values from the result-set record’s fields to procedure variables or procedure output parameters with the SQLCursor.getValue() method
   • Process the data in some manner
   • If the fetch operation did not return a record, exit the loop
4. Close the cursor with the SQLCursor.close() method

The following example shows an example that uses SQLCursor() to process the result set returned by a FairCom DB SQL SELECT statement.

Processing a Result Set from a SELECT Statement

CREATE PROCEDURE get_sal ()
IMPORT
import java.math.BigDecimal;
BEGIN
    StringBuffer ename = new StringBuffer (20) ;
    BigDecimal esal = new BigDecimal () ;

    SQLCursor empcursor = new SQLCursor (
        "SELECT name, sal FROM emp " ) ;
   
    empcursor.open () ;
    empcursor.fetch ();
        while (empcursor.found ())
        {
            ename = (StringBuffer)empcursor.getValue (1, CHAR);
            esal = (BigDecimal)empcursor.getValue (2, DECIMAL);
            empcursor.fetch();
        }
    empcursor.close () ;
END

 Stored procedures also use SQLCursor objects to process a result set returned by another stored procedure. Instead of a SELECT statement, the SQLCursor() constructor includes a CALL statement that invokes the desired procedure.

The following example shows an excerpt from a stored procedure that processes the result set returned by another procedure, get_customers() customers which takes dept_no as input and returns two columns cust_number and cust_name as its result set.

Processing a Result Set from a CALL Statement

CREATE PROCEDURE get_customer()
IMPORT
import java.math.BigDecimal;
BEGIN
   SQLCursor cust_cursor = new SQLCursor ("CALL get_customers (?) ") ;
   Integer cust_number;
   StringBuffer cust_name;
   cust_cursor.setParam (1, new Integer(10));
   cust_cursor.open ();
   cust_cursor.fetch();
   while (cust_cursor.found())
      {
         cust_number = (Integer)cust_cursor.getValue (1, INTEGER);
         cust_name = (Stringz)cust_cursor.getValue (2, CHAR);
         cust_cursor.fetch ();
      }
   cust_cursor.close () ;
END

registerOutParam Method: registering the Type of OUT and INOUT Variables

Before executing a stored procedure call, you must explicitly call registerOutParam() to register the type of each OUT and INOUT parameter.

The registerOutParam() method of the SQLCursor class registers the type of OUT and INOUT parameters.

Syntax

The format and arguments for the registerOutParam() method are as follows:

registerOutParam( param_num , data_type ) ;

• param_num - An integer that specifies which parameter of the called procedure is to be registered. (1 denotes the first parameter, 2 denotes the second, and so on).
• data_type - Specifies the required SQL type of the returned parameter (see "Implicit Data Type Conversion Between SQL and Java Types" details of how SQL data types map to Java data types).

Limit: The number of arguments is limited to 50.

The following example shows an excerpt from a stored procedure that uses registerOutParam() interface to register the OUT and INOUT parameters.

registerOutParam() Interface Example

CREATE PROCEDURE get_sal (IN cust_num INTEGER,
                          OUT dept_num INTEGER,
                          INOUT salary DOUBLE PRECISION
                         )
BEGIN
    Integer dept = new Integer(40);
    Double incr = 0.25;

// select department number into dept using cust_num.
    dept_num = dept;
    salary = new Double(salary.doubleValue() + (salary.doubleValue) *
                        incr.double_Value());
END

CREATE PROCEDURE call_get_sal()
BEGIN
    Integer num = new Integer(54);
    Double sal = new Double(25000.00);
SQLCursor cust_cur = new SQLCursor("call get_sal(?,?,?)");
    cust_cur.setParam(1,num);
    cust_cur.registerOutParam(2,INTEGER);
    cust_cur.registerOutParam(3,DOUBLE);
    cust_cur.setParam(3,sal);
    cust_cur.open();

// process the data
    cust_cur.close();
END

If all the OUT and INOUT parameters are not registered, then sql engine returns an error: error(-20161): Not all OUT/INOUT parameters are registered.

Returning a Procedure Result Set with SQLResultSet

The get_sal() procedure in the previous example used the SQLCursor.getValue() method to store the values of a database record in individual variables. But the procedure did not do anything with those values, and they would be overwritten in the next iteration of the loop that fetches records. The RESULT section defines the columns

SQLResultSet derives from a lower DhSQLResultSet class and provides a way for a procedure to store rows of data in a procedure result set to be returned to the calling application. There can only be one procedure result set in a stored procedure.

A stored procedure must explicitly process a result set to return it to the calling application:

• Declare the procedure result set through the RESULT clause of the procedure specification
• Populate the procedure result set in the body of the procedure using the methods of SQLResultSet

When FairCom DB SQL creates a Java class from a CREATE PROCEDURE statement that contains the RESULT clause, it implicitly instantiates a SQLResultSet() object. Invoke methods of SQLResultSet() to populate fields and rows of the procedure result set.

SQLResultSet provides three methods:

• SQLResultSet.set(col, val) - Set column col with value val in a result set row. Column positions are referred by numerical value with 1 as the first column
• SQLResultSet.makeNULL(col) - Set column col as null
• SQLResultSet.insert() - Insert a populated row into the result set

Limit: SQLResultSet is limited to 50 columns.

The following example processes a result set from a SELECT statement to return a procedure result set. For each row of the FairCom DB SQL result set assigned to procedure variables, the procedure:

• Assigns the current values in the procedure variables to corresponding fields in the procedure result set with the SQLResultSet.set() method
• Inserts a row into the procedure result set with the SQLResultSet.insert() method

Example Returning a Procedure Result Set From a Stored Procedure

CREATE PROCEDURE get_sal2 ()
RESULT (
    empname  CHAR(20),
    empsal   NUMERIC
)
IMPORT
import java.math.BigDecimal;
BEGIN
    StringBuffer ename = new StringBuffer (20) ;
    BigDecimal esal = new BigDecimal () ;
    SQLCursor empcursor = new SQLCursor (
        "SELECT name, sal FROM emp " ) ;
   
    empcursor.open () ;
    do
    {
        empcursor.fetch ();
        if (empcursor.found ())
        {
            ename = (StringBuffer)empcursor.getValue (1, CHAR);
            esal = (BigDecimal)empcursor.getValue (2, DECIMAL);
            SQLResultSet.set (1, ename);
            SQLResultSet.set (2, esal);
            SQLResultSet.insert ();
        }
    } while (empcursor.found ()) ;
    empcursor.close () ;
END

 Note: The resultset parameter empname in the procedure above should be of type CHAR in ANSI versions of FairCom DB SQL.

Handling Null Values

Stored procedures need to routinely set and detect null values:

• Stored procedures may need to set the values of a FairCom DB SQL statement input parameters or procedure result fields to null.
• Stored procedures must check if the value of a field in a FairCom DB SQL result set is null before assigning it through the SQLCursor.getValue() method. (FairCom DB SQL generates a runtime error if the result-set field specified in getValue() is null.)

Setting SQL Statement Input Params & Procedure Result SetFields to Null

Both the setParam() method (see “The setParam Method: Pass Input Values to SQL Statements”) and set() method (see “Returning a Procedure Result Set: the RESULT Clause and DhSQLResultSet”) take objects as their value arguments. You can pass a null reference directly to the method or pass a variable which has been assigned the null value. The following example shows using both techniques to set a FairCom DB SQL input parameter to null.

Example Passing Null Values to setParam()

CREATE PROCEDURE test_nulls( )
BEGIN
     Integer pvar_int1      = new Integer(0);
     Integer pvar_int2      = new Integer(0);
     Integer pvar_int3;
     pvar_int3 = null;
     SQLIStatement insert_t1 = new SQLIStatement
     ( "INSERT INTO ADMIN.t1 (c1,c2, c3) values (?,?,?) ");
     insert_t1.setParam(1, new Integer(1)); // Set to non-null value
     insert_t1.setParam(2, null);           // Set directly to null
     insert_t1.setParam(3, pvar_int3);      // Set indirectly to null
     insert_t1.execute();
END

Assigning Null Values from Result Sets:SQLCursor.wasNULL Method

If the value of the field argument to the SQLCursor.getValue() method is null, FairCom DB SQL returns a runtime error:

(error(-20144): Null value fetched.)

This means you must always check whether a value is null before attempting to assign a value in a FairCom DB SQL result set to a procedure variable or output parameter. The SQLCursor class provides the wasNULL() method for this purpose.

The SQLCuror.wasNULL() method returns TRUE if a field in the result set is null. It takes a single integer argument that specifies which field of the current row of the result set to check.

The following example illustrates using wasNULL().

Example Result Sets for Null Values with wasNULL()

CREATE PROCEDURE test_nulls2( )
RESULT ( res_int1 INTEGER ,
         res_int2 INTEGER ,
         res_int3 INTEGER )
BEGIN
     Integer pvar_int1      = new Integer(0);
     Integer pvar_int2      = new Integer(0);
     Integer pvar_int3      = new Integer(0);
     SQLCursor select_t1 = new SQLCursor
     ( "SELECT c1, c2, c3 from t1" );
    
     select_t1.open();
     select_t1.fetch();
     while ( select_t1.found() )
     {
         // Assign values from the current row of the SQL result set
         // to the pvar_intx procedure variables. Must first check
         // whether the values fetched are null: if they are, must set
         // pvars explicitly to null.
         if ((select_t1.wasNULL(1)) == true)
            pvar_int1 = null;
         else
            pvar_int1 = (Integer)select_t1.getValue(1, INTEGER);
         if ((select_t1.wasNULL(2)) == true)
            pvar_int2 = null;
         else
            pvar_int2 = (Integer)select_t2.getValue(2, INTEGER);
         if ((select_t1.wasNULL(3)) == true)
            pvar_int3 = null;
         else
            pvar_int3 = (Integer)select_t13getValue(3, INTEGER);
      // Transfer the value from the procedure variables to the
      // columns of the current row of the procedure result set.
         SQLResultSet.set(1,pvar_int1);
         SQLResultSet.set(2,pvar_int2);
         SQLResultSet.set(3,pvar_int3);
         // Insert the row into the procedure result set.
         SQLResultSet.insert();
        
         select_t1.fetch();
     }
     // Close the SQL result set.
     select_t1.close();
END

Handling Errors

FairCom DB SQL stored procedures use standard Java try/catch constructs to process exceptions.

Any errors in a FairCom DB SQL statement execution result in the creation of an DhSQLException class object. When FairCom DB SQL detects an error in an SQL statement, it throws an exception. The stored procedure should use try/catch constructs to process such exceptions. The getDiagnostics() method of the DhSQLException class object provides a mechanism to retrieve different details of the error.

The getDiagnostics() method takes a single argument whose value specifies which error message detail it returns:

getDiagnostics Method Arguments

The error messages and the associated SQLSTATE and FairCom DB SQL error code values are documented in the FairCom DB SQL Reference Manual, Table B-2.

The following example shows an excerpt from a stored procedure that uses DhSQLException.getDiagnostics().

Example Exception Handling with DhSQLException.getDiagnostics()

CREATE PROCEDURE test_proc()
BEGIN
   try
   {
      SQLIStatement insert_cust = new SQLIStatement (
      "INSERT INTO customer VALUES (1,2) ");
   }
   catch (DhSQLException e)
   {
      String errstate = e.getDiagnostics (DhSQLException.RETURNED_SQLSTATE) ;
      String errmesg = e.getDiagnostics (DhSQLException.MESSAGE_TEXT) ;
   }
END

 Stored procedures can also throw their own exceptions by instantiating a DhSQLException object and throwing the object when the procedure detects an error in execution. The conditions under which the procedure throws the exception object are completely dependent on the procedure.

The following example illustrates using the DhSQLException() constructor to create an exception object called excep. It then throws the excep object under all conditions.

Example Throwing Procedure-Specific Exceptions

CREATE PROCEDURE sp1_02()
BEGIN
     // raising exception
     DhSQLException excep = new DhSQLException(666,new String("Entered the tst02 procedure"));
     if (true)
     throw excep;
END

Calling Stored Procedures from Stored Procedures

Stored procedures and triggers can call other stored procedures. Nesting procedures lets you take advantage of existing procedures. Instead of rewriting the code, procedures can simply issue CALL statements to the existing procedures.

Another use for nesting procedures is to assemble result sets generated by queries on different databases into a single result set. With this technique, the stored procedure processes multiple SELECT statements through multiple instances of the SQLCursor class. For each of the instances, the procedure uses the DhSQLResultSet class to add rows to the result set returned by the procedure.

Debugging Java Stored Procedures

FairCom DB SQL allows Java debugging tools, such as JSwat, to connect and directly debug stored procedure routines. JSwat is an open-source GUI Java debugger, which can be downloaded from https://github.com/nlfiedler/jswat.

This section includes information about configuring your environment to enable debugging with JSwat and provides an example of how to use JSwat.

Enabling Java Debugging Tools

FairCom DB SQL allows Java debugging tools to connect and directly debug stored procedure routines. To enable this feature, follow the following steps.

1. Add the following keywords to ctsrvr.cfg:

   1. SETENV DEBUG_JVM=S
      
      SETENV DEBUG_JVM_PORT=45987
      

   2. The first keyword enables the FairCom DB SQL JVM debug feature by creating a TCP/IP socket at the port specified with the DEBUG_JVM_PORT keyword for a Java debugger to attach. In addition, the DEBUG_JVM keyword instruct the server to compile stored procedures with debugging information and to not remove the stored procedure source file from disk.
2. Start the FairCom DB SQL server.
3. Create a stored procedure (for example, “test”).
4. Examine the database directory (i.e. ctreeSQL.dbs) for a .java file. This is the Java file source. Pay particular attention to the class name (i.e. public final class admin_test_SP extends JavaBaseSP).
5. Start a Java debugger and attach it to localhost on the port specified by DEBUG_JVM_PORT.
   1. For example, using the Java debugger included with the JDK, run:
   2. jdb -attach 45987
   3. (run on the same machine running the server to access the Java source files).
6. Set a breakpoint on the method dhSPwrap of the stored procedure calls (i.e. admin_test_SP.dhSPwrap).
7. Call the stored procedure from any client side SQL tool such as ISQL.
8. The debugger should break at the start of the stored procedure.

JSWAT Example

JSwat is an open-source GUI Java debugger. It can be downloaded from https://github.com/nlfiedler/jswat.

1. Add the following keywords to ctsrvr.cfg:

   1. SETENV DEBUG_JVM=S
      SETENV DEBUG_JVM_PORT=45987

2. Start the FairCom DB SQL Server.
3. Start ISQL and create a stored procedure as follows:

   1. # isql -a ADMIN -u admin ctreeSQL
      
      CREATE PROCEDURE admin.test( IN name CHAR (20) )
      
      BEGIN
      
      Integer testing = new Integer(24);
      
      END

4. Start jswat.
5. Select “New Session” from the “Session” menu and edit the following information:
   1. Assign a name (for instance “FairCom DB Session”).
   2. Switch to the “classes” panel and add the path to the ctreeSQLSP.jar file and the path to the ctreeSQL.dbs directory.
   3. Switch to the “Sources” panel and add the path to the ctreeSQL.dbs directory.
6. Select the session just created from the Sessions Drop Down menu on the toolbar.
7. From the “Session” menu, click on “Attach”
8. Configure the Transport as "Attach by socket". Fill in Host and Port with information from your FairCom DB SQL Server configuration.

   1. 

9. Click OK (The “debugger console” should now read” “VM attached to session FairCom DB Session”.
10. In the “Breakpoint” menu select “New Breakpoint” to create a new breakpoint.
    1. Set Breakpoint type to “Method.”
    2. Set Class to the stored procedure name (for example, admin_test_SP).
    3. Set Method to “dhSPwrap.”

    4. 

11. Return to ISQL and run the stored procedure:
    1. call test ('John Smith');
12. The debugger displays the current line in the stored procedure.

    1. 

CREATE PROCEDURE [ owner_name. ] procname
        ( [ parameter_decl [ , ... ] ) ] ]
        [ RESULT ( column_name data_type [ , ... ] ) ]
        [ IMPORT
            java_import_clause ]
        BEGIN
            java_snippet
        END

parameter_decl ::
        { IN | OUT | INOUT } parameter_name data_type
 

ISQL> CREATE TABLE helloworldTBL (fld1 CHAR(100));
ISQL> CALL HelloWorld();
0 records returned
ISQL> SELECT * FROM helloworldTBL;
FLD1
----
Hello World!
1 record selected

$ type helloworldscript.sql
SET ECHO ON;
SET AUTOCOMMIT OFF;
CREATE PROCEDURE HelloWorld ()

BEGIN
     SQLIStatement Insert_HelloWorld = new SQLIStatement
         ("INSERT INTO HelloWorldTBL(fld1) values ('Hello World!')");
     Insert_HelloWorld.execute();
END
commit work;
$ isql -s hello_world_script.sql example_db
SET AUTOCOMMIT OFF;
CREATE PROCEDURE HelloWorld ()

BEGIN
     SQLIStatement Insert_HelloWorld = new
       SQLIStatement ("INSERT INTO HelloWorld(fld1)
       values ('Hello World!')");
     Insert_HelloWorld.execute();
END
;
commit work;
$

	C:\example_scripts>type type_mismatch.sql
	CREATE PROCEDURE error_proc()
	BEGIN
	    Double dbl_val;
	    Integer int_val = new Integer(1234);
	    dbl_val = int_val;
	END
 
	C:\example_scripts>isql -s type_mismatch.sql testdb
	error(-20141): error in compiling the stored procedure
	:03: Incompatible type for =. Can't convert java.lang.Integer to java.lang.Double.

 SQLCursor select_syscalctable = new SQLCursor (
  "SELECT fld FROM ADMIN.syscalctable ");
     select_syscalctable.open();
     select_syscalctable.fetch();
     err ("Any records? Found returned " + select_syscalctable.found());
.
.
.
 

CREATE  TRIGGER [ owner_name. ] trigname
    { BEFORE | AFTER }
    { INSERT |  DELETE |  UPDATE [ OF (column_name [ , ... ] ) }
ON table_name
    [ REFERENCING {  OLDROW  [ , NEWROW ]  | NEWROW  [ , OLDROW ]  }  ]
    [ FOR EACH { ROW | STATEMENT } ]
    [ IMPORT  java_import_clause ]

    BEGIN
        java_snippet 
    END

CREATE TRIGGER BUG_UPDATE_TRIGGER
AFTER UPDATE OF (STATUS, PRIORITY) ON BUG_INFO
REFERENCING OLDROW, NEWROW
FOR EACH ROW

IMPORT 
   import java.sql.* ;
BEGIN
   try
{    
  // column number of STATUS is 10
  String  old_status, new_status;

  old_status = (String)OLDROW.getValue(10,CHAR);
  new_status = (String)NEWROW.getValue(10,CHAR);

  if ((old_status.compareTo("OPEN") == 0) &&
      (new_status.compareTo("FIXED") == 0))
    {
     //  If STATUS has changed from OPEN to FIXED
     //  increment the bugs_fixed_cnt by 1 in the
     //  row corresponding to current month 
     //  and current year
     SQLIStatement  update_stmt = new SQLIStatement (
        " update BUG_STATUS set bugs_fixed_cnt = bugs_fixed_cnt + 1 "
        " where  month = ?  and year = ?");
        Integer current_month = 10;
        Integer current_year  = 1997;
        update_stmt.setParam(1, current_month);
        update_stmt.setParam(2, current_year);
        update_stmt.execute();
    }
}
catch(DhSQLException e)
  {throw e;}
END

	ISQL> CREATE FUNCTION str_cat(IN org_string VARCHAR(20), IN string_to_concat VARCHAR(20))
	RETURNS VARCHAR(40)
	IMPORT
	import java.math.*;
	BEGIN
	     String new_str = org_string +  string_to_concat ;
	     return new_str;
	END

CREATE FUNCTION [ owner_name.]function_name
         ( [parameter_decl , ...] )
         RETURNS (data_type)
        [ IMPORT
                    java_import_clause ]
        BEGIN
            java_snippet
        END
parameter_decl ::
        [ IN ] parameter_name data_type

SELECT str_cat('abcd','efgh') FROM syscalctable;
 
 
STR_CAT(ABCD,EFGH)
-------------------------------
abcdefgh
1 record selected

SELECT empfname, str_cat(empfname, emplname) FROM emp WHERE str_cat('mary', 'john') = 'maryjohn';
 

EMPFNAME          STR_CAT(EMPFNAME,EMPLNAME)
----------------  ---------------------------------------------
Mary              MaryJohn
1 records selected

SELECT str_cat(?, ?) FROM emp
SELECT * FROM emp WHERE str_cat(?, ?) = 'MaryJohn'

CREATE FUNCTION  myuser.TO_NATIVE_DATE(IN UNIX_TYPE_DATE INT)
RETURNS TIMESTAMP
IMPORT
import java.sql.*;
BEGIN
    StringBuffer query_str = new StringBuffer(“SELECT TIMESTAMPADD(SQL_TSI_SECOND,”);
    query_str.append(UNIX_TYPE_DATE).append(“, TO_TIMESTAMP('01/01/1970 00:00:00')) FROM SYSCACLTABLE”);
    SQLCursor SelCursor = new SQLCursor(query_str.toString());
    SelCursor.open();
    SelCursor.fetch();
    return(Timestamp)SelCursor.getValue(1, TIMESTAMP);
END