HOWTO: Use GetChunk and AppendChunk Methods of RDO Object

ID: Q153238


The information in this article applies to:


SUMMARY

This article describes how to use the GetChunk and AppendChunk methods of an RDO column object. Included is the code for a working example of how to implement this behavior.

The GetChunk and AppendChunk methods work with the LongVarChar and LongVarBinary column types, also known as TEXT and IMAGE columns, in Microsoft SQL Server. To identify these column types in RDO, use the <Column Object>.Type property that will return the constants rdLongVarChar or rdLongVarBinary, or use the <Column object>.ChunkRequired property to determine if you need to use the Get/AppendChunk methods to access the column. Each of these column types is commonly referred to as Binary Large Objects (BLOBs), so the term BLOB will be used for the remainder of this article.

Following are some suggestions for using BLOBs with RDO:

With the ODBC cursor library, it is not possible to use the GetChunk or AppendChunk methods on a resultset returned from a stored procedure. This is because the BLOB data does not come across the pipe with the rest of the resultset. RDO has to go back and use the SQLGetData or SQLPutData ODBC AP functions on the column when you request it with the RDO GetChunk or AppendChunk methods. When a stored procedure creates a result set that is returned to your application, RDO can use the data in the result set, but it cannot go back to the base tables and columns and perform the SQLGetData/SQLP tData because all it knows is that the SQL Statement was something like "{call myproc(...)}", and there is no way for the cursor library to know how to ask for the BLOB data because there is no base table or column expressed there.

With server side cursors, it is possible to get at your BLOB data. The server-side cursor knows the content of the stored procedure and can thus get at the base table. A limitation of this is that you can't create a server-side cursor based on a stored procedure that has anything besides just one single select statement in it (a SQL Server restriction), so it is pretty limiting and doubtful that you would be able to use this as your primary technique.

The fact that users want to update their BLOB column demands that they expose their base tables and create the cursor by using a standard select statement from that base table. This would be true even if you were coding directly to ODBC (not an RDO thing), as well as dbLibary (a proprietary SQL Server API). If you use Jet, you can't update cursors based on stored procedures at all because they always become read-only.


MORE INFORMATION

