Re: [sqlite] Question about using SQLite3 on Windows Mobile 6+
Thank you sir! Indeed, I am writing it in .NET. Anthony - Original Message - From: "Roosevelt Anderson" To: "General Discussion of SQLite Database" Sent: Sunday, March 07, 2010 8:10 PM Subject: Re: [sqlite] Question about using SQLite3 on Windows Mobile 6+ > I'm guessing you are writing this in .NET. If so, you need to use the > sqlite ADO.NET provider found here: > > http://sqlite.phxsoftware.com/ > > On Sun, Mar 7, 2010 at 7:33 PM, Anthony Papillion > wrote: >> Hello Everyone, >> >> I'm totally new to SQLite and am trying to use it in a new Windows Mobile >> application. I've downloaded the SQLite3.dll and went to my project and >> tried to add a reference to the dll. For some reason, when I try to add >> the >> reference, I am told "A reference to sqlite3.dll could not be added" with >> no >> further information. >> >> I've been searching Google for a while and can't find a resolution so I'm >> hoping someone can help me out. >> >> Thanks a lot! >> Anthony Papillion >> OpenEMR HQ, Inc. >> www.openemrhq.com >> ___ >> 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 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question about using SQLite3 on Windows Mobile 6+
I'm guessing you are writing this in .NET. If so, you need to use the sqlite ADO.NET provider found here: http://sqlite.phxsoftware.com/ On Sun, Mar 7, 2010 at 7:33 PM, Anthony Papillion wrote: > Hello Everyone, > > I'm totally new to SQLite and am trying to use it in a new Windows Mobile > application. I've downloaded the SQLite3.dll and went to my project and > tried to add a reference to the dll. For some reason, when I try to add the > reference, I am told "A reference to sqlite3.dll could not be added" with no > further information. > > I've been searching Google for a while and can't find a resolution so I'm > hoping someone can help me out. > > Thanks a lot! > Anthony Papillion > OpenEMR HQ, Inc. > www.openemrhq.com > ___ > 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
[sqlite] Question about using SQLite3 on Windows Mobile 6+
Hello Everyone, I'm totally new to SQLite and am trying to use it in a new Windows Mobile application. I've downloaded the SQLite3.dll and went to my project and tried to add a reference to the dll. For some reason, when I try to add the reference, I am told "A reference to sqlite3.dll could not be added" with no further information. I've been searching Google for a while and can't find a resolution so I'm hoping someone can help me out. Thanks a lot! Anthony Papillion OpenEMR HQ, Inc. www.openemrhq.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Feasability of a Range function
Jean-Christophe Deschamps wrote: >> Wouldn't it make more sense for "i in 1..5" to expand to "i >= 1 and i >> <= 5"? >> >> Then it would also work for ordered types that aren't ordinal, such as >> rationals >> and strings and blobs and dates etc, and it would work for very large >> ranges, >> since there's no conceptual need to generate all the individual values. >> >> Of course, you'd want to support all 4 variants: 1..5, 1..^5, 1^..5, >> 1^..^5, >> where a ^ means exclude that endpoint and its absence means include. >> >> This is more flexible than SQL's BETWEEN, which I believe only covers >> one of >> those 4 options. > > That's getting into generic interval support. I find this interesting > even if its really much more ambitious than my simple-minded (and > highly optional) initial need/question. > > I might dig further in this direction someday. Good point. > > Thanks Darren. Getting into, yes. Generally speaking, any time someone is talking about a range in terms of 2 endpoint values, there are 2 distinct things they want: 1. Test if a value is between those 2 endpoints, in which case we have an interval, and the endpoints can be any ordered type. Alternate ways to spell that generally is a pair of binary order-comparison tests. Or you support having your interval as a value in and of itself, but that would be overkill in a system that doesn't already support collection-typed values. That is, I would not recommend SQLite goes to support actual interval/range types, but supporting the .. etc as simply a shorthand syntax for existing comparison ops it already supports, I would say is reasonable to support, essentially an expansion of BETWEEN. 2. Generate a list of values, in which case you need an ordinal type, or a closure to explicitly generate the next list element from a prior one. Such as how one may generically define a "sequence generator". FYI, my Muldis D language for RDBMSs, and Perl 6, as well as other languages, have actual interval types, so you can say "foo in bar" if you want. -- Darren Duncan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Feasability of a Range function
>Wouldn't it make more sense for "i in 1..5" to expand to "i >= 1 and i ><= 5"? > >Then it would also work for ordered types that aren't ordinal, such as >rationals >and strings and blobs and dates etc, and it would work for very large >ranges, >since there's no conceptual need to generate all the individual values. > >Of course, you'd want to support all 4 variants: 1..5, 1..^5, 1^..5, >1^..^5, >where a ^ means exclude that endpoint and its absence means include. > >This is more flexible than SQL's BETWEEN, which I believe only covers >one of >those 4 options. That's getting into generic interval support. I find this interesting even if its really much more ambitious than my simple-minded (and highly optional) initial need/question. I might dig further in this direction someday. Good point. Thanks Darren. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLITE3 Recompiling for FTS3
For Windows: I followed the best of my ability recompiling the most current SQLITE3.DLL to get the FTS3 incorporated. I added the compiler directive SQLITE_ENABLE_FTS3 SQLITE_ENABLE_FTS3_PARENTHESIS and was able to create a SQLITE3.DLL but it faults when a example test C/C++ program runs. No problem compiling with FTS3 disable. But the DLLs are smaller than the official sqlite.org website v3.6.22 precompiled binaries For Windows. My compile: 417,792 sqlite3.dll Official:511,383 sqlite3.dll The difference I can only see if the official has a dependency on std C++ rtl msvcrt.dllwhere my compiled has no dependency on msvcrt.dll I am not sure if I have the MS VS2005 project file correct, but I also recompiled under VC6 too. All I did to create the project was to add all the *.C/*.H files to a project and added the defined (for release) WIN32 NDEBUG _WINDOWS _USRDLL SQLITE3_EXPORTS; SQLITE_ENABLE_FTS3 SQLITE_ENABLE_FTS3_PARENTHESIS SQLITE_ENABLE_COLUMN_METADATA I guess, I ideally I would like to able to reproduce the same official size and then see how to get the FTS3 logic added. Tips? Thanks in Advance -- HLS ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Feasability of a Range function
Jean-Christophe Deschamps wrote: > I'm trying to determine if a Range(from, to) function can be made as an > extension function. > > Its effect would be to expand, for instance, range(1, 5) into (1, 2, 3, > 4, 5) for use in constructs similar to > select some_scalar_function(i) where i in range(1, 5); > without having to build a table holding integers 1..5 > > I don't see how that's possible, but maybe someone with better internal > knowledge can advise. Wouldn't it make more sense for "i in 1..5" to expand to "i >= 1 and i <= 5"? Then it would also work for ordered types that aren't ordinal, such as rationals and strings and blobs and dates etc, and it would work for very large ranges, since there's no conceptual need to generate all the individual values. Of course, you'd want to support all 4 variants: 1..5, 1..^5, 1^..5, 1^..^5, where a ^ means exclude that endpoint and its absence means include. This is more flexible than SQL's BETWEEN, which I believe only covers one of those 4 options. -- Darren Duncan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how to execute an ATTACH DATABASE?
There is another condition I didn't see originally: You have to attach to the other database BEFORE starting the transaction. P.S. Who ever is responsible for putting in helpful error messages, God bless you! On Sun, Mar 7, 2010 at 4:34 PM, Sam Carleton wrote: > On Sun, Mar 7, 2010 at 4:16 PM, Jean-Christophe Deschamps > wrote: >> >>>ATTACH DATABASE ?1 as sysDB >> >> AFAIK you can't use parameter binding for anything else than litteral >> values. >> >> It makes sense since it would be impossible for the parser and >> optimizer to evaluate and produce run-time code for a statement without >> knowing beforehand which database or column the statement actually >> refers to. > > Thank you, I have a sneaking suspicion that was the issue. I was just > hoping that I could get away with it, fore I use parameter binding as > much as a sprintf, as I do to prevent SQL injection. Should the DB > path be in singe quotes, double quotes, or no quotes at all? > > Sam > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Understanding TRANSACTION
I figured it out, I was NOT calling sqlite3_finalize(stmt) in the initial call to setDefaultIsInSlideShow() which was read only, so the table still had a read lock on it, so the read/write always failed. On Sun, Mar 7, 2010 at 3:36 PM, Sam Carleton 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 trans
Re: [sqlite] Problem with SQLite in BCB 4
Well, sorry to hear that. I tested the function: vector > CSGDb::query(char* query, sqlite3 *database) { sqlite3_stmt *statement; vector > results; if(sqlite3_prepare_v2(database, query, -1, &statement, 0) == SQLITE_OK) { int cols = sqlite3_column_count(statement); int result = 0; while(true) { result = sqlite3_step(statement); if(result == SQLITE_ROW) { vector values; for(int col = 0; col < cols; col++) { std::string val; char * ptr = (char*)sqlite3_column_text(statement, col); if(ptr) { val = ptr; } else val = ""; values.push_back(val); } results.push_back(values); } else { break; } } sqlite3_finalize(statement); } return results; } and it works for me fine. Could you print the value of val before it is pushed to the values vector? Of course the problem is not Sqlite problem but the proper usage of STL. - Original Message From: Chimerian To: sqlite-users@sqlite.org Sent: Sun, March 7, 2010 4:23:30 PM Subject: Re: [sqlite] Problem with SQLite in BCB 4 Unfortunately it still doesn't work. I have error in line values.push_back(val); I tried to run program on Windows XP - I have this same error. Links to error screens: http://chimerian.net/error1.jpg http://chimerian.net/error2.jpg __ Looking for the perfect gift? Give the gift of Flickr! http://www.flickr.com/gift/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how to execute an ATTACH DATABASE?
On Sun, Mar 7, 2010 at 4:16 PM, Jean-Christophe Deschamps wrote: > >>ATTACH DATABASE ?1 as sysDB > > AFAIK you can't use parameter binding for anything else than litteral > values. > > It makes sense since it would be impossible for the parser and > optimizer to evaluate and produce run-time code for a statement without > knowing beforehand which database or column the statement actually > refers to. Thank you, I have a sneaking suspicion that was the issue. I was just hoping that I could get away with it, fore I use parameter binding as much as a sprintf, as I do to prevent SQL injection. Should the DB path be in singe quotes, double quotes, or no quotes at all? Sam ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problem with SQLite in BCB 4
Unfortunately it still doesn't work. I have error in line values.push_back(val); I tried to run program on Windows XP - I have this same error. Links to error screens: http://chimerian.net/error1.jpg http://chimerian.net/error2.jpg if(result == SQLITE_ROW) { vector values; for(int col = 0; col < cols; col++) { std::string val; char * ptr = (char*)sqlite3_column_text(statement, col); if(ptr) { val = ptr; } else val = ""; // now below is a error values.push_back(val); } results.push_back(values); } else { break; } ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how to execute an ATTACH DATABASE?
>ATTACH DATABASE ?1 as sysDB AFAIK you can't use parameter binding for anything else than litteral values. It makes sense since it would be impossible for the parser and optimizer to evaluate and produce run-time code for a statement without knowing beforehand which database or column the statement actually refers to. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] how to execute an ATTACH DATABASE?
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 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( s
Re: [sqlite] Understanding TRANSACTION's
Please ignore this post, I just followed up with another post with a lot more info, I am still having the problem, though. Sam On Sun, Mar 7, 2010 at 10:27 AM, Sam Carleton wrote: > My use of SQLite is within a Apache module and Axis2/C web services. At a > minimum, every request invokes a unique call to sqlite3_open_v2, most of the > time there are multiple calls to sqlite3_open_v2, one for each specific > task. An example would be the module part, which sends configuration to the > web servers calls sqlite3_open_v2 in readonly to set things up, then it > closes the connection. Later the web service will open up the database in > either read/write or read, depending on what it is doing. > So far all the inserts/updates have not needed to be wrapped in a > transaction. I have finally run into a case where a set of updates do need > to be wrapped in a tranaction, and things are not working correctly. Here > is what I am doing: > 1: sqlite3_exec(db, "BEGIN TRANSACTION", NULL, NULL, NULL); > 2: Calling a function that prepare and execute: UPDATE Customer SET > IsInSlideShow = 0 WHERE CustomerId <> ?1 > 3: Calling same method that prepare and execute: UPDATE Customer SET > IsInSlideShow = 1 WHERE CustomerId = ?1 > 4a: if all is successful: sqlite3_exec(db, "COMMIT TRANSACTION", NULL, NULL, > NULL); > 4b: if something fails: sqlite3_exec(db, "ROLLBACK TRANSACTION", NULL, NULL, > NULL); > This seems to work fine. The problem comes in the next web service call > that tries to update the database. The call to sqlite3_step() always > returns SQLITE_BUSY when called right after the above web service, but runs > fine when called before the above web service. I am guessing that I am > doing something wrong in the above transaction. Any thoughts? > Sam ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Understanding TRANSACTION
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
[sqlite] Understanding TRANSACTION's
My use of SQLite is within a Apache module and Axis2/C web services. At a minimum, every request invokes a unique call to sqlite3_open_v2, most of the time there are multiple calls to sqlite3_open_v2, one for each specific task. An example would be the module part, which sends configuration to the web servers calls sqlite3_open_v2 in readonly to set things up, then it closes the connection. Later the web service will open up the database in either read/write or read, depending on what it is doing. So far all the inserts/updates have not needed to be wrapped in a transaction. I have finally run into a case where a set of updates do need to be wrapped in a tranaction, and things are not working correctly. Here is what I am doing: 1: sqlite3_exec(db, "BEGIN TRANSACTION", NULL, NULL, NULL); 2: Calling a function that prepare and execute: UPDATE Customer SET IsInSlideShow = 0 WHERE CustomerId <> ?1 3: Calling same method that prepare and execute: UPDATE Customer SET IsInSlideShow = 1 WHERE CustomerId = ?1 4a: if all is successful: sqlite3_exec(db, "COMMIT TRANSACTION", NULL, NULL, NULL); 4b: if something fails: sqlite3_exec(db, "ROLLBACK TRANSACTION", NULL, NULL, NULL); This seems to work fine. The problem comes in the next web service call that tries to update the database. The call to sqlite3_step() always returns SQLITE_BUSY when called right after the above web service, but runs fine when called before the above web service. I am guessing that I am doing something wrong in the above transaction. Any thoughts? Sam ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Feasability of a Range function
>Ah. You want table-valued functions, like this: > >http://msdn.microsoft.com/en-us/library/ms191165.aspx Thanks Igor, that's what I had in mind. >In any case, SQLite doesn't support table-valued functions. The >closest thing to it is a virtual table: OK, got it, but this is a bit of heavy engine for such a marginal use. I'll do the simple way for now. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite compile error
Gary Zigmann a écrit : Hello, Since sqlite is a library, could you show us your main program, in order to help you ? Best wishes Noël Frankinet > Good Afternoon, > > Today I downloaded sqlite-amalgamation-3_6_22.zip from the > sqlite.org website because I was looking for a SQL database to > replace MS Access on my machine. I compiled sqlite3.c using a c > compiler and came up with this error: > > [Linker error] undefined reference to `winm...@16' > > Can you help me? > > Thanks, > > >> Gary Zigmann, MBA >> Clinical Data Analyst >> Gifford Medical Center >> gzigm...@giffordmed.org >> >> This information is confidential. If you are not the intended recipient, >> delete this e-mail and any attachments without forwarding it or retaining a >> copy. To try to obtain permission to use this transmission or to notify the >> sender of the error, call (802) 728-2135 or e-mail gzigm...@giffordmed.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] Feasability of a Range function
Jean-Christophe Deschamps wrote: >> Why not just >> >> select some_scalar_function(i) where i between 1 and 5; > > That's because we then get > No such column: i. Ah. You want table-valued functions, like this: http://msdn.microsoft.com/en-us/library/ms191165.aspx Those should appear where a table may - in your example, in a FROM clause. Having one in a WHERE clause makes no sense. WHERE clause never introduces a new identifier. In any case, SQLite doesn't support table-valued functions. The closest thing to it is a virtual table: http://www.sqlite.org/cvstrac/wiki?p=VirtualTables -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Feasability of a Range function
>Why not just > >select some_scalar_function(i) where i between 1 and 5; That's because we then get No such column: i. That was not very important. I would have the use for such possibility but I can live without. My question was just curiosity about whether something along the line could work, without any materialized table holding successive integers in the needed range. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Feasability of a Range function
Jean-Christophe Deschamps wrote: > I'm trying to determine if a Range(from, to) function can be made as an > extension function. > > Its effect would be to expand, for instance, range(1, 5) into (1, 2, 3, > 4, 5) for use in constructs similar to > select some_scalar_function(i) where i in range(1, 5); > without having to build a table holding integers 1..5 I don't see how SQLite would accept that as a valid syntax, no matter how you define the function. However, I don't understand the point of the exercise. Why not just select some_scalar_function(i) where i between 1 and 5; -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Feasability of a Range function
I'm trying to determine if a Range(from, to) function can be made as an extension function. Its effect would be to expand, for instance, range(1, 5) into (1, 2, 3, 4, 5) for use in constructs similar to select some_scalar_function(i) where i in range(1, 5); without having to build a table holding integers 1..5 I don't see how that's possible, but maybe someone with better internal knowledge can advise. Thank you. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Crash after add column
- Original Message >From: VasiliyF4 >After I try to ADD a column by run the querry "ALTER TABLE x >ADD y NUMERIC" from my application, I can't use the data base any more. If I >try to get or save any data at my DB it cause crash of the application. Of course your old INSERT statements will not work anymore, since INSERT statement requires knowledge of all columns in the table. __ Looking for the perfect gift? Give the gift of Flickr! http://www.flickr.com/gift/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Crash after add column
>I try to ADD a column by run the querry "ALTER TABLE x >ADD y NUMERIC" from my application, I can't use the data base any more. Try this: "ALTER TABLE main.x ADD y NUMERIC" and verify by external tool that column x has been added. __ Connect with friends from any web browser - no download required. Try the new Yahoo! Canada Messenger for the Web BETA at http://ca.messenger.yahoo.com/webmessengerpromo.php ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Running SQlite commands from perl 5.8.0
Hi, Have anyone managed to automate SQLite commands via perl 5.8.0? I've tried to install the DBD::SQLite module but it is supportd from 5.8.1 only. If any of you have any idea, I'd appreciate it. YG "This e-mail message may contain confidential, commercial or privileged information that constitutes proprietary information of Comverse Technology or its subsidiaries. If you are not the intended recipient of this message, you are hereby notified that any review, use or distribution of this information is absolutely prohibited and we request that you delete all copies and contact us by e-mailing to: secur...@comverse.com. Thank You." ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Crash after add column
On Sat, 2010-03-06 at 23:39 -0800, VasiliyF4 wrote: > I Use C/Linux. After I try to ADD a column by run the querry "ALTER TABLE x > ADD y NUMERIC" from my application, I can't use the data base any more. If I > try to get or save any data at my DB it cause crash of the application. Could you provide us with some reproducible steps? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users