Home Articles Books Downloads FAQs Tips

Q: Store bitmap and JPEG images in a SQL database


Answer

This FAQ explains how to save images to a database and read those images back into your program. The VCL field and parameter classes provide all of the support that you need.

Tip Note:
This FAQ assumes that your database is a SQL database that can actually store image files in a blob field of some kind. For example, MS SQL Server 7.0 provides an image field type. When you create a table in SQL Server 7.0, you can use the image field type for columns that contain images. I don't want to (nor am I capable of) explaining how to configure the tables in your database so they can store images. Consult the documentation for your database system if you need help creating your tables.

Saving images to a database

Saving images to a database is a three step process. First, you load the contents of the image into a VCL stream object of some kind. An excellent stream class to use is TMemoryStream. If you are storing an image from the file system, you can use the LoadFromStream method of TMemoryStream to get the contents of the image into the stream. If you already have the image loaded, in a TImage for example, you can copy the image into the memory stream by using the SaveToStream method of TBitmap. Wherever your image resides, you have the tools needed to get that image into a TMemoryStream.

The second step is to copy the image from the stream into a parameter of an insert SQL query. The syntax of this step is a little different based on whether you are using ADO or BDE queries. Note that if you are using table controls or TClientDataset controls instead of query controls, then you should copy from the stream into a TField instead of into a parameter. The code examples that follow demonstrate each method.

The last step is to execute your SQL insert query, or to call the Post method of your table.

Here are some examples. These examples assume that the target table is named image_test, and that it contains two columns. The first column is a string field called string_field. The second column is a blob column called image_field. The image goes into the blob column.

//-------------------------------------------------------------
// Example 1: Inserting a bitmap using a BDE query component
void __fastcall TForm1::Button2Click(TObject *Sender)
{
    // load a bitmap into a memory stream object
    std::auto_ptr<TMemoryStream> stream (new TMemoryStream);
    stream->LoadFromFile("androm.bmp");
    stream->Position = 0;

    // Assign the parameters of the query. The SQL property of the query
    // is set to:
    //
    //    insert image_test
    //    (
    //        string_field,
    //        image_field
    //    )
    //    values
    //    (
    //        :string_field,
    //        :image_field
    //    )
    //
    // Note that the image_field param should have its DataType set to ftBlob
    //
    Query1->Params->ParamByName("string_field")->AsString = "androm.bmp";
    Query1->Params->ParamByName("image_field") ->SetBlobData(stream->Memory,
                                                             stream->Size);

    // execute the query to perform the insert
    Query1->ExecSQL();
}

//-------------------------------------------------------------
// Example 2: Inserting a bitmap using a ADO query component
void __fastcall TForm1::Button2Click(TObject *Sender)
{
  // load a bitmap into a memory stream object
  std::auto_ptr<TMemoryStream> stream (new TMemoryStream);
  stream->LoadFromFile("androm.bmp");
  stream->Position = 0;

  // Assign the parameters of the query. The SQL property of the query
  // is the same SQL from the BDE example. The image_field param should
  // have its DataType set to ftVarBytes, ftBlob or ftGraphic
  ADOQuery->Parameters->ParamByName("string_field")->Value= "androm.bmp";
  ADOQuery->Parameters->ParamByName("image_field")->LoadFromStream(stream.get(),
                                                                   ftGraphic);

  // execute the query to perform the insert
  ADOQuery->ExecSQL();
}

//-------------------------------------------------------------
// Example 3: Inserting a bitmap using a table component (BDE or ADO)
void __fastcall TForm1::Button2Click(TObject *Sender)
{
    // load a bitmap into a memory stream object
    std::auto_ptr<TMemoryStream> stream (new TMemoryStream);
    stream->LoadFromFile("androm.bmp");
    stream->Position = 0;

    // Add a record to the table, fill in the fields, and then post the record.
    // Note that Table1string_field and Table1image_field are persistent TField
    // objects. The image field must be a TBlobField, or a TBlobField
    // descendent or this code won't compile. TBlobField is the only field type
    // that provides a LoadFromStream method. Also note that if you use
    // FieldByName to reference the field, you will need to cast the result to
    // TBlobField before you call LoadFromStream because FieldByName returns a
    // TField pointer, not a TBlobField pointer.
    //
    the image_field property should a TBlobField.
    Table1->Append();
    Table1string_field->AsString = "androm.bmp";
    Table1image_field ->LoadFromStream(stream.get(),
                                                        ftGraphic);
    Table1->Post();
}