The following example is divided into three separate procedures, Command1_Click, ColumnToFile, and FileToColumn. ColumnToFile and FileToColumn are two self-contained procedures that you should be able to paste directly into your code if you are moving BLOB data back and forth from your table to files on disk. Each of the procedures accept parameters that can be provided by your application. Command1_Click contains the example code that makes the connection to your database, creates the table CHUNKTABLE if it does not exist, and calls ColumnToFile and FileToColumn procedures with the proper parameters.

  1. Start a new project in Visual Basic. Form1 is created by default.


  2. Add a Command button, Command1, to Form1.


  3. Paste the following code into the General Declarations section of Form1:
    
       Private Sub Command1_Click()
         MousePointer = vbHourglass
         Dim cn As rdoConnection
         Dim rs As rdoResultset, TempRs As rdoResultset
         Dim cnstr As String, sqlstr As String
         cnstr = "Driver={SQLServer};Server=myserver;Database=pubs;Uid=sa;Pwd="
         sqlstr = "Select int1, char1, text1, image1 from chunktable"
    
         rdoEnvironments(0).CursorDriver = rdUseServer
         Set cn = rdoEnvironments(0).OpenConnection( _
           "", rdDriverNoPrompt, False, cnstr)
         On Error Resume Next
         If cn.rdoTables("chunktable").Updatable Then
           'table exists
         End If
         If Err > 0 Then
           On Error GoTo 0
           Debug.Print "Creating new table..."
           cn.Execute "Create table chunktable(int1 int identity, " & _
                      "char1 char(30), text1 text, image1 image)"
           cn.Execute "create unique index int1index on chunktable(int1)"
         End If
         On Error GoTo 0
         Set rs = cn.OpenResultset(Name:=sqlstr, _
           Type:=rdOpenDynamic, _
           LockType:=rdConcurRowver)
         If rs.EOF Then
           rs.AddNew
           rs("char1") = Now
           rs.Update
           rs.Requery
         End If
         Dim currec As Integer
         currec = rs("int1")
         rs.Edit
         FileToColumn rs.rdoColumns("text1"), App.Path & "\README.TXT", 102400
         FileToColumn rs.rdoColumns("image1"), App.Path & "\SETUP.BMP", 102400
         rs("char1") = Now  'need to update at least one non-BLOB column
         rs.Update
    
         'this code gets the columnsize of each column
         Dim text1_len As Long, image1_len As Long
         If rs("text1").ColumnSize = -1 Then
           'the function Datalength is SQL Server specific
           'so you may have to change this for your database
           sqlstr = "Select Datalength(text1) As text1_len, " & _
                    "Datalength(image1) As image1_len from chunktable " & _
                    "Where int1=" & currec
           Set TempRs = cn.OpenResultset(Name:=sqlstr, _
             Type:=rdOpenStatic, _
             LockType:=rdConcurReadOnly)
           text1_len = TempRs("text1_len")
           image1_len = TempRs("image1_len")
           TempRs.Close
         Else
           text1_len = rs("text1").ColumnSize
           image1_len = rs("image1").ColumnSize
         End If
    
         ColumnToFile rs.rdoColumns("text1"), App.Path & "\text1.txt",  _
           102400, text1_len
         ColumnToFile rs.rdoColumns("image1"), App.Path & "\image1.bmp",  _
           102400, image1_len
         MousePointer = vbNormal
      End Sub
    
       Sub ColumnToFile(Col As rdoColumn, ByVal DiskFile As String, _
         BlockSize As Long, ColSize As Long)
         Dim NumBlocks As Integer
         Dim LeftOver As Long
         Dim byteData() As Byte   'Byte array for LongVarBinary
         Dim strData As String    'String for LongVarChar
         Dim DestFileNum As Integer, i As Integer
    
         ' Remove any existing destination file
         If Len(Dir$(DiskFile)) > 0 Then
           Kill DiskFile
         End If
    
         DestFileNum = FreeFile
         Open DiskFile For Binary As DestFileNum
    
         NumBlocks = ColSize \ BlockSize
         LeftOver = ColSize Mod BlockSize
         Select Case Col.Type
           Case rdTypeLONGVARBINARY
             byteData() = Col.GetChunk(LeftOver)
             Put DestFileNum, , byteData()
             For i = 1 To NumBlocks
               byteData() = Col.GetChunk(BlockSize)
               Put DestFileNum, , byteData()
             Next i
           Case rdTypeLONGVARCHAR
             For i = 1 To NumBlocks
               strData = String(BlockSize, 32)
               strData = Col.GetChunk(BlockSize)
               Put DestFileNum, , strData
             Next i
             strData = String(LeftOver, 32)
             strData = Col.GetChunk(LeftOver)
             Put DestFileNum, , strData
           Case Else
             MsgBox "Not a ChunkRequired column."
         End Select
         Close DestFileNum
    
       End Sub
    
       Sub FileToColumn(Col As rdoColumn, DiskFile As String, _
       BlockSize As Long)
         'moves a disk file to a ChunkRequired column in the table
         'A Byte array is used to avoid a UNICODE string
         Dim byteData() As Byte   'Byte array for LongVarBinary
         Dim strData As String    'String for LongVarChar
         Dim NumBlocks As Integer
         Dim filelength As Long
         Dim LeftOver As Long
         Dim SourceFile As Integer
         Dim i As Integer
         SourceFile = FreeFile
         Open DiskFile For Binary Access Read As SourceFile
         filelength = LOF(SourceFile) ' Get the length of the file
         If filelength = 0 Then
           Close SourceFile
           MsgBox DiskFile & " empty or not found."
         Else
           ' Calculate number of blocks to read and left over bytes
           NumBlocks = filelength \ BlockSize
           LeftOver = filelength Mod BlockSize
           Col.AppendChunk Null
    
           Select Case Col.Type
             Case rdTypeLONGVARCHAR
               ' Read the 'left over' amount of LONGVARCHAR data
               strData = String(LeftOver, " ")
               Get SourceFile, , strData
               Col.AppendChunk strData
               strData = String(BlockSize, " ")
               For i = 1 To NumBlocks
                 Get SourceFile, , strData
                 Col.AppendChunk strData
               Next i
               Close SourceFile
             Case rdTypeLONGVARBINARY
               ' Read the left over amount of LONGVARBINARY data
               ReDim byteData(1, LeftOver)
               Get SourceFile, , byteData()
               Col.AppendChunk byteData()
               ReDim byteData(1, BlockSize)
               For i = 1 To NumBlocks
                 Get SourceFile, , byteData()
                 Col.AppendChunk byteData()
               Next i
               Close SourceFile
             Case Else
               MsgBox "not a chunkrequired column."
           End Select
         End If
    
       End Sub 


  4. You will need to change the Server, Database, UID, and PWD values in the cnstr variable in order to connect to your database.


  5. The code in the Command1_Click event expects to find two files named README.TXT and SETUP.BMP in the current directory. These files are usually found in the Windows directory. You can either move these files to your current directory or change the path to match another bitmap and text file on your hard drive.


  6. Press the F5 key to start the program.


  7. Click the Command1 button to execute the RDO code. The code will automatically create a table named chunktable, if it does not already exist, and move the text and bitmap files into and out of the BLOB columns.



REFERENCES

Hitchhiker's Guide to Visual Basic and SQL Server, Microsoft Press. ISBN: 1-55615-906-4.

For additional information, please see the following article in the Microsoft Knowledge Base:

Q152715 : RDO 1.0b Release Now Available

Additional query words: kbVBp400 kbVBp600 kbdse kbDSupport kbVBp kbRDO kbVBp500


Keywords          : 
Version           : WINDOWS:4.0,5.0,6.0
Platform          : WINDOWS 
Issue type        : 

Last Reviewed: May 15, 1999