SQLFire extends the CALL statement to enable execution of Data-Aware Procedures (DAP). These procedures can be routed to SQLFire members that host the required data.


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]* ) } }


The CALL syntax enables you to specify the SQLFire members that execute the procedure as well as the result processor that you want to use to process the results from multiple members.

SQLFire has a default result processor that collates results from different members if you do not specify a custom result processor. The default processor performs unordered merges on the dynamic result sets from each server where the procedure is executed, and presents the same number of ResultSets to the JDBC client that was declared in the CREATE PROCEDURE statement.

Using Data-Aware Stored Procedures provides more information about developing and configuring data-aware procedures and custom result processors.


With this clause a custom result processor can be given which collates results and OUT parameters from multiple servers.

ON and WHERE Clauses

The optional ON and WHERE clauses control the execution of DAPs on specific SQLFire members. If no ON clause is provided, the procedure is executed in only one server, the coordinator (data-independent). Otherwise, it is executed on only the servers that are hosting data for the specified table, optionally routed based on a WHERE clause. If an ON ALL or ON SERVER GROUPS clause is provided, then execution is routed to either all servers or the servers in the specified server groups.


This call executes the procedure "procedureName" only on those members that belong to server group "sg2:"

CALL procedureName() ON SERVER GROUPS (sg2)

This call executes the procedure on members where values of ID are in the range 'ID >= 20 and ID < 40'. ID should be the partitioning column, otherwise SQLFire routes the procedure execution to all members that host data for the table:


This call executes the procedure on all members (both accessors and data hosts):

CALL procedureName() ON ALL

The default behavior executes the procedure only on the query member:

CALL procedureName()