[sqlite] Rollback of nested savepoints with System.Data.SQLite
Hello, I'm using System.Data.SQLite 1.0.82.0 and can't get nested savepoint work in my application. Rollbacks to the nested savepoint do not have any effects, only rollbacks to the outermost savepoint undo my changes. According to the SQLite documentation this should work and I verified that with the Qt-based SQLite Database Browser. Here is the connection trace, the select statements are executed with SQLiteCommand.ExecuteScalar and the other statements with ExecuteNonQuery: BEGIN IMMEDIATE SAVEPOINT SP1 CREATE TABLE a(key number primary key); SAVEPOINT SP2 INSERT INTO a VALUES(666); SELECT count(1) FROM a Expected: 1, Got: 1 SAVEPOINT SP3 INSERT INTO a VALUES(13); SELECT count(1) FROM a Expected: 2, Got: 2 ROLLBACK TRANSACTION TO SP3 SELECT count(1) FROM a Expected: 1, Got: 2 If I rollback to SP1 instead the last select throws as expected an exception, since the table vanished. Has anyone an idea, what I'm doeing wrong or is this a bug? Thanks in advance, Sven ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] System.Data.SQLite and VS2012 RTM designer support
Browser IA wrote: > > Does System.Data.SQLite support Visual Studio 2012 Ultimate RTM? > The latest trunk code now supports Visual Studio 2012 and the .NET Framework 4.5. The changes required to make this work were fairly extensive. This support will be present in the next release, 1.0.83.0, currently scheduled for early November. Alternatively, if you cannot wait until then, the current trunk code binaries and setups can be built using the steps described here: https://system.data.sqlite.org/index.html/doc/trunk/www/build.wiki https://system.data.sqlite.org/index.html/doc/trunk/www/release.wiki Please let us know if this works for you. -- Joe Mistachkin ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Count(*) help
> PS Note that it is count(X) and not count("X")
Count(X) and Count("X") are identical if X is a column in the table (or join)
from which you are counting, and X does not contain embeded spaces, special
characters, or other inanities. count(*) counts rows, and count('X') or
count(1) [or any other constant in place of 1] will also return the row count
(because you have provided a non-null value to be counted for each row).
In fact, count(expression) is exactly equivalent to sum(1) where expression is
not null, unless expression includes the keyword distinct.
---
() ascii ribbon campaign against html e-mail
/\ www.asciiribbon.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Count(*) help
On 9/16/2012 9:17 AM, John Clegg wrote:
I have a table Members with 896 rows and a text field "Year2012". It
contains "Paid" 156 times, "Comp" 13 times and the rest are null (confirmed
in sqlitebrowser as "empty")
Back in the olden days when this table was in Access, select
count("Year2013") from Members used to return 169. In LibreOfiice with the
data stored in embedded HSQL it returns 169. In LibreOffice connecting to
sqlite3 it returns 896.
Any ideas please?
Read the documentation? http://sqlite.org/lang_aggfunc.html
"The count(X) function returns a count of the number of times that /X/
is not NULL in a group.
The count(*) function (with no arguments) returns the total number of
rows in the group. "
HTH,
Gerry
PS Note that it is count(X) and not count("X")
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Count(*) help
The Year2012 field is not null, it is an empty string (that is, '', which is
not NULL).
---
() ascii ribbon campaign against html e-mail
/\ www.asciiribbon.org
> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of John Clegg
> Sent: Sunday, 16 September, 2012 10:18
> To: sqlite-users@sqlite.org
> Subject: [sqlite] Count(*) help
>
> I have a table Members with 896 rows and a text field "Year2012". It
> contains "Paid" 156 times, "Comp" 13 times and the rest are null (confirmed
> in sqlitebrowser as "empty")
>
> Back in the olden days when this table was in Access, select
> count("Year2013") from Members used to return 169. In LibreOfiice with the
> data stored in embedded HSQL it returns 169. In LibreOffice connecting to
> sqlite3 it returns 896.
>
> Any ideas please?
> ___
> 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] Count(*) help
On 16-09-2012 18:21, Marcus Ilgner wrote:
On So, 2012-09-16 at 17:17 +0100, John Clegg wrote:
I have a table Members with 896 rows and a text field "Year2012". It
contains "Paid" 156 times, "Comp" 13 times and the rest are null (confirmed
in sqlitebrowser as "empty")
Back in the olden days when this table was in Access, select
count("Year2013") from Members used to return 169. In LibreOfiice with the
data stored in embedded HSQL it returns 169. In LibreOffice connecting to
sqlite3 it returns 896.
Any ideas please?
You're probably looking for something like
SELECT COUNT(*) FROM Members WHERE Year2012='Paid'
or maybe even
SELECT Year2012, COUNT(*) FROM Members GROUP BY Year2012
or:
SELECT count(*) FROM Members where Year2012 IS NOT NULL;
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Count(*) help
John Clegg wrote:
> I have a table Members with 896 rows and a text field "Year2012". It
> contains "Paid" 156 times, "Comp" 13 times and the rest are null (confirmed
> in sqlitebrowser as "empty")
>
> Back in the olden days when this table was in Access, select
> count("Year2013") from Members used to return 169. In LibreOfiice with the
> data stored in embedded HSQL it returns 169. In LibreOffice connecting to
> sqlite3 it returns 896.
sqlite> create table Members(Year2012);
sqlite> insert into Members values('Paid');
sqlite> insert into Members values('Comp');
sqlite> insert into Members values(null);
sqlite> select count(*) from Members;
3
sqlite> select count(Year2012) from Members;
2
sqlite> select count("Year2012") from Members;
2
sqlite> select count('Year2012') from Members;
3
What is the exact query you're using in LibreOffice?
Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Count(*) help
Hi John,
Funny seeing you here on the SQLite forum.
Are these by any chance the ISUG members?
Doing a count without a WHERE clause is always likely to give different results
with the various SQL implications as far as I know.
Why not add a WHERE?
RBS
On Sun, Sep 16, 2012 at 5:17 PM, John Clegg wrote:
> I have a table Members with 896 rows and a text field "Year2012". It
> contains "Paid" 156 times, "Comp" 13 times and the rest are null (confirmed
> in sqlitebrowser as "empty")
>
> Back in the olden days when this table was in Access, select
> count("Year2013") from Members used to return 169. In LibreOfiice with the
> data stored in embedded HSQL it returns 169. In LibreOffice connecting to
> sqlite3 it returns 896.
>
> Any ideas please?
> ___
> 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] Count(*) help
On So, 2012-09-16 at 17:17 +0100, John Clegg wrote:
> I have a table Members with 896 rows and a text field "Year2012". It
> contains "Paid" 156 times, "Comp" 13 times and the rest are null (confirmed
> in sqlitebrowser as "empty")
>
> Back in the olden days when this table was in Access, select
> count("Year2013") from Members used to return 169. In LibreOfiice with the
> data stored in embedded HSQL it returns 169. In LibreOffice connecting to
> sqlite3 it returns 896.
>
> Any ideas please?
You're probably looking for something like
SELECT COUNT(*) FROM Members WHERE Year2012='Paid'
or maybe even
SELECT Year2012, COUNT(*) FROM Members GROUP BY Year2012
All the best
Marcus Ilgner
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Count(*) help
I have a table Members with 896 rows and a text field "Year2012". It
contains "Paid" 156 times, "Comp" 13 times and the rest are null (confirmed
in sqlitebrowser as "empty")
Back in the olden days when this table was in Access, select
count("Year2013") from Members used to return 169. In LibreOfiice with the
data stored in embedded HSQL it returns 169. In LibreOffice connecting to
sqlite3 it returns 896.
Any ideas please?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
