DOCUMENT:Q197914 28-JUL-2001 [vbwin] TITLE :SAMPLE: RDOBLOBS.EXE RDO and BLOB/TEXT Data with SQL SP PRODUCT :Microsoft Visual Basic for Windows PROD/VER::5.0,6.0 OPER/SYS: KEYWORDS:kbfile kbSample kbRDO kbSQL kbVBp500 kbVBp600 kbGrpDSVBDB kbSQLProg ====================================================================== ------------------------------------------------------------------------------- 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 2001.