(sqlite) dbnavigator and transaction

[marcov]

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]

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]

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]

and transaction.action are seted to caCommit?

[marcov]

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]

"no simple designer", i agree! 

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

[marcov]

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

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]

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]

It depends on UpdateMode 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 UpdateMode 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]

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

Default 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.