Database procedures are supported through the JDBC CallableStatement interface. The JDBC Driver supports the following database procedure syntax.

For all of these statements, the JDBC Driver supports a combined parameter syntax supporting features of the ODBC positional parameter syntax and the Ingres named parameter syntax:

Parameters can be named or unnamed, but mixing of named and unnamed parameters is not allowed. Dynamic parameters can also be named using CallableStatement methods introduced with JDBC 3.0. Literals can only be named using the syntax provided above. All Ingres database procedure parameters are named.

When connecting to an Ingres 9.x or earlier DBMS Server, the use of named procedure parameters is encouraged. (Using named parameters improves performance by eliminating a query of the database catalog to assign names to the parameters based on the declared order of the procedure parameters.) When connecting to an Ingres 10 or later DBMS Server, named procedure parameters are not required.

The JDBC Driver provides support for parameter default values by allowing parameter values to be omitted. This support is intended primarily for ODBC positional parameters. For Ingres named parameters, default values can be used simply by omitting the parameter entirely.

Ingres supports the parameter attributes IN, OUT, and INOUT when creating database procedures. When invoking a database procedure, the JDBC Driver marks a parameter as IN when an input value is set using a CallableStatement.setXXX() method. Registering a parameter for output using a CallableStatement registerOutParameter() method will mark the parameter as OUT. Setting a value and registering for output will mark a parameter as INOUT. All dynamic parameters must have an input value assigned and/or be registered for output prior to executing the procedure.

Ingres database procedure parameters can also be passed by value or reference when not explicitly marked with IN, OUT, or INOUT attributes. The JDBC Driver treats parameters passed by value as IN parameters, and parameters passed by reference (BYREF) as INOUT parameters. If an input value is not provided for a parameter registered for output, the driver sends a NULL value of the output type registered for that parameter.

Ingres Global Temporary Table procedure parameters are specified by providing a parameter value in the form session.table_name. In this parameter, table_name is the name of the Global Temporary Table, and 'session.' identifies the parameter as a Global Temporary Table parameter.

The CallableStatement methods executeQuery() and execute() can be used to execute a row-producing procedure. The methods executeUpdate() and execute() can be used for non-row-producing procedures. Ingres does not permit output parameters with procedures that return rows.

Procedure return values, output parameter values and rows returned by row-producing procedures are accessed by standard JDBC methods and interfaces. The CallableStatement getXXX() methods are used to retrieve procedure return and output parameter values. Rows returned by a procedure are accessed using the ResultSet returned by the CallableStatement getResultSet() method.

Ingres database procedures permit the use of the transaction statements COMMIT and ROLLBACK, however, the use of these statements is highly discouraged!

Using these statements in a procedure executed by the JDBC Driver can result in the unintentional commitment or rollback of work done prior to procedure execution. It is also possible that a change in transaction state during procedure execution can be interpreted as a transaction abort. For these reasons, applications must make sure that no transaction is active prior to executing a database procedure that contains COMMIT or ROLLBACK statements.