Loading images from a database

Loading images from a database is also a three step process. In fact, loading the image is just like saving the image, but in reverse.

The first step is to perform a SQL query or open a table component. The result set should include the graphical object. When the VCL opens a dataset with graphical blobs in it, it creates a TBlobField or a TBlobField descendant to store the image.

The second step is to extract the contents of the blob field into a memory stream. Once you get the image into a memory stream, you can perform the final step of saving the image to its final destination. If you want to save the image to the file system, you can call the SaveToFile method of TMemoryStream. If you would rather put the image into a TImage control, you can use TBitmap::LoadFromStream.

Here are some examples of how to load the image from the database. The first the example employs a SQL query (BDE or ADO). The second uses a TTable.

//-------------------------------------------------------------
// Example 4: Loading a bitmap from a database with a
//            query component (BDE or ADO)
void __fastcall TForm1::Button3Click(TObject *Sender)
{
    // query the image table
    Query->Active = false;
    Query->SQL->Text = "select * from image_test\n"
                       "where string_field = 'androm.bmp'";
    Query->Active = true;

    // Create a memory stream and copy the image from the blob
    // field into the stream. Note that Queryimage_field is a
    // a persistent TBlobField created from the fields editor
    std::auto_ptr<TMemoryStream> stream (new TMemoryStream);
    Queryimage_field->SaveToStream(stream.get());

    // save the stream to a BMP file
    stream->Position = 0;
    stream->SaveToFile("androm.bmp");

    // load the stream into the a TImage
    stream->Position = 0;
    Image1->Picture->Bitmap->LoadFromStream(stream.get());

    Query->Active = false;
}


//-------------------------------------------------------------
// Example 5: Loading a bitmap from a database with a
//            table component (BDE or ADO)
void __fastcall TForm1::Button3Click(TObject *Sender)
{
    // query the image table
    Table->Open();

    // Create a memory stream and copy the image from the blob
    // field into the stream. Note that Tableimage_field is a
    // a persistent TBlobField created from the fields editor
    std::auto_ptr<TMemoryStream> stream (new TMemoryStream);
    Tableimage_field->SaveToStream(stream.get());

    // save the stream to a BMP file
    stream->Position = 0;
    stream->SaveToFile("androm.bmp");

    // load the stream into the a TImage
    stream->Position = 0;
    Image1->Picture->Bitmap->LoadFromStream(stream.get());

    Table->Close();
}

Tip Note:

The examples above utilize the SaveToStream method of TBlobField. This is a new member function introduced in TBlobField that is not present in the base class TField. Since TField does not contain a SaveToStream member function, you cannot write code like this:

Table->FieldByName("image_field")->SaveToStream(stream);

If you do, you will get a compiler error stating that SaveToStream is not a member of TField. The error is caused because FieldByName returns a TField *. Although the field in this case is a TBlobField instance, the compiler must use the static return type of the function to verify the syntax of the statement. There is no way the compiler can know that FieldByName will return a TBlobField in this case. All the compiler knows is that the return type is a TField of some kind, and TField does not have a SaveToStream method.

There are a couple of ways to get around this problem. The first is to use persistent fields. When the Fields Editor creates persistent fields, it uses a field class that corresponds to the field type from the dataset. String fields are declared as TStringField objects, and blob fields are declared as TBlobField objects. Since blob fields are declared as TBlobFields, you can use the persistent field to call the SaveToStream method.

Another way to deal with this issue is to downcast the result of FieldByName to a TBlobField. If you choose this method, I encourage you to use dynamic_cast to perform the cast.

TBlobField field =
  dynamic_cast<TBlobField *>(query->FieldByName("image_field"));
if(field)
    field->SaveToStream(stream);

Working with JPEG images

The previous code examples also work for reading and writing JPEG files. In fact, if you only need to move images from the file system to the database, then you can use the previous examples without any modifications (other than changing a few file names). However, if you need to read a JPEG image from a database and display it in your program, then you have to add a little bit of code. You must perform a JPEG decompression step after you read the JPEG image from the database and load it into the memory stream. Fortunately, the VCL TJPEGImage class can handle this for you in only a few lines of code.

