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

巨大八爪鱼

已于 2024-01-16 22:47:38 修改

阅读量744

点赞数 9

分类专栏： Win32 文章标签： c语言数据库开发语言

于 2024-01-11 22:19:25 首次发布

本文链接：https://blog.csdn.net/ZLK1214/article/details/135540171

版权

Win32 专栏收录该内容

22 篇文章 2 订阅

订阅专栏

编译环境：Windows XP + Visual Studio 2010
数据库：Access 2010，accdb格式
本例程只使用char[]字符数组，不使用wchar_t[]字符数组，更适合C语言初学者。
如果读取字符串时，db_bind_str提供的字符数组空间小了，db_fetch会执行失败返回-2。
由于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 <stdlib.h>
#include "db.h"

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

	sql = "SELECT [产品ID], [产品名称], [单价], [库存量] FROM [产品] WHERE [产品ID] <= 4 ORDER BY [产品ID]";
	stmt = db_query(sql);
	if (stmt != NULL)
	{
		printf("num_rows=%d\n", db_num_rows(stmt));
		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);
			printf("remaining_num_rows=%d\n", db_num_rows(stmt));
		}
		db_free(stmt);
	}

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

	//sql = "SELECT [产品名称], [单价] FROM [产品] WHERE [产品名称] LIKE '虾%'";
	sql = "SELECT [产品名称], [单价] FROM [产品], [供应商] WHERE [产品].[供应商ID] = [供应商].[供应商ID] AND [公司名称] = '义美'";
	stmt = db_query(sql);
	if (stmt != NULL)
	{
		printf("num_rows=%d\n", db_num_rows(stmt));
		db_bind_str(stmt, 1, name, sizeof(name), NULL);
		db_bind_float(stmt, 2, &price2);
		while (db_fetch(stmt) == 1)
			printf("%s %.2f\n", name, price2);
		db_free(stmt);
	}
}

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);
		printf("num_rows=%d\n", db_num_rows(stmt));
		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);
		printf("num_rows=%d\n", db_num_rows(stmt));
		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;
	float fval;
	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);

		fval = 1.75f;
		db_set_float(stmt, 1, &fval);
		db_set_str(stmt, 2, "蕃茄酱");
		db_exec_stmt(stmt, 0);
		cnt = db_affected_rows(stmt);
		printf("update2: cnt=%d\n", cnt);
		db_free(stmt);
	}
}

void show_customs()
{
	char *sql;
	char name[10];
	char *p;
	int col;
	intptr_t real_len;
	Statement stmt;

	sql = "SELECT * FROM [客户] WHERE [邮政编码] LIKE '1%' ORDER BY [邮政编码] ASC";
	stmt = db_query(sql);
	if (stmt != NULL)
	{
		printf("num_rows=%d\n", db_num_rows(stmt));
		col = db_find_column(stmt, "公司名称");
		printf("col=%d\n", col);

		while (db_fetch(stmt) == 1)
		{
			db_get_str(stmt, col, name, sizeof(name), &real_len);
			if (real_len + 1 <= sizeof(name))
				printf("公司名称: %s\n", name);
			else
			{
				p = malloc(real_len + 1);
				if (p != NULL)
				{
					db_refetch(stmt);
					db_get_str(stmt, col, p, (int)real_len + 1, &real_len);
					printf("*公司名称: %s\n", p);
					free(p);
				}
			}
		}
		db_free(stmt);
	}
}

