Re: [sqlite] The database disk image is malformed on windows mobile system.

At 2010-09-25，"Simon Slavin" wrote: > >On 25 Sep 2010, at 3:43am, Zhonghuifeng163 wrote: > >> I use sqlite 3.6.20 on windows mobile system,but the sqlite often return >> error message "The database disk image is malformed", then the sqlite >> database file can not be

Re: [sqlite] The database disk image is malformed on windows mobile system.

On 25 Sep 2010, at 3:43am, Zhonghuifeng163 wrote: > I use sqlite 3.6.20 on windows mobile system,but the sqlite often return > error message "The database disk image is malformed", then the sqlite > database file can not be used! I also use sqlite 3.6.20 on other operate > system on mobile

[sqlite] The database disk image is malformed on windows mobile system.

I use sqlite 3.6.20 on windows mobile system,but the sqlite often return error message "The database disk image is malformed", then the sqlite database file can not be used! I also use sqlite 3.6.20 on other operate system on mobile phone, and do not have this problem.

Re: [sqlite] FTS3 MATCH syntax

On Fri, Sep 24, 2010 at 7:16 PM, GHCS Software wrote: > I'm just started with generating a table using FTS3 and understand > most of the query formats, but am stuck on one minor point of syntax. My > FTS3 query will make reference to a specific table, e.g.: > > MATCH

Re: [sqlite] FTS3 MATCH syntax

I'm not an authority, but I've been using FTS3. FTS3 tokenizes strings on whitespace (and other chars), so I think the best you can do would be something like given:john given:q. It doesn't work really well out of the box for substring matching. On Fri, Sep 24, 2010 at 4:16 PM, GHCS Software

Re: [sqlite] Need help with self-join (I think)

Am 25.09.2010 01:47, schrieb Kristoffer Danielsson: [...] > > Because, given a certain algorithm, generating statistics will become a lot > easier if each value combination is represented in the returned row set. > really? NULL means there are no values present or there are unknown values -

Re: [sqlite] Need help with self-join (I think)

Yeah. USING is good to have :) And yes, 123 might very well be a random number. I should have made that clear! > To: sqlite-users@sqlite.org > From: oliver@web.de > Date: Sat, 25 Sep 2010 00:19:44 + > Subject: Re: [sqlite] Need help with self-join (I think) > > Kristoffer

Re: [sqlite] Need help with self-join (I think)

Kristoffer Danielsson writes: > > > Ah, this one's easier to follow. What do you mean by "in this case"? What was the condition that made it the point was not to remove something but to take into consideration that there has to be a JOIN on the TestIDs (in this case

Re: [sqlite] Need help with self-join (I think)

Ah, this one's easier to follow. What do you mean by "in this case"? What was the condition that made it possible to remove the rest? Again, thanks! > To: sqlite-users@sqlite.org > From: oliver@web.de > Date: Sat, 25 Sep 2010 00:05:11 + > Subject: Re: [sqlite] Need help with

Re: [sqlite] Need help with self-join (I think)

sry, a little mistake - here's the correction: SELECT DISTINCT t.Year, b.Name, (SELECT SomeValue FROM Test INNER JOIN Test2 WHERE Year = t.Year AND Name = b.Name) AS SomeValue FROM Test t CROSS JOIN (SELECT DISTINCT Name FROM Test) b ; (results don't differ - in this case!) Oliver

Re: [sqlite] Need help with self-join (I think)

Neat! Many thanks :) Putting this logic together with my original query will be an interesting challenge, hehe. Do you believe this is the best solution? Chris > To: sqlite-users@sqlite.org > From: oliver@web.de > Date: Fri, 24 Sep 2010 23:47:59 + > Subject: Re: [sqlite] Need

Re: [sqlite] Need help with self-join (I think)

Kristoffer Danielsson writes: SELECT DISTINCT t.Year, b.Name, CASE WHEN (SELECT TestID FROM Test WHERE Year = t.Year AND Name = b.Name) IS NULL THEN NULL ELSE (SELECT SomeValue FROM Test2) END AS SomeValue FROM Test t CROSS JOIN (SELECT DISTINCT Name FROM Test) b ;

Re: [sqlite] Need help with self-join (I think)

Hi, Thanks for your reply. You're right, it's an outer-join I'm looking for. Unfortunately, your suggestion does not do the trick. "You didn't have a Test1 row for (2007, 'C'), so why would you get 2007|C|NULL?" Because, given a certain algorithm, generating statistics will become a lot

