[re-open]Sqlite3 which component should I use to use DataSet attached to TDBEDIT

[laguna]

Sqlite3, which component should I use to use DataSet attached to TDBEdit, with SQL commands (insert, Delete, Refresh, and Update)?

Thanks
Vincenzo

[Handoko]

You can use SQLdb:
TSQLConnector > TSQLQuery > TDataSource > TDBEdit

Read more about SQLdb:
http://wiki.lazarus.freepascal.org/SQLdb_Package#Using_the_SQLdb_Package

[kapibara]

First you put a TSQLite3Connection and a TSQLTransaction on the Form or DataModule.

Then from the sqlDB tab, get a TSQLQuery, set its Database property and fill out the SELECT statement in the SQL property. There are such properties for refresh, update, delete too, but they are often not necessary to fill out because if the dataset is simple. But if for example Refresh doesn't work, then fill it out.

After that you get a TDataSource component from the Data Access tab and set its DataSet property to the SQLQuery mentioned above.

Finally you link the TDBEdit to the DataSource and open the query to see data.

Note that the TSQLite3Connection component needs the FULL path to the sqlite datafile or it will create an empty one. (In the lazarus working directory I think).

[laguna]

Thanks, 

I test TSQLite3Connection with  TSQLTransaction  connect TSqlite3Dataset  and TBEdit (RUN OK)

but not save permanent record.

Restart application, record-set is blank-nil , not save permanent.

Thanks
Vincenzo

[kapibara]

Without calling Commit, changes are not saved.

Post -> ApplyUpdates -> Transaction.Commit

The query has an option to keep the dataset open on calls to commit. Plus you can set AutoApplyUpdates and AutoCommit. Then a call to Post is enough to write the data to the database.

Otherwise the AfterPost event of the SQLQuery is a good place to commit the data from.

[laguna]

I did just that, but I do not understand why it does not work.
I attach a small zip, with the sample design including the DataBase.
The DataBase already contains a record inserted with SQLiteStudio.

There is no sqlite3.dll library in the zip

Tell me where I am wrong?

Thank you all

[kapibara]

I tried your program, you don't call query.ApplyUpdates.

1) Post only sends the data to a buffer in the TSQLQuery.
2) ApplyUpdates sends the data to the database but doesn't write it to disk.
3) Commit tells the DB to write received data to disk.

This is by design, to be able to regret and for use with transactions, I think.

You can either toggle AutoApplyUpdates in the options of the query. When you Post now, the DBEdits will go blank and the data is saved. Thats because of AutoCommit in options, Commit closes the Transaction and then the dataset closes. You could toggle KeepOpenOnCommit to avoid closing. Also remove your call to CommitRetaining because the data is already commited by AutoCommit.

Or you can disable AutoApply and AutoCommit and instead call:

Code: Pascal  [Select][+][-]

1. procedure TForm1.sqlArtAfterPost(DataSet: TDataSet);
2. begin
3.   sqlArt.ApplyUpdates;
4.   SQLTransaction1.Commit;
5. end;

[laguna]

OK  Run.

Thanks All
Vincenzo

[laguna]

Hi, sorry if I reopen the post,
I think it can be useful to both me and others.

With the Sqlite3Dataset1 component I did this to work with 2 databases.

How do I convert this feature to TSQLite3Connection?

 

Code: Pascal  [Select][+][-]

1.  with Sqlite3Dataset1 do
2.   begin
3.     FileName:='Anagrafica.db';
4.     TableName:='Dati';
5.     PrimaryKey:='idx';
6.     ExecSQL('ATTACH "Vendite.db" AS "vendite";');
7.     SQL:='select   * from Dati  join  vendite.Merce on vendite.Merce.fx_AnagDati=dati.idx';
8.     ShowMessage(SQL);
9.     Clipboard.AsText:=SQL;
10.     Open;
11.   end;  

Thanks
Vincenzo

[kapibara]

You want a JOIN query to get a resultset from two different database files? Look at the attached example.

We had a discussion three years ago about attaching sqlite databases:
http://forum.lazarus.freepascal.org/index.php/topic,24751.0.html

I added a new query to the project in the thread that does a JOIN and shows the result in a DBGrid.

Notice that the SELECT statement below will fail. You must remove the dotted name from 'main.cities' and use only 'cities'. That will refer to main.cities, the main database table.

Code: Pascal  [Select][+][-]

1. SELECT city_name FROM main.cities
2. UNION
3. SELECT city_name norway.cities
4.  

EDIT: If forgot that if you turn off ParseSQL in the query, it is possible to use .main as dotted identifier above. http://forum.lazarus.freepascal.org/index.php/topic,17796.0.html

Maybe attaching could be done some other way too, by pragma directive in the Param property of Sqlite3Connection.

[laguna]

Thanks ok run.