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

[Andrew Finkenstadt]

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, &handle) creates the file, which prevents the
changing of the page size, as the sqlite master tables are created, thereby
rubbing up against the proviso, " The page-size may only be set if the
database has not yet been created. ".

Or am I missing something really obvious?

Plainly I can compile sqlite3.c with SQLITE_DEFAULT_PAGE_SIZE equal to my
desired page size, but surely that was not the way its use was intended.

--andy

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

[Yuriy Martsynovskyy]

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

[Igor Tandetnik]

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. You are doing something wrong.

Igor Tandetnik

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

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

[Yuriy Martsynovskyy]

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

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

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

[Nuno Lucas]

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
struct winFile.
Is this mean the windows platform don't need the function such as share
memory(CreateFileMappingW,MapViewOfFile) inside the OS_WINCE swither?


The shared memory is needed on WinCE to implement the LockFile
functions (which don't exist on WinCE).
If you don't need to do locking (because only your app will access the
db), you don't need the shared memory thing.



whether I should realize the share memory lock function in the embeded os?
I have find there is not a direct way similar to the windows share memory
and the interface funcitons.
It seems difficult to simulate the share memory funciton and it's lock
function in my embeded os.
Does it mean I must realize it. or the porting will fail.


It's up to you to know what level of compatibility you need. I would
guess that for you embedded os you don't need the locking part, so can
safely replace it with dummy functions that always succeed.



another question:
There is also a little difficult to realize the
sqlite3WinThreadSpecificData function to get the thread information,
Is this also must realize ?


If you use threads, then that would depend on your use of sqlite.


Regards,
~Nuno Lucas



thanks a lot.
allen.zhang




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

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

[Igor Tandetnik]

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
final resultset


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.


Igor Tandetnik 



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

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

[Clark Christensen]

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 values ('apple', 'limbertwig', 2.87);
insert into fruits values ('orange', 'valencia', 3.59);
insert into fruits values ('orange', 'navel', 9.36);
insert into fruits values ('pear', 'bradford', 6.05);
insert into fruits values ('pear', 'bartlett', 2.14);
insert into fruits values ('cherry', 'bing', 2.55);
insert into fruits values ('cherry', 'chelan', 6.33);

select
  f.type,
  f.variety,
  f.price
from 
  fruits f
where
  rowid in (select rowid from fruits where type = f.type order by price desc 
limit 2)
order by 
  f.type asc,
  f.price desc;

apple|limbertwig|2.87
apple|gala|2.79
cherry|chelan|6.33
cherry|bing|2.55
orange|navel|9.36
orange|valencia|3.59
pear|bradford|6.05
pear|bartlett|2.14

It's slow for a small result set.  100ms on my 2Ghz system under Windows.  It 
was over 300ms without the index.

 -Clark

- Original Message 
From: Yuriy Martsynovskyy <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Friday, May 11, 2007 2:44:30 PM
Subject: [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   |  3.59 |
| orange | navel  |  9.36 |
| pear   | bradford   |  6.05 |
| pear   | bartlett   |  2.14 |
| cherry | bing   |  2.55 |
| cherry | chelan |  6.33 |
+++---+

The result should be this:
++--+---+
| type   | variety  | price |
++--+---+
| apple  | gala |  2.79 |
| apple  | fuji |  0.24 |
| orange | valencia |  3.59 |
| orange | navel|  9.36 |
| pear   | bradford |  6.05 |
| pear   | bartlett |  2.14 |
| cherry | bing |  2.55 |
| cherry | chelan   |  6.33 |

The actual table is large and may contain millions of records.
This sample is taken from article at
http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/.
They offer this solution for MySQL as fastest:

(select * from fruits where type = 'apple' order by price limit 2)
union all
(select * from fruits where type = 'orange' order by price limit 2)
union all
(select * from fruits where type = 'pear' order by price limit 2)
union all
(select * from fruits where type = 'cherry' order by price limit 2)

But this query will not work on SQLite as SQLite supports only one
LIMIT. Can you suggest a good way to accomplish this task on SQLite?
Correlated queries are going to be slow

-
To unsubscribe, send email to [EMAIL PROTECTED]
-





-
To unsubscribe, send email to [EMAIL PROTECTED]
-

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

[Yuriy Martsynovskyy]

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

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

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

[Igor Tandetnik]

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  | limbertwig |  2.87 |
orange | valencia   |  3.59 |
orange | navel  |  9.36 |
pear   | bradford   |  6.05 |
pear   | bartlett   |  2.14 |
cherry | bing   |  2.55 |
cherry | chelan |  6.33 |

+++---+

The result should be this:
++--+---+

type   | variety  | price |

++--+---+

apple  | gala |  2.79 |
apple  | fuji |  0.24 |
orange | valencia |  3.59 |
orange | navel|  9.36 |
pear   | bradford |  6.05 |
pear   | bartlett |  2.14 |
cherry | bing |  2.55 |
cherry | chelan   |  6.33 |


The actual table is large and may contain millions of records.
This sample is taken from article at
http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/.
They offer this solution for MySQL as fastest:

(select * from fruits where type = 'apple' order by price limit 2)
union all
(select * from fruits where type = 'orange' order by price limit 2)
union all
(select * from fruits where type = 'pear' order by price limit 2)
union all
(select * from fruits where type = 'cherry' order by price limit 2)

But this query will not work on SQLite as SQLite supports only one
LIMIT. Can you suggest a good way to accomplish this task on SQLite?
Correlated queries are going to be slow


You can save this solution as follows:

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)

