在存储过程中利用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 -- 指定记录ID
)
AS
DECLARE @ptrval binary(16)
-- 第一次更新photo字段,将字段内容设置为空
Update tblTest SET photo = '' Where id=@ID and photo is null
-- 当接收的数据少于4096时,要将多余的一个字节去除
IF DATALENGTH(@bin)<4096
SET @bin = SUBSTRING(@bin, 1, DATALENGTH(@bin)-1)
-- 获取 photo 字段的地址指针
Select @ptrval = TEXTPTR(photo) FROM tblTest Where id = @ID
-- 向 photo 字段中添加二进制数据
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
&