(sqlite) dbnavigator and transaction
lazarus / 16-Databases

----------------------------------------------------------------------
marcov · 2015-04-12 13:23

Another question from a beginner with sqldb. (did Zeos under Delphi
though)

I expanded the little program so I have a convenient little table
editor (joins are for next session), and I'm stuck with one little
problem:

If, after editing, I press the "v" sign of the dbnavigator, nothing
gets stored, I assume because the transaction is not committed.

What is the most elegant way of doing this? Maybe removing
transactions completely?

----------------------------------------------------------------------
bylaardt · 2015-04-12 14:41

if the target of your project is for dummies users, transactions can
be a problem.
use autocommit and be happy.

otherwise, transactions are necessary if you must use rollback to
maintain the integrity of the data. is this your case?

----------------------------------------------------------------------
marcov · 2015-04-12 14:45

Not in this application, and that is what I would do in ZEOS. But I
thought sqldb always required transaction

e.g. the first few lines of tcustomsqlstatement.prepare are

  if not assigned(Transaction) then
    DatabaseError(SErrTransactionnSet);

----------------------------------------------------------------------
bylaardt · 2015-04-12 14:55

and transaction.action are seted to caCommit?

----------------------------------------------------------------------
marcov · 2015-04-12 15:40

Now it is. (default seems to be carollback)

I brought back the transaction, set it cacommit, but now I get a "must
call applyupdates" when I press the (v) mark.

I assume that there is no simple designer only application in examples
should have warned me.

----------------------------------------------------------------------
bylaardt · 2015-04-12 16:04

"no simple designer", i agree! 

i have a word (in portuguese) but haven't translate to english without
distortions:
"saudades" from zeos...

----------------------------------------------------------------------
marcov · 2015-04-12 17:18

Quote from: bylaardt on April 12, 2015, 06:04:59 pm"no simple
designer", i agree! 

i have a word (in portuguese) but haven't translate to english without
distortions:
"saudades" from zeos...

(Portuguese? Never would have guessed from that last name :-) (
-aard(t) is a typical Dutch/Flemish ending))

Yes, Zeos is the easy way out, but without testing and pushing the
envelope it will forever stay that way, which is why I picked sqldb
this time around.

----------------------------------------------------------------------
LacaK · 2015-04-12 18:53

There are also articles on wiki f.e. :
http://wiki.freepascal.org/SqlDBHowto#How_to_change_data_in_a_table.3F
http://wiki.freepascal.org/SQLdb_Tutorial1
etc.

Btw if you use trunk version of fcl-db you can use also
TSQLQuery.Options property and set:
"sqoAutoApplyUpdates", "sqoAutoCommit"

Which are convenience options and they do what you want. On Post
changed record is applied to database and transaction is committed.
Plus seting "sqoKeepOpenOnCommit" remains client dataset opened so you
can continue in editing.

----------------------------------------------------------------------
marcov · 2015-04-12 19:45

Ok, yes I am using trunk, and it seems to work so I'm one step
further.

Still updating fails:

"No update query specified and failed to generate one (No fields for
Inclusion in where statement found)".

I only have "select * from tabelname" style select query. Under what
conditions can the tquery generate an update statement and when not?

----------------------------------------------------------------------
LacaK · 2015-04-13 05:16

It depends on Upda]"]>Blockedde property.

Default is upWhereKeyOnly and in this case table must have primary key
(or you must manually set pfInKey in Field.ProviderFlag)

If you use for Upda]"]>Blockedde f.e. upWhereAll then SQL UPDATE
statement is constructed, and in WHERE are used all fields from your
dataset (which have in Field.ProviderFlag pfInWhere, which is default)

----------------------------------------------------------------------
mangakissa · 2015-04-13 07:47

Quote from: marcov"No update query specified and failed to generate
one (No fields for Inclusion in where statement found)".It's also a
problem in ZEOS. Lacak is right. I assuming you working with a DBGrid?
For user, who doen't have the latest trunk, use afterpost to apply the
record. If it's not a dbgrid, call refresh for the primary key or
create this before the post.

----------------------------------------------------------------------
marcov · 2015-04-15 16:26

Quote from: LacaK on April 13, 2015, 07:16:09 amDefault is
upWhereKeyOnly and in this case table must have primary key (or you
must manually set pfInKey in Field.ProviderFlag)

That indeed fixed it. I now have a very simple working app, thanks.