[sqlite] Question on errors - IOERR and CANTOPEN

I sometimes get either a SQLITE_IOERR or a SQLITE_CANTOPEN when issuing BEGIN IMMEDIATE TRANSACTION or END TRANSACTION, however the database file is there and is being used by another thread. I thought I'd get the usual SQLITE_BUSY or SQLITE_LOCKED, but sometimes these file-related errors come up.

Re: [sqlite] read_uncommitted=on; question

> You're probably better off ignoring all this stuff and just getting a > working solution. Only then is it worth running some sort of > profiling system on your application to find out which bits are most > worth optimising. Since the sqlite3 library is pretty fast already > you might find that

Re: [sqlite] read_uncommitted=on; question

On 26 May 2009, at 4:56am, Dennis Volodomanov wrote: > Yes, good point... I might compare performance differences in using a > shared connection (multiple threads each opening its own copy of the > database and sharing the connection) vs using the same database from > those threads, unless such

Re: [sqlite] read_uncommitted=on; question

> > It's multiple threads all using the same sqlite3* handle. I call > > sqlite3_enable_shared_cache(1); before opening the database and have > > "PRAGMA read_uncommitted=1;" right after opening the database. > > None of that has any effect as long as you only have one connection. > For > the

Re: [sqlite] [OT] How to implement paging

Hello Sam, SC> I take it that there is no solution to my problem? That's our job isn't it? Designing some solution that works? - Might have a worker thread watching the DB and have it sending notifications to the GUI as deletes and additions were made to the DB so, the display would be updated

Re: [sqlite] read_uncommitted=on; question

"Dennis Volodomanov" wrote in message news:8501919721c3de4c81bca22846b08721a3f...@lazarus.conceiva.com >> Wrong. Statements on the same connection certainly see changes made >> on that connection, committed or otherwise. >> >> Are you talking about the same

Re: [sqlite] read_uncommitted=on; question

> Because catching and dealing with the duplication is handled within > the library function, using these things appropriately should mean > that you don't have to do any fancy worrying about threads, processes > or simultaneity at all: if anything funny goes on, only one of the > INSERT

Re: [sqlite] read_uncommitted=on; question

On 26 May 2009, at 1:11am, Dennis Volodomanov wrote: > Basically these are file names being inserted into a table, so, before > each insert we check whether that file exists in the table already or > not (by doing a SELECT on an indexed lowercase full file path). So, it > really matters to me

Re: [sqlite] read_uncommitted=on; question

> Wrong. Statements on the same connection certainly see changes made on > that connection, committed or otherwise. > > Are you talking about the same connection, or two different connections > in shared cache mode? You started describing the latter, but now keep > mentioning the former. Which

Re: [sqlite] Simple Outer Join question?

"Kees Nuyt" wrote in message news:8u3m151rqbbel40ilsvaatqmfhcnhsj...@dim53.demon.nl > On Mon, 25 May 2009 23:14:50 +0200, Leo Freitag > wrote: >> I have a table 'person' and a table 'group'. Every person can join >> none, one or more groups. >> No I

Re: [sqlite] read_uncommitted=on; question

> How do you know the read on connection B doesn't actually happen right > _before_ the write on connection A? What kind of synchronization do you > employ between these two threads? I'm using a critical section (with a CSingleLock) to synchronize threads. However, just looking back at the code

Re: [sqlite] read_uncommitted=on; question

"Dennis Volodomanov" wrote in message news:8501919721c3de4c81bca22846b08721a3f...@lazarus.conceiva.com > If I turn off read_uncommitted, then data won't be "visible" by other > threads (same database connection) until a commit is done, right? Wrong. Statements on

Re: [sqlite] read_uncommitted=on; question

"Dennis Volodomanov" wrote in message news:8501919721c3de4c81bca22846b08721a3f...@lazarus.conceiva.com > Ok, thank you for confirming that. It seems that connection B > *sometimes* doesn't see data just inserted into a table by connection > A. How do you know the

Re: [sqlite] read_uncommitted=on; question

> > Ok, thank you for confirming that. It seems that connection B > > *sometimes* doesn't see data just inserted into a table by > > connection A. > > How long a time is 'just' ? You might want everything to be > completely up-to-date but does that record really matter if it didn't > exist a

