How do I delete a row in a database with FireDAC?

[JohnLM 14]

Win7, D11.2, FMX app, using TStringGrid and FireDAC 

 

I am using a very basic SQLite database with one table and learning how to do various things with it before I start the real project.

 

I would like to be able to delete a row using two different methods.
A) at current row position - as in when user selects a place in the tstringgrid
B) at the last row (or record) position in the list in the tstringgrid

 

I don't want to delete the Database, nor the Table.  I just want to delete the last row, or the current row that I am in, depending on which "delete" method I choose at the time, and have my TStringGrid update to show it.

 

On my form, I have two delete buttons: [DelCurRow] and [DelLastRow]

For [DelCurRow], I can get the selected row with sg1CellClick(const Column: TColumn; const Row: Integer);

For [DelLastRow], I can use Qry.Last or Table.Last, I think.   

 

I've tried various scenarios with these components, and many other things to no avail: 

 

form -> Connection -> Table -> [delete]
form -> connection -> Table -> Query -> [delete]
form -> connection -> Query -> [delete]

 

The final outcomes are mostly Error messages. 

 

How do I delete the current or last row?

[programmerdelphi2k 237]

look, StringList class in general (grid, stringlist, etc...) is not aware data... it just know itself data, like: strings, pointers, properties, etc...

then, when you move between record (if using Livebinding, for example), it's it who receive the info about record changes, then the stringGrid is updated.

said this, you can:

1) use the  BindSourceDB to get your info about your records, because it is like a Datasource regular to datasets... many properties and methods with same functions

BindSourceDb.Dataset.Field....

 

2) to delete any record, you can use your Query component with

qr.Sql.Text:="delete from tablex where fieldX = nValue";

qr.EXECUTE;

 

it's ready!

 

Edited December 4, 2022 by programmerdelphi2k

[JohnLM 14]

Hi.  I saw that method used in other areas of my search.   However, I can't us it because I don't know what the value is because I am using a barcode scanner in real-time, and the number scanned gets inserted into the db. 

 

However, as I re-read this, and ponder it in my head, I think I might be able to accomplish it somehow, by getting the data from say, the first column in the query, and feed that to the code you posted.  hmm. If I go to .Last, I just have to figure out how to pull the field's content into a variable and then feed that somehow into the qry.SQL.Text;="" part.  

Edited December 4, 2022 by JohnLM

[programmerdelphi2k 237]

implementation

{$R *.dfm}

procedure TForm1.Button1Click(Sender: TObject);
var
  LSQLText: string;
begin
  LSQLText := Format('delete from MyTable where MyField=%s', { }
    [                                                        { }
    StringGrid1.Cells[StringGrid1.Col, StringGrid1.Row]      { }
    ]);
  //
  ShowMessage(LSQLText);
end;

end.

  LSQLText := Format('delete from MyTable where MyField=%s',                    { }
    [                                                                           { }
    StringGrid1.Cells[StringGrid1.VisibleColCount, StringGrid1.VisibleRowCount] { }
    ]);
  //
  ShowMessage(LSQLText);

  StringGrid1.Cols[1].Text := 'MyQRcode';
  //
  LSQLText := Format('delete from MyTable where %s=%s',                         { }
    [StringGrid1.Cols[1].Text.Trim,                                             { }
    StringGrid1.Cells[StringGrid1.VisibleColCount, StringGrid1.VisibleRowCount] { }
    ]);
  //
  ShowMessage(LSQLText);

 

Edited December 4, 2022 by programmerdelphi2k

[Stano 143]

I don't know what your table looks like, but:

1. every table "must" have a PK primary key
2. You save the PK in the component that displays the data
3. you only access table entries via PK

I consider it the alpha and omega when working with DB

[Lars Fosdal 1792]

A common way to deal with unique identities in a database is to use an auto-incremented identity field.  

• SQLite has an autoincrement feature
• PostgreSQL calls it a Serial
• MSSQL names the feature Identity, although they also have a synonymous auto-increment feature
• MySQL and MariaDB call it an Auto_Increment

Basically, it is an automatically incremented integer value which uniquely identifies the row, without having any other relation to the data in the row.

You use these identity fields as primary keys for efficiently joining tables, and as unique id's for doing updates and deletions.

 

SELECT
    *
FROM
    t_parent
    LEFT JOIN t_child ON t_child.parentid = t_parent.id

 

Pitfall - it is not advisable to use these keys as part of a URL - since it allows for fishing for content by variating the id.

If you need to expose the row identity as part of a URL, it is better - although more costly with regards to space - to have a second field in the form of a unique GUID.

 

[skyzoframe[hun] 4]

4 hours ago, Lars Fosdal said:

Basically, it is an automatically incremented integer value which uniquely identifies the row

How do you solve the limit of integer range? What are you think about not "basically way"?

[Attila Kovacs 629]

