[sqlite] multiple rows of VALUES in an INSERT incompatible with SQLITE_OMIT_COMPOUND_SELECT

The new feature to insert multiple rows of VALUES in a single INSERT http://www.sqlite.org/src/info/eb3b6a0ceb gives wrong results if SQLite is compiled with SQLITE_OMIT_COMPOUND_SELECT. Example: CREATE TABLE t10(a,b,c); INSERT INTO t10 VALUES(1,2,3), (4,5,6), (7,8,9); SELECT * FROM

Re: [sqlite] About new ticket "Per page/region checksums"

Roger, it's good to know the reasons of SQLITE_CORRUPT signal. And very useful is possibility to mark all corrupted database pages as unused in repair process and so make the database workable again. And this repair may be processed only by SQLite internals because is needed ignore corrupted pages

Re: [sqlite] About new ticket "Per page/region checksums"

It's almost trivial to add per-page checksums at the page level. Here are basic steps: 1) Define SQLITE_HAS_CODEC when building. This will cause 5 unresolved externals (functions declared but not defined/implemented). You need to provide implementations of these 5 functions: a.

Re: [sqlite] About new ticket "Per page/region checksums"

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 07/02/12 09:12, Michael Stephenson wrote: > It's almost trivial to add per-page checksums at the page level. You omitted the steps of extensive testing and documentation :-) > Given the above, would per-page checksums be better served up as an >

[sqlite] how to perform join?

Hi, sorry for the easy question. I have a table "lessons" I query this way: "SELECT lesson_id,title,total_time FROM lessons WHERE lesson_id>=$id AND group_id>=$group_id ORDER BY group_id,lesson_id LIMIT 0,2" lesson_id is primary and autoincrement. This query perfectly works and it does

Re: [sqlite] re trieve data from movie sqlite database

I never had the time to THANK everyone in this thread! THANK YOU! :-) BareFeetWare-2 wrote: > > On 04/01/2012, at 8:06 PM, E3 wrote: > >> I've a SQLite db containing data about movies. > > Implementing your description into a schema: > > create table "movies" > ( movie_id integer

Re: [sqlite] how to perform join?

On 2/7/2012 1:55 PM, E3 wrote: Hi, sorry for the easy question. I have a table "lessons" I query this way: "SELECT lesson_id,title,total_time FROM lessons WHERE lesson_id>=$id AND group_id>=$group_id ORDER BY group_id,lesson_id LIMIT 0,2" lesson_id is primary and autoincrement. This query

Re: [sqlite] A sqlite c++ wrapper, sqlite3x

Hi! Just to be clear - i'm the sqlite3x maintainer, not the original author. I forked it several years ago when the author failed to respond to several emails. I haven't added or removed any features in a couple years - it should work fine with 3.7 but does not have any features specific to 3.7.

[sqlite] File locking requirements in Single Process/Multi-Threaded system

I'm trying to use sqlite in an embedded, multi-tasking system with a very simple file system. Essentially, it doesn't support having a file open multiple times. That is, only one reader or writer. I have studied the code, but I can't quite tell how sqlite manages the DB files in a single

Re: [sqlite] File locking requirements in Single Process/Multi-Threaded system

On Tue, Feb 7, 2012 at 2:42 PM, Marc L. Allen wrote: > I'm trying to use sqlite in an embedded, multi-tasking system with a very > simple file system. Essentially, it doesn't support having a file open > multiple times. That is, only one reader or writer. > > I

Re: [sqlite] File locking requirements in Single Process/Multi-Threaded system

Ok. I can do that. Do I need to keep track of where each pseudo-file open is at, or does sqlite always seek on a file before reading/writing? Also, do the VFS xLock, etc. functions need to affect the opening or closing of files? That is, if sqlite requests a PENDING or EXCLUSIVE lock on a

Re: [sqlite] About new ticket "Per page/region checksums"

On Tue, Feb 7, 2012 at 11:12 AM, Michael Stephenson wrote: > It's almost trivial to add per-page checksums at the page level.  Here are > basic steps: This is not enough, though it's a lot better than nothing. You need to be able to store the checksums where the page

Re: [sqlite] File locking requirements in Single Process/Multi-Threaded system

On Tue, Feb 7, 2012 at 3:13 PM, Marc L. Allen wrote: > Ok. I can do that. Do I need to keep track of where each pseudo-file > open is at, or does sqlite always seek on a file before reading/writing? > The methods on the VFS open-file

Re: [sqlite] About new ticket "Per page/region checksums"

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 07/02/12 12:14, Nico Williams wrote: > You need to be able to store the checksums where the page pointers are > stored. The page containing the page pointers is itself checksummed. While there are still some extremely rare cases that isn't

Re: [sqlite] File locking requirements in Single Process/Multi-Threaded system

