Progress
SQL-92
Guide and Reference


Calling Stored Procedures

Once a stored procedure is created and stored in the database, any application or other stored procedure can execute it by calling it. You can call stored procedures from ODBC applications, JDBC applications, ESQL-92 applications, or directly from the SQL-92 Explorer.

EXAMPLE

This example shows an excerpt from an ODBC application that calls a stored procedure (order_parts) using the ODBC syntax { call procedure_name ( param ) }:

SQLUINTEGER Part_num;
SQLINTEGER  Part_numInd = 0;
// Bind the parameter.
     SQLBindParameter (hstmt, 1, SQL_PARAM_INPUT,
          SQL_C_SLONG, SQL_INTEGER, 0, 0, &Part_num, 0, Part_numInd);
// Place the department number in Part_num.
Part_num = 318;
// Execute the statement.
SQLExecDirect(hstmt, "{call order_parts(?)}", SQL_NTS); 

Executing a stored procedure involves the following general steps:

  1. The application calls the stored procedure through its native calling mechanism. The previous example uses the ODBC call escape sequence.
  2. Progress SQL-92 retrieves the compiled bytecode form of the procedure and submits it to the Java Virtual Machine for execution.
  3. For every SQL statement in the procedure, the Java Virtual Machine calls Progress SQL-92.
  4. Progress SQL-92 manages the interaction of the stored procedure with the database and execution of the SQL statements, and returns any result to the Java Virtual Machine.
  5. The Java Virtual Machine returns result (output parameters and result sets) of the procedure to Progress SQL-92, which in turn passes them to the calling application.

Figure 5–2 illustrates the steps in executing a stored procedure.

Figure 5–2: Executing Stored Procedures


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