C语言通过ODBC函数操作Access数据库（mdb和accdb格式）（char字符数组）

编译环境：Windows XP + Visual Studio 2010
数据库：Access 2010，accdb格式
本例程只使用char[]字符数组，不使用wchar_t[]字符数组，更适合C语言初学者。
如果读取字符串时，提供的字符数组空间小了，db_fetch会执行失败返回-1。
由于Windows系统设计原因，char[]字符数组只能存储GB2312编码的字符串，wchar_t[]字符数组只能存储UTF-16编码的字符串。如果是UTF-8编码的字符串（也用char[]数组存储），必须用MultiByteToWideChar函数转成UTF-16格式（用wchar_t[]数组存储，CodePage参数=CP_UTF8），再调用W版本的API函数操作数据库。

请注意，Visual Studio里面使用以_s结尾的函数时，凡是参数名是maxcount的，都要减1，否则程序会闪退。
_snprintf_s函数的正确用法：
_snprintf_s(str, sizeof(str), sizeof(str) - 1, "fmt"); // C语言专用
_snprintf_s(str, sizeof(str) - 1, "fmt"); // C++专用, 和上面的语句等效

如果系统是32位，请编译并运行32位版本的程序；如果系统是64位，请编译并运行64位版本的程序，否则无法成功连接数据库。

主代码（main.c）：

#include <stdio.h>
#include "db.h"

void show_products()
{
	char *sql;
	char name[50];
	double price;
	int id, num;
	size_t namelen;
	Statement stmt;

	sql = "SELECT [产品ID], [产品名称], [单价], [库存量] FROM [产品] WHERE [产品ID] <= 4 ORDER BY [产品ID]";
	stmt = db_query(sql);
	if (stmt != NULL)
	{
		db_bind_int(stmt, 1, &id);
		db_bind_str(stmt, 2, name, sizeof(name), &namelen);
		db_bind_double(stmt, 3, &price);
		db_bind_int(stmt, 4, &num);
		while (db_fetch(stmt) == 1)
			printf("%d %s(len=%d) %.2lf %d\n", id, name, (int)namelen, price, num);
		db_free(stmt);
	}

	sql = "SELECT * FROM [产品] WHERE [产品名称] = '苏打水'";
	printf("exists: %d\n", db_has_records(sql));
}

void find_product_by_id(int id)
{
	char *sql;
	char name[50];
	Statement stmt;

	sql = "SELECT [产品名称], [单价], [库存量] FROM [产品] WHERE [产品ID] = ?";
	stmt = db_prepare(sql);
	if (stmt != NULL)
	{
		db_set_int(stmt, 1, &id);
		db_exec_stmt(stmt, 0);
		while (db_fetch(stmt) == 1)
		{
			printf("find %d: %s %.2f %d\n", id, db_get_str(stmt, 1, name, sizeof(name), NULL), db_get_float(stmt, 2), db_get_int(stmt, 3));
		}
		db_free(stmt);
	}
}

void find_product_by_name(const char *name)
{
	char *sql;
	Statement stmt;

	sql = "SELECT [产品ID], [单价], [库存量] FROM [产品] WHERE [产品名称] = ?";
	stmt = db_prepare(sql);
	if (stmt != NULL)
	{
		db_set_str(stmt, 1, name);
		db_exec_stmt(stmt, 0);
		while (db_fetch(stmt) == 1)
			printf("find %s: %d %.2lf %d\n", name, db_get_int(stmt, 1), db_get_double(stmt, 2), db_get_int(stmt, 3));
		db_free(stmt);
	}
}

