[sqlite] sqlite3_step()

2010-01-16 Thread Mike Johnston
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

2009-12-01 Thread Mike Johnston
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

2008-10-18 Thread Mike Johnston
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

2008-10-18 Thread Mike Johnston
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

2008-04-21 Thread Mike Johnston
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

2008-04-21 Thread Mike Johnston
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

2008-04-18 Thread Mike Johnston
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] last_insert_rowid

2008-03-27 Thread Mike Johnston
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

2007-09-11 Thread Mike Johnston
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

2007-09-11 Thread Mike Johnston
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

2007-08-09 Thread Mike Johnston
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

2007-07-09 Thread Mike Johnston
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

2007-07-06 Thread Mike Johnston

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

2007-07-06 Thread Mike Johnston
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

2007-05-15 Thread Mike Johnston
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

2007-05-15 Thread Mike Johnston
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

2007-05-12 Thread Mike Johnston
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

2007-04-11 Thread Mike Johnston
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()

2007-04-09 Thread Mike Johnston
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

2007-04-06 Thread Mike Johnston

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.