利用存储过程向SQL Server 2000 的表中写入大二进制文件

    通常，我们使用ADO的Appenchunk方法来向SQL Server中的IMAGE写入二进制文件，但是当文件很大或网络带宽很窄时，记录集更新时就会很慢，甚至象死机一样，这时客户将无法忍受。
    本文在存储过程中利用SQL Server的UPDATETEXT语句进行分段写入，很好地解决了这一个问题，读者还可以稍加改进，使之成为断点续传。

在SQL SERVER 2000数据库中有表:
CREATE TABLE [dbo].[tblTest] (
 [id] [int] IDENTITY (1, 1) NOT NULL ,
 [name] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
 [photo] [image] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

存储过程：
ALTER PROCEDURE addImage
(
 @bin varbinary(4096),
 @ID integer
)
AS
DECLARE @ptrval binary(16)

UPDATE tblTest SET photo = '' WHERE id=@ID and photo is null
IF DATALENGTH(@bin)<4096
 SET @bin = SUBSTRING(@bin, 1, DATALENGTH(@bin)-1)
SELECT @ptrval = TEXTPTR(photo) FROM tblTest WHERE id = @ID
UPDATETEXT tblTest.photo @ptrval null NULL @bin

VBA代码：
Private Sub Test(ByVal FileName As String, ByVal id As Long)
    Dim cmm As ADODB.Command
    Dim cnn As New ADODB.Connection
    Dim par As New ADODB.Parameter
    Dim image() As Byte
    Const BLOBSIZE = 4096

    Dim strConn As String
    Dim FileNo As Integer
    Dim lngPosition As Long
    Dim LenF As Long
    Dim NoGr As Long
    Dim i As Long

    lngPosition = 0
    FileNo = FreeFile

    Open FileName For Binary As FileNo
    LenF = LOF(FileNo)

    Debug.Print LenF

    NoGr = LenF \ BLOBSIZE

    strConn = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security
Info=False;Initial Catalog=test;Data Source=(local)"
    cnn.Open strConn

    For i = 1 To NoGr
        Set cmm = New ADODB.Command
        With cmm
            Set .ActiveConnection = cnn
            .CommandType = adCmdStoredProc
            .CommandText = "addImage"

            lngPosition = (i - 1) * BLOBSIZE + 1
            ReDim image(BLOBSIZE)

            Get FileNo, lngPosition, image

            Set par = .CreateParameter("bin", adVarBinary, adParamInput,
BLOBSIZE + 1)

            par.Attributes = adParamLong
            par.AppendChunk image()
            .Parameters.Append par
            Set par = .CreateParameter("ID", adInteger, adParamInput)
            par.Value = id
            .Parameters.Append par
            .Execute

        End With
        Set cmm = Nothing
    Next

    Dim lngLast As Long
    lngLast = LenF Mod BLOBSIZE

    If lngLast > 0 Then
        Set cmm = New ADODB.Command
        With cmm
            .CommandType = adCmdStoredProc
            .CommandText = "addImage"
            Set .ActiveConnection = cnn
            ' 6 = 5 + 1
            lngPosition = (i - 1) * BLOBSIZE + 1
            ReDim image(lngLast)

            Get FileNo, lngPosition, image

            Set par = .CreateParameter("bin", adVarBinary, adParamInput,
lngLast + 1)

            par.Attributes = adParamLong
            par.AppendChunk image()
            .Parameters.Append par
            Set par = .CreateParameter("ID", adInteger, adParamInput)
            par.Value = id
            .Parameters.Append par
            .Execute

        End With
        Set cmm = Nothing
    End If
    Close FileNo

    cnn.Close
    Set cnn = Nothing
End Sub

朱亦文
2004.04.19