...

Igor Tandetnik 



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Unsupported file format

[P Kishor]

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 version 3.2.8
  Enter ".help" for instructions
  sqlite> .schema
  Error: unsupported file format

Any hints on what I might be doing wrong?


Yes. You have created a database via the latest version of DBD::SQLite
(the latest version of DBD::SQLite is 1.13 which is compatible sqlite
3.3.4 and onward, I believe) that is likely binary incompatible with
the older version of sqlite3 command line shell (you are using 3.2.8)
that you are using.

Just download the latest source code and compile a new sqlite3 with
it. All will be well.

--
Puneet Kishor http://punkish.eidesis.org/
Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/
Open Source Geospatial Foundation http://www.osgeo.org/education/
-
collaborate, communicate, compete
=

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Select the top N rows from each group

[Yuriy Martsynovskyy]

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   |  3.59 |
| orange | navel  |  9.36 |
| pear   | bradford   |  6.05 |
| pear   | bartlett   |  2.14 |
| cherry | bing   |  2.55 |
| cherry | chelan |  6.33 |
+++---+

The result should be this:
++--+---+
| type   | variety  | price |
++--+---+
| apple  | gala |  2.79 |
| apple  | fuji |  0.24 |
| orange | valencia |  3.59 |
| orange | navel|  9.36 |
| pear   | bradford |  6.05 |
| pear   | bartlett |  2.14 |
| cherry | bing |  2.55 |
| cherry | chelan   |  6.33 |

The actual table is large and may contain millions of records.
This sample is taken from article at
http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/.
They offer this solution for MySQL as fastest:

(select * from fruits where type = 'apple' order by price limit 2)
union all
(select * from fruits where type = 'orange' order by price limit 2)
union all
(select * from fruits where type = 'pear' order by price limit 2)
union all
(select * from fruits where type = 'cherry' order by price limit 2)

But this query will not work on SQLite as SQLite supports only one
LIMIT. Can you suggest a good way to accomplish this task on SQLite?
Correlated queries are going to be slow

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] GDBM or SQLite?

[ziozio]

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 if I am right (not really a problem for me)

Do you have an idea about the solution I have?
 - Open the DBM file for each request and wait if not ready? What about
performances ? (I will have a lot requests, from several processes)
 - Use a separate DBM server that will sequentialize the requests from all
processes?
 - Use SQLite or something else?

Thank you very much for all advices!
Vincent

-- 
View this message in context: 
http://www.nabble.com/GDBM-or-SQLite--tf3729794.html#a10439979
Sent from the SQLite mailing list archive at Nabble.com.


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] default values at CREATE TABLE

[Frank Pool]

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, test_name varchar(256) DEFAULT ??? NOT NULL,")

Any ideas ?
Thanks in advance,
Frank


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

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

[John Stanton]

