VC/VB 以 ADO 存取資料庫儲存影像完整指南

資料庫儲存影像 (BLOB) 是常見需求，ADO 提供 Field.AppendChunk/GetChunk 分塊讀寫與 Stream 物件串流操作兩種模式。本文完整示範 VC++ (ATL/COM) 與 VB6 兩種語言的存取模式、錯誤處理與效能優化。

一、資料表結構建議

CREATE TABLE CustomInfo (
    ID      INT PRIMARY KEY IDENTITY,
    Name    NVARCHAR(100),
    Image   VARBINARY(MAX)  -- SQL Server 2005+ 建議用 MAX
    -- 或 IMAGE (舊版相容)
);

二、VC++ (ADO + _RecordsetPtr) 完整實作

1. 寫入影像檔案到資料庫

#import "msado15.dll" no_namespace rename("EOF", "adoEOF")
#include <comdef.h>

bool SaveImageToDB(const CString& filePath, const CString& connStr) {
    try {
        _ConnectionPtr pConn(__uuidof(Connection));
        pConn->Open(_bstr_t(connStr), L"", L"", adConnectUnspecified);

        _RecordsetPtr pRs(__uuidof(Recordset));
        pRs->Open(L"CustomInfo", _variant_t((IDispatch*)pConn, true),
                  adOpenKeyset, adLockOptimistic, adCmdTable);

        // 讀取檔案到位元組陣列
        CFile file(filePath, CFile::modeRead);
        ULONGLONG len = file.GetLength();
        BYTE* pBuf = new BYTE[len];
        file.Read(pBuf, (UINT)len);
        file.Close();

        // 建立 SAFEARRAY (VT_UI1 | VT_ARRAY)
        SAFEARRAYBOUND bound = {0, (ULONG)len};
        SAFEARRAY* psa = SafeArrayCreate(VT_UI1, 1, &bound);
        BYTE* pData;
        SafeArrayAccessData(psa, (void**)&pData);
        memcpy(pData, pBuf, len);
        SafeArrayUnaccessData(psa);
        delete[] pBuf;

        _variant_t varBlob;
        varBlob.vt = VT_ARRAY | VT_UI1;
        varBlob.parray = psa;

        // 寫入
        pRs->AddNew();
        pRs->Fields->GetItem(L"Image")->AppendChunk(varBlob);
        pRs->Update();

        pRs->Close();
        pConn->Close();
        return true;
    }
    catch (_com_error& e) {
        CString msg; msg.Format(L"DB Error: %s", e.ErrorMessage());
        AfxMessageBox(msg);
        return false;
    }
}

2. 從資料庫讀取影像存檔

bool LoadImageFromDB(long id, const CString& outPath, const CString& connStr) {
    try {
        _ConnectionPtr pConn(__uuidof(Connection));
        pConn->Open(_bstr_t(connStr), L"", L"", adConnectUnspecified);

        _RecordsetPtr pRs(__uuidof(Recordset));
        pRs->Open(L"CustomInfo", _variant_t((IDispatch*)pConn, true),
                  adOpenKeyset, adLockOptimistic, adCmdTable);

        // 定位記錄 (假設 ID 為主鍵)
        pRs->Filter = _variant_t(L"ID = " + CString(id));

        long dataLen = pRs->Fields->GetItem(L"Image")->ActualSize;
        _variant_t varBlob = pRs->Fields->GetItem(L"Image")->GetChunk(dataLen);

        if (varBlob.vt == (VT_ARRAY | VT_UI1)) {
            BYTE* pData;
            SafeArrayAccessData(varBlob.parray, (void**)&pData);

            CFile outFile(outPath, CFile::modeCreate | CFile::modeWrite);
            outFile.Write(pData, dataLen);
            outFile.Close();

            SafeArrayUnaccessData(varBlob.parray);
        }

        pRs->Close();
        pConn->Close();
        return true;
    }
    catch (_com_error& e) {
        AfxMessageBox(e.ErrorMessage());
        return false;
    }
}

三、VB6 (ADODB) 完整實作

1. 寫入

