Predictability is ensured by using transactions. By using BEGIN and
COMMIT to make transactions atomic you enforce a predictable state.
B V, Phanisekhar wrote:
Thanks Igor,
Why it's unpredictable?
Why can't the unpredictable be made predictable?
Assume I update the column of a row that meets the criteria of some
select stmt and I am yet to encounter that row in sqlite3_step. So the
sqlite3_step on that row will return the old column value or new column
value for that row?
E.g. a query with ORDER BY on a table that
doesn't have a suitable index has no choice but to retrieve all
records
In case of complex queries for e.g. ORDER BY (as mentioned by you), I
assume I will not be able to see the updated column value. Doesn't u
think there is an inconsistency?
Also, is there a better function to retrieve all (rows) of the results
in one call? Sqlite3_get_table does that, but it uses internally
sqlite3_step, which takes lot of time. So is there a way that I can get
all the result rows in one step rather then stepping for each row.
Regards,
Phani
-----Original Message-----
From: Igor Tandetnik [mailto:[EMAIL PROTECTED]
Sent: Thursday, June 07, 2007 5:28 PM
To: SQLite
Subject: [sqlite] Re: Re: Does sqlite3_step searches for a row in the
table / or some results buffer?
B V, Phanisekhar <[EMAIL PROTECTED]> wrote:
Why it's unpredictable?
You are enumerating rows in some order. A new row may be inserted before
or after your current position in this order. If it is inserted before,
you will not see it in this enumeration. If it is inserted after, you
will eventually reach it.
With simple enough queries, you may guess (or explicitly specify wit
ORDER BY) the order in which rows are enumerated, and can predict
whether a newly inserted order will be seen. With complex queries, it
may be difficult to make such a prediction.
In sqlite 3.3.8, since it allows INSERT while SELECT statement is
running, I assume that it will return an 11th row. Can you explain how
step operation works interiorly on a table?
It walks a B-Tree, moving from current node to the next.
(Does it gets all the
results at one time
Not most of the time, but some queries require such a temporary
intermediate resultset. E.g. a query with ORDER BY on a table that
doesn't have a suitable index has no choice but to retrieve all records,
sort them in a temporary resultset, then enumerate that resultset. In
this case, by the way, a row inserted into the table in the middle of
enumeration will not be seen.
or it searches for the rows matching the criteria one after the other
SQLite tries hard to do it this way, but for some queries it is not
possible.
Igor Tandetnik
------------------------------------------------------------------------
-----
To unsubscribe, send email to [EMAIL PROTECTED]
------------------------------------------------------------------------
-----
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
