利用存储过程向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
(责任编辑：admin)
