[sqlite] chickens and egg problem: how to set page size before creating

It would appear that I need one "sqlite3* handle" in order to execute statements such as "pragma page_size=32768;", but the act of calling sqlite3_open(filename, ) creates the file, which prevents the changing of the page size, as the sqlite master tables are created, thereby rubbing up against

Re: [sqlite] Re: Re: Re: Select the top N rows from each group

Igor, Your query works now and returns 4 records. that was my mistake probably. Thank you! - To unsubscribe, send email to [EMAIL PROTECTED] -

[sqlite] Re: Re: Re: Select the top N rows from each group

Yuriy Martsynovskyy <[EMAIL PROTECTED]> wrote: That's not quite true. ORDER BY and LIMIT are allowed in sub-selects, as is the case here. This query works, I tested it before posting. Your query returns 2 records instead of 4. I'm testing it on SQLite v3.3.10 Works for me. Returns 4 rows.

Re: [sqlite] Re: Re: Select the top N rows from each group

Igor, That's not quite true. ORDER BY and LIMIT are allowed in sub-selects, as is the case here. This query works, I tested it before posting. Your query returns 2 records instead of 4. I'm testing it on SQLite v3.3.10

Re: [sqlite] porting sqlite3 to embeded os-----lock question

On 5/9/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: I am porting sqlite3 to the embeded os,such as threadx,nucleus,ect.. I am writing the file such as os_threadx.c,os_nucleus.c according to the os_win.c,os_unix.c. I have read the os_win.c and find that there is a switcher OS_WINCE in the

[sqlite] Re: Re: Select the top N rows from each group

Yuriy Martsynovskyy <[EMAIL PROTECTED]> wrote: select * from (select * from fruits where type = 'apple' order by price limit 2) union all select * from (select * from fruits where type = 'orange' order by price limit 2) SQlite allows only one LIMIT clause per query, that is applied to the

Re: [sqlite] Select the top N rows from each group

I'm sure somebody can do better, but I I came up with this: create table fruits (type text, variety text, price number); create index fruit_type_price on fruits (type, price); insert into fruits values ('apple', 'gala', 2.79); insert into fruits values ('apple', 'fuji', 0.24); insert into fruits

Re: [sqlite] Re: Select the top N rows from each group

Igor, select * from (select * from fruits where type = 'apple' order by price limit 2) union all select * from (select * from fruits where type = 'orange' order by price limit 2) SQlite allows only one LIMIT clause per query, that is applied to the final resultset

[sqlite] Re: Select the top N rows from each group

Yuriy Martsynovskyy <[EMAIL PROTECTED]> wrote: I need to select top 2 (or N) most expensive fruits of each type from this table: +++---+ type | variety| price | +++---+ apple | gala | 2.79 | apple | fuji | 0.24 | apple |

Re: [sqlite] Unsupported file format

On 5/11/07, Alberto Simões <[EMAIL PROTECTED]> wrote: Hi, I am using a Mac, and probably doing something weird with fink software and (probably) other installations. The fact is that I create a database using DBD::SQLite, and then: [EMAIL PROTECTED] ProjectoDicionario]$ sqlite3 dic.db

[sqlite] Select the top N rows from each group

I need to select top 2 (or N) most expensive fruits of each type from this table: +++---+ | type | variety| price | +++---+ | apple | gala | 2.79 | | apple | fuji | 0.24 | | apple | limbertwig | 2.87 | | orange | valencia |

[sqlite] GDBM or SQLite?

Hi all, I need to save some data on hard disk in tables. Very basic tables for example hashtables would be sufficient for me and I don't need SQL. My first idea was to use GDBM but: - GDBM does not allow several processes to open the same file at the same time - GDBM does not work on windows

[sqlite] default values at CREATE TABLE

I want to create a table with two colums: One ist the primary key (test_num) and the second column sholud contain the value of the primary key (maybe as a string) by default. How can I define this table in sql ? CREATE TABLE test_table ("test_num integer primary key AUTOINCREMENT NOT NULL,

Re: [sqlite] Odd results return by SELECT query WHERE word = "word"

'word' is correct SQL, "word" is not. Matteo Vescovi wrote: Hi, I am getting weird results when executing a query that has this WHERE clause: WHERE word = "word". The query works fine if I use WHERE word = 'word'. The following illustrates the problem: [EMAIL PROTECTED]:~$ sqlite -version

[sqlite] Unsupported file format

Hi, I am using a Mac, and probably doing something weird with fink software and (probably) other installations. The fact is that I create a database using DBD::SQLite, and then: [EMAIL PROTECTED] ProjectoDicionario]$ sqlite3 dic.db SQLite version 3.2.8 Enter ".help" for instructions sqlite>

