使用ADO处理MS Access中的附件类型字段 [英] Handling fields of Attachment type in MS Access using ADO

问题描述

我的数据库中有一些字段需要存储图像（位图，JPG或PNG）和PDF（或Excel / RTF / TXT）文件。通过互联网浏览，我了解到 MS Access 2007 （和更新的版本）有一个附件类型的字段，可以适合我的需要。



但是，我通过GUI填充数据库（用 C ++ 和<$ c $制作c> WinAPI ），使用 ADO 。我无法找到使用 ADO 在数据库中插入/加载此类数据的示例。搜索Stack Overflow并在这里通过一些类似的问题（VB，Delphi，Python ......）我发现我的方法可能不是最好的，但似乎可以这样做。



由于缺乏经验，而且这是我第一次处理这类任务，我请社区帮助我解决这个问题。因此我的问题是：



如何处理/从 MS Access中插入/加载此类数据（图像，文档......） 2007 （或更高）数据库使用 ADO ？



你能来吗？提供小代码片段，说明使用ADO将数据插入/插入数据库？



谢谢。

I have some fields in my database that will need to store images ( bitmap, JPG or PNG ) and PDF ( or Excel/RTF/TXT ) files. Browsing through Internet, I have learned that MS Access 2007 ( and newer versions ) have a field of type Attachment that can suit my needs.

However, I am filling database via GUI ( made in C++ and WinAPI ), using ADO. I was not able to find an example of inserting/loading such data into/from database using ADO. Searching on Stack Overflow and here through some similar questions ( VB, Delp Python...) I have found out that my approach might not be the best, but seems possible to do.

Being inexperienced, and since this is my first time tackling this type of task, I ask community to help me solve this task. Therefore my question:

How should I handle inserting/loading this type of data ( images, documents...) into/from MS Access 2007 (or higher) database using ADO?

Can you provide small code snippet that illustrates inserting/loading such data into/from database using ADO?

Thank you.

推荐答案

我自己就是在一个项目中做到了这一点。我要做的是将DB字段创建为OLE对象（字段类型）。为了读/写字段，我使用了包含在COleVariant中的SafeArray（vt = VT_ARRAY | VT_UI1）。



下面是一些示例代码：

I just did this in a project myself. What I had to do was Create the DB field as an "OLE Object" (field type). To read/write the field I used a SafeArray packed in a COleVariant (vt = VT_ARRAY | VT_UI1).

Heres some example code:

// ADO - 2.7 comes with WinXP
#import "msado27.tlb" no_namespace \
	rename("EOF", "EndOfFile") \
	rename("Connection", "ADOConnection") \
	rename("Recordset", "ADORecordset")

CString g_cstrDBCon	= _T("Driver={Microsoft Access Driver (*.mdb)};Dbq=Test.mdb;Uid=;Pwd=;");

#define __DBCLOSE(AdoObj) { \
	if(AdoObj) { \
		if(AdoObj->State == adStateOpen) \
			AdoObj->Close(); \
		AdoObj.Release(); \
		AdoObj = NULL; \
	} \
}


BOOL Load()
{
	BOOL bRetVal = FALSE;
	HRESULT hr = NOERROR;
	COleVariant cvFieldVal;
	CString cstrSQL = _T("");
	CString cstrDBError = _T("");
	_ConnectionPtr pCn = NULL;
	_RecordsetPtr pRs = NULL;

	try
	{
		// Create database objects 
		if(FAILED(hr = pCn.CreateInstance(__uuidof(ADOConnection))))
			_com_issue_error(hr);
		if(FAILED(hr = pRs.CreateInstance(__uuidof(ADORecordset))))
			_com_issue_error(hr);
		// Open connection
		if(FAILED(pCn->Open(_bstr_t(g_cstrDBCon), "", "", adConnectUnspecified)))
			_com_issue_error(hr);
		// Open and read table
		pRs->Open(_bstr_t(L"TableName"), _variant_t((IDispatch*)pCn, true),
			adOpenDynamic, adLockPessimistic, adCmdTable);
		while(!pRs->EndOfFile)
		{
			// Get [ID] (long value example)
			cvFieldVal = pRs->Fields->GetItem(_bstr_t(L"ID"))->GetValue();
			long lID = cvFieldVal.lVal;
			// Get [Name] (string value example)
			cvFieldVal = pRs->Fields->GetItem(_bstr_t(L"Name"))->GetValue();
			CString cstrName = cvFieldVal.bstrVal;
	
			// Get [BinaryValue]
			cvFieldVal = pRs->Fields->GetItem(_bstr_t(L"BinaryValue"))->GetActualSize();
			long lActSize = cvFieldVal.lVal; // Get binary data size
			cvFieldVal = pRs->Fields->GetItem(_bstr_t(L"BinaryValue"))->GetChunk(pNewProp->m_lDefValLen);
			if(cvFieldVal.vt == (VT_ARRAY|VT_UI1))
			{
				BYTE* pBinaryData = new BYTE[lActSize];
				char* pBuf = NULL;
				SafeArrayAccessData(cvFieldVal.parray, (void**)&pBuf);
				memcpy(pBinaryData, pBuf, lActSize);
				SafeArrayUnaccessData(cvFieldVal.parray);
	
				// Do something with data ... 

				delete [] pBinaryData;
			}

			// Next record
			pRs->MoveNext();
		}
		pRs->Close();
		bRetVal = TRUE;
	}
	catch(_com_error& e) 
	{ 
		if((pCn) && (pCn->Errors->Count > 0)) 
		{
			ErrorPtr pErr = pCn->Errors->GetItem(0); 
			cstrDBError.Format(L"Error %x: %s", pErr->Number, (LPWSTR)pErr->Description); 
			pCn->Errors->Clear(); 
		} 
		else 
		{ 
			cstrDBError.Format(L"Error %x: %s", e.Error(), (LPWSTR)e.Description()); 
		} 
	} 
	catch(...) 
	{ 
		cstrDBError = L"Unknown Error"; 
	}

	__DBCLOSE(pRs);
	__DBCLOSE(pCn);
	
	if(!bRetVal)
	{
		MessageBox(cstrDBError, _T("DB Error"), MB_ICONERROR);
	}

	return bRetVal;
}