Re: [sqlite] read_uncommitted=on; question

On 26 May 2009, at 12:36am, Dennis Volodomanov wrote: > Ok, thank you for confirming that. It seems that connection B > *sometimes* doesn't see data just inserted into a table by > connection A. How long a time is 'just' ? You might want everything to be completely up-to-date but does that

Re: [sqlite] read_uncommitted=on; question

> > I thought (from reading the docs) that with read_uncommitted=1 and > > sqlite3_enable_shared_cache(1), if I INSERT something, it will be > > picked > > up if I do a SELECT on another thread's connection as being in the > > database, even if a COMMIT has not been issued yet. Am I wrong in my >

Re: [sqlite] [OT] How to implement paging

On 25 May 2009, at 7:42pm, Sam Carleton wrote: > So in the end, you are saying that it is completely and totally the > responsibility of the frontend to keep track of the page number, > correct? The result set should simply return a total count so that > the # of pages can be calculated.

Re: [sqlite] Simple Outer Join question?

On Mon, 25 May 2009 23:14:50 +0200, Leo Freitag wrote: >Hallo, > >I have a table 'person' and a table 'group'. Every person can join none, >one or more groups. >No I want to select all persons except those who are member in group 1. >- Sounds simple, but not for me.

[sqlite] Simple Outer Join question?

Hallo, I have a table 'person' and a table 'group'. Every person can join none, one or more groups. No I want to select all persons except those who are member in group 1. - Sounds simple, but not for me. Thanks in advance Leo ___ sqlite-users

Re: [sqlite] [OT] How to implement paging

I understand and agree that things changing in the middle is not ideal. In the situation I am dealing with, things MUST disappear in the middle of the dataset. As far as adding things, it should happen at the end, but that is outside of my control, it all depends on how things are sorted.

Re: [sqlite] [OT] How to implement paging

Hello Sam, Are you planning on periodically updating the display as the user interacts with it? Have items pop in and pop out again as they're added or deleted? From your description, the data displayed in the GUI will go stale very quickly. I actually have similar logic in my app and I always

Re: [sqlite] [OT] How to implement paging

On Mon, May 25, 2009 at 4:05 PM, Teg wrote: > Hello Sam, > > Paging or scrolling is purely an abstraction you create with the GUI > itself. If you load up 88 titles into memory and your screen display is > 40 lines then you have 3 "pages" in memory and you simply replace a > "page"

Re: [sqlite] [OT] How to implement paging

Hello Sam, Paging or scrolling is purely an abstraction you create with the GUI itself. If you load up 88 titles into memory and your screen display is 40 lines then you have 3 "pages" in memory and you simply replace a "page" each time they scroll or page up/down. You seem to be letting the

Re: [sqlite] SQLite3 support for 64-bit unsigned integers

Just a small clarification you probably forgot... On Mon, May 25, 2009 at 6:56 PM, Jay A. Kreibich wrote: >  Since you haven't said what you're trying to do, it is difficult to >  propose a workaround.  If all you need is a unsigned long long, you >  can either use an 8-byte BLOB

Re: [sqlite] [OT] How to implement paging

On Mon, May 25, 2009 at 2:43 PM, Tito Ciuro wrote: > Hi Sam, > > On May 25, 2009, at 10:58 AM, Sam Carleton wrote: > >> Example:  Following the logic of the ScrollingCursor page, lets assume >> a total result set of 88 titles.  If the lasttitle happens to be the >> 29th title, so

Re: [sqlite] [OT] How to implement paging

Hi Sam, On May 25, 2009, at 10:58 AM, Sam Carleton wrote: > Example: Following the logic of the ScrollingCursor page, lets assume > a total result set of 88 titles. If the lasttitle happens to be the > 29th title, so the set that is returned is 30 through 34, how do I > determine that this is

Re: [sqlite] [OT] How to implement paging

On Mon, May 25, 2009 at 2:31 PM, Simon Slavin wrote: > > On 25 May 2009, at 6:58pm, Sam Carleton wrote: > >> Example:  Following the logic of the ScrollingCursor page, lets assume >> a total result set of 88 titles.  If the lasttitle happens to be the >> 29th title,

Re: [sqlite] [OT] How to implement paging

