1.1.1

Note: When working with custom result processors, you may find it helpful to create an alias for the
result processor class name. You can use the alias in place of the full result processor class name when
you invoke a data-aware procedure. To create an alias use the SQL statement:
CREATE ALIAS processor_alias FOR 'processor_class_name'
where processor_class_name is the full name of a class that implements
com.vmware.sqlfire.ProcedureResultProcessor and has a default constructor.
Invoking a Procedure
SQLFire uses an extended CALL syntax for invoking data-aware procedures.
The SQLFire syntax for invoking a procedure is:
CALL procedure_name
( [ expression [, expression ]* ] )
[ WITH RESULT PROCESSOR processor_class ]
[ { ON TABLE table_name [ WHERE whereClause ] }
|
{ ON { ALL | SERVER GROUPS (server_group_name [, server_group_name]* )
} }
]
Use the optional ON and WHERE clauses to provide routing hints to the SQLFire engine, in order to prune the
procedure execution to a subset of SQLFire members:
• ON TABLE executes the procedure code on SQLFire members that host data for the table. With partitioned
tables, you can also use the WHERE clause to further specify only those members host specific data values
for the table.
Note that both the ON TABLE and WHERE clauses are only used to prune procedure execution to specific
members; the clauses do not limit the data results, and neither the ON TABLE nor the WHERE clause restriction
is applied to queries within the procedure body.
• ON ALL executes the procedure code on all SQLFire members, while ON SERVER GROUPS executes the
procedure code on one or more named server groups.
Note: Specifying ON TABLE, ON ALL, and ON SERVER groups also affects the scoping of nested
queries within the procedure implementation. See Populating Result Sets on page 164 and Using the <local>
and <global> Escape Syntax with Nested Queries on page 166 for more information.
If you omit the ON clause, SQLFire executes the procedure as a data-independent procedure on the local,
coordinating member.
If you omit the WITH RESULT PROCESSOR clause when calling a data-aware procedure, then SQLFire uses
a default result processor implementation.
If the called procedure does not specify an OUT parameter or result set, then SQLFire calls the procedure
asynchronously, without waiting for a reply. SQLFire logs any error that occurs during procedure execution.
High Availability for SQLFire Procedures
If an error occurs before a procedure has returned any rows to the client, then SQLFire automatically re-executes
the procedure.
Procedure and result processor implementations can use the isPossibleDuplicate() method to determine
if SQLFire re-executed a procedure after a SQLFire member failed. This type of detection is necesary for certain
implementations that perform write operations that can lead to duplicate entries on procedure re-execution.
159
Using Data-Aware Stored Procedures