通过 ODBC 连接 PostgreSQL 数据库查询表数据，如果表中数据量非常大，一次性全部取出会占用大量的内存，可能导致程序崩溃。此时可以考虑采用游标分批返回数据的方式来实现。

ODBC 使用游标分批返回数据需要先在 odbc.ini 配置文件中配置参数 UseDeclareFetch 和 Fetch。

• UseDeclareFetch，表示启用数据库服务器的游标分批返回数据
• Fetch，表示使用数据库服务器的游标时，结果集在客户端缓存的最大行数，默认值为 100

配置 odbc.ini，如下：

[pg]
Description = PostgreSQL ODBC
Driver = PostgreSQL
Database = postgres
Servername = 127.0.0.1
UserName = admin
Password = 123456
Port = 36099
ReadOnly = -1
ConnSettings = set client_encoding to UTF8
UseDeclareFetch = 1
Fetch = 10000

测试程序代码，查询表 t，如下：

#include <stdio.h>
#include <stdlib.h>
#include <sql.h>
#include <sqlext.h>
#include <string.h>

#define NAME_LEN 64
#define STMT_LEN 128

#define CHECK_ERROR(e, s, h, t) ({\
            if (e!=SQL_SUCCESS && e != SQL_SUCCESS_WITH_INFO) {extract_error(s, h, t); goto exit;} \
})

void extract_error(char *fn, SQLHANDLE handle, SQLSMALLINT type)
{
    SQLINTEGER i = 0;
    SQLINTEGER NativeError;
    SQLCHAR SQLState[ 7 ];
    SQLCHAR MessageText[256];
    SQLSMALLINT TextLength;
    SQLRETURN ret;

    fprintf(stderr, "\nThe driver reported the following error %s\n", fn);
    do
    {
        ret = SQLGetDiagRec(type, handle, ++i, SQLState, &NativeError,
                            MessageText, sizeof(MessageText), &TextLength);
        if (SQL_SUCCEEDED(ret)) {
            printf("%s:%ld:%ld:%s\n",
                        SQLState, (long) i, (long) NativeError, MessageText);
        }
    }
    while( ret == SQL_SUCCESS );
}

int main () {

        SQLHENV  henv  = SQL_NULL_HENV;         // Environment
        SQLHDBC  hdbc  = SQL_NULL_HDBC;         // Connection handle
        SQLHSTMT hstmt = SQL_NULL_HSTMT;        // Statement handle
        SQLRETURN retcode;
        long len;
        char buf[1024];
        char stmt[] = "select * from t";

        // Allocate an environment handle
        retcode=SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
        CHECK_ERROR(retcode, "SQLAllocHandle(SQL_HANDLE_ENV)",
                    henv, SQL_HANDLE_ENV);

        // We want ODBC 3 support
        retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION,
                            (void *) SQL_OV_ODBC3, 0);
        CHECK_ERROR(retcode, "SQLSetEnvAttr(SQL_ATTR_ODBC_VERSION)",
                    hdbc, SQL_HANDLE_DBC);

        // Allocate a connection handle
        retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
        CHECK_ERROR(retcode, "SQLAllocHandle", hdbc, SQL_HANDLE_DBC);

        // Connect to the DSN
        retcode=SQLDriverConnect(hdbc, NULL, "DSN=pg;", SQL_NTS,
                             NULL, 0, NULL, SQL_DRIVER_COMPLETE);
        CHECK_ERROR(retcode, "SQLDriverConnect(DSN=DATASOURCE;)",
                    hdbc, SQL_HANDLE_DBC);

        // Allocate a statement handle
        retcode=SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
        CHECK_ERROR(retcode, "SQLAllocHandle(STMT)", hstmt, SQL_HANDLE_STMT);


        retcode = SQLExecDirect(hstmt, stmt, SQL_NTS);

        if ( SQL_SUCCEEDED( retcode ))
        {
                while(SQL_SUCCEEDED(retcode = SQLFetch(hstmt)))
                {
                        SQLGetData(hstmt,1,SQL_C_CHAR, buf, 50, &len);
                        printf("%s\n", buf);

                }
        }
         else
        {
                CHECK_ERROR(retcode, "SQLExecDirect(STMT)", hstmt, SQL_HANDLE_STMT);
        }

exit:

    printf ("\nComplete.\n");

    // Free handles
    // Statement
    if (hstmt != SQL_NULL_HSTMT)
        SQLFreeHandle(SQL_HANDLE_STMT, hstmt);

    // Connection
    if (hdbc != SQL_NULL_HDBC) {
        SQLDisconnect(hdbc);
        SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
    }

    // Environment
    if (henv != SQL_NULL_HENV)
        SQLFreeHandle(SQL_HANDLE_ENV, henv);

    return 0;
}

编译执行，如下：

[zhang@localhost odbc]$ gcc select.c -o select -I/data/zhang/app/unixODBC/include -L/data/zhang/app/unixODBC/lib -lodbc
[zhang@localhost odbc]$ ./select
aaaaa
bbbbb
ccccc
ddddd
eeeee

Complete.

设置 PostgreSQL 的参数 log_statement = 'all'，记录所有执行的 SQL，会发现如下的日志记录：

BEGIN;
declare "SQL_CUR0x2f92b5d0" cursor with hold for select * from t;
fetch 10000 in "SQL_CUR0xa0905d0";
close "SQL_CUR0xa0905d0";
commit;

文章评论