|
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, broken links 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 blog or 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. Starting with Pascal in 1979, he then became involved with Object Oriented Programming, Delphi, Sql, Tcp/Ip, Html, UML. Currently, he is mainly
active in the area of custom software development (new projects, maintenance, audits, BDE migration, Delphi
Xe_n migrations, refactoring), Delphi Consulting and Delph
training. His web site features tutorials, technical papers about programming with full downloadable source code, and the description and calendar of forthcoming Delphi, FireBird, Tcp/IP, Web Services, OOP / UML, Design Patterns, Unit Testing training sessions.
|
