Friday, 12 April 2013

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:
, `Title` varchar(255) NOT NULL
, `ImdbUrl` varchar(255) DEFAULT NULL
, `Picture` blob
, KEY `Title` (`Title`)

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,
  • 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 tblMoviesTableName 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);
vFileStream: TStream;
// 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);
// 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

// refresh the table so it shows the new picture

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 :
Oleh : Luthfi Hakim

No comments:

Post a Comment

Install Fortesreport community Delphi 7 dan RX Berlin

Download  Pertama2 kita harus punya file installernya terlebih dahulu, download