关闭

ODBC 应用编程

标签: 编程sqlbuffersql serverloginapi
480人阅读 评论(1) 收藏 举报

转自:http://www.phpfans.net/article/htmls/201008/Mjk1NzY1.html

 

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

<!--[if !supportLists]-->

1.       <!--[endif]-->连接数据源

步骤:

<!--[if !supportLists]-->(1)       <!--[endif]-->分配ODBC环境句柄,设置句柄属性(设置ODBC版本)

<!--[if !supportLists]-->(2)       <!--[endif]-->分配连接句柄,设置连接属性(可以使用默认属性)

<!--[if !supportLists]-->(3)       <!--[endif]-->连接数据源

 

涉及的API定义:

句柄——SQLHENVSQLHDBC

句柄类型——SQL_HANDLE_ENVSQL_HANDLE_DBC

属性类型——SQL_ATTR_ODBC_VERSION

属性值——SQL_OV_ODBC3

指示符——SQL_NULL_HANDLESQL_NTS

返回值——SQL_SUCCESS SQL_SUCCESS_WITH_INFOSQL_ERROR

         SQL_INVALID_HANDLE

API函数:

     SQLAllocHandleSQLSetEnvAttrSQLSetConnectAttrSQLConnect
   
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

数据类型——charSQLINTEGERSQLSMALLINT

指示符——SQL_C_CHARSQL_C_ULONG

      返回值——SQL_NO_DATA

接口函数:

     SQLBindColSQLExecDirectSQLNumResultColsSQLRowCountSQLFetch

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

0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:15347次
    • 积分:287
    • 等级:
    • 排名:千里之外
    • 原创:9篇
    • 转载:3篇
    • 译文:0篇
    • 评论:1条
    文章存档
    最新评论