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 <scarle...@miltonstreet.com> 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 sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users