ZQuery and Master-Detail problem (SQLite)
lazarus / 16-Databases

----------------------------------------------------------------------
lukaszb · 2014-06-10 22:02

Hi all.

I'm trying to use Zeos components in Lazarus to manage the data in
SQLite DB.

And here's my problem.
I have 2 tables in master-detail relation, viewed through master-
detail form.
Two groups of buttons are used to add, edit and delete records in both
tables respectively. Addition and editing is performed through
separate form with data aware controls (in transaction of course).

Addition, deletion and editing of master records works fine.

In order to add a detail record I set the foreign key value using the
following construct:

  if FCardMode=cmAddition then
  begin
    ZQEmployees.Append;
    ZQEmployees.FieldByName('iddptmt').AsInteger:=FIDDptmt;
  end;

FIDDptmt is the primary key value of the current master record (passed
by a parameter to the detail form).

Then I execute:
  ZQEmployees.Post
in the bbOKClick event handler when the user hits the OK button on the
form.

Looks safe to me but doesn't work.
Exception I get is:
SQL Error: constraint failed, library routine called out of sequence.

Thanks in advance for any hints.

lukasz

----------------------------------------------------------------------
cdbc · 2014-06-11 06:32

Hi
Me thinks you cannot write the primary key... You can use it.
It is internally maintained, by sqlite.
Sorry, been a few years since I read the Sqlite docs...

----------------------------------------------------------------------
mangakissa · 2014-06-11 07:24

That's strange. In MySQL and Firebird I can write my primary key. It's
just a unique field with data in it.
In Firebird I call the generator and use the number to fill my primary
key field.

----------------------------------------------------------------------
lukaszb · 2014-06-11 07:29

Quote from: cdbc on June 11, 2014, 08:32:57 amMe thinks you cannot
write the primary key... You can use it.
And this is what I do. I take the value of the master table primary
key and set it as a value of the detail table foreign key.
Or I try to set it, because it breaks.

Lukasz

----------------------------------------------------------------------
mangakissa · 2014-06-11 08:49

Okay. I Did a different approach. I've created a database with these
tables:
Code: [Select]CREATE TABLE [cities] (
[id] INTEGER  NOT NULL PRIMARY KEY AUTOINCREMENT,
[cdcountry] INTEGER  NULL,
[cdcity] INTEGER  NULL,
[city] VARCHAR(30)  NULL)

CREATE TABLE [master] (
[id] INTEGER  NOT NULL PRIMARY KEY AUTOINCREMENT,
[cdcountry] INTEGER  UNIQUE NULL,
[country] VARCHAR(30)  NULL)

CREATE UNIQUE INDEX [master_detail] ON [cities](
[cdcountry]  DESC,
[cdcity]  DESC)Put two DBGrid on a form and created a master/detail
relation on the datasets. My masterkey is cdcountry. Inserted a record
in the master table. Then inserted two records in detail. Before
saving the detail record I have a BefoePost event which fill my
related cdcountry with the value of the master.

All works fine. As you see I don't use the primary key as a reference
to the detail table.

In other way, I can't see what's wrong with your approach, but
master/detail works with ZEOS without problems,

I hope this helps.

----------------------------------------------------------------------
lukaszb · 2014-06-11 09:41

Do you use the:
q.Open;
q.Append;
q.FieldByName('Fieldname').AsInteger:=FValue;
q.Post;
combination ?

lukasz

----------------------------------------------------------------------
mangakissa · 2014-06-11 09:54

No.
Code: [Select]procedure TDataModule1.ZDetailBeforePost(DataSet:
TDataSet);
begin
  if dataset.state in [dsInsert, dsEdit] then
  begin
    dataset.FieldByName('cdcountry').AsInteger :=
ZMaster.FieldByName('cdcountry').AsInteger;
  end;
end;                                    
But there's no differents. The only thing your problem can be is
Fvalue is not assigned

----------------------------------------------------------------------
lukaszb · 2014-06-12 16:57

I think I got it. And the solution is quite unexpected.

Inspired by mangakissa I ported my toy-application to the Firebird.
Created tables, changed connection parameters and got ... an exception
at the same place where it was for SQLite. But in Firebird the message
corresponded to the real cause of the error. And it was... that two of
the table fields with the "NOT NULL" clause where not set in spite of
the fact that they were connected to data aware TDBCalendar controls.

So my preliminary diagnosis is that TDBCalendar controls are not so
data aware as claimed or there is some communication problem between
these controls and TZQuery object.

When I set the values of these fields in the code the problem
disappeared.

Lukasz

----------------------------------------------------------------------
lukaszb · 2014-06-29 20:35

Is somebody able to confirm (or refute) my claim from the message
above ? I have described the problem in the bugtracker in more detail:
http://bugs.freepascal.org/view.php?id=26335

Lukasz