Code Examples of Calling Database Stored Procedures in esProc

esProc can call database stored procedures conveniently. This article will illustrate in detail the program writing with examples.

A.Call stored procedures without return values

 Take oracle stored procedure as an example, the stored procedure has only one input parameter, and no output parameter: 

    create or replace procedure pro1

    (pid IN VARCHAR)

    as

    begin

    insert into emp values(pid,'mike');

    update emp set name='rose' where id=pid;

    commit;

    end;

execute function or proc function can be used in esProc to call this stored procedure:

A1：Connect to the database.

A2：Call stored procedure, input parameter value is 4.

proc function is mainly used to call stored procedures which return values and result sets, or to call stored procedure pro1 which does not return parameters. 

A1：Connect to the database.

A2：Call stored procedure pro1. The code 4:0:"i": after comma describes and defines an input parameter, in which 4 is the input parameter value, 0 represents that the type of input parameter is automatically identified by esProc, and "i" represents input type. If the parameter type need to be designated manually, the code may be written as 4:1:"i":, in which the 1 in the middle represents Integer(int). For more information about parameter types supported by esProc, please see appendix: Definition of Parameter Types.

B. Call stored procedures which return a single value

The stored procedure below will return one parameter value, so proc function, instead of execute function, will be used.

    create or replace procedure testb

    (para1 in varchar2,para2 out varchar2)  

    as

    begin

    select name into para2 from emp where id= para1;

    end testb;

The code for calling this stored procedure by esProc is:

A1：Connect to the database.
A2：proc function is used to call stored procedure testb. Here two parameters are used: one is 1:0:"i" representing that the value is 1 and the input parameter is automatically identified; the other is 11:"o":name in which 11 represents string type (see appendix Definition of Parameter Types for more), "o" represents type of output parameter and name defines an esProc variable to receive return values for the output parameter.
A3：Assign output value of stored procedure in A2 to cell A3 through name variable.

C. Call stored procedures which return a single result set
    Stored procedure RQ_TEST_CUR returns a single result set
    CREATE OR REPLACE PROCEDURE RQ_TEST_CUR
    (
V_TEMP OUT TYPE.RQ_REF_CURSOR,
PID IN VARCHAR
    )
    AS
    BEGIN
OPEN V_TEMP FOR SELECT * FROM TEST WHERE ID =PID;
    END RQ_TEST_CUR;

The stored procedure has an input parameter and returns a result set. The code for calling the stored procedure in esProc is as follows:

Cell A2 uses proc function to call the stored procedure: proc("{call RQ_TEST_CUR(?,?)}",:101:"o":table1,1:0:"i":). The following will explain the input parameters of proc function.
1） SQL strings
"{call RQ_TEST_CUR(?,?)}" represents calling the stored procedure’s name. The question marks represent SQL's parameters.
2） Output parameters
:101:"o":table1 defines an output parameter, in which 101 represents that its data type is cursor, and ”o” represents that it is an output parameter. Table1 defines a variable which is used to reference returned results. 1:0:"i": defines an input parameter, in which 1 is value of the input parameter, 0 represents that the parameter type is automatically identified by esProc.
Cell A3 uses output variable in A1 to reference the execution results of the stored procedure. Computed result is a table sequence containing two fields: id and name, which is the same as that in A2.

D. Call stored procedures which return multiple result sets
The code will begin with an oracle stored procedure which returns two result sets:
    create or replace procedure proAA
    (
out_var out sys_refcursor,
out_var2 out sys_refcursor
    )
    as
    begin
openout_var for select * from emp;
open out_var2 for select * from test;
    end;
The stored procedure returns result sets of two tables: emp and test. Call the stored procedure in esProc and the program for receiving the two result sets is as follows:

Cell A2 uses proc function to call the stored procedure: orac.proc("{call proAA(?,?)}",:101:"o":a,:101:"o":b), which returns two result sets (table sequences) to form a sequence, i.e., a set of table sequence. The sequence assigns value to A2. The following will explain the input parameters ofproc function.
1）SQL strings
"{call proAA(?,?)}" represents calling the stored procedure’s name. The question marks represent SQL's parameters.
2）Output parameter 1
:101:"o":a defines an output parameter, in which 101 represents that its data type is cursor, and "o" represents it is an output parameter. a defines a variable which is used to reference returned results.
3）Output parameter 2
:101:"o":b defines an output parameter, in which 101 represents that its data type is cursor, and "o" represents it is an output parameter. b defines a variable which is used to reference returned results.

Cell A3 returns cell A2’s first table sequence (table emp's result set)
Cell A4 and cell A5 use respectively the output variables a and b in A2 to obtain execution results of corresponding stored procedure. a corresponds to data in table emp and assigns value to A4; b corresponds to data in table test and assigns value to A5.

Appendix: Definition of Parameter Types
Values of type are:
public final static byte DT_DEFAULT = (byte) 0; //default, automatically identify
public final static byte DT_INT = (byte) 1;
public final static byte DT_LONG = (byte) 2;
public final static byte DT_SHORT = (byte) 3;
public final static byte DT_BIGINT = (byte) 4;
public final static byte DT_FLOAT = (byte) 5;
public final static byte DT_DOUBLE = (byte) 6;
public final static byte DT_DECIMAL = (byte) 7;
        public final static byte DT_DATE = (byte) 8;
public final static byte DT_TIME = (byte) 9;
public final static byte DT_DATETIME = (byte) 10;
public final static byte DT_STRING = (byte) 11;
public final static byte DT_BOOLEAN = (byte) 12;

public final static byte DT_INT_ARR = (byte) 51;
public final static byte DT_LONG_ARR = (byte) 52;
public final static byte DT_SHORT_ARR = (byte) 53;
public final static byte DT_BIGINT_ARR = (byte) 54;
public final static byte DT_FLOAT_ARR = (byte) 55;
public final static byte DT_DOUBLE_ARR = (byte) 56;
public final static byte DT_DECIMAL_ARR = (byte) 57;

public final static byte DT_DATE_ARR = (byte) 58;
public final static byte DT_TIME_ARR = (byte) 59;
public final static byte DT_DATETIME_ARR = (byte) 60;
public final static byte DT_STRING_ARR = (byte) 61;
public final static byte DT_BYTE_ARR = (byte) 62;
public final static byte DT_CURSOR = (byte) 101;
public final static byte DT_AUTOINCREMENT = (byte) 102;