ID: Q197914
The information in this article applies to:
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).
The following file is available for download from the Microsoft Software Library:
~ RDOblobs.exe (size: 35246 bytes)
For more information about downloading files from the Microsoft Software Library, please see the following article in the Microsoft Knowledge Base:
ARTICLE-ID: Q119591
TITLE : How to Obtain Microsoft Support Files from Online Services
FileName Size
---------------------------------------------------------
RDOblobs.frm 10KB
uspGetBLOB.sql 1KB
uspInsertBLOB.sql 1KB
RDO_Blobs.exe 36KB
RDOblobs.vbp 1KB
Readme.txt 1KB
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.
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
'======================
For more information, please see the following articles in the Microsoft Knowledge Base:
ARTICLE-ID: Q153238
TITLE : HOWTO: Use GetChunk and AppendChunk Methods of RDO Object
ARTICLE-ID: Q194975
TITLE : HOWTO: Sample Functions Demonstrating GetChunk and
AppendChunk
Additional query words:
Keywords : kbRDO
Version : WINDOWS:5.0,6.0
Platform : WINDOWS
Issue type : kbhowtoLast Reviewed: January 1, 1999