SAMPLE: RDOBLOBS.EXE RDO and BLOB/TEXT Data with SQL SP (197914)

MORE INFORMATION

The following files are available for download from the Microsoft Download Center:
Release Date: DEC-29-1998

For additional information about how to download Microsoft Support files, click the following article number to view the article in the Microsoft Knowledge Base: Microsoft scanned this file for viruses. Microsoft used the most current virus-detection software that was available on the date that the file was posted. The file is stored on security-enhanced servers that help to prevent any unauthorized changes to the file.

   FileName                    Size
   ---------------------------------------------------------

   RDOblobs.frm                10KB
   uspGetBLOB.sql               1KB
   uspInsertBLOB.sql            1KB
   RDO_Blobs.exe               36KB
   RDOblobs.vbp                 1KB
   Readme.txt                   1KB
				

You may use other techniques to store and retrieve binary large object (BLOB) data with RDO, as described in the articles located in the REFERENCES section of this article, although using stored procedures to Insert/Retrieve BLOBs may be more efficient. Please see the Microsoft Developer Network for a more comprehensive explanation.

The following excerpt is taken from the Microsoft Developer Network article "Processing Queries and Results": The following code sample demonstrates creating the stored procedures for the BLOB Inserts and Selects then calling the InsertBLOB() function to pass the BLOB information as a Byte Array parameter to the uspInsertBLOB stored procedure. Note that normally you would create these stored procedures once (not dynamically) and use them again as needed. Please reference the ode in the RDOblobs.exe file for more detail.

Sample Code

   Private Sub Command4_Click()

   'Create uspInsertBLOB and uspGetBLOB stored procedures.
   Dim strSQL As String


   If Not (rdoCn Is Nothing) Then
      With rdoCn
         strSQL = "if exists (select * from sysobjects " & _
                  "where id = object_id('dbo.uspGetBLOB') and sysstat & 0xf
                   = 4) " & _
                  "drop procedure dbo.uspGetBLOB"
         .Execute strSQL
         strSQL = "Create Procedure uspGetBLOB " & _
                  "(@RowID  int=0, " & _
                  "@BLOBname varchar(255)=" & "'') " & _
                  "AS select RowID, BLOBname, BLOBtxt, BLOBimg " & _
                  "FROM BLOBtable " & _
                  "WHERE BLOBname = @BLOBname " & _
                  "OR RowID = @RowID"
         .Execute strSQL
         strSQL = "if exists (select * from sysobjects " & _
                  "where id = object_id('dbo.uspInsertBLOB') and sysstat &
                   0xf = 4) " & _
                  "drop procedure dbo.uspInsertBLOB"
         .Execute strSQL
         strSQL = "Create Procedure uspInsertBLOB " & _
                  "(@BLOBname   varchar(255)=" & "'', " & _
                  "@BLOBtxt    text=" & "'', " & _
                  "@BLOBimg    image=" & "'') " & _
                  "AS SET NOCOUNT ON " & _
                  "INSERT BLOBtable(BLOBname, BLOBtxt, BLOBimg) " & _
                  "VALUES (@BLOBname, @BLOBtxt, @BLOBimg) " & _
                  "IF @@ROWCOUNT >0 " & _
                  "return -1 " & _
                  "Else " & _
                  "return 0"
         .Execute strSQL
      End With
      Label1.Caption = "Insert/Get BLOB Stored Procs Created..."
   Else
      Label1.Caption = "No Connection..."
   End If

   End Sub
   '======================

   Private Sub InsertBLOB()

   On Error GoTo ErrHandler

   MousePointer = vbHourglass

   Dim bytData() As Byte
   Dim strData As String
   Dim lngBlockSize As Long
   Dim intBlocks As Integer
   Dim intBlocksLo As Integer
   Dim lngFileLen1 As Long
   Dim lngFileLen2 As Long
   Dim intFileNum1 As Integer
   Dim intFileNum2 As Integer
   Dim strFileName1 As String
   Dim strFileName2 As String
   Dim intCnt As Integer
   Dim rdoQy As New rdoQuery

   lngBlockSize = 15000

   'Open BLOB file.
   intFileNum1 = FreeFile
   strFileName1 = Text1.Text
   Open strFileName1 For Binary Access Read As intFileNum1
   lngFileLen1 = LOF(intFileNum1)

   'Open TEXT file.
   intFileNum2 = FreeFile
   strFileName2 = Text2.Text
   Open strFileName2 For Binary Access Read As intFileNum2
   lngFileLen2 = LOF(intFileNum2)

   If (lngFileLen1 = 0) Or (lngFileLen2 = 0) Then
      Label1.Caption = "Empty or Missing Files..."
      GoTo Shutdown
   End If

   With rdoQy
      Set .ActiveConnection = rdoCn
      .SQL = "{? = CALL uspInsertBLOB(?, ?, ?) }"
   End With

   rdoQy(0).Direction = rdParamReturnValue
   rdoQy(1).Direction = rdParamInput
   rdoQy(2).Direction = rdParamInput
   rdoQy(3).Direction = rdParamInput
   rdoQy(1) = strFileName1

   intBlocks = lngFileLen1 \ lngBlockSize
   intBlocksLo = lngFileLen1 Mod lngBlockSize

   'AppendChunk IMAGE Column
   ReDim bytData(intBlocksLo)
   Get intFileNum1, , bytData()
   rdoQy(3).AppendChunk bytData()

   ReDim bytData(lngBlockSize)
   For intCnt = 1 To intBlocks
      Get intFileNum1, , bytData()
      rdoQy(3).AppendChunk bytData()
   Next intCnt

   intBlocks = lngFileLen2 \ lngBlockSize
   intBlocksLo = lngFileLen2 Mod lngBlockSize

   'AppendChunk TEXT Column
   strData = String(intBlocksLo, " ")
   Get intFileNum2, , strData
   rdoQy(2).AppendChunk strData
   strData = String(lngBlockSize, " ")

   For intCnt = 1 To intBlocks
      Get intFileNum2, , strData
      rdoQy(2).AppendChunk strData
   Next intCnt

   rdoQy.Execute
   Debug.Print rdoQy(0).Value

   GoTo Shutdown

   ErrHandler:
      MsgBox "Error : " & Err.Number & vbCrLf & Err.Description

   Shutdown:
      Close intFileNum1
      Close intFileNum2
      MousePointer = vbNormal
      Label1.Caption = "BLOB Inserted..."

   End Sub
   '======================