C通过unixODBC连接数据库 - young525

首先进程数据库的相关配置，主要是修改/etc/odbcinst.ini和/etc/odbc.ini两个文件。（这是它们的默认位置，有时也可能在/usr/local/etc/或者/usr/local/unixODBC/etc/，这个和你安装的unixODBC位置有关）

odbcinst.ini（配置驱动）

点击(此处)折叠或打开

[mysql]

Driver=/usr/local/lib/libmyodbc5.so

SETUP=/usr/local/lib/libmyodbc5.so

UsageCount=1

odbc.ini（配置连接）

点击(此处)折叠或打开

[mysql]

Description = The Database for mysql

Trace = On

TraceFile = stderr

Driver = mysql

SERVER = 192.168.1.229

USER = roam

PASSWORD = wd_roam

PORT = 3306

DATABASE = roam_t

charset = UTF8

option = 3

最后测试：
isql mysql

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

#include "logfile.h"
#include "global.h"

pthread_mutex_t database_control_mutex = PTHREAD_MUTEX_INITIALIZER;

SQLHDBC V_OD_hstmt;
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];

BOOL connect_database()
{
    sem_take(&database_control_mutex);

    // 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))
    {
        monitor_log(ERROR,"Error AllocHandle");
        sem_give(&database_control_mutex);
        return FALSE;
    }

    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))
    {
        monitor_log(ERROR,"Error SetEnv");
        SQLFreeHandle(SQL_HANDLE_ENV, V_OD_Env);
        sem_give(&database_control_mutex);
        return FALSE;
    }

    // 2. allocate connection handle,and then 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))
    {
        monitor_log(ERROR,"Error AllocHDB %d",V_OD_erg);
        SQLFreeHandle(SQL_HANDLE_ENV, V_OD_Env);
        sem_give(&database_control_mutex);
        return FALSE;
    }
    SQLSetConnectAttr(V_OD_hdbc, SQL_LOGIN_TIMEOUT, (SQLPOINTER *)5, 0);

    // 3. Connect to the datasource "network_monitor"
    V_OD_erg = SQLConnect(V_OD_hdbc,
                          (SQLCHAR*) "network_monitor", SQL_NTS,
                          (SQLCHAR*) "netmonitor", SQL_NTS,
                          (SQLCHAR*) "voipack", SQL_NTS);

    if ((V_OD_erg != SQL_SUCCESS) && (V_OD_erg != SQL_SUCCESS_WITH_INFO))
    {
        monitor_log(ERROR,"Error SQLConnect %d",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)",V_OD_msg,V_OD_err);
        SQLFreeHandle(SQL_HANDLE_ENV, V_OD_Env);
        sem_give(&database_control_mutex);
        return FALSE;
    }
    monitor_log(SYSLOG,"Database Connected !/n");
    sem_give(&database_control_mutex);
    return TRUE;
}

BOOL close_database()
{
    sem_take(&database_control_mutex);
    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);
    monitor_log(SYSLOG,"Database Closed !/n");
    sem_give(&database_control_mutex);
    return TRUE;
}

