unixODBC 应用编程（1）

    最近，需要在Linux平台上连接SQL Server，在网上找到了unixODBC，对官方网站上的资料作了整理，以作系统掌握。

1.       连接数据源

步骤：

（1）       分配ODBC环境句柄，设置句柄属性（设置ODBC版本）

（2）       分配连接句柄，设置连接属性（可以使用默认属性）

（3）       连接数据源

 

涉及的API定义：

句柄——SQLHENV，SQLHDBC

句柄类型——SQL_HANDLE_ENV，SQL_HANDLE_DBC

属性类型——SQL_ATTR_ODBC_VERSION

属性值——SQL_OV_ODBC3

指示符——SQL_NULL_HANDLE，SQL_NTS

返回值——SQL_SUCCESS， SQL_SUCCESS_WITH_INFO，SQL_ERROR，


         SQL_INVALID_HANDLE

API函数：

     SQLAllocHandle，SQLSetEnvAttr，SQLSetConnectAttr，SQLConnect
   
sample code

SQLHENV             V_OD_Env; // Handle ODBC environment

long             V_OD_erg; // result of functions

SQLHDBC             V_OD_hdbc; // Handle connection


char             V_OD_stat[10]; // Status SQL

SQLINTEGER         V_OD_err,V_OD_rowanz,V_OD_id;
SQLSMALLINT         V_OD_mlen;
char V_OD_msg[200],V_OD_buffer[200];

    // 1. allocate Environment handle and register version

    V_OD_erg=SQLAllocHandle(SQL_HANDLE_ENV,SQL_NULL_HANDLE,&V_OD_Env);
    if ((V_OD_erg != SQL_SUCCESS) && (V_OD_erg != SQL_SUCCESS_WITH_INFO))
    {
        printf("Error AllocHandle\n");
        exit(0);
    }
    V_OD_erg=SQLSetEnvAttr(V_OD_Env, SQL_ATTR_ODBC_VERSION,
                               (void*)SQL_OV_ODBC3, 0);
    if ((V_OD_erg != SQL_SUCCESS) && (V_OD_erg != SQL_SUCCESS_WITH_INFO))
    {
        printf("Error SetEnv\n");
        SQLFreeHandle(SQL_HANDLE_ENV, V_OD_Env);
        exit(0);
    }
    // 2. allocate connection handle, set timeout

    V_OD_erg = SQLAllocHandle(SQL_HANDLE_DBC, V_OD_Env, &V_OD_hdbc);
    if ((V_OD_erg != SQL_SUCCESS) && (V_OD_erg != SQL_SUCCESS_WITH_INFO))
    {
        printf("Error AllocHDB %d\n",V_OD_erg);
        SQLFreeHandle(SQL_HANDLE_ENV, V_OD_Env);
        exit(0);
    }
    SQLSetConnectAttr(V_OD_hdbc, SQL_LOGIN_TIMEOUT, (SQLPOINTER *)5, 0);
    // 3. Connect to the datasource "web"

    V_OD_erg = SQLConnect(V_OD_hdbc, (SQLCHAR*) "web", SQL_NTS,
                                     (SQLCHAR*) "christa", SQL_NTS,
                                     (SQLCHAR*) "", SQL_NTS);
    if ((V_OD_erg != SQL_SUCCESS) && (V_OD_erg != SQL_SUCCESS_WITH_INFO))
    {
        printf("Error SQLConnect %d\n",V_OD_erg);
        SQLGetDiagRec(SQL_HANDLE_DBC, V_OD_hdbc,1,
         V_OD_stat, &V_OD_err,V_OD_msg,100,&V_OD_mlen);
        printf("%s (%d)\n",V_OD_msg,V_OD_err);
        SQLFreeHandle(SQL_HANDLE_DBC, V_OD_hdbc);
        SQLFreeHandle(SQL_HANDLE_ENV, V_OD_Env);
        exit(0);
    }
    printf("Connected !\n");


2. 查询数据库

步骤：

  （1）分配查询句柄

（2）根据查询字段类型，绑定表列到变量

（3）执行查询

（4）取出结果

 

涉及API定义：（之前出现的不再列出）

ODBC句柄——SQLHSTMT

句柄类型——SQL_HANDLE_STMT

数据类型——char，SQLINTEGER，SQLSMALLINT

指示符——SQL_C_CHAR，SQL_C_ULONG

      返回值——SQL_NO_DATA

接口函数：

     SQLBindCol，SQLExecDirect，SQLNumResultCols，SQLRowCount，SQLFetch

sample code

