SQL relay的C接口

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]  下一页

[Access]sql随机抽取记录  [Access]ASP&SQL让select查询结果随机排序的实现方法
[办公软件]在sybase中插入图片、PDF、文本文件  [办公软件]安装Sybase ASE
[办公软件]linux指令大全(完整篇)  [办公软件]Linux新手入门常用命令大全
[办公软件]在RedHat Linux 9里安装gaim0.80  [办公软件]浅谈Linux 下Java 1.5 汉字方块问题解决方法
[办公软件]Linux程序员必读：中文化与GB18030标准  [办公软件]linux指令大全



 

Copyright ＠ 2007-2012 敏韬网(敏而好学，文韬武略--MinTao.Net)（学习笔记） Inc All Rights Reserved.
闵涛 E_mail:admin@mintao.net(欢迎提供学习资源)

鄂公网安备 42011102001154号

站长：MinTao ICP备案号：鄂ICP备11006601号-18