on disk?
If this is not the intended behaviour - is there a way I can find out which
statements are causing the lock to be held open? Or can I force SQLite to
discard its cache?
Any help would be appreciated.
Regards,
Barry Smith
Hi Dinu,
Am I correct in my understanding then that it is not enough to call
sqlite3_reset, I must call sqlite3_finalize after I am done with a
statement?
Cheers,
- Barry
> On Mar 12, 2015 11:40 AM, "R.Smith" wrote:
>
> >
> >
> > On 2015-03-12 04:38 AM, Barr
irst step()
> actually)
>
>
> On 13.03.2015 02:44, Simon Slavin wrote:
>
>> On 13 Mar 2015, at 12:17am, Barry wrote:
>>
>> On 13 March 2015 at 01:21, Dinu Marina wrote:
>>>
>>> You should be also aware of a more common pitfall: unclosed result sets.
&
SQL commands do not need to be on multiple lines (they only need a
semicolon after each command).
But dot commands do.
Have you tried the following?
(ECHO .bail on
ECHO %multiple commands%) | sqlite3.exe %dbname%
But, much more importantly (particularly since you included BEGINNER in big
letters
Would not the following snippet be useful to return the record ID if the
feature set already exists?
SELECT rec.id
FROM records rec
WHERE
1 = (SELECT SUM(id_f IN (21, 22, 23)) = 3 AND COUNT(*) = 3 FROM
records_features rf WHERE rf.id_r = rec.id)
You would have to have some logic in your calling
to free(...)) was
the fact that Brett is encountering an exception rather than an invalid
return code.
On 8 February 2017 at 14:16, Richard Hipp <d...@sqlite.org> wrote:
> On 2/8/17, Barry Smith <smith.bar...@gmail.com> wrote:
> >
> > I believe SQLite doesn't use the
On 21 February 2017 at 13:48, Clyde Eisenbeis <cte...@gmail.com> wrote:
> Thanks for the good info!
>
> I can't find SQLite.Interop.dll ... is referenced at
> https://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki,
> but don't see a download.
>
> On Mon, F
Perhaps I misunderestimate your question, but I think a certain amount of
explanation of how SQLite works may be in order. What are you trying to do?
The SQLite shell tool does not depend on the DLL (the SQLite library is
instead included in the executable file). You can put sqlite3.exe wherever
This started off as a bug report about the writable schema method
recommended in https://www.sqlite.org/lang_altertable.html, but I realised
the problem is a bit broader.
It seems SQLite is ignoring manual changes to sqlite_master when combined
with increments to schema_version. The alter table
had returned all possible values, then stalled for a long time
as the DB engine processed all rows that could never return anything.
Cheers,
- Barry
[*I suspected that most of the time with the rowid query was taken in
writing to the console, so I SELECT'd COUNT(*) instead of * and it returned
Do I correctly understand the intention of the UPDATE is that for
each my_id in meta_table, it will store the count of all the hashes that
are associated only with my_id and no other id's?
In that case, have you tried:
UPDATE
meta_table
SET
distinct_hashes = (
SELECT
Trying again since this didn't go through the first time...
This started off as a bug report about the writable schema method
recommended in https://www.sqlite.org/lang_altertable.html, but I realised
the problem is a bit broader.
It seems SQLite is ignoring manual changes to sqlite_master when
Do you allow the entity framework to create your database, or do you
do it yourself?
You need text affinity on the columns in question. Then any numbers
will be stored as text, not numbers.
See:
https://sqlite.org/datatype3.html
Particularly:
3.1. Determination Of Column Affinity
The affinity
It looks like you're using C# and System.Data.SQLite.
If that's the case, use the connection string parameter FailIfMissing=True
to throw an exception if the file does not exist.
On 1 April 2018 at 02:34, Jens Alfke wrote:
>
> > On Mar 31, 2018, at 8:17 AM, Mike Clark
Thu 20 Dec 2018 6:26 PM, Barry Smith wrote:
> >
> >
> > > On 20 Dec 2018, at 4:21 pm, Jungle Boogie
> wrote:
> > >
> > > Hi All,
> > >
> > > Some sample data:
> > > 2018/04/15,foo,170644
> > > 2018/04/15,bar.69625
> &
I use Convert.ToInt32 (or whatever else is appropriate) instead of direct
casts when reading from System.Data.SQLite... I use a very similar schema
in an SQLServer and SQLite database, and Convert seems to handle many of
the differences without requiring different code for each database engine.
Your nested select statement wants to return many rows. Technically I think
this is illegal SQL (?), but it looks like SQLite is being lax about this
as usual and doing what it normally does in this sort of situation: picks a
row at random from the inner select.
Your statement is more or less
Dr. Hipp, you're sending your replies to the mailing list as well as your
intended recipient. Not sure if this is intended?
On Wed, 8 May 2019 at 08:02, Richard Hipp wrote:
> On 5/8/19, Richard Hipp wrote:
> > On 5/8/19, Regina Wilson (regiwils) wrote:
> >>
> >> Here’s a copy of the report.
>
If you use the SQLite backup api to 'backup' (save) the in-memory database
to the disk database, an atomic transaction is used to write the data to
the destination database. This means that all of SQLites usual guarantees
apply: If the operation completes, the disk database will contain a
be doing
the TimeSpan <-> (Number or string or however you want to store it in the
database) conversion.
Cheers,
- Barry
On Mon, 19 Aug 2019 at 16:40, Simon Slavin wrote:
> On 20 Aug 2019, at 12:19am, Barry wrote:
>
> > Would it be possible for a future version of SQLite t
that schema.
Would it be possible for a future version of SQLite to support TimeSpan
mapping in the entity framework?
Thanks in advance,
- Barry Smith
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin
Why do it this way?
Why not write your own custom_sqlite3_exec(...) that uses the standard,
stable, documented interfaces?
custom_sqlite3_exec(...) could call prepare / step / finalize, and use the
standard sqlite3_column_* interfaces to fill a result array. This would be
very little work and
Serialization isolation requires 'The databases ends up in the state it
would appear if every transaction were run sequentially'. It doesn't
actually require that all transactions occur sequentially, just that the
database ends up in a state as though they had.
Why do you think that SQLite's
Even in rollback journal mode, it is not universally safe to use normal
file operations on a SQLite database. See section 1.3 and 1.4 of
https://www.sqlite.org/howtocorrupt.html
If you want to use normal file system operations (or any type of
manipulation not using the SQLite library) on an
.
Of course, I'm just talking about how to code it, the issues mentioned by R
Smith is a different kettle of fish.
Cheers,
Barry
> On 23 Aug 2015, at 3:05 am, "R.Smith" wrote:
>
>
>
>> On 2015-08-21 11:23 PM, Will Parsons wrote:
>>> On 21 Aug 2015, R.Smith wr
and then restarting? Do you have shared cached enabled?
Process Explorer is a windows tool that will tell you which process has open
handles on a particular file. I found it a right pita to use though.
Cheers,
Barry
> On 23 May 2015, at 1:42 pm, "Keith Medcalf" wrote:
>
>
>
If you have a unique index on name, you could use INSERT OR IGNORE.
https://www.sqlite.org/lang_conflict.html
INSERT OR IGNORE INTO TAGS (NAME, COUNT) VALUES ('Bleh', 1)
As for your original query: think about just the select clause (you can run it
independently). This will return
My database is permanently locked, and I've spent two fruitless days
trying to unlock it.
The problem:
$ sqlite trac.db
SQLite version 3.3.6
Enter ".help" for instructions
sqlite> .databases
Error: database is locked
I believe this was caused by something bad happening in Trac 0.9.6. More
than
Christian Smith wrote:
> Max Barry uttered:
>
> My database is permanently locked, and I've spent two fruitless days
> trying to unlock it.
>
> You haven't said what sort of box this is. I guess a generic Unix. If Linux,
> you'll probably have fuser install
I am trying to locate an ODBC driver that I can use in the Windows Data
Link.
Is this doable? Does one exist?
Thanks, Barry
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
t of using a lock to synchronise access to the
> databaseso that only one thread at a time could change the
> database although both could read simultaneously?
> JS
>
> Barry Paul wrote:
> >
> > Hi, I am having some unexpected locking issues with SQLite.
> >
&
have intercepted it. Perhaps your debugging environment could tell you more
details of the real proximate cause of the error (unless the you mean that the
error message of the exception, by coincidence, was the same as a standard
SQLite error message)
Cheers,
- Barry
> On 8 Feb 2017, a
ple WPF program that uses the .dll
> class name to access the functions.
>
> With this info, which option would you recommend?
>
>> On Sun, Feb 19, 2017 at 9:45 PM, Barry Smith <smith.bar...@gmail.com> wrote:
>> Strange, I replied to this earlier... Perhaps my me
System.Data.SQLite is the package you want if you just want a .Net style (i.e.
Using the standard .net db interfaces) wrapper around SQLite. You can find it
on NuGet.
The entity framework is a library that maps database entries and relations to
OOP style objects. Look up object relational
Aliases in SQL are not the same as variables in most procedural languages. So
every time you mention 'totaltime' SQLite is probably recalculating that value
by adding all the columns together. See the various discussions regarding no
deterministic (random) functions last year. Less references
Oh, I do remember having this issue before.
I think the cause is this: Visual studio attempts to trace which dlls are
required for each project. Then if project A is dependent on project B, visual
studio will copy what it thinks is all the required dlls for both projects into
project A's
Strange, I replied to this earlier... Perhaps my messages are not getting
through.
You cannot include a .c file for compilation in a c# project. You'd have to do
use a separate DLL and do some pinvoke stuff to get to the raw SQLite
interface, but in my opinion you're better off using the
Nice.
Say goodbye to transitive equality though.
> On 3 Dec 2016, at 5:02 AM, Keith Medcalf wrote:
>
>
> feq (and friends) are an extension I wrote that does proper floating point
> comparisons:
>
> /*
> ** 2015-11-09
> **
> ** The author disclaims copyright to this
A less LMGTFY-like response:
First off, using SQLite does require that you become familiar with SQL, and a
bunch of database ideas: normalization for good schema design, unit of work for
good use of transactions, how to use indexes, others I'm sure I don't know
about. But those ideas are not
Are your updates sorted by DS? If your queries are sorted then sequential
queries are more likely to hit the same db pages while searching the index,
resulting in higher cache usage and fewer decompression operations. This would
have less benefit if your 100k DS values of the updates are
(which is done at
the start of almost every method in the class, and therefore again when you
call ExecuteNonQuery.)
> On 27 Sep 2017, at 5:07 am, Roberts, Barry (FINTL) <b.robe...@fugro.com>
> wrote:
>
> Hi,
>
> I am aware of the sqlite FAQ, and especially the comments r
> On 26 Sep 2017, at 12:14 am, Clemens Ladisch <clem...@ladisch.de> wrote:
>
> Roberts, Barry (FINTL) wrote:
>> As per my original post, all C# access code is making extensive use of
>> "using" statements. However we do obviously rely on the connection po
Ryan's way works well. Here is a second method which expresses it in a
different way:
SELECT * FROM T t1 where rowid IN (SELECT rowid FROM T t2 WHERE t1.F2 = t2.F2
ORDER BY rowid LIMIT 10)
If you have WITHOUT ROWID tables you'd have to replace rowid with your primary
key.
(The query may
__
> From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf
> of Barry Smith [smith.bar...@gmail.com]
> Sent: Tuesday, May 01, 2018 5:23 PM
> To: SQLite mailing list
> Subject: Re: [sqlite] probably recursive?
>
> Is there a uniqueness constraint on your in
In you initial email, what is n? Some real number between zero and one?
> On 2 May 2018, at 8:37 am, Abroży Nieprzełoży
> <abrozynieprzelozy314...@gmail.com> wrote:
>
> I think Barry mean that you can represent the (x,y) pair as a single
> number like (max(X)-min(X))*(Y-m
Is there a uniqueness constraint on your initial data? Can the same coordinate
be listed multiple times?
Is there a requirement that X > 0 and Y > 0?
> On 2 May 2018, at 3:35 am, Simon Slavin wrote:
>
>> On 1 May 2018, at 6:28pm, Simon Slavin
Ah my bad, I misunderstood the initial condition. nX is a function of X. My
statements were only true if nX=X. Well, sorry about the noise.
> On 2 May 2018, at 8:20 am, Roman Fleysher <roman.fleys...@einstein.yu.edu>
> wrote:
>
> Dear Barry,
>
> The statement about t
Some ideas:
Sqlite may return that the database is locked immediately if it detects a
deadlock situation. Something like: a different connection holds a reserved
lock (waiting for read connections to close so it can promote to exclusive),
and the current connection tries to promote from a read
I believe the inotifywait does not actually wait for the fsync operation before
notifying.
Process A can write to a file, the OS can cache it without flushing to disk,
and a request by process B will be served directly from the cache. Therefore
the operating system can notify you of the change
> On 20 Dec 2018, at 4:21 pm, Jungle Boogie wrote:
>
> Hi All,
>
> This is more of a how do I do this in sql question. I apologize in advance
> for a simple question, but I need to learn somehow, so any pointers are
> appreciated.
>
> My very simple schema:
>
> CREATE TABLE mileage (
>
That's not how you set a busy timeout using a connection string. That's not how
you set any (pragma) options with a connection string. Check the
System.Data.SQLite documentation (or google) to find out connection string
parameters, or play around with the SQLiteConnectionStringBuilder.
The
Without an order by, sqlite can return the rows in any order it pleases. Likely
whatever consumes the least resources. Although unlikely given your indices, it
might be possible - for instance if some future micro-optimisation finds that
it's quicker to read the index in reverse, then sqlite
You might be interested in the BEGIN CONCURRENT branch. It does page level
locking (not quite as granular as row level).
https://www.sqlite.org/cgi/src/doc/begin-concurrent/doc/begin_concurrent.md
> On 22 Mar 2019, at 11:48 am, Thomas Kurz wrote:
>
> This sounds interesting. I have some
I think the sessions extension can be used for what you want to do. I haven't
used it myself, but from it's documented behaviour it looks like you could
record change sets for every three second interval then apply them back to your
database on disk. If your app is multi-threaded it might be a
many
connection objects will get instantiated very close to each other time wise.
Unfortunately the problem seems to be very difficult to replicate reliably.
Generally we are seeing around about a 5% failure rate at application start.
Any further info, ask away.
Hope that helps,
Barry Roberts
Regards,
Barry Roberts.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
.
at System.Data.SQLite.SQLiteLog.Initialize(String className)
at System.Data.SQLite.SQLiteConnection..ctor(String connectionString,
Boolean parseViaFramework)
We have parse via framework set to true.
Kind Regards,
Barry Roberts.
___
sqlite-users mailing list
Regardless of whether you decide to store GUIDs as text or binary, things will
be easier if you set your connection string appropriately. BinaryGUID is the
parameter you want to change.
See https://www.connectionstrings.com/sqlite-net-provider/store-guid-as-text/
For performance binary is
On the original topic...
How does one end up with a database in this state? I.e with a binary value that
contains 0x00 bytes followed by other bytes but a type of TEXT?
If the definition of a text string in SQLite is that it ends at the first 0x00
byte, then it seems that anything stored as a
One thing that really stands is “creates 64 threads that operate on independent
tables in the sqlite database, performing operations that should be almost
entirely independent.”
But that’s not how SQLite works - at least not when writing data. SQLite takes
a lock on the entire database, there
SQL is a declarative language, not a procedural one. Therefore I find it helps
to think in declarative terms rather than procedural.
What that means practically in this case is don’t think in terms of loops.
Think about what you want, and think about how your data is related.
I don’t entirely
Is not identifier resolution (search in the current scope first then step out)
part of the SQL standard? Damn closed standards and their inability to check
without paying through the nose.
Even if not, and this is in fact undocumented, I would be amazed if it changed,
purely for the sheer
Dec 2019, at 9:48 am, Simon Slavin wrote:
>
> On 28 Dec 2019, at 5:19pm, Barry Smith wrote:
>
>> Is not identifier resolution (search in the current scope first then step
>> out) part of the SQL standard?
>
> The way the SELECT statements are nested in the question is
}
[NotNull]
public IDbCommand CreateCommand([NotNull] IDbConnection connection)
{
IDbCommand command = new
SQLiteCommand();
command.Connection =
connection.NotNull("connection");
,
Barry Roberts.
b.robe...@fugro.com<mailto:b.robe...@fugro.com> | www.fugro.com
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
et the above working in
the later drivers?
Kind Regards,
Fugro Intersite.
Barry Roberts.
b.robe...@fugro.com<mailto:b.robe...@fugro.com> | www.fugro.com
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-b
se journal mode "persist".
Should we not use prepare calls when running connection pooling, or do you have
any other recommendations we should take note of.
Kind Regards,
Barry Roberts.
b.robe...@fugro.com<mailto:b.robe...@fugro.com> | www.fugro.com
the new driver to improve performance, but will trade off the
command prepare calls, to enable driver stability.
Kind Regards,
Barry Roberts.
b.robe...@fugro.com<mailto:b.robe...@fugro.com> | www.fugro.com
___
sqlite-users mailing list
sqlite
s now. This helps me justify
altering our code base, to work with the revised behaviour.
Kind Regards,
Fugro Intersite.
Barry Roberts.
b.robe...@fugro.com<mailto:b.robe...@fugro.com> | www.fugro.com
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
works and returns ok, so access to the file is ok, just some internal
indexes are messed up. Is there any way to get the above working in the later
drivers, or can someone tell me which driver update changed this behaviour?
Kind Regards,
Fugro Intersite.
Barry Roberts.
b.robe...@fugro.com<mai
70 matches
Mail list logo