How to Insert/Edit/Delete data to Database in Delphi

Photo by Kevin Ku on Unsplash

After making a successful connection to your database you may want to inser/edit/delete data from your delphi application.

We are not going to go deep on MySQL, but I’ll post the table I’ll be using throughout this tutorial.

CREATE TABLE event_book(
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(80),
    content VARCHAR(1000),
    author VARCHAR(255),
    cpf_author VARCHAR(20),
    created_at TIMESTAMP DEFAULT NOW(),
);

Step 1. Add the components

Add the following components to your project.

1. TFDQuery (name: qryEventBook)
2. TEdits (To insert the data)
3. TMaskEdits (To insert the data formatted like birthdate/created_at)
4. TButton (name: btnSave)

INSERT DATA

Step 2. Add the code to your Button OnClick Event

procedure TfrmInsertEvent.btnSaveClick(Sender: TObject);
var
  Title, Author, CPFAuthor, Description: string;
  PersonID, LotID: Integer;
begin
  // -------------------------------------------------------
  //                     Raise Exceptions
  // -------------------------------------------------------
  // Fiels which can't be empty (Required Fields)
  if (edtTitle.Text = '') or (edtAuthor.Text = '') or
    (mmoDescription.Text = '') then
    raise Exception.Create(SWarningFillInAllObligedFields);
  // Fields too big
  if Length(edtTitle.Text) > 80 then
    raise Exception.Create(SErrorTitleTooBig);
  if Length(mmoDescription.Text) > 1000 then
    raise Exception.Create(SErrorDescriptionTooBig);
  // -------------------------------------------------------
  //                Get Fields and insert data 
  // -------------------------------------------------------
  Title := edtTitle.Text;
  Author := edtAuthor.Text;
  CPFAuthor := medtAuthorCPF.Text;
  Description := mmoDescription.Text;
  with qryEventBook do
    begin
      Connection := frmDMCondominium.FDConnection;
      Active := False;
      SQL.Clear;
      SQL.Text := 'INSERT INTO event_book ' +
      '(title, content, author, cpf_author) ' +
      'VALUES ' +
      '(:title, :content, :author, :cpf_author)';
      Params[0].AsString := Title;
      Params[1].AsString := Description;
      Params[2].AsString := Author;
      Params[3].AsString := CPFAuthor;
      ExecSQL;
      ShowMessage(SEventInsertedSuccessfully);
    end;
  Close;
end;

First, I raise some exceptions in case the Fields are empty or too big.

Second, I get the data from the fields and put them into string variables.

Third, it comes the interesting part.

with qryEventBook do
    begin
      Connection := frmDMCondominium.FDConnection;
      Active := False;
      SQL.Clear;
      SQL.Text := 'INSERT INTO event_book ' +
      '(title, content, author, cpf_author) ' +
      'VALUES ' +
      '(:title, :content, :author, :cpf_author)';
    ...

I set the connection, clean and desactivate the FDQuery before doing anything.

Then, I add my SQL Text to insert data. This is a simple Insert MySQL statement.

INSERT INTO event_book (title, content, author, cpf_author) VALUES (..);

The only difference is that instead of directly insert the values. I put the parameters.

Parameters are names preceded by a colon you put into SQL.Text ánd you can add its values after the SQL.Text by specifying its position.

To assign the values to my parameters I need the position, datatype and the value. The following example show how to add the value to the title parameter, which is the first one.

Params[0].AsString := Title;

Following the sequence in the SQL.Text I add the remaining values.

In the very end you must execute ExecSQL; method to run your SQL Text. If everything goes fine your code keeps running and the ShowMessage will display a successfully message to the user, if it goes wrong delphi will raise an exception for you.

ADITIONAL

Another thing you may want to do is to assign a null value to your database. In this case you need to first tell the datatype of your parameter. Second, check the value of your variable, in case it is empty (nothing was assigned) clear the parameter (this is going to send null to the database). Otherwise you insert the value.

Params[0].DataType := ftString;
if Title = '' then
  Params[0].Clear // Null
else
  Params[0].AsString := Title;

EDIT DATA

To edit data you will follow the same process, except you will change your SQL text.

SQL.Text := 'UPDATE event_book SET ' +
  'title = :title, content = :content, author = :author, ' +
    'cpf_author = :cpf_author ' +
  'WHERE id = ' + FEventID.ToString;

Parameters will be treated in the same way you did in INSERT DATA.

You need to specify wich record you want to edit, to do that you should tell MySQL which id you are edditing by using

WHERE id = event_id

In my case the id of the event is saved as a private variable in my form as FEventID.

DELETE DATA

To delete data you will need to change your text.

with qryDelete do
begin
  Connection := frmDMCondominium.FDConnection;
  Active := False;
  SQL.Clear;
  SQL.Text := 'DELETE FROM livro_registro ' +
    'WHERE id = ' + qryEventBook.FieldByName(C_ID).AsString;
  ExecSQL;
end;

I need to specify an id as I did in EDIT DATA, but in this case I am taking this id from the FDQuery that populated my DBGrid. If you don’t know how to use FDQuery and DBGrid you can check out this article: Connect FDQuery to DBGrid programatically — Delphi.

ADITIONAL

In some time you may end up with the necessity to change your database column or table names. If this happen and you have a big project, you will need to follow every single SQL.Text and change its values.

To avoid that you can add the SQL Column and Table names as Constants into your project. For the insert statement it would be like that.

SQL.Add('INSERT INTO ' + T_EVENT_BOOK);
SQL.Add('(' + C_TITLE + ', ');
SQL.Add(C_CONTENT + ', ');
SQL.Add(C_AUTHOR + ', ');
SQL.Add(C_CPF_AUTHOR + ') ');
SQL.Add('VALUES ');
SQL.Add('(:' + C_TITLE + ', ');
SQL.Add(':' + C_CONTENT + ', ');
SQL.Add(':' + C_AUTHOR + ', ');
SQL.Add(':' + C_CPF_AUTHOR + ')');

And in your uConstants.pas file

unit uConstants;
interface
const
  T_EVENT_BOOK = 'event_book';
  C_TITLE = 'title';
  C_CONTENT = 'content';
  C_AUTHOR = 'author';
  C_CPF_AUTHOR = 'cpf_author';
implementation
end.

You can check more articles about delphi and android on my profile!

Happy Coding! by SoonClass :)