Using Stored Procedures

Contents

Stored Procedure Overview
Creating a Stored Procedure
Calling a Stored Procedure
Setup for All Databases
Special Setup for Oracle Databases

This topic describes how to use stored procedures with the Query operator when connected to a JDBC Table data construct. Some of the following content is drawn from the JDBC Table Data Construct Sample, which you can load into StreamBase Studio, configure for the data source you are using, and run.

Stored Procedure Overview

The Spotfire Streaming 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.

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 that represents all rows in the database where column k equals the value of {myInt} being passed to the stored procedure. The result set only includes columns val and txt.

Creating a Stored Procedure

This section provides examples of a stored procedure. There are some differences between implementations of stored procedures by different database vendors. Proper syntax thus varies. The following examples illustrate these variations in supported database vendors. You may need administrative access to the database to create a stored procedure.

Note

For some databases, the SQL syntax for calling stored functions begins and ends with curly braces. That is, the SQL statement must begin with "{" and end with "}". When specifying such SQL strings in an EventFlow Query operator, you must quote the curly braces with a backslash so they are not interpreted by StreamBase as parameter substitution.

Oracle 10g Data Source

  1. Define a package: 

    CREATE OR REPLACE PACKAGE types
AS
    TYPE ref_cursor IS REF CURSOR;
END;

  2. Define the procedure: 

    CREATE PROCEDURE get_orders_by_symbol(test_cursor OUT types.ref_cursor, 
in_symbol IN VARCHAR2) AS
BEGIN
    OPEN test_cursor FOR
        SELECT * FROM ORDERBOOK
        WHERE ORDERBOOK.SYMBOL = in_symbol;
END;

MySQL 5 Data Source 

CREATE PROCEDURE GetOrdersBySymbol (
    Symbol VARCHAR(50)
)
BEGIN
    SELECT * FROM OrderBook WHERE OrderBook.Symbol=Symbol;
END;

Calling a Stored Procedure

The Query operator can run a stored procedure using a command specified in the SQL statement field of its Query Settings tab. The syntax for calling a stored procedure varies between database server types. For example:

  • MS SQL Server 2008: exec dbo.GetOrdersBySymbol @Symbol={Symbol}

  • Oracle 10g: call get_orders_by_symbol(?, {Symbol})

  • MySQL 5.0: call GetOrdersBySymbol({Symbol})

Note

Do not append a semicolon to calling statements. The query will not run and you receive an error message from the database which may not be very descriptive.

Setup for All Databases

For supported 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 names of fields in the schema you specify in the Query operator must match the database column names returned from the query. You can use the Execute query and populate fields link in the Query operator’s Result Settings tab to send the SQL statement to the database. This returns the columns of the database being queried, which then automatically populates the Fields grid, and has the advantage of not requiring a database query during typechecking.

Special Setup for Oracle Databases

For Oracle, additional configuration is required on the Result Settings tab. You must:

  • Select the Use CallableStatement to run 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 ?.

For example, to run the stored procedure described in Oracle 10g Data Source above, you can use a SQL statement like the following to invoke the example procedure above: 

call get_orders_by_symbol(?, {Symbol})

In this case, set the JDBC parameter index with result property to 1. This property setting specifies that the first ? parameter in the SQL statement corresponds to an OUT parameter of the stored procedure whose value will be used as the SQL Results for this Query operator.

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.

Note

The Query operator cannot directly call procedures that return output variables, only those that return a result set or cursor. However, you can wrap procedures that return variables inside a second procedure that selects the values returned by the first procedure into a result set.

Back to Top