Re: [sqlite] Need help with self-join (I think)

On Sat, Sep 25, 2010 at 01:01:36AM +0200, Kristoffer Danielsson wrote: > CREATE TABLE Test (TestID INTEGER PRIMARY KEY, Year INT NOT NULL, Name TEXT > NOT NULL); > INSERT INTO Test (Year, Name) VALUES (2007, 'A'); > INSERT INTO Test (Year, Name) VALUES (2007, 'B'); > INSERT INTO Test (Year, Name)

[sqlite] FTS3 MATCH syntax

I'm just started with generating a table using FTS3 and understand most of the query formats, but am stuck on one minor point of syntax. My FTS3 query will make reference to a specific table, e.g.: MATCH 'surname:smith' The problem is that I can't figure out how to specify it if the

[sqlite] Need help with self-join (I think)

Hi, I have an interesting SQL problem where I want certain rows to always be represented. It feels like a self-join, but I'm not sure. Please help! Create a database as follows: CREATE TABLE Test (TestID

Re: [sqlite] Query to compare two sqlite databases

Oliver Peters writes: sry > > the result is what is not in table01 > I meant: the result is what is in table01 but NOT in table02 Oliver ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] Query to compare two sqlite databases

Am 24.09.2010 20:41, schrieb John Reed: > Hello, > > I compare an application every few days which has sqlite as it's client > database. > I look at the content and check whether documents have made it into the > application after it has been built. I also check the metadata in the sqlite >

Re: [sqlite] Query to compare two sqlite databases

Hello, Thanks for your response. I'm almost there but I keep getting sql syntax error (near col1) with the last part of the query where(not exists in db2); Here's where I'm at: select 'db1', db1.table1.col1, 'db2', db2.table2.col1 from db1.table1, db2.table2 WHERE (NOT EXISTS col1 IN db2);

Re: [sqlite] Query to compare two sqlite databases

On Fri, 24 Sep 2010, luuk34 wrote: > you mean something like: > select id1, id2 from callprog a where id1 not in (select b.id1 FROM > callprog b where b.id1=a.id1 ); Yeah; much better. > But what is there is more than 1 column? it will grow in complexity when > you have a lot of

Re: [sqlite] Query to compare two sqlite databases

On 24-09-10 21:06, Rich Shepard wrote: > On Fri, 24 Sep 2010, John Reed wrote: > >> I compare an application every few days which has sqlite as it's client >> database. I look at the content and check whether documents have made it >> into the application after it has been built. I also check

Re: [sqlite] Query to compare two sqlite databases

On Fri, 24 Sep 2010, John Reed wrote: > I compare an application every few days which has sqlite as it's client > database. I look at the content and check whether documents have made it > into the application after it has been built. I also check the metadata in > the sqlite client database for

[sqlite] Query to compare two sqlite databases

Hello, I compare an application every few days which has sqlite as it's client database. I look at the content and check whether documents have made it into the application after it has been built. I also check the metadata in the sqlite client database for changes. So, I am constantly

Re: [sqlite] Help: Required help/guidance for the segmentation fault in sqlite3 library to proceed further...

On Fri, Sep 24, 2010 at 9:50 AM, Pavel Ivanov wrote: > Apparently you are calling sqlite3_free twice on the same statement > pointer. Try to add assigning to NULL after freeing and checking for > NULL before freeing. > The second step (checking for NULL before freeing) is

Re: [sqlite] Help: Required help/guidance for the segmentation fault in sqlite3 library to proceed further...

On Sep 23, 2010, at 9:57 PM, Borra, Kishore Babu wrote: > Hi, > > I require some help in getting the fix for some memory corruption > issues, occurring while using the sqlite3 library. It would be very > helpful, if you can guide to fix the below issues or atleast provide > some info on

Re: [sqlite] Help: Required help/guidance for the segmentation fault in sqlite3 library to proceed further...

Apparently you are calling sqlite3_free twice on the same statement pointer. Try to add assigning to NULL after freeing and checking for NULL before freeing. Pavel On Thu, Sep 23, 2010 at 10:57 AM, Borra, Kishore Babu wrote: > Hi, > > I require some help in getting

Re: [sqlite] Bug? "no such column" referring to a table by name, works by alias

Quoth Igor Tandetnik , on 2010-09-24 08:16:07 -0400: > sqlite> create table A (id integer primary key); > sqlite> create table B (id integer primary key, name text); > sqlite> select * from A as X order by (select name from B where B.id = A.id); > Error: no such column: A.id >

