Re: [sqlite] A question about prepared statements
Григорий Григоренко wrote: > 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 http://www.sqlite.org/c3ref/column_blob.html If the SQL statement does not currently point to a valid row... the result is undefined. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] A question about prepared statements
Fri, 19 Oct 2012 13:16:31 -0700 от Pavel Ivanov :
>>> 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 wrote:
>
> Григорий Григоренко 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
Re: [sqlite] A question about prepared statements
Fri, 19 Oct 2012 15:49:07 -0400 от "Igor Tandetnik" :
>Григорий Григоренко 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.
OK.
>
>
> 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).
What kind of error is returned in this case?
Actually, I should (and would) find out this via simple test app )
>
>
> 2. Can I drop client table while having such prepared stmt not closed?
>
>
What do you mean by "closed" here?
Finalized.
>
>
> 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.
OK.
>
--
>
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
Re: [sqlite] A question about prepared statements
>> 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.
>> 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 wrote:
> Григорий Григоренко 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
Re: [sqlite] A question about prepared statements
Григорий Григоренко 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] A question about prepared statements
Hello,
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?
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?
2. Can I drop client table while having such prepared stmt not closed? Will I
be able to access data even if table was dropped?
3. If I have a lot of such stmts (100 or more) will it somehow affect
performance of SQLite or waste lots of memory? Are there some kind of cursors
for each selecting prepared stmt?
4. If there is a cursor, maybe there is a way to disconnect a cursor from stmt
keeping the single record still available? Its always about a single record. Is
there anything to ease the burden of stopped selective stmt but not closing it?
5. Other disadvantages I might have missed?
A countrary to this is getting all the data from record to some user class and
closing stmt. i dont like overhead of it.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
