Example 3
This example queries data using a stored procedure in the Database Inbound protocol.
For example, to select all records from database table Table_sanity
, you can create a stored procedure for data
selecting. Then, you can pass this stored procedure in the Database Inbound protocol Read
Action.
Note: For the Database Inbound protocol, passing
parameters to Read Action for the SQL statement or stored procedure is not supported. For
the Oracle database, you define an out parameter with
sys_refcursor
type to return the result set. When passing this stored
procedure, the OUT CURSOR
key should be set in the
Read Action stored procedure
implementation at the end of the cursor out parameter. Query data from a SQL Server database:
- Define the stored procedure on
SQL Server:
create procedure sp_dbp_read AS select * from Table_sanity where Table_sanity.flag=0 order by Table_sanity.ID
- Pass the stored procedure in
Read Action using:
{call sp_dbp_read()}
- Run the hcidbprotocoltest:
hcidbprotocoltest -r read
SQL result:
{call sp_dbp_read}
Read 1 message(s) from DB.
key01,test1,1,10/01/12,0
Query data from an Oracle database:
- Define the stored procedure on
Oracle:
create or replace procedure sp_dbp_read(out_var out sys_refcursor) IS BEGIN open out_var for select * from TABLE_SANITY where TABLE_SANITY.FLAG=0 order by TABLE_SANITY.ID; END;
- Pass the stored procedure in
Read Action using:
{call sp_dbp_read(rowset OUT CURSOR)}
- Run the hcidbprotocoltest:
hcidbprotocoltest -r read
SQL result:
{call sp_dbp_read(rowset OUT CURSOR)}
Read 10 message(s) from DB.
key01,test1,1,04/01/14,0