TSqlite3Dataset [SOLVED]
lazarus / 16-Databases

----------------------------------------------------------------------
LumirP · 2011-05-18 05:27

Hello All,
Lazarus 0.9.31, FPC 2.4.3, Win7
Iam doing a simple application and I have a problem with saving
record. I use TSqlite3Dataset, Datasource,DBgrid. In DBGrid I want to
show for example: "Select Name, In, Out, In-Out as State from
Warehouse"  If this expression I insert in  TSqlite3Dataset. SQL
 property I am not able to save neither new record nor changes in
exist record. There is not any massage only the changes are not saved.
If the Sqlite3Dataset. SQL  property is empty or I use "Select * from
Warehouse "  Saving  is OK.
Please can you tel me what Iam doing wrong ?
Thanks for advices

----------------------------------------------------------------------
Nouba · 2011-05-18 08:59

Hi,

you should avoid special characters in all names in your database.
SQLite3 interprets In-Out as In minus Out. You can try to put double
quotes around your suspisious field name "In-Out".

hth Nouba

----------------------------------------------------------------------
LumirP · 2011-05-18 10:40

It is correct. "In-Out"  is  In minus Out. It works properly. The
problem is when I write down expression in Dataset.SQL   "Select Name,
In, Out, In-Out as State from Warehouse" or "Select In, Out from
Warehouse" In  this case any change is not saved. But if  Dataset.SQL 
is empty or there is "Select * from Warehouse "  saving work properly.

----------------------------------------------------------------------
Nouba · 2011-05-18 12:46

Hi,

If you use the SQL property, it is necessary to have at least the
primary key field in the selection statement for inserting, changing
or deleting rows in the dataset.

If you only provide a table name the procedure
TCustomSqliteDataset.InternalInitFieldDefs ensures that you get a
valid sql statement in the variable FEffectiveSQL 'Select * from ' +
FTableName + ';';, which is used for retrieving the field definitions.
Otherwise if the SQL property is set then this expression is used as
the FEffictiveSQL.

Nouba

----------------------------------------------------------------------
LuizAmérico · 2011-05-18 23:12

http://sqlite4fpc.yolasite.com/faq.php

----------------------------------------------------------------------
LumirP · 2011-05-19 05:14

Hi,
To LuizAmérico: If You think 

     * Set PrimaryKey property to the name of a Primary Key field
    * Add an AutoInc field (This is easier since the TSqlite*DataSet
automatically handles it as a Primary Key)

 then I can say that it is set. Thanks to this It works in case I
don't fill DataSet.SQL property.   But if I want to use DataSet.SQL
property Iam not able to save any changes.
For saving I use

procedure TForm1.Button1Click(Sender: TObject);
begin
  DSetSklad.Post;
  DSetSklad.ApplyUpdates;
  DSetSklad.Refresh;
  DSetSklad.Active:=false;
  DSetSklad.Active:=true;
end;    

After click on the Button All changes in DBGrid are disappeared.

To Nouba:
Iam not sure that I understand well. Please can you explain to me more
in detail. Iam beginner in programming.
LumirP

----------------------------------------------------------------------
sasa · 2011-05-19 06:45

Attach simple project example with SQL scripts to create tables.

----------------------------------------------------------------------
LuizAmérico · 2011-05-19 10:12

The primarykey field must be in the select
Select MyPrimaryKey, In, Out from Warehouse

----------------------------------------------------------------------
garlar27 · 2011-05-19 12:19

I've notice something:
   When I'm working with TSQLite3Dataset or TSqlQuery I've noticed
that I can't mix inserts or updates sql sentences with
TSQLite3Dataset.Append or TSQLite3Dataset.Edit because they will never
go down to the DB file, regardless any method I execute o property I
change in any of the components (TSQLite3Dataset, TTransaction,
TSQLite3Connection). I never knew why it happens.

   I've missed something? I did something wrong?

----------------------------------------------------------------------
LumirP · 2011-05-19 16:40

Hi,

To sasa:
I do not create tables by script. I did it by Firefox and its plugin
for SQLite3  administration or  right mouse button on TSqliteDataset
component.

To LuizAmérico:
Now I do not have my project with me. I will try it tomorrow morning
and then I let you know.
Thanks for a tip.

----------------------------------------------------------------------
LumirP · 2011-05-20 04:18

Hi,

Quote from: LuizAmérico on May 19, 2011, 12:12:49 pmThe primarykey
field must be in the select
Select MyPrimaryKey, In, Out from Warehouse

