首先进程数据库的相关配置,主要是修改/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时已经按缺省设置了,可是似乎这个路经作为一个参数存起来了,应用执行时就到这个地方找去配置文件去了.