|
BDP Ado.Net Blobs - Felix John COLIBRI.
|
- abstract : reading and writing Blob fields using the Borland Data Provider
and Turbo Delphi
- key words : Blob - BDP - Ado Net
- software used : Windows XP, Turbo Delphi for Net
- hardware used : Pentium 1.400Mhz, 256 M memory, 140 G hard disc
- scope : Delphi 2005, 2006, Turbo Delphi
- level : Delphi / Interbase developer
- plan :
1 - Using Blob data with the BDP
We will explain here how to insert and retrieve Blob fields into a Table using
the the ADO.Net BDP data access components
2 - Reading and Writing Blob data
The Blob data is treated as ARRAY OF BYTE (binary blob) or ARRAY OF CHAR
(memo Blob) by Dot Net. So we have to read or write those kind of arrays from
or to the current row.
2.1 - Reading a Blob field
For reading
- we first open a BdpDataReader:
my_bdp_command:= BdpCommand.Create('SELECT * FROM project',
BdpConnection1);
my_bdp_datareader:=
my_bdp_command.ExecuteReader(CommandBehavior.CloseConnection);
|
- we read a row, and, if we do not know the field index of the Blob, test the
field BdpDataType:
my_bdp_datareader.Read();
with my_bdp_datareader do
for my_column_index:= 0 to FieldCount- 1 do
if (GetDataType(my_column_index)= BdpType.Blob)
and (GetDataSubType(my_column_index)= BdpType.stMemo)
then ...
|
- and once the test succeeds, we get the size of the Blob:
my_blob_size:= my_bdp_datareader.GetChars(my_column_index, 0, Nil, 0, 0);
|
- and transfer all the data:
my_read_index:= 0;
my_bdp_datareader.GetChars(my_column_index, my_read_index,
my_array_of_char, 0, my_blob_size);
SetLength(my_display_string, my_blob_size);
for my_display_index:= 0 to my_blob_size- 1 do
begin
my_character:= my_array_of_char[my_display_index];
my_display_string[1+ my_display_index]:= my_character;
end;
|
2.2 - Writing a Blob Field
To write data into a Blob field, we must use a parametrized query. Here is the
code:
- first we build and fill an ARRAY OF CHAR array (assuming the text is in
some String):
SetLength(my_array_of_char, Length(my_text));
for my_index:= 1 to Length(my_text) do
my_array_of_char[my_index- 1]:= my_text[my_index];
|
- we use a BdpCommand with a parametrized query. Assuming the Blob is in the
PROJ_DESC field, we have:
my_BdpConnection.Open;
my_bdp_command:= BdpCommand.Create(
'INSERT INTO PROJECT (PROJ_ID, PROJ_DESC)'
+ ' VALUES (''DNUKE'', ? )',
my_BdpConnection);
|
- we initialize the BdpParameter corresponding to the Blob:
my_bdp_parameter:= my_bdp_command.Parameters.Add('my_parameter',
BdpType.Blob, 8);
my_bdp_parameter.BdpSubType:= BdpType.stMemo;
my_bdp_parameter.Direction:= ParameterDirection.Input;
my_bdp_parameter.Value:= my_array_of_char;
|
- and we send the command to the Sql Engine:
my_bdp_command.ExecuteNonQuery();
my_BdpConnection.Close
|
3 - The Turbo Delphi source code
3.1 - Creating the connection
We will use the standard EMPLOYEE.GDB database, wich is included with each
Interbase version. Since we will modify the database, we will use a copy of
the sample database:
|
find the EMPLOYEE.GDB database. It should be in
c:\Program Files\Borland\Interbase\examples\database\
and copy it to a folder in the vicinity of the Delphi project:
..\_data\interbase\employee_7\
|
In order to easily get the connection string, we will use the Data Explorer,
which is located in the third tab of the upper right panel:
To add the new connection entry:
|
|
start Turbo Delphi
|
|
|
select "Interbase" entry in the the Data Explorer
|
|
|
right click on "Interbase" and select "Add new Connection"
|
|
the Data Explorer opens the new connection editor:
|
|
|
enter the name of the connection, for instance "employee_7_copy_connection"
and click "Ok"
|
|
|
the new connection is added to the Interbase connections
|
|
|
to set the connection parameter, right click on the new entry and select
"Modify Connection"
|
|
|
the Connection Editor is displayed
|
|
|
enter the path, the user (SYSDBA) and the password (masterkey)
|
|
|
Click "Test" to check the parameters
|
|
|
the connection is successful
|
|
|
click "ok" and "ok"
|
3.2 - The PROJECT Table blobs
The Employee.PROJECT Table contains an proj_desc ASCII Blob. We can see this
by clicking on the PROJECT Table:
Alternately, we can use our sql_script_extraction tool (see Google) to extract
the EMPLOYEE.GDB Sql Script, which contains the PROJECT creation request:
/* domains */
CREATE DOMAIN projno AS CHAR(5)
CHECK (VALUE = UPPER (VALUE));
CREATE DOMAIN empno AS SMALLINT;
CREATE DOMAIN prodtype AS VARCHAR(12)
DEFAULT 'software'
CHECK (VALUE IN ('software', 'hardware', 'other', 'N/A'))
NOT NULL;
/* Table: PROJECT, Owner: SYSDBA */
CREATE TABLE PROJECT
(
proj_id projno NOT NULL,
proj_name VARCHAR(20) NOT NULL,
proj_desc BLOB SUB_TYPE TEXT SEGMENT SIZE 800,
team_leader empno,
product prodtype,
UNIQUE (proj_name),
PRIMARY KEY (proj_id)
);
|
Our goal is to retrieve the text from the Blob, and to write some new rows with
their textual blobs.
3.3 - Reading Blob Data
Blob data will be retrieved using this code:
|
|
start Turbo Delphi | File | New | Windows Forms Application
|
|
|
select the employee_7_copy_connection in the DataExplorer and drag it on
the FORM
|
|
|
a new BdpConnection1 is added in the non-visual area:
|
|
|
in the Tools Palette, select a Button, drop it on the Form and create
its Click event. Write the code which will display in a TextBox the
content of all the blobs:
const k_select_project= 'SELECT * FROM project';
procedure TWinForm.read_blob__Click(sender: System.Object;
e: System.EventArgs);
var l_c_bdp_transaction: BdpTransaction;
l_c_bpd_command: BdpCommand;
l_c_bdp_datareader: BdpDataReader;
l_row_index: Integer;
l_display: String;
l_column_index: Integer;
begin
Try
BdpConnection1.Open;
l_c_bdp_transaction:= BdpConnection1.BeginTransaction;
l_c_bpd_command:= BdpCommand.Create(k_select_project,
BdpConnection1, l_c_bdp_transaction);
l_c_bdp_datareader:=
l_c_bpd_command.ExecuteReader(CommandBehavior.CloseConnection);
l_row_index:= 0;
while l_c_bdp_datareader.Read() do
begin
l_display:= l_row_index.ToString+ ' | ';
for l_column_index:= 0 to l_c_bdp_datareader.FieldCount- 1 do
begin
if f_is_bdp_memo_blob(l_c_bdp_datareader, l_column_index)
then l_display:= l_display+ '|'
+ f_bdp_memo_string(l_c_bdp_datareader, l_column_index);
end; // for l_column_index
display(l_display);
Inc(l_row_index);
end; // while l_c_p_datareader
// -- close the transaction
l_c_bdp_transaction.Commit();
BdpConnection1.Close();
except
on e: Exception do
display('*** ERROR '+ e.Message);
end; // try except
end; // read_blob__Click
|
and add the auxiliary function which tests the field type:
function f_is_bdp_memo_blob(p_c_bdp_datareader: BdpDataReader;
p_field_index: Integer): Boolean;
begin
with p_c_bdp_datareader do
Result:= (GetDataType(p_field_index)= BdpType.Blob)
and (GetDataSubType(p_field_index)= BdpType.stMemo);
end; // f_is_bdp_memo_blob
|
as well as the function which retrieves the Blob character array:
function f_bdp_memo_string(p_c_bdp_datareader: BdpDataReader;
p_field_index: Integer): String;
var l_blob_size: Integer;
l_read_index: Integer;
l_read_result: Integer;
l_display_index: Integer;
l_char_array: Array of Char;
l_char: Char;
begin
l_blob_size:= p_c_bdp_datareader.GetChars(p_field_index, 0, Nil, 0, 0);
if l_blob_size > 0
then begin
SetLength(l_char_array, l_blob_size);
l_read_index:= 0;
// -- the result is always 0
l_read_result:= p_c_bdp_datareader.GetChars(p_field_index,
l_read_index, l_char_array, 0, l_blob_size);
SetLength(Result, l_blob_size);
for l_display_index:= 0 to l_blob_size- 1 do
begin
l_char:= l_char_array[l_display_index];
Result[1+ l_display_index]:= l_char;
end;
end
else Result:= '';
end; // f_bdp_memo_string
|
|
|
|
compile, execute and click "read_blob_"
|
|
|
here is a snapshot of the application:
|
3.4 - Writing Blob Data
To write a new row with some Blob text;
|
|
drop a TextBox on the Form for the new Blob Field
|
|
|
drop a Button on the Form and create its Click event. Write the code
which will add some text to a new row:
const k_insert_into_project=
'INSERT INTO PROJECT (PROJ_ID, PROJ_NAME, PROJ_DESC, PRODUCT) '
+ ' VALUES (''DNUKE'', ''Dot Net Nuke'', ?, ''software'')';
procedure TWinForm.write_blob__Click(sender: System.Object;
e: System.EventArgs);
var l_text: String;
l_char_array: Array of Char;
l_length, l_index: Integer;
l_c_bdp_command: BdpCommand;
l_c_bdp_transaction: BdpTransaction;
l_c_bdp_parameter: BdpParameter;
begin
l_text:= input_textbox_.Text;
l_length:= Length(l_text);
SetLength(l_char_array, l_length);
for l_index:= 1 to l_length do
l_char_array[l_index- 1]:= l_text[l_index];
BdpConnection1.Open;
l_c_bdp_transaction:= BdpConnection1.BeginTransaction;
l_c_bdp_command:= BdpCommand.Create(k_insert_into_project, BdpConnection1);
l_c_bdp_parameter:=
l_c_bdp_command.Parameters.Add('my_parameter', BdpType.Blob, 8);
// l_c_bdp_parameter.SubType:= stMemo;
l_c_bdp_parameter.Direction:= ParameterDirection.Input;
l_c_bdp_parameter.Value:= l_char_array;
l_c_bdp_command.ExecuteNonQuery();
l_c_bdp_transaction.Commit;
BdpConnection1.Close();
end; // write_blob__Click
|
|
|
|
compile, execute and click "read_blob_"
|
|
|
here is a snapshot of the application:
|
Please note that
- we had to include values for the NOT NULL fields (this is why we first
looked at the Table definition)
- how did we know how to setup the parametrized query parameters ? Well, first
we looked at the code generated by Turbo Delphi in some other project
where we used the Parameter Editor. The folded
"$REGION 'Windows Form Designer generated code"
contains the Form initialization (the Turbo For Net equivalent of the
.DFM), where a parameter was initialized like this:
Self.BdpCommand1.Parameters.Add(Borland.Data.Common.BdpParameter.Create
('EMP_NO', Borland.Data.Common.BdpType.Int16,
Borland.Data.Common.BdpType.Unknown,
2, System.Data.ParameterDirection.Input,
False, (Byte(2)), (Byte(0)),
2, '', System.Data.DataRowVersion.Current, '8'));
|
We then looked at the Help, which indicated:
and since many of those parameters are filled by default, we used the cut
down version above: only the type, size and value are used (but for Output
parameters, we would have to add the ParameterDirection.Output Direction,
as well as the MaxPrecision).
3.5 - Displaying Memo Blobs in a TextBox
We now will display the Table in a DataGrid and the Blob of the current row
in a TextBox
The only problem stems from the DataGrid / TextBox synchronization. In our
case
- we used a DataBinding and a CurrencyManager
- the OnPositionChanged event of the CurrencyManager is used to read and
display the text
First the usual code to display the Table in a DataGrid:
|
|
in the "Borland Data Provider" tab of the Tools Palette, select a
BdpDataAdapter (the read arrow below), and drop it on the Form
|
|
|
the new BdpDataProvider1 is displayed in the non visual area (the yellow
arrow)
|
|
|
select the "configure DataAdapter" link at the bottom of the Object
Inspector (the green arrow)
|
|
|
the Configuration Editor is displayed
|
|
|
select "PROJECT", click "Generate SQL", and eventually preview the data
|
|
|
to create the DataSet corresponding to this request, select the "DataSet"
tab, select "New Dataset" and click "Ok"
|
|
|
the DataSet1 component is added in the non visual area
|
|
|
to fill the DataSet (at design time), select the BdpDataAdapter1, and, in
the Object Inspector, toggle Active to True
|
Now for the DataGrid:
|
|
from the "Data Controls" tab of the Tools Palette, select the DataGrid
and drop it on the Form
|
|
|
in the Object Inspector, select the DataSource property, and set the
value to DataTable1
|
|
|
the content of the PROJECT table is displayed (at run time)
|
Now the binding:
|
|
drop a TextBox on the Form
|
|
|
drop Button on the Form and type the code which will create the binding:
var g_c_currency_manager: CurrencyManager= Nil;
procedure TWinForm.bind__Click(sender: System.Object;
e: System.EventArgs);
begin
g_c_currency_manager:= BindingContext[DataGrid1.DataSource]
as CurrencyManager;
Include(g_c_currency_manager.PositionChanged, table_position_changed);
end; // bind__Click
|
|
|
|
declare the table_position_changed in the CLASS:
type
TWinForm = class(System.Windows.Forms.Form)
// -- ...
public
constructor Create;
procedure table_position_changed(sender: System.Object;
e: EventArgs);
end; // TWinForm
|
|
|
|
then write the body of this event, using the technique shown earlier for
retrieving Blob data:
procedure TWinForm.table_position_changed(sender: System.Object;
e: EventArgs);
var l_c_bdp_datareader: BdpDataReader;
l_c_bdp_transaction: BdpTransaction;
l_row_index: Integer;
l_display: String;
l_column_index: Integer;
l_selected_row_index: Integer;
l_has_read: Boolean;
begin
Try
if BdpConnection1.State= ConnectionState.Open
then BdpConnection1.Close;
// -- get the position
l_selected_row_index:= BindingContext[DataSet1.Tables[0]].Position;
l_c_bdp_datareader:= f_c_datareader_2(BdpConnection1, k_select_project,
l_c_bdp_transaction);
// -- find this row in the Table
l_row_index:= 0;
while l_row_index<= l_selected_row_index do
begin
l_has_read:= l_c_bdp_datareader.Read();
Inc(l_row_index);
end; // while l_row_index
// -- now get the blob field value
l_display:= '';
for l_column_index:= 0 to l_c_bdp_datareader.FieldCount- 1 do
if f_is_bdp_memo_blob(l_c_bdp_datareader, l_column_index)
then l_display:= l_display+ '|'+ f_bdp_memo_string(l_c_bdp_datareader, l_column_index);
TextBox2.Text:= l_display;
l_c_bdp_transaction.Commit;
BdpConnection1.Close;
Except
on e: Exception do
display('*** ERR '+ e.Message);
end; // try except
end; // table_position_changed
|
|
|
|
compile, execute and click "bind_". The change the row position in the
DataGrid : the Blob text is displayed in the TextBox
|
|
|
here is a snapshot of the application:
between the
|
4 - Download the Sources
Here are the source code files:
- bdp_ado_net_blob.zip: the complete Turbo Delphi project, which reads
and writes Blob from and to the EMPLOYEE.PROJECT table (20 K)
The .ZIP file(s) contain:
- the main program (.DPR, .DOF, .RES), the main form (.PAS, .DFM), and any
other auxiliary form
- any .TXT for parameters, samples, test data
- all units (.PAS) for units
Those .ZIP
- are self-contained: you will not need any other product (unless expressly
mentioned).
- for Delphi 6 projects, can be used from any folder (the pathes are RELATIVE)
- will not modify your PC in any way beyond the path where you placed the .ZIP
(no registry changes, no path creation etc).
To use the .ZIP:
- create or select any folder of your choice
- unzip the downloaded file
- using Delphi, compile and execute
To remove the .ZIP simply delete the folder.
The Pascal code uses the Alsacian notation, which prefixes identifier by
program area: K_onstant, T_ype, G_lobal, L_ocal, P_arametre,
F_unction, C_lasse etc. This notation is presented in the Alsacian Notation
paper.
As usual:
- please tell us at fcolibri@felix-colibri.com if you found some errors, mistakes, bugs or had
some problem downloading the file. Resulting corrections will be helpful
for other readers
- we welcome any comment, criticism, enhancement, other sources or reference
suggestion. Just send an e-mail to fcolibri@felix-colibri.com.
- or more simply, enter your (anonymous or with your e-mail if you want an
answer) comments below and clic the "send" button
- and if you liked this article, talk about this site to your fellow
developpers, add a link to your links page ou mention our articles in
your newsgroup posts when relevant. That's the way we operate: the more
traffic and Google references we get, the more articles we will write.
5 - References
Here are a couple of references about Blobs
- Integrating into the Borland Data Provider (BDP) - Ramesh THEIVENDRAN ,
Borcon 2004 : the description of the BDP INTERFACEs
- Borland Data Provider 2.5
Features : by Ramesh THEIVENDRAN : a description of the new BDP
features
- Delphi for .Net Developper's Guide - Xavier PACHECO. 2004 - ISBN
0.672.32443-1
Although written for Delphi 8, this still remains the best book about .Net
programming with Delphi. Many design time features are not present (because
they were not yet developed !). But the explanation are clear, and the code,
like all previous PACHECO books, pertinent
- Mastering Delphi 2005 - Marco CANTU
A more general Win32 / .Net book
- NET 2.0 For Delphi Programmers - Jon SHEMITZ
Nice introduction about .Net, but mainly about basic coding (nothing about
Asp.Net or Ado.Net, not to mention the BDP).
6 - The author
Felix John COLIBRI works at the Pascal
Institute. He programs in Pascal since 1979, and is mainly active in the area
of custom software
development and training, and is a frequent speaker at Borland
Developer Conferences. His web site features
tutorials, technical papers about programming with full downloadable source
code, and the description and calendar of forthcoming Delphi,
Interbase, Asp.Net, Ado.Net and OOP / UML training sessions.
|
