Technology Blogs by SAP
Learn how to extend and personalize SAP applications. Follow the SAP technology blog for insights into SAP BTP, ABAP, SAP Analytics Cloud, SAP HANA, and more.
cancel
Showing results for 
Search instead for 
Did you mean: 
JWootton
Advisor
Advisor
0 Kudos

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;