Re: [sqlite] indexing large databases

On May 10, 2007, at 11:08 PM, Juri Wichanow wrote: For "create index.." in large database : "pragma default_cache_size = 2000;" For "select ..." -- "pragma default_cache_size = 1200;" Hmm, quite interesting. I would like to share my naive observations, which led me to believe the

Re: [sqlite] A suggestion

Doskey was the history TSR. On Wed, 9 May 2007, Rich Shepard wrote: > On Wed, 9 May 2007, John Stanton wrote: > > > That program does have the capability, but may not be implemented that way > > on Windows. Why not make the change yourself? > > > > A.J.Millan wrote: > > > As a suggestion, and

Re: [sqlite] Multiple connections to the same database and CREATE TABLE command

On 5/11/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: "Vivien Malerba" <[EMAIL PROTECTED]> wrote: > Hi! > > In a single process, I open two connections (C1 and C2) to the same > database (this is actually a corner case which could happen) and the > following sequence of operations fail: > 1-

Re: [sqlite] Multiple connections to the same database and CREATE TABLE command

"Vivien Malerba" <[EMAIL PROTECTED]> wrote: > Hi! > > In a single process, I open two connections (C1 and C2) to the same > database (this is actually a corner case which could happen) and the > following sequence of operations fail: > 1- on C1 execute "CREATE table actor (...)" => Ok > 2- on C1

Re: [sqlite] SQLITE_CORRUPT recover

"Sabyasachi Ruj" <[EMAIL PROTECTED]> wrote: > Hi, > Is there any way to programmatically fix a corrupted sqlite database? > I am using sqlite version 3.3.8 with C APIs > Sometimes VACUUM or REINDEX will help, but usually not. You can also try to recover using: sqlite3 OLD.DB .dump | sqlite3

Re: [sqlite] Odd results return by SELECT query WHERE word = "word"

--- Tomash Brechko <[EMAIL PROTECTED]> wrote: > On Fri, May 11, 2007 at 09:57:01 +0100, Matteo > Vescovi wrote: > > Hi, > > I am getting weird results when executing a query > that > > has this WHERE clause: WHERE word = "word". > > > > The query works fine if I use WHERE word = 'word'. > >

Re: [sqlite] Odd results return by SELECT query WHERE word = "word"

On Fri, May 11, 2007 at 09:57:01 +0100, Matteo Vescovi wrote: > Hi, > I am getting weird results when executing a query that > has this WHERE clause: WHERE word = "word". > > The query works fine if I use WHERE word = 'word'. The WHERE word = "word" is a no-op. From "SQLite Keywords" section of

[sqlite] Odd results return by SELECT query WHERE word = "word"

Hi, I am getting weird results when executing a query that has this WHERE clause: WHERE word = "word". The query works fine if I use WHERE word = 'word'. The following illustrates the problem: [EMAIL PROTECTED]:~$ sqlite -version 2.8.17 [EMAIL PROTECTED]:~$ cat populate.sql CREATE TABLE _1_gram

Re: [sqlite] Multiple connections to the same database and CREATE TABLE command

Is C1 in transaction? If it is, commit will enable C2 'see' the new table. regards, Radzi. - Original Message - From: "Vivien Malerba" <[EMAIL PROTECTED]> To: Sent: Friday, May 11, 2007 4:19 PM Subject: [sqlite] Multiple connections to the same database and

Re: [sqlite] Multiple connections to the same database and CREATE TABLE command

Vivien Malerba wrote: Hi! In a single process, I open two connections (C1 and C2) to the same database (this is actually a corner case which could happen) and the following sequence of operations fail: 1- on C1 execute "CREATE table actor (...)" => Ok 2- on C1 execute "SELECT * FROM actor" =>

[sqlite] Multiple connections to the same database and CREATE TABLE command

Hi! In a single process, I open two connections (C1 and C2) to the same database (this is actually a corner case which could happen) and the following sequence of operations fail: 1- on C1 execute "CREATE table actor (...)" => Ok 2- on C1 execute "SELECT * FROM actor" => Ok 3- on C2 execute

[sqlite] SQLITE_CORRUPT recover

Hi, Is there any way to programmatically fix a corrupted sqlite database? I am using sqlite version 3.3.8 with C APIs -- Sabyasachi

[sqlite] indexing large databases

For "create index.." in large database : "pragma default_cache_size = 2000;" For "select ..." -- "pragma default_cache_size = 1200;" Juri