Stored procedures in Sybase and Microsoft SQL Server can return a result set if the last command in the procedure is a select query, however SQL Relay doesn''''t currently support stored procedures that return result sets.

Stored procedures in Interbase and Firebird can return a result set if a select query in the procedure selects values into the output parameters and then issues a suspend command, however SQL Relay doesn''''t currently support stored procedures that return result sets.

Stored procedures in DB2 can return a result set if the procedure is declared to return one, however SQL Relay doesn''''t currently support stored procedures that return result sets.

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

create function testfunc() returns setof record as ''''
        declare output record;
begin
        for output in select * from mytable loop
                return next output;
        end loop;
        return;
end;
'''' language plpgsql

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

sqlrcur_sendQuery(cur,"select * from testfunc() as (testint int, testfloat float, testchar char(40))");
char    *field00=sqlrcur_getFieldByIndex(cur,0,0);
char    *field01=sqlrcur_getFieldByIndex(cur,0,1);
char    *field02=sqlrcur_getFieldByIndex(cur,0,2);
char    *field10=sqlrcur_getFieldByIndex(cur,1,0);
char    *field11=sqlrcur_getFieldByIndex(cur,1,1);
char    *field12=sqlrcur_getFieldByIndex(cur,1,2);
...

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

drop function testfunc

Say you''''re writing a web-based report where a query with a huge result set is executed and 20 rows are displayed per page. Rather than rerunning the query for every page every time and dumping all but the 20 rows you want to display, you can run the query once, cache the result set to a local file and just open the file for each page of the report.

First CGI:

#include <sqlrelay/sqlrclientwrapper.h>
#include <stdio.h>

main() {

        char       *filename;

        sqlrcon      con=sqlrcon_alloc("host",9000,"","user","password",0,1);
        sqlrcur      cur=sqlrcur_alloc(con);

	... generate a unique file name ...

        sqlrcur_cacheToFile(cur,filename);
        sqlrcur_setCacheTtl(cur,600);
        sqlrcur_sendQuery(cur,"select * from my_table");
        sqlrcon_endSession(con);
        sqlrcur_cacheOff(cur);

        ... pass the filename to the next page ...

        sqlrcur_free(cur);
        sqlrcon_free(con);
}

Second CGI:

#include <sqlrelay/sqlrclientwrapper.h>
#include <stdio.h>

main() {

        int        row,col;

        ... get the filename from the previous page ...

        ... get the page to display from the previous page ...

        sqlrcon      con=sqlrcon_alloc("host",9000,"","user","password",0,1);
        sqlrcur      cur=sqlrcur_alloc(con);

        sqlrcur_openCachedResultSet(cur,filename);
        sqlrcon_endSession(con);

        for (row=pagetodisplay*20; row<(pagetodisplay+1)*20; row++) {
                for (col=0; col<sqlrcur_colCount(cur); col++) {
                        printf("%s,",sqlrcur_getFieldByIndex(cur,row,col));
                }
                printf("\n");
        }

        sqlrcur_free(cur);
        sqlrcon_free(con);
}

Sometimes web-based applications need a single database transaction to span multiple pages. Since SQL Relay sessions can be suspended and resumed, this is possible.

First CGI:

#include <sqlrelay/sqlrclientwrapper.h>
#include <stdio.h>

main() {

        int        port;
        char       *socket;
        int        rs;

        sqlrcon      con=sqlrcon_alloc("host",9000,"","user","password",0,1);
        sqlrcur      cur=sqlrcur_alloc(con);

        sqlrcur_sendQuery(cur,"insert into my_table values (1,2,3)");
        port=getConnectionPort(cur);
        socket=getConnectionSocket(cur);
	rs=sqlrcur_getResultSetId(cur);
	sqlrcur_suspendResultSet(cur);
        sqlrcon_suspendSession(con);

        ... pass the rs, port and socket to the next page ...

        sqlrcur_free(cur);
        sqlrcon_free(con);
}

Second CGI:

#include <sqlrelay/sqlrclientwrapper.h>
#include <stdio.h>

ma
 << 上一页  [11] [12]  下一页