sqlite
lazarus / 16-Databases

----------------------------------------------------------------------
quest · 2005-05-15 16:29

Hi, I try use lazarus and sqlite, but this code
procedure TForm1.Button2Click(Sender: TObject);
begin
     popis1.Caption:='obsah Edit1: '+Edit1.Text;
     SqliteDataset1:=TSqliteDataset.Create(nil);
     with SqliteDataset1 do
     begin
          FileName:='pokus.db';
          TableName:='customer';
          SQL:='INSERT INTO customer(cust_firstname, cust_lastname,
cust_no) values
('''+Edit1.Text+''','''+Edit2.Text+''','''+Edit3.Text+''')';
          Open;
          Close;
          Destroy;
     end;
end;
insert values twice, why?
I use lazarus 0.9.7 20050509 and sqliteds 1.13
thanks

----------------------------------------------------------------------
matthijs · 2005-05-17 06:33

If the TSqliteDataSet is developed like all other datasets, you should
not use 'Open' but 'ExecSQL' to let the record be inserted. The 'Open'
method is only to be used when reocrds are returned. So try that and
check if the records gets inserted twice.

----------------------------------------------------------------------
Anonymous · 2005-05-17 20:14

Thank you, now it is work fine

----------------------------------------------------------------------
Anonymous · 2005-05-25 16:34

I rewrote code and as I wrote, it is work fine, but only on win.
uses
{$ifdef Linux}
  cmem,
{$endif}
  Classes, SysUtils, LResources, Forms, Controls, Graphics, Dialogs,
Buttons,
  StdCtrls, crt, SqliteDS, sqldb, ComCtrls, DB;
....
procedure TForm1.Button2Click(Sender: TObject);
begin
     popis1.Caption:='INSERT INTO customer';
     SqliteDataset1:=TSqliteDataset.Create(nil);
     with SqliteDataset1 do
     begin
          FileName:='pokus.db';
          TableName:='customer';
          ExecSQL('INSERT INTO
customer(cust_firstname,cust_lastname,cust_no)
values'+'('''+Edit1.Text+''','''+Edit2.Text+''','''+Edit3.Text+''')');
          Destroy;
     end;
     comboupdate;
end;
on linux nothing insert.

----------------------------------------------------------------------
LuizAmérico · 2005-06-30 15:57

After calling ExecSql try to evaluate the SqliteReturnString property
of TSqliteDataset. THis will give a hint what's going on

----------------------------------------------------------------------
Anonymous · 2005-07-08 12:37

SqliteReturnString return "SQLITE_OK", it's strange for me, I don't
know, what I do with this.

----------------------------------------------------------------------
LuizAmérico · 2005-07-14 21:25

Some thoughts:

1) What version of fpc/sqliteds are you using?, both are equal in
windows/Linux? I'd suggest using the sqliteds.pas found in fixes2_0
svn branch (do not use the one of the trunk branch)

2) Check the return value of ExecSql it should report the number of
affected rows in the db.

3) Numbers doesn't need to be enclosed by quotes in sql statements

4) Are you sure that the database are not changed or only the GUI
doesnt catch the change? To see this type in the shell sqlite
[filename] and then select * from tablename;

5) What version of sqlite do you have installed in both OS? sqliteds
is know to work on 2.8.15 or above

6) It's not needed to insert manually records, TSqliteDataset can do
automatically for you:
   TableName:= 'xxx';
   FileName:= 'xxxx';
   PrimaryKey:= 'NameOfAfieldThatIsAPrimaryKey'; //To inserts any
fieldname works, but not for deletes or updates
   Append;
   FieldByName('AFieldName').AsString:='xxxx';
   FieldByName('AnotherFieldName').AsInteger:=1;
   Post;
   ApplyUpdates;
   See Lazarus wiki (Databases) for more info
  
7) To know if the problem is with the TSqliteDataset do the following:
  add sqlite to uses;
  add var DBHandle:Pointer
   DBHandle := sqlite_open(PChar(AFilename),0,nil)
   sqlite_exec(DBHandle,PChar(ASql),nil,nil,nil);
   sqlite_close(DBHandle);
   if the problem is with TSqliteDataset thi should work

----------------------------------------------------------------------
Anonymous · 2005-07-22 20:26

Very thanx for your answer, my problem was with no right set primary
key in db