'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
2.8.17
[EMAIL PROTECTED]:~$ cat populate.sql
CREATE TABLE _1_gram (word TEXT, count INTEGER,
UNIQUE(word) );
INSERT INTO _1_gram VALUES("foo", 13);
INSERT INTO _1_gram VALUES("bar", 16);
INSERT INTO _1_gram VALUES("word", 36);
INSERT INTO _1_gram VALUES("foobar", 336);
[EMAIL PROTECTED]:~$ sqlite test.db < populate.sql
[EMAIL PROTECTED]:~$ sqlite test.db
SQLite version 2.8.17
Enter ".help" for instructions
sqlite> SELECT * FROM _1_gram WHERE word = 'word';
word|36
sqlite> SELECT * FROM _1_gram WHERE word = "word";
foo|13
bar|16
word|36
foobar|336
sqlite> .quit
[EMAIL PROTECTED]:~$

Am I missing something here (I haven't used SQL in a
while...), or is this a bug?

Cheers,
- Matteo Vescovi




___ 
All New Yahoo! Mail – Tired of unwanted email come-ons? Let our SpamGuard protect you. http://uk.docs.yahoo.com/nowyoucan.html


-
To unsubscribe, send email to [EMAIL PROTECTED]
-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Unsupported file format

[Alberto Simões]

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> .schema
 Error: unsupported file format

Any hints on what I might be doing wrong?

Thank you
Alberto
--
Alberto Simões

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] indexing large databases

[Kasper Daniel Hansen]

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 cache_size was important (although I am now beginning to doubt  
this, see below).


First we run the indexing on server 1 - I am essentially the only  
user, but we only have 2GB of ram. I see (this is using top as is  
everything else I say about performance) that very quickly, the  
memory usage caps out and the cpu usage drops. My conclusion - an I/O  
bottleneck. So I increase cache_size and surely - the program runs  
longer before it seems to bottleneck. So I switch to another server  
(about the same speed, 16GB ram, unknown I/O performance compared to  
the first one), and increase the cache_size dramatically,  
hypothesizing that I could just have the entire db in ram (I know  
think I understand that I will still have to do I/O when writing the  
index). And surely I see no drop in CPU usage over the five days the  
program runs before the server gets rebooted. At this point  
(yesterday) I was convinced that the new server was dramatically  
better. Nevertheless I had never terminated my old runs on server 1.  
And lo and behold, yesterday I accidently discovered they were  
finished in around 7-8 days. This is something that makes no sense to  
me, but it could have something to do with how top measures cpu usage  
or whatever. Or I could have made a mistaken observation.


Well, I am curious now, so I am going to time this carefully on  
various servers (and report my results back to the list of course).


Kasper

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] A suggestion

[jphillip]

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 even in the risk to abuse of Mr Hipp's patience.
> > > Would
> > > it be possible to include in the command-line program (sqlite3.exe) the
> > > ability to edit, an repeat at least the five or six last commands, as in
> > > Linux?. Is to say with up-arrow and down-arrow.  I believe it would be too
> > > helpful.
> 
>   That, I believe, is a function of the shell, not the application using it.
> For example, the command history available in bash is not present -- at
> least, was not present -- in sh. Command history, IIRC, was a feature of the
> csh, and bash (the Bourne Again Shell) took the best features of sh, csh,
> and the Textronics version tcsh.
> 
>   Years ago, about a decade, when I had PC DOS 7.0 installed along with
> linux, I discovered that the DOS shell allowed tab completions. I don't
> recall if it had the history recall, too.
> 
> Rich
> 
> -- 
> Richard B. Shepard, Ph.D.   |The Environmental Permitting
> Applied Ecosystem Services, Inc.|  Accelerator(TM)
>  Voice: 503-667-4517  Fax: 503-667-8863
> 
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
> 
> 

You have to be BRAVE to grow OLD.
There are no old CARELESS pilots or electricians.


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

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

[Vivien Malerba]

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- on C1 execute "CREATE table actor (...)" => Ok
> 2- on C1 execute "SELECT * FROM actor" => Ok
> 3- on C2 execute "SELECT * FROM actor" => error because table "actor"
> does not exist.
>

When C2 goes to parse the SQL in statement 3, it does not know
that the database schema has changed because it has not attempted
to access the database file.  Thus it does not know that the new
table exists.

