VC++ / VB6 以 ADO 將圖片儲存至資料庫 IMAGE 欄位完整指南

前言

資料庫 IMAGE (SQL Server) / LONGBLOB (MySQL) / BLOB (Oracle) 欄位可儲存二進位圖片資料。ADO 透過 AppendChunk/GetChunk 分塊讀寫，避免大圖一次載入記憶體。本文提供 VC++ (ATL/MFC) 與 VB6 兩套完整範本。

一、VC++ (MFC/ATL) 版本

1. 寫入圖片至資料庫

HRESULT SaveImageToDB(LPCTSTR szFilePath, _ConnectionPtr pConn)
{
    // 1. 讀取檔案至記憶體
    CFile file;
    if (!file.Open(szFilePath, CFile::modeRead | CFile::typeBinary))
        return E_FAIL;

    ULONGLONG len = file.GetLength();
    if (len > 0xFFFFFFFF) return E_FAIL;  // 超過 4GB 需分塊處理

    std::vector buffer(len);
    file.Read(buffer.data(), (UINT)len);
    file.Close();

    // 2. 建立 SAFEARRAY (VT_UI1 | VT_ARRAY)
    SAFEARRAYBOUND bound = {0, (ULONG)len};
    SAFEARRAY* psa = SafeArrayCreate(VT_UI1, 1, &bound);
    if (!psa) return E_OUTOFMEMORY;

    BYTE* pData;
    SafeArrayAccessData(psa, (void**)&pData);
    memcpy(pData, buffer.data(), len);
    SafeArrayUnaccessData(psa);

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

    // 3. ADO 寫入
    _RecordsetPtr pRs(__uuidof(Recordset));
    pRs->Open(_T("Images"), _variant_t((IDispatch*)pConn, true),
              adOpenKeyset, adLockOptimistic, adCmdTable);

    pRs->AddNew();
    pRs->Fields->GetItem(_T("ImageData"))->AppendChunk(varBlob);
    pRs->Update();

    pRs->Close();
    SafeArrayDestroy(psa);
    return S_OK;
}

2. 從資料庫讀出存檔

HRESULT LoadImageFromDB(_ConnectionPtr pConn, LPCTSTR szOutPath)
{
    _RecordsetPtr pRs(__uuidof(Recordset));
    pRs->Open(_T("SELECT ImageData FROM Images WHERE ID=1"),
              _variant_t((IDispatch*)pConn, true),
              adOpenStatic, adLockReadOnly, adCmdText);

    if (pRs->adoEOF) return E_FAIL;

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

    if (varBlob.vt != (VT_ARRAY | VT_UI1)) return E_UNEXPECTED;

    BYTE* pData;
    SafeArrayAccessData(varBlob.parray, (void**)&pData);

    CFile file(szOutPath, CFile::modeCreate | CFile::modeWrite | CFile::typeBinary);
    file.Write(pData, dataLen);
    file.Close();

    SafeArrayUnaccessData(varBlob.parray);
    pRs->Close();
    return S_OK;
}

二、VB6 版本

1. 存圖 (Sub SavePic)

Sub SavePic(FileName As String, IndexNumber As Long)
    Dim cn As New ADODB.Connection
    Dim rs As New ADODB.Recordset

    cn.CursorLocation = adUseClient
    cn.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Initial Catalog=MyDB;Data Source=."

    rs.CursorType = adOpenKeyset
    rs.LockType = adLockOptimistic
    rs.Open "Images", cn, , , adCmdTable

    rs.Move IndexNumber
    Call FileToBlob(rs.Fields("ImageData"), FileName, FileLen(FileName))
    rs.UpdateBatch adAffectCurrent

    rs.Close: cn.Close
End Sub

Private Sub FileToBlob(fld As ADODB.Field, FileName As String, Optional ChunkSize As Long = 8192)
    Dim fNum As Integer, bytesLeft As Long, bytes As Long
    Dim tmp() As Byte

    If (fld.Attributes And adFldLong) = 0 Then Err.Raise 1001, , "欄位不支援 GetChunk"

    fNum = FreeFile
    Open FileName For Binary As fNum
    bytesLeft = LOF(fNum)

    Do While bytesLeft > 0
        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)

Sub LoadPic(IndexNumber As Long)
    Dim cn As New ADODB.Connection
    Dim rs As New ADODB.Recordset

    cn.CursorLocation = adUseClient
    cn.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Initial Catalog=MyDB;Data Source=."

    rs.CursorType = adOpenKeyset
    rs.LockType = adLockOptimistic
    rs.Open "Images", cn, , , adCmdTable

    rs.Move IndexNumber
    Call BlobToFile(rs.Fields("ImageData"), "C:\Temp\output.bmp", rs.Fields("ImageData").ActualSize)

    rs.Close: cn.Close
End Sub

Private Sub BlobToFile(fld As ADODB.Field, FileName As String, Optional ChunkSize As Long = 8192)
    Dim fNum As Integer, bytesLeft As Long, bytes As Long
    Dim tmp() As Byte

    If (fld.Attributes And adFldLong) = 0 Then Err.Raise 1001, , "欄位不支援 GetChunk"
    If Dir$(FileName) <> "" Then Kill FileName

    fNum = FreeFile
    Open FileName For Binary As fNum
    bytesLeft = fld.ActualSize

    Do While bytesLeft > 0
        bytes = bytesLeft
        If bytes > ChunkSize Then bytes = ChunkSize
        tmp = fld.GetChunk(bytes)
        Put #fNum, , tmp
        bytesLeft = bytesLeft - bytes
    Loop
    Close #fNum
End Sub

三、關鍵點總結

四、現代化建議 (VC++ 11+ / .NET)

• 改用 varbinary(max) 取代 IMAGE (已棄用)。
• VC++ 採用 std::vector + ADO::Stream 簡化二進位處理。
• .NET 直接用 SqlParameter .Value = File.ReadAllBytes(path) 或 SqlFileStream (Filestream 屬性)。
• 大檔案 (>100MB) 必須啟用 Chunking 並搭配 SqlFileStream 避免記憶體壓力。

結語

ADO AppendChunk/GetChunk 是跨語言儲存二進位大物件的標準模式。VC++ 需手動管理 SAFEARRAY 生命週期，VB6 則享有自動記憶體管理優勢。理解分塊讀寫邏輯與欄位屬性 (adFldLong) 檢查，即可可靠實作圖片/文件資料庫存取。