之前在百度找了很久都没有找到利用insert语句插入二进制数据的方法,后来终于是在国外网站上找到了,真是艰辛……
这里主要是利用 _CommandPtr 对象来添加参数的形式来实现附带二进制数据。
大概的流程就是:
1、把二进制数据封装到 SAFEARRAY 对象中
2、把SAFEARRAY 对象填充到 variant_t 的变量中
3、把 variant_t 变量作为参数的值传入到 CommandPtr 中
4、执行 _CommandPtr 的insert 语句
这里假设表的结构为
create table ImageInfo
{
SeriaNumber varchar(36),
Photo image,
Time time,
flag int
}
// BeginAddNewCpp.cpp
// compile with: /EHsc /c
#include <io.h>
#include <ole2.h>
#include <stdio.h>
#import "msado15.dll" no_namespace rename("EOF", "EndOfFile")
size_t Tool_GetFileSize(const char *FileName);
bool Tool_LoadFileDataToBuffer(const char* FileName, void* infoBuf, size_t& bufLength);
int main()
{
// TODO: 在此添加控件通知处理程序代码
_ConnectionPtr pConnection = NULL;
_CommandPtr pCommand = NULL;
_ParameterPtr pParameterFirst = NULL;
_ParameterPtr pParameterSecond = NULL;
_ParameterPtr pParameterThird = NULL;
_ParameterPtr pParameterFourth = NULL;
try
{
pConnection.CreateInstance(__uuidof(Connection));
_bstr_t strCnn("Provider=sqloledb;Data Source=127.0.0.1;"
"Initial Catalog='DB';User Id=sa;Password=123456;");
HRESULT hr = pConnection->Open(strCnn, "", "", adConnectUnspecified);
//int nTick=::GetTickCount();
HRESULT hr2 = S_FALSE;
hr2 = pRecordset.CreateInstance(__uuidof(Recordset));
TESTHR(pCommand.CreateInstance(__uuidof(Command)));
pCommand->CommandType = adCmdText;
pCommand->CommandText = _bstr_t("INSERT INTO ImageInfo (SeriaNumber,Photo, Time, flag )VALUES( ? , ?, ?, ?);");
pCommand->ActiveConnection = pConnection;
//给参数赋值
char* pchImagePath = "E:\\QQDownload\\123.jpg";
char szSeriaNumber[36] = { 0 };
sprintf_s(szSeriaNumber, sizeof(szSeriaNumber), "%lu", GetTickCount());
size_t iImgLength = Tool_GetFileSize(pchImagePath);
unsigned char* pImage = new unsigned char[iImgLength];
Tool_LoadFileDataToBuffer(pchImagePath, pImage, iImgLength);
pParameterFirst = pCommand->CreateParameter(_bstr_t("@SeriaNumber"), adBSTR, adParamInput, (long)36);
pParameterFirst->Value = (_variant_t)szSeriaNumber;
pCommand->Parameters->Append(pParameterFirst);
variant_t vtChunk;
vtChunk.vt = VT_ARRAY | VT_UI1;
SAFEARRAY FAR *psaChunk = NULL;
psaChunk = SafeArrayCreateVector(VT_UI1, 1, (ULONG)iImgLength);
unsigned char* imgBuff = NULL;
SafeArrayAccessData(psaChunk, (void **)&imgBuff);
memcpy(imgBuff, pImage, iImgLength);
SafeArrayUnaccessData(psaChunk);
vtChunk.parray = psaChunk;
if (pImage)
{
delete[] pImage;
pImage = NULL;
}
pParameterSecond = pCommand->CreateParameter(_bstr_t("@Photo"), adVarBinary, adParamInput, (ULONG)iImgLength, vtChunk);
pCommand->Parameters->Append(pParameterSecond);
pParameterThird = pCommand->CreateParameter(_bstr_t("@Time"), adBSTR, adParamInput, (long)36);
pParameterThird->Value = (_variant_t)"2018-12-11 18:25:35.529";
pCommand->Parameters->Append(pParameterThird);
pParameterFourth = pCommand->CreateParameter(_bstr_t("@flag"), adBSTR, adParamInput, (long)1);
pParameterFourth->Value = (_variant_t)"1";
pCommand->Parameters->Append(pParameterFourth);
pCommand->Execute(NULL, NULL, adExecuteNoRecords);
if (pConnection != NULL
&& pConnection->State == adStateOpen
)
{
pConnection->Close();
}
}
catch (_com_error e)
{
char strErrorMessage[256] = {0};
sprintf_s(strErrorMessage, sizeof(strErrorMessage), "保存图片失败!错误信息:%s 错误码:%d, 错误描述:%s", e.ErrorMessage(), GetLastError(), (char*)e.Description());
printf(strErrorMessage);
}
}
bool Tool_LoadFileDataToBuffer(const char* FileName, void* infoBuf, size_t& bufLength)
{
if (infoBuf == NULL || bufLength <= 0)
{
printf("参数错误.\n");
return false;
}
size_t iFileSize = Tool_GetFileSize(FileName);
if (iFileSize > bufLength)
{
bufLength = iFileSize;
printf("传入缓冲区的长度不足.\n");
return false;
}
bufLength = iFileSize;
FILE* pFile = NULL;
errno_t errCode;
_set_errno(0);
errCode = fopen_s(&pFile, FileName, "rb");
if (pFile)
{
size_t iReadSize = fread(infoBuf, 1, iFileSize, pFile);
fclose(pFile);
pFile = NULL;
if (iReadSize != iFileSize)
{
printf("文件读取错误,大小不一致.\n");
return false;
}
else
{
return true;
}
}
else
{
printf("文件打开失败. error code = %d\n", errCode);
return false;
}
}
size_t Tool_GetFileSize(const char *FileName)
{
//FILE* tmpFile = fopen(FileName, "rb");
FILE* tmpFile = NULL;
errno_t errCode;
_set_errno(0);
errCode = fopen_s(&tmpFile, FileName, "rb");
if (tmpFile)
{
//fseek(tmpFile, 0, SEEK_END);
//long fileSize = ftell(tmpFile);
//fclose(tmpFile);
//tmpFile = NULL;
//return fileSize;
long fileSize = _filelength(_fileno(tmpFile));
fclose(tmpFile);
tmpFile = NULL;
return fileSize;
}
else
{
printf("Tool_GetFileSize, failed, error code = %d", errCode);
return 0;
}
}