I have tried this and without succes. It is still the same.
I wanted to do like this because I want to use calculated field. It
appeared to me like easy way.

----------------------------------------------------------------------
sasa · 2011-05-20 06:33

First of all, you cannot use reserved words as field names (IN is
reserved word). It is highly suggested to avoid, but if you must use
square brackets.

----------------------------------------------------------------------
LumirP · 2011-05-20 07:42

Hi,
 It is clear for me. "IN, Out....." It was only exapmle. It was only
translation from my native language for better understanding . The
problem is somewhere else. But I don't know where....

----------------------------------------------------------------------
sasa · 2011-05-20 14:47

Did you tried examples on link Luiz suggested? Can you find any other
working Lazarus example of using SQLite by "native" components? Did
you tried Zeos 7.x?

----------------------------------------------------------------------
marcov · 2011-05-20 16:23

(I would try with Trunk. There are patches committed to sqlite nearly
weekly due to a very active contributor)

----------------------------------------------------------------------
LuizAmérico · 2011-05-21 11:57

Post a sample application showing the problem

----------------------------------------------------------------------
LumirP · 2011-05-21 22:25

Hi,

Here is a small example. It is adapted tutorial for showing my
problem. If You click on "Pure view" you can add and modify record a
then saving by "Save" button. In case that you cklick on "SQL view"
you can not add or modify any record.

to sasa: I have instaled Zeos 7.x but don't know work with it. I would
like to use native components in Lazarus if it is possible.

Quote from: marcov on May 20, 2011, 06:23:15 pm(I would try with
Trunk. There are patches committed to sqlite nearly weekly due to a
very active contributor)
I don't know it.

----------------------------------------------------------------------
LuizAmérico · 2011-05-22 00:24

The problem:
You where not saving when the program is finished

Solutions:

1) Call ApplyUpdates in the OnDestroy event

2) Set SaveOnClose to True
With this option you can remove all calls to ApplyUpdates

More tips:
- Don't need calling Active true/false everywhere. Only when changing
the Sql
- When manually appending/editing is necessary to call post (no need
to call applyupdates here if one of the two tips above are followed):

Append/Edit
[Set Field Values]
Post

----------------------------------------------------------------------
LuizAmérico · 2011-05-22 00:40

Here's the updated code (SaveOnClose = True)

See how simple is to use: no sql at all

procedure TForm1.FormCreate(Sender: TObject);
begin
  with ContactsDataset do
  begin
    FileName := 'data.db';
    if not TableExists then
    begin
      FieldDefs.Clear;
      FieldDefs.Add('Id', ftAutoInc);
      FieldDefs.Add('Name', ftString);
      FieldDefs.Add('Email', ftString);
      FieldDefs.Add('Input', ftInteger);
      FieldDefs.Add('Output', ftInteger);
      CreateTable;
    end;
    Open;
  end;
end;

procedure TForm1.AddButtonClick(Sender: TObject);
begin
  ContactsDataset.Append;
  ContactsDataset.FieldByName('Name').AsString := 'New Contact';
  ContactsDataset.FieldByName('Email').AsString := 'xxx@yyy.com';
  ContactsDataset.FieldByName('Input').AsInteger := 250;
  ContactsDataset.FieldByName('Output').AsInteger := 115;
  ContactsDataset.Post;
end;

procedure TForm1.Button1Click(Sender: TObject);
begin
  ContactsDataset.Active:=False;
  ContactsDataset.SQL:='Select Id, Name, Email, Input, Output,Input -
Output as State From Contacts';
  ContactsDataset.Active:=True;
end;

procedure TForm1.DeleteButtonClick(Sender: TObject);
begin
  if ContactsDataset.RecordCount > 0 then
    ContactsDataset.Delete;
end;

----------------------------------------------------------------------
LuizAmérico · 2011-05-22 00:53

Attached is an example that uses calculated fields. The State is
updated automatically, no need to use sql or to call ApplyUpdates. All
handled automatically.

----------------------------------------------------------------------
picstart · 2011-05-22 09:43

testsqlite.zip has dependencies on a newer version of lclbase than in
the stable 9.30 version. Even with the stable release lazarus 9.30
there are  serious duplications between lcl and lclbase0.9.3
concerning TAchart that prevent recompiling the IDE. Now
testsqlite.zip calls for lcl>=1.01 this is further again away from the
released version and its stability.

----------------------------------------------------------------------
LumirP · 2011-05-23 13:59

Hi,
I have tried It according to LuizAmérico's instructions and can say
that now Iam able to do what I need.  Thanks a lot LuizAmérico
LumirP