Chinaunix首页 | 论坛 | 博客
  • 博客访问: 408898
  • 博文数量: 47
  • 博客积分: 1488
  • 博客等级: 上尉
  • 技术积分: 729
  • 用 户 组: 普通用户
  • 注册时间: 2010-08-15 11:35
文章分类

全部博文(47)

文章存档

2012年(4)

2011年(22)

2010年(21)

分类: LINUX

2010-08-21 00:36:05

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


1.       连接数据源

步骤:

(1)       分配ODBC环境句柄,设置句柄属性(设置ODBC版本)

(2)       分配连接句柄,设置连接属性(可以使用默认属性)

(3)       连接数据源

 

涉及的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);
}


阅读(2862) | 评论(1) | 转发(1) |
给主人留下些什么吧!~~

chinaunix网友2011-01-01 15:13:19

很好的, 收藏了 推荐一个博客,提供很多免费软件编程电子书下载: http://free-ebooks.appspot.com