4.1 Why Upload to the Database
4.2 Sample Database
4.3 ODBC-based File Uploads
4.4 ODBC-based File Exporting from the Database
4.5 ADO-based File Uploads
4.6 ADO-based File Exporting From the Database
4.7 Exporting Files from the Database to a Client Browser
Most modern database management systems allow for storing
arbitrary binary files in database records as "BLOBs" (binary large objects).
In Microsoft Access, a file can be stored in a field of the type OLE Object,
in SQL Server, the corresponding data type is IMAGE, and in Oracle Long Raw.
If you are implementing a file storage application
such as a document archive or image repository, storing the files in the database is
a sensible alternative to keeping them in a folder. The advantages of the database
approach are:
- Data consolidation. Your repository will probably need a database
anyway, so why not keep all the data pieces in one place.
- No filename collisions. You no longer need
to worry about multiple files having the same name,
since the files are now identified by their record IDs rather than filenames.
- Simplified backup. Backing up your database
will backup both your data and files in a single step.
The main disadvantage of this approach is that the database storage and retrieval
of files is somewhat slower than with the regular file system.
To demonstrate AspUpload's database features, a sample MS Access database,
aspupload.mdb, has been included in the installation. It can
be found in the folder \Samples\04_db together with the sample ASP files that use it.
The database contains a single table, MYIMAGES, with the following columns:
| Column |
Type |
Description |
| id |
Autonumber |
Unique record ID |
| image_blob |
OLE Object |
Stores file BLOB |
| filename |
Text (50) |
Stores file name |
| filesize |
Number |
Stores file size |
| description |
Text (255) |
Stores file description |
| hash |
Text (50) |
Stores one-way hash value of file |
Before running the code samples, make sure
the NTFS permissions on the database file aspupload.mdb are to set to Everyone/Full Control using
Windows Explorer.
If you prefer to work with SQL Server, you can recreate this table
in SQL Server using the SQL script CreateTable.sql located in the same folder.
With AspUpload, an uploaded file can be saved in an ODBC-enabled database
in just one line of code via the method File.ToDatabase. This method accepts two
arguments: an ODBC connection string, and an INSERT or UPDATE SQL statement
which must contain one question mark sign (?) as a placeholder
for the file being saved in the database, for example:
File.ToDatabase "DSN=mydb;UID=jsmth;PWD=xxx",_
"INSERT INTO IMAGES(img, name) VALUES(?, 'name.ext')"
This statement inserts the current file into the field
img of the table IMAGES, and the string 'name.ext' into the field name.
The DSN parameter in the first argument points to a system DSN creatable
via the ODBC control panel. A DSNless connection string for MS Access
must explicitly reference the full path to the MDB file, as follows:
File.ToDatabase "Driver={Microsoft Access Driver (*.mdb)};DBQ=c:\path\db.mdb", ...
For SQL Server, your connection string may look as follows:
"Driver=SQL Server;Server=MYSERVER;UID=sa;PWD=xxxxxx"
If you need to replace an existing file in a record, you should use an UPDATE
statement instead of INSERT, for example:
"UPDATE MYIMAGES SET img=?, name='newname.ext' WHERE id=3"
The sample files odbc.asp and odbc_upload.asp
demonstrate the use of the ToDatabase method. The form
located in the file odbc.asp (not shown here) contains a file item THEFILE
and a text item DESCR. Here is what odbc_upload.asp looks like:
<HTML>
<BODY>
<%
Set Upload = Server.CreateObject("Persits.Upload")
' Capture files
Upload.Save "c:\upload"
' Obtain file object
Set File = Upload.Files("THEFILE")
If Not File Is Nothing Then
' Build ODBC connection string
Connect = "Driver={Microsoft Access Driver (*.mdb)};DBQ=" & Server.MapPath(".\aspupload.mdb")
' If you use SQL Server, the connecton string must look as follows:
' Connect = "Driver=SQL Server;Server=MYSERVER;UID=sa;PWD=xxxxxxxxx"
' Build SQL INSERT statement
SQL = "INSERT INTO MYIMAGES(image_blob, filename, description, filesize) VALUES(?, '"
SQL = SQL & File.Filename & "', '"
SQL = SQL & Replace(Upload.Form("DESCR"), "'", "''") & "', "
SQL = SQL & File.Size & ")"
' Save to database
File.ToDatabase Connect, SQL
Response.Write "File saved."
Else
Response.Write "File not selected."
End If
%>
</BODY>
</HTML>
|
Note the use of the built-in Replace function which replaces all occurrences
of the ' character (single quote) by two single quotes to avoid
a run-time error if the description contains single quotes.
Click the link below to run this code sample:
http://localhost/aspupload/04_db/odbc.asp
AspUpload also provides the top-level method Upload.ToDatabaseEx
which enables you to save an arbitrary file on your system in the database, not just an uploaded file.
The first argument for this method is a file path, the other two are the same as for
the method File.ToDatabase.
The File.ToDatabase method has a counterpart, Upload.FromDatabase,
which performs the opposite operation: exports a file from a database record back to the hard drive.
This method expects three arguments.
The first argument is an ODBC connection string. The second argument
is a SELECT statement that must return one record containing a BLOB.
The BLOB data field name must immediately follow the SELECT keyword.
The following code (sample file odbcexport.asp) exports all files from our sample database that were placed
there by the previous code sample:
<HTML>
<BODY>
<%
' Build ODBC connection string
Connect = "Driver={Microsoft Access Driver (*.mdb)};DBQ=" & Server.MapPath(".\aspupload.mdb")
' For SQL Server use a string simlar to this:
' Connect = "Driver=SQL Server;Server=MYSERVER;UID=sa;PWD=xxxxx"
Set rs = Server.CreateObject("adodb.recordset")
Set Upload = Server.CreateObject("Persits.Upload")
' Open MYIMAGES table
rs.Open "MYIMAGES", Connect, 2, 3
' Scroll through records
Count = 0
While Not rs.EOF
Filename = Trim(rs("filename"))
SQL = "select image_blob from myimages where id=" & rs("id")
Upload.FromDatabase Connect, SQL, "c:\upload\" & Filename
Response.Write "File exported: c:\upload\" & Filename & "<BR>"
Count = Count + 1
rs.MoveNext
Wend
Response.Write "<P>" & Count & " files exported."
%>
</BODY>
</HTML>
|
Here, we have to employ ADO to scroll through all the records of our sample table
and call FromDatabase on each individual record.
Downloading a file from the database directly to a client browser
is possible without using AspUpload or any other third-party component,
it can be achieved with ADO alone. This will be covered later in this chapter.
Click the link below to run this code sample:
http://localhost/aspupload/04_db/odbcexport.asp
File.ToDatabase is not the only way to save files in the database
with AspUpload. Instead of ODBC, you can use the ADO Recordset object
in conjunction with the File.Binary property. Using the Binary property,
a file can be assigned to the recordset much the same way as a regular numeric or string value,
for example:
rs.Open "MYIMAGES", Connect, 2, 3
rs.AddNew
Set File = Upload.Files("FILE1")
rs("image_blob").Value = File.Binary
rs("filename").Value = File.FileName
rs("filesize").Value = File.Size
rs.Update
This code is more intuitive as it does not require building complex
SQL statements. Instead, the traditional ADO objects are used. This approach
works equally well for inserts and updates.
If the ultimate destination of a file is the database, you may consider
using uploads to memory (described in the previous chapter)
for better security and performance. The sample files ado.asp
and ado_upload.asp demonstrate the combined use of memory uploads and File.Binary
for saving files in the database.
This code sample also utilizes one-way hashing to determine whether a newly
uploaded file already exists in the database. A one-way hash function
is an algorithm which uses a variable-length input such as an arbitrary file
or text string, and produces a fixed-length output (128 bit or 160 bit
for the hash algorithms MD5 and SHA1, respectively).
AspUpload offers MD5 hash value computation via the property File.MD5Hash.
The term "one-way" is used because it is
practically impossible to come up with an input which would
produce a given hash value. Also, it is impossible
to come up with two different documents which would hash to the same value.
This remarkable feature of the one-way hash function can be used
to determine whether a given document already exists in the database.
We store each file's hash value along with other information,
and before adding a new file to the database, we look up its hash value.
If the value is already in the database, we do not save the file.
<HTML>
<BODY>
<%
Set Upload = Server.CreateObject("Persits.Upload")
' we use memory uploads, so we must limit file size
Upload.SetMaxSize 100000, True
' Save to memory. Path parameter is omitted
Count = Upload.Save
' Obtain file object
Set File = Upload.Files("THEFILE")
If Not File Is Nothing Then
' Build ADO connection string
Connect = "Driver={Microsoft Access Driver (*.mdb)};DBQ=" & Server.MapPath(".\aspupload.mdb")
' If you use SQL Server, the connecton string may look like this:
' Connect = "Provider=SQLOLEDB;Server=SRV;Database=mydb;UID=sa;PWD=xxx"
' Use ADO Recordset object
Set rs = Server.CreateObject("adodb.recordset")
' Optional: check whether this file already exists using MD5 hash
Hash = File.MD5Hash
rs.Open "SELECT * from MYIMAGES WHERE Hash='" & Hash & "'", Connect, 2, 3
If Not rs.EOF Then
Response.Write "This file already exists in the database."
Response.End
End If
rs.Close
' Reopen recordset to insert file
rs.Open "MYIMAGES", Connect, 2, 3
rs.AddNew
rs("image_blob") = File.Binary
rs("filename") = File.FileName
rs("filesize") = File.Size
rs("hash") = Hash
rs("description") = Upload.Form("DESCR")
rs.Update
Response.Write "File saved."
Else
Response.Write "File not selected."
End If
%>
</BODY>
</HTML>
|
Click the link below to run this code sample:
http://localhost/aspupload/04_db/ado.asp
http://localhost/aspupload/04_db/ado.aspx 
Needless to say, AspUpload offers an ADO-based method for exporting
files from the database to hard drive. The method
is called Upload.FromRecordset
which accepts a recordset value and a local path as parameters.
The usage of this method is demonstrated by the code sample adoexport.asp.
Click the link below to run this code sample:
http://localhost/aspupload/04_db/adoexport.asp
A file saved in the database can be downloaded to the client browser
directly without saving it to the hard drive first. No third-party components
are necessary, downloading from the database can be achieved with
ADO alone.
To download a file from the database, you need to provide a link
on your web page pointing to an ASP script that calls Response.BinaryWrite.
For example, your HTML page contains the following link:
<A HREF="download.asp?id=2">Click here to download</A>
The file download.asp may look as follows:
<%
Set db = Server.CreateObject("ADODB.Connection")
db.Open Connect
SQL = "SELECT * FROM MYIMAGES where id = " & Request("id")
Set rs =db.Execute( SQL )
Response.ContentType = "application/octet-stream"
' let the browser know the file name
Response.AddHeader "Content-Disposition", "attachment;filename=" & Trim(rs("filename"))
' let the browser know the file size
Response.AddHeader "Content-Length", rs("filesize")
Response.BinaryWrite rs("image_blob")
%>
|
To display an image stored in the database, you should use an <IMG> tag instead
of <A>, as follows:
<IMG SRC="download.asp?id=3">
Note that the download script must not contain any HTML tags such as <HTML> or <BODY>, just pure ASP script.
The sample files filelist.asp and filelist_download.asp
demonstrate this technique. The script filelist.asp lists all files previously uploaded to
the database and generates download links for them. The links invoke the file filelist_download.asp
similar to the download script shown above.
Click the link below to run this code sample:
http://localhost/aspupload/04_db/filelist.asp
|
