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

时　间:2004-05-11 15:36:46
作　者:zhuyiwen   ID：61  城市：岳阳
摘　要:利用存储过程向SQL Server 2000 的表中写入大二进制文件
正　文:

在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, adParamInpu

Access软件网QQ交流群 (群号:483923997) 　　　　　　Access源码网店