Re: Is it possible that I can use ADO Recordset object in VC to access (store and retrieve) the LOB data with BLOB field in Oracle table?

Hi mark,

If you can translate this VB code to VC, it do the job fine with ADO recordset

to load the blob :

Private Sub loadIt(ByVal sPathToLoad As String, _

                   ByVal sExtension As String)

    Dim sPictBmp As String
    Dim bData() As Byte 'Byte array for Blob data.     Dim iSourceFile As Integer
    Dim lFileLength As Long
    Dim sSql As String
    Dim rs As New ADODB.Recordset
    Dim sName As String
    Dim lNumblocks As Long
    Dim lBlockSize As Long
    Dim lLeftOver As Long
    Dim i As Long     

    Call GetIniSetting(App.Path & "\" & App.EXEName & ".ini", "GENERAL", "BLOCKSIZE", lBlockSize)     

    sName = Left$(ExtractFileName(sPathToLoad), Len(ExtractFileName(sPathToLoad)) - 4)     

    sSql = "Select rph_no_id, rph_image, rph_extension"     sSql = sSql & " from repertoires_photos where rph_no_id = " & sName     

    rs.CursorType = adOpenKeyset
    rs.LockType = adLockOptimistic
    rs.Open sSql, objCon
    
    If rs.EOF Or rs.BOF Then
        'on DOIT ajouteR SINON ON UPDATE SEULEMENT
        rs.AddNew

      ' on ouvre le fichier
    iSourceFile = FreeFile
    Open sPathToLoad For Binary Access Read As iSourceFile

    lFileLength = LOF(iSourceFile)

    If lFileLength = 0 Then

        'le fichier est vide ...
        Close iSourceFile
        Exit Sub

        lNumblocks = lFileLength / lBlockSize
        lLeftOver = lFileLength Mod lBlockSize
        
        ReDim bData(lLeftOver)
        Get iSourceFile, , bData()
        rs("RPH_IMAGE").AppendChunk bData()
        
        ReDim bData(lBlockSize)
        For i = 1 To lNumblocks
            Get iSourceFile, , bData()
            rs("RPH_IMAGE").AppendChunk bData()
        Next i
        
        rs("RPH_NO_ID") = sName
        rs("RPH_extension") = sExtension
        rs.Update
        
        Close iSourceFile

End Sub

to retreive :

Public Function GetImageString(ByVal lId As Long) As String

    Dim sSql As String
    Dim rs As New ADODB.Recordset
    Dim cmdBind As New ADODB.Command
    Dim sRetour As String     

    sSql = "Select rph_no_id, rph_image"     sSql = sSql & " from repertoires_photos where rph_no_id = ?" ' & CStr(lId)     

    rs.CursorType = adOpenForwardOnly
    rs.LockType = adLockReadOnly     

    With cmdBind

        .ActiveConnection = objCon
        .CommandText = sSql
        .CommandType = adCmdText
        .Prepared = True
        
        .Parameters.Append .CreateParameter("", adNumeric,

        Set rs = .Execute
        

    End With     

    If rs.EOF Or rs.BOF Then

        Exit Function
    End If     

    GetImageString = rs("rph_image")     

    rs.Close

End Function

hth

Chris

"Mark Liao" <markl_at_symtrontech.com> wrote in message news:<9sa9nf$b1$1_at_news.is.net.tw>...
> Dear All,
>
> I try to use ADO Recordset to access binary array data into the Oracle
> table. If I setup the field into "Long Raw" datatype, the program works
> fine. I can use ADO Recordset and Filed objects to get and set the binary
> array from the Oracle table. However, if I setup the field into "BLOB"
> datatype, the original source code can not work.
>
> I also test the sample code which download from the OTN (Oracle
> Technology Network). That program used ADO Command and Parameter objects to
> access the binary data and call stored procedure to set/get binary array
> data with BLOB field.
>
> So, my question is "Is it possible that I can use ADO Recordset to
> access binary array data with BLOB field without calling any stored
> procedure?". And the source code probably will be such like below :
>
>
> /* source code for adding Binary array into Oracle table */
>
> BYTE BinaryImage[1024]; // binary array
>
> VARIANT vValue; // variant parameter
>
> BinaryArrayToVariant(BinaryImage,vValue); // transfer binary array to
> variant
>
> pRecordset->Fields->GetItem("BinaryData")->Value=vValue; // add binary
> data to BLOB field
>
> VariantClear(&vValue);
>
>
>
> PS. The Oracle system I used is Oracle 9i.
>
>
>
> Mark Liao ^^*
Received on Wed Nov 07 2001 - 14:23:41 CET