下面是 Linux 环境下一个简单的 C 程序示例，通过 psqlodbc 连接到 PostgreSQL 数据库，创建一张表 odbc_table。

1. 配置ODBC数据源

编辑 ODBC 数据源配置文件 ~/.odbc.ini，具体配置信息如下：

[pg]
Description = PostgreSQL ODBC
Driver = PostgreSQL
Database = postgres
Servername = 127.0.0.1
UserName = admin
Password = 123456
Port = 5432
ReadOnly = -2
ConnSettings = set client_encoding to UTF8

2. 编写C程序

C 程序代码文件 sample.c，如下：

/**********************************************************************
* FILENAME :        CreateTableWithID.c
*
* DESCRIPTION :
*       Simple SQL SERVER example to create a basic table with an
*       identity field
*
* ODBC USAGE :
*               Prompts for table name
*               SQLExecDirect - to execute CREATE TABLE statement
*
*/
#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;

        char sqlStmtCreate[] = "CREATE TABLE odbc_table"
                            "(PersonID int NOT NULL,"
                            "FirstName varchar(255) NOT NULL,"
                            "LastName varchar(255),"
                            "Address varchar(255), City varchar(255))";

        // 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);



        // Execute CREATE TABLE
        retcode = SQLExecDirect(hstmt, sqlStmtCreate, SQL_NTS);
        if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {
                printf ("Table Created\n");
        } else {
                printf ("Table Create Failed : \n");
                 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;
}

3. 编译执行

其中关于 PostgreSQL ODBC 环境的编译安装配置见链接：PostgreSQL ODBC 编译安装

# 编译
gcc sample.c -o sample -I/data/zhang/app/unixODBC/include -L/data/zhang/app/unixODBC/lib -lodbc

# 执行
[zhang@localhost odbc]$ ./sample
Table Created

Complete.

文章评论