- Very Basic Database Application in Delphi
- Most Basic Editing Database in Delphi
- Manipulating Database Record with Codes
- Master Detail Datasets
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).
- MS SQL Server
- MS Access
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:
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]=:IDCheck 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