SQLite3 Relational bases
lazarus / 16-Databases

----------------------------------------------------------------------
Pe3s · 2021-12-16 20:21

Hello, where can I find an example of a SQLite relational database
written in lazarus?

----------------------------------------------------------------------
af0815 · 2021-12-17 07:21

Is something unclear with https://wiki.freepascal.org/SQLite ,
https://wiki.freepascal.org/TSqlite3_Master_Detail_Example and some
more site ? found with searchin on google with lazarus wiki sqlite

and in the wiki is a tutorial
https://wiki.freepascal.org/SQLdb_Tutorial1

----------------------------------------------------------------------
Pe3s · 2021-12-18 10:30

Do I need to duplicate components when creating a relational database?

----------------------------------------------------------------------
JanRoza · 2021-12-18 16:29

What components are you talking about?
Normally for databases you need one connection, one transaction and
one or more queries per table in that database.

----------------------------------------------------------------------
Pe3s · 2021-12-19 12:06

How do I build a SELECT query to display the entire contents of both
tables?

----------------------------------------------------------------------
JanRoza · 2021-12-19 14:06

Something like SELECT * FROM table2 WHERE field1 = table1.field1
Not sure what you want exactly but I think the SQL JOIN is what you
are looking for.
I advise you to visit W3schools website
https://www.w3schools.com/sql/, they have tutorials for almost
anything you can do with SQL.

----------------------------------------------------------------------
Pe3s · 2021-12-19 15:09

I want to combine these two queries
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
SQLQuery1.SQL.Text := 'SELECT * FROM table';SQLQuery2.SQL.Text :=
'SELECT * FROM table_1 WHERE table_1.Col_id = :id';

----------------------------------------------------------------------
JanRoza · 2021-12-19 15:56

Like I already mentioned, check out the SQL JOIN statement.

----------------------------------------------------------------------
balazsszekely · 2021-12-19 16:04

@Pe3s
QuoteLike I already mentioned, check out the SQL JOIN statement.
Take a look at the union command, column count from table and table_1
must be the same. A better approach would be something like this:
Code: MySQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i  
SQLQuery1.SQL.Text := 'select tb.id, tb.firstname, tb.lastname,
tb.salary from table tb' +                        'union ' +         
             'select tb_1.id, tb_1.firstname, tb_1.lastname,
tb_1.salary from table_1 tb_1 where tb_1.id = :id';

----------------------------------------------------------------------
JanRoza · 2021-12-19 17:49

Thanks GetMem, even better.

----------------------------------------------------------------------
Pe3s · 2021-12-21 09:44

Thank you

----------------------------------------------------------------------
Pe3s · 2021-12-21 09:48

I still have a question, how can we update the data in the table, e.g.
after deleting the record, we are alive select or can we refresh the
view differently?

----------------------------------------------------------------------
paweld · 2021-12-21 10:49

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
SQLQuery1.Refresh;

----------------------------------------------------------------------
Pe3s · 2021-12-21 15:53

Why is SQLQuery1.Refresh; causes an error Operation cannot be
performed on an inactive dataset.

----------------------------------------------------------------------
JanRoza · 2021-12-21 16:22

I think you will have to show some code of how you delete and then
refresh your table.
Now we can only guess.

----------------------------------------------------------------------
Sieben · 2021-12-21 17:22

At line #9 you're trying to refresh a query that doesn't have a result
set, thus the error (remember Open vs ExecSQL?). I guess you want to
refresh the query that actually holds your result set from the table
in question.

----------------------------------------------------------------------
Pe3s · 2021-12-21 17:30

here is the code
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i procedure
TForm1.Button3Click(Sender: TObject);    begin      if
MessageDlg('Pytanie', 'Czy usunac', mtWarning, mbYesNo, 0) = mrYes
then      begin        SQLQuery1.SQL.Text := 'DELETE FROM base WHERE
id=:id';        SQLQuery1.ParamByName('id').AsString :=
Form1.Caption;        SQLQuery1.ExecSQL;       
SQLTransaction1.Commit;        SQLQuery1.Refresh;      end;    end;

----------------------------------------------------------------------
af0815 · 2021-12-21 19:30

After a ExecSQL nothing is open, so a Refresh here is complete false.
On the other hand, there is nothing to Refresh after a Delete
statement.

You can use Refresh if you use the correct Sql, Update, delete and
insert SQL of a query. After Open the query, it is handling the
insert, update, delete and select statement by itself. This is one
point if you can use Refresh.

----------------------------------------------------------------------
JanRoza · 2021-12-21 20:58

After the Commit first make sure that SQLQuery1.SQL.Text contains your
select statement again and only then do the refresh.
Now you are refreshing a Delete statement, which is not what you want.

----------------------------------------------------------------------
Pe3s · 2021-12-28 10:15

I made the record updates
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
SQLQuery1.SQL.Text := 'UPDATE ......'; SQLQuery1.ExecSQL; 
SQLTransaction1.Commit; If I call the SELECT command, the record focus
is reset, how to correct the record so that the record remains
selected after the update

----------------------------------------------------------------------
JanRoza · 2021-12-28 13:27

Focus is reset because you use SQLTransaction1.Commit which closes the
database, try SQLTransaction1.CommitRetaining.
The database will then not be closed after committing the changes and
you can continue working.

----------------------------------------------------------------------
Pe3s · 2021-12-28 16:09

After using ComitRetaining
does not refresh the view in the table

----------------------------------------------------------------------
JanRoza · 2021-12-28 18:32

How do you view the table?
If you use database dependant elements like DbGrid, DbListbox, etc.
than the table view refreshes as soon as you update data in the table.
Not knowing what you are doing makes it hard to give a good answer.

----------------------------------------------------------------------
Pe3s · 2021-12-29 08:32

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
 SQLQuery1.SQL.Text := 'UPDATE database SET Name=:Name,
Product=:Product,  WHERE id=:id'; 
SQLQuery1.ParamByName('Name').AsString := LabeledEdit1.Text; 
SQLQuery1.ParamByName('Product').AsString := LabeledEdit2.Text; 
SQLQuery1.ParamByName('id').AsInteger :=
SQLQuery1.FieldByName('id').AsInteger;  SQLQuery1.ExecSQL; 
SQLTransaction1.CommitRetaining; SQLQuery1.SQL.Text := 'SELECT * FROM
database';  SQLQuery1.Open; 
The changes appear after restarting the program