menu
  Home  ==>  papers  ==>  db  ==>  bdp  ==>  bdp_ado_net_blobs   

BDP Ado.Net Blobs - Felix John COLIBRI.


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_indexmy_read_index,
        my_array_of_char, 0, my_blob_size);

    SetLength(my_display_stringmy_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_charLength(my_text));
    for my_index:= 1 to Length(my_textdo
      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:

image

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:

image

   enter the name of the connection, for instance "employee_7_copy_connection" and click "Ok"

   the new connection is added to the Interbase connections

image

   to set the connection parameter, right click on the new entry and select "Modify Connection"

   the Connection Editor is displayed

image

   enter the path, the user (SYSDBA) and the password (masterkey)

   Click "Test" to check the parameters

   the connection is successful

image

   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:

image

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:

image

   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(senderSystem.Object;
    eSystem.EventArgs);
  var l_c_bdp_transactionBdpTransaction;
      l_c_bpd_commandBdpCommand;
      l_c_bdp_datareaderBdpDataReader;
      l_row_indexInteger;
      l_displayString;
      l_column_indexInteger;
  begin
    Try
      BdpConnection1.Open;
      l_c_bdp_transaction:= BdpConnection1.BeginTransaction;

      l_c_bpd_command:= BdpCommand.Create(k_select_project,
          BdpConnection1l_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_datareaderl_column_index)
            then l_display:= l_display'|'
                + f_bdp_memo_string(l_c_bdp_datareaderl_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 eException 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_datareaderBdpDataReader;
    p_field_indexInteger): 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_datareaderBdpDataReader;
    p_field_indexInteger): String;
  var l_blob_sizeInteger;
      l_read_indexInteger;
      l_read_resultInteger;
      l_display_indexInteger;
      l_char_arrayArray of Char;
      l_charChar;
  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_arrayl_blob_size);
          l_read_index:= 0;
          // -- the result is always 0
          l_read_result:= p_c_bdp_datareader.GetChars(p_field_index,
              l_read_indexl_char_array, 0, l_blob_size);

          SetLength(Resultl_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:

image



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(senderSystem.Object;
    eSystem.EventArgs);
  var l_textString;
      l_char_arrayArray of Char;
      l_lengthl_indexInteger;
      l_c_bdp_commandBdpCommand;
      l_c_bdp_transactionBdpTransaction;
      l_c_bdp_parameterBdpParameter;
  begin
    l_text:= input_textbox_.Text;
    l_length:= Length(l_text);
    SetLength(l_char_arrayl_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_projectBdpConnection1);
    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:

image

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:

    image

    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)

image

   select the "configure DataAdapter" link at the bottom of the Object Inspector (the green arrow)
   the Configuration Editor is displayed

image

   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_managerCurrencyManagerNil;

procedure TWinForm.bind__Click(senderSystem.Object;
    eSystem.EventArgs);
  begin
    g_c_currency_manager:= BindingContext[DataGrid1.DataSource]
        as  CurrencyManager;
    Include(g_c_currency_manager.PositionChangedtable_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(senderSystem.Object;
          eEventArgs);
    end// TWinForm

   then write the body of this event, using the technique shown earlier for retrieving Blob data:

procedure TWinForm.table_position_changed(senderSystem.Object;
    eEventArgs);
  var l_c_bdp_datareaderBdpDataReader;
      l_c_bdp_transactionBdpTransaction;
      l_row_indexInteger;
      l_displayString;
      l_column_indexInteger;
      l_selected_row_indexInteger;
      l_has_readBoolean;
  begin
    Try
      if BdpConnection1.StateConnectionState.Open
        then BdpConnection1.Close;

      // -- get the position
      l_selected_row_index:= BindingContext[DataSet1.Tables[0]].Position;

      l_c_bdp_datareader:= f_c_datareader_2(BdpConnection1k_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_datareaderl_column_index)
          then l_display:= l_display'|'f_bdp_memo_string(l_c_bdp_datareaderl_column_index);

      TextBox2.Text:= l_display;

      l_c_bdp_transaction.Commit;
      BdpConnection1.Close;
    Except
      on eException 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:

image
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
    Name :
    E-mail :
    Comments * :
     

  • 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.
Created: nov-04. Last updated: dec-15 - 99 articles, 220 .ZIP sources, 1068 figures
Copyright © Felix J. Colibri   http://www.felix-colibri.com 2004 - 2015. All rigths reserved
Back:    Home  Papers  Training  Delphi developments  Links  Download
the Pascal Institute

Felix J COLIBRI

+ Home
  + articles_with_sources
    + database
      + interbase
      – firebird_trans_simulator
      + sql_server
      + bdp
        – bdp_ado_net_blobs
      – db_refactoring
      – sql_parser
      – sql_to_html
      – sniffing_interbase
      – eco_tutorial
      – dbx4_programming
      – blackfishsql
      – rave_pdf_intraweb
      – rave_reports_tutorial
      – rave_reports_video
      – embarcadero_er/studio
      + firedac
      – bde_unidac_migration
    + web_internet_sockets
    + oop_components
    + uml_design_patterns
    + debug_and_test
    + graphic
    + controls
    + colibri_utilities
    + colibri_helpers
    + delphi
    + firemonkey
    + compilers
  + delphi_training
  + delphi_developments
  + sweet_home
  – download_zip_sources
  + links
Contacts
Site Map
– search :

RSS feed  
Blog