> The methods on the VFS open-file > objectthat do reading and > writing specify both the amount and the offset. There are no seeks. Oh, yeah. :( Sorry. I've even implemented those routines. > The open succeeds. xLock() does not interact with any

[sqlite] Does disabling synchronous AND shared cache cause "Error: database disk image is malformed"?

Hi! We're seeing the dreaded "Error: database disk image is malformed" message, even though the database is actually fine. I've read a few other threads on it and I'd previously concluded that it was just an outstanding bug. But I'm wondering if it's actually due to a combination of

[sqlite] SQLite .NET exception with parallel reading connections

Hi, I have written an application that reads values from an SQLite database and uses them for generating graph images. This process takes up to a few seconds (most of it is reading the database) and multiple images shall be generated. So I thought that doing things in parallel might be a good

Re: [sqlite] SQLite .NET exception with parallel reading connections

Just one more addition: If I only start a single background thread, everything is fine. If I start two threads, one of them always fails. But it's not always the same one. With two graph painting jobs of very different time duration, sometimes the shorter wins, and sometimes it's the longer one.

Re: [sqlite] SQLite .NET exception with parallel reading connections

That issue was fixed prior to release 1.0.77.0, here: http://system.data.sqlite.org/index.html/info/13a3981ec0 If possible, I recommend using the latest released version, 1.0.79.0. -- Joe Mistachkin ___ sqlite-users mailing list

Re: [sqlite] Does disabling synchronous AND shared cache cause "Error: database disk image is malformed"?

On Tue, Feb 7, 2012 at 5:19 PM, David Barrett wrote: > Hi! We're seeing the dreaded "Error: database disk image is malformed" > message, even though the database is actually fine. I've read a few other > threads on it and I'd previously concluded that it was just an

[sqlite] problem with case when

Have a table with an integer age field and a text age_group field. Need to update the age_group field according to the age. Tried with several case when constructions, but sofar nil working, eg: update pats set age_group = (case when age between(0 and 9) then '0 to 9' when age between(10 and 19)

Re: [sqlite] problem with case when

On Tue, Feb 7, 2012 at 6:14 PM, Bart Smissaert wrote: > Have a table with an integer age field and a text age_group field. > Need to update the age_group field according to the age. > Tried with several case when constructions, but sofar nil working, eg: > > update pats

Re: [sqlite] problem with case when

On Feb 8, 2012, at 12:14 AM, Bart Smissaert wrote: > Have a table with an integer age field and a text age_group field. Are you 100% positive that you have a number and not a text? Contrasts: select case when '1' between 0 and 9 then '0 to 9' end as band Vs. select case

Re: [sqlite] problem with case when

Yes, thanks had just figured that out the same. Working fine now. RBS On Tue, Feb 7, 2012 at 11:17 PM, Richard Hipp wrote: > On Tue, Feb 7, 2012 at 6:14 PM, Bart Smissaert > wrote: > >> Have a table with an integer age field and a text age_group

Re: [sqlite] Does disabling synchronous AND shared cache cause "Error: database disk image is malformed"?

On 02/07/2012 03:00 PM, Richard Hipp wrote: On Tue, Feb 7, 2012 at 5:19 PM, David Barrettwrote: 2) However, we get erratic behavior when using the sqlite3 command-line tool to just do a basic select on the database: sometimes it works, sometimes it returns "Error:

Re: [sqlite] SQLite .NET exception with parallel reading connections

On 7 Feb 2012, at 10:20pm, Yves Goergen wrote: > I have written an application that reads values from an SQLite database > and uses them for generating graph images. This process takes up to a > few seconds (most of it is reading the database) and multiple images > shall be generated. So I

Re: [sqlite] Does disabling synchronous AND shared cache cause "Error: database disk image is malformed"?

On Tue, Feb 7, 2012 at 6:31 PM, David Barrett wrote: > On 02/07/2012 03:00 PM, Richard Hipp wrote: > >> On Tue, Feb 7, 2012 at 5:19 PM, David Barrett> >wrote: >> >>> 2) However, we get erratic behavior when using the sqlite3 command-line >>> >>>

Re: [sqlite] Does disabling synchronous AND shared cache cause "Error: database disk image is malformed"?

On 02/07/2012 04:05 PM, Richard Hipp wrote: This tells me that the error is occurring at http://www.sqlite.org/src/artifact/5047fb303cdf6?ln=1362 which occurs right as SQLite is first starting to decode a page that it as loaded from the disk. The error indicates that the shell really is seeing

Re: [sqlite] Does disabling synchronous AND shared cache cause "Error: database disk image is malformed"?