The next code example demonstrates how to read a JPEG image from a database and display it in an image control. The code combines the previous examples with the code from the FAQ on how to read JPEG images. In fact, this code example employs the same stream technique as the FAQ on JPEG resources. The only difference is where the JPEG image comes from (ie a database instead of the program's binary resources).

//-------------------------------------------------------------
// Example 6: Loading a JPEG from a database

#include <jpeg.hpp>

void __fastcall TForm1::Button3Click(TObject *Sender)
{
    // query the image table
    Query->SQL->Text = "select * from image_test\n"
                       "where string_field = 'space.jpg'";
    Query->Active = true;

    // Create a memory stream and copy the image from the blob
    // field into the stream. Note that Queryimage_field is a
    // a persistent TBlobField created from the fields editor
    std::auto_ptr<TMemoryStream> stream (new TMemoryStream);
    Queryimage_field->SaveToStream(stream.get());

    // create a jpeg image object, and tell it to load the jpeg
    // from the memory stream
    stream->Position = 0;
    std::auto_ptr<TJPEGImage> jpeg(new TJPEGImage);
    jpeg->LoadFromStream(stream.get());

    // Now load the jpeg into the image. This step
    // decompresses the JPEG to a bitmap
    Image1->Picture->Bitmap->Assign(jpeg.get());

    Query->Active = false;
}

So how do you save a JPEG image to a database? Well, that depends on whether you just need to save an existing JPEG file on your file system, or whether you need to compress a bitmap into a JPEG before saving it. If you only need to save a JPEG file from the file system, then use the code from Examples 1-3. You can write a JPEG file directly to the database, just like you would for a BMP file.

If you need to compress an image before saving it to the database, then you will once again need to employ the TJPEGImage class. The next two code examples demonstrate how you can do this. The first example assumes that the source image is a .BMP file on your file system. The second example saves an image that has already been loaded into a TImage component. Note that for these two examples, I assume that you are use BDE query components. If you are using ADO controls or table controls, then substitute code as neede. See Examples 1-3 for more details.

//-------------------------------------------------------------
// Example 7: Saving a BMP file as a JPEG image to a database.

#include <jpeg.hpp>

void __fastcall TForm1::Button2Click(TObject *Sender)
{
    // load a bitmap into a TBitmap object
    std::auto_ptr<TBitmap> bitmap(new TBitmap;
    bitmap->->LoadFromFile("androm.bmp");

    // create a jpeg object and assign the bitmap
    // to the jpeg. This performs the JPEG compression step.
    std::auto_ptr<TJPEGImage> jpeg(new TJPEGImage);
    jpeg->Assign(bitmap.get());

    // Now create a stream object and write the compressed JPEG
    // image to the stream. Once we have the JPEG data in the
    // stream, we can get it into the database.
    std::auto_ptr<TMemoryStream> stream (new TMemoryStream);
    jpeg->SaveToStream(stream.get());
    stream->Position = 0;

    // Save the stream to the database. This is exactly like Example 1
    Query1->Params->ParamByName("string_field")->AsString = "androm.bmp";
    Query1->Params->ParamByName("image_field") ->SetBlobData(stream->Memory,
                                                             stream->Size);

    // execute the query to perform the insert
    Query1->ExecSQL();
}

//-------------------------------------------------------------
// Example 8: Writing a TImage as a JPEG to a database.

#include <jpeg.hpp>

void __fastcall TForm1::Button2Click(TObject *Sender)
{
    // create a jpeg object and assign the bitmap of the
    // TImage to the jpeg object.
    std::auto_ptr<TJPEGImage> jpeg(new TJPEGImage);
    jpeg->Assign(Image1->Picture->Bitmap);

    // Now create a stream object and write the compressed JPEG
    // image to the stream. Once we have the JPEG data in the
    // stream, we can get it into the database.
    std::auto_ptr<TMemoryStream> stream (new TMemoryStream);
    jpeg->SaveToStream(stream.get());
    stream->Position = 0;

    // Save the stream to the database. This is exactly like Example 1
    Query1->Params->ParamByName("string_field")->AsString = "androm.bmp";
    Query1->Params->ParamByName("image_field") ->SetBlobData(stream->Memory,
                                                             stream->Size);

    // execute the query to perform the insert
    Query1->ExecSQL();
}

Notes

Note 1: If you are reading bitmaps, icons, or Windows meta files from a database, you can save a few lines of code by using the TDBImage data-aware control. You can connect the TDBImage to a blob field via a TDataSource component. The TDBImage control is so easy to use, that I didn't bother describing it. The FISHFACT example program that ships with BCB demonstrates how to use this control. Note that you can only use TDBImage to view images, and you can only view native Windows images (ie not JPEGs).

Note 2: The code examples for displaying images did not account for result sets that contain more than one record. This is one of the nice features of TDBImage. When you use a TDBImage, it automatically updates the image on the screen as you scroll through the dataset. If you don't use TDBImage, then you will need to add some supporting code if you're result sets return multiple rows. You will need to update the image on the screen each time you scroll from one record to another. The dataset components provide events that can help you accomplish this (namely AfterOpen, AfterClose, and AfterScroll).

Note 3: The VCL provides a class called TBlobStream that is worth mentioning in this FAQ. TBlobStream is similar to TFileStream. TFileStream encapsulates a file in a stream object. When you read from or write to a TFileStream, you are actually reading from or writing to the underlying file. TBlobStream does the same thing, except that it encupulates reading and writing blob fields.

When you create a TBlobStream, you pass it two constructor arguments. The first is a TBlobField. This is the field that you want to read or write. The second argument is an enum that tells the blob stream whether it will be reading from or writing to the underlying blob field. After you construct a blob stream, any operation that you perform on it will affect the internal blob field.

We could have used TBlobStream in our examples. The code example below demonstrates how we could have written Example 4 using TBlobStream

//-------------------------------------------------------------
// Example 9: Loading a bitmap using a blob stream
void __fastcall TForm1::Button3Click(TObject *Sender)
{
    // query the image table
    Query->Active = false;
    Query->SQL->Text = "select * from image_test\n"
                       "where string_field = 'androm.bmp'";
    Query->Active = true;

    // create a blob stream on the field
    std::auto_ptr<TBlobStream> stream
      (new TBlobStream(Query->FieldByName("image_field"),bmRead));

    // Tell the picture control to load from the blob stream
    Image1->Picture->Bitmap->LoadFromStream(stream.get());
    Query->Active = false;
}

This code is noticeably more compact than the code from Example 4, yet it does pretty much the same thing. Note that TBlobStream has some drawbacks. First of all, TBlobStream can only be used with BDE datasets. If you use the ADO datasets, you have to replace TBlobStream with TADOBlobStream. I prefer to have as much of my code remain constant as possible between the two, so I use TMemoryStream instead. Secondly, you navigating a record invalidates all existing blob streams that are connected to fields in a dataset. You have to delete and recreate each blob stream when you navigate a record.

Note 4: You may have noticed that my code makes use the auto_ptr template class. auto_ptr is a wrapper class for a plain old pointer in C++. When an auto_ptr instance goes out of scope and is destroyed, its destructor automatically calls the delete operator on the pointer that you passed in during construction. This way, you don't have to call delete manually.

This functionality is extremely beneficial for the obvious reason that you don't have to write delete statements all over the place. It is also beneficial for some not so obvious reasons. Namely, that auto_ptr cleans up objects even when an exception occurs. If you don't use auto_ptr, you have to be careful to ensure that exceptions don't bypass your delete statements, and this can be very difficult to do cleanly. auto_ptr is a great asset in all types of prorams, but especially in database programs, since just about any call on a database object can generate an exception.

Note 5: The techniques presented in this FAQ don't apply only to graphics. You can use the code examples to store files of any type, including sound files, movie files, Word files and Excel files. Now obviously, you wouldn't try to load these types of objects into a TImage. But the example code for reading a file from the file system and saving it to the database applies to all types of files. The code that reverses the process by reading from the database and writing back to the file system can also be applied to other types of files.

Note 6: The code examples for reading an image from the database fail miserably when the image has been stored using an embedded OLE object. MS Access is notorious for this. Actually, you can read and write the image just find, but you can't send the contents of an embedded OLE image to TImage because TImage doesn't understand the OLE image format. If you have to deal with an embedded OLE image, send me an email or search the newsgroups at google for posts by me. I have posted an example of how to deal with the evil that is embedded OLE. I may post that code in the form of an FAQ later.



Copyright © 1997-2000 by Harold Howe.
All rights reserved.