Hi,
I recently ran into a problem with prepared statements in SQLite3.
I use a prepared statement with long life time because it's very complex
and not executed concurrently. But after execution no modifying
statements could be executed in that database because it was locked.
After some investigation I found
http://www.sqlite.org/cvstrac/wiki?p=DatabaseIsLocked . So sqlite3_reset
has to be called on the statement. As the library has no knowledge about
the point in time the statement is no longer needed (multi-row sets
etc.), I thought something like an explicit reset is needed.
I attached a patch which introduces a reset method on statements. It has
a no-op implementation for all backends except SQLite. The patch also
contains a test case which does not run without the reset. It leads to
the error "SOCIERROR: sqlite3_statement_backend::loadOne: database is
locked".
All this is tested on Windows XP and Ubuntu 10.10 with SQLite 3.7.2.
Best Regards
Henning
>From 868a20e703cf1ef714a53a05233ef2039061506b Mon Sep 17 00:00:00 2001
From: Henning Basold <[email protected]>
Date: Thu, 27 Jan 2011 08:21:47 +0100
Subject: [PATCH] Added reset facilities for statements
The need arises in using SQLite with prepared statements.
If one uses a prepared statement in SQLite then other connections
can not aquire a write lock after executing that statement
if sqlite3_reset isn't called. This is reflected by the test
case which causes a "db locked" error.
See http://www.sqlite.org/cvstrac/wiki?p=DatabaseIsLocked.
---
src/backends/sqlite3/soci-sqlite3.h | 1 +
src/backends/sqlite3/statement.cpp | 18 ++++++++++++++
src/backends/sqlite3/test/test-sqlite3.cpp | 35 ++++++++++++++++++++++++++++
src/core/soci-backend.h | 1 +
src/core/statement.cpp | 5 ++++
src/core/statement.h | 3 ++
6 files changed, 63 insertions(+), 0 deletions(-)
diff --git a/src/backends/sqlite3/soci-sqlite3.h b/src/backends/sqlite3/soci-sqlite3.h
index 3f9d2fe..2ee2874 100644
--- a/src/backends/sqlite3/soci-sqlite3.h
+++ b/src/backends/sqlite3/soci-sqlite3.h
@@ -167,6 +167,7 @@ struct sqlite3_statement_backend : details::statement_backend
virtual void clean_up();
virtual void prepare(std::string const &query,
details::statement_type eType);
+ virtual void reset();
void reset_if_needed();
virtual exec_fetch_result execute(int number);
diff --git a/src/backends/sqlite3/statement.cpp b/src/backends/sqlite3/statement.cpp
index 0933c75..805233a 100644
--- a/src/backends/sqlite3/statement.cpp
+++ b/src/backends/sqlite3/statement.cpp
@@ -68,6 +68,24 @@ void sqlite3_statement_backend::prepare(std::string const & query,
databaseReady_ = true;
}
+void sqlite3_statement_backend::reset()
+{
+ if (stmt_)
+ {
+ int res = sqlite3_reset(stmt_);
+ if (res != SQLITE_OK)
+ {
+ char const* zErrMsg = sqlite3_errmsg(session_.conn_);
+
+ std::ostringstream ss;
+ ss << "sqlite3_statement_backend::reset: "
+ << zErrMsg;
+ throw soci_error(ss.str());
+ }
+ databaseReady_ = true;
+ }
+}
+
// sqlite3_reset needs to be called before a prepared statment can
// be executed a second time.
void sqlite3_statement_backend::reset_if_needed()
diff --git a/src/backends/sqlite3/test/test-sqlite3.cpp b/src/backends/sqlite3/test/test-sqlite3.cpp
index 93d26dd..d579c37 100644
--- a/src/backends/sqlite3/test/test-sqlite3.cpp
+++ b/src/backends/sqlite3/test/test-sqlite3.cpp
@@ -258,6 +258,40 @@ void test5()
std::cout << "test 5 passed" << std::endl;
}
+// Test reused prepared statement which would lock the database for writing without reset.
+// This happens only when two different connections to the database are used
+// where the first is "assigned" to the prepared statement and the second one
+// is used for writing.
+void test6()
+{
+ {
+ // A file for the database has to be used as in memory databases
+ // can not be shared throughout connections.
+ session sql1(backEnd, "test6.db");
+ session sql2(backEnd, "test6.db");
+
+ test4_table_creator tableCreator(sql1);
+
+ sql1 << "insert into soci_test(name) values('john')";
+ sql1 << "insert into soci_test(name) values('james')";
+
+ {
+ int key1;
+ std::string name1;
+ statement st = (sql1.prepare << "select * from soci_test", into(key1), into(name1));
+
+ st.execute(true);
+ assert(name1 == "john");
+ // finishes the query so that writing is possible again
+ st.reset();
+
+ std::string name2 = "james";
+ sql2 << "delete from soci_test where name = :name", use(name2);
+ }
+ }
+ std::cout << "test 6 passed" << std::endl;
+}
+
// DDL Creation objects for common tests
struct table_creator_one : public table_creator_base
{
@@ -359,6 +393,7 @@ int main(int argc, char** argv)
test3();
test4();
test5();
+ test6();
std::cout << "\nOK, all tests passed.\n\n";
diff --git a/src/core/soci-backend.h b/src/core/soci-backend.h
index 0576569..b31ad84 100644
--- a/src/core/soci-backend.h
+++ b/src/core/soci-backend.h
@@ -147,6 +147,7 @@ public:
virtual void alloc() = 0;
virtual void clean_up() = 0;
+ virtual void reset(){}
virtual void prepare(std::string const& query, statement_type eType) = 0;
diff --git a/src/core/statement.cpp b/src/core/statement.cpp
index 582a6a9..ad194d1 100644
--- a/src/core/statement.cpp
+++ b/src/core/statement.cpp
@@ -705,6 +705,11 @@ void statement_impl::dec_ref()
}
}
+void statement_impl::reset()
+{
+ backEnd_->reset();
+}
+
standard_into_type_backend *
statement_impl::make_into_type_backend()
{
diff --git a/src/core/statement.h b/src/core/statement.h
index e639a84..43cdf7d 100644
--- a/src/core/statement.h
+++ b/src/core/statement.h
@@ -55,6 +55,7 @@ public:
void describe();
void set_row(row * r);
void exchange_for_rowset(into_type_ptr const & i);
+ void reset();
// for diagnostics and advanced users
// (downcast it to expected back-end statement class)
@@ -191,6 +192,8 @@ public:
impl_->exchange_for_rowset(i);
}
+ void reset() { impl_->reset(); }
+
// for diagnostics and advanced users
// (downcast it to expected back-end statement class)
details::statement_backend * get_backend()
--
1.7.1
------------------------------------------------------------------------------
Special Offer-- Download ArcSight Logger for FREE (a $49 USD value)!
Finally, a world-class log management solution at an even better price-free!
Download using promo code Free_Logger_4_Dev2Dev. Offer expires
February 28th, so secure your free ArcSight Logger TODAY!
http://p.sf.net/sfu/arcsight-sfd2d
_______________________________________________
Soci-users mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/soci-users
