Skip to Content
Author's profile photo Jeff Wootton

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.
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))
  select * from stock where symbol=@symbol

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)



          /* 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;


          ON StockStream {


                          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);





Assigned Tags

      Be the first to leave a comment
      You must be Logged on to comment or reply to a post.