SQLHSTMT V_OD_hstmt; // Handle for a statement

SQLINTEGER V_OD_err,V_OD_id;
char V_OD_buffer[200];
SQLBindCol(V_OD_hstmt,1,SQL_C_CHAR, &V_OD_buffer,200,&V_OD_err);
SQLBindCol(V_OD_hstmt,2,SQL_C_ULONG,&V_OD_id,sizeof(V_OD_id),&V_OD_err);
V_OD_erg=SQLExecDirect(V_OD_hstmt,
             "SELECT dtname,iduser FROM tkeyuser order by iduser",SQL_NTS);
 if ((V_OD_erg != SQL_SUCCESS) && (V_OD_erg != SQL_SUCCESS_WITH_INFO))
    {
     printf("Error Select %d\n",V_OD_erg);
     SQLGetDiagRec(SQL_HANDLE_DBC, V_OD_hdbc,1, V_OD_stat, &V_OD_err,
     V_OD_msg,100,&V_OD_mlen);
     printf("%s (%d)\n",V_OD_msg,V_OD_err);
     SQLFreeHandle(SQL_HANDLE_STMT,V_OD_hstmt);
     SQLDisconnect(V_OD_hdbc);
     SQLFreeHandle(SQL_HANDLE_DBC,V_OD_hdbc);
     SQLFreeHandle(SQL_HANDLE_ENV, V_OD_Env);
     exit(0);
    }


3. 关闭连接

 步骤：

（1）关闭连接

（2）释放句柄

 

涉及API接口：

    SQLDisconnect

    SQLFreeHandle

sample code

     SQLFreeHandle(SQL_HANDLE_STMT,V_OD_hstmt);
     SQLDisconnect(V_OD_hdbc);
     SQLFreeHandle(SQL_HANDLE_DBC,V_OD_hdbc);
     SQLFreeHandle(SQL_HANDLE_ENV, V_OD_Env);

4. 完整样例代码

/* odbc.c
    testing unixODBC
*/
#include <stdlib.h>
#include <stdio.h>
#include <odbc/sql.h>
#include <odbc/sqlext.h>
#include <odbc/sqltypes.h>

SQLHENV             V_OD_Env;            // Handle ODBC environment

long             V_OD_erg;            // result of functions

SQLHDBC             V_OD_hdbc;            // Handle connection


char             V_OD_stat[10];        // Status SQL

SQLINTEGER         V_OD_err,V_OD_rowanz,V_OD_id;
SQLSMALLINT         V_OD_mlen,V_OD_colanz;
char V_OD_msg[200],V_OD_buffer[200];


