Support for SPL Routines

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

To create and execute 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.
Dynamic Server

If you use Dynamic Server, use the CREATE FUNCTION statement if the routine returns values.

For more information on the CREATE FUNCTION statement, see the IBM Informix: Guide to SQL Syntax.

End of Dynamic Server
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.
Dynamic Server

If you use Dynamic Server and created your routine with the CREATE FUNCTION statement, enter an EXECUTE FUNCTION statement to run the function.

End of Dynamic Server
Use the Run option to execute the routine and display the results.

Figure 23 shows the text of the routine in the c_proc.sql command file, which is supplied with the demonstration database. To try this routine, use the Choose option and then select c_proc.

To register the routine in the database, select the Run option, as Figure 23 shows.

Figure 23. 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 procedure 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 execute 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 last name is Pauli:

If you use Dynamic Server, enter:

EXECUTE FUNCTION 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 execute the routine. Figure 24 shows the result of executing the routine.

Figure 24. Result of Executing 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

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.