Importing and Exporting from a database

Instead of relying on the file system for storing and retrieving files, you may decide to use a database instead. Using the ActiveFile File object, you can store and retrieve files from any database that supports ADO access to BLOBs (Binary Large OBjects).

Database Topics

Setting up your database
Importing files into a database
Exporting files from a database

The first step in using your database to import and export files is to make sure you can access it using Active Data Objects (ADO) from your Active Server Pages. You will want to refer to the Active Server Pages documentation for help on using ADO. However, if you are using the default ODBC provider interface here are a few quick tips:

• Make sure your database is properly configured for ODBC access using the Control Panel
• Make sure the ODBC DSN for your database is a SYSTEM DSN

The next step is to make sure that the database column that you wish to use to import and export from is defined properly. ActiveFile can only import and export from database columns that Active Data Objects (ADO) understands as adLongVarBinary.  Here are some example native datatypes that work:

• For Microsoft Access use the native datatype OLE Object
• For Microsoft SQL Server use the native datatype image
• For Oracle use the native datatype LONG RAW

For help on determining the correct native datatype for other databases, consult the database design documentation for that database.

If you are using a version of ADO prior to ADO 1.5, it is strongly recommended that you upgrade. Earlier versions of ADO may not work properly with your database. For information on upgrading ADO, visit Microsoft's web site at http://www.microsoft.com/data.

Importing files involves storing the data from a file into the BLOB field of a database record that can be accessed through ADO.

The steps to perform an import are simple:

1. Connect to the database via ADO:
   
   Set Connection = Server.CreateObject("ADODB.Connection")
   Connection.Open "MyDSN", "sa"
    
2. Create an ADO Record Set object with the proper cursor location
   
   Set Rs = Server.CreateObject("ADODB.Recordset")
   ' If ADO 1.5 or higher use client cursors
   If CSng(Left(db.Version,3)) >= 1.5 Then
       rs.CursorLocation = 3
   End If
    

   	If you are using a database other than Microsoft Access or SQL Server, such as Oracle, you may need to set the value of rs.CursorLocation to 1.

3. Locate an existing record or create a new one:
   
   Rs.Open "MyTable", Connection, 1,3,2
   Rs.AddNew
    
4. Import the file and update the record
   
   UpFile.Import Rs.Fields.Item("BLOB")
   Rs.Update
   

The following shows a complete example of how to store an uploaded file and the name of the submitter into a database:

' Upload a file Set Post = Server.CreateObject("ActiveFile.Post") Post.Upload "C:\TEMP" Set UpFile = Post.FormInputs("UploadFile").File ' Remember value of submitter Submitter = Post.FormInputs("Submitter").Value ' Now load the file into the database Set Connection = Server.CreateObject("ADODB.Connection") ' Make a connection to a system DSN Connection.Open "MyDSN", "sa" ' Create an ADO resultset Set Rs = Server.CreateObject("ADODB.Recordset") ' If ADO 1.5 or higher use client cursors If CSng(Left(db.Version,3)) >= 1.5 Then     rs.CursorLocation = 3 End If ' Add a new record Rs.Open "MyTable", Connection, 1,3,2 Rs.AddNew ' Set the submitter name Rs.Fields.Item("SUBMITTER") = Submitter ' Store the file into the database UpFile.Import Rs.Fields.Item("BLOB") Rs.Update

For a more complete example, take a look at the ActiveFile Database Explorer sample application that demonstrates both importing and exporting files from a Microsoft Access database.

Exporting files involves retrieving the data  a BLOB field of a database record that can be accessed through ADO and writing it out to a file.

The steps to perform an export are simple:

1. Connect to the database via ADO:
   
   Set Connection = Server.CreateObject("ADODB.Connection")
   Connection.Open "MyDSN", "sa"
    
2. Create an ADO Record Set object with the proper cursor location
   
   Set Rs = Server.CreateObject("ADODB.Recordset")
   ' If ADO 1.5 or higher use client cursors
   If CSng(Left(db.Version,3)) >= 1.5 Then
       rs.CursorLocation = 1
   End If
     

   	If you are using a database other than Microsoft Access or SQL Server, such as Oracle, you may need to set the value of rs.CursorLocation to 3.

3. Locate an existing record:
   
   Query = "select BLOB from MyTable where ID = " & Request("ID")
   Rs.Open Query, Connection, 1,3
    
4. Create a temporary file for holding the results
   
   Set File = Server.CreateObject("ActiveFile.File")
   File.CreateTemp "C:\TEMP"
   
5. Export the file
   
   File.Export Rs.Fields.Item("BLOB"), False
   

The following shows a complete example of how to export a file and download it to the client:

<% Response.Buffer = True ' Open the database Set Connection = Server.CreateObject("ADODB.Connection") Connection.Open "MyDSN", "sa" ' Create an ADO Recordset Set Rs = Server.CreateObject("ADODB.Recordset") ' Query the database to find the file you want to download Query = "select BLOB from MyTable where ID = " & Request("ID") Rs.Open Query,Connection, 1,3 ' Create a temporary file for holding the results Set File = Server.CreateObject("ActiveFile.File") File.CreateTemp "C:\TEMP" ' Grab the file File.Export Rs.Fields.Item("BLOB"), False ' Make sure there is no extraneous output in the response buffer Response.Clear ' And send it down to the browser File.Download ' And cleanup File.Delete ' End the page now to prevent any additional output for the response Response.End %>

For a more complete example, take a look at the ActiveFile Database Explorer sample application that demonstrates both importing and exporting files from a Microsoft Access database.