4 minutes ago, skyzoframe[hun] said:

How do you solve the limit of integer range?

not buying every hdd's on the earth? 😉

[programmerdelphi2k 237]

2 hours ago, skyzoframe[hun] said:

How do you solve the limit of integer range? What are you think about not "basically way"?

if can use "int64"...  starting in min-Int64 to max-Int64!

• -9.223.372.036.854.775.808 +1, +1, +1...   to +9.223.372.036.854.775.807 = 18.... 
• at least, your brain can rest a little longer! ( I think ... but if you're ZKeeZOfranic.. . 

Edited December 4, 2022 by programmerdelphi2k

[Lars Fosdal 1792]

17 hours ago, skyzoframe[hun] said:

What are you think about not "basically way"?

I am not sure if I understand your question?

 

[skyzoframe[hun] 4]

 

21 hours ago, Lars Fosdal said:

Basically, it is an automatically incremented integer value which uniquely identifies the row, without having any other relation to the data in the row.

You use these identity fields as primary keys for efficiently joining tables, and as unique id's for doing updates and deletions.

There are any other ways to handle unique identities?

[Stano 143]

What exactly do you mean by that?

[Lars Fosdal 1792]

2 hours ago, skyzoframe[hun] said:

 

There are any other ways to handle unique identities?

The point of identity fields is to be able to do table joins without relying on unique keys in your data.  

 

You can still have other unique keys - f.x. for books, it could be the ISBN, for warehouse articles it could be the EAN code, for shipping containers it could be SSCC.  All of these are supposed to be unique - but they are strings, and hence far more expensive with regards to space and computation.  And - at times, those unique IDs might not be available at the time of insertion (example of unique field that allows for multiple rows with field = null).

 

As I mentioned - the point of the identity field is to be able to create relations without involving the actual data. The value will not change for the lifetime of the row(s), even if you later change all the actual data for that record - including other columns that must be unique.  

[Fr0sT.Brutal 900]

3 hours ago, Lars Fosdal said:

You can still have other unique keys - f.x. for books, it could be the ISBN, for warehouse articles it could be the EAN code, for shipping containers it could be SSCC

And the experience advises not to fall into temptation to use these values as primary keys. In real world everything could change. Literally everything.

[JohnLM 14]

Progress update.. Success! and Resolved!

 

Thanks to the help here, I now have a working delete method that will do what I need. Below is what I decided to implement, crude but works. 

 

var
  Form1: TForm1;
  LSQLText: string; // SQL string for [deleting last row]
  LastRowValue: string; // from StringGrid

.
.

procedure TForm1.btnDelLastRowClick(Sender: TObject); // this routine works!  12/4/sun late pm
  // delete last row, but is actually [current row selected in stringgrid]
begin
  LastRowValue := sg1.Cells[sg1.Col,sg1.Row];
  beep;
  LSQLText := Format('delete from tblBarcodes where IDNo=%s', [LastRowValue]);

  // do the row deletion here..
  qry.SQL.Clear;
  //('delete from tblBarcodes where IDNO=2'); // <-- i.e.,
  qry.SQL.Add(LSQLText+';');
  qry.SQL.Add('select * from tblBarcodes;');
  qry.ExecSQL;

  // show me the updated list
  conn.Connected:=true;
  qry.Active:=true;
end;

 

However, I now have another problem, but I will post a new topic for that.  Thank you all for your responses.

Edited December 5, 2022 by JohnLM

[programmerdelphi2k 237]

or just:  FDQuery1.ExecSQL('....', [],[]);

qry.SQL.EXECSQL( LSQLText + '; select * from tablex');

[Serge_G 87]

7 hours ago, JohnLM said:

Progress update.. Success! and Resolved!

I don't think this is a good one solution.

 

You say it's a FMX project and I assume :

  - you use livebindings

  - con is your fdconnection

You can do your delete via the FDConnection like this
 

procedure TForm1.btnDelLastRowClick(Sender: TObject);
var lastrowid : integer;
begin
Query1.last;
lastrowid:=Query1.Fieldbyname('IDNO').asInteger;
if lastrowid.isnull then exit; // if table is empty 
con.ExecSQL('delete from tblBarcodes where IDNo=:s',[lastRowValue]);
query1.Open(); // or Query1.open('Select * from tblbarcodes');
end;

now this code should show some flickering and have disadvantage to unselect the current row selected 😞 because of Query1.open

1- flickering can be avoided by BeginUpdate .. EndUpdate block

2- there are many ways to memorize current position in the dataset
       - using key, memorize in another variable the current idno and using

var currentid : integer :=Query1.FieldbyName('IDNO').asInteger;
// code for deleting
Query1.Open();
Query1.Locate('IDNO',currentid,[]);   

     not recommended if not mono user app

     - using recordno 

     - using FDQuery.GetBookmark and FDQuery.GotoBookmark