在VC++中使用 Ado 连接到sql server,并使用insert 语句插入二进制数据

最新推荐文章于 2020-11-20 16:51:05 发布

humadivinity

最新推荐文章于 2020-11-20 16:51:05 发布

阅读量1.6k

点赞数 1

CC 4.0 BY-SA版权

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

本文链接：https://blog.youkuaiyun.com/humadivinity/article/details/84982825

数据库开发专栏收录该内容

3 篇文章

订阅专栏

本文详细介绍了如何使用ADODB组件通过_CommandPtr对象和SAFEARRAY结构将包含二进制数据的图像插入数据库的过程。文章提供了一段完整的C++代码示例，演示了从文件加载图像数据、创建并填充参数、执行插入操作的全过程。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

之前在百度找了很久都没有找到利用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;
    }
}