Product Documentation

FairCom Java & .NET Stored Procedures

Previous Topic

Next Topic

Executing an SQL Statement

If a FairCom DB SQL statement does not generate a result set, stored procedures can execute it in one of two ways:

  • Immediate execution, using methods of the SQLIStatement class, executes a statement once.
  • Prepared execution, using methods of the SQLPStatement class, prepares a statement so you can execute it multiple times in a procedure loop.

Both SQLIStatement and SQLPStatement classes can be used to call stored procedures that do not have a RESULT clause (that is, the JSPs that do not return any resultset). Note that values of OUT and INOUT arguments can be retrieved using the getParam() method of SQLIStatement, SQLPStatement, and SQLCursor.

The following table shows the SQL statements that do not generate result sets. You can execute these statements in a stored procedure using either the SQLIStatement or SQLPStatement class.

Executable SQL Statements

ALTER TABLE

DROP PROCEDURE

CALL (if no result set)

DROP TABLE

CREATE INDEX

DROP TRIGGER

CREATE SYNONYM

DROP VIEW

CREATE PROCEDURE

GRANT

CREATE TABLE

INSERT

CREATE TRIGGER

RENAME

CREATE VIEW

REVOKE

DELETE

UPDATE

DROP INDEX

UPDATE STATISTICS

DROP SYNONYM

 

Previous Topic

Next Topic

Immediate Execution

Use immediate execution when a procedure needs to execute an SQL statement only once. The following example shows an instance of immediate execution.

Immediate Execution Example


CREATE PROCEDURE insert_customer (

IN cust_number INTEGER,

IN cust_name CHAR(20)

)

BEGIN

SQLIStatement insert_cust = new SQLIStatement (

"INSERT INTO customer VALUES (?,?) ");

insert_cust.setParam (1, cust_number);

insert_cust.setParam (2, cust_name);

insert_cust.execute ();

END


Note: The input parameter cust_name in the procedure above should be of type CHAR in ANSI versions of FairCom DB SQL.

This example inserts a row in a table. The constructor for SQLIStatement() takes the FairCom DB SQL INSERT statement as its only argument. In this example, the statement includes two parameter markers.

TOCIndex