This topic describes how to use stored procedures with the Query operator when connected to a JDBC Table data construct.
The StreamBase Query operator can deliver calls to SQL stored procedures and stored functions to the database defined in its JDBC data source. If you want StreamBase applications to access information returned by stored procedures and functions, you must select the Explicitly Specify Result Set Schema option in the Result Settings tab of the Query operator's Properties view. Conversely, if you call a stored procedure or stored function that does not return a result set, do not select this option.
There are significant differences between implementations of stored procedures by different database vendors, so the correct settings depend on the database you are using.
Note
For some databases, the SQL for stored functions requires that they begin and end with braces. That is, the function must begin with "{" and end with "}". When defining such functions with the Query operator, you must quote the braces with a backslash so they are not interpreted by StreamBase as parameter substitution.
The supported returned data types from stored procedures and functions are
result set and cursor. If a
stored procedure or function returns any other data type to the Query operator, it is
silently ignored. Typical stored procedures that return the results of a SELECT
statement have a return data type of result set.
The following is an example of a simple procedure stored in a database:
CREATE PROC dbo.getproc
@p1 int
AS
SELECT val, txt FROM dbo.proctest WHERE k=@p1
Use a SQL statement like the following to invoke the example procedure above:
EXEC dbo.getproc {myInt}
This returns a result set with 0 to N rows, and with columns val and txt from the table dbo.proctest, where all values equal the value passed as
{myInt}.
For most databases, the only setup required to use stored procedures is that you must explicitly set the schema of the expected result set in the Result Settings tab of the operator's Properties view to match the data being returned:
-
Select Explicitly declare fields below in the SQL Results field drop-down list.
-
Specify the fields of the schema in the Fields grid.
The returned data is accessed by name, so the names of fields in the schema must match the column names returned from the query. You can use the Execute query and populate fields link to send the SQL statement to the database. This returns the columns of the queried table, which then automatically populates the Fields grid. This has the advantage of not requiring a database query during typechecking.
For some databases, including Oracle, there is additional configuration required on the Result Settings tab. For these databases, you must:
-
Select the Use CallableStatement to execute stored procedures check box.
-
For stored procedures, in the JDBC parameter index with result control, specify the one-based index number of the output parameter that contains the returned data of interest.
-
For stored functions, specify 1.
An example will clarify how to determine the parameter index. Consider a stored procedure defined with the following SQL:
CREATE PROCEDURE return_proc(start_num IN integer,
my_cursor OUT types.ref_cursor, my_host IN VARCHAR2) AS
BEGIN
OPEN my_cursor FOR
SELECT * FROM MACHINE_RUN
WHERE ID < start_num AND hostname = my_host;
END;
Use a SQL statement like the following to invoke the example procedure above:
call return_proc({myInt}, ?, {myString})
In this case, set the JDBC index field to 2. This specifies that the second parameter of the procedure (the OUT parameter in the procedure definition) is the parameter that contains the output.
A stored procedure might have more than one OUT parameter. The Query operator can only process one result set, so you must specify which of the several OUT parameters contains the output data of interest to your query.