On 25 May 2009, at 6:58pm, Sam Carleton wrote: > Example: Following the logic of the ScrollingCursor page, lets assume > a total result set of 88 titles. If the lasttitle happens to be the > 29th title, so the set that is returned is 30 through 34, how do I > determine that this is the 6th

Re: [sqlite] Reading pragma's results

Well, thank you all. I managed to solve the problem. It was some initialization problems with sqlite. On Mon, May 25, 2009 at 7:20 PM, João Eiras wrote: > Okay, but I'm using the latest sqlite source version 3.6 > > On Mon, May 25, 2009 at 7:17 PM, Derrell Lipman >

[sqlite] [OT] How to implement paging

I want to thank the mailing list in general you all have been very helpful in my learning both SQLite and SQL in general. I am working on paging right now and simply don't know the SQL way of implementing it.  I have read through the SQLite page on scrolling cursor

Re: [sqlite] SQLite3 support for 64-bit unsigned integers

On Sun, May 24, 2009 at 11:28:59PM -0700, Kelly Jones scratched on the wall: > I tried inserting 2^63-1 and the two integers after it into an SQLite3 > db, but this happened: > > SQLite version 3.6.11 > Enter ".help" for instructions > Enter SQL statements terminated with a ";" > sqlite> CREATE

Re: [sqlite] Reading pragma's results

Okay, but I'm using the latest sqlite source version 3.6 On Mon, May 25, 2009 at 7:17 PM, Derrell Lipman wrote: > On Mon, May 25, 2009 at 1:10 PM, João Eiras wrote: >> Btw, the same happens with the command line sqlite program >> >> $

Re: [sqlite] Reading pragma's results

On Mon, May 25, 2009 at 1:10 PM, João Eiras wrote: > Btw, the same happens with the command line sqlite program > > $ sqlite > SQLite version 2.8.17 These pragmas didn't exist six years ago when 2.8.17 was current. The only pragmas in that version are: default_cache_size

Re: [sqlite] Reading pragma's results

Btw, the same happens with the command line sqlite program $ sqlite SQLite version 2.8.17 Enter ".help" for instructions sqlite> create table t(a integer); sqlite> insert into t values(1); sqlite> select * from t; 1 sqlite> pragma page_size; sqlite> pragma page_count; sqlite> Both pragmas don't

Re: [sqlite] Reading pragma's results

On Mon, May 25, 2009 at 6:48 PM, Igor Tandetnik wrote: > "João Eiras" wrote > in message > news:e72b1b360905250934h1b39c95eycab618faca87d...@mail.gmail.com >> Sorry, wrong url... >> http://pastebin.ca/1433959 >> >> On Mon, May 25, 2009 at 5:37 PM, João

Re: [sqlite] Reading pragma's results

"João Eiras" wrote in message news:e72b1b360905250934h1b39c95eycab618faca87d...@mail.gmail.com > Sorry, wrong url... > http://pastebin.ca/1433959 > > On Mon, May 25, 2009 at 5:37 PM, João Eiras > wrote: >> Hi there. >> I'm trying the following code >>

Re: [sqlite] Reading pragma's results

Sorry, wrong url... http://pastebin.ca/1433959 On Mon, May 25, 2009 at 5:37 PM, João Eiras wrote: > Hi there. > I'm trying the following code > http://pastebin.ca/143395 > > Strangelly, both pragmas don't return any row at all ! > What am I missing ? > The same happens if I

[sqlite] Reading pragma's results

Hi there. I'm trying the following code http://pastebin.ca/143395 Strangelly, both pragmas don't return any row at all ! What am I missing ? The same happens if I open a data file on disk. Thanks. ___ sqlite-users mailing list sqlite-users@sqlite.org

[sqlite] Older versions of sqlite3 analyzer?

Hi all, is there any pre-built windows sqlite3_analyzer binary for older SQLite Versions (3.5.9 in particular)? The download page only shows the 3.6.1 compatible one. Michael -- Michael Schlenker Software Engineer CONTACT Software GmbH Tel.: +49 (421) 20153-80 Wiener Straße 1-3

[sqlite] SQLite version 3.6.14.2

