SQLdb and query with multiple tables
lazarus / 16-Databases

----------------------------------------------------------------------
mangakissa · 2014-01-26 21:05

I have created this query:
Code: [Select]SELECT t.id, t.adoptieid,t.aniwaybedrag,t.donatiebedrag,
t.teruggeboekt,
       c.lidnummer, c.achternaam, c.tussenvoegsels, c.voornaam
FROM transacties t
LEFT OUTER JOIN contacten c on c.lidnummer = t.lidnummer
WHERE t.id = :ID
Put those fields as Persistent fields with these PoviderFlags-
settings:

Code: [Select]id                  pfinkKey
adoptieid           pfHidden
aniwaybedrag        pfInUpdate
donatiebedrag       pfInUpdate
teruggeboekt        pfInUpdate
lidnummer           pfHidden
c.achternaam        pfHidden
c.tussenvoegsels    pfHidden
c.voornaam          pfHidden

                    
All the fields has the property keyfield set to id.

Why is my dataset readonly. Even if I set TSQLTable.readonly to false,
the dataset is still readonly.

----------------------------------------------------------------------
goodname · 2014-01-26 22:15

The query parser is not clever enough to figure out how to write when
more than one table is in the query. You have to set the UpdateSQL,
DeleteSQL, and InsertSQL queries to let the parser know how to do the
writes.

----------------------------------------------------------------------
mangakissa · 2014-01-28 08:55

Thanks, goodname. That's the trick. Never worked with
InsertSQL/UpdateSQL/DeleteSQL, but there's always a first time.

----------------------------------------------------------------------
marcov · 2014-01-28 09:48

It's the same with ZEOS btw.

----------------------------------------------------------------------
mangakissa · 2014-01-28 10:44

It could be. But MyDAC in Delphi has his own intelligent driver to do
all those things. That's why I never used it before.

----------------------------------------------------------------------
exdatis · 2014-01-28 11:11

Yes, but select-insert-update-delete(separately) method solves many
other problems and gives greater control.

----------------------------------------------------------------------
BigChimp · 2014-01-28 11:25

@Mangakissa: have a look through the database articles on the wiki and
the FPC help on sqldb; there's a lot of info there. Contributions
welcome as well. Thanks.

(I'm saying this because it may be easier to get a grip on the
details/idiosyncracies of sqldb versus other db technologies you are
familiar with)