To fix this, you have to get C2 to access the database so that
it will reread and reparse the schema and thus discover the new
table.  Perhaps something like this:

   2.5- on C2 execute "SELECT 1 FROM sqlite_master LIMIT 1"



This seems to work, see test case (uncomment line 68, 69 to make it
work, remove the Test.db before each run).

Thanks!

Vivien
#include 
#include 
#include 

static int
execute_sql (sqlite3 *db, const char *sql)
{
	int status;
	sqlite3_stmt *stmt = NULL;
	int retval = 0;

	printf ("== %p SQL: %s\n", db, sql);
	status = sqlite3_prepare_v2 (db, sql, -1, &stmt, NULL);
	if (status != SQLITE_OK) 
		printf ("   ERROR preparing statement: %s\n", sqlite3_errmsg (db));
	else {
		status = sqlite3_step (stmt);
		if ((status != SQLITE_OK) && (status != SQLITE_DONE) && (status != SQLITE_ROW))
			printf ("   ERROR executing statement: %s\n", sqlite3_errmsg (db));
		else {
			retval = 1;
			printf ("   Ok\n");
		}
	}

	if (stmt)
		sqlite3_finalize (stmt);
	
	return retval;
}

int 
main(int argc, char **argv){
	char *dbname = "Test.db";
	sqlite3 *db1, *db2;
	int rc;

 	rc = sqlite3_open(dbname, &db1);
	if (rc) {
		printf ("Can't open database: %s\n", sqlite3_errmsg(db1));
		exit(1);
	}
	rc = sqlite3_open(dbname, &db2);
	if (rc) {
		printf ("Can't open database: %s\n", sqlite3_errmsg(db2));
		exit(1);
	}

	printf ("DB1: %p\nDB2: %p\n", db1, db2);

	if (! execute_sql (db1, " CREATE TABLE if not exists actor (\
  actor_id INTEGER PRIMARY KEY, first_name VARCHAR(45) NOT NULL, last_name VARCHAR(45) NOT NULL, \
  last_update TIMESTAMP NOT NULL);"))
		exit (1);

	if (! execute_sql (db1, "SELECT * FROM actor"))
		exit (1);
	if (! execute_sql (db2, "SELECT * FROM actor"))
		exit (1);

	if (! execute_sql (db1, " CREATE TABLE if not exists othertable (\
  othertable_id INTEGER PRIMARY KEY, first_name VARCHAR(45) NOT NULL, last_name VARCHAR(45) NOT NULL, \
  last_update TIMESTAMP NOT NULL);"))
		exit (1);

	if (! execute_sql (db1, "SELECT * FROM othertable"))
		exit (1);
	/*if (! execute_sql (db2, "SELECT 1 FROM sqlite_master LIMIT 1"))
	  exit (1);*/
	if (! execute_sql (db2, "SELECT * FROM othertable"))
		exit (1);
	
	sqlite3_close(db1);
	sqlite3_close(db2);
	return 0;
}
-
To unsubscribe, send email to [EMAIL PROTECTED]
-

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

[drh]

"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 execute "SELECT * FROM actor" => Ok
> 3- on C2 execute "SELECT * FROM actor" => error because table "actor"
> does not exist.
> 

When C2 goes to parse the SQL in statement 3, it does not know
that the database schema has changed because it has not attempted
to access the database file.  Thus it does not know that the new
table exists.

To fix this, you have to get C2 to access the database so that
it will reread and reparse the schema and thus discover the new
table.  Perhaps something like this:

   2.5- on C2 execute "SELECT 1 FROM sqlite_master LIMIT 1"

--
D. Richard Hipp <[EMAIL PROTECTED]>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] SQLITE_CORRUPT recover

[drh]

"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 NEW.DB

But that doesn't always work either.  The best approach
is to avoid corruption in the first place.
--
D. Richard Hipp <[EMAIL PROTECTED]>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

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

[Matteo Vescovi]

--- 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'.
> 
> The WHERE word = "word" is a no-op.  From "SQLite
> Keywords" section of
> http://www.sqlite.org/lang.html:
> 
> 'keyword'   A keyword in single quotes is
> interpreted as a literal
> string if it occurs in a context where a
> string literal is
> allowed, otherwise it is understood as
> an identifier.
> 
> "keyword"   A keyword in double-quotes is
> interpreted as an identifier
> if it matches a known identifier.
> Otherwise it is
> interpreted as a string literal.
> 

