> testproc
drop package types
Sybase and Microsoft SQL Server
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. Interbase and Firebird
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. DB2
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. Postgresql
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
Caching The Result Set
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);
}
Suspending and Resuming Sessions
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] 下一页
|
注：本站部分文章源于互联网，版权归原作者所有！如有侵权，请原作者与本站联系，本站将立即删除！ 本站文章除特别注明外均可转载，但需注明出处！ [MinTao学以致用网] 