Re: [sqlite] Does changing the db change result sets?
Roger Binns wrote:
>
> The cursor object in apsw wraps a prepared statement. Since the cursor
> gets reused the earlier results are no longer available:
>
> cursor.execute("select * from numbers")
> ...
> cursor.execute("delete from numbers where no=5")
>
OK, that makes sense.
>
> Alternatively the "delete" statement can be done with a different cursor
> so that the earlier one is not clobbered:
>
> cursor2=conn.cursor()
> cursor2.execute("delete from numbers where no=5")
>
This is effectively what the sqlite3_exec(db, "delete") call does
internally in the C API program I posted.
Dennis Cote
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Does changing the db change result sets?
Nikolaus Rath wrote:
>
> So as soon as I make a change in the database, I do not get any more
> results. If I understood you correctly, I should have gotten (4,)
> instead.
>
> Is this a bug?
>
It is probably a bug (or a design decision) in the apsw wrapper. The
following program produces the expected output using the C API functions.
#include
#include
#include
using namespace std;
int main(int argc, char *argv[])
{
sqlite3* db;
sqlite3_open("test.db3", );
sqlite3_exec(db, "create table numbers (n integer)", 0, 0, 0);
char sql[100];
for (int i = 1; i <=10; ++i) {
sprintf(sql, "insert into numbers values(%d)", i);
sqlite3_exec(db, sql, 0, 0, 0);
}
sqlite3_stmt* s;
sqlite3_prepare(db, "select * from numbers", -1, , 0);
sqlite3_step(s);
printf("%d\n", sqlite3_column_int(s, 0));
sqlite3_step(s);
printf("%d\n", sqlite3_column_int(s, 0));
sqlite3_step(s);
printf("%d\n", sqlite3_column_int(s, 0));
sqlite3_exec(db, "delete from numbers where n = 5", 0, 0, 0);
sqlite3_step(s);
printf("%d\n", sqlite3_column_int(s, 0));
sqlite3_step(s);
printf("%d\n", sqlite3_column_int(s, 0));
sqlite3_step(s);
printf("%d\n", sqlite3_column_int(s, 0));
sqlite3_finalize(s);
sqlite3_close(db);
system("PAUSE");
return EXIT_SUCCESS;
}
This produces:
1
2
3
4
6
7
Press any key to continue . . .
HTH
Dennis Cote
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Does changing the db change result sets?
"Igor Tandetnik" <[EMAIL PROTECTED]> writes:
> "Nikolaus Rath" <[EMAIL PROTECTED]> wrote in
> message news:[EMAIL PROTECTED]
>> What happens if
>>
>> 1. I prepare and execute a select statement
>> 2. I retrieve a couple of rows
>> 3. I execute a new query that would change the result of the
>>query in 1
>
> On the same connection, I assume.
Yes.
>> 4. I continue to retrieve the results of 1)
>>
>> Will I get the results as if step 3 hasn't happened, is the result
>> undefined, or will my result set somehow be updated?
>
> You may or may not see the changes introduced by step 3. E.g. if
> step 3 updates a record that you've already visited, you naturally
> won't visit it again and thus won't see the changes. But if it
> updates a record you haven't visited yet, when you get to it you
> will see the new data.
This doesn't seem right when I test it. "numbers" is a table with one
column containing the numbers 1 to 10. When using the python
interface:
>>> import apsw
>>> conn=apsw.Connection("data")
>>> cursor = conn.cursor()
>>> res = cursor.execute("select * from numbers")
>>> res.next()
(1,)
>>> res.next()
(2,)
>>> res.next()
(3,)
>>> cursor.execute("delete from numbers where no=5")
>>> res.next()
Traceback (most recent call last):
File "", line 1, in
StopIteration
So as soon as I make a change in the database, I do not get any more
results. If I understood you correctly, I should have gotten (4,)
instead.
Is this a bug?
Best,
-Nikolaus
--
»It is not worth an intelligent man's time to be in the majority.
By definition, there are already enough people to do that.«
-J.H. Hardy
PGP fingerprint: 5B93 61F8 4EA2 E279 ABF6 02CF A9AD B7F8 AE4E 425C
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Does changing the db change result sets?
"Stephen Woodbridge" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > Igor Tandetnik wrote: >> "Nikolaus Rath" <[EMAIL PROTECTED]> wrote in >> message news:[EMAIL PROTECTED] >>> What happens if >>> >>> 1. I prepare and execute a select statement >>> 2. I retrieve a couple of rows >>> 3. I execute a new query that would change the result of the >>>query in 1 >> >> On the same connection, I assume. >> >>> 4. I continue to retrieve the results of 1) >>> >>> Will I get the results as if step 3 hasn't happened, is the result >>> undefined, or will my result set somehow be updated? >> >> You may or may not see the changes introduced by step 3. E.g. if >> step 3 updates a record that you've already visited, you naturally >> won't visit it again and thus won't see the changes. But if it >> updates a record you haven't visited yet, when you get to it you >> will see the new data. > > Hmmm, that does not seem right, but what do I know. The reason I say > this is because if the update change a column value on a record the > would cause it to ne not selected or it changes some other that had > not been selected before but would be after the change I'm not sure > how these would magically get included or excluded. SQLite doesn't prepare the whole resultset up front, but retrieves records on demand - one record for every sqlite3_step call. If the record matches the conditions by the time the statement gets to it, it will be returned. If not, it will be skipped. > I assume the when you make a query your results on in a set of data > the is private to your session/connection and that other transactions > on the database can not change you private data. ... but the same connection within the same transaction can. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Does changing the db change result sets?
Igor Tandetnik wrote: > "Nikolaus Rath" <[EMAIL PROTECTED]> wrote in > message news:[EMAIL PROTECTED] >> What happens if >> >> 1. I prepare and execute a select statement >> 2. I retrieve a couple of rows >> 3. I execute a new query that would change the result of the >>query in 1 > > On the same connection, I assume. > >> 4. I continue to retrieve the results of 1) >> >> Will I get the results as if step 3 hasn't happened, is the result >> undefined, or will my result set somehow be updated? > > You may or may not see the changes introduced by step 3. E.g. if step 3 > updates a record that you've already visited, you naturally won't visit > it again and thus won't see the changes. But if it updates a record you > haven't visited yet, when you get to it you will see the new data. Hmmm, that does not seem right, but what do I know. The reason I say this is because if the update change a column value on a record the would cause it to ne not selected or it changes some other that had not been selected before but would be after the change I'm not sure how these would magically get included or excluded. I assume the when you make a query your results on in a set of data the is private to your session/connection and that other transactions on the database can not change you private data. But I'm not an expert on this stuff. -Steve ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Does changing the db change result sets?
"Nikolaus Rath" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > What happens if > > 1. I prepare and execute a select statement > 2. I retrieve a couple of rows > 3. I execute a new query that would change the result of the >query in 1 On the same connection, I assume. > 4. I continue to retrieve the results of 1) > > Will I get the results as if step 3 hasn't happened, is the result > undefined, or will my result set somehow be updated? You may or may not see the changes introduced by step 3. E.g. if step 3 updates a record that you've already visited, you naturally won't visit it again and thus won't see the changes. But if it updates a record you haven't visited yet, when you get to it you will see the new data. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Does changing the db change result sets?
Hello, What happens if 1. I prepare and execute a select statement 2. I retrieve a couple of rows 3. I execute a new query that would change the result of the query in 1 4. I continue to retrieve the results of 1) Will I get the results as if step 3 hasn't happened, is the result undefined, or will my result set somehow be updated? Thanks in advance, -Nikolaus -- »It is not worth an intelligent man's time to be in the majority. By definition, there are already enough people to do that.« -J.H. Hardy PGP fingerprint: 5B93 61F8 4EA2 E279 ABF6 02CF A9AD B7F8 AE4E 425C ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