[sqlite] Bug? "no such column" referring to a table by name, works by alias

Consider this SQLite session: sqlite> create table A (id integer primary key); sqlite> create table B (id integer primary key, name text); sqlite> select * from A as X order by (select name from B where B.id = A.id); Error: no such column: A.id sqlite> select * from A as X order by (select name

[sqlite] Auto Reply: sqlite-users Digest, Vol 33, Issue 24

This is an auto-replied message. I am currently unavailable. I will be back at work on Monday 27th September. For urgent questions please contact Michael Brey (michael.b...@oracle.com) ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] DELETE OR IGNORE statement?

On Friday, September 24, 2010 1:03 PM, Josh Gibbs wrote: > What's the chance that 2 people with the same surname would have > the same problem in the same week... > > [ ... ] I can only think its something in the name! I did actually see your thread moments after creating my own. I'd done a

Re: [sqlite] DELETE OR IGNORE statement?

What's the chance that 2 people with the same surname would have the same problem in the same week... I believe I just solved the same problem you are asking about yesterday thanks to a query from Richard: CREATE TABLE Message (message_id INTEGER PRIMARY KEY AUTOINCREMENT, >>> Subject TEXT);

[sqlite] Help: Required help/guidance for the segmentation fault in sqlite3 library to proceed further...

Hi, I require some help in getting the fix for some memory corruption issues, occurring while using the sqlite3 library. It would be very helpful, if you can guide to fix the below issues or atleast provide some info on this, to minimize the memory corruption, occurring mostly while

[sqlite] Required clarification for the segmentation fault in sqlite3 library..

Hi, I require some help in getting the fix for some memory corruption issues, occurring while using the sqlite3 library. It would be very helpful, if you can guide to fix the below issues or atleast provide some info on this, to minimize the memory corruption, occurring mostly while

Re: [sqlite] COUNT very slow

I can get a big speed up of COUNT if I first do a VIEW of what I have to count and than make select COUNT on the view. Without VIEW: 9 Minutes With VIEW: 8 Seconds! Il 24/09/2010 10.58, Martin Engelschalk ha scritto: > > Am 24.09.2010 10:38, schrieb Michele Pradella: >> ok, thank you for

Re: [sqlite] COUNT very slow

Am 24.09.2010 10:38, schrieb Michele Pradella: >ok, thank you for the advices, I'll try to use a TRIGGER. > The DB already has an index. > Anyway if I have to count something like this: > select COUNT(*) from logs WHERE DateTime<=yesterday > I can't do it with a TRIGGER No, but in this case

Re: [sqlite] COUNT very slow

ok, thank you for the advices, I'll try to use a TRIGGER. The DB already has an index. Anyway if I have to count something like this: select COUNT(*) from logs WHERE DateTime<=yesterday I can't do it with a TRIGGER Il 24/09/2010 10.29, Martin Engelschalk ha scritto: >Hello Michele, > >

Re: [sqlite] COUNT very slow

Quoth Michele Pradella , on 2010-09-24 10:13:59 +0200: > I have an SQLite DB of about 9GB with about 2.500.000 records. > I can't understand why the "select COUNT(*) from log" statement is > extremely slow, it takes me about 9-10 minutes! > I try with: > select

Re: [sqlite] COUNT very slow

Hello Michele, sqlite does not remember the number of records in a table. Therefore, counting them requires to scan the full table, which explains the slow perfornamce. This topic has been discussed previously in this list. See

Re: [sqlite] COUNT very slow

On Sep 24, 2010, at 3:13 PM, Michele Pradella wrote: > I have an SQLite DB of about 9GB with about 2.500.000 records. > I can't understand why the "select COUNT(*) from log" statement is > extremely slow, it takes me about 9-10 minutes! In SQLite, count() is obliged to traverse the entire

[sqlite] COUNT very slow

I have an SQLite DB of about 9GB with about 2.500.000 records. I can't understand why the "select COUNT(*) from log" statement is extremely slow, it takes me about 9-10 minutes! I try with: select COUNT(1) from logs select COUNT(DateTime) from logs same result. Have you idea of why it's so

Re: [sqlite] Performance problems and large memory size

I read about optimization for page_size, and I'll try to use different size to check if I got speed up with page size 4096 instead of 1024. Anyway, did you make some tests about this speed up? which operation are faster with 4096 page size: Select, insert or delete? Il 22/09/2010 20.19, Max