Using getdata() to call an external DB stored procedure from CCL

Reposting an old example from a different forum, in response to recent questions on how to do this.

This example shows how the getdata() CCL function can be used to call stored procedures in a database. This example uses SAP ASE.
 
Inputs
Manually input values like SAP, MSFT, IBM to the StockStream to make the example call the getData() function.
Output is produced in stock_traded stream, only if the symbol is SAP or IBM and data is present in the stock table.

Server Tables
This example requires a table and stored procedure in ASE. 
  create table stock(symbol varchar(10),volume integer,price float)
  insert into stock values(‘SAP’,10,200.00)
  insert into stock values(‘IBM’,10,300.00)
  insert into stock values(‘AAPL’,10,500.00)

Create a stored procedure:
  create procedure stock_scan
  (@symbol varchar(10))
  as
  begin
  select * from stock where symbol=@symbol
  end

Note that you first need to define a “data service” on the streaming/ESP cluster, since you reference the data service by name in the getdata() call.

Note: The getData() function call requires a return value from the stored procedure and the return  datatype of the return value should match the vector parameter datatype. If users have a stored procedure that just does insertion in to the tables then users can return a dummy row and makes sure the dummy row datatype matches the return value.

Author: Geetha Chittaranjan

CREATE INPUT stream StockStream SCHEMA (symbol string);

CREATE Flex stock_flex

  IN StockStream

  OUT OUTPUT STREAM stock_traded SCHEMA(symbol string,volume integer,price float)

BEGIN

          DECLARE

          /* The typedef must be the same as the return value of the stored procedure.            */

                  typedef[string symbol;|integer volume; float price;] datarec;

          /* asedata is a vector variable to store the return value of the stored procedure  */

             vector(datarec) asedata;

          END;

          ON StockStream {

                  if(isnull(asedata))

                          asedata :=new vector(datarec);                 

                  if(StockStream.symbol=’SAP’ OR StockStream.symbol=’IBM’)

                  {

                          getData(asedata,’ASE_Connection’,’exec stock_scan ?’,StockStream.symbol);

                          for(rec in asedata)

                                  output setOpcode(rec,insert);

                  }              

                  resize(asedata,0);

                    };     

END;