I just posted a question a few minutes ago and it just dawned on me
that the order I am calling things in that post is be ideal, so much
that I would like to combine both web services calls. I want the
transaction that enableSlideShow() creates to also execute the SQL
which is called in setDefaultIsInSlideShow(). Since the two calls are
in different databases, I believe this would fall into the "Multi-file
Commit" concept. I understand the concept from
http://www.sqlite.org/atomiccommit.html, but how does one actually go
about doing a Multi-file Commit? I just tried to do an ATTACH
DATABASE, but the step is failing:
#define SQL_ATTACH_SYSDB "ATTACH DATABASE ?1 AS sysdb"
ATTACH DATABASE ?1 as sysDB
static int AttachToSysDb(const axutil_env_t *env, sqlite3 *db,
photo_parata_cfg_t * pCfg)
{
adb_imageDTO_t* pImageDTO = NULL;
sqlite3_stmt *stmt = NULL;
const char* pzTail = NULL;
int rc = sqlite3_prepare_v2(db, SQL_CMD(SQL_ATTACH_SYSDB), &stmt,
&pzTail);
if( rc == SQLITE_OK) rc = sqlite3_bind_text( stmt, 1,
pCfg->pszSystemDBFile, -1, SQLITE_STATIC);
if( rc == SQLITE_OK) rc = sqlite3_step(stmt);
sqlite3_finalize(stmt);
if(rc == SQLITE_DONE)
return SQLITE_OK;
return rc;
}
Sam
Here is the other post, if you want some context:
On Sun, Mar 7, 2010 at 3:36 PM, Sam Carleton <[email protected]> wrote:
> My use of SQLite is within a Apache module and Axis2/C web services.
> Every request invokes a unique call to sqlite3_open_v2, most of the
> time there are multiple calls to sqlite3_open_v2 in one request. For
> example the module part, which sends configuration to the web servers
> calls sqlite3_open_v2 as read only to initialize the web service, it
> closes the connection prior to the web service being called. Next the
> web service will open up the database in either read/write or read,
> depending on what it is doing.
>
>
>
> I am using my first transaction, which I think it working fine, except
> I am getting some strange behavior with a call that is made
> immediately after the transaction.
>
> (This client is a test harness designed exclusively to test web
> services, no other clients are running and only in instance of the
> Apache process is running. I am able to change the order quickly and
> easily, and this order is not indicative to the order things will
> normally be called in.)
>
>
> 1: Client calls the web server method setDefaultIsInSlideShow () as to
> have it return the current state, aka read only.
> 2: The client calls the method enableSlideShow() which creates and
> commits the transaction all in one call, code to follow.
> 3: The client calls setDefaultIsInSlideShow() again, this time to set
> the value. It is this time the sqlite3_step() returns SQLITE_BUSY,
> EVERY time.
>
> When I move #3 into the #1 spot, it works fine. What is really
> strange is even when I removed the BEGIN TRANSACTION/COMMIT
> TRANSACTION, I continued to get the error when #3 is #3. So at this
> point I really don't know what the issue might be. Oh, another
> strange fact, the two databases are DIFFERENT files. The transaction
> happens in the event database, the other call acts on the system
> database. Since I am totally stumped, I am posting the code as
> complete as I can make it: It is broken up into a number of different
> functions in my code, but I will try to consolidate it as much as
> possible while not leaving out details:
>
> /********** #2: enableSlideShow *********/
>
> #define SQL_SET_ISINSLIDESHOW_CUSTOMER \
> "UPDATE Customer SET IsInSlideShow = ?2 WHERE CustomerId = ?1"
>
> #define SQL_SET_ISINSLIDESHOW_NOT_CUSTOMER
> "UPDATE Customer SET IsInSlideShow = ?2 WHERE CustomerId <> ?1"
>
> enableSlideShow()
> {
> // Make note this is a different database file then the call below
> int rc = sqlite3_open_v2(pCfg->pszEventDB, &db,
> SQLITE_OPEN_READWRITE|SQLITE_OPEN_FULLMUTEX, zVfs);
> rc = sqlite3_exec(db, "BEGIN TRANSACTION", NULL, NULL, NULL);
>
> rc = UpdateSlideShowTable(env, db,
> SQL_CMD(SQL_SET_ISINSLIDESHOW_NOT_CUSTOMER), nCustomerId,
> AXIS2_FALSE);
> if( rc == SQLITE_OK) rc = UpdateSlideShowTable(env, db,
> SQL_CMD(SQL_SET_ISINSLIDESHOW_CUSTOMER), nCustomerId, AXIS2_TRUE);
>
> if( rc == SQLITE_OK)
> rc = sqlite3_exec(db, "COMMIT TRANSACTION", NULL, NULL, NULL);
> else
> rc = sqlite3_exec(db, "ROLLBACK TRANSACTION", NULL, NULL,
> NULL);
>
> sqlite3_close(db);
> }
>
>
>
> static int UpdateSlideShowTable(const axutil_env_t *env, sqlite3 *db,
> const char *zSql, int nByte, int nCustomerId, axis2_bool_t bEnable)
> {
> adb_imageDTO_t* pImageDTO = NULL;
> sqlite3_stmt *stmt = NULL;
> const char* pzTail = NULL;
>
> int rc = sqlite3_prepare_v2(db, zSql, nByte, &stmt, &pzTail);
> if( rc == SQLITE_OK) rc = sqlite3_bind_int( stmt, 1, nCustomerId);
> if( rc == SQLITE_OK) rc = sqlite3_bind_int( stmt, 2, bEnable ==
> AXIS2_FALSE ? 0 : 1);
> if( rc == SQLITE_OK) rc = sqlite3_step(stmt);
>
> sqlite3_finalize(stmt);
> if(rc == SQLITE_DONE)
> return SQLITE_OK;
> return rc;
> }
>
> /********** #3: setDefaultIsInSlideShow() that is a reader/writer *********/
>
> #define SQL_INSERT_DEFAULTISINSLIDESHOW \
> "UPDATE DBLookup SET ItemValue = ?1 WHERE Category = 'SystemSettings'
> AND ItemName = 'DefaultIsInSlideShow'"
>
> setDefaultIsInSlideShow()
> {
> // Make know that this is a different database file then the call above
> int rc = sqlite3_open_v2(pCfg->pszSystemDBFile, &db,
> SQLITE_OPEN_READWRITE|SQLITE_OPEN_FULLMUTEX, NULL);
> sqlite3_stmt *stmt = NULL;
> const char* pzTail = NULL;
>
> rc = sqlite3_prepare_v2(db, SQL_CMD(SQL_INSERT_DEFAULTISINSLIDESHOW),
> &stmt, &pzTail);
> if( rc == SQLITE_OK) rc = sqlite3_bind_int( stmt, 1, bValue ==
> AXIS2_FALSE ? 0 : 1);
> if( rc == SQLITE_OK) rc = sqlite3_step(stmt); // *** This returns
> SQLITE_BUSY every time it is called AFTER the transaction above,
> whether or not the transaction statements are present.
>
> sqlite3_finalize(stmt);
> sqlite3_close(db);
> }
>
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
