怎样用ADO操作ORACLE的BLOB数据

ADO的getchunk/AppendChunk与Oracle一起可用于BLOB数据的操作

本文的目的是演示如何使用ActiveX数据对象（ADO）方法getchunk和appendchunk将二进制大对象（BLOB）数据保存和检索到Oracle8.17数据库中的长原始数据类型列。

长原始数据类型用于存储长度不超过2 GB的可变大小二进制数据。每个表只能定义一个长的原始列。在子查询、函数、表达式、WHERE子句或索引中不能使用长的原始列。包含长原始列的表不能群集。每个表只能定义一个长的原始列，并且不能在同一个表中同时定义长列和长原始列。

应该注意的是，不建议将BLOB数据或长文本数据存储在表中。一种更有效的方法是将文件指针存储在表中，该表定位包含数据的实际文件。

以下项目在启动窗体上有一个图片框、CommonDialog控件和三个命令按钮。结果和状态显示在调试窗口或窗体标题中。必须修改连接字符串以匹配Oracle安装的设置。

注意：对于Visual Basic 5.0用户，您需要获取并安装本文中示例的Microsoft数据访问组件（MDAC）。有关安装MDAC 2.0的信息，请参阅参考资料部分中列出的文章。mdac 2.0包含ActiveX数据对象（ADO）版本2.0和用于Oracle版本2.5的Microsoft ODBC驱动程序。

对于Visual Basic 6.0用户，ADO 2.0和Microsoft ODBC for Oracle驱动程序2.5版与Visual Basic 6.0一起安装。
此项目使用一个名为blobtable的表。下面是用于创建表并向表中添加一行的脚本：

CREATE TABLE BLOBTABLE (

          MYID                            NUMBER(2) NOT NULL PRIMARY KEY,
          BLOBFLD                                  LONG RAW
        );
        /
        INSERT INTO BLOBTABLE (MYID) VALUES (1);
        /
        Commit;


应用程序说明
Visual Basic应用程序有一个图片框控件来查看选定的图片文件（默认为.bmp或.ico文件）、一个用于选择图片文件的公共对话框控件和三个用于控制应用程序流的命令按钮。

单击AppendChunk命令按钮会弹出“打开文件”对话框，允许您选择.bmp或.ico文件。按钮后面的代码获取该文件，并使用appendchunk方法将其存储到blobtable long raw列中。

单击getchunk按钮时，检索长原始列中的blob数据，将二进制数据转换为BMP文件，并在图片框控件中显示该文件。第三个按钮是退出应用程序。

通过以下步骤创建应用程序：

在Visual Basic中打开新项目。默认情况下创建Form1。

在新窗体上放置图片框和CommonDialog控件以及三个命令按钮。您可能需要将CommonDialog控件添加到项目中。在“项目”菜单上，指向“组件”，然后选择Microsoft Common Dialog Control 5.0或6.0版本（如果使用的是Visual Basic 6.0）。

在“项目”菜单上，指向“引用”，然后选择“Microsoft ActiveX数据对象2.x库”。

将以下代码放入Form1的“一般声明”部分：

'此应用程序演示如何将ADO与AppendChunk一起使用

'和针对Oracle8.17数据库的getchunk方法。

Option Explicit
      Dim Cn As ADODB.Connection
      Dim Rs As ADODB.Recordset
      Dim strConn As String
      Dim strSQL As String
      Dim FileLength As Long    'Used in Command1 and Command2 procedures.
      Dim Numblocks As Integer
      Dim LeftOver As Long
      Dim i As Integer
      Const BlockSize = 100000 'This size can be experimented with for
                                                        'performance and reliability.

      Private Sub Form_Load()
            Command1.Caption = "AppendChunk"
            Command2.Caption = "GetChunk"
            Command3.Caption = "Exit"
            Command2.Enabled = False

            'Make Connection
            Set Cn = New ADODB.Connection
            strConn = "UID=MyUID;PWD=MyPassword;" & _
                                "driver={Microsoft ODBC for Oracle};" & _
                                "SERVER=MyServer;"
            Cn.Open strConn
            Debug.Print Cn.ConnectionString

      End Sub

      Public Sub Command1_Click()
            ' AppendChunk button
            ' This procedure prompts for a BMP file,
            ' converts that file to a Byte array,
            ' and saves the Byte Array to the table
            ' using the Appendchunk method.
            '
            Dim PictBmp As String
            Dim ByteData() As Byte      'Byte array for Blob data.
            Dim SourceFile As Integer

            ' Open the BlobTable table.
            strSQL = "Select MyID, BLOBfld from BLOBTABLE WHERE MyID = 1"
            Set Rs = New ADODB.Recordset
            Rs.CursorType = adOpenKeyset
            Rs.LockType = adLockOptimistic
            Rs.Open strSQL, Cn

            ' Retrieve the picture and update the record.
            CommonDialog1.Filter = "(*.bmp;*.ico)|*.bmp;*.ico"
            CommonDialog1.ShowOpen
            PictBmp = CommonDialog1.filename
            Me.MousePointer = vbHourglass
            Me.Caption = "Retrieving the picture"

            ' Save Picture image to the table column.
            SourceFile = FreeFile
            Open PictBmp For Binary Access Read As SourceFile

            FileLength = LOF(SourceFile)    ' Get the length of the file.
            Debug.Print "Filelength is " & FileLength

            If FileLength = 0 Then

                    Close SourceFile
                    MsgBox PictBmp & " empty or not found."
                    Exit Sub
            Else

                    Numblocks = FileLength / BlockSize
                    LeftOver = FileLength Mod BlockSize

                    ReDim ByteData(LeftOver)
                    Get SourceFile, , ByteData()
                    Rs(1).AppendChunk ByteData()

                    ReDim ByteData(BlockSize)
                    For i = 1 To Numblocks
                            Get SourceFile, , ByteData()
                            Rs(1).AppendChunk ByteData()
                    Next i

                    Rs.Update      'Commit the new data.

                  Close SourceFile
            End If

            Me.Caption = "Picture Retrieved"
            Command2.Enabled = True
            Me.MousePointer = vbNormal
      End Sub

      Private Sub Command2_Click()
            ' GetChunk Button
            ' This procedure retrieves the picture image
            ' from the table using the GetChunk method,
            ' converts the data to a file and
            ' displays that file in the Picture box.
            '
            Dim ByteData() As Byte      'Byte array for picture file.
            Dim DestFileNum As Integer
            Dim DiskFile As String

            Me.MousePointer = vbHourglass
            Me.Caption = "Creating Picture File"

            ' Remove any existing destination file.
            DiskFile = App.Path & "\image1.bmp"
            If Len(Dir$(DiskFile)) > 0 Then
                  Kill DiskFile
            End If

            DestFileNum = FreeFile
            Open DiskFile For Binary As DestFileNum

            Numblocks = FileLength / BlockSize
            LeftOver = FileLength Mod BlockSize

            ByteData() = Rs(1).GetChunk(LeftOver)
            Put DestFileNum, , ByteData()

            For i = 1 To Numblocks
                    ByteData() = Rs(1).GetChunk(BlockSize)
                    Put DestFileNum, , ByteData()
            Next i

            Close DestFileNum

            Picture1.Visible = True
            Picture1.Picture = LoadPicture(App.Path & "\image1.bmp")
            Rs.Close
            Debug.Print "Complete"
            Me.Caption = "Success!"
            Me.MousePointer = vbNormal
      End Sub

      Private Sub Command3_Click()
      'Exit button.
      Cn.Close
      Unload Me
      End Sub