void show_addresses()
{
	char *sql;
	char *mem, *p;
	int capacity = 15;
	int col, ret;
	intptr_t real_len;
	Statement stmt;

	sql = "SELECT * FROM [客户] WHERE [邮政编码] LIKE '1%' ORDER BY [邮政编码] DESC";
	stmt = db_query(sql);
	if (stmt != NULL)
	{
		printf("num_rows=%d\n", db_num_rows(stmt));
		col = db_find_column(stmt, "地址");
		printf("col=%d\n", col);

		mem = malloc(capacity);
		if (mem != NULL)
		{
			db_bind_str(stmt, col, mem, capacity, &real_len);
			while (1)
			{
				ret = db_fetch(stmt);
				if (ret == 0 || ret == -1)
					break;
				
				if (ret == -2)
				{
					if (real_len + 1 < capacity)
						break;
					capacity = (int)real_len + 1;
					printf("realloc capacity=%d\n", capacity);
					p = realloc(mem, capacity);
					if (p == NULL)
						break;
					mem = p;
					db_bind_str(stmt, col, mem, capacity, &real_len);

					ret = db_refetch(stmt);
					if (ret != 1)
						break;
				}

				// 这里一定要注意空值的处理
				// 如果当前行为null, 则mem为上一行的内容, 且real_len=-1
				if (real_len == -1)
					mem[0] = '\0';
				printf("客户地址: %s\n", mem);
			}
			free(mem);
		}
		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();

	show_customs();
	show_addresses();

	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_num_rows(Statement stmt); // 获取SELECT语句返回的行数
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); // 获取一行记录
int db_refetch(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, intptr_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, intptr_t *real_len); // 直接以字符串类型读取第col列的内容
float db_get_float(Statement stmt, int col); // 直接以小数类型读取第col列的内容
double db_get_double(Statement stmt, int col);

/* 查找指定列名 */
int db_get_column_name(Statement stmt, int col, char *buf, int len);
int db_find_column(Statement stmt, const char *name);

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;

	db_errmsg[0] = '\0';
	if (db_connected)
		return -1;

	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;

	rc = SQLSetStmtAttr(stmt, SQL_ATTR_CURSOR_TYPE, (SQLPOINTER)SQL_CURSOR_STATIC, 0);
	if (!SQL_SUCCEEDED(rc))
	{
		db_free(stmt);
		return NULL;
	}

	rc = SQLPrepareA(stmt, (char *)sql, (int)strlen(sql));
	if (!SQL_SUCCEEDED(rc))
	{
		db_free(stmt);
		return NULL;
	}
	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, (SQLPOINTER)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, (SQLPOINTER)s, 0, &len);
}

void db_set_float(Statement stmt, int i, const float *p)
{
	static SQLLEN size = sizeof(float);

	// SQL_C_FLOAT只能搭配SQL_REAL
	// 详见https://learn.microsoft.com/en-us/sql/odbc/reference/appendixes/converting-data-from-c-to-sql-data-types?view=sql-server-ver16
	SQLBindParameter(stmt, i, SQL_PARAM_INPUT, SQL_C_FLOAT, SQL_REAL, 0, 0, (SQLPOINTER)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, (SQLPOINTER)p, 0, &size);
}

Statement db_query(const char *sql)
{
	SQLHSTMT stmt;
	SQLRETURN rc;

	rc = SQLAllocHandle(SQL_HANDLE_STMT, db_dbc, &stmt);
	if (!SQL_SUCCEEDED(rc))
		return NULL;

	rc = SQLSetStmtAttr(stmt, SQL_ATTR_CURSOR_TYPE, (SQLPOINTER)SQL_CURSOR_STATIC, 0);
	if (!SQL_SUCCEEDED(rc))
	{
		db_free(stmt);
		return NULL;
	}

	rc = SQLExecDirectA(stmt, (char *)sql, (int)strlen(sql));
	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_num_rows(Statement stmt)
{
	int count = 0;

	while (db_fetch(stmt) == 1)
		count++;
	SQLFetchScroll(stmt, SQL_FETCH_RELATIVE, -(count + 1));
	return count;
}

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_SUCCESS_WITH_INFO)
		return -2;
	else if (rc == SQL_NO_DATA)
		return 0;
	else
		return -1;
}

