Passing blob/binary data to a stored procedure in SQL Server 2008 1

[AndyInNC]

What is the easiest way to serialize an image (or document, for that matter) via VB Script to pass it into a stored procedure to be saved in a table? The data type is varbinary(MAX).

I'm just looking for the ASP side, not the SQL.

Thanks.

 

[AndyInNC]

The solution I wanted was how to pass the stream data to a stored procedure. This does not do that, but it's doing the job I guess.

This approach creates a client-side recordset from the SQL table and updates it from there.

It's a good thing I'm also the DBA.

[COLOR=blue]Sub[/color] SavePicture(iPeopleID)
    [COLOR=blue]Dim[/color] rs, stmStream, sFileName, sSQL
    sFileName = "D:\Uploaded_Signatures\Signature_" & iPeopleID & ".gif"
    
    [COLOR=blue]Dim[/color] cn
    [COLOR=blue]Set[/color] cn = Server.[COLOR=blue]CreateObject[/color]("ADODB.Connection")
    cn.Provider = "sqloledb"
    cn.Open(strConn)
    [COLOR=blue]Set[/color] rs = Server.[COLOR=blue]CreateObject[/color]("ADODB.Recordset")
    sSQL = "SELECT blobSignature From People WHERE iPeopleID = " & iPeopleID
    rs.Open sSQL, cn, adOpenKeyset, adLockOptimistic, adCmdText
    
    [COLOR=blue]Set[/color] stmStream = [COLOR=blue]CreateObject[/color]("ADODB.Stream")
    stmStream.Type = 1 'adTypeBinary
    stmStream.Open
    stmStream.LoadFromFile sFileName
    rs.Fields("blobSignature").Value = stmStream.Read
    rs.Update
    
    rs.Close
    [COLOR=blue]Set[/color] rs = [COLOR=blue]Nothing[/color]
    stmStream.Close
    [COLOR=blue]Set[/color] stmStream = [COLOR=blue]Nothing[/color]
    cn.Close
    [COLOR=blue]Set[/color] cn = [COLOR=blue]Nothing[/color]
    
    [COLOR=blue]Kill[/color] sFileName
[COLOR=blue]End Sub[/color]

 

[tsuji]

[0] You can do it with command object and use its parameters collection to pass on the data.

[1] Suppose the stored procedure be symbolically sp01(@iPeorpleID int, @file_pic varbinary(max)). Then you do it like this.
[tt]
[green]'your givens or retrieved as needed
'oconn your established connection object
iPeopleID=123
sFileName = "D:\Uploaded_Signatures\Signature_" & iPeopleID & ".gif"
scmd="sp01" 'your input as mentioned[/green]

set ostream=createobject("adodb.stream")
with ostream
.Type = 1 'adTypeBinary
.Open
.LoadFromFile sFileName
file_pic=.read 'byte()
.close
end with
set ostream=nothing
file_size=ubound(file_pic)+1

set ocmd=createobject("adodb.command")
with ocmd
set ocmd.activeconnection=oconn 'your connection object
.commandtype=4 'adCmdStoredProc=4
.commandtext=scmd

.parameters.refresh
'adInteger=3, adParamInput=1, size=4 bytes
.parameters.append .createparameter("@iPeopleID",3,1,4)
'adVarBinary=204, adParamInput=1, size=file_size
.parameters.append .createparameter("@file_pic",204,1,file_size)

.parameters.item("@iPeopleID").value=iPeopleID
.parameters.item("@file_pic").value=file_pic

.execute
end with
set ocmd=nothing
[/tt]
[1.1] Apart from the error control, that has been done and the task is dependent on the performance of the stored procedure.

 

[AndyInNC]

That's EXACTLY what I was looking for! Very straightforward.

Thanks!

 

[tsuji]

Thanks. Just a supplementary note.

The first line within the with ocmd can be rewritten (though, as it shown, it is just fine) to make the style uniform.
[tt]
with ocmd
set .activeconnection=oconn 'your connection object
'etc etc
end with
[/tt]