Once a stored procedure is created and stored in the database, any application or other stored procedure can execute it. You can call stored procedures from either ODBC applications or JDBC applications.
Example: Stored procedure using ODBC syntax
The following example shows an excerpt from an ODBC application that calls a stored procedure (order_parts) using the ODBC syntax
{ callprocedure_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);
A stored procedure executes using the following process:
1. The application calls the stored procedure through its native calling mechanism. The previous example uses the ODBC call escape sequence.
2. The OpenEdge SQL 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 OpenEdge SQL.
4. OpenEdge SQL 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 OpenEdge SQL, which in turn passes them to the calling application.
The following figure illustrates the steps in executing a stored procedure.
