Recent

Author Topic: SQLite, Lazarus, How to start - I am beginner  (Read 28989 times)

George_Prague

  • Newbie
  • Posts: 4
SQLite, Lazarus, How to start - I am beginner
« on: May 04, 2012, 12:41:40 pm »
Hi Programmers,

I would like to work with SQLite3 in Lazarus, but I am a beginner. May I ask you, would somebody patient help me step by step to process it, please? I must do something total wrong :-/

I made a test.db in SQLite Administrator and tried to connect in Lazarus through the TSQLite3Connection, TSQLTransaction and TSQLQuery, but if I select TSQLQuery -> Activ - there is still some warning message (Transaction is not set).

May I ask you – do I understand well?
1)   The SQLite database could be only a file created e.g. by SQLite Administrator, I can place it in the same folder like the project, ok?
2)   The sqlite3.dll library could be placed also in the same folder like the project, ok?
3)   TSQLite3Connection – Database Name = C:/pascal/test1/database1.db
3a) How about the slash - / or \ - C:/pascal... or C:\pascal...?
4)   SQLiteLibraryName:=’C:/pascal/test1/sqlite3.dll’;
4a) or can I use relative path './sqlite3.dll';
5)   TSQLQuery – SQL = SELECT * FROM table1

Any idea whats wrong, please?

Or would somebody have some easy project with SQLite (e.g. easy adressbook…), which could send me by email as I could understand it just from the code and setting?

Thank you all very much for any help…

George

fredycc

  • Sr. Member
  • ****
  • Posts: 265
Re: SQLite, Lazarus, How to start - I am beginner
« Reply #1 on: May 04, 2012, 03:58:32 pm »
Here an little example:

Code: [Select]
  SQLiteLibraryName:='sqlite3.7.11.dll';
  SQLite3Connection1.DatabaseName := 'Enviar.dbe';
  SQLite3Connection1.Connected := True;

  SQLQuery1.Active := True;

See the project example:https://sites.google.com/site/fredydevelopments/SQLite_Laz.zip?attredirects=0&d=1

Regards

George_Prague

  • Newbie
  • Posts: 4
Re: SQLite, Lazarus, How to start - I am beginner
« Reply #2 on: May 07, 2012, 09:52:35 am »
Thanks a lot, it works perfect! :-)

Please, would you have a small example with insert data from ‘edit form’ as well?

I tried ‘insert into db_table values…’ but it doesn’t work, so I tried ‘sqlquery.add(insert into…)’ but the data was not saved into the database.

Would you have an very easy project code with edit form and inserting values into the SQLite database, please?

Thank you very much for your help,

Jirka

Arbee

  • Full Member
  • ***
  • Posts: 223
Re: SQLite, Lazarus, How to start - I am beginner
« Reply #3 on: May 07, 2012, 03:00:25 pm »
Can you show a little code of your insert statement?
An "insert into" should work, I've done it many times.  there are some things to consider though ... like doing a "commit" on the transaction.

1.0/2.6.0  XP SP3 & OS X 10.6.8

George_Prague

  • Newbie
  • Posts: 4
Re: SQLite, Lazarus, How to start - I am beginner
« Reply #4 on: May 08, 2012, 11:13:38 pm »
Hi,

The project is here:
download.jirik.org/test_address.zip

I have two questions about the code:

A) When I click on the Insert button, it looks like, it works, but the data was not saved into the database. When I open the database later, the data are not there.

B) Could you help me to rewrite the code as I can use the TEdit (Phone and Name) and as it works correct, please? I mean, when I click on the Insert button I would like to save the Phone and Name included into the TEdits to be saved into the database.

Thank you very much, I have not any idea what is wrong and how to process it...

Geroge

fredycc

  • Sr. Member
  • ****
  • Posts: 265

Arbee

  • Full Member
  • ***
  • Posts: 223
Re: SQLite, Lazarus, How to start - I am beginner
« Reply #6 on: May 09, 2012, 06:45:55 pm »
First of all,  you do indeed need to add a commit, to "freeze" the changes in the database, like this
Code: [Select]
procedure TForm1.Button2Click(Sender: TObject);
begin
  //SQLiteLibraryName := 'sqlite3.dll';
  SQLite3Connection1.DatabaseName := 'db_address.db';
  SQLite3Connection1.Connected := True;
  SQLQuery2.SQL.Clear;
  SQLQuery2.SQL.Add('insert into tb_address (phone, name) values ("123456789", "George")');
  SQLQuery2.ExecSQL;
  SQLTransAction1.Commit;    // <===== commit changes
  SQLQuery2.Close;
  //SQLQuery2.Active := True;
end;
1.0/2.6.0  XP SP3 & OS X 10.6.8

Arbee

  • Full Member
  • ***
  • Posts: 223
Re: SQLite, Lazarus, How to start - I am beginner
« Reply #7 on: May 09, 2012, 06:52:02 pm »
Getting data from the edit fields is by using paramaterized SQL statements:

Code: [Select]
procedure TForm1.Button2Click(Sender: TObject);
begin
  //SQLiteLibraryName := 'sqlite3.dll';
  SQLite3Connection1.DatabaseName := 'db_address.db';
  SQLite3Connection1.Connected := True;
  SQLQuery2.SQL.Clear;
  SQLQuery2.SQL.Add('insert into tb_address (phone, name) values (:phone, :name)');
  SQLQuery2.Params.paramByName('phone').AsString := Edit1.Text;
  SQLQuery2.Params.paramByName('name').AsString := Edit2.Text;
  SQLQuery2.ExecSQL;
  SQLTransAction1.Commit;
  SQLQuery2.Close;
  //SQLQuery2.Active := True;
end;
1.0/2.6.0  XP SP3 & OS X 10.6.8

George_Prague

  • Newbie
  • Posts: 4
Re: SQLite, Lazarus, How to start - I am beginner
« Reply #8 on: May 10, 2012, 03:01:15 pm »
Perfect, it works  :)
Thank you all very much!
George