BOOL Save()
{
	BOOL bRetVal = FALSE;
	HRESULT hr = NOERROR;
	COleVariant cvFieldVal;
	CString cstrSQL = _T("");
	CString cstrFilter = _T("");
	CString cstrDBError = _T("");
	_ConnectionPtr pCn = NULL;
	_RecordsetPtr pRs = NULL;

	try
	{
		// Create database objects 
		if(FAILED(hr = pCn.CreateInstance(__uuidof(ADOConnection))))
			_com_issue_error(hr);
		if(FAILED(hr = pRs.CreateInstance(__uuidof(ADORecordset))))
			_com_issue_error(hr); 
		// Open connection
		if(FAILED(pCn->Open(_bstr_t(g_cstrDBCon), "", "", adConnectUnspecified)))
			_com_issue_error(hr);

		long lRecordID = 1;

		// Position recordset
		cstrFilter.Format(L"ID=%d", lRecordID);
		pRs->put_Filter(COleVariant(cstrFilter, VT_BSTR));

		// Write [Name] (string value example)
		CString cstrVal = _T("Some Text");
		pRs->Fields->GetItem(_bstr_t(L"Name"))->put_Value(COleVariant(cstrVal, VT_BSTR));
			
		// Dummy binary value
		long lBinaryValLen = 333;
		BYTE* pValue = new BYTE[lBinaryValLen];
		memset(pValue, 1, lBinaryValLen);

		// Create safearray  
		SAFEARRAYBOUND sab[1];
		sab[0].lLbound = 0;	
		sab[0].cElements = lBinaryValLen;
		SAFEARRAY FAR* psa = SafeArrayCreate(VT_UI1, 1, sab);
		BYTE* pByte;
		if(SafeArrayAccessData(psa, (void**)&pByte) == NOERROR)
			memcpy(pByte, pProp->m_pDefValue, pProp->m_lDefValLen);
		SafeArrayUnaccessData(psa);
		VARIANT vArray;
		VariantInit(&vArray);
		vArray.vt = VT_ARRAY|VT_UI1;
		vArray.parray = psa;

		// Write [BinaryValue]
		if(FAILED(pRs->Fields->GetItem(_bstr_t(L"BinaryValue"))->AppendChunk(vArray)))
			_com_issue_error(hr);
		VariantClear(&vArray);
		SafeArrayDestroy(psa);
	
		pRs->Update();
		pRs->Close();

		bRetVal = TRUE;
	}
	catch(_com_error& e) 
	{ 
		if((pCn) && (pCn->Errors->Count > 0)) 
		{
			ErrorPtr pErr = pCn->Errors->GetItem(0); 
			cstrDBError.Format(L"Error %x: %s", pErr->Number, (LPWSTR)pErr->Description); 
			pCn->Errors->Clear(); 
		} 
		else 
		{ 
			cstrDBError.Format(L"Error %x: %s", e.Error(), (LPWSTR)e.Description()); 
		} 
	} 
	catch(...) 
	{ 
		cstrDBError = _T("Unknown Error"); 
	}

	__DBCLOSE(pRs);
	__DBCLOSE(pCn);
	
	if(!bRetVal)
	{
		MessageBox(cstrDBError, _T("DB Error"), MB_ICONERROR);
	}

	return bRetVal;
}

祝你好运

Good luck

我的提示是保存不在数据库中的图像和文件，而是保存在db-server上的一些额外目录中。请务必提供唯一的名称，以便文件不匹配或覆盖。如果数据集被删除则删除文件...



看起来微软有一些好的示例代码。

My tip is to save images and files NOT in the database, but in some extra directories on the db-server. Be sure to give unique names, so files arent mismatch or overwritten. And delete the files if the dataset is deleted...

Looks like Microsoft has some good example code.

这篇关于使用ADO处理MS Access中的附件类型字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！