Jump to content




Recent Status Updates

View All Updates

Developed by TechBiz Xccelerator
Photo
- - - - -

Storing Images in Database (MySQL and MS Access example)

store image mysql

  • Please log in to reply
5 replies to this topic

#1 Luthfi

Luthfi

    CC Leader

  • Expert Member
  • PipPipPipPipPipPipPip
  • 1,320 posts
  • Programming Language:PHP, Delphi/Object Pascal, Pascal, Transact-SQL
  • Learning:C, Java, PHP

Posted 10 December 2011 - 02:23 AM

Previous tutorials in this series:
Overview
Beside plain simple data, such as strings and numbers, in most (if not all) modern database we usually can also store digital values of nearly unlimited size. We can use this for example to store images, text files, executables, and binary libraries. This kind of values usually called BLOB, short for binary large object. Fields to store these values also refered as blob fields.

See this wikipedia page for more information about BLOB.

The following database is known to support blob fields (note: the list covers only a very small fraction of database supporting blob fields).
  • MySQL
  • MS SQL Server
  • MS Access
  • Interbase
  • Firebird

Handling BLOB value in Delphi datasets
There is no primitive data type in Delphi directly available to handle BLOB value. Therefore we have to settle with handling BLOB as stream. TBlobStream is a descendant of TStream that was defined to handle BLOB values as stream. See here for official information on TBlobStream.

Excerpt from Delphi documentation about TBlobStream:

DBTables.TBlobStream is a stream object that provides services which allow applications to read from or write to field objects that represent Binary large object (BLOB) fields.

Use DBTables.TBlobStream to access or modify the value of a BLOB field in a BDE-enabled dataset. TBlob stream works with persistent TBlobField objects (including descendants of TBlobField such as TGraphicField and TMemoField). BLOB fields use BLOB streams to read data from and write data to the dataset.

DBTables.TBlobStream allows objects that have no specialized knowledge of how data is stored in a BLOB field to read or write such data by employing the uniform stream mechanism.

To use a BLOB stream, create an instance of DBTables.TBlobStream, use the methods of the stream to read or write the data, and then free the BLOB stream. Do not use the same instance of DBTables.TBlobStream to access data from more than one record. Instead, create a new DBTables.TBlobStream object every time you need to read or write BLOB data on a new record.


The most important thing you should note is that you can not reuse an instance of TBlobStream. You must free it as soon as you are done with it, and create new one for another record.

Another important thing you should remember is that it is prefered to call a dataset's CreateBlobStream method instead of directly call TBlobStream's constructor. Because this approach will give the dataset a chance to prepare itself.

Writing BLOB field
Basic steps in writing to a BLOB field:
  • If the dataset is no in editing mode, put the dataset into the mode by either calling Edit or Insert.
  • Create a TBlobStream by calling respected dataset's CreateBlobStream method and specifying the field and bmWrite as access mode.
  • Fill the returned TBlobStream with your binary value.
  • Free the TBlobStream.
  • Store the changes by posting the modification, either by calling Post or CheckBrowseMode.

Reading BLOB field
Basic steps in writing to a BLOB field:
  • Create a TBlobStream by calling respected dataset's CreateBlobStream method and specifying the field and bmRead as access mode.
  • Read the content of the returned TBlobStream
  • Free the TBlobStream.
  • Use the read content according to task in hand

In next sections I show you the implementations of the above steps in order to store and read images to and from some database.

Handling Images in MS Access
For our demo project with MS Access, create a new Delphi application project. Drop these components to the main forms and rename them respectively:
  • TADOConnection, cnnMain
  • TADOTable, tblMovies
  • TDataSource, dsMovies
  • TOpenPictureDialog, OpenPictureDialog1
  • TADOCommand, qCmd
  • TDBGrid, DBGrid1
  • TImage, Image1
  • TBitBtn, btnOpenUrl
  • TBitBtn, btnAddPict
  • TBitBtn, btnAddPictSQL

Arrange the controls/components into a layout something like this:

