TSqlite3Dataset [SOLVED]

[LumirP]

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]

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]

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]

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]

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

[LumirP]

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]

Attach simple project example with SQL scripts to create tables.

[LuizAmérico]

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

[garlar27]

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]

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]

Hi,

The 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]

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]

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]

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]

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