发信人: nightcat()
整理人: (1999-07-22 12:14:07), 站内信件
|
Jul-02-1999
Downloading and Uploading BLOBs from a SQL Database Using a
Browser
Part I of II
By Pieter Reint Siegers Kort
SQL Server has two data types that can be used to manipulate
large amounts of data - the text and image field data types.
The former is used to store large text objects, and the latter
Binary Large OBjects ( BLOB s) - although somewhat confusingly
the term 'object' in the context of SQL has nothing to do with
object orientated programming. The manner in which SQL works
with binary data types means that it's possible to use any sort
of binary stored file - JPG images, Word documents, PDF files,
and so on.
BLOBs used to be pretty tricky to work with in web development,
largely because of their structure and disk storage (physically
they are pointer-concatenated byte blocks). The introduction of
ASP/ADO (and in the example I will be looking at, some ActiveX
technology) has made it rather easier to up- and down - load
BLOBs (where 'upload' denotes the transfer of binary files from
the client browser to the SQL database and download the transfer
back again.) As you抣l see in the follow up article, on Monday,
it takes little more than a few minor code modifications to
prepare the data to be uploaded to the database.
To make use of the code we'll be looking at in this article
you're going to need:
Internet Explorer 4.01 with sp2
Visual Basic 6.0 with sp2
Visual InterDev 6.0 with sp2
ADO 2.0 with sp2 (comes with MDAC 2.0 with sp2)
on the client
NT4 preferably with sp5 (sp4 will do also)
Internet Explorer 4.01 with sp2
IIS 4.0 and ADO 2.0 with sp2
on the Web server and
SQL Server 6.5 with sp5a
on the SQL server.
You'll also need some knowledge about the creation of ActiveX
components in VB5 or VB6 - if you need help with this, or any
other programming language or technology covered in this
article, there are links to various help pages and tutorials
at the end.
The Scenario
Suppose there are some images that you want others to annotate
for you, on their machines, in their various locations, and to
save in a SQL server database table afterwards. Let's say that,
for the sake of the applications performance, the images for
download are small JPG previews of the originals, and have a
size of approximately 20-30 KB. The annotated images, together
with the related data, will be used at a later date to process
changes in the original image, and this will take place in
another department in another location. Because your users work
in different offices around the world, your application should
install, update, and register its components automatically
without their intervention. (They have, of course, installed
Internet Explorer 4.01 sp1/sp2, which is required for your
application to run smoothly, and have made the appropriate
alterations to their browser preferences.)
In this particular circumstance, you could develop an Intranet
with direct access, and the appropriate file permissions
assigned, to both the Web and SQL servers, which would make use
of a client component so that the annotations can be made
remotely. (Had the browser simply needed to display the images,
it would be is better to use a COM component on the Web server.)
In order to download the images from the SQL database, and
display it on the client抯 browser for further working, you can
use a custom COM ActiveX Control developed with VB6. This uses
ActiveX Data Objects (ADO) to access the database, retrieve the
requested image and save it on the local disk. If you are
familiar with components, you know that they have properties
and methods. Properties are like variables, and methods are
similar to VB public subs or functions, in that they perform
actions - database access, copying files, converting data, etc.
The Public properties and methods of a component can be
programmed from the outside world (i.e. accessed by another
component, a Web page, for example). You can make the component
methods and/or properties public by using VB ActiveX Control
Interface Wizard, or through your own methods and properties.
A component can also have private properties and methods, which
are used internally by the usercontrol or the embedded (intrinsic)
controls.
An Approach
Let's suppose that after the component has been developed,
tested, and compiled, it is called FotoEd6ocx. Among other
properties and methods, it has a public method which is called
LoadImage2(). This makes use of two ADO objects: the Connection
and Recordset objects, of which the Connection object, logically
enough, is used to connect to the database server, and the
Recordset object to receive and manipulate the query data. Let
have a closer look at what this method does for us:
Initially the ADO Connection and Recordset objects are named as
variants. These will be used later for the connection to the
database and retrieval of the image:
Dim con As New ADODB.Connection
Dim rs As New ADODB.Recordset
Then the connection string is created, which is used by the
component instead of a custom Data Source Name (DSN). DSNs are
used to define connections to a database server or file. Not
using them means eliminating the need to create a DSN on every
client machine but still requires that the core of ADO be
installed on it:
con.ConnectionString = "driver={SQL Server};" & _
"server=" & strServerSQL & ";uid=sa;pwd=;database=pubs"
con.ConnectionTimeout = 60
con.Open
Finally the SQL Stored Procedure (more of this later) is
executed, which is responsible for the retrieval of the image
bytes from the database:
if strServerSQL = strServerSQLRem then
strQuery = "GetPreview " & strPub_Id$
else
strQuery = strServerSQLRem & ".pubs..GetPreview " & strPub_Id$
end if
This also works on remote SQL servers, so long as the remote SQL
servers are registered as remote servers on the calling SQL
server, and vice versa. (You should do this for every connected
SQL server.) This is the dialog box of the submenu Remote
Servers from the menu Server in the SQL program Enterprise
Manager:
To add a Remote Server, type in the server name and in the
section Remote Logins, check Translate all Remote Login Names
to and choose the appropriate login (for example, a? checking
Trusted. Then click Add, repeat the steps for all other SQL
servers and close the window. Repeat the procedure on each SQL
server.
The SQL Stored Procedure (which is a compiled query) used to
retrieve the image bytes is quite simple:
CREATE Procedure GetPreview
@strPub_Id int
AS
set nocount on
/* maximum amount of bytes returned is 32KB */
set textsize 32768
select Preview=Logo
from Pub_Info
where pub_id=@strPub_Id
return
The table used here is an existing table from the pubs database
(Pub_Info). The line set textsize 32768 is added to increase
the amount of bytes returned by the query (the default size is
4 KB) when executed remotely.
This is the code fragment that saves the bytes on the local disk:
lngPictSize = rs(0).ActualSize
FileNum = FreeFile
Open FileName For Binary Access Write Lock Write As FileNum
Do While lngOffset < lngPictSize
varChunk = rs(0).GetChunk(conChunkSize)
Put #1, , varChunk
lngOffset = lngOffset + conChunkSize
If lngOffset > lngPictSize Then Exit Do
Loop
Close FileNum
The methods ActualSize and GetChunk determine the image size
and pass the bytes to the byte array (VarChunk) respectively.
The method GetChunk uses 32 KB chunks. The download at the end
of the article shows how the complete code for the LoadImage2()
method of the component fits together.
The next thing to do is to prepare the component for download
from the server. First you need to reference the VB project to
the ADO library (Microsoft ActiveX Data Objects 2.0 Library.)
Then compile the control, quit VB, and use VB6 Package &
Deployment Wizard to create an Internet Package and include all
the nesessary OCXs and DLLs, and associated dependencies. Be
sure to add the core ADO file Msado15.dll. You should also
include the VB run-time components. The wizard should produce
a document containing important guidelines for testing your
control.
The created Internet Package consists of a CABinet (CAB) file
and a sample HTML file. The CAB file is placed on the Web server
in a virtual directory, which allows virtual addressing to the
CAB file. In this way, the path specified in the codebase
property of the <OBJECT> tag (see the explanation of this tag
properties later in this article) is relative to the Intranet
and not to the specific machine, which makes it unnecessary to
change the path when the Intranet is copied. To protect your
files you can assign Read permission on the files (provided
your server uses Windows NT NTFS file system). If you want
maximum security, then it is better to place the CAB in a normal
but protected folder, but then the path of the codebase property
is an absolute path.
When users request a Web page containing the control definition,
their machine is checked for an installation of the CAB
components. If there isn't one, or the components are outdated,
the missing or updated files are downloaded, installed and
automatically registered: if everything is already on the
client then the local loads instantly.
So now your all set to integrate the component in a Web page.
Visual InterDev 6, which is part of MS Visual Studio 6, is
great for this, but if you want simplicity you can always use
Notepad. After we have created a workspace and a project we
will add an ASP page which has also a VBScript function (running
on the client-side) which will communicate with the component.
This code excerpt is from the sample HTML Web page containing
the <OBJECT> tag declaration:
<OBJECT classid="CLSID:416B1AD0-A61F-11D2-BCCD-00A0246D10AE"
codeBase=FotoEditor6ocx.CAB#version=6,0,0,3
id=FotoEd6ocx style="LEFT: 0px; TOP: 0px">
<PARAM NAME="_ExtentX" VALUE="13732">
<PARAM NAME="_ExtentY" VALUE="15478">
</OBJECT>
The <OBJECT> tag contains the declaration of the component. The
parameter ?CODE >classid?identifies a Global Unique IDentifier
(GUID.) GUIDs are used to uniquely identify your component, so
that it can be re-used by another computer without generating
run-time errors. GUIDs are generated based on the network card
ID of the computer from which it is compiled.
Another important parameter is the codebase, which points to a
relative directory on the Web server (in this case, the CAB file
is placed in the same directory as the Web pages). From this
directory the CAB components are downloaded when the client
requests the page. The attribute version checks the component
version on the client machine. The id parameter contains the
name of the component and is used when referring to the
component抯 methods and properties.
The component can be placed in your page using, for example, a
<TABLE> tag. This is the code segment of the ASP page which
calls the component抯 methods/properties:
<SCRIPT LANGUAGE="VBScript">
' init vars (code within ?lt;%= %>?is ASP code and runs on the ser
ver)
strPub_Id = "<%= Trim(Request.QueryString("Pub_Id")) %>"
strServerSQL = "<%= Trim(Request.QueryString("ServerSQL")) %>"
strServerSQLRem = "<%= Trim(Request.QueryString("ServerSQLRem")
) %>"
' load the image
call FotoEd6ocx.LoadImage2(strPub_Id,strServerSQL,strServerSQLR
em)
' visualize the window if it is hidden
Parent.focus()
</SCRIPT>
Note the call to the method LoadImage2() of the component.
Client-side VBScript is used to fill in the method parameters
and invoke the method. Only the parameter data is passed to the
component, and so the component takes care of the connection
and retreival of the image. Client-side VBScript is being used,
but as your target browser is IE, you could choose to use
client-side J(ava)Script, although the way methods and
properties are called in J(ava)Script is different and you would
need to translate the script shown here. I prefer VBScript
because the structure is narrowly related to VB, and I find
JavaScript is better suited to managing windows and frames, and
programming properties of HTML objects.
The last line - Parent.focus() - is a little trick I use which
allows the window re-appear when hidden on the screen. Using
the statement causes the window to pop-up after refreshing its
contents.
An afterthought
Although I'll draw more comprehensive conclusions at the end
of Monday's Part II, after I've shown you a method of uploading
BLOBs to SQL server, it seems appropriate at this point to draw
attention to the possibility of allowing users to access the Web
server anonymously. If they're mapped to a local Windows NT
account, having execute permission on SQL Stored Procedures only,
then your SQL database is better protected. You could also
implement a dedicated Proxy Server (otherwise known as a
firewall) as a gateway to the Internet, which can restrict
access to your pages and databases to specific sites.
--
※ 来源:．月光软件站 http://www.moon-soft.com．[FROM: 202.103.124.22]
|
|