fmMain_Access_design001.png

Wire tblMovies's and qCmd's Connection properties to cnnMain. Set dsMovies' Dataset to tblMovies. Wire DBGrid1's DataSource to dsMovies. Build the connection string of cnnMain by double clicking it. Make sure the connection string pointing to the sample ms access datase attached here (download and save it somewhere in your computer).

Set Movies to tblMovies' TableName property. Build the persistent fields for tblMovies by double clicking it and then Add all fields in the dialog that pops up.

Give the following SQL command to qCmd's CommandText.
UPDATE Movies SET Picture=:Picture WHERE [ID]=:ID
Check that qCmd has two parameters, Picture and ID, by opening its Parameters property in object inspector.

Writing Image With Table-kind of Dataset
What I mean with Table-kind datasets are datasets that containing records, e.g. TTable, TADOTable, TQuery with SELECT query, and TADODataset with SELECT query. For these kind of datasets, we can store BLOBs directly to the corresponding fields. This is where TBlobStream plays important role.

In our demo project we will use a TADOTable which we named tblMovies.

Put the following codes for btnAddPict's event handler:
procedure TForm1.btnAddPictClick(Sender: TObject);
var
  vBlobStream: TStream;
  vFileStream: TStream;
  vEditing: Boolean;
begin
  // do we have record to store the picture to? Don't continue if not
  if tblMovies.IsEmpty then Exit;
  // do the user select a picture file? Dont continue if not
  if not OpenPictureDialog1.Execute then Exit;
  // are we in editing mode? Enter into one if not
  vEditing := tblMovies.State in [dsEdit, dsInsert];
  if not vEditing then tblMovies.Edit; // enter editing mode if not in one
  try
    // create writable TBlobStream for field 'Picture' of tblMovies
    [b]vBlobStream := tblMovies.CreateBlobStream(tblMoviesPicture, bmWrite);[/b]
    try
      // create stream of the selected picture file
      vFileStream := TFileStream.Create(OpenPictureDialog1.FileName, fmOpenRead or fmShareDenyNone);
      try
        // copy the content of picture stream to the TBlobStream
        [b]vBlobStream.CopyFrom(vFileStream, 0);[/b]
      finally
        vFileStream.Free;
      end;
    finally
      // freeing writable TBlobStream also writes its content to the underlying field
      vBlobStream.Free;
    end;
  finally
    // post the changes if we are the one putting into editing mode
    if not vEditing then tblMovies.CheckBrowseMode;
  end;
end;

I think the code and the comments pretty much explained everything about the process.

Writing Image with SQL command
In this case we want to store images using SQL command INSERT or UPDATE. It will be different with the method we used with table-kind of datasets, since obviously here we don't have a TField to help us out. No TBlobStream for us here. However we have TParameter objects to work with.

Basically TParameter is a way to communicate with our sql command. We pass values using TParameter, and we also read values using TParameter. Declaration of TParameter is done directly inside the sql command itself. Just add a colon (:) in front of the parameter's name, and later Delphi will create instance(s) of corresponding TParameter(s) for you.

For our example here, we have given this sql: UPDATE Movies SET Picture=:Picture WHERE [ID]=:ID. The bold parts are the ones
that define our two TParameter-s, i.e. Picture and ID. Of course initially these TParameter-s have no value. So before we execute the sql, we need to give values to them. TParameter is capable to hold BLOB value, like I show you in the OnClick event handler of btnAddPictSQL below:

procedure TForm1.btnAddPictSQLClick(Sender: TObject);
var
  vFileStream: TStream;
begin
  // do we have record to store the picture to? Don't continue if not
  if tblMovies.IsEmpty then Exit;
  // do the user select a picture file? Dont continue if not
  if not OpenPictureDialog1.Execute then Exit;
  // load the picture file into a stream
  vFileStream := TFileStream.Create(OpenPictureDialog1.FileName, fmOpenRead or fmShareDenyNone);
  try
    // give ID of the current movie record to the :ID parameter of qCmd
    qCmd.Parameters.ParamByName('ID').Value := tblMoviesID.Value;
    // Assign the content of FS stream to the :Picture parameter
    [b]qCmd.Parameters.ParamByName('Picture').LoadFromStream(vFileStream, ftBlob);[/b]
    // execute the sql
    qCmd.Execute;

    // refresh the table so it shows the new picture
    RefreshTable;
  finally
    vFileStream.Free;
  end;
