I just passed along this example that Geetha wrote on how to call a stored procedure in an external database using the CCL getdata() function. I realized that this hasn't been added to the set of examples that ship with the product yet, so in the mean time I thought I'd share it here:
Description: This example shows how the getdata() SPLASH function can be used to call stored procedures in a Sybase ASE database. (but same basic approach should work for other databases)
Inputs: Manually input values like SAP, MSFT, IBM into 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 Table: 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
Create an ODBC connection to the table and configure ESP. For more information on how to configure ESP for an external database please refer to the ESP doc on setting up ESP data services in the ESP Administrators Guide.
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 into the tables then users can return a dummy row and makes sure the dummy row datatype matches the return value.
And here's the CCL:
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_GEETHA','exec stock_scan ?',StockStream.symbol);
for(rec in asedata)
output setOpcode(rec,insert);
}
resize(asedata,0);
};
END;
Thanks again to Geetha Chittaranjan for putting this example together.