On Tue, Feb 7, 2012 at 7:59 PM, David Barrett wrote: > > > Or, perhaps you are running the command-line tool on a >> different machine where it is not able to access the WAL's database-shm >> file in shared memory. So the command-line tool reads a one page of the >>

Re: [sqlite] Does disabling synchronous AND shared cache cause "Error: database disk image is malformed"?

On 02/07/2012 05:08 PM, Richard Hipp wrote: There are no known limitations on the use of PRAGMA synchronous=OFF, WAL mode, and shared cache together. Ah, I guess that blows that theory. But thanks for the quick and thorough response nonetheless. I'm a huge sqlite fan. I just wish I could

Re: [sqlite] Does disabling synchronous AND shared cache cause "Error: database disk image is malformed"?

On 8 Feb 2012, at 1:13am, David Barrett wrote: > On 02/07/2012 05:08 PM, Richard Hipp wrote: >> There are no known limitations on the use of PRAGMA synchronous=OFF, WAL >> mode, and shared cache together. > > Ah, I guess that blows that theory. But thanks for the quick and thorough > response

Re: [sqlite] Does disabling synchronous AND shared cache cause "Error: database disk image is malformed"?

On Tue, Feb 7, 2012 at 8:13 PM, David Barrett wrote: > On 02/07/2012 05:08 PM, Richard Hipp wrote: > >> There are no known limitations on the use of PRAGMA synchronous=OFF, WAL >> mode, and shared cache together. >> > > Ah, I guess that blows that theory. But thanks for

Re: [sqlite] Does disabling synchronous AND shared cache cause "Error: database disk image is malformed"?

On Tue, Feb 7, 2012 at 8:13 PM, David Barrett wrote: > > Ah, I guess that blows that theory. But thanks for the quick and thorough > response nonetheless. I'm a huge sqlite fan. I just wish I could figure > out this (non)malformed database issue! > Are you, perhaps,

Re: [sqlite] Does disabling synchronous AND shared cache cause "Error: database disk image is malformed"?

On 02/07/2012 05:27 PM, Richard Hipp wrote: On Tue, Feb 7, 2012 at 8:13 PM, David Barrettwrote: My best guess still is that the command-line shell is somehow not seeing the shared-memory file that is created when the database is running in WAL mode. Or, perhaps the posix

Re: [sqlite] Does disabling synchronous AND shared cache cause "Error: database disk image is malformed"?

On Tue, Feb 7, 2012 at 9:16 PM, David Barrett wrote: > Are you, perhaps, using -DSQLITE_SHM_DIRECTORY when compiling the servers, > >> but failing to use the same -D when compiling the command-line tool? >> > > This is possible -- we haven't been hand-compiling the sqlite3

Re: [sqlite] how to perform join?

I guess the USING syntax is where the joining column names match in both tables. Another way is to use the explicit: JOIN othertable ON condition SELECT L.lesson_id, L.title, L.total_time, P.wasPurchased FROM lessons L JOIN purchase_data P ON P.group_id = L.group_id WHERE L.lesson_id>=$id AND

[sqlite] Interpolation

Hello I have a large (> 1GB) collection of text files containing sensor values associated with timestamps, and I want to switch to an sqlite database. For simplicity, let's assume that I have two tables, one that stores temperatures and another that stores relative_humidity: CREATE TABLE

Re: [sqlite] About new ticket "Per page/region checksums"

On Sat, Feb 4, 2012 at 3:51 PM, Alexey Pechnikov wrote: > It's very important but there are some questions about > http://www.sqlite.org/src/info/72b01a982a > Some times ago DRH wrote that checksum calculation don't slow down > SQLite significantly. > But can be this

Re: [sqlite] how to perform join?

Igor Tandetnik wrote: > > SELECT lesson_id,title,total_time, wasPurchased > FROM lessons join purchase_data using (group_id) > WHERE lesson_id>=$id AND group_id>=$group_id > ORDER BY group_id,lesson_id LIMIT 0,2 > Thank you very much indeed: I could not undestand how and where to insert the

Re: [sqlite] how to perform join?

Larry Knibb wrote: > > Btw, be careful where you get your $id and $group_id variable content > from. If you are embedding these in a string (e.g. in a Perl script) > then you should check these are numeric before you use them. Otherwise > this is how SQL injection attacks happen, e.g. I might

Re: [sqlite] how to perform join?

Depends if your database is hosted on a webserver which the mobile app accesses, or if it's local on the device. It's probably harder to manipulate embedded JavaScript if everything is local on the phone. It's also less likely that you will deliver any "secrets" in an embedded DB too (because

Re: [sqlite] Interpolation

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 07/02/12 23:14, Steinar Midtskogen wrote: > I'm not asking the list to solve these problems for me, but it would > greatly help if anyone could point me to the right direction. Which > approach would work? I'd recommend you write code in your