Product Documentation

FairCom Java & .NET Stored Procedures

Previous Topic

Next Topic

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, JDBC, or .NET applications, or directly from the Interactive SQL utility, isql.

For example, the following example shows an excerpt from an ODBC application that calls a stored procedure (order_parts) using the ODBC syntax:

{ call procedure_name ( param ) }.

Executing a Stored Procedure Through ODBC


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 steps:

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

The following figure illustrates the steps in executing a stored procedure.

TOCIndex