void update_products()
{
	char *sql;
	double dval;
	int cnt;
	Statement stmt;

	sql = "UPDATE [产品] SET [库存量] = [库存量] + 1 WHERE [产品ID] = 1";
	db_exec(sql, &cnt);
	printf("update: cnt=%d\n", cnt);

	sql = "UPDATE [产品] SET [库存量] = [库存量] + 1 WHERE [产品名称] = ?";
	stmt = db_prepare(sql);
	if (stmt != NULL)
	{
		db_set_str(stmt, 1, "牛奶");
		db_exec_stmt(stmt, 0);
		cnt = db_affected_rows(stmt);
		printf("update: cnt=%d\n", cnt);
		db_free(stmt);
	}

	sql = "UPDATE [产品] SET [单价] = [单价] + ? WHERE [产品名称] = ?";
	stmt = db_prepare(sql);
	if (stmt != NULL)
	{
		dval = 0.25;
		db_set_double(stmt, 1, &dval);
		db_set_str(stmt, 2, "盐");
		db_exec_stmt(stmt, 0);
		cnt = db_affected_rows(stmt);
		printf("update: cnt=%d\n", cnt);
		db_free(stmt);
	}
}

int main()
{
	int ret;

	ret = db_connect("test.accdb");
	if (ret == -1)
	{
		printf("%s\n", db_geterror());
		return -1;
	}

	show_products();
	find_product_by_id(14);
	find_product_by_name("饼干");
	update_products();

	db_disconnect();
	return 0;
}

db.h：

#pragma once

typedef void *Statement;

/* 数据库连接与断开 */
int db_connect(const char *filename);
void db_disconnect();
const char *db_geterror();

/* Prepared Statement 相关 */
Statement db_prepare(const char *sql);
int db_exec_stmt(Statement stmt, int free);
// 设置SQL语句中的问号
void db_set_int(Statement stmt, int i, const int *p);
void db_set_str(Statement stmt, int i, const char *s);
void db_set_float(Statement stmt, int i, const float *p);
void db_set_double(Statement stmt, int i, const double *p);

/* 直接执行查询，不Prepare */
Statement db_query(const char *sql); // 执行SELECT查询，需要手动释放资源
int db_affected_rows(Statement stmt); // 获取INSERT、UPDATE和DELETE语句影响的行数
int db_exec(const char *sql, int *affected_cnt); // 执行普通查询
int db_has_records(const char *sql); // 直接执行SELECT查询，判断结果集是否有记录
void db_free(Statement stmt); // 释放db_query的资源

/* 记录集操作 */
int db_fetch(Statement stmt); // 获取一行记录
// 绑定字段到变量上
void db_bind_int(Statement stmt, int col, int *p); // 以整数类型保存第col列的内容
void db_bind_str(Statement stmt, int col, char *buf, int len, size_t *real_len); // 以字符串类型保存第col列的内容
void db_bind_float(Statement stmt, int col, float *p); // 以小数类型保存第col列的内容
void db_bind_double(Statement stmt, int col, double *p);
// 直接读取字段内容
int db_get_int(Statement stmt, int col); // 直接以整数类型读取第col列的内容
char *db_get_str(Statement stmt, int col, char *buf, int len, size_t *real_len); // 直接以字符串类型读取第col列的内容
float db_get_float(Statement stmt, int col); // 直接以小数类型读取第col列的内容
double db_get_double(Statement stmt, int col);

db.c：

/* 
这个文件里封装了很多操作数据库的函数
参考资料：https://msdn.microsoft.com/en-us/library/ms714562%28v=vs.85%29.aspx
*/
#include <stdio.h>
#include <Windows.h>
#include <sqlext.h>
#include "db.h"

#define DB_DSN "Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=%s;"

static char db_errmsg[500];
static BOOL db_connected;
static SQLHENV db_env;
static SQLHDBC db_dbc;

