Progress
SQL-92
Guide and Reference


Invoking Stored Procedures

How applications call stored procedures depends on their environment.

From ODBC

From ODBC, applications use the ODBC call escape sequence:

SYNTAX
{ CALL proc_name [ ( parameter [ , ... ] ) ] } ; 

Use parameter markers (question marks used as placeholders) for input or output parameters to the procedure. You can also use literal values for input parameters only. Progress SQL-92 stored procedures do not support return values in the ODBC escape sequence. See the Microsoft ODBC Programmer’s Reference , Version 3.0, for more information on calling procedures from ODBC applications.

Embed the escape sequence in an ODBC SQLExecDirect call to execute the procedure.

EXAMPLE

This example shows a call to a stored procedure named order_parts that passes a single input parameter using a parameter marker:

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); 

From JDBC

The JDBC call escape sequence is the same as in ODBC:

SYNTAX
{ CALL proc_name [ ( parameter [ , ... ] ) ] } ; 

Embed the escape sequence in a JDBC CallableStatement.prepareCall method invocation.

EXAMPLE

This example shows the JDBC code parallel to the ODBC code excerpt shown in the previous example:

try
{
     CallableStatement statement;
     int Part_num = 318;
     
     // Associate the statement with the procedure call
     // (conn is a previously-instantiated connection object)
     statement = conn.prepareCall("{call order_parts(?)}");
     
     // Bind the parameter.
     statement.setInt(1, Part_num);
     
     // Execute the statement.
     statement.execute();
} 

From SQL Explorer

From SQL Explorer, issue the SQL CALL statement.

EXAMPLE

This example shows the CALL statement that invokes the order_parts stored procedure, using a literal value instead of a parameter marker:

CALL order_parts (318); 


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