AspUpload.com - User Manual. Chapter 4 -- Saving Files and/or Filenames in the Database

4. Database

5. Progress Bar

6. Security

7. Images

Chapter 4. Saving Files and/or Filenames in the Database

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

4.1 Why Upload to the Database

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