sqlrcur_prepareQuery(cur,"exec testproc");
sqlrcur_inputBindLong(cur,"in1",1);
sqlrcur_inputBindDouble(cur,"in2",1.1,2,1);
sqlrcur_inputBindString(cur,"in3","hello");
sqlrcur_defineOutputBind(cur,"out1",20);
sqlrcur_executeQuery(cur);
char    *result=sqlrcur_getOutputBind(cur,"out1");

To drop the stored procedure, run a query like the following.

drop procedure testproc

To create the stored procedure, run a query like the following.

create procedure testproc(in1 integer, in2 float, in3 varchar(20)) returns (out1 integer) as
begin
        out1=in1;
        suspend;
end;

To execute the stored procedure from an SQL Relay program, use code like the following.

sqlrcur_prepareQuery(cur,"select * from testproc(?,?,?)");
sqlrcur_inputBindLong(cur,"1",1);
sqlrcur_inputBindDouble(cur,"2",1.1,2,1);
sqlrcur_inputBindString(cur,"3","hello");
sqlrcur_executeQuery(cur);
char    *result=sqlrcur_getFieldByIndex(cur,0,0);

Alternatively, you can run a query like the following and receive the result using an output bind variable. Note that in Interbase/Firebird, input and output bind variable indices are distict from one another. The index of the output bind variable is 1 rather than 4, even though there were 3 input bind variables.

sqlrcur_prepareQuery(cur,"execute procedure testproc ?, ?, ?");
sqlrcur_inputBindLong(cur,"1",1);
sqlrcur_inputBindDouble(cur,"2",1.1,2,1);
sqlrcur_inputBindString(cur,"3","hello");
sqlrcur_defineOutputBind(cur,"1",20);
sqlrcur_executeQuery(cur);
char    *result=sqlrcur_getOutputBind(cur,"1");

To drop the stored procedure, run a query like the following.

drop procedure testproc

In DB2, stored procedures return values through output parameters rather than as return values of the procedure itself.

To create the stored procedure, run a query like the following.

create procedure testproc(in in1 int, in in2 double, in in3 varchar(20), out out1 int) language sql
begin
        set out1 = in1;
end

To execute the stored procedure from an SQL Relay program, use code like the following.

sqlrcur_prepareQuery(cur,"call testproc(?,?,?,?)");
sqlrcur_inputBindLong(cur,"1",1);
sqlrcur_inputBindDouble(cur,"2",1.1,2,1);
sqlrcur_inputBindString(cur,"3","hello");
sqlrcur_defineOutputBind(cur,"4",25);
sqlrcur_executeQuery(cur);
char    *result=sqlrcur_getOutputBind(cur,"4");

To drop the stored procedure, run a query like the following.

drop procedure testproc

To create the stored procedure, run a query like the following.

create function testfunc(int,float,char(20)) returns int as ''''
declare
        in1 int;
        in2 float;
        in3 char(20);
begin
        in1:=$1;
        return;
end;
'''' language plpgsql

To execute the stored procedure from an SQL Relay program, use code like the following.

sqlrcur_prepareQuery(cur,"select * from testfunc(:in1,:in2,:in3)");
sqlrcur_inputBindLong(cur,"in1",1);
sqlrcur_inputBindDouble(cur,"in2",1.1,4,2);
sqlrcur_inputBindString(cur,"in3","hello");
sqlrcur_executeQuery(cur);
char    *result=sqlrcur_getFieldByIndex(cur,0,0);

To drop the stored procedure, run a query like the following.

drop function testfunc(int,float,char(20))

Some stored procedures return multiple values. Below are examples, illustrating how to create, execute and drop this kind of stored procedure for each database that SQL Relay supports.

In Oracle, stored procedures can return values through output parameters or as return values of the procedure itself. If a procedure needs to return multiple values, it can return one of them as the return value of the procedure itself, but the rest must be

上一页  [1] [2] [3] [4] [5] [6] [7] [8] [9] [10]  ...  下一页 >>