void insert_database(char *sql_string)
{
    sem_take(&database_control_mutex);
    // allocate sql connection handle,and execute query
    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))
    {
        monitor_log(ERROR,"Fehler im AllocStatement %d",V_OD_erg);
        SQLGetDiagRec(SQL_HANDLE_DBC, V_OD_hdbc,1, V_OD_stat,&V_OD_err,V_OD_msg,100,&V_OD_mlen);
        monitor_log(ERROR,"%s (%d)",V_OD_msg,V_OD_err);
        SQLFreeHandle(SQL_HANDLE_ENV, V_OD_Env);
        sem_give(&database_control_mutex);
        return ;
    }
    // execute sql
    V_OD_erg=SQLExecDirect(V_OD_hstmt,sql_string,SQL_NTS);

    if ((V_OD_erg != SQL_SUCCESS) && (V_OD_erg != SQL_SUCCESS_WITH_INFO))
    {
        monitor_log(ERROR,"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);
        monitor_log(ERROR,"%s (%d)/n",V_OD_msg,V_OD_err);
        SQLFreeHandle(SQL_HANDLE_STMT,V_OD_hstmt);
        SQLFreeHandle(SQL_HANDLE_DBC,V_OD_hdbc);
        SQLFreeHandle(SQL_HANDLE_ENV, V_OD_Env);
        sem_give(&database_control_mutex);
        return;
    }

    sem_give(&database_control_mutex);
    return;
}
void update_database(char *sql_string)
{
    sem_take(&database_control_mutex);

    // allocate sql connection handle,and execute query
    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))
    {
        monitor_log(ERROR,"Fehler im AllocStatement %d",V_OD_erg);
        SQLGetDiagRec(SQL_HANDLE_DBC, V_OD_hdbc,1, V_OD_stat,&V_OD_err,V_OD_msg,100,&V_OD_mlen);
        monitor_log(ERROR,"%s (%d)",V_OD_msg,V_OD_err);
        SQLFreeHandle(SQL_HANDLE_ENV, V_OD_Env);
        sem_give(&database_control_mutex);
        return ;
    }
    // execute sql
    V_OD_erg=SQLExecDirect(V_OD_hstmt,sql_string,SQL_NTS);

    if ((V_OD_erg != SQL_SUCCESS) && (V_OD_erg != SQL_SUCCESS_WITH_INFO))
    {
        monitor_log(ERROR,"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);
        monitor_log(ERROR,"%s (%d)/n",V_OD_msg,V_OD_err);
        SQLFreeHandle(SQL_HANDLE_STMT,V_OD_hstmt);
        SQLFreeHandle(SQL_HANDLE_DBC,V_OD_hdbc);
        SQLFreeHandle(SQL_HANDLE_ENV, V_OD_Env);
        sem_give(&database_control_mutex);
        return;
    }

    sem_give(&database_control_mutex);
    return;
}
void query_database(char *sql_string)
{
    sem_take(&database_control_mutex);
    // allocate sql connection handle,and execute query
    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))
    {
        monitor_log(ERROR,"Fehler im AllocStatement %d",V_OD_erg);
        SQLGetDiagRec(SQL_HANDLE_DBC, V_OD_hdbc,1, V_OD_stat,&V_OD_err,V_OD_msg,100,&V_OD_mlen);
        monitor_log(ERROR,"%s (%d)",V_OD_msg,V_OD_err);
        SQLFreeHandle(SQL_HANDLE_ENV, V_OD_Env);
        sem_give(&database_control_mutex);
        return ;
    }
    SQLBindCol(V_OD_hstmt,1,SQL_C_ULONG,&V_OD_id,150,&V_OD_err);
    SQLBindCol(V_OD_hstmt,2,SQL_C_CHAR, &V_OD_buffer,150,&V_OD_err);

    // execute sql
    V_OD_erg=SQLExecDirect(V_OD_hstmt,sql_string,SQL_NTS);

    if ((V_OD_erg != SQL_SUCCESS) && (V_OD_erg != SQL_SUCCESS_WITH_INFO))
    {
        monitor_log(ERROR,"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);
        monitor_log(ERROR,"%s (%d)/n",V_OD_msg,V_OD_err);
        SQLFreeHandle(SQL_HANDLE_STMT,V_OD_hstmt);
        SQLFreeHandle(SQL_HANDLE_DBC,V_OD_hdbc);
        SQLFreeHandle(SQL_HANDLE_ENV, V_OD_Env);
        sem_give(&database_control_mutex);
        return;
    }
    // output the result
    // get the column counts
    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);
        sem_give(&database_control_mutex);
        return;
    }
    monitor_log(SYSLOG,"Number of Columns %d/n",V_OD_colanz);
    //get the row counts
    V_OD_erg=SQLRowCount(V_OD_hstmt,&V_OD_rowanz);
    if ((V_OD_erg != SQL_SUCCESS) && (V_OD_erg != SQL_SUCCESS_WITH_INFO))
    {
        monitor_log(ERROR,"Number of RowCount %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);
        sem_give(&database_control_mutex);
        return;
    }
    monitor_log(SYSLOG,"Number of Rows %d/n",V_OD_rowanz);
    V_OD_erg=SQLFetch(V_OD_hstmt);
    while(V_OD_erg != SQL_NO_DATA)
    {
        monitor_log(SYSLOG,"Result: %s %d/n",V_OD_buffer,V_OD_id);
        V_OD_erg=SQLFetch(V_OD_hstmt);
    } ;
    sem_give(&database_control_mutex);
    return;
}

int main(int argc,char *argv[])
{
    char *p = "select * from http";
    char *insert = "insert into http(s_ip,d_ip,s_port,d_port,accesstime,url,filename,filepath,isimportance) values(/"1/",/"2/",/"3/",/"4/",/"5/",/"6/",/"7/",/"8/",/"1/")";
    char *update = "update http set s_ip = /"thisis/" where s_ip = /"1/"";
    monitor_log_Init();
    monitor_log(SYSLOG,"starting.....");
    connect_database();
    //insert_database(insert);
    update_database(update);
    query_database(p);
    close_database();
    return(0);
}

如果用unixodbc的测试程序isql已经可以连通并访问目标数据库,但是使用API做同样的事就失败，可能需要进程如下操作：

将/usr/local/etc/odbcinst.ini拷贝到
/usr/local/unixODBC/etc/odbcinst.ini后就可以了.
/usr/local/unixODBC/只是最早安装unixodbc时指定的目录,后面安装unixodbc时已经按缺省设置了,可是似乎这个路经作为一个参数存起来了,应用执行时就到这个地方找去配置文件去了.

发表于 2015-04-30 09:59 young525 阅读(1010) 评论(0) 编辑收藏举报