int db_connect(const char *filename)
{
	char fullpath[MAX_PATH] = ""; // 数据库文件完整路径
	char dsn[MAX_PATH + 100]; // 数据库连接字符串
	char code[6]; // 错误代码
	char msg[500]; // 错误信息
	char *p;
	SQLRETURN rc;

	rc = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &db_env);
	if (!SQL_SUCCEEDED(rc))
		goto err;
	rc = SQLSetEnvAttr(db_env, SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC3, 0);
	if (!SQL_SUCCEEDED(rc))
		goto err;
	rc = SQLAllocHandle(SQL_HANDLE_DBC, db_env, &db_dbc);
	if (!SQL_SUCCEEDED(rc))
		goto err;

	// 根据数据库文件名生成数据库连接字符串
	if (filename[1] != ':')
	{
		GetModuleFileNameA(NULL, fullpath, sizeof(fullpath));
		p = strrchr(fullpath, '\\');
		if (p != NULL)
			*(p + 1) = '\0';
	}
	strncat_s(fullpath, sizeof(fullpath), filename, sizeof(fullpath) - 1);
	_snprintf_s(dsn, sizeof(dsn), sizeof(dsn) - 1, DB_DSN, fullpath);
	
	// 根据连接字符串连接数据库
	rc = SQLDriverConnectA(db_dbc, NULL, dsn, SQL_NTS, NULL, 0, NULL, 0);
	if (!SQL_SUCCEEDED(rc))
	{
		// 获取连接失败的错误提示
		rc = SQLGetDiagRecA(SQL_HANDLE_DBC, db_dbc, 1, code, NULL, msg, sizeof(msg), NULL);
		goto err;
	}
	db_connected = TRUE;
	return 0; // 连接成功时，函数返回0
	
err:
	db_disconnect();
	if (SQL_SUCCEEDED(rc))
		_snprintf_s(db_errmsg, sizeof(db_errmsg), sizeof(db_errmsg) - 1, "连接数据库失败。\n错误代码: %s\n错误信息: %s", code, msg);
	else
		strcpy_s(db_errmsg, sizeof(db_errmsg), "未知错误。");
	return -1; // 连接失败时，函数返回-1
}

void db_disconnect()
{
	if (db_dbc != SQL_NULL_HDBC)
	{
		if (db_connected)
		{
			SQLDisconnect(db_dbc);
			db_connected = FALSE;
		}
		SQLFreeHandle(SQL_HANDLE_DBC, db_dbc);
		db_dbc = SQL_NULL_HDBC;
	}
	if (db_env != SQL_NULL_HENV)
	{
		SQLFreeHandle(SQL_HANDLE_ENV, db_env);
		db_env = SQL_NULL_HENV;
	}
}

const char *db_geterror()
{
	return db_errmsg;
}

Statement db_prepare(const char *sql)
{
	SQLHSTMT stmt;
	SQLRETURN rc;
 
	rc = SQLAllocHandle(SQL_HANDLE_STMT, db_dbc, &stmt);
	if (!SQL_SUCCEEDED(rc))
		return NULL;

	SQLPrepareA(stmt, (char *)sql, (int)strlen(sql));
	return stmt;
}

int db_exec_stmt(Statement stmt, int free)
{
	SQLRETURN rc;
	
	rc = SQLExecute(stmt);
	if (free)
		db_free(stmt);
 
	if (!SQL_SUCCEEDED(rc))
		return -1;
	return 0;
}

void db_set_int(Statement stmt, int i, const int *p)
{
	static SQLLEN size = sizeof(int); // 该变量的地址必须固定, 所以要加static
 
	SQLBindParameter(stmt, i, SQL_PARAM_INPUT, SQL_C_LONG, SQL_INTEGER, 0, 0, (int *)p, 0, &size); // 整型变量的地址也必须固定
}
 
void db_set_str(Statement stmt, int i, const char *s)
{
	static SQLLEN len = SQL_NTS;
 
	SQLBindParameter(stmt, i, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_VARCHAR, strlen(s), 0, (char *)s, 0, &len);
}

void db_set_float(Statement stmt, int i, const float *p)
{
	static SQLLEN size = sizeof(float);
 
	SQLBindParameter(stmt, i, SQL_PARAM_INPUT, SQL_C_FLOAT, SQL_FLOAT, 0, 0, (float *)p, 0, &size);
}

