FairCom Java & .NET Stored Procedures

Writing Stored Procedures

Use any text editor to write the CREATE PROCEDURE statement and save the source code as a text file. That way, you can easily modify the source code and try again if it generates syntax or Java compilation errors.

Submit the file containing the CREATE PROCEDURE statement to interactive SQL as a script, as shown in the following example.

Submitting Scripts to Create Stored Procedures

$ 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;

Keep in mind that the Java snippet within the CREATE PROCEDURE statement does not execute as a standalone program. Instead, it executes in the context of an application call to the method of the class created by FairCom DB SQL. This characteristic has the following implications:

It is meaningless for a snippet to declare a main method, since it will never be executed.
If the snippet declares any classes, it must instantiate them within the snippet to invoke their methods.
FairCom DB SQL redirects the standard output stream to a file, sql_server.log. Method invocations such as System.out.println() will not display messages on the screen, however, instead writes to that file.
The Java System.exit() call is not allowed.