C通过unixODBC连接数据库

来源：互联网发布：java的区别和易语言编辑：程序博客网时间：2024/06/05 18:20

首先进程数据库的相关配置，主要是修改/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 environmentlong V_OD_erg; // result of functionsSQLHDBC V_OD_hdbc; // Handle connectionchar V_OD_stat[10]; // Status SQLSQLINTEGER 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时已经按缺省设置了,可是似乎这个路经作为一个参数存起来了,应用执行时就到这个地方找去配置文件去了.

0 0