[sqlite] System.Data.SQLite: Incompatible file locking behavior starting version 1.0.82.0
It seems that in version 1.0.81.0 and earlier the database file was released
immediately after all SQLiteConnection objects were disposed even if some
dependent SQLiteCommand and/or SQLiteDataReader objects were not yet disposed
or closed.
However starting version 1.0.82.0 the file remains locked. Consider this code:
var file = Path.GetTempFileName();
using (var connection = new SQLiteConnection(Data Source= + file))
{
Console.WriteLine(connection.GetType().Assembly.FullName);
connection.Open();
var command = connection.CreateCommand();
command.CommandText = CREATE TABLE t(a);
command.ExecuteNonQuery();
}
// the following line succeeds in v1.0.81.0 and earlier but
// FAILS on v1.0.82.0 and later with file still in use error
File.Delete(file);
Was this change made by design?
Thanks,
- Levi
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] System.Data.SQLite: Incompatible file locking behavior starting version 1.0.82.0
Thank you Joe,
What was the motivation for this change (it seems to be quite inconvenient in
my case)?
- Levi
- Original Message -
From: sql...@mistachkin.com
To: sqlite-users@sqlite.org
Cc: Levi Haskell (BLOOMBERG/ 731 LEXIN)
At: Apr 25 2013 15:07:54
As of release 1.0.82.0, the object disposal semantics were changed to keep
the underlying connection around until all associated System.Data.SQLite
objects have been properly disposed.
Levi Haskell wrote:
var file = Path.GetTempFileName();
using (var connection = new SQLiteConnection(Data Source= + file))
{
Console.WriteLine(connection.GetType().Assembly.FullName);
connection.Open();
var command = connection.CreateCommand();
command.CommandText = CREATE TABLE t(a);
command.ExecuteNonQuery();
}
// the following line succeeds in v1.0.81.0 and earlier but
// FAILS on v1.0.82.0 and later with file still in use error
File.Delete(file);
In the above example, adding command.Dispose(); just prior to the end of
the using block should allow the file to be deleted.
Was this change made by design?
Yes.
--
Joe Mistachkin
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] System.Data.SQLite: Incompatible file locking behavior starting version 1.0.82.0
Is this what the INTEROP_LEGACY_CLOSE compile-time option refers to? *Use the legacy connection closing algorithm when built with the INTEROP_LEGACY_CLOSE compile-time option. Thanks, - Levi - Original Message - From: sql...@mistachkin.com To: sqlite-users@sqlite.org Cc: Levi Haskell (BLOOMBERG/ 731 LEXIN) At: Apr 25 2013 16:38:27 Levi Haskell wrote: gt; gt; What was the motivation for this change (it seems to be quite inconvenient gt; in my case)? gt; The previous method used to deal with the non-deterministic finalization order imposed by the CLR did not work reliably in all circumstances, did not follow best-practices for the IDisposable interface, and relied heavily upon internal semantics of the native SQLite core library. -- Joe Mistachkin ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite version 3.7.16
Hi Richard, Downloaded the 3.7.16. I still don't see any mentioning of the optional table argument to the foreign_key_check pragma in: http://www.sqlite.org/pragma.html#pragma_foreign_key_check Is this feature officially unsupported? Thanks, - Levi ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite 3.7.16 beta
Hi Richard, I don't see any mentioning of the optional table argument to the foreign_key_check pragma in: http://www.sqlite.org/draft/pragma.html#pragma_foreign_key_check For example: SQLite version 3.7.16 2013-03-02 03:25:55 Enter .help for instructions Enter SQL statements terminated with a ; sqlite create table p(k integer primary key); sqlite create table c(k int references p); sqlite insert into c values (1),(2),(3); sqlite pragma foreign_key_check(c); c|1|p|0 c|2|p|0 c|3|p|0 sqlite pragma foreign_key_check(p); sqlite Thanks, - Levi - Original Message - From: sqlite-users@sqlite.org To: sqlite-...@sqlite.org, sqlite-users@sqlite.org At: Mar 2 2013 11:30:23 The anticipated release date for SQLite 3.7.16 is currently 2013-03-12. Additional information about the forthcoming 3.7.16 release: (1) http://www.sqlite.org/draft/releaselog/3_7_16.html (2) http://www.sqlite.org/draft/download.html (3) http://www.sqlite.org/checklists/3071600 See a summary of changes at (1). Download a snapshot of the latest code (in the form of an amalgamation sqlite3.c source file) from (2). The status board at (3) will show the current state of release testing. When the status board goes all green, we will cut the release. The links above, and indeed the entire http://www.sqlite.org/draft/ sub-website, will be updated periodically between now and the official release so check back frequently. Please report any problems encountered. -- D. Richard Hipp d...@sqlite.org ___ 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] How to verify referential integrity of SQLite database
Perfect! Exactly what I needed
Thanks a lot,
- Levi
- Original Message -
From: d...@sqlite.org
To: LEVI HASKELL (BLOOMBERG/ 731 LEXIN)
Cc: sqlite-users@sqlite.org
At: Jan 1 2013 09:03:00
On Thu, Dec 27, 2012 at 11:43 AM, Levi Haskell (BLOOMBERG/ 731 LEXIN)
lhask...@bloomberg.net wrote:
Hi Richard,
Sorry, I missed your message before.
Look, in my example:
SQLite version 3.7.16 2012-12-20 01:15:20
Enter .help for instructions
Enter SQL statements terminated with a ;
sqlite create table p(a, b, primary key(a, b), unique(b, a));
sqlite create table c(x, y, foreign key(x, y) references p);
sqlite insert into p values (1, 2);
sqlite insert into c values (1, 2), (2, 1);
sqlite pragma foreign_key_check(c);
c|2|p|0
sqlite pragma foreign_key_list(c);
0|0|p|x||NO ACTION|NO ACTION|NONE
0|1|p|y||NO ACTION|NO ACTION|NONE
sqlite pragma table_info(p);
0|a||0||1
1|b||0||1
sqlite pragma index_list(p);
0|sqlite_autoindex_p_2|1
1|sqlite_autoindex_p_1|1
No pragma will give me enough info to tell whether fkey implies that c.x = p.a
and c.y = p.b *or* c.x = p.b and c.y = p.a.
Am I missing something?
In the latest code on the SQLite trunk, the table_info pragma has been enhanced
so that the pk column (the right-most column) gives the order of the columns
in the primary key.
Thanks,
- Levi
- Original Message -
From: d...@sqlite.org
To: LEVI HASKELL (BLOOMBERG/ 731 LEXIN)
Cc: sqlite-users@sqlite.org
At: Dec 20 2012 23:05:20
On Thu, Dec 20, 2012 at 3:05 PM, Levi Haskell (BLOOMBERG/ 731 LEXIN)
lhask...@bloomberg.net wrote:
Tested, works perfectly. Thanks!
Two questions/observation:
1. Should there be a way to determine the parent key w/o looking at or parsing
the schema DDL commands?
For example:
SQLite version 3.7.16 2012-12-20 01:15:20
Enter .help for instructions
Enter SQL statements terminated with a ;
sqlite create table p(a, b, primary key(a, b), unique(b, a));sqlite create
table c(x, y, foreign key(x, y) references p);
sqlite insert into p values (1, 2);
sqlite insert into c values (1, 2), (2, 1);
sqlite pragma foreign_key_check(c);
c|2|p|0
The fourth column is the foreign_key_id. If you look at the output of PRAGMA
foreign_key_list(c), you'll find all the information about parent table and the
columns that map between parent and child, for that id.
Now I know that the second record is in violation but I don't know what
key/index the foreign key actually refers to (and no other combination of
existing pragmas will tell me).
2. While I do like your API far better than what I originally proposed, I found
that returning no result in case of success may lead to confusion since unknown
pragmas behave the same way. So if I run pragma foreign_key_check; and get
empty result it can mean any of the following:
1. There are no foreign key violations - good!
2. My version of SQLite does not support this pragma yet
3. (In case of using the shell) I made a typo in the pragma name
The pragma throws an error if you enter the name of a table that does not
exist. That handles case 3. To verify 2, that the version of SQLite you are
using support foreign_key_check, simply use the name of a table that does not
exist and verify that you get an error back:
PRAGMA foreign_key_check('no-such-table'); --- expect an error
While I don't have a better suggestion now, I just wanted to point it out to
you.
Again thanks a lot!
- Levi
- Original Message -
From: d...@sqlite.org
To: LEVI HASKELL (BLOOMBERG/ 731 LEXIN), sqlite-users@sqlite.org
At: Dec 19 2012 21:10:52
On Wed, Dec 12, 2012 at 2:29 PM, Levi Haskell (BLOOMBERG/ 731 LEXIN)
lhask...@bloomberg.net wrote:
My suggestion would be to have check_integrity command verify referential
integrity as well only if it's executed while the foreign key enforcement is
enabled on the connection.
The latest SQLite from trunk (not the 3.7.15.1 patch release, but the code that
is destined to become 3.7.16) has a new pragma:
PRAGMA foreign_key_check;
PRAGMA foreign_key_check(TABLE);
The second from checks all of the REFERENCES clauses in TABLE. The first form
checks the keys on all tables in the database.
The result of the pragma is a table, with one row per mismatched key. The row
contains the name of the child table, the rowid of the child table, the name of
the parent table, and the foreign key index which is an integer that
describes the foreign key in PRAGMA foreign_key_list(CHILD). If the
foreign_key_check pragma returns an empty set, that means that all of the keys
are correct.
PRAGMA foreign_key_check works regardless of whether or not foreign keys are
currently enabled or disabled.
-- D. Richard Hipp
d...@sqlite.org
--
D. Richard Hipp
d...@sqlite.org
--
D. Richard Hipp
d...@sqlite.org
Re: [sqlite] How to verify referential integrity of SQLite database
Hi Richard,
Sorry, I missed your message before.
Look, in my example:
SQLite version 3.7.16 2012-12-20 01:15:20Enter .help for instructions
Enter SQL statements terminated with a ;
sqlite create table p(a, b, primary key(a, b), unique(b, a));
sqlite create table c(x, y, foreign key(x, y) references p);
sqlite insert into p values (1, 2);
sqlite insert into c values (1, 2), (2, 1);
sqlite pragma foreign_key_check(c);
c|2|p|0
sqlite pragma foreign_key_list(c);
0|0|p|x||NO ACTION|NO ACTION|NONE
0|1|p|y||NO ACTION|NO ACTION|NONE
sqlite pragma table_info(p);
0|a||0||1
1|b||0||1
sqlite pragma index_list(p);
0|sqlite_autoindex_p_2|1
1|sqlite_autoindex_p_1|1
No pragma will give me enough info to tell whether fkey implies that c.x = p.a
and c.y = p.b *or* c.x = p.b and c.y = p.a.
Am I missing something?
Thanks,
- Levi
- Original Message -
From: d...@sqlite.org
To: LEVI HASKELL (BLOOMBERG/ 731 LEXIN)
Cc: sqlite-users@sqlite.org
At: Dec 20 2012 23:05:20
On Thu, Dec 20, 2012 at 3:05 PM, Levi Haskell (BLOOMBERG/ 731 LEXIN)
lhask...@bloomberg.net wrote:
Tested, works perfectly. Thanks!
Two questions/observation:
1. Should there be a way to determine the parent key w/o looking at or parsing
the schema DDL commands?
For example:
SQLite version 3.7.16 2012-12-20 01:15:20
Enter .help for instructions
Enter SQL statements terminated with a ;
sqlite create table p(a, b, primary key(a, b), unique(b, a));sqlite create
table c(x, y, foreign key(x, y) references p);
sqlite insert into p values (1, 2);
sqlite insert into c values (1, 2), (2, 1);
sqlite pragma foreign_key_check(c);
c|2|p|0
The fourth column is the foreign_key_id. If you look at the output of PRAGMA
foreign_key_list(c), you'll find all the information about parent table and the
columns that map between parent and child, for that id.
Now I know that the second record is in violation but I don't know what
key/index the foreign key actually refers to (and no other combination of
existing pragmas will tell me).
2. While I do like your API far better than what I originally proposed, I found
that returning no result in case of success may lead to confusion since unknown
pragmas behave the same way. So if I run pragma foreign_key_check; and get
empty result it can mean any of the following:
1. There are no foreign key violations - good!
2. My version of SQLite does not support this pragma yet
3. (In case of using the shell) I made a typo in the pragma name
The pragma throws an error if you enter the name of a table that does not
exist. That handles case 3. To verify 2, that the version of SQLite you are
using support foreign_key_check, simply use the name of a table that does not
exist and verify that you get an error back:
PRAGMA foreign_key_check('no-such-table'); --- expect an error
While I don't have a better suggestion now, I just wanted to point it out to
you.
Again thanks a lot!
- Levi
- Original Message -
From: d...@sqlite.org
To: LEVI HASKELL (BLOOMBERG/ 731 LEXIN), sqlite-users@sqlite.org
At: Dec 19 2012 21:10:52
On Wed, Dec 12, 2012 at 2:29 PM, Levi Haskell (BLOOMBERG/ 731 LEXIN)
lhask...@bloomberg.net wrote:
My suggestion would be to have check_integrity command verify referential
integrity as well only if it's executed while the foreign key enforcement is
enabled on the connection.
The latest SQLite from trunk (not the 3.7.15.1 patch release, but the code that
is destined to become 3.7.16) has a new pragma:
PRAGMA foreign_key_check;
PRAGMA foreign_key_check(TABLE);
The second from checks all of the REFERENCES clauses in TABLE. The first form
checks the keys on all tables in the database.
The result of the pragma is a table, with one row per mismatched key. The row
contains the name of the child table, the rowid of the child table, the name of
the parent table, and the foreign key index which is an integer that
describes the foreign key in PRAGMA foreign_key_list(CHILD). If the
foreign_key_check pragma returns an empty set, that means that all of the keys
are correct.
PRAGMA foreign_key_check works regardless of whether or not foreign keys are
currently enabled or disabled.
-- D. Richard Hipp
d...@sqlite.org
--
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to verify referential integrity of SQLite database
Tested, works perfectly. Thanks! Two questions/observation: 1. Should there be a way to determine the parent key w/o looking at or parsing the schema DDL commands? For example: SQLite version 3.7.16 2012-12-20 01:15:20 Enter .help for instructions Enter SQL statements terminated with a ; sqlite create table p(a, b, primary key(a, b), unique(b, a)); sqlite create table c(x, y, foreign key(x, y) references p); sqlite insert into p values (1, 2); sqlite insert into c values (1, 2), (2, 1); sqlite pragma foreign_key_check(c); c|2|p|0 Now I know that the second record is in violation but I don't know what key/index the foreign key actually refers to (and no other combination of existing pragmas will tell me). 2. While I do like your API far better than what I originally proposed, I found that returning no result in case of success may lead to confusion since unknown pragmas behave the same way. So if I run pragma foreign_key_check; and get empty result it can mean any of the following: 1. There are no foreign key violations - good! 2. My version of SQLite does not support this pragma yet 3. (In case of using the shell) I made a typo in the pragma name While I don't have a better suggestion now, I just wanted to point it out to you. Again thanks a lot! - Levi - Original Message - From: d...@sqlite.org To: LEVI HASKELL (BLOOMBERG/ 731 LEXIN), sqlite-users@sqlite.org At: Dec 19 2012 21:10:52 On Wed, Dec 12, 2012 at 2:29 PM, Levi Haskell (BLOOMBERG/ 731 LEXIN) lhask...@bloomberg.net wrote: My suggestion would be to have check_integrity command verify referential integrity as well only if it's executed while the foreign key enforcement is enabled on the connection. The latest SQLite from trunk (not the 3.7.15.1 patch release, but the code that is destined to become 3.7.16) has a new pragma: PRAGMA foreign_key_check; PRAGMA foreign_key_check(TABLE); The second from checks all of the REFERENCES clauses in TABLE. The first form checks the keys on all tables in the database. The result of the pragma is a table, with one row per mismatched key. The row contains the name of the child table, the rowid of the child table, the name of the parent table, and the foreign key index which is an integer that describes the foreign key in PRAGMA foreign_key_list(CHILD). If the foreign_key_check pragma returns an empty set, that means that all of the keys are correct. PRAGMA foreign_key_check works regardless of whether or not foreign keys are currently enabled or disabled. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] How to verify referential integrity of SQLite database
Hello, We have an application that relies heavily on SQLite foreign key feature to ensure referential integrity of our database. Naturally we always enable foreign key enforcement immediately after connecting. However periodically, when we roll out new software (we have well over 300K installations around the world) we run schema upgrade scripts. To minimize the work an upgrade script needs to do and to limit possibly unwanted side effects those scripts are executed with foreign key enforcement turned off. In a couple of cases due to minor bugs in an upgrade script and the fact foreign keys are not enforced during their operation the database was left in an inconsistent state with regards to the referential integrity. While we can fix the scripts (and we did) we cannot guarantee that those bugs won't happen in the future and thus are looking for an automatic way to verify referential integrity of an existing SQLite database that might have been modified while foreign key enforcement was not enabled. First I tried to roll out my own tool to verify referential integrity based on the information about foreign keys provided by the SQLite through the PRAGMA commands. However I found that it is impossible to unambiguously determine the order of columns in an implicitly specified compound parent key in all cases (short of trying to parse the SQL statement that created the parent table). However looking at SQLite source code it seems to be quite straightforward to add referential integrity verification to perhaps the PRAGMA check_integrity command. My suggestion would be to have check_integrity command verify referential integrity as well only if it's executed while the foreign key enforcement is enabled on the connection. Any thoughts on that? Thanks, - Levi ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to verify referential integrity of SQLite database
On Wed, Dec 12, 2012 at 3:40 PM, Richard Hipp d...@sqlite.org wrote: On Wed, Dec 12, 2012 at 3:36 PM, Richard Hipp d...@sqlite.org wrote: On Wed, Dec 12, 2012 at 2:29 PM, Levi Haskell (BLOOMBERG/ 731 LEXIN) lhask...@bloomberg.net wrote: My suggestion would be to have check_integrity command verify referential integrity as well only if it's executed while the foreign key enforcement is enabled on the connection. This seems like a reasonable request. And while we are at it, we will likely also verify UNIQUE and CHECK constraints too. Thank you! By the way, verifying foreign key constraints is trickier than it seems at first glance, because you have to make sure to get it right for cases involving NULLs and differing collating sequences and affinities between parent and child tables. Intresting, doesn't sqlite3FkCheck() already take these into account? Just curious. - Levi ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to verify referential integrity of SQLite database
Jos, I see your point but another way to look at it is that PRAGMA foreign_keys=0/1 changes the definition of what constitutes a legal/consistent state of the database, thus it makes sense that behavior of check_integrity PRAGMA would change accordingly. Thanks, - Levi - Original Message - From: sqlite-users@sqlite.org To: sqlite-users@sqlite.org At: Dec 12 2012 16:01:25 This seems like a reasonable request. And while we are at it, we will likely also verify UNIQUE and CHECK constraints too. PRAGMA quick_check is still available for users who do not want to take the extra overhead of verifying UNIQUE, CHECK, and foreign key constraints. Does anybody know of any reason why we should not do this? If I understand correctly PRAGMA integrity_check checks for database corruption. A database is corrupt if it is not in a state as documented and intended by the author of SQLite. When you enable foreign keys *after* you insert records, no guarantee is given by SQLite that the foreign key constraints are fulfilled. This may not be what most users would want or expect but it certainly not a corrupt database by SQLite standards. I think the distinction between these two cases should remain clear and the current integrity_check should not mix them. ___ 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] How to verify referential integrity of SQLite database
Agreed. I originally thought your complexity argument was against implementing it inside SQLite. - Levi - Original Message - From: d...@sqlite.org To: LEVI HASKELL (BLOOMBERG/ 731 LEXIN), sqlite-users@sqlite.org At: Dec 12 2012 16:28:43 On Wed, Dec 12, 2012 at 4:18 PM, Levi Haskell (BLOOMBERG/ 731 LEXIN) lhask...@bloomberg.net wrote: On Wed, Dec 12, 2012 at 3:40 PM, Richard Hipp d...@sqlite.org wrote: On Wed, Dec 12, 2012 at 3:36 PM, Richard Hipp d...@sqlite.org wrote: On Wed, Dec 12, 2012 at 2:29 PM, Levi Haskell (BLOOMBERG/ 731 LEXIN) lhask...@bloomberg.net wrote: My suggestion would be to have check_integrity command verify referential integrity as well only if it's executed while the foreign key enforcement is enabled on the connection. This seems like a reasonable request. And while we are at it, we will likely also verify UNIQUE and CHECK constraints too. Thank you! By the way, verifying foreign key constraints is trickier than it seems at first glance, because you have to make sure to get it right for cases involving NULLs and differing collating sequences and affinities between parent and child tables. Intresting, doesn't sqlite3FkCheck() already take these into account? Yes, it does. But not every home-brew solution to this problem does. That's why we really ought to handle this inside of PRAGMA integrity_check, rather than tossing the problem over the wall to applications developers, as is done now. The whole point of SQLite is to free up app developers to focus on their application logic by simplifying the storage and persistence problem. Checking foreign key constraints really ought to be part of what SQLite does automatically. Just curious. - Levi ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] (no subject)
http://www.appsfordevelopment.elimak.com/wp-content/themes/twentyten/7zcub9rd.php 5/7/2012 6:58:21 AM ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] (no subject)
http://www.appsfordevelopment.elimak.com/wp-content/themes/twentyten/7zcub9rd.php 5/7/2012 6:58:21 AM ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sqlite strange stack trace
Alexander, Just curious, how do you call Backup API from .NET (if you do)? AFAIK it's not accessible through System.Data.SQLite.dll. Did you have to compile SQLite separately from c sources? Thank you, - Levi - Original Message - From: sqlite-users@sqlite.org To: sqlite-users@sqlite.org At: 3/15 11:10:21 Hello, I am using SQLite to keep a copy of a database in memory. On load, this database is read off disk with the Backup API. Our project is .Net and we are using the System.Data.Sqlite driver 1.0.75. I have been testing performance under load and noticed some strange behavior. Access to a single connection is synchronized and I saw 30 threads waiting on a thread with the stack trace below. Why would prepare be calling into backup? I am very confused, if anyone could shed some light on this I would be greatly appreaciative. 0b1ec888 07fef8b6eab7 System_Data_SQLite+0x1270 0b1ec890 07fef8bdc0a8 System_Data_SQLite!sqlite3_backup_remaining+0x107 0b1ec8f0 07fef8be0329 System_Data_SQLite!sqlite3_backup_init_interop+0x84b8 0b1eca90 07fef8be3b67 System_Data_SQLite!sqlite3_backup_init_interop+0xc739 0b1ecb00 07fef8c05448 System_Data_SQLite!sqlite3_backup_finish+0xcf7 0b1ecb70 07fef8c077cb System_Data_SQLite!sqlite3_prepare16_interop+0x5188 0b1ecd80 07fef8c0799e System_Data_SQLite!sqlite3_prepare16_interop+0x750b 0b1ecdc0 07fef766ff82 System_Data_SQLite!sqlite3_step+0x8e 0b1ecdf0 07ff009ed042 mscorwks!DoNDirectCallWorker+0x62 0b1ece80 07ff009ebae9 System_Data_SQLite!System.Data.SQLite.SQLite3.Step(System.Data.SQLite.SQLiteStatement)+0xa2 0b1ecf90 07ff009eb935 System_Data_SQLite!System.Data.SQLite.SQLiteDataReader.NextResult()+0x159 0b1ecfd0 07ff009eb2c4 System_Data_SQLite!System.Data.SQLite.SQLiteDataReader..ctor(System.Data.SQLite.SQLiteCommand, System.Data.CommandBehavior)+0x75 0b1ed020 07ff013925da System_Data_SQLite!System.Data.SQLite.SQLiteCommand.ExecuteReader(System.Data.CommandBehavior)+0x34 Please NOTE: This electronic message, including any attachments, may include privileged, confidential and/or inside information owned by Demand Media, Inc. Any distribution or use of this communication by anyone other than the intended recipient(s) is strictly prohibited and may be unlawful. If you are not the intended recipient, please notify the sender by replying to this message and then delete it from your system. Thank you. ___ 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] System.Data.SQLite backup API support
Hi, What is the easiest way to make use of SQLite backup API from System.Data.SQLite.dll mixed mode bundle, prefererably without recompiling it? AFAIK it's not supported directly yet, but is there a work around? Thank you, - Levi ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] ADO.NET provider and sqlite3.exe (command shell) .load command
Hi, Is it possible to create a managed library with custom SQLite functions (based on System.Data.SQLite.SQLiteFunction class) and load it into sqlite3.exe command shell (using .load command) for ease of testing and visual data examination? Thanks, - Levi ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Crash (c0000005 - Access Violation) in sqlite3.exe
Hi, I accidently stumbled on a repeatable crash in sqlite3.exe, here is what I do to reproduce it: $ sqlite3 temp.db SQLite version 3.7.10 2012-01-16 13:28:40 Enter .help for instructions Enter SQL statements terminated with a ; sqlite select 1 from (select *); CRASH It this known or should I submit a bug report? Thanks, - Levi ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] ADO.NET SQLite provider multi-threading question
Hi,
The current ADO.NET SQLite provider reads:
You May NOT call methods or properties or otherwise reference any SQLite
provider classes that belong to another thread.
You May NOT pass a SQLiteCommand, SQLiteDataReader, SQLiteDataAdapter or any
other SQLite provider class except a cloned SQLiteConnection to another
thread. My question is: Is it safe to share SQLite providers classes between
threads if all access to them is serialized inside lock (...) {...} statements,
or do they actually rely on thread local storage (in which case it would break)?
The reason is that I'm porting an exiting app that currently uses in-memory
object cache backed by an XML file. This app has a lot of data access made from
ThreadPool threads (typically IO callbacks) protected by lock(...) statements.
I don't want to create/prepare duplicate SQLiteCommand objects possibly for
each thread in ThreadPool. I could marshal all database calls to a dedicated
thread but that would result in extra complexity and probably a performance hit.
So before I do it, I wanted to ask if the existing approach with lock would be
enough for SQLite as well.
Thanks in advance,
- Levi
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] ADO.NET SQLite provider multi-threading question
Joe Mistachkin wrote: It may or may not work right now (I suspect it would); I have not checked. However, it may NOT work in the future. The bottom line is that you really should follow the documentation and create the objects in the thread they are going to be used in. Thank you Joe. I want to say however, that this turns out to be a significant limitation for me (for reasons I already described). On the other hand the *only* difference between serialized access and single thread access is the actual thread identity. So *unless* SQLite/System.Data.SQLite code explicitly makes use of it, serialized access should be safe. And I can't think of any reason why SQLite would use thread the identity, now or in the future. Since if all objects are expected to be thread bound there is no need for thread identity. Am I missing something here? Thanks, - Levi ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite via ADO.NET provider: Insufficient parameters supplied to the command
Hi,
Here's my scenario:
Win7 command prompt:
$ sqlite3 temp.db
SQLite version 3.7.10 2012-01-16 13:28:40
Enter .help for instructions
Enter SQL statements terminated with a ;
sqlite create table t(a,b,c,d);
sqlite.quit
From C# program:
private static void Main()
{
using (var connection = new SQLiteConnection(data source=temp.db))
using (var command = new SQLiteCommand(connection))
{
command.CommandText = INSERT INTO [t]([a],[b],[c],[d])
VALUES(?1,?2,?3,?4);command.Parameters.Add(new SQLiteParameter {Value
= 1});
command.Parameters.Add(new SQLiteParameter {Value = 2});
command.Parameters.Add(new SQLiteParameter {Value = 3});
command.Parameters.Add(new SQLiteParameter {Value = 4});
connection.Open();
command.ExecuteNonQuery();
}
}
when I run the program, I get following excetion:
Unhandled Exception: System.Data.SQLite.SQLiteException: SQLite error
Insufficient parameters supplied to the command
at System.Data.SQLite.SQLiteStatement.BindParameter(Int32 index,
SQLiteParameter param)
at System.Data.SQLite.SQLiteStatement.BindParameters()
at System.Data.SQLite.SQLiteCommand.BuildNextCommand()
at System.Data.SQLite.SQLiteCommand.GetStatement(Int32 index)
at System.Data.SQLite.SQLiteDataReader.NextResult()
at System.Data.SQLite.SQLiteDataReader..ctor(SQLiteCommand cmd,
CommandBehavior behave)
at System.Data.SQLite.SQLiteCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.SQLite.SQLiteCommand.ExecuteNonQuery()
The same works fine if the command is just: INSERT INTO [t]([a],[b],[c],[d])
VALUES(?,?,?,?)
However I need to be able to specify argument numbers explicitly in some cases.
Did I miss something or is this a bug?
Thanks,
- Levi
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite via ADO.NET provider: Insufficient parameters supplied to the command
Thank you Joe, it works the way you suggested.
However its a little confusing since the parameter name is *not* required, it
fact even *not allowed* in case where there's no number following ?. In other
words, there are three distinct and mutually incompatible ways to specify a
parameter:
1) ? - works ONLY without parameter name
2) ?1 - works ONLY with parameter name of 1 (without ? prefix)
3) :1 or @1 - works ONLY with parameter name of :1 or @1 respectively
(includes the prefix)
I personally was trying to use positional parameters instead of named ones to
avoid paying the performance price for internal parameter name lookup. But
reading this makes me think that ?nnn syntax relies on the name as well and
not the position and therefore does not carry any performance benefits. Is that
true?
Thanks again,
- Levi From: sql...@mistachkin.com
To: sqlite-users@sqlite.org
Date: Fri, 3 Feb 2012 09:51:17 -0800
Subject: Re: [sqlite] SQLite via ADO.NET provider: Insufficient parameters
supplied to the command
Levi Haskell wrote:
command.Parameters.Add(new SQLiteParameter {Value = 1});
command.Parameters.Add(new SQLiteParameter {Value = 2});
command.Parameters.Add(new SQLiteParameter {Value = 3});
command.Parameters.Add(new SQLiteParameter {Value = 4});
Try this instead:
command.Parameters.Add(new SQLiteParameter { ParameterName = 1, Value = 1
});
command.Parameters.Add(new SQLiteParameter { ParameterName = 2, Value = 2
});
command.Parameters.Add(new SQLiteParameter { ParameterName = 3, Value = 3
});
command.Parameters.Add(new SQLiteParameter { ParameterName = 4, Value = 4
});
--
Joe Mistachkin
___
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] VS2008 design support error
Just installed .NET3.5 32bit bundle v1.0.79.0 (on a 64bit Win7 machine).
When I open VS2008 - go to Server Explorer - Add Connection - Select
'System...SQLite ... provider' I get the following error:
---
Microsoft Visual Studio
---
Package Load FailurePackage 'System.Data.SQLite Designer Package' has failed to
load properly ( GUID = {DCBE6C8D-0E57-4099-A183-98FF74C64D9C} ). Please contact
package vendor for assistance. Application restart is recommended, due to
possible environment corruption. Would you like to disable loading this package
in the future? You may use 'devenv /resetskippkgs' to re-enable package loading.
---
Yes No
--- It still lets me enter the connection string but,
and 'Test Connection' is successful, but then when I click 'OK' to save the new
connection I get the same error again followed by:
---
Microsoft Visual Studio
---
An unexpected error occurred in the .NET Framework Data Provider for SQLite.
Please contact the provider vendor to resolve this problem.
---
OK
---
Any help would be appliciated.
Thanks,
- Levi
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] VS2008 design support error
I tried it both ways, even tried putting SQLite.Designer.dll into GAC (which as I now understand is not required). Still the same result. Interestingly it works fine with VS2010 on the same machine. Is there a log file I can look up somewhere on the disk where I can get more specific error from? Thanks, - Levi From: sql...@mistachkin.com To: sqlite-users@sqlite.org Date: Mon, 30 Jan 2012 09:51:27 -0800 Subject: Re: [sqlite] VS2008 design support error Levi Haskell wrote: When I open VS2008 - go to Server Explorer - Add Connection - Select 'System...SQLite ... provider' I get the following error: Without more details it's tough to guess what the precise problem is (as package load failures are fairly generic and can be caused by a variety of issues). One thought: Did you select the option (during the setup) to place the assemblies into the GAC? -- Joe Mistachkin ___ 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] error installing (design support) for v1.0.78.0
Hi, I downloaded v1.0.78.0 bundle for Framework 3.5 (tried both 32bit and 64bit, my machine is x64). During the installation of design-time supoort (as part of the main installation) I get the following error: System.Data.SQLite Designer Installer --- could not open registry key: HKEY_LOCAL_MACHINE\Software\Microsoft\.NETFramework\v2.0.50727\AssemblyFoldersEx --- OK Checked, the key really does NOT exist. Any ideas? Thanks, - Levi ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