void db_set_double(Statement stmt, int i, const double *p)
{
	static SQLLEN size = sizeof(double);
 
	SQLBindParameter(stmt, i, SQL_PARAM_INPUT, SQL_C_DOUBLE, SQL_DOUBLE, 0, 0, (double *)p, 0, &size);
}

Statement db_query(const char *sql)
{
	SQLRETURN rc;
	Statement stmt;
	
	stmt = db_prepare(sql);
	if (stmt == NULL)
		return NULL;

	rc = SQLExecute(stmt);
	if (!SQL_SUCCEEDED(rc))
	{
		db_free(stmt);
		return NULL;
	}
	return stmt;
}

// 此函数只能用于获取INSERT、UPDATE和DELETE语句影响的行数
// 无法获取SELECT语句查询返回的记录集的行数
int db_affected_rows(Statement stmt)
{
	SQLLEN cnt;
	SQLRETURN rc;

	if (stmt == NULL)
		return -1;
 
	rc = SQLRowCount(stmt, &cnt);
	if (!SQL_SUCCEEDED(rc))
		return -1;
	return (int)cnt;
}

int db_exec(const char *sql, int *affected_cnt)
{
	Statement stmt;
	
	stmt = db_query(sql);
	if (affected_cnt != NULL)
		*affected_cnt = db_affected_rows(stmt);
	if (stmt == NULL)
		return -1;

	db_free(stmt);
	return 0;
}

int db_has_records(const char *sql)
{
	int ret;
	Statement stmt;
	
	stmt = db_query(sql);
	if (stmt == NULL)
		return 0;
 
	ret = db_fetch(stmt);
	db_free(stmt);
	return ret;
}

void db_free(Statement stmt)
{
	SQLFreeHandle(SQL_HANDLE_STMT, stmt);
}

int db_fetch(Statement stmt)
{
	SQLRETURN rc;
 
	rc = SQLFetch(stmt);
	if (rc == SQL_SUCCESS)
		return 1;
	else if (rc == SQL_NO_DATA)
		return 0;
	else
		return -1;
}

void db_bind_int(Statement stmt, int col, int *p)
{
	SQLBindCol(stmt, col, SQL_C_LONG, p, sizeof(int), NULL);
}
 
void db_bind_str(Statement stmt, int col, char *buf, int len, size_t *real_len)
{
	SQLBindCol(stmt, col, SQL_C_CHAR, buf, len, real_len);
}
 
void db_bind_float(Statement stmt, int col, float *p)
{
	SQLBindCol(stmt, col, SQL_C_FLOAT, p, sizeof(float), NULL);
}
 
void db_bind_double(Statement stmt, int col, double *p)
{
	SQLBindCol(stmt, col, SQL_C_DOUBLE, p, sizeof(double), NULL);
}
 
int db_get_int(Statement stmt, int col)
{
	int n;
	SQLRETURN ret;
 
	ret = SQLGetData(stmt, col, SQL_C_LONG, &n, sizeof(int), NULL);
	if (SUCCEEDED(ret))
		return n;
	else
		return 0;
}

char *db_get_str(Statement stmt, int col, char *buf, int len, size_t *real_len)
{
	SQLRETURN ret;

	ret = SQLGetData(stmt, col, SQL_C_CHAR, buf, len, real_len);
	if (!SUCCEEDED(ret))
	{
		buf[0] = '\0';
		if (real_len != NULL)
			*real_len = 0;
	}
	return buf;
}

float db_get_float(Statement stmt, int col)
{
	float n;
	SQLRETURN ret;
 
	ret = SQLGetData(stmt, col, SQL_C_FLOAT, &n, sizeof(float), NULL);
	if (SUCCEEDED(ret))
		return n;
	else
		return 0;
}

double db_get_double(Statement stmt, int col)
{
	double n;
	SQLRETURN ret;
 
	ret = SQLGetData(stmt, col, SQL_C_DOUBLE, &n, sizeof(double), NULL);
	if (SUCCEEDED(ret))
		return n;
	else
		return 0;
}