Handling Images in MySQL

We will be using ZEOS library to provide access to our MySQL server. If you don't have it, get ithere (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 totblMovies.

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 withTZTable. 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 nameExecSQL 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.


Download : http://www.facebook.com/download/242147332595934/Demo_MySql_Zeos.zip
Oleh : Luthfi Hakim