Recent

Author Topic: (sqlite) dbnavigator and transaction  (Read 5037 times)

marcov

  • Global Moderator
  • Hero Member
  • *****
  • Posts: 8583
  • FPC developer.
(sqlite) dbnavigator and transaction
« on: April 12, 2015, 03:23:00 pm »
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

  • Sr. Member
  • ****
  • Posts: 308
Re: (sqlite) dbnavigator and transaction
« Reply #1 on: April 12, 2015, 04:41:45 pm »
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

  • Global Moderator
  • Hero Member
  • *****
  • Posts: 8583
  • FPC developer.
Re: (sqlite) dbnavigator and transaction
« Reply #2 on: April 12, 2015, 04:45:38 pm »
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);
« Last Edit: April 12, 2015, 04:49:05 pm by marcov »

bylaardt

  • Sr. Member
  • ****
  • Posts: 308
Re: (sqlite) dbnavigator and transaction
« Reply #3 on: April 12, 2015, 04:55:50 pm »
and transaction.action are seted to caCommit?

marcov

  • Global Moderator
  • Hero Member
  • *****
  • Posts: 8583
  • FPC developer.
Re: (sqlite) dbnavigator and transaction
« Reply #4 on: April 12, 2015, 05:40:52 pm »
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

  • Sr. Member
  • ****
  • Posts: 308
Re: (sqlite) dbnavigator and transaction
« Reply #5 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...

marcov

  • Global Moderator
  • Hero Member
  • *****
  • Posts: 8583
  • FPC developer.
Re: (sqlite) dbnavigator and transaction
« Reply #6 on: April 12, 2015, 07:18:30 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

  • Hero Member
  • *****
  • Posts: 600
Re: (sqlite) dbnavigator and transaction
« Reply #7 on: April 12, 2015, 08:53:53 pm »
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

  • Global Moderator
  • Hero Member
  • *****
  • Posts: 8583
  • FPC developer.
Re: (sqlite) dbnavigator and transaction
« Reply #8 on: April 12, 2015, 09:45:46 pm »
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

  • Hero Member
  • *****
  • Posts: 600
Re: (sqlite) dbnavigator and transaction
« Reply #9 on: April 13, 2015, 07:16:09 am »
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

  • Hero Member
  • *****
  • Posts: 1086
Re: (sqlite) dbnavigator and transaction
« Reply #10 on: April 13, 2015, 09:47:54 am »
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.
Lazarus 2.06 (64b) / FPC 3.0.4 / Windows 10
stucked on Delphi 10.3.1

marcov

  • Global Moderator
  • Hero Member
  • *****
  • Posts: 8583
  • FPC developer.
Re: (sqlite) dbnavigator and transaction
« Reply #11 on: April 15, 2015, 06:26:28 pm »
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.

 

TinyPortal © 2005-2018