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:

  1. 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
  2. Pass the stored procedure in Read Action using:
    {call sp_dbp_read()}    
  3. 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:

  1. 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;   
  2. Pass the stored procedure in Read Action using:
    {call sp_dbp_read(rowset OUT CURSOR)}    
  3. 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