unixODBC程序c

最新推荐文章于 2024-09-22 23:57:36 发布

zhoujunyi

最新推荐文章于 2024-09-22 23:57:36 发布

阅读量552

点赞数

CC 4.0 BY-SA版权

文章标签： database sql string insert query buffer

本文链接：https://blog.youkuaiyun.com/zhoujunyi/article/details/1612688

本文提供了一个使用ODBC进行数据库连接、插入、更新及查询操作的C语言示例程序。该程序首先初始化环境并建立数据库连接，随后执行SQL语句进行数据操作，并通过示例展示了如何处理查询结果。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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

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

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