[sqlite] sqlite3_step()
Using sqlite 3.6.22. Here's my sql SELECT sno, lname, fname FROM addressbook where sno>? Suppose your stepping through a 100 row result set. I'm calling sqlite3_step(), getting my values all good. If I were to call sqlite3_bind_int() function with a new value for my where clause at row 50, does that cause the step to re-execute the entire SQL? Do I have to do a reset, bind, step sequence ? When does sqlite3_step() evaluate the bound values in the where clause? Thank you,Mike ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLITE_BUSY
I have two threads in a Linux process using sqlite 3.6.12 in shared cache mode. One thread opens the database file in read only mode (sqlite3_open_v2()), sets to read uncommitted and only ever performs selects from the database. The other thread inserts, updates and deletes rows from the database. On occasion the return code of SQLITE_BUSY is returned from sqlite3_step(). Is this possible with the other thread in read only mode? I have a busy handler installed but I would like to understand what's going on here. TIA ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] database filename
I understand the pragma but was hoping for something like this const char *sqlite3_column_database_name(sqlite3_stmt*,int); but with the sqlite3* and not a statement? Thanks again --- On Sat, 10/18/08, Igor Tandetnik <[EMAIL PROTECTED]> wrote: From: Igor Tandetnik <[EMAIL PROTECTED]> Subject: Re: [sqlite] database filename To: sqlite-users@sqlite.org Date: Saturday, October 18, 2008, 3:56 PM "Mike Johnston" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > Given a sqlite3 * database handle, is there a way to retrieve the > filename used to open the database? Run this statement on your handle: PRAGMA database_list; Note that there may be more than one file associated with the handle (see ATTACH statement). Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] database filename
Given a sqlite3 * database handle, is there a way to retrieve the filename used to open the database? TIA __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] fetching and updating
OK, so that makes sense. What I see happening is creation of a journal file when i do the update/insert/delete statement each of which return SQLITE_OK. However, nothing is visible from an outside process (like sqlite3). If I understand you, this journal will be applied once the sqlite3_step finishes and I do the release on the prepared statement? Is this correct? Igor Tandetnik <[EMAIL PROTECTED]> wrote: "Mike Johnston" wrote in message news:[EMAIL PROTECTED] > I'm running the 3.5.7 version now. My understanding was that if I > have a prepared statement and currently using sqlite_step to walk > through the results, the database was unable to commit a write. You cannot commit a transaction, but you can make updates. They will be committed at the end of the transaction (possibly an implicit transaction started when SELECT statement was issued). The transaction is promoted from read-only to read-write under the usual rules. > In my > case, I would be using the same connection. Would you explain more? Which part? > Also, what if same connection but different thread (assuming sqlite > compiled with multi-thread option turned on). I'm not sure about that, but I suspect it will also work. Why don't you try it and see? Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users - Be a better friend, newshound, and know-it-all with Yahoo! Mobile. Try it now. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] fetching and updating
I'm running the 3.5.7 version now. My understanding was that if I have a prepared statement and currently using sqlite_step to walk through the results, the database was unable to commit a write. In my case, I would be using the same connection. Would you explain more? Also, what if same connection but different thread (assuming sqlite compiled with multi-thread option turned on). Many thanks Igor Tandetnik <[EMAIL PROTECTED]> wrote: "Mike Johnston" wrote in message news:[EMAIL PROTECTED] > I'm in a loop processing the result set using sqlite3_step. I need to > insert/update the same sqlite database but a different table. I > can't get the write lock while I have this active statement I'm using > sqlite3_step to move through. With a recent enough SQLite version, you can run modification statements while a select statement is in progress. You have to do it on the same connection. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users - Be a better friend, newshound, and know-it-all with Yahoo! Mobile. Try it now. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] fetching and updating in Ruby
Hi, Does anyone on the list use the sqlite3 gem with Ruby? Is this list ok to post sqlite/ruby questions? Many thanks in advance Mike - Be a better friend, newshound, and know-it-all with Yahoo! Mobile. Try it now. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] fetching and updating
Hi, I'm in a loop processing the result set using sqlite3_step. I need to insert/update the same sqlite database but a different table. I can't get the write lock while I have this active statement I'm using sqlite3_step to move through. I'm sure this is pretty common so what's the conventional wisdom for getting around this? Thanks Mike - Be a better friend, newshound, and know-it-all with Yahoo! Mobile. Try it now. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] last_insert_rowid
Hi, So I'm using the last_insert_rowid() with no issue. The note at http://www.sqlite.org/c3ref/last_insert_rowid.html states that the function returns unpredictable results should "a separate thread" do a new insert. Does that apply to a separate process as well? I have two processes collecting data from two separate sources happily inserting into a single sqlite database. Is there a possiblity last_insert_rowid() may return a bogus value in this context? (Both processes are single threaded). Thanks in advance, - Be a better friend, newshound, and know-it-all with Yahoo! Mobile. Try it now. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] monitoring inserts
It seems clunky no real specific reason. I tried it out today and it's quite fast and simple. I'm not sure I could say the same thing about a lot of IPC stuff. thanks - Boardwalk for $500? In 2007? Ha! Play Monopoly Here and Now (it's updated for today's economy) at Yahoo! Games.
[sqlite] Monitoring for inserts
What is the best way to get notified a table changed? I would like to monitor a table for inserts without resorting to polling. Anyway to have a trigger call a 'C' function or something? TIA, Mike - Yahoo! oneSearch: Finally, mobile search that gives answers, not web links.
Re: [sqlite] Proposed incompatible change to SQLite
Game on!! We'd love to see this happen. [EMAIL PROTECTED] wrote: We are considering a change to the SQLite API which is technically not backwards compatible. On the other hand, it is difficult to imagine a situation where the change would cause anyone any serious hardship. We suspect that most people will consider this change a vast improvement. The proposed changes is to these APIs: sqlite3_soft_heap_limit(); sqlite3_enable_shared_cache(); sqlite3_release_memory(); As currently implemented (and documented), these routines only work across database connections in the same thread. We propose to modify this so that these routines work across all database connections in the same process. If you think such a change will cause problems for you, please let me know. Tnx. -- D. Richard Hipp - To unsubscribe, send email to [EMAIL PROTECTED] - - Park yourself in front of a world of choices in alternative vehicles. Visit the Yahoo! Auto Green Center.
Re: [sqlite] Views
To clarify, if I have the three join SELECT statement in code or i have the same three join SELECT in a view, once I do the prepare it should pretty much be same as in my case they are simple one-to-one joins returning 0 or 1 row max. Thanks Mike Joe Wilson <[EMAIL PROTECTED]> wrote: --- Mario Figueiredo wrote: > On 7/6/07, Joe Wilson wrote: > > --- Mike Johnston wrote: > > > I have to join three tables to retrieve bits of data from each. I'm > > > wondering if I use a > view > > > are there any performance issues vs. issuing the complete 3 table join > > > query in code. > > > > As long as your VIEW/subquery does not make use of > > UNION/EXCEPT/INTERSECT, then it's usually the same speed. > > > > I would not recommend complex querying of views/subquery of unions > > of selects with large result sets within SQLite. The select will run > > much faster and use far less memory if you manually expand these > > queries to not make use of the VIEW. SQLite will not perform this > > optimization for you. > > Unless performance issues are really at a premium, I would probably > advise exactly the opposite. In other words, use views if you can, > don't use them only when you must not. No one is disputing views are useful. I just wished they were faster in SQLite and used less memory on large datasets in compound queries, so you don't have to manually rewrite queries to get good performance: http://www.sqlite.org/cvstrac/tktview?tn=1924 Fussy? Opinionated? Impossible to please? Perfect. Join Yahoo!'s user panel and lay it on us. http://surveylink.yahoo.com/gmrs/yahoo_panel_invite.asp?a=7 - To unsubscribe, send email to [EMAIL PROTECTED] - - Be a PS3 game guru. Get your game face on with the latest PS3 news and previews at Yahoo! Games.
[sqlite] Views
n Fri, 2007-07-06 at 07:18 -0700, Mike Johnston wrote: > I have to join three tables to retrieve bits of data from each. I'm wondering if I use a view are there any performance issues vs. issuing the complete 3 table join query in code. > > Also, is it quicker to have sqlite do joins on primary keys between tables instead of doing three separate single table queries on the primary key in code? >The short answers are probably not and no. >But it depends on the exact view you're defining. If it's just a join, >no GROUP BY or ORDER BY or aggregate functions, it should be fine. >Dan. They are very simple select statements retrieving exactly one row. On the second point, are you saying that doing a select with a three table join is no quicker than having three groups of sqlite_prepare, sqlite_step sequences for single table, single row lookups? That just seems a little counter-intuitive (at least to me). Mike - Choose the right car based on your needs. Check out Yahoo! Autos new Car Finder tool.
[sqlite] Views
I have to join three tables to retrieve bits of data from each. I'm wondering if I use a view are there any performance issues vs. issuing the complete 3 table join query in code. Also, is it quicker to have sqlite do joins on primary keys between tables instead of doing three separate single table queries on the primary key in code? Thanks Mike - Park yourself in front of a world of choices in alternative vehicles. Visit the Yahoo! Auto Green Center.
Re: [sqlite] Re: sqlite3_column_xxx question
Absolutely but it seems like duplicate effort if sqlite already has the info. Any guess if this is a big deal to put into the source? Igor Tandetnik <[EMAIL PROTECTED]> wrote: Mike Johnston wrote: > While using sqlite3_step(), is there a clean easy way to retrieve the > data by column name instead of by ordinal? I know I can convert an > ordinal to a column name but not the other way around. You can enumerate all columns, retrieve the name of each, and find the one you want. You only need to do this once after the statement is prepared: after that, you can use the ordinal you've found. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] - - Sick sense of humor? Visit Yahoo! TV's Comedy with an Edge to see what's on, when.
[sqlite] sqlite3_column_xxx question
While using sqlite3_step(), is there a clean easy way to retrieve the data by column name instead of by ordinal? I know I can convert an ordinal to a column name but not the other way around. TIA, Mike - Take the Internet to Go: Yahoo!Go puts the Internet in your pocket: mail, news, photos & more.
[sqlite] Column order in resultset
When doing a "select * from foo" and using sqlite3_step() with the associated sqlite3_column_xx funcs, is the order of the columns in the resultset deterministic? What is the rule that defines what the order? it seems to be the order of the create table statement but I can't seem to find where it is written a "select *"must follow that order. TIA - No need to miss a message. Get email on-the-go with Yahoo! Mail for Mobile. Get started.
[sqlite] Select columns & performance
Are there any significant performance benefits by limiting the number of columns in a single table select statement? Does joining (<5 tables) make a significant difference to that answer? TIA - TV dinner still cooling? Check out "Tonight's Picks" on Yahoo! TV.
[sqlite] sqlite3_create_module()
Hi, The docs for "CREATE VIRTUAL TABLE" refer to this function (in subject line) but I can't seem to see find it anywhere. I see the full text search uses modules but some docs would be helpful on how to create my own module. TIA, Mike - Bored stiff? Loosen up... Download and play hundreds of games for free on Yahoo! Games.
[sqlite] in memory databases
Hi, Is it possible to have memory databases located at a specific memory address? I have a battery backed memory I'd like to store specific information apart from the main database. Thanks, Mike - No need to miss a message. Get email on-the-go with Yahoo! Mail for Mobile. Get started.