int db_refetch(Statement stmt)
{
	SQLRETURN rc;

	rc = SQLFetchScroll(stmt, SQL_FETCH_RELATIVE, 0);
	if (rc == SQL_SUCCESS)
		return 1;
	else if (rc == SQL_SUCCESS_WITH_INFO)
		return -2;
	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);
}

// 若db_bind_str函数绑定的字符数组太小，则db_fetch()失败返回-2，失败后可重新绑定*real_len+1大小的字符数组, 再db_refetch一下
// 请一定要记得判断空值 (*real_len=-1)
void db_bind_str(Statement stmt, int col, char *buf, int len, intptr_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;
}

// 若db_get_str函数传入的字符数组太小，则db_get_str()失败返回NULL，失败后先db_refetch再通过*real_len+1大小的字符数组重新获取
// 如果字段为null值, 则函数返回空字符串且*real_len=SQL_NULL_DATA=-1
char *db_get_str(Statement stmt, int col, char *buf, int len, intptr_t *real_len)
{
	intptr_t temp;
	SQLRETURN ret;

	if (real_len == NULL)
		real_len = &temp;

	buf[0] = '\0';
	ret = SQLGetData(stmt, col, SQL_C_CHAR, buf, len, real_len);
	if (ret == SQL_SUCCESS)
		return buf;
	else if (ret == SQL_SUCCESS_WITH_INFO)
		return NULL; // buf里面存的是已截断的不完整字符串
	else
		return NULL;
}

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

int db_get_column_name(Statement stmt, int col, char *buf, int len)
{
	SQLRETURN ret;
	SQLSMALLINT namelen;

	ret = SQLDescribeColA(stmt, col, buf, len, &namelen, NULL, NULL, NULL, NULL);
	if (!SUCCEEDED(ret))
		return -1;
	return namelen; // 这个长度值不一定正确, 不能依赖这个值
}

int db_find_column(Statement stmt, const char *name)
{
	char buf[100];
	SQLRETURN ret;
	SQLSMALLINT i, n;
	
	ret = SQLNumResultCols(stmt, &n);
	if (!SUCCEEDED(ret))
		return -1;

	for (i = 1; i <= n; i++)
	{
		db_get_column_name(stmt, i, buf, sizeof(buf));
		if (strcmp(buf, name) == 0)
			return i;
	}
	return -1;
}

程序运行结果：

num_rows=4
1 苹果汁(len=6) 18.00 255
remaining_num_rows=3
2 牛奶(len=4) 19.00 227
remaining_num_rows=2
3 蕃茄酱(len=6) 265.50 13
remaining_num_rows=1
4 盐(len=2) 63.50 53
remaining_num_rows=0
exists: 1
num_rows=5
烤肉酱 45.60
鸭肉 123.79
黄豆 33.25
浓缩咖啡 7.75
辣椒粉 13.00
num_rows=1
find 14: 沙茶 23.25 35
num_rows=1
find 饼干: 16 17.45 29
update: cnt=1
update: cnt=1
update: cnt=1
update2: cnt=1
num_rows=8
col=2
公司名称: 宇欣实业
公司名称: 永业房屋
*公司名称: 威航货运有限公司
公司名称: 仪和贸易
公司名称: 光远商贸
公司名称:
公司名称: 幸义房屋
*公司名称: 凯诚国际顾问公司
num_rows=8
col=5
客户地址: 威刚街 481 号
客户地址: 七一路 89 号
客户地址: 英雄山路 84 号
realloc capacity=16
客户地址: 成川东街 951 号
realloc capacity=17
客户地址: 经三纬四路 18 号
客户地址: 经七纬二路 13 号
客户地址:
客户地址: 大峪口街 702 号
请按任意键继续. . .

证明：当SQLDescribeColA函数传入的szColName字符数组的大小小于字符串的实际长度时，函数返回的pcbColName值不正确。
图中strlen(name)=6，但namelen却等于4。

扩展阅读

