Progress
SQL-92
Guide and Reference


Executing an SQL-92 Statement

If an SQL-92 statement does not generate a result set, stored procedures can execute the statement in one of two ways:

Table 5–3 shows the SQL statements that do not generate result sets. You can execute these statements in a stored procedure using either the SQLIStatement or the SQLPStatement class.

Table 5–3: Executable SQL-92 Statements 
ALTER USER
CREATE INDEX
CREATE PROCEDURE
CREATE SYNONYM
CREATE TABLE
CREATE TRIGGER
CREATE USER
CREATE VIEW
DELETE
DROP INDEX
DROP PROCEDURE
DROP TABLE
DROP TRIGGER
DROP USER
DROP VIEW
GRANT
INSERT
REVOKE
UPDATE
UPDATE STATISTICS

Immediate Execution

Use immediate execution when a procedure needs to execute an SQL statement only once.

EXAMPLE

This stored procedure in this sample script inserts a row in a table. The constructor for SQLIStatement takes the SQL INSERT statement as its only argument. In this example, the statement includes five parameter markers:

TeamProc.sql 
------------------------------------------------------------------------- 
-- File Name: TeamProc.sql
--
-- Purpose:  Creates the insert_team stored procedure
--           in the current database.
--          
-- Requirements:
--           (1) Database must be created
--           (2) Database must be running
--           (3) Submit this script to SQL Explorer as an input file
--               (character mode) or as a run file (GUI mode). 
--           
-- Revision History:
-- 
-- Date                Author                     Change 
-- ----                ------                     ------
--
-- 11/99               DB-DOC                     Created, V9.1A
------------------------------------------------------------------------- 

CREATE PROCEDURE insert_team(
     IN  empnum      INTEGER not null,
     IN  FirstName   VARCHAR(30) not null,
     IN  LastName    VARCHAR(50) not null,
     IN  State       VARCHAR(50) not null,
     IN  Sport       CHAR(20)
) ;
 
BEGIN
     SQLIStatement insert_team = new SQLIStatement (
        "INSERT INTO team (empnum, FirstName, LastName, State, Sport)
          VALUES ( ?,?,?,?,? ) ");
     insert_team.setParam (1, empnum);
     insert_team.setParam (2, FirstName);
     insert_team.setParam (3, LastName);
     insert_team.setParam (4, State);
     insert_team.setParam (5, Sport) ;
     insert_team.execute ();
END

COMMIT WORK ; 

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.

There is an advantage to prepared execution when you execute the same SQL statement from within a loop. Instead of creating an object with each iteration of the loop, prepared execution creates an object once and supplies input parameters for each execution of the statement.

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

EXAMPLE

This code fragment extends the previous example to use prepared execution:

CREATE PROCEDURE prepared_insert_customer (
   IN  cust_number INTEGER,
   IN  cust_name   CHAR(20)
)
 
BEGIN
   SQLPStatement p_insert_cust = new SQLPStatement (
   "INSERT INTO customer VALUES (?,?) ");
   .
   .
   .
int i;
for (i = 0; i < new_custs.length; i++)
{
   p_insert_cust.setParam (1, new_custs[i].cust_number);
   p_insert_cust.setParam (2, new_custs[i].cust_name);
   p_insert_cust.execute ();
}
END 


Copyright © 2004 Progress Software Corporation
www.progress.com
Voice: (781) 280-4000
Fax: (781) 280-4095