Re: [sqlite] Insert statement is ok but after reopening the db data is missing ?
Lothar Behrens wrote: > Am 10.09.2008 um 17:37 schrieb Dennis Cote: > >> Lothar Behrens wrote: >>> What is the function to rollback a transaction or commit ? >>> I want also to break into these functions. If there is no way I try >>> to implement the rollback and commit callbacks. >>> Also the closing of the database would be interesting, or analysing >>> the data in the jornal. >> The journal file is closed when a transaction ends. This is done by >> the function pager_end_transaction() at line 28880 of the >> amalgamation. Note this function is called for both a rollback or a >> commit. >> > > Hi, > > I now have seen that many of my simple select statements automatically > does a rollback on behalv of OP_Halt. > > Also I have seen that an insert, update or delete statement does > automatically a commit in some circumstances as: > > * One VDBE is running only > * the statement hits an ON FAIL and have to commit in that case > * other circumstances I do not understand yet > > If I do understand all this correctly I have one case I may stuck into: > > A select statement (not readonly) is still open (having sqlite3_step() > returning SQLITE_ROW) and then > I have created an insert statement that is committed but the outer > transaction as of the select statement > does a rollback if closed later. > > Thus, this results in readable (just inserted) data but loses these > data because the outer rollback occurs. > > Right ? > > If so, then I have to redesign something as of this may be the case in > my usage of the database API :-) > > My database form opens a statement to select some data and navigates > to one (the first, the next or any other) and > leaves the statement open in a transaction I think (form A, database > A) as of a call to sqlite3_step() returning SQLITE_ROW. > > Then I open another database form (form B, database A) and try to add > some data. There is no Commit (sqlite3BTreeCommitPhaseOne) or > rollback (sqlite3BTreeRollback), so I assume the running transaction > from form A is causing this. > > Then when I close my application the transaction (form A, database A) > is rolled back and this loses my data changes. > > Right ? That is correct. You are in autocommit mode, so each SQL statement executes in its own transaction. The outer select starts a transaction. The insert does not start a transaction, since a transaction is already open (sqlite only has a single transaction open at any time). The application can see all the changes to the database (i.e. it can see the uncommitted data). If you now close the database before you reset or finalize the select query (which will commit the transaction it started), then the open transaction will be rolled back and the changes will be lost. > > So my solution would be this: > > Don't leave sqlite3_step() operations in SQLITE_ROW state. Better try > to finish until SQLITE_DONE to close the transaction. > > I can do this because: > > * I mostly read only the primary keys of a table (there it is done > automatically) to prepare for lazy load (pattern). > * I read the full data row for a specific primary key as of any cursor > activity. (That way I have simulated full cursor support) > > I hope with that I get solved this problem. > > Please comment, If there is something still wrong in my understanding. > That should work fine. You don't have to let the select run to completion if you don't want to. If you reset or finalize the select after it returns the last desired row, it will also close the transaction. HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Insert statement is ok but after reopening the db data is missing ?
Am 10.09.2008 um 17:37 schrieb Dennis Cote: > Lothar Behrens wrote: >> What is the function to rollback a transaction or commit ? >> I want also to break into these functions. If there is no way I try >> to implement the rollback and commit callbacks. >> Also the closing of the database would be interesting, or analysing >> the data in the jornal. > > The journal file is closed when a transaction ends. This is done by > the function pager_end_transaction() at line 28880 of the > amalgamation. Note this function is called for both a rollback or a > commit. > Hi, I now have seen that many of my simple select statements automatically does a rollback on behalv of OP_Halt. Also I have seen that an insert, update or delete statement does automatically a commit in some circumstances as: * One VDBE is running only * the statement hits an ON FAIL and have to commit in that case * other circumstances I do not understand yet If I do understand all this correctly I have one case I may stuck into: A select statement (not readonly) is still open (having sqlite3_step() returning SQLITE_ROW) and then I have created an insert statement that is committed but the outer transaction as of the select statement does a rollback if closed later. Thus, this results in readable (just inserted) data but loses these data because the outer rollback occurs. Right ? If so, then I have to redesign something as of this may be the case in my usage of the database API :-) My database form opens a statement to select some data and navigates to one (the first, the next or any other) and leaves the statement open in a transaction I think (form A, database A) as of a call to sqlite3_step() returning SQLITE_ROW. Then I open another database form (form B, database A) and try to add some data. There is no Commit (sqlite3BTreeCommitPhaseOne) or rollback (sqlite3BTreeRollback), so I assume the running transaction from form A is causing this. Then when I close my application the transaction (form A, database A) is rolled back and this loses my data changes. Right ? So my solution would be this: Don't leave sqlite3_step() operations in SQLITE_ROW state. Better try to finish until SQLITE_DONE to close the transaction. I can do this because: * I mostly read only the primary keys of a table (there it is done automatically) to prepare for lazy load (pattern). * I read the full data row for a specific primary key as of any cursor activity. (That way I have simulated full cursor support) I hope with that I get solved this problem. Please comment, If there is something still wrong in my understanding. Thanks Lothar > HTH > Dennis Cote > -- | Rapid Prototyping | XSLT Codegeneration | http://www.lollisoft.de Lothar Behrens Heinrich-Scheufelen-Platz 2 73252 Lenningen ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Insert statement is ok but after reopening the db data is missing ?
Am 09.09.2008 um 22:49 schrieb Dennis Cote: > Lothar Behrens wrote: >> >> But when you say, that, if jornal files are open, transactions are >> opened, I would set a >> breakpoint at the line of code the transaction opens these jornal >> file >> and I could look >> arount there from who the transaction comes. >> >> Is that an option ? >> >> What function in the sqlite library does this ? >> > > Yes, that is an option if you are using a source code version of > SQLite, > either the individual source files or the amalgamation file, > sqlite3.c. > > The journal file is opened by the function pager_open_journal() at > line > 30868 in the amalgamation source for version 3.6.2. > Yes, It passes the opening of the jornal file as an Op_Transation block (I think so inside of VDBE) of code (Insert). And if I start my application, the first transaction is started as of an select statement to give me back my localized messages (jornal not opened in select statements) I have also checked the cleanup of the prepared statements. They would be finalized as assumed. What is the function to rollback a transaction or commit ? I want also to break into these functions. If there is no way I try to implement the rollback and commit callbacks. Also the closing of the database would be interesting, or analysing the data in the jornal. Thanks Lothar > HTH > Dennis Cote > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- | Rapid Prototyping | XSLT Codegeneration | http://www.lollisoft.de Lothar Behrens Heinrich-Scheufelen-Platz 2 73252 Lenningen ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Insert statement is ok but after reopening the db data is missing ?
Lothar Behrens wrote: > > But when you say, that, if jornal files are open, transactions are > opened, I would set a > breakpoint at the line of code the transaction opens these jornal file > and I could look > arount there from who the transaction comes. > > Is that an option ? > > What function in the sqlite library does this ? > Yes, that is an option if you are using a source code version of SQLite, either the individual source files or the amalgamation file, sqlite3.c. The journal file is opened by the function pager_open_journal() at line 30868 in the amalgamation source for version 3.6.2. HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Insert statement is ok but after reopening the db data is missing ?
Am 09.09.2008 um 20:46 schrieb Dennis Cote:
> Lothar Behrens wrote:
>>
>> I have added this function right after sqlite3_step, that does the
>> prepared insert statement.
>>
>> int nReturn = sqlite3_step((sqlite3_stmt*)(*start));
>>
>>int autocommit = sqlite3_get_autocommit(m_pDatabase);
>>
>>if (autocommit == 0) {
>> wxLogError(_("Warning: Database is not in autocommit mode.\n"));
>>}
>>
>> autocommit is always 1. Also I have thested the following:
>>
>> Open the application and opening the form to display first row -> no
>> jornal file is opened, because no write is yet done.
>>
>> Adding some rows and navigating forward and backbackward -> jornal
>> file is opened and I can see my data in the application.
>>
>
> The fact that a journal file exists at this point implies that you are
> still in a transaction. If you close the database without committing
> this transaction, the changes that you can see in your application
> will
> be rolled back and lost (see H12019 at
> http://www.sqlite.org/c3ref/close.html).
>
> Can you add a function to check the auto commit status in your main
> line
> code (i.e. where you are navigating and viewing the data)?
>
Hmm,
I can add such a function beside the others to update my status line
for sample.
But I don't believe, that I do start any transaction. This is because
if I simply open
only this database form that makes these problems all additions will
be stored and if
I restart my application the data is still there as inserted.
I only start a transaction, when I create tables, because I rewrite
some statements.
But this only happens, when the database is freshly created. Also the
code is tested
and the transaction is committed, otherwise the shema wouldn't exist
after a restart.
But when you say, that, if jornal files are open, transactions are
opened, I would set a
breakpoint at the line of code the transaction opens these jornal file
and I could look
arount there from who the transaction comes.
Is that an option ?
What function in the sqlite library does this ?
Lothar
> Dennis Cote
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
-- | Rapid Prototyping | XSLT Codegeneration | http://www.lollisoft.de
Lothar Behrens
Heinrich-Scheufelen-Platz 2
73252 Lenningen
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Insert statement is ok but after reopening the db data is missing ?
Lothar Behrens wrote:
>
> I have added this function right after sqlite3_step, that does the
> prepared insert statement.
>
> int nReturn = sqlite3_step((sqlite3_stmt*)(*start));
>
> int autocommit = sqlite3_get_autocommit(m_pDatabase);
>
> if (autocommit == 0) {
> wxLogError(_("Warning: Database is not in autocommit mode.\n"));
> }
>
> autocommit is always 1. Also I have thested the following:
>
> Open the application and opening the form to display first row -> no
> jornal file is opened, because no write is yet done.
>
> Adding some rows and navigating forward and backbackward -> jornal
> file is opened and I can see my data in the application.
>
The fact that a journal file exists at this point implies that you are
still in a transaction. If you close the database without committing
this transaction, the changes that you can see in your application will
be rolled back and lost (see H12019 at
http://www.sqlite.org/c3ref/close.html).
Can you add a function to check the auto commit status in your main line
code (i.e. where you are navigating and viewing the data)?
Dennis Cote
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Insert statement is ok but after reopening the db data is missing ?
Am 09.09.2008 um 17:20 schrieb Dennis Cote:
> Jay A. Kreibich wrote:
>>
>> Everything you describe sounds exactly as if a transaction has been
>> started, but is not committed. When you close the database, the
>> transaction is automatically (and correctly) rolled back. This will
>> also delete the journal file.
>>
>> I know you said you weren't trying to start a transaction, but you
>> might double check that. Set a breakpoint right after the INSERT is
>> finished and check to see if you have a journal file or not. You
>> could also try issuing a "BEGIN" right after the INSERT. If you get
>> an error, you're already inside a transaction.
>>
>
> An easier and more accurate way to check may be to add a call to
> sqlite3_get_autocommit() after your insert is complete. It will return
> zero if there is an active transaction, and 1 if there is not (i.e. it
> it in autocommit mode).
>
Ok,
I have added this function right after sqlite3_step, that does the
prepared insert statement.
int nReturn = sqlite3_step((sqlite3_stmt*)(*start));
int autocommit = sqlite3_get_autocommit(m_pDatabase);
if (autocommit == 0) {
wxLogError(_("Warning: Database is not in autocommit mode.\n"));
}
autocommit is always 1. Also I have thested the following:
Open the application and opening the form to display first row -> no
jornal file is opened, because no write is yet done.
Adding some rows and navigating forward and backbackward -> jornal
file is opened and I can see my data in the application.
Now I will try to use pragma database_list; but I need to restart my
box.
So I will mail the result later :-(
Lothar
-- | Rapid Prototyping | XSLT Codegeneration | http://www.lollisoft.de
Lothar Behrens
Heinrich-Scheufelen-Platz 2
73252 Lenningen
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Insert statement is ok but after reopening the db data is missing ?
Jay A. Kreibich wrote: > > Everything you describe sounds exactly as if a transaction has been > started, but is not committed. When you close the database, the > transaction is automatically (and correctly) rolled back. This will > also delete the journal file. > > I know you said you weren't trying to start a transaction, but you > might double check that. Set a breakpoint right after the INSERT is > finished and check to see if you have a journal file or not. You > could also try issuing a "BEGIN" right after the INSERT. If you get > an error, you're already inside a transaction. > An easier and more accurate way to check may be to add a call to sqlite3_get_autocommit() after your insert is complete. It will return zero if there is an active transaction, and 1 if there is not (i.e. it it in autocommit mode). See http://www.sqlite.org/c3ref/get_autocommit.html for details. HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Insert statement is ok but after reopening the db data is missing ?
On Tue, Sep 09, 2008 at 04:55:01PM +0200, Lothar Behrens scratched on the wall: > I prepare an insert statement and bind the parameters that afterwards > get executed with sqlite_step(). This function then returns SQLITE_DONE > and I prepare a new select statement wich let me display the just > inserted data in my database forms (database file not closed while > that). > Does someone have any more ideas how to narrow the problem ? > (After the insert statement until to closing of that file) Everything you describe sounds exactly as if a transaction has been started, but is not committed. When you close the database, the transaction is automatically (and correctly) rolled back. This will also delete the journal file. I know you said you weren't trying to start a transaction, but you might double check that. Set a breakpoint right after the INSERT is finished and check to see if you have a journal file or not. You could also try issuing a "BEGIN" right after the INSERT. If you get an error, you're already inside a transaction. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "'People who live in bamboo houses should not throw pandas.' Jesus said that." - "The Ninja", www.AskANinja.com, "Special Delivery 10: Pop!Tech 2006" ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Insert statement is ok but after reopening the db data is missing ?
Lothar Behrens wrote: > > Does someone have any more ideas how to narrow the problem ? > (After the insert statement until to closing of that file) > Try executing "pragma database_list;" after the insert. Double check the file name and path shown for the main database and ensure that is the same file you are looking at with your database browser. HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Insert statement is ok but after reopening the db data is missing ?
Hi, I am still struggle with my insert statement with bound parameters that - followed step by step in the VDBE - seems to be inserted but is not available after closing the database. (Checked with sqliteman) I have also created a small test application using my database API wrappers. In this sample I could insert the data, thus I cannot recreate the problem in a small application. Problem: I prepare an insert statement and bind the parameters that afterwards get executed with sqlite_step(). This function then returns SQLITE_DONE and I prepare a new select statement wich let me display the just inserted data in my database forms (database file not closed while that). I do a close of the database, have a breakpoint after that to see what sqliteman tells me: The table is empty and no jornal file is opened ! How can it be, that 1.) The insert statement succeeds (have been able to select them with a new statement while database is still opened) ? 2.) The engine doesn't store these changes, even I have seen these changes (I do not manually start a transaction that wouldn't be committed) ? Does someone have any more ideas how to narrow the problem ? (After the insert statement until to closing of that file) Thanks in advance, Lothar -- | Rapid Prototyping | XSLT Codegeneration | http://www.lollisoft.de Lothar Behrens Heinrich-Scheufelen-Platz 2 73252 Lenningen ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
