TSQlite3Dataset Question - SOLVED
lazarus / 16-Databases

----------------------------------------------------------------------
Kyjan · 2010-09-19 14:08

Hi everyone!

I'm trying to use the tsqlite3dataset for managing my database, but i
found a strange thing.

Code: [Select]with dataset do
   begin
         Open;
         Append;
         Fieldvalues['name']:='Mr X.';
         FieldValues['address']:='City of NoWhere';
         Post;ApplyUpdates;
         Close;
   end;

So, i wrote the code you can see above. I get no errors, when the
program runs, but the data will not get into the database. I'd like to
use this method, becouse it seems more confortable for me than the
following:

Code: [Select]
with dataset do
   begin
      Open;
      Append;
      SQL:='insert into table (name, address) values ("Mr X.", "City
of NoWhere');
      ExecSQL;
      Close;
  end;


If I use the 2nd method, everything goes fine, the data get into the
database. Why I can't use FieldValues[] method with sqlite3? Any idea
is welcome.

Regards,
Kyjan

----------------------------------------------------------------------
LuizAmérico · 2010-09-19 14:59

It's necessary to set the PrimaryKey property or use an autoinc field
so applyupdates can work

----------------------------------------------------------------------
Kyjan · 2010-09-19 15:07

Thanks, Luiz, but I have an index field with autoinc property. Do I
have to set up it in Tsqlite3dataset in object manager?

----------------------------------------------------------------------
LuizAmérico · 2010-09-19 15:11

How do you created the table?

Using ExecSql('Create Table ....')

or
 FieldDefs.Add('Id', ftAutoInc);
 CreateTable?

Send me the layout of the table (captured by any sqlite manager)

----------------------------------------------------------------------
Kyjan · 2010-09-19 15:15

Hi Luiz,

Code: [Select]CREATE TABLE "proba"
("index" INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL ,
"szoveg" TEXT,
"masik" TEXT)

It was created with a firefox extension, called SQlite manager.

----------------------------------------------------------------------
eny · 2010-09-19 16:16

Try to use the type-safe version of field access:

FieldByName('name').AsString := 'Mr. X';

----------------------------------------------------------------------
LuizAmérico · 2010-09-19 16:33

In this case TSqlite3Dataset will not automatically handle 'index' as
an autoincrement field.

To use applyupdates is necessary to set PrimaryKey property to 'index'

And if you want to handle as autoincrement set also AutoIncrementKey
to True

----------------------------------------------------------------------
LuizAmérico · 2010-09-19 16:38

Also take a look at what sqlite declared field type maps to TDataset
field types at http://sqlite4fpc.yolasite.com/faq.php (Q5)

----------------------------------------------------------------------
Kyjan · 2010-09-19 17:27

Thank you, guys, you're great!
It works fine!