Using Long Data Types

Long data types include Microsoft® SQL Server™ ntext, text and image data types. ntext, text and image data is sometimes so large that it cannot be retrieved in a single operation or fit into memory. If the long data can fit into memory, the Value property of the Field object can be used to retrieve all the data in one operation. If the long data is too large to fit into memory, the data must be retrieved or written in chunks. There are two ways to manipulate long data in chunks: through the Field object and through the Parameter object.

The Field object allows you to write and read long data through the Recordset object. The AppendChunk method of the Field object allows you to append data at the end of the current data when the query has already been executed. The GetChunk method allows you to read the data in chunks.

The Parameter object handles long data in a similar way, but there is no GetChunk method for the Parameter object, and there is no Recordset object when you are dealing with long data at run time. With the Parameter object, long data is bound at run time and executed with the Command object.

There are some restrictions for long data when using MSDASQL. If no server cursor is used, all long columns must be to the right of all nonlong columns. If there are multiple long columns, the long columns must be accessed in order (from left to right).

This example shows how to use ADO with SQLOLEDB to read and write image data. The critical routines are the while loops that copy the long data (image) to a variable and write the variable to a record in chunks (using the GetChunk and AppendChunk methods).

Before setting up the destination table in this example, make sure to run the sp_dboption stored procedure:

EXEC sp_dboption 'pubs', 'Select into/bulkcopy', 'True'

  

The destination table is a copy of the pub_info table in the pubs database. Create the table by running:

USE pubs

SELECT * INTO pub_info_x

   FROM pub_info

GO

  

The pub_info_x table is the destination table in which the long data will be inserted.

The ADO code is:

Public Sub AppendChunkX()

  

    Dim cn As ADODB.Connection

    Dim rstPubInfo As ADODB.Recordset

    Dim strCn As String

    Dim strPubID As String

    Dim strPRInfo As String

    Dim lngOffset As Long

    Dim lngLogoSize As Long

    Dim varLogo As Variant

    Dim varChunk As Variant

  

    Const conChunkSize = 100

  

    ' Open a connection.

    Set cn = New ADODB.Connection

    strCn = "Server=srv;Database=pubs;UID=sa;Pwd=;"

  

   cn.Provider = "sqloledb"

   cn.Open strCn

  

   'Open the pub_info_x table.

   Set rstPubInfo = New ADODB.Recordset

   rstPubInfo.CursorType = adOpenDynamic

   rstPubInfo.LockType = adLockOptimistic

   rstPubInfo.Open "pub_info_x", cn, , , adCmdTable

  

   'Prompt for a logo to copy.

   strMsg = "Available logos are : " & vbCr & vbCr

  

   Do While Not rstPubInfo.EOF

      strMsg = strMsg & rstPubInfo!pub_id & vbCr & _

        Left(rstPubInfo!pr_info,

         InStr(rstPubInfo!pr_info, ",") - 1) & vbCr & vbCr

      rstPubInfo.MoveNext

   Loop

  

   strMsg = strMsg & "Enter the ID of a logo to copy:"

   strPubID = InputBox(strMsg)

  

   ' Copy the logo to a variable in chunks.

   rstPubInfo.Filter = "pub_id = '" & strPubID & "'"

   lngLogoSize = rstPubInfo!logo.ActualSize

   Do While lngOffset < lngLogoSize

      varChunk = rstPubInfo!logo.GetChunk(conChunkSize)

      varLogo = varLogo & varChunk

      lngOffset = lngOffset + conChunkSize

   Loop

  

   ' Get data from the user.

   strPubID = Trim(InputBox("Enter a new pub ID:"))

   strPRInfo = Trim(InputBox("Enter descriptive text:"))

  

   ' Add a new record, copying the logo in chunks.

   rstPubInfo.AddNew

   rstPubInfo!pub_id = strPubID

   rstPubInfo!pr_info = strPRInfo

   lngOffset = 0   ' Reset offset.

  

   Do While lngOffset < lngLogoSize

      varChunk = LeftB(RightB(varLogo, lngLogoSize - _

        lngOffset),conChunkSize)

      rstPubInfo!logo.AppendChunk varChunk

      lngOffset = lngOffset + conChunkSize

   Loop

  

   rstPubInfo.Update

  

   ' Show the newly added data.

   MsgBox "New record: " & rstPubInfo!pub_id & vbCr & _

     "Description: " & rstPubInfo!pr_info & vbCr & _

     "Logo size: " & rstPubInfo!logo.ActualSize

  

   rstPubInfo.Close

   cn.Close

  

End Sub

  


(c) 1988-98 Microsoft Corporation. All Rights Reserved.