Chapter 4: Saving Files and/or Filenames in 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:

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:

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:

For SQL Server, your connection string may look as follows:

If you need to replace an existing file in a record, you should use an UPDATE statement instead of INSERT, for example:

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:

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:

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:

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.

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:

The file download.asp may look as follows:

To display an image stored in the database, you should use an <IMG> tag instead of <A>, as follows:

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