Fri, 19 Oct 2012 13:16:31 -0700 от Pavel Ivanov <paiva...@gmail.com>:
>>> Var Alice := Db.SelectRecord('SELECT * FROM client WHERE client_name = ?',
>>> [ 'alice' ]);
>
>>
>
>> 1. If I drop record for "alice" from db and then access column data in
>> prepared stmt will it work OK?
>
>
>
> If the select statement wasn't reset or finalized, you won't be able to
> delete a record from the database (or, in WAL journaling mode, you would be
> able to, but the reader would still see original data).
>
>
Well, if he uses the same connection used to prepare the statement,
>
then he will be able to delete record even in normal journaling mode.
>
What happens in this case is undefined. It might be that column data
>
will be still accessbile, it might be you get some garbage, it might
>
be an access violation.
Using same connection means using same transaction, right?
Let's say app does the following (via the same connection):
1. BEGIN TRANSACTION;
2. prepare and step stmt A (SELECT * FROM foo WHERE rowid=1), A is not
finalized or reset;
3. execute stmt B (DELETE FROM foo WHERE rowid=1), B is prepared, executed and
finalized;
4. access column data via stmt A
5. COMMIT
A record with rowid=1 is actually deleted from db on step 5, right?
Does accessing record data via stmt A (step 4) "break any rules"?
>
>
>> 2. Can I drop client table while having such prepared stmt not closed?
>
>
I think table client cannot be dropped altogether until all statements
>
using it are reset/finalized.
>
>
>
Pavel
>
>
>
On Fri, Oct 19, 2012 at 12:49 PM, Igor Tandetnik <itandet...@mvps.org> wrote:
>
> Григорий Григоренко <grigore...@mail.ru> wrote:
>
>> I am using prepared stmts to cache selected record for later use. I have a
>> wrapper class, that has methods like AsString(name),
>
>> AsFloat(name) etc to extract data for a column name.
>
>>
>
>> I prepare, bind and then do Sqlite3_step() to get record. Later I use
>> sqlite3_column_XXX() to access data.
>
>>
>
>> My concern is - do open selective prepared stmts depend on something in db
>> or lock something in db?
>
>
>
> Yes, until you call sqlite3_reset or sqlite3_finalize. For as long as the
> statement is "active" (meaning, sqlite3_reset or sqlite3_finalize has not
> been called after the most recent sqlite3_step call), it holds a read
> transaction open.
>
>
>
>> Var Alice := Db.SelectRecord('SELECT * FROM client WHERE client_name = ?', [
>> 'alice' ]);
>
>>
>
>> 1. If I drop record for "alice" from db and then access column data in
>> prepared stmt will it work OK?
>
>
>
> If the select statement wasn't reset or finalized, you won't be able to
> delete a record from the database (or, in WAL journaling mode, you would be
> able to, but the reader would still see original data).
>
>
>
>> 2. Can I drop client table while having such prepared stmt not closed?
>
>
>
> What do you mean by "closed" here?
>
>
>
>> 3. If I have a lot of such stmts (100 or more) will it somehow affect
>> performance of SQLite or waste lots of memory?
>
>
>
> No, not really. There is a reasonably small data structure associated with a
> prepared statement, on the order of a few hundred bytes perhaps.
>
>
>
>> Are there some kind of cursors for each selecting prepared stmt?
>
>
>
> In a sense. After a call to sqlite3_step and before a call of sqlite3_reset
> or sqlite3_finalize, you might think of a statement handle as a form of a
> cursor. Each subsequent sqlite3_step call advances this cursor forward by one
> row.
>
>
>
>> 4. If there is a cursor, maybe there is a way to disconnect a cursor from
>> stmt keeping the single record still available?
>
>
>
> No, short of making a copy of every column's value.
>
> --
>
> Igor Tandetnik
>
>
>
> _______________________________________________
>
> 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
