Support for SPL Routines

You can create and run routines that are written in SPL from the SQL menu.

You can store the SPL routine in a separate command file and then call it from an application or run it as a stand-alone program. After you create the SPL routine, you can run it within DB-Access with the appropriate SQL statement. The following example details the steps.

To create and run an SQL routine

To create the text of the routine, type directly in the NEW screen or the Use-editor screen. Enter the SPL and SQL statements for your routine in the statement block of a CREATE PROCEDURE statement.
Use the CREATE FUNCTION statement if the routine returns values.
Use the Run option to create the routine and register it in the sysprocedures system catalog table.
Use the NEW screen to enter an EXECUTE PROCEDURE statement that names the routine that you want to run.
If you use HCL Informix® and created your routine with the CREATE FUNCTION statement, enter an EXECUTE FUNCTION statement to run the function.
Use the Run option to run the routine and display the results.

For example, the c_proc.sql command file, which is supplied with the demonstration database, contains an SPL. Before you can run the routine, change the word procedure in the c_proc.sql file to function because the routine returns a value. Then use the Choose option and select c_proc.

First you must register the routine in the database. Select the Run option, as the following figure shows.

Figure 1. Displaying the text of an SPL routine on the SQL menu

SQL:   New  Run   Modify  Use-editor  Output  Choose  Save  Info  Drop  Exit

Run the current SQL statements.

----------------------- mydata@mynewdb ------- Press CTRL-W for Help --------

create function read_address (lastname char(15))

        returning char(15), char(15), char(20), char(15),char(2), char(5);
        define p_fname, p_city char(15);
        define p_add char(20);
        define p_state char(2);
        define p_zip char(5);
        select fname,  address1, city, state, zipcode
                into p_fname,  p_add, p_city, p_state, p_zip
                from customer
                where lname = lastname;

        return p_fname, lastname, p_add, p_city, p_state, p_zip;

end procedure;

DB-Access displays a message to indicate that the database server created the routine. To run the routine, select New from the SQL menu and then enter the appropriate EXECUTE statement. In the following example, the user requests the address of a customer whose surname is Pauli:

EXECUTE PROCEDURE read_address ("Pauli")

After you enter the EXECUTE PROCEDURE or EXECUTE FUNCTION statement on the NEW screen, press Esc to return to the SQL menu. Select Run from the SQL menu to run the routine. The following figure shows the result of running the routine.

Figure 2. Result of running an SPL routine on the SQL menu

SQL:   New   Run   Modify  Use-editor  Output  Choose  Save  Info  Drop  Exit

Run the current SQL statements.

----------------------- mydata@mynewdb ------- Press CTRL-W for Help --------

Ludwig
Pauli
213 Erstwild Court
Sunnyvale
CA
94086

Tip: SPL routines are stored in the system catalog tables in executable format. Use the Routines option on the DATABASE INFO menu to display a list of the routines in the current database or to display the text of a specified routine.