KnowledgeBase Archive

An Archive of Early Microsoft KnowledgeBase Articles

View on GitHub

Q197914: SAMPLE: RDOBLOBS.EXE RDO and BLOB/TEXT Data with SQL SP

Article: Q197914
Product(s): Microsoft Visual Basic for Windows
Version(s): 5.0,6.0
Operating System(s): 
Keyword(s): kbfile kbSample kbRDO kbSQL kbVBp500 kbVBp600 kbGrpDSVBDB kbSQLProg
Last Modified: 28-JUL-2001

-------------------------------------------------------------------------------
The information in this article applies to:

- Microsoft Visual Basic Enterprise Edition for Windows, versions 5.0, 6.0 
-------------------------------------------------------------------------------

SUMMARY
=======

RDOblobs.exe is a sample file that demonstrates how to use Remote Data Objects
(RDO) AppendChunk/GetChunk with stored procedures to Insert and/or Retrieve SQL
Server 6.5 IMAGE and TEXT datatypes (BLOBS).

MORE INFORMATION
================

The following files are available for download from the Microsoft Download
Center:

  RDOblobs.exe
  (http://download.microsoft.com/download/vb60ent/demo/1/WIN98/EN-US/Rdoblobs.exe)

Release Date: DEC-29-1998

For additional information about how to download Microsoft Support files, click
the article number below to view the article in the Microsoft Knowledge Base:

  Q119591 How to Obtain Microsoft Support Files from Online Services

Microsoft used the most current virus detection software available on the date of
posting to scan this file for viruses. Once posted, the file is housed on secure
servers that 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":

  Sites can realize performance gains by coding most of their SQL statements
  into stored procedures and having applications call the stored procedures
  rather than issuing the SQL statements themselves. This offers the following
  benefits:

  * Higher performance
  The SQL statements are parsed and compiled only when the procedures are
  created, not when the procedures are executed by the applications.

  * Reduced network overhead
  Having an application execute a procedure instead of sending sometimes complex
  queries across the network can reduce the traffic on the network. If an ODBC
  application uses the ODBC { CALL MyProcedure} syntax to execute a stored
  procedure, the ODBC driver makes additional optimizations that eliminate the
  need to convert parameter data (for more information, see "ODBC Call vs.
  Transact-SQL EXECUTE").

  * Better consistency
  The organization's business rules can be coded and debugged once in a stored
  procedure, and they will then be consistently applied by all of the
  applications. The site does not have to depend on all application programmers
  coding their SQL statements correctly in all the applications.

  * Better accuracy
  Most sites will have their best SQL programmers developing stored procedures.
  This means that the SQL statements in procedures tend to be more efficient
  and have fewer errors than when the code is developed multiple times by
  programmers of varying skill levels.

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
     '======================

REFERENCES
==========

For more information, please see the following articles in the Microsoft
Knowledge Base:

  Q153238 HOWTO: Use GetChunk and AppendChunk Methods of RDO Object

  Q194975 HOWTO: Sample Functions Demonstrating GetChunk and AppendChunk


Additional query words:

======================================================================
Keywords          : kbfile kbSample kbRDO kbSQL kbVBp500 kbVBp600 kbGrpDSVBDB kbSQLProg 
Technology        : kbVBSearch kbAudDeveloper kbZNotKeyword6 kbZNotKeyword2 kbVB500Search kbVB600Search kbVB500 kbVB600
Version           : :5.0,6.0
Issue type        : kbhowto

=============================================================================

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Copyright Microsoft Corporation 1986-2002.