Images in a Database

Many applications require images or thumbnails of images to be stored in a database. ImgX can store and retrieve images from a database with it's memory file support and ADO's GetChunk method which reads and writes to a database BLOB field. Use Import.FromMemoryFile to retrieve an image from a database and Export.ToMemoryFile to store images into a database.

This example will use ADO to access the database. ADO is universal and is compatible with Access, SQL, Oracle and others. This example will show you how to open a database connection and access the image field in a database. For more information on ADO and database access please see your Visual Studio documentation.

Database Image Field

The database you are accessing must have a field that can store binary data. In Access it is an OLE Data Type. In SQL it is an Image data type.

Establish a Database Connection

The connection string is what is unique to the different database types. The following shows a connection string for some of the most common database types:

ConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=DB_PATH" '## MS Access 2000
ConnString = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=DB_PATH" '## MS Access 97
ConnString = "Provider=SQLOLEDB;Data Source=SERVER_NAME;database=DB_NAME;uid=UID;pwd=PWD;" '## MS SQL Server 6.x/7.x/2000 (OLEDB connection)
ConnString = "driver={SQL Server};server=SERVER_NAME;uid=UID;pwd=PWD;database=DB_NAME" '## MS SQL Server 6.x/7.x/2000 (ODBC connection)
ConnString = "driver=MySQL;server=SERVER_IP;uid=UID;pwd=PWD;database=DB_NAME" '## MySQL
ConnString = "DSN_NAME" '## DSN

To open the database connection and create a recordset containing the images the following code can be used assuming the Images field is named "Images":

Dim db
Dim rst

Set DB = Server.CreateObject("ADODB.Connection")
Set Rst = Server.CreateObject("ADODB.Recordset")

DB.Open ConnString 'connection string to your database
Rst.Open "Select * From Images", DB, adOpenDynamic, adLockOptimistic

Storing Images

Now that the recordset is open, you can stream images into and out of the database. The following code shows how to use a byte array to store an image into the database. You will need to store the size of the file in another field in the database so you know how big to make the byte array when reading it.

' get a byte array for this image
bytes = ImgX.Export.ToMemoryFile ixmfJPG

' save the size of the thumbnail in the database
Rst("ImageSize").Value = UBound(bytes()) + 1

' finally, save it to the database
Rst("Image").AppendChunk bytes

' save the record
Rst.Update

Retrieving Images

Retrieving the image is fairly straightforward as well. This example uses one large chunk for the image. You could retrieve and store the image in smaller chunks by creating a loop that calls GetChunk and AppendChunk with smaller chunks of memory as that would be more memory efficient.

' load the thumbnail
t = CLng(Rst("ThumbSize").Value)

' load it into ImgX
ImgX.Import.FromMemoryFile Rst("Thumbnail").GetChunk(t), ixmfJPG