FairCom Java & .NET Stored Procedures

From ODBC, applications use the ODBC call escape sequence:

{ 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. FairCom DB SQL stored procedures do not support return values in the ODBC escape sequence. See the Microsoft ODBC Programmer’s Reference, Version 3.0, for more detail on calling procedures from ODBC applications.

Embed the escape sequence in an ODBC SQLExecDirect() call to execute the procedure. The following example shows a call to a stored procedure named order_parts that passes a single input parameter using a parameter marker.

Example

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

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

{ call proc_name [ ( parameter [ , ... ] ) ] }

Embed the escape sequence in a JDBC CallableStatement.prepareCall() method invocation. The following example shows the JDBC code parallel to the ODBC code excerpt shown in the previous example.

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 .NET applications, use the CtreeSqlCommand() object to execute stored procedures. The following example illustrates use of a stored procedure with parameters.

Example

// Create a new connection object.
String conString = "User=ADMIN;Password=ADMIN;Database=ctreeSQL";
CtreeSqlConnection hConnection = new CtreeSqlConnection(conString);

// Open the connection.
hConnection.Open();

// Create a new command object.
CtreeSqlCommand hCommand = new CtreeSqlCommand(hConnection);
hCommand.CommandText = "order_parts";

// Set the command type.
hCommand.CommandType = CommandType.StoredProcedure;

// Construct a parameter and add to the command.
CtreeSqlParameter param1 = hCommand.Parameters.Add("part_num", CtreeSqlDbType.Integer);

// Set the value for part number.
param1.Value = 318;

// Execute the command and create a DataReader object.
CtreeSqlDataReader hReader = hCommand.ExecuteReader();

From the Interactive SQL utility, isql, simply issue a SQL CALL statement.

Example

The following example demonstrates how a CALL statement invokes an order_parts stored procedure, using a literal value instead of a parameter marker:

CALL order_parts (318);

For an example of executing a stored procedure using parameters from Interactive SQL see the ISQL and Tools Reference Manual.

A stored procedure can be used in place of a table reference in a SQL SELECT statement if the stored procedure meets certain criteria:

• The stored procedure must return a result set.
• The stored procedure may not have any IN/OUT parameters or any OUTPUT parameters

Example

As a Tabled Valued Function in a SELECT Statement:

select * from myproc() where col1 < 20;