int main(int argc,char *argv[])
{
    // 1. allocate Environment handle and register version

    V_OD_erg=SQLAllocHandle(SQL_HANDLE_ENV,SQL_NULL_HANDLE,&V_OD_Env);
    if ((V_OD_erg != SQL_SUCCESS) && (V_OD_erg != SQL_SUCCESS_WITH_INFO))
    {
        printf("Error AllocHandle\n");
        exit(0);
    }
    V_OD_erg=SQLSetEnvAttr(V_OD_Env, SQL_ATTR_ODBC_VERSION, (void*)SQL_OV_ODBC3, 0);
    if ((V_OD_erg != SQL_SUCCESS) && (V_OD_erg != SQL_SUCCESS_WITH_INFO))
    {
        printf("Error SetEnv\n");
        SQLFreeHandle(SQL_HANDLE_ENV, V_OD_Env);
        exit(0);
    }
    // 2. allocate connection handle, set timeout

    V_OD_erg = SQLAllocHandle(SQL_HANDLE_DBC, V_OD_Env, &V_OD_hdbc);
    if ((V_OD_erg != SQL_SUCCESS) && (V_OD_erg != SQL_SUCCESS_WITH_INFO))
    {
        printf("Error AllocHDB %d\n",V_OD_erg);
        SQLFreeHandle(SQL_HANDLE_ENV, V_OD_Env);
        exit(0);
    }
    SQLSetConnectAttr(V_OD_hdbc, SQL_LOGIN_TIMEOUT, (SQLPOINTER *)5, 0);
    // 3. Connect to the datasource "web"

    V_OD_erg = SQLConnect(V_OD_hdbc, (SQLCHAR*) "web", SQL_NTS,
                                     (SQLCHAR*) "christa", SQL_NTS,
                                     (SQLCHAR*) "", SQL_NTS);
    if ((V_OD_erg != SQL_SUCCESS) && (V_OD_erg != SQL_SUCCESS_WITH_INFO))
    {
        printf("Error SQLConnect %d\n",V_OD_erg);
        SQLGetDiagRec(SQL_HANDLE_DBC, V_OD_hdbc,1,
         V_OD_stat, &V_OD_err,V_OD_msg,100,&V_OD_mlen);
        printf("%s (%d)\n",V_OD_msg,V_OD_err);
        SQLFreeHandle(SQL_HANDLE_ENV, V_OD_Env);
        exit(0);
    }
    printf("Connected !\n");
    V_OD_erg=SQLAllocHandle(SQL_HANDLE_STMT, V_OD_hdbc, &V_OD_hstmt);
    if ((V_OD_erg != SQL_SUCCESS) && (V_OD_erg != SQL_SUCCESS_WITH_INFO))
    {
        printf("Fehler im AllocStatement %d\n",V_OD_erg);
        SQLGetDiagRec(SQL_HANDLE_DBC, V_OD_hdbc,1, V_OD_stat,&V_OD_err,V_OD_msg,100,&V_OD_mlen);
        printf("%s (%d)\n",V_OD_msg,V_OD_err);
        SQLDisconnect(V_OD_hdbc);
        SQLFreeHandle(SQL_HANDLE_DBC,V_OD_hdbc);
        SQLFreeHandle(SQL_HANDLE_ENV, V_OD_Env);
        exit(0);
    }
    SQLBindCol(V_OD_hstmt,1,SQL_C_CHAR, &V_OD_buffer,150,&V_OD_err);
    SQLBindCol(V_OD_hstmt,2,SQL_C_ULONG,&V_OD_id,150,&V_OD_err);
    
    V_OD_erg=SQLExecDirect(V_OD_hstmt,"SELECT dtname,iduser FROM tkeyuser order by iduser",SQL_NTS);
    if ((V_OD_erg != SQL_SUCCESS) && (V_OD_erg != SQL_SUCCESS_WITH_INFO))
    {
       printf("Error in Select %d\n",V_OD_erg);
       SQLGetDiagRec(SQL_HANDLE_DBC, V_OD_hdbc,1, V_OD_stat,&V_OD_err,V_OD_msg,100,&V_OD_mlen);
       printf("%s (%d)\n",V_OD_msg,V_OD_err);
       SQLFreeHandle(SQL_HANDLE_STMT,V_OD_hstmt);
       SQLDisconnect(V_OD_hdbc);
       SQLFreeHandle(SQL_HANDLE_DBC,V_OD_hdbc);
       SQLFreeHandle(SQL_HANDLE_ENV, V_OD_Env);
       exit(0);
    }
    V_OD_erg=SQLNumResultCols(V_OD_hstmt,&V_OD_colanz);
    if ((V_OD_erg != SQL_SUCCESS) && (V_OD_erg != SQL_SUCCESS_WITH_INFO))
    {
        SQLFreeHandle(SQL_HANDLE_STMT,V_OD_hstmt);
        SQLDisconnect(V_OD_hdbc);
        SQLFreeHandle(SQL_HANDLE_DBC,V_OD_hdbc);
        SQLFreeHandle(SQL_HANDLE_ENV, V_OD_Env);
        exit(0);
    }
    printf("Number of Columns %d\n",V_OD_colanz);
    V_OD_erg=SQLRowCount(V_OD_hstmt,&V_OD_rowanz);
    if ((V_OD_erg != SQL_SUCCESS) && (V_OD_erg != SQL_SUCCESS_WITH_INFO))
    {
      printf("Number ofRowCount %d\n",V_OD_erg);
      SQLFreeHandle(SQL_HANDLE_STMT,V_OD_hstmt);
      SQLDisconnect(V_OD_hdbc);
      SQLFreeHandle(SQL_HANDLE_DBC,V_OD_hdbc);
      SQLFreeHandle(SQL_HANDLE_ENV, V_OD_Env);
      exit(0);
    }
    printf("Number of Rows %d\n",V_OD_rowanz);
    V_OD_erg=SQLFetch(V_OD_hstmt);
    while(V_OD_erg != SQL_NO_DATA)
    {
     printf("Result: %d %s\n",V_OD_id,V_OD_buffer);
     V_OD_erg=SQLFetch(V_OD_hstmt);
    } ;
    SQLFreeHandle(SQL_HANDLE_STMT,V_OD_hstmt);
    SQLDisconnect(V_OD_hdbc);
    SQLFreeHandle(SQL_HANDLE_DBC,V_OD_hdbc);
    SQLFreeHandle(SQL_HANDLE_ENV, V_OD_Env);
    return(0);
}