发信人: nightcat()
整理人: (1999-07-22 12:14:18), 站内信件
|
Downloading and Uploading BLOBs to a SQL Database Using a Browser
Part II of II
By Pieter Reint Siegers Kort
In Friday's article I looked at a way to download BLOBs from a
SQL Server database to remote machines for editing work, as
well as the necessary tools and languages. (Part 1should you
need to refer back to it for that information.) Today, I'm
going show you how to upload the disk image to the SQL database,
which is a bit more complex because of the necessary preparation
of the bytes. The order of events will be:
first, an ActiveX control is used to fill an ASP variable with
the bytes of the file on disk
then, through ADO, a parameter object is created and filled.
This will be passed to an ADO command object
the rest of the SQL table fields will be assigned proper values
and finally, a SQL Stored Procedure will be executed in order to
store the new record in the table.
While it is entirely possible to add a method to the already
existing client component that will transfer the image bytes to
the Web server, I have chosen to create a server-side component
instead. This makes sense to me primarily because the byte
preparation is carried out using an ASP page, which runs on the
server, but also because it gives me the opportunity to do the
processing on the server-side.
The component is called GenPrevPC, and has the public method
FillPreview()which looks like this:
Public Function FillPreview(ByVal PathPreview As String) As Variant
' declarations
Dim Fnum As Integer
Dim LenImage As Long
Dim ContPreview() As Byte
' open preview file and read its content (path is UNC)
Fnum = FreeFile
Open PathPreview For Binary Access Read As Fnum
' determine length of image
LenImage = LOF(Fnum)
' redim array of bytes
ReDim ContPreview(LenImage)
' read bytes
Get Fnum, , ContPreview
' and close the file
Close Fnum
' return the result (assign to function name)
FillPreview = ContPreview
End Function
The file is accessed using a UNC path. The length of the file
is determined using the function LOF(Fnum). Then the bytes are
assigned to the array variable ContPreview. The function
declaration As Variant allows us to return the result to the
function name - an action performed by the code phrase
FillPreview = ContPreview at the end of the function.
Let抯 have a look at the part of the ASP page calling the
component抯 method FillPreview(). The whole lot looks like
this:
<%
' retrieving the physical path to the image file
PathUNC = Server.MapPath(Trim(Request.Form("URLFoto")))
' convert to UNC if path type is DOS (local)
if InStr(1,PathUNC,"\\") = 0 then
PathUNC = "\\" & Trim(Request.Form("ServerFoto")) & Mid(PathUNC,3)
end if
' creating the destination UNC path using PathUNC to dinamically obt
ain the filename
PathPreview = "\\" & Trim(ServLocal) & "\FotoEditor6\Previstas\" & R
ight(PathUNC,Len(PathUNC)- InStrRev(PathUNC,"\"))
' create an instance of GenPrevPC.dll at the server
Set GenPrevPCi = Server.CreateObject("GenPrevPC.GenPC")
' copy the image to a local folder
GenPrevPCi.CopyImage PathUNC,PathPreview
' assign contents and length of the binary file to an ASP variable
ContPreview = GenPrevPCi.FillPreview(PathPreview)
' liberate resources
Set GenPrevPCi = nothing
%>
Some of these lines of code are more significant than others -
the first one to examine in detail is the first line itself:
PathUNC = Server.MapPath(Trim(Request.Form("URLFoto")))
This will forge the absolute path to the image. When the path
is in the URL format, it must be converted to a Universal
Naming Convention (UNC) path. UNCs are paths which point to a
computer using its NetBios name instead of a drive name (which
is done in normal DOS paths): for example \\servername\temp\x.jpg.
UNCs allow us to access computers without having to map the drive
on the accessing computer (in this case, the Web server). The next
line of code assures that the path is a UNC: if not (if, for
example, the URL is local), it will be converted to a valid UNC
path using the local Web server name. After that, the destination
path is created next using the PathUNC variable.
This line instantiates the COM component at the server:
Set GenPrevPCi = Server.CreateObject("GenPrevPC.GenPC")
After which, the image is copied to a local folder, making all
the necessary data available locally. PathPreview is the
destination path, and points to a local directory on the Web
server:
GenPrevPCi.CopyImage PathUNC,PathPreview
The variable ContPreview is filled with the bytes from the disk
by calling the component抯 method FillPreview. Note that a VB
Byte Array is used to return the bytes, and then assigned to
the function name (a fairly common way to return data in VB):
ContPreview = GenPrevPCi.FillPreview(PathPreview)
Finally, the really interesting bit: it is possible to upload
an image in just one pass to the SQL database table, along with
the other field values, using the ADO Parameter object. First,
the parameter object is created:
Set objParPrev = Server.CreateObject("ADODB.Parameter")
And then its properties filled in:
objParPrev.Name = "@Preview"
objParPrev.Type = adLongVarBinary '(NOTE: use adLongVarChar for tex
t BLOBs)
objParPrev.Attributes = adParamLong
objParPrev.Size = LenB(ContPreview)
objParPrev.Value = ContPreview
Of which the Name property is the SQL table column name,
prefixed with @. The Type property for images is of type
adLongVarBinary, which correlates to the image data type in
SQL. The Attributes property is assigned the value adParamLong,
which indicates that the parameter object accepts long binary
data. The Size property is assigned the byte size of the image
file in question, using the function LenB. And last of all, the
image bytes are assigned to the Value property.
It's time to put the pieces together. Now that the parameter
object is created and properly assigned with values, we can
continue to prepare the rest of the fields of the SQL table:
<%' create the parameter object:
Set objParPrev = Server.CreateObject("ADODB.Parameter")
objParPrev.Name = "@Preview"
objParPrev.Type = adLongVarBinary '(use adLongVarChar for text BL
OBs)
objParPrev.Attributes = adParamLong
objParPrev.Size = LenB(ContPreview)
objParPrev.Value = ContPreview
' assign SP name
objCmd.CommandText = "UploadImage2SQL"
' agregating parameters
objCmd.Parameters.Append objCmd.CreateParameter("RETURN_VALUE", adIn
teger,adParamReturnValue, 4)
objCmd.Parameters.Append objCmd.CreateParameter("@id", adInteger, ad
ParamInput, 4, CLng(id))
objCmd.Parameters.Append objParPrev
' execute the SP
objCmd.Execute
' return the result of execution
objCmdReturn = CInt(objCmd(0))
' close the object to free resources
set objParPrev = nothing %>
Finally the query is executed against SQL and the image is
inserted into the database table. A RETURN_VALUE is included
in order to receive information about the result of the query.
This can be manipulated by server- or client- side script.
This code excerpt would generate an error message in the case
of failure, for instance:
CREATE PROCEDURE UploadImage2SQL
@id int,
@Preview image=null
AS
set nocount on
insert Images
values(@id,@Preview)
return
The procedure inserts the image bytes into the image field
Preview together with its assigned id.
The SQL table can be created, executing the following script
in the Query Tool of SQL 6.5 (or use Visual InterDev 6 and
create a new procedure):
CREATE TABLE dbo.IMAGES (
id int NOT NULL,
Preview image NULL
)
GO
It is possible to add any type of field to this table.
Conclusion
In the two parts to this article I have tried to demonstrate
how (relatively) easy it is to download and upload BLOBs to
a SQL database table using a Web browser. The code samples
given are not meant to give a complete solution, but should
serve as a guide to develop a more complete web site which
both takes advantage of the technologies available, and also
integrates them.
There are other ways of uploading and downloading images. The
solution I've shown here is taken from a real world application,
and is not necessarily always the best one. Some important
things to think about when you're choosing your optimal solution
are:
Internet security issues, as ever, like file protection and
database security - you might consider firewalls, and anonymous
user access which allows you more control.
Limiting your audience through browser type and version
restrictions.
Even if your audience has appropriate browser types and
versions, their preferences can still influence the functionality
of your applications because you抮e using ActiveX. They need to
allow ActiveX controls to be downloaded, installed, and run on
their machines, by adapting the settings of the browser
preferences.
-----------------------------------------------------------------
--
※ 来源:．月光软件站 http://www.moon-soft.com．[FROM: 202.103.124.22]
|
|