[sqlite] Is this code OK?
Hi, ALL,
Here is the code I'm trying to use:
char *errmsg = NULL;
sqlite3_exec( handle, BEGIN, 0, 0, errmsg );
if( sqlite3_exec( , errmsg ) != SQLITE_OK )
{
printf( Error executing query: %s, sqlite3_errmsg( m_handle ) );
sqlite3_exec( handle, ROLLBACK, 0, 0, errmsg );
}
Can I reuse errmsg variable like this or do I have to call sqlite3_free()
and then execute ROLLBACK statement?
Thank you.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is this code OK?
On Thu, Sep 19, 2013 at 8:55 AM, Igor Korot ikoro...@gmail.com wrote: Can I reuse errmsg variable like this or do I have to call sqlite3_free() and then execute ROLLBACK statement? Per the API docs: ** ^If an error occurs while evaluating the SQL statements passed into ** sqlite3_exec(), then execution of the current statement stops and ** subsequent statements are skipped. ^If the 5th parameter to sqlite3_exec() ** is not NULL then any error message is written into memory obtained ** from [sqlite3_malloc()] and passed back through the 5th parameter. ** To avoid memory leaks, the application should invoke [sqlite3_free()] ** on error message strings returned through the 5th parameter of ** of sqlite3_exec() after the error message string is no longer needed. ** ^If the 5th parameter to sqlite3_exec() is not NULL and no errors ** occur, then sqlite3_exec() sets the pointer in its 5th parameter to ** NULL before returning. -- - stephan beal http://wanderinghorse.net/home/stephan/ http://gplus.to/sgbeal ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is this code OK?
Stephan, On Thu, Sep 19, 2013 at 12:46 AM, Stephan Beal sgb...@googlemail.comwrote: On Thu, Sep 19, 2013 at 8:55 AM, Igor Korot ikoro...@gmail.com wrote: Can I reuse errmsg variable like this or do I have to call sqlite3_free() and then execute ROLLBACK statement? Per the API docs: ** ^If an error occurs while evaluating the SQL statements passed into ** sqlite3_exec(), then execution of the current statement stops and ** subsequent statements are skipped. ^If the 5th parameter to sqlite3_exec() ** is not NULL then any error message is written into memory obtained ** from [sqlite3_malloc()] and passed back through the 5th parameter. ** To avoid memory leaks, the application should invoke [sqlite3_free()] ** on error message strings returned through the 5th parameter of ** of sqlite3_exec() after the error message string is no longer needed. ** ^If the 5th parameter to sqlite3_exec() is not NULL and no errors ** occur, then sqlite3_exec() sets the pointer in its 5th parameter to ** NULL before returning. OK, so I guess I have to call sqlite3_free(). Now, I do have to execute ROLLBACK statement, right? Thank you. -- - stephan beal http://wanderinghorse.net/home/stephan/ http://gplus.to/sgbeal ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is this code OK?
On Thu, Sep 19, 2013 at 10:06 AM, Igor Korot ikoro...@gmail.com wrote: OK, so I guess I have to call sqlite3_free(). Now, I do have to execute ROLLBACK statement, right? Correct - IMO, you have all the pieces in the right place, you just need to free the errmsg string. (i assume you have a corresponding COMMIT somewhere down further).) But... you're not using the error message: printf( Error executing query: %s, sqlite3_errmsg( m_handle ) ); sqlite3_exec( handle, ROLLBACK, 0, 0, errmsg ); instead you're using sqlite3_errmsg(), which means you don't need errmsg unless you use it at another place which isn't shown here. What's the difference between errmsg and calling sqlite3_errmsg()? i'm not sure there is one, and one of the astute listers will probably see this and explain the difference (if any) to us. -- - stephan beal http://wanderinghorse.net/home/stephan/ http://gplus.to/sgbeal ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is this code OK?
Stephan, On Thu, Sep 19, 2013 at 1:09 AM, Stephan Beal sgb...@googlemail.com wrote: On Thu, Sep 19, 2013 at 10:06 AM, Igor Korot ikoro...@gmail.com wrote: OK, so I guess I have to call sqlite3_free(). Now, I do have to execute ROLLBACK statement, right? Correct - IMO, you have all the pieces in the right place, you just need to free the errmsg string. (i assume you have a corresponding COMMIT somewhere down further).) Yes, I do. ;-) But... you're not using the error message: printf( Error executing query: %s, sqlite3_errmsg( m_handle ) ); sqlite3_exec( handle, ROLLBACK, 0, 0, errmsg ); instead you're using sqlite3_errmsg(), which means you don't need errmsg unless you use it at another place which isn't shown here. What's the difference between errmsg and calling sqlite3_errmsg()? i'm not sure there is one, and one of the astute listers will probably see this and explain the difference (if any) to us. It would be interesting to know the difference (if any). At least I (and probably someone else) will know what is better to use. Thank you. -- - stephan beal http://wanderinghorse.net/home/stephan/ http://gplus.to/sgbeal ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is this code OK?
On 19 Sep 2013, at 9:09am, Stephan Beal sgb...@googlemail.com wrote: What's the difference between errmsg and calling sqlite3_errmsg()? No difference in terms of the result, they're just to cope with two different programming styles. The function is provided for situations where you have already lost touch with errmsg or never kept it to start with. By the way, Igor, if this code is being shipped away from your own personal computer you should theoretically be checking the result returned by the execution of BEGIN too. Depending on what goes wrong, it may be the BEGIN which fails, and the result code from subsequent operations wouldn't tell you what the real problem is. Though you may have just simplified your code for posting. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is this code OK?
On 9/19/2013 2:55 AM, Igor Korot wrote: Here is the code I'm trying to use: char *errmsg = NULL; sqlite3_exec( handle, BEGIN, 0, 0, errmsg ); if( sqlite3_exec( , errmsg ) != SQLITE_OK ) As you are not checking the return value of the first sqlite3_exec, and are not using the error message it returns - why do you pass errmsg at all? Just pass NULL there. If you do pass a non-NULL pointer as the last parameter, then SQLite would allocate memory for it. You should then free said memory, or else you leak it. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question about begin/commit
Under normal circumstances only these stmts perform some DB activity: - BeginTransaction - ExecuteNonQuery - Commit SqliteCommand constructor as well as the following lines (setting parameters and command text) are memory constructs that prepare data for ExecuteNonQuery. If everything works correctly, then it is basically irrelevant whether these lines are inside or outside transaction block. However, if something goes wrong you have to think of the destructors. (i.e. Dispose() methods) Imagine this situation: - CommandText is a concatenation of several SQL statements. - Some of these statements (but not the last one) fail with an exception. Now SqliteCommand.Dispose() attempts to execute part of the CommandText that was not executed. In this case the order of C# using statements makes a difference. Your code, for example, makes sure that the whole action will be terminated by a rollback. On the other hand, if you exchanged the order of SQLiteCommand/SQLiteTransaction constructors, it might well happen that SqliteCommand.Dispose() will be run after rollback and thus perform unwanted data change. The code of System.Data.Sqlite wrapper is pretty complex (in my opinion too complex) and it is quite possible that more complications are possible. Placing the transaction at the uppermost level seems to be the safest and simplest procedure - you do not need to think of implementation details. -- View this message in context: http://sqlite.1065341.n5.nabble.com/Question-about-begin-commit-tp71289p71365.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Sqlite on Windows 8.1 Metro Application returning Cannot Open Error
Hey guys, I found this page describing how sqlite on Windows RT should set the temp file directory. http://www.sqlite.org/c3ref/temp_directory.html I'm using sqlite3_win32_set_directory method (in C#) to do this but I've noticed that in some instances sqlite is still returning cannot open errors (when dropping some tables). The post below suggested using PRAGMA temp_store = memory, to prevent storing temp files http://sqlite.1065341.n5.nabble.com/SQLITE-CANTOPEN-returned-from-sqlite3-step-td58437.html Doing this resolves our issue since sqlite doesn't try to write temp files. I'm just curious if anyone else has encountered this issue. - dave ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Regression: Query takes 10x longer when using version 3.8.x
On Thu, Sep 19, 2013 at 12:07 PM, Jared Albers jaredaalb...@gmail.comwrote: What is the status on this? Is there an official ticket for this so that I can track the issue without having to look for updates on the mailing list? The status is that I was unable to replicate the problem. But I have a little more time now. I'll try again. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Regression: Query takes 10x longer when using version 3.8.x
What is the status on this? Is there an official ticket for this so that I can track the issue without having to look for updates on the mailing list? Do you need more information from me? -Jared ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] A graphical tool to handle sqlite schema change(more than ALTER TABLE)
I have a tool that will do this for you and just about any other schema changte you can think of while preservbing the data and integrity of your database. Runs on WIndows and OSX and I could produce a Linux version if necessary. Check out SQLiteAdmin at www.lcsql.com Pete On Thu, Sep 19, 2013 at 9:00 AM, sqlite-users-requ...@sqlite.org wrote: Message: 3 Date: Wed, 18 Sep 2013 13:54:34 -0700 (PDT) From: niubao niuba...@gmail.com To: sqlite-users@sqlite.org Subject: Re: [sqlite] A graphical tool to handle sqlite schema change(more than ALTER TABLE) Message-ID: cafluvw4ltqasttefwnmupqvd1ogsqe5o54aetud78+4yodl...@mail.gmail.com Content-Type: text/plain; charset=us-ascii Thank you very much, but this tool does not allow me to change column names, and this is not a trivial feature. Am I missing something here? Pete lcSQL Software http://www.lcsql.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sqlite on Windows 8.1 Metro Application returning CannotOpen Error
Dave Protasowski wrote: I found this page describing how sqlite on Windows RT should set the temp file directory. http://www.sqlite.org/c3ref/temp_directory.html I'm using sqlite3_win32_set_directory method (in C#) to do this but I've noticed that in some instances sqlite is still returning cannot open errors (when dropping some tables). What version of SQLite are you using? For the first argument to sqlite3_win32_set_directory(), are you passing a value of SQLITE_WIN32_TEMP_DIRECTORY_TYPE (which equals 2)? Is the call to sqlite3_win32_set_directory() being executed prior to opening the database and is it returning SQLITE_OK? -- Joe Mistachkin ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] A graphical tool to handle sqlite schema change(more than ALTER TABLE)
On Wed, 18 Sep 2013 20:53:31 -0700 (PDT), niubao niuba...@gmail.com wrote: Thank you very much Simon, for your detailed and very clear explanation on this. I wonder if there is some materials, a tutorial or something, that are dedicated to SQLite schema change for beginners? There seems to be so many things to consider. In the Structure tab of the Firefox SQLite Manager add-on, you can export the schema for a table to a text file [1], then edit the text file and feed it back into the database with the menu item Database / Import, or with the sqlite command line tool [2]. [1] can be compared with .dump in the sqlite command line tool, as in echo .dump | sqlite3 yourdbfile schemafile.sql edit schemafile.sql to reflect your modifications [2] sqlite3 yourdbile schemafile.sql Hope this helps -- Groet, Cordialement, Pozdrawiam, Regards, Kees Nuyt ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Regression: Query takes 10x longer when using version 3.8.x
After laboriously translating your schema and query into something readable, I get script shown below. We (all SQLite developers have been consulted and agree) conclude that the result of the query is undefined. You are sorting on the rowid of a subquery. But the rowid of a subquery is undefined, so anything can happen with there. Was SQLite 3.7.17 really giving you the answer you expected? If so, we think that was by luck. Can you recast the query in such a way that it does not sort on the rowid of a subquery? Note that an ORDER BY clause without a LIMIT clause in a subquery is pointless and might be removed by the query optimizer. CREATE TABLE Items( uid INTEGER PRIMARY KEY ASC, id TEXT DEFAULT '', type TEXT DEFAULT track, kind TEXT DEFAULT '', error INT DEFAULT 0, name TEXT DEFAULT '', album TEXT DEFAULT '', artist TEXT DEFAULT '', genre TEXT DEFAULT '', composer TEXT DEFAULT '', description TEXT DEFAULT '', popularity REAL DEFAULT 0, frequency REAL DEFAULT 0, band TEXT DEFAULT '', call_letters TEXT DEFAULT '', city TEXT DEFAULT '', state TEXT DEFAULT '', country TEXT DEFAULT '', provider TEXT DEFAULT '', label TEXT DEFAULT '', copyright TEXT DEFAULT '', is_explicit TEXT DEFAULT false, is_protected TEXT DEFAULT false, is_purchased TEXT DEFAULT false, bpm INT DEFAULT 0, bit_rate INT DEFAULT 0, sample_rate INT DEFAULT 0, format TEXT DEFAULT '', size INT DEFAULT 0, time INT DEFAULT 0, url TEXT DEFAULT '', can_play TEXT DEFAULT false, can_pause TEXT DEFAULT false, can_seek TEXT DEFAULT false, can_next TEXT DEFAULT false, can_previous TEXT DEFAULT false, can_queue TEXT DEFAULT false, can_order TEXT DEFAULT false, can_like TEXT DEFAULT false, can_rate TEXT DEFAULT false, can_star TEXT DEFAULT false, modified_date INT DEFAULT 0, released_date INT DEFAULT 0, purchased_date INT DEFAULT 0, skipped_date INT DEFAULT 0, track_number INT DEFAULT 0, disc_number INT DEFAULT 0, track_count INT DEFAULT 0, disc_count INT DEFAULT 0, listener_count INT DEFAULT 0, play_count INT DEFAULT 0, skip_count INT DEFAULT 0 ); CREATE TABLE Relationships( parent INTEGER, child INTEGER, instance INT DEFAULT 0, owner DEFAULT '', relationship DEFAULT '' ); CREATE TABLE Defaults( type TEXT DEFAULT '', may_play TEXT DEFAULT true, may_pause TEXT DEFAULT true, may_seek TEXT DEFAULT true, may_next TEXT DEFAULT true, may_previous TEXT DEFAULT true, may_queue TEXT DEFAULT false, is_queued TEXT DEFAULT true, may_order TEXT DEFAULT true, may_like TEXT DEFAULT false, is_liked TEXT DEFAULT '', may_rate TEXT DEFAULT false, rating REAL DEFAULT 0.0, may_star TEXT DEFAULT false, is_starred TEXT DEFAULT false, added_date INT DEFAULT 0, played_date INT DEFAULT 0 ); CREATE TABLE Attributes( parent INTEGER, child INTEGER, instance INT, owner TEXT, may_play TEXT, may_pause TEXT, may_seek TEXT, may_next TEXT, may_previous TEXT, may_queue TEXT, is_queued TEXT, may_order TEXT, may_like TEXT, is_liked TEXT, may_rate TEXT, rating REAL, may_star TEXT, is_starred TEXT, added_date INT, played_date INT ); CREATE INDEX dtypes ON Defaults (type); CREATE INDEX types ON Items (type); CREATE INDEX albums ON Items (album); CREATE INDEX genres ON Items (genre); CREATE UNIQUE INDEX guids ON Items (id); CREATE UNIQUE INDEX relations ON Relationships (parent, child, instance); CREATE UNIQUE INDEX attribs ON Attributes (parent, child, instance, owner); CREATE INDEX parents ON Relationships (parent); CREATE INDEX childs ON Relationships (child); CREATE INDEX instances ON Relationships (instance); CREATE INDEX owners ON Relationships (owner); CREATE INDEX relation ON Relationships (relationship); CREATE INDEX kinds ON Items (kind); CREATE INDEX attrib_parent ON Attributes (parent); CREATE INDEX attrib_child ON Attributes (child); CREATE INDEX attrib_instance ON Attributes (instance); CREATE INDEX attrib_owner ON Attributes (owner); CREATE INDEX names ON Items (name collate nocase); CREATE INDEX artists ON Items (artist collate nocase); explain query plan SELECT R.child, R.instance, R.owner, R.relationship, I.*, NS.rowid AS sort, COALESCE(L1.may_play, L2.may_play, L3.may_play, L4.may_play, L5.may_play, L6.may_play, L7.may_play, L8.may_play, D.may_play) AS may_play, COALESCE(L1.may_pause, L2.may_pause, L3.may_pause, L4.may_pause, L5.may_pause, L6.may_pause, L7.may_pause, L8.may_pause, D.may_pause) AS may_pause, COALESCE(L1.may_seek, L2.may_seek, L3.may_seek, L4.may_seek, L5.may_seek, L6.may_seek, L7.may_seek, L8.may_seek, D.may_seek) AS may_seek, COALESCE(L1.may_next, L2.may_next, L3.may_next, L4.may_next, L5.may_next, L6.may_next, L7.may_next, L8.may_next, D.may_next) AS may_next, COALESCE(L1.may_previous, L2.may_previous,
Re: [sqlite] Is this code OK?
Hi, Simon, On Thu, Sep 19, 2013 at 5:16 AM, Simon Slavin slav...@bigfraud.org wrote: On 19 Sep 2013, at 9:09am, Stephan Beal sgb...@googlemail.com wrote: What's the difference between errmsg and calling sqlite3_errmsg()? No difference in terms of the result, they're just to cope with two different programming styles. The function is provided for situations where you have already lost touch with errmsg or never kept it to start with. By the way, Igor, if this code is being shipped away from your own personal computer you should theoretically be checking the result returned by the execution of BEGIN too. Depending on what goes wrong, it may be the BEGIN which fails, and the result code from subsequent operations wouldn't tell you what the real problem is. Though you may have just simplified your code for posting. Yes, the check code omitted for simplicity. And thank you for clarification. It is hard to imagine the situation when the variable will go out of scope, but that's OK. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sqlite on Windows 8.1 Metro Application returning CannotOpen Error
For the first argument to sqlite3_win32_set_directory(), are you passing a value of SQLITE_WIN32_TEMP_DIRECTORY_TYPE (which equals 2)? Yes Is the call to sqlite3_win32_set_directory() being executed prior to opening the database and is it returning SQLITE_OK? Yes to both I'm using this library the relevant code snippet is: https://github.com/koush/sqlite-net/blob/master/src/SQLite.cs#L150 While debugging I've confirmed that the call to SetDirectory is returning SQLITE_OK (0). I'm using the sqlite visual studio extension ( http://www.sqlite.org/2013/sqlite-winrt81-3080002.vsix) - dave ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is this code OK?
Hi, Igor, On Thu, Sep 19, 2013 at 5:54 AM, Igor Tandetnik i...@tandetnik.org wrote: On 9/19/2013 2:55 AM, Igor Korot wrote: Here is the code I'm trying to use: char *errmsg = NULL; sqlite3_exec( handle, BEGIN, 0, 0, errmsg ); if( sqlite3_exec( , errmsg ) != SQLITE_OK ) As you are not checking the return value of the first sqlite3_exec, and are not using the error message it returns - why do you pass errmsg at all? Just pass NULL there. I'm. See my reply to Simon. But as he explain, I changed my code to use sqlite3_errmsg() and am passing 0 to sqlite3_exec(). If you do pass a non-NULL pointer as the last parameter, then SQLite would allocate memory for it. You should then free said memory, or else you leak it. Yes, I understand that. My question was more about re-using the variable between to calls to SQLite. Thank you. -- Igor Tandetnik __**_ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**usershttp://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] COMMIT or ROLLBACK failure
Hi, ALL,
Consider following piece of code:
int res = sqlite3_exec(..., BEGIN... );
if( res != SQLITE_OK )
{
printf( Error occured on begin transaction. Please try again. );
return;
}
// some operations on the database
// if operations are successful
sqlite3_exec( ..., COMMIT, );
// else
sqlite3_exec( ..., ROLLBACK, ... );
Now, AFAIU, I need to check if the COMMIT is successful.
But what should I do if it fails? Do I just report the failure to the user?
Do I need to call ROLLBACK? And what if it will also fail?
And in else branch - do I check for ROLLBACK result? And if it fails
what do I do?
Thank you.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sqlite on Windows 8.1 Metro Application returning CannotOpen Error
Dave Protasowski wrote: I'm using this library the relevant code snippet is: https://github.com/koush/sqlite-net/blob/master/src/SQLite.cs#L150 While debugging I've confirmed that the call to SetDirectory is returning SQLITE_OK (0). Are you setting the temporary directory to a location where your application has read-write permissions? What result do you get for PRAGMA temp_store_directory; ? Are you able to install a sqlite3_log callback (via sqlite3_config) and see more details about the error message you are seeing? -- Joe Mistachkin ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is this code OK?
On 9/19/2013 5:43 PM, Igor Korot wrote: If you do pass a non-NULL pointer as the last parameter, then SQLite would allocate memory for it. You should then free said memory, or else you leak it. Yes, I understand that. My question was more about re-using the variable between to calls to SQLite. Well, you can reuse the variable *after* you free the memory it points to. Consider: // OK char* p = new char[42]; delete[] p; p = new char[84]; // Not OK char* p = new char[42]; p = new char[84]; Your situation is the same: sqlite3_exec effectively acts as a memory allocation routine. It's not reusing the variable per se that's a problem, it's losing a pointer to memory that was allocated but not yet freed. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] COMMIT or ROLLBACK failure
On Thu, Sep 19, 2013 at 5:50 PM, Igor Korot ikoro...@gmail.com wrote: Now, AFAIU, I need to check if the COMMIT is successful. But what should I do if it fails? Do I just report the failure to the user? Do I need to call ROLLBACK? And what if it will also fail? And in else branch - do I check for ROLLBACK result? And if it fails what do I do? You probably should report the COMMIT failure to the user, yes. Beyond that, what you do depends on what you want to do next. You can just call exit(1) if that is appropriate. Or you can ROLLBACK and keep using the connection. Or you can sqlite3_close() the database connection if you want. Depending on the reason for the COMMIT failure, you might be able to try it again, after a delay. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] COMMIT or ROLLBACK failure
On Thu, Sep 19, 2013 at 4:35 PM, Richard Hipp d...@sqlite.org wrote: On Thu, Sep 19, 2013 at 5:50 PM, Igor Korot ikoro...@gmail.com wrote: Now, AFAIU, I need to check if the COMMIT is successful. But what should I do if it fails? Do I just report the failure to the user? Do I need to call ROLLBACK? And what if it will also fail? And in else branch - do I check for ROLLBACK result? And if it fails what do I do? You probably should report the COMMIT failure to the user, yes. Beyond that, what you do depends on what you want to do next. You can just call exit(1) if that is appropriate. Or you can ROLLBACK and keep using the connection. Or you can sqlite3_close() the database connection if you want. OK, so if I don't call ROLLBACK what will happen? Will I end up with the screw-up database file? Problem is that if the COMMIT fails, ROLLBACK might fail as well. That's why I ask if I have to call ROLLBACK or just continue working. Thank you. Depending on the reason for the COMMIT failure, you might be able to try it again, after a delay. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] COMMIT or ROLLBACK failure
On Thu, Sep 19, 2013 at 4:35 PM, Richard Hipp d...@sqlite.org wrote: On Thu, Sep 19, 2013 at 5:50 PM, Igor Korot ikoro...@gmail.com wrote: Now, AFAIU, I need to check if the COMMIT is successful. But what should I do if it fails? Do I just report the failure to the user? Do I need to call ROLLBACK? And what if it will also fail? And in else branch - do I check for ROLLBACK result? And if it fails what do I do? You probably should report the COMMIT failure to the user, yes. Beyond that, what you do depends on what you want to do next. You can just call exit(1) if that is appropriate. Or you can ROLLBACK and keep using the connection. Or you can sqlite3_close() the database connection if you want. The answers to your questions are self-evident ... OK, so if I don't call ROLLBACK what will happen? Then the transaction will continue. Any further operations will take place within the context of the existing transaction that you have not yet committed. Attempts to start a new transaction will fail. Will I end up with the screw-up database file? That depends on your definition of screw-up. If you mean screw-up as in render corrupt and unuseable that will depend on the nature of the error you are ignoring. If you mean screw up as in the database is perfectly fine but you have by your own actions rendered your own data inconsistent, that may be very likely. Especially since you want a rule for whether to continue or not. Whether you continue or not is your decision based upon (a) the exact error (ie, the reason why the commit did not succeed) and (b) the consequence of continuing the same transaction. It is determined by your application. Problem is that if the COMMIT fails, ROLLBACK might fail as well. That is entirely possible. You can determine what to do by the error code returned from the commit attempt. And/or the rollback attempt. These codes will inform your decision as to how to carry on. Or to die. That's why I ask if I have to call ROLLBACK or just continue working. That depends if given the circumstance and the failure to commit, you want to attempt to commit again, rollback, or just ignore the failure to commit and carry on depending, on if you need the transaction to commit, to rollback, or if you can just carry on. This is an application issue, not a database issue. Consider the following scenario: BEGIN; UPDATE Accounts set balance=balance-100.00 where acctno=394583; UPDATE Accounts set balance=balance+100.00 where acctno=837294; COMMIT; And the commit does not return SQLITE_OK. Is it OK to just carry on with the transfer not having been confirmed? Do you want to try to commit again? Do you want to rollback the transaction? I don't know. Nobody knows except the person writing the application. Thank you. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