Sub SavePic(FileName As String, IndexNumber As Long)
    Dim cnn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    cnn.CursorLocation = adUseClient
    cnn.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Initial Catalog=CUSTOM;Data Source=SERVER"
    rs.CursorType = adOpenKeyset
    rs.LockType = adLockOptimistic
    rs.Open "CustomInfo", cnn, , adCmdTable
    rs.Move IndexNumber
    Call FileToBlob(rs.Fields("Image"), FileName, FileLen(FileName))
    rs.UpdateBatch adAffectCurrent
    rs.Close: cnn.Close
End Sub

Private Sub FileToBlob(fld As ADODB.Field, FileName As String, Optional ChunkSize As Long)
    If (fld.Attributes And adFldLong) = 0 Then Err.Raise 1001, , "Field not support GetChunk"
    Dim fnum As Integer, bytesLeft As Long, bytes As Long, tmp() As Byte
    fnum = FreeFile
    Open FileName For Binary As fnum
    bytesLeft = LOF(fnum)
    Do While bytesLeft
        bytes = bytesLeft
        If bytes > ChunkSize Then bytes = ChunkSize
        ReDim tmp(1 To bytes) As Byte
        Get #fnum, , tmp
        fld.AppendChunk tmp
        bytesLeft = bytesLeft - bytes
    Loop
    Close #fnum
End Sub

2. 讀取

Sub LoadPic(IndexNumber As Long)
    Dim cnn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    cnn.CursorLocation = adUseClient
    cnn.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Initial Catalog=CUSTOM;Data Source=SERVER"
    rs.CursorType = adOpenKeyset
    rs.LockType = adLockOptimistic
    rs.Open "CustomInfo", cnn, , adCmdTable
    rs.Move IndexNumber
    Call BlobToFile(rs.Fields("Image"), "C:\Temp\img.bmp", rs.Fields("Image").ActualSize)
    rs.Close: cnn.Close
End Sub

Private Sub BlobToFile(fld As ADODB.Field, FileName As String, Optional ChunkSize As Long)
    If (fld.Attributes And adFldLong) = 0 Then Err.Raise 1001, , "Field not support GetChunk"
    Dim fnum As Integer, bytesLeft As Long, bytes As Long, tmp() As Byte
    If Dir$(FileName) <> "" Then Kill FileName
    fnum = FreeFile
    Open FileName For Binary As fnum
    bytesLeft = fld.ActualSize
    Do While bytesLeft
        bytes = bytesLeft
        If bytes > ChunkSize Then bytes = ChunkSize
        tmp = fld.GetChunk(bytes)
        Put #fnum, , tmp
        bytesLeft = bytesLeft - bytes
    Loop
    Close #fnum
End Sub

四、Stream 物件優化 (ADO 2.5+)

上述 AppendChunk/GetChunk 需手動分塊，Stream 物件支援一次性讀寫：

// VC++ Stream 寫入
_StreamPtr pStm; pStm.CreateInstance(__uuidof(Stream));
pStm->Type = adTypeBinary;
pStm->Open();
pStm->LoadFromFile(filePath); // 直接載入檔案
pRs->Fields->GetItem(L"Image")->Value = pStm->Read(adReadAll);
pRs->Update();

// VC++ Stream 讀取並另存
pStm->Write(pRs->Fields->GetItem(L"Image")->Value);
pStm->SaveToFile(outPath, adSaveCreateOverWrite);

五、效能與最佳實務

六、常見錯誤排查

• 800A0CD3: Field not support GetChunk：欄位型別非 BLOB/TEXT/IMAGE，或 adFldLong 屬性未設。
• Out of memory：一次 GetChunk 讀取過大檔案，改用分塊或 Stream。
• 資料截斷：SQL Server IMAGE 型別 2GB 上限，VARBINARY(MAX) 2GB，超過需分表/檔案系統。
• VB6 字串/位元組陣列混淆：AppendChunk 接受 Byte 陣列，字串會被當作 ANSI 字元碼寫入導致損壞。

掌握 ADO BLOB 讀寫模式，配合 Stream 物件簡化程式碼，能高效實現影像/文件資料庫化管理。