在简体中文Windows系统下，char[]字符数组使用的编码为GB2312。
如果想要使用Unicode编码，就必须改用wchar_t[]字符数组。
Unicode编码有两种存储方式：UTF-8（变长方式存储）和UTF-16（定长方式存储）。
UTF-8是一种变长存储方式，每种语言的字符占用的字节数不一样。如每个英文字母占1个字节，但一个汉字要占3个字节。
UTF-16是一种定长存储方式，在基本多语言平面内，每种语言的字符占用的字节数都是两个字节。

在C语言中，GB2312和UTF-8编码的字符串都用char[]字符数组存储，但UTF-16编码的字符串用wchar_t[]宽字符数组存储。
双引号字符串使用的编码和.c源文件的编码相同。通常Visual Studio里面的C文件都是用GB2312编码保存的，所以char str[]="简体中文"是GB2312编码的字符串。
无论.c源文件是什么编码，带前导L的双引号字符串始终为UTF-16编码，所以wchar_t wstr[]=L"简体中文"是UTF-16编码的字符串。

在Windows系统中，每种带字符串参数的系统函数都分为A、W两个版本，比如用来创建窗口的CreateWindowA函数和CreateWindowW函数。
A版本的函数接收GB2312编码的char[]字符串，W版本的函数接收UTF-16编码的wchar_t[]字符串。如果字符串是UTF-8编码，必须要用MultiByteToWideChar函数转换成UTF-16编码后，再调用W版本的函数。

由此，我们可以封装出使用wchar_t[]宽字符串的数据库操作函数，以及GB2312、UTF-8、UTF-16编码转换函数。
db.h：

/* UTF-16专用函数 */
void db_set_wstr(Statement stmt, int i, const wchar_t *ws);
void db_bind_wstr(Statement stmt, int col, wchar_t *buf, int size, intptr_t *real_size);
wchar_t *db_get_wstr(Statement stmt, int col, wchar_t *buf, int size, intptr_t *real_size);
wchar_t *utf8_to_16(const char *us);
char *utf16_to_8(const wchar_t *ws);
wchar_t *gb2312_to_utf16(const char *s);
char *utf16_to_gb2312(const wchar_t *ws);

db.c：

void db_set_wstr(Statement stmt, int i, const wchar_t *ws)
{
	static SQLLEN len = SQL_NTS;

	SQLBindParameter(stmt, i, SQL_PARAM_INPUT, SQL_C_WCHAR, SQL_VARCHAR, wcslen(ws) * sizeof(wchar_t), 0, (SQLPOINTER)ws, 0, &len);
}

void db_bind_wstr(Statement stmt, int col, wchar_t *buf, int size, intptr_t *real_size)
{
	SQLBindCol(stmt, col, SQL_C_WCHAR, buf, size, real_size);
}

wchar_t *db_get_wstr(Statement stmt, int col, wchar_t *buf, int size, intptr_t *real_size)
{
	intptr_t temp;
	SQLRETURN ret;

	if (real_size == NULL)
		real_size = &temp;

	buf[0] = '\0';
	ret = SQLGetData(stmt, col, SQL_C_WCHAR, buf, size, real_size);
	if (ret == SQL_SUCCESS)
		return buf;
	else if (ret == SQL_SUCCESS_WITH_INFO)
		return NULL; // buf里面存的是已截断的不完整字符串
	else
		return NULL;
}

wchar_t *utf8_to_16(const char *us)
{
	int n;
	wchar_t *ws;

	n = MultiByteToWideChar(CP_UTF8, 0, us, -1, NULL, 0);
	ws = malloc(n * sizeof(wchar_t));
	if (ws != NULL)
		MultiByteToWideChar(CP_UTF8, 0, us, -1, ws, n);
	return ws;
}

