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

本文详细介绍了如何使用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;
    }
}

评论 4
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值