That explains it. I was missing something, after all.

Thanks for pointing me to the documentation and sorry
for the noise.

- Matteo

> 
> -- 
>Tomash Brechko
> 
>
-
> To unsubscribe, send email to
> [EMAIL PROTECTED]
>
-
> 
> 



  ___ 
Yahoo! Mail is the world's favourite email. Don't settle for less, sign up for
your free account today 
http://uk.rd.yahoo.com/evt=44106/*http://uk.docs.yahoo.com/mail/winter07.html 

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

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

[Tomash Brechko]

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
http://www.sqlite.org/lang.html:

'keyword'   A keyword in single quotes is interpreted as a literal
string if it occurs in a context where a string literal is
allowed, otherwise it is understood as an identifier.

"keyword"   A keyword in double-quotes is interpreted as an identifier
if it matches a known identifier. Otherwise it is
interpreted as a string literal.


-- 
   Tomash Brechko

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

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

[Matteo Vescovi]

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 (word TEXT, count INTEGER,
UNIQUE(word) );
INSERT INTO _1_gram VALUES("foo", 13);
INSERT INTO _1_gram VALUES("bar", 16);
INSERT INTO _1_gram VALUES("word", 36);
INSERT INTO _1_gram VALUES("foobar", 336);
[EMAIL PROTECTED]:~$ sqlite test.db < populate.sql
[EMAIL PROTECTED]:~$ sqlite test.db
SQLite version 2.8.17
Enter ".help" for instructions
sqlite> SELECT * FROM _1_gram WHERE word = 'word';
word|36
sqlite> SELECT * FROM _1_gram WHERE word = "word";
foo|13
bar|16
word|36
foobar|336
sqlite> .quit
[EMAIL PROTECTED]:~$

Am I missing something here (I haven't used SQL in a
while...), or is this a bug?

Cheers,
- Matteo Vescovi




___ 
All New Yahoo! Mail – Tired of unwanted email come-ons? Let our SpamGuard 
protect you. http://uk.docs.yahoo.com/nowyoucan.html

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

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

[Mohd Radzi Ibrahim]

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 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 "SELECT * FROM actor" => error because table "actor"
does not exist.

Is there a way to make this sequence of operations work, or should I
make sure one can never open two connections on the same DB file in
the same process?

Thanks,

Vivien

-
To unsubscribe, send email to [EMAIL PROTECTED]
-






-
To unsubscribe, send email to [EMAIL PROTECTED]
-

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

[Martin Jenkins]

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" => Ok
3- on C2 execute "SELECT * FROM actor" => error because table "actor"
does not exist.

Is there a way to make this sequence of operations work


What SQLite version/API are you using?

ISTR this was fixed some time ago - you need to use sqlite3_prepare_v2() 
instead of sqlite3_prepare().


AIUI, pre the v2 API, connections cache a copy of the schema and aren't 
aware of changes made by other connections, so your case would result in 
a schema changed error. The caller would detect this and re-prepare and 
re-execute the statement. Since this was a common error case the _v2 API 
was created. It stores a copy of the statement's SQL and automatically 
re-prepares it if a schema change is encountered.


I don't know offhand when this was added, but I assume it was ages ago 
because it works with Python's wrapper and that uses SQLite v3.3.4.


>>> import sqlite3
>>> c1=sqlite3.Connection("c:\\fred")
>>> c2=sqlite3.Connection("c:\\fred")
>>> c1.execute("create table t(a,b,c)")

>>> c1.execute("select * from t")

>>> c1.execute("select * from t").fetchall()
[]
>>> c2.execute("select * from t").fetchall()
[]
>>> sqlite3.sqlite_version
'3.3.4'
>>>

Martin



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

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

[Vivien Malerba]

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 "SELECT * FROM actor" => error because table "actor"
does not exist.

Is there a way to make this sequence of operations work, or should I
make sure one can never open two connections on the same DB file in
the same process?

Thanks,

Vivien

-
To unsubscribe, send email to [EMAIL PROTECTED]
-