char *utf16_to_8(const wchar_t *ws)
{
	char *us;
	int n;

	n = WideCharToMultiByte(CP_UTF8, 0, ws, -1, NULL, 0, NULL, NULL);
	us = malloc(n);
	if (us != NULL)
		WideCharToMultiByte(CP_UTF8, 0, ws, -1, us, n, NULL, NULL);
	return us;
}

wchar_t *gb2312_to_utf16(const char *s)
{
	int n;
	wchar_t *ws;

	n = MultiByteToWideChar(CP_ACP, 0, s, -1, NULL, 0);
	ws = malloc(n * sizeof(wchar_t));
	if (ws != NULL)
		MultiByteToWideChar(CP_ACP, 0, s, -1, ws, n);
	return ws;
}

char *utf16_to_gb2312(const wchar_t *ws)
{
	char *s;
	int n;

	n = WideCharToMultiByte(CP_ACP, 0, ws, -1, NULL, 0, NULL, NULL);
	s = malloc(n);
	if (s != NULL)
		WideCharToMultiByte(CP_ACP, 0, ws, -1, s, n, NULL, NULL);
	return s;
}

main.c：

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

// C语言自带的wprintf函数无法打印中文
// 所以这里重新实现一个能打印中文的my_wprintf函数
int my_wprintf(const wchar_t *format, ...)
{
	int n;
	va_list list;
	wchar_t str[1024];
	DWORD ret;
	HANDLE console;

	va_start(list, format);
	n = _vsnwprintf_s(str, _countof(str), _countof(str) - 1, format, list);
	va_end(list);

	console = GetStdHandle(STD_OUTPUT_HANDLE);
	WriteConsoleW(console, str, (DWORD)wcslen(str), &ret, NULL);
	return n;
}

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

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

void find_product_by_name_utf16(const wchar_t *name)
{
	char *sql;
	char *unit_gb2312;
	intptr_t unit_size;
	wchar_t unit[6];
	Statement stmt;

	sql = "SELECT [产品ID], [单位数量], [单价], [库存量] FROM [产品] WHERE [产品名称] = ?";
	stmt = db_prepare(sql);
	if (stmt != NULL)
	{
		db_set_wstr(stmt, 1, name);
		db_exec_stmt(stmt, 0);
		while (db_fetch(stmt) == 1)
		{
			db_get_wstr(stmt, 2, unit, sizeof(unit), &unit_size);
			my_wprintf(L"find %ls: %d %ls(size=%d) %.2lf %d\n", name, db_get_int(stmt, 1), unit, (int)unit_size, db_get_double(stmt, 3), db_get_int(stmt, 4));

			unit_gb2312 = utf16_to_gb2312(unit);
			if (unit_gb2312 != NULL)
			{
				printf("unit_gb2312=%s\n", unit_gb2312);
				free(unit_gb2312);
			}
		}
		db_free(stmt);
	}
}

void find_product_by_name_gb2312(const char *name)
{
	wchar_t *ws;

	ws = gb2312_to_utf16(name);
	if (ws != NULL)
	{
		find_product_by_name_utf16(ws);
		free(ws);
	}
}

int main()
{
	int ret;

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

	show_products();
	find_product_by_name_utf16(L"饼干");
	find_product_by_name_gb2312("桂花糕");

	db_disconnect();
	return 0;
}

巨大八爪鱼

关注

9
点赞
踩
11

收藏

觉得还不错? 一键收藏
打赏
0
评论
C语言通过ODBC函数操作Access数据库（mdb和accdb格式）（char字符数组）

编译环境：Windows XP + Visual Studio 2010数据库：Access 2010，accdb格式本例程只使用char[]字符数组，不使用wchar_t[]字符数组，更适合C语言初学者。如果读取字符串时，db_bind_str提供的字符数组空间小了，db_fetch会执行失败返回-2。由于Windows系统设计原因，char[]字符数组只能存储GB2312编码的字符串，wchar_t[]字符数组只能存储UTF-16编码的字符串。
复制链接

扫一扫