SQLite version 3.6.14.2 is now available on the SQLite website. Version 3.6.14.2 is a very small change over version 3.6.14.1. Version 3.6.14.2 fixes a single bug. The bug report and patch can be seen here: http://www.sqlite.org/cvstrac/tktview?tn=3879

Re: [sqlite] queries for a fulltext-engine

"Lukas Haase" wrote in message news:gve1lh$30...@ger.gmane.org >> SELECT topic_fulltext.topicID FROM topic_fulltext >> where exists (select 1 from fulltext >> WHERE topic_fulltext.fulltextID = fulltext.fulltextID and word >> LIKE 'word1%') >> and exists (select 1 from

Re: [sqlite] Checking if an "integer" column is set to NULL

On 25/05/2009 10:15 PM, chandan wrote: > Hi, > I have used sqlite3_bind_null() API to bind an integer column with > NULL. When I read the value of that integer column I get the value as 0 > (zero). Is there any way I can check if the column is set to NULL? You do realise that calling it

Re: [sqlite] Checking if an "integer" column is set to NULL

Thanks a lot!. The solution works :-) Hamish Allan wrote: > On Mon, May 25, 2009 at 1:15 PM, chandan > wrote: > > >>I have used sqlite3_bind_null() API to bind an integer column with >> NULL. When I read the value of that integer column I get the value as

Re: [sqlite] Checking if an "integer" column is set to NULL

On Mon, May 25, 2009 at 1:15 PM, chandan wrote: >    I have used sqlite3_bind_null() API to bind an integer column with > NULL. When I read the value of that integer column I get the value as 0 > (zero). Is there any way I can check if the column is set to NULL?

Re: [sqlite] queries for a fulltext-engine

Igor Tandetnik schrieb: > "Lukas Haase" wrote in > message news:gv9fcm$5r...@ger.gmane.org >> I have a database containing thousands of HTML pages ("topics"). There >> is a fulltext index for these topics. First there is a table >> containing all single words. Each word is

Re: [sqlite] [sqlite-dev] How to install sqlite3-3.6.14.1.bin.gz

On May 25, 2009, at 2:52 PM, Manasi Save wrote: > Hi, > > Can anyone help me on how to run sqlite3-3.6.14.1.bin.gz on Linux > machine. > I am working on Fedora 10. Please if anyone can provide any input on > this. Like this: d...@computer2:~/tmp$ wget

Re: [sqlite] read_uncommitted=on; question

On May 25, 2009, at 2:54 PM, Dennis Volodomanov wrote: > Hello all, > > Is it possible that with the read_uncommitted=1 and the shared cache > mode turned on (multithreaded application), that some data that has > been > inserted into the database, but not yet committed, could not be picked >

[sqlite] read_uncommitted=on; question

Hello all, Is it possible that with the read_uncommitted=1 and the shared cache mode turned on (multithreaded application), that some data that has been inserted into the database, but not yet committed, could not be picked up by another thread as being in the database? I thought (from reading

[sqlite] How to install sqlite3-3.6.14.1.bin.gz

Hi, Can anyone help me on how to run sqlite3-3.6.14.1.bin.gz on Linux machine. I am working on Fedora 10. Please if anyone can provide any input on this. -- Thanks and Regards, Manasi Save ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] SQLite3 support for 64-bit unsigned integers

On 25/05/2009 4:28 PM, Kelly Jones wrote: > I tried inserting 2^63-1 and the two integers after it into an SQLite3 > db, but this happened: > > SQLite version 3.6.11 > Enter ".help" for instructions > Enter SQL statements terminated with a ";" > sqlite> CREATE TABLE test (test INT); > sqlite>

[sqlite] SQLite3 support for 64-bit unsigned integers

I tried inserting 2^63-1 and the two integers after it into an SQLite3 db, but this happened: SQLite version 3.6.11 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> CREATE TABLE test (test INT); sqlite> INSERT INTO test VALUES (9223372036854775807); sqlite> INSERT

Re: [sqlite] sqlite3.exe and formatting binary (GUID) data

John Machin wrote: > Assuming your guid is a BLOB, then >SELECT other_stuff, QUOTE(guid), etc > will display it as hex e.g. X'01020304' > This is great; now I can see them at least! >> P.S. Of course even better would be the ability to read/write GUIDs as >> properly formatted strings!