end;

Reading Image from Dataset
Here is the code in our demo project to read image from the dataset (in this case tblMovies).
procedure TForm1.LoadPicture;
var
  BS: TStream;
  vGraphic: TGraphic;
begin
  // clear the currently displayed picture
  Image1.Picture.Graphic := nil;
  // do we have something to display?
  if tblMovies.IsEmpty then Exit;

  // Get a TBlobStream instance from the content of field Picture of tblMovies, with read-only access
  BS := tblMovies.CreateBlobStream(tblMovies.FieldByName('Picture'), bmRead);
  try
    // create the object to parse the image information into jpg picture
    vGraphic := TJPEGImage.Create;
    try
      // give the content of our TBlobStream to the jpg image object
      vGraphic.LoadFromStream(BS);
      
      // give the jpg image object to TImage to display
      Image1.Picture.Graphic := vGraphic;
    finally
      vGraphic.Free;
    end;
  finally
    BS.Free;
  end;
end;

Here is the source code of the demo project, including the database: [ATTACH]4379[/ATTACH]

Handling Images in MySQL

Will be continued in next post, including how to store image/blob using mysql stored procedure. So stay tune

Attached Files


  • 1

#2 Alexander

Alexander

    YOL9

  • Moderator
  • 3,956 posts
  • Location:Vancouver, Eh! Cleverness: 200
  • Programming Language:C, C++, PHP, Assembly

Posted 10 December 2011 - 04:27 AM

Thank you for your submission Luthfi, your zipped demo is a great way to complete the feel of the tutorial. I am looking forward to your next part.

Alexander.
  • 0

All new problems require investigation, and so if errors are problems, try to learn as much as you can and report back.


#3 Luthfi

Luthfi

    CC Leader

  • Expert Member
  • PipPipPipPipPipPipPip
  • 1,320 posts
  • Programming Language:PHP, Delphi/Object Pascal, Pascal, Transact-SQL
  • Learning:C, Java, PHP

Posted 10 December 2011 - 05:17 AM

Thanks Alex.

Just having problem with my virtual machine that has mysql server. Trying to find out why its hostname is not recognized.
  • 0

#4 Luthfi

Luthfi

    CC Leader

  • Expert Member
  • PipPipPipPipPipPipPip
  • 1,320 posts
  • Programming Language:PHP, Delphi/Object Pascal, Pascal, Transact-SQL
  • Learning:C, Java, PHP

Posted 16 December 2011 - 02:11 AM

Handling Images in MySQL
We will be using ZEOS library to provide access to our MySQL server. If you don't have it, get it here (Zeos at sourceforge). Zeos is an open source project which aiming to provide Delphi users a single library, thus a single framework, to access many kind of database.

Before creating our demo project. We have to create our sample database. Using any MySQL client you prefer (e.g. mysql command line or PhpMyAdmin), create a new database in your mysql server. Name the database Movies. In the database, create new table (name it Movies) with this structure:
CREATE TABLE IF NOT EXISTS `Movies` (
`ID` int(11) NOT NULL AUTO_INCREMENT
, `Title` varchar(255) NOT NULL
, `ImdbUrl` varchar(255) DEFAULT NULL
, `Picture` blob
, PRIMARY KEY (`ID`)
, KEY `Title` (`Title`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

Let's use our demo project with MS Access for the start of mySQL demo project. Open the MS Access demo project, then:
  • rename the project into StoreImageDemo_MySQL_Zeos
  • rename the main form unit into frmStoreImageDemo_MySql_Zeos
  • while keeping the component names, replace ADO components with respected counterparts in ZEOS, i.e.
  • replace TADOConnection with TZConnection (keep the cnnMain name)
  • replace TADOTable with TZTable (keep tblMovies name)
  • replace TADOCommand with TZQuery (keep qCmd name)
Then you will get the layout of the main form into something like this:


For the new cnnMain:
  • Set Protocol property into mysql-5
  • Set Host property into your mySQL server host address (e.g. localhost, 127.0.0.1).
  • Set User and Password properties with proper credentials to login to your mySql server.
  • If your mySql server use custom port, update Port property with correct port number, otherwise leave it as is.
  • Set Connected property to True.
Wire tblMovies's and qCmd's Connection properties to cnnMain. Set dsMovies' Dataset to tblMovies.

Set Movies to tblMovies' TableName property. Build the persistent fields for tblMovies by double clicking it and then Add all fields in the dialog that pops up.

Give the following SQL command to qCmd's CommandText.
UPDATE Movies SET Picture=:Picture WHERE ID=:ID
Check that qCmd has two parameters, Picture and ID, by opening its Params property in object inspector.

Writing Image With Table-kind of Dataset

Note that we don't have to change anything here. Code that works with TADOTable also works with TZTable. This is caused by both still use the same framework. So there is nothing we need to change here. See the explanation for MS Access.

Writing Image with SQL command
Here also everything is nearly the same with when we are working with MS Access database. The only differences are that TZQuery uses the name of Params for its property that containing parameters, while TADOCommand uses the name of Parameters. And TZQuery uses the name ExecSQL for executing data manipulation sql command instead of Execute in TADOCommand.

So basically we ended with the same code for btnAddPictSQL onclick event handler, we only need to change "Parameters" into "Params", and "Execute" into "ExecSQL". Like this:

procedure TForm1.btnAddPictSQLClick(Sender: TObject);
var
vFileStream: TStream;
begin
// do we have record to store the picture to? Don't continue if not
if tblMovies.IsEmpty then Exit;
// do the user select a picture file? Dont continue if not
if not OpenPictureDialog1.Execute then Exit;
// load the picture file into a stream
vFileStream := TFileStream.Create(OpenPictureDialog1.FileName, fmOpenRead or fmShareDenyNone);
try
// give ID of the current movie record to the :ID parameter of qCmd
qCmd.[B]Params[/B].ParamByName('ID').Value := tblMoviesID.Value;
// Assign the content of FS stream to the :Picture parameter
qCmd.[B]Params[/B].ParamByName('Picture').LoadFromStream(vFileStream, ftBlob);
// execute the sql
qCmd.[B]ExecSQL[/B];

// refresh the table so it shows the new picture
RefreshTable;
finally
vFileStream.Free;
end;
end;

Reading Image from Dataset
Here it's also the same. Since at this level both (zeos and ado) still using the same framework. Therefore we can keep the code that we previously use to read image from MS Access.

Isn't it cool? We hardly write new codes when we are migrating from MS Access to mySQL.

NOTE:
I am having problem uploading attachment, so I will update this post later with source code and design layout image.

EDIT:
One recent request reminded me that I had not uploaded the source code and design layout. I just uploaded them. Enjoy!

Attached Files


Edited by LuthfiHakim, 06 January 2013 - 06:11 AM.

  • 1

#5 Ezi

Ezi

    CC Lurker

  • Just Joined
  • Pip
  • 1 posts

Posted 24 April 2012 - 10:03 AM

Thanks Mr. LuthfiHakim. very useful info!
  • 0

#6 Luthfi

Luthfi

    CC Leader

  • Expert Member
  • PipPipPipPipPipPipPip
  • 1,320 posts
  • Programming Language:PHP, Delphi/Object Pascal, Pascal, Transact-SQL
  • Learning:C, Java, PHP

Posted 24 April 2012 - 06:05 PM

Thank you Ezi, and welcome to CodeCall!
  • 0





Also tagged with one or more of these keywords: store image, mysql

Powered by binpress