Writing stored procedures

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

From the command prompt, you can invoke SQL Explorer and submit the file containing the CREATE PROCEDURE statement as an input script, as shown in the following example.

Example: CREATE PROCEDURE input script

$ sqlexp -infile hello_world_script.sql example_db

Example: CREATE PROCEDURE in context of application call

The following example illustrates the use of the CREATE PROCEDURE statement in the context of an application call.

-- File name: hello_world_script.sql
-- Purpose: Illustrate a CREATE PROCEDURE statement.
@echo true;
@autocommit true;
CREATE PROCEDURE HelloWorld ()
BEGIN
SQLIStatement Insert_HelloWorld = new SQLIStatement (
"INSERT INTO HelloWorld(fld1) values ('Hello World!')");
Insert_HelloWorld.execute();
END
;COMMIT WORK;

The Java snippet within the CREATE PROCEDURE statement does not execute as a stand-alone program. Instead, it executes in the context of an application call to the method of the class created by the OpenEdge SQL Engine. This characteristic has the following implications:

If the snippet declares any classes, it must instantiate them within the snippet to invoke their methods.

It is not possible to make use of stdout messages in stored procedures. This means method invocations such as System.out.println will not display messages, because stdout cannot be used in a server process where stored procedures are executed. If you would like to put tracing code in your stored procedures, it is recommended that you open and close a regular text file via Java and write your messages to that file.

In this section:

Invoking stored procedures

Modifying and deleting stored procedures

Stored procedure security

Using the OpenEdge SQL Java classes