db-CDatabase

发表于 更新于 分类于 阅读次数： 本文字数： 4.3k 阅读时长 ≈ 4 分钟

use ODBC access database

dsn 设置

// access database use
string getDsnDbPath(const string& dsnName)
{
	string strRet = "";
	HKEY hKey;
	string subKey = "Software\\ODBC\\ODBC.INI\\" + dsnName;
	LONG lResult = RegOpenKeyExA(HKEY_CURRENT_USER, (subKey).c_str(), 0, KEY_WOW64_64KEY | KEY_READ, &hKey);
	if (ERROR_SUCCESS == lResult)
	{
		DWORD dwType = REG_SZ;
		CHAR szLocation[MAX_PATH] = { '\0' };
		DWORD dwSize = MAX_PATH;
		// 获取缓存的长度dwSize及类型dwDataType
		lResult = ::RegQueryValueEx(hKey, "DBQ", 0, &dwType, (LPBYTE)&szLocation, &dwSize);
		if (ERROR_SUCCESS == lResult)
		{
			strRet = &szLocation[0];
		}
		RegCloseKey(hKey);
	}
	else {
		// 不存在,或打开失败
	}
	return strRet;
}

BOOL isDsnPresent(const string& dsnName)
{
	BOOL bRet = TRUE;
	HKEY hKey;
	string subKey = "Software\\ODBC\\ODBC.INI\\" + dsnName;
	LONG lResult = RegOpenKeyExA(HKEY_CURRENT_USER, (subKey).c_str(), 0, KEY_WOW64_64KEY |KEY_READ, &hKey);
	if (ERROR_SUCCESS == lResult)
	{
		bRet = TRUE;
	}
	else
	{
		if (lResult == ERROR_FILE_NOT_FOUND)
		{
			bRet = FALSE;
		}
		else
		{
			subKey = str::cstr::format("open registry failed. %d",lResult);
			OutputDebugString(subKey.c_str());
		}
		bRet = FALSE;
	}
	RegCloseKey(hKey);
	return bRet;
}
 
 BOOL configAddDSN(const string& driver, const vector<string>& attr)
 {
 	char buf[512] = {'\0'}; 
 	char* p = buf;
 	for (auto&it:attr)
 	{
 		strcpy(p, it.c_str());	p += strlen(p) + 1;
 	}
 	return SQLConfigDataSource(NULL, ODBC_ADD_DSN, driver.c_str(), buf);
 }

连接字符串

注意 Exclusive=0 避免独占打开

strDbPath = getDsnDbPath("dsnReal16Access");
g_strDsn = "ODBC;DSN=dsnReal16Access;DBQ=" + strDbPath + ";DriverId=25;FIL={MS Access};Exclusive=0;MaxBufferSize=2048;PageTimeout=5;UID=admin;";

查询

dbString = "SELECT f_no, f_name FROM t_people_purpose_type;";
CRecordset rs(&db);
if (FALSE == rs.Open(CRecordset::forwardOnly, dbString.c_str(), CRecordset::readOnly))
{
	if (g_bWriteLog)
	{
		string strLog = "1biao6shi: get t_people_purpose_type error: ";
		OutputDebugString(strLog.c_str());
		if (g_logger)
		{
			g_logger->error(strLog);
		}
	}
}else
{
	CDBVariant varValue;
	int iNo = 0;
	string strName;
	while (!rs.IsEOF())
	{
		rs.GetFieldValue(short(0), varValue);
		iNo = atoi(*varValue.m_pstring);
		rs.GetFieldValue(short(1), varValue);
		if (string::npos != pwnd->m_strDsn.find("Access"))
		{
			strName = ((*varValue.m_pstring).GetBuffer());
		}
		else
		{
			strName = str::cstr::utf8ToAscii((*varValue.m_pstring).GetBuffer()).c_str();
		}
		
		if (strName.empty())
		{
			ASSERT(FALSE);
		}
		pwnd->m_mapPersonPurposeType.insert(pair<int, string>(iNo, strName));
		rs.MoveNext();
	}
	rs.Close();
}

非查询语句

db->executeNonQuery("set names utf8");

insert

db.BeginTrans();
db.ExecuteSQL(sql.c_str());
db.CommitTrans();

insert by CRecordset

插入图片
https://blog.csdn.net/weixin_40026797/article/details/83719070

使用class wizard 添加 CRecordset 的继承类,使用 mfc odbc consumer 选择要处理的表.

db.BeginTrans();
Ct_personRecordset rs(&db); //绑定数据库

1. Call AddNew.
2. Set field appropriately.
3. Call Update

db.CommitTrans();

下面代码一直报错,未成功. Conversion is not supported

// has picture need to store
rs.m_f_pic.m_dwDataLength = vOut.size();
HGLOBAL hGlobal = GlobalAlloc(GPTR, rs.m_f_pic.m_dwDataLength);
rs.m_f_pic.m_hData = GlobalLock(hGlobal);
memcpy_s(rs.m_f_pic.m_hData, rs.m_f_pic.m_dwDataLength, &vOut[0], vOut.size());

rs.SetFieldDirty(&rs.m_f_pic, true);
rs.SetFieldNull(&rs.m_f_pic, false);

while (!rs.CanUpdate())
    Sleep(200);
GlobalUnlock(rs.m_f_pic.m_hData);

modify by CRecordset

rs.Edit();
rs.m_field1 = ...;//字段1
rs.m_field2 = ...;//字段2
.....
rs.Update();

CRecordset 相关说明

Snapshots and Dynasets

Snapshots 是静态数据,反应获取时的那一点的数据
Dynasets 是动态数据,如果有其他人修改了,会动态反应出来,需要调用 requery

class view 窗口右键项目-菜单选择 add class- mfc - mfc odbc consumer 然后选择数据源,生成类

问题

CRecordset 的坑

手动修改了生成类的字段似乎不生效,需要重新生成.

RFX_TEXT nMaxLength 默认 255 不够用,需要手动修改

RFX_Text(pFX, _T("[f_gis]"), m_f_gis, 1024);
RFX_Text(pFX, _T("[f_gisArea]"), m_f_gisArea, 1024);

blob error: Conversion is not supported

insert image to blob field error.

Lock wait timeout exceeded; try restarting transaction

Enter MySQL

mysql -u your_user -p

  1. Let’s see the list of locked tables

mysql> show open tables where in_use>0;

  1. Let’s see the list of the current processes, one of them is locking your table(s)

mysql> show processlist;

  1. Kill one of these processes

mysql> kill <put_process_id_here>;

Conversion is not supported

State:07006,Native:0,Origin:[MySQL][ODBC 8.0(a) Driver][mysqld-5.5.62-log]