Re: [sqlite] Fortran 95 Language Bindings
Gary Scott wrote: Hi, CVF initially. I will likely transition to IVF once a suitable version is released. Well, that should be easy enough - MSVC/CVF is the platform I used to develop the interface. I managed to extend the documentation on the interface last night - see http://flibs.sf.net - actually it took me an embarrassingly small amount of time to fill in the big gap I had left there. If there is anything unclear about it, let me know and I will try to fix it. Also, if you see any obvious omissions from the full SQLite API, let me know. The best way forward would be a real project, rather than any of the toys I have dabbled with. Regards, Arjen - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] SQLite Performance
Hi I've found SQLite faster than MySQL and Postgres for small/medium databases. Now I have big ones and I really do not want to change, but I have some performance issues. Consider the following database schema: CREATE TABLE tetragrams (word1 INTEGER, word2 INTEGER, word3 INTEGER, word4 INTEGER, occs INTEGER, PRIMARY KEY (word1, word2, word3, word4)); CREATE INDEX tet_b ON tetragrams (word2); CREATE INDEX tet_c ON tetragrams (word3); CREATE INDEX tet_d ON tetragrams (word4); And the following database size: sqlite SELECT COUNT(*) from tetragrams; 18397532 Now, a query like SELECT FROM tetragrams WHERE word1 = 6; returns 166579 rows; This query takes some time, but starts as soon as I type the query. The problem is that I want to use: SELECT * FROM tetragrams WHERE word1=6 ORDER BY occs DESC LIMIT 10; and it takes.. five minutes and did not give the result yet... Is there anything I can do to make it speed up CONSIDERABLY? I mean, this is to be used in a CGI and each CGI query will make 11 queries like the one above to the database. Thank you in advance, Alberto -- Alberto Simões - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SQLite Performance
On Tue, 2007-04-17 at 11:53 +0100, Alberto Simões wrote: Hi I've found SQLite faster than MySQL and Postgres for small/medium databases. Now I have big ones and I really do not want to change, but I have some performance issues. Consider the following database schema: CREATE TABLE tetragrams (word1 INTEGER, word2 INTEGER, word3 INTEGER, word4 INTEGER, occs INTEGER, PRIMARY KEY (word1, word2, word3, word4)); CREATE INDEX tet_b ON tetragrams (word2); CREATE INDEX tet_c ON tetragrams (word3); CREATE INDEX tet_d ON tetragrams (word4); And the following database size: sqlite SELECT COUNT(*) from tetragrams; 18397532 Now, a query like SELECT FROM tetragrams WHERE word1 = 6; returns 166579 rows; This query takes some time, but starts as soon as I type the query. The problem is that I want to use: SELECT * FROM tetragrams WHERE word1=6 ORDER BY occs DESC LIMIT 10; and it takes.. five minutes and did not give the result yet... Is there anything I can do to make it speed up CONSIDERABLY? I mean, this is to be used in a CGI and each CGI query will make 11 queries like the one above to the database. You might need an index like: CREATE INDEX tet_e ON tetragrams(word1, occs); Otherwise you have to sort the 166000 items each time the query is made. Dan. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Optimize a query
Marco Bambini wrote: This query on a small database sometimes takes more than 40 seconds: select _rowid, public_id, vote_count, status, summary, component, date(date_modified), quickfix from reports where public = 1 AND _rowid IN (select distinct r._rowid from reports r, segments s where s.report_id = r._rowid AND r.public = 1 AND s.public = 1 AND (r.summary LIKE '%server%' OR s.content LIKE '%server%')) order by vote_count DESC I'm no expert, but won't this bit LIKE '%server%' OR s.content LIKE '%server%')) order by vote_count force the query to perform a full table scan regardless of the indexes? What happens to the query speed if you (temporarily) change the LIKE clauses to look for a specific record? Martin - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SQLite Performance
On 4/17/07, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: =?ISO-8859-1?Q?Alberto_Sim=F5es?= [EMAIL PROTECTED] wrote: Consider the following database schema: CREATE TABLE tetragrams (word1 INTEGER, word2 INTEGER, word3 INTEGER, word4 INTEGER, occs INTEGER, PRIMARY KEY (word1, word2, word3, word4)); CREATE INDEX tet_b ON tetragrams (word2); CREATE INDEX tet_c ON tetragrams (word3); CREATE INDEX tet_d ON tetragrams (word4); The problem is that I want to use: SELECT * FROM tetragrams WHERE word1=6 ORDER BY occs DESC LIMIT 10; and it takes.. five minutes and did not give the result yet... Is there anything I can do to make it speed up CONSIDERABLY? CREATE INDEX tet_e ON tetragrams(word1, occs); Hmms, Yes, it works as expected and speeds up to about one second, or less :) Thank you, Alberto -- Alberto Simões - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Insert order maintained?
Hi I would like to know if the order I get the rows from a select (without ORDER BY) is the order by which the values were inserted. I know this is the behavior for MySQL, but not sure about SQLite. THank you Alberto -- Alberto Simões - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Insert order maintained?
Additionally, note that if you use ORDER BY, and it _is_ in the indicated order already, then sqlite will optimize the ORDER BY away entirely. So use ORDER BY. -scott On 4/17/07, Andrew Finkenstadt [EMAIL PROTECTED] wrote: The order of the rows returned by a select that does not have an ORDER BY clause is guaranteed by the standard to be in any arbitrary order, even from one execution to another due to changes in the underlying data, index statistics, amount of memory available, or even the phase of the moon. Use an ORDER BY clause if order matters to you. --andy On 4/17/07, Alberto Simões [EMAIL PROTECTED] wrote: Hi I would like to know if the order I get the rows from a select (without ORDER BY) is the order by which the values were inserted. I know this is the behavior for MySQL, but not sure about SQLite. THank you Alberto -- Alberto Simões - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Insert order maintained?
We got bit by this when moving from MSSQL 2000 to MSSQL 2005. MSSQL returned rows by default in PK order and one of our former developers depended on this so when the behavior changed in MSSQL 2005 (which is fine 'cause it wasn't documented or expected behavior) our app broke in unexpected ways. With any db it's always best to specify an ORDER BY if you care about the order. Sam --- We're Hiring! Seeking a passionate developer to join our team building products. Position is in the Washington D.C. metro area. If interested contact [EMAIL PROTECTED] -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 17, 2007 11:18 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Insert order maintained? I know this is the behavior for MySQL, but not sure about SQLite. I'v heard about some version of mysql that didn't return rows in the same order (but haven't seen it myselt). So unless this behaviour is documented in mysql manual, it's not a good idea to rely on this. Actually I'v seen only 1 database that allways returns rows in random order, but it doesn't mean that other databases guarantee anything. -- Jak bedzie wygladac koniec swiata? Zobacz http://link.interia.pl/f1a38 - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Insert order maintained?
I know this is the behavior for MySQL, but not sure about SQLite. I'v heard about some version of mysql that didn't return rows in the same order (but haven't seen it myselt). So unless this behaviour is documented in mysql manual, it's not a good idea to rely on this. Actually I'v seen only 1 database that allways returns rows in random order, but it doesn't mean that other databases guarantee anything. -- Jak bedzie wygladac koniec swiata? Zobacz http://link.interia.pl/f1a38 - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Optimize a query
This is news to me. Why can't SQlite use more than one index? Samuel R. Neff wrote: afaik SQLite will only use one index per table -- Martin Pelletier Informatique / Software Development Infodev Electronic Designers International Inc. Tel : +1 (418) 681-3539, poste /ext. 114 Fax : +1 (418) 681-1209 - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Optimize a query
At 16:46 17/04/2007, you wrote: This is news to me. Why can't SQlite use more than one index? Possibly because it's 'SQ *Lite*'? The query optimiser in SQLite is a lot less powerful than in some other SQL databases - but then it's a fraction of the size as well... Instead of having two indices on columns A and B, you need to consider having another index on both columns at once. PaulVPOP3 - Internet Email Server/Gateway [EMAIL PROTECTED] http://www.pscs.co.uk/ - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Is this a valid syntax
At 16:45 17/04/2007, Stef Mientki wrote: I don't understand this behaviour, is this too complex ? or am I doing something wrong ? I use the following syntax, and I get 7 records back, (which is not correct in my opinion) SELECT PO.* FROM Koppel LEFT JOIN PO WHERE (Koppel.K_App == PO.App) AND (Koppel.K_naam == 'MVE') AND (PO.ALL_answered == '0') SELECT PO.* FROM Koppel LEFT JOIN PO WHERE (Koppel.K_App == PO.App) AND (Koppel.K_naam == 'MVE') AND (PO.ALL_answered != '0') I don't know if this is the problem, but, for some reason you're mixing C/C++ syntax in with SQL there. You don't use '==', you should just use '=' You don't use '!=', you should use '' So, try SELECT PO.* FROM Koppel LEFT JOIN PO WHERE (Koppel.K_App = PO.App) AND (Koppel.K_naam = 'MVE') AND (PO.ALL_answered '0') PaulVPOP3 - Internet Email Server/Gateway [EMAIL PROTECTED] http://www.pscs.co.uk/ - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Is this a valid syntax
I don't understand this behaviour, is this too complex ? or am I doing something wrong ? I use the following syntax, and I get 7 records back, (which is not correct in my opinion) SELECT PO.* FROM Koppel LEFT JOIN PO WHERE (Koppel.K_App == PO.App) AND (Koppel.K_naam == 'MVE') AND (PO.ALL_answered == '0') If I leave the last line out, I get 16 records (which might be ok, I can't check it) SELECT PO.* FROM Koppel LEFT JOIN PO WHERE (Koppel.K_App == PO.App) AND (Koppel.K_naam == 'MVE') Now if I only invert the last line, and suposing the above results where ok (which isn't), I should get 16-7= 9 records back. SELECT PO.* FROM Koppel LEFT JOIN PO WHERE (Koppel.K_App == PO.App) AND (Koppel.K_naam == 'MVE') AND (PO.ALL_answered != '0') But this just returns 0 records I tried all kinds of combinations, nested select statements etc, but most of them (may be all, can't remember anymore), returns 16-0-7 records. Sorry, I must be doing something terrible wrong, but I don't see the clue. thanks, Stef Mientki Kamer van Koophandel - handelsregister 41055629 / Netherlands Chamber of Commerce - trade register 41055629 - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Is this a valid syntax
Stef Mientki [EMAIL PROTECTED] wrote: I use the following syntax, and I get 7 records back, (which is not correct in my opinion) SELECT PO.* FROM Koppel LEFT JOIN PO WHERE (Koppel.K_App == PO.App) AND (Koppel.K_naam == 'MVE') AND (PO.ALL_answered == '0') If I leave the last line out, I get 16 records (which might be ok, I can't check it) Left join would produce records with all NULLs in the PO half where no record in PO matches that in Koppel. The test of (PO.ALL_answered == '0') then filters out those records where PO.ALL_answered is NULL. Make it SELECT PO.* FROM Koppel LEFT JOIN PO ON (Koppel.K_App == PO.App) AND (Koppel.K_naam == 'MVE') AND (PO.ALL_answered == '0') Now if I only invert the last line, and suposing the above results where ok (which isn't), I should get 16-7= 9 records back. When PO.ALL_answered is NULL, both (PO.ALL_answered == '0') and (PO.ALL_answered != '0') are false. But this just returns 0 records Apparently, in all rows PO.ALL_answered is either '0' or NULL. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Optimize a query
Martin Pelletier [EMAIL PROTECTED] wrote: This is news to me. Why can't SQlite use more than one index? It can. You just have to tell it to explicitly by restructuring your SQL. As an example, consider this query: SELECT * FROM table1 WHERE a=5 AND b=11; Suppose there are two indices: CREATE INDEX index1 ON table1(a); CREATE INDEX index2 ON table1(b); As written, SQLite will only use one of these two indices to perform the query. The choice is arbitrary (unless you have run ANALYZE and SQLite has some information to help it pick the best index.) If you want to use both indices, rewrite the query this way: SELECT * FROM table1 WHERE rowid IN (SELECT rowid FROM table1 WHERE a=5 INTERSECT SELECT rowid FROM table1 WHERE b=11); The optimizer in PostgreSQL will make this change for you automatically and will use a bitmap to implement the IN operator and the INTERSECT. With SQLite, though, you have to type in the expanded version yourself. And because rowids in SQLite are user visible and changeable and can thus be diffuse, SQLite is unable to use bitmaps to optimize the computation. But modulo the bitmap optimization, SQLite gives you all the capabilities of PostgreSQL, you just have to type it in yourself rather than letting the optimizer do it for you. -- D. Richard Hipp [EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Insert order maintained?
The order of the rows returned by a select that does not have an ORDER BY clause is guaranteed by the standard to be in any arbitrary order, even from one execution to another due to changes in the underlying data, index statistics, amount of memory available, or even the phase of the moon. Use an ORDER BY clause if order matters to you. --andy On 4/17/07, Alberto Simões [EMAIL PROTECTED] wrote: Hi I would like to know if the order I get the rows from a select (without ORDER BY) is the order by which the values were inserted. I know this is the behavior for MySQL, but not sure about SQLite. THank you Alberto -- Alberto Simões - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Optimize a query
This query on a small database sometimes takes more than 40 seconds: select _rowid, public_id, vote_count, status, summary, component, date (date_modified), quickfix from reports where public = 1 AND _rowid IN (select distinct r._rowid from reports r, segments s where s.report_id = r._rowid AND r.public = 1 AND s.public = 1 AND (r.summary LIKE '%server%' OR s.content LIKE '%server%')) order by vote_count DESC Table reports contains 22,605 records Table segments contains 71,413 records I suspect that the slowdown is due to the IN clause used in the query or something else that prevents sqlite from using some optimizations. Anyone can help me to optimize this query? Details follows... TABLES: CREATE TABLE reports (_rowid integer NOT NULL PRIMARY KEY, vote_count integer DEFAULT 0, summary varchar(256), public integer DEFAULT 0, date_created date, locked_by integer DEFAULT 0, public_id varchar (16), component integer, severity varchar(32), priority integer DEFAULT 0, user_id integer, release_note_id integer DEFAULT 0, type integer DEFAULT 0, date_modified timestamp, fixed_version varchar (16), status integer DEFAULT 0, quickfix integer DEFAULT 0, easyfix integer DEFAULT 0) CREATE TABLE segments (_rowid integer not null primary key autoincrement, type integer, public integer default 0, date_created date, content varchar(4096), date_modified date, report_id integer, user_id integer) INDEXES: CREATE INDEX reports_component_idx on reports (component) CREATE UNIQUE INDEX reports_public_id_idx on reports (public_id) CREATE INDEX reports_public_idx on reports (public) CREATE INDEX reports_status_idx on reports (status) CREATE INDEX reports_summary_idx on reports (summary) CREATE INDEX reports_user_id_idx on reports (user_id) CREATE INDEX segments_content on segments (content) CREATE INDEX segments_public on segments (public) CREATE INDEX segments_report_id on segments (report_id) CREATE INDEX segments_type on segments (type) CREATE INDEX segments_user_id on segments (user_id) Thanks a lot, --- Marco Bambini - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Optimize a query
afaik SQLite will only use one index per table so if you have a where clause WHERE public = 1 and _rowid IN (...) it will use an index on public and not _rowid. Swapping the where clause around should have a significant impact: select _rowid, public_id, vote_count, status, summary, component, date (date_modified), quickfix from reports where _rowid IN ( select distinct r._rowid from reports r, segments s where s.report_id = r._rowid AND r.public = 1 AND s.public = 1 AND (r.summary LIKE '%server%' OR s.content LIKE '%server%') ) AND public = 1 order by vote_count DESC but really the public = 1 where clause on the outer query doesn't look necessary since you already are checking public = 1 in the inner query. Also, formatting SQL statements so they aren't a huge blob of sql will make them easier to read, especially for mailing lists. :-) HTH, Sam --- We're Hiring! Seeking a passionate developer to join our team building products. Position is in the Washington D.C. metro area. If interested contact [EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Optimize a query
Interesting and thanks for that tip. Is there a performance penalty from structuring the query like that? I take it that there will be. RBS Martin Pelletier [EMAIL PROTECTED] wrote: This is news to me. Why can't SQlite use more than one index? It can. You just have to tell it to explicitly by restructuring your SQL. As an example, consider this query: SELECT * FROM table1 WHERE a=5 AND b=11; Suppose there are two indices: CREATE INDEX index1 ON table1(a); CREATE INDEX index2 ON table1(b); As written, SQLite will only use one of these two indices to perform the query. The choice is arbitrary (unless you have run ANALYZE and SQLite has some information to help it pick the best index.) If you want to use both indices, rewrite the query this way: SELECT * FROM table1 WHERE rowid IN (SELECT rowid FROM table1 WHERE a=5 INTERSECT SELECT rowid FROM table1 WHERE b=11); The optimizer in PostgreSQL will make this change for you automatically and will use a bitmap to implement the IN operator and the INTERSECT. With SQLite, though, you have to type in the expanded version yourself. And because rowids in SQLite are user visible and changeable and can thus be diffuse, SQLite is unable to use bitmaps to optimize the computation. But modulo the bitmap optimization, SQLite gives you all the capabilities of PostgreSQL, you just have to type it in yourself rather than letting the optimizer do it for you. -- D. Richard Hipp [EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SQLite Performance
=?ISO-8859-1?Q?Alberto_Sim=F5es?= [EMAIL PROTECTED] wrote: Consider the following database schema: CREATE TABLE tetragrams (word1 INTEGER, word2 INTEGER, word3 INTEGER, word4 INTEGER, occs INTEGER, PRIMARY KEY (word1, word2, word3, word4)); CREATE INDEX tet_b ON tetragrams (word2); CREATE INDEX tet_c ON tetragrams (word3); CREATE INDEX tet_d ON tetragrams (word4); The problem is that I want to use: SELECT * FROM tetragrams WHERE word1=6 ORDER BY occs DESC LIMIT 10; and it takes.. five minutes and did not give the result yet... Is there anything I can do to make it speed up CONSIDERABLY? CREATE INDEX tet_e ON tetragrams(word1, occs); -- D. Richard Hipp [EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Still getting Insertion failed because database is full. errors
The saga continues... I was very excited by the idea that there was something wrong with the CF Card. The theory fits all the facts: it explains why the original database threw unspecified errors, it explains why now SQLite is throwing errors, it explains why I can't reproduce the problem in house or on my machine. It just seemed to explain everything, so yesterday I went out and bought a brand-spankin' new SanDisk CF card. I loaded it up with the database, installed it on my tester's machine, and this morning it went back out to the field for testing. Within minutes, he encountered the same error. Now I just don't believe the problem is with the card, so I feel that I am right back at square one. I'm really at my wits end and don't know what to do next. I am going to go ahead and install the database on the device memory instead of removable media, just to test it out, but I have no faith that it will change anything. When that fails, I will send the tester out with another device entirely, but again I expect the same results. I'm convinced now that the problem is with the application architecture, but I have no idea what to look at anymore. I've stared and fiddled with this code so much that I'm ready to throw in the towel. But since I'd like to keep my job that isn't an option. If I had hair, I'd pull it out. Any help at all would be appreciated. -- Joel Cochran On 4/13/07, Michael Ruck [EMAIL PROTECTED] wrote: Unless things have changed recently, the following should still be valid for Windows Mobile/Windows CE devices: Usually these devices do not power off, but stay in a standby state where the memory is always powered. Check if that's the case with your system and move as much as possible into RAM or a RAM disk, if that feature is provided by the windows mobile kernel built for your device. If that's not possible, I'd suggest replacing CF cards with micro drives - these are regular hard drives in a CF card format. I'm not up to date on storage space, but should be sufficient for your needs. To test the cards I'd put them in a card reader format it and fill it completely up with zeros. When a flash card erases a byte, it sets all bits to ones and upon write clears those, which need to be zero. So to test all bits you really need to zero out the entire card. This will also give the controller in the card a chance to remap bad sectors with spares. Finally you determine the file size of the card, when you receive the first write error. This is (approximately) the number of bytes the card can store (at that point in time) and falling. It seems some cards even return read errors, when they hit a defective sector upon read. Maybe the actual error code just gets lost/mangled on the way up and the actual error is just a simple read error ;) I've seen reports about this with some digital cameras, which would not even let people view the pictures taken a minute ago. Mike -Ursprüngliche Nachricht- Von: John Stanton [mailto:[EMAIL PROTECTED] Gesendet: Freitag, 13. April 2007 23:44 An: sqlite-users@sqlite.org Betreff: Re: [sqlite] Still getting Insertion failed because database is full. errors You might find some joy in the baby disk drives such as installed in the original ipods. Can you substitute RAM with a battery backup if the memory card is always in the device? Joel Cochran wrote: Thanks John and Dennis, At least now I have something to look at. I will look into the CF problem next. The database itself gets generated on a PC and then transferred to the CF Card. During testing and development, this could have been 20-30 times a day, constantly erasing and recreating the existing DB. We have also sent large numbers of JPGs along with the database in the past (there are none now, but have been before). So these cards have been written over a lot, perhaps that is the problem. I think to test this, I will send the device back to the field with a brand new card and see if the problem persists. If the user can go several days of normal use without the problem, then I'll be convinced that it is the card. Out of curiosity I just checked the CF cards we've been using: on the development machine (which has NEVER shown the error) I have a SanDisk CF Card. On the Testing machine that is having the problem, there is a PNY Technologies CF Card. I wouldn't be surprised if the SanDisk card isn't simply better than the PNY card, so there is something else to consider. Once actual field use begins, the database will be replaced every week or so, along with a fair number of images (like 100-300 a week). The purpose of the application would have every record in the database being updated and some new ones created. And it would be that way week in and week out, essentially forever. We may eventually port it over to very small Tablet PCs, but right now it is all Windows Mobile 5. This is one of the reasons I went with SQLite, so that down the road I
[sqlite] what do I compile with -dthreadsafe=1
Hi, when I read the article about sqlite and multithread in cvstrac, I understood that I need to compile my source files using sqlite functions with -DTHREADSAFE=1. Now, due to a pproblem I had in my project I re-readad this article and began to think I should compile sqlite source files using -DTHREADSAFE=1, which I did not do yet. So I'm confused. Where do i use this? compile sqlite sources? my sources? both? In case of sqlite sources, where do I put this -DTHREADSAFE=1? in a specific makefile? in all makefiles? Please help. I have linux and using sqlite 3.3.12. Thanks, Rafi.
Re: [sqlite] Still getting Insertion failed because database is full. errors
What is the guy on in the field doing that you are not? Are you using his device for the testing? Since it takes minutes for him to encounter the error it can't be that hard to recreate. Follow him around for an hour or so and see how he uses the program. It could easily be something he's doing that you aren't... On 4/17/07, Joel Cochran [EMAIL PROTECTED] wrote: The saga continues... I was very excited by the idea that there was something wrong with the CF Card. The theory fits all the facts: it explains why the original database threw unspecified errors, it explains why now SQLite is throwing errors, it explains why I can't reproduce the problem in house or on my machine. It just seemed to explain everything, so yesterday I went out and bought a brand-spankin' new SanDisk CF card. I loaded it up with the database, installed it on my tester's machine, and this morning it went back out to the field for testing. Within minutes, he encountered the same error. Now I just don't believe the problem is with the card, so I feel that I am right back at square one. I'm really at my wits end and don't know what to do next. I am going to go ahead and install the database on the device memory instead of removable media, just to test it out, but I have no faith that it will change anything. When that fails, I will send the tester out with another device entirely, but again I expect the same results. I'm convinced now that the problem is with the application architecture, but I have no idea what to look at anymore. I've stared and fiddled with this code so much that I'm ready to throw in the towel. But since I'd like to keep my job that isn't an option. If I had hair, I'd pull it out. Any help at all would be appreciated. -- Joel Cochran On 4/13/07, Michael Ruck [EMAIL PROTECTED] wrote: Unless things have changed recently, the following should still be valid for Windows Mobile/Windows CE devices: Usually these devices do not power off, but stay in a standby state where the memory is always powered. Check if that's the case with your system and move as much as possible into RAM or a RAM disk, if that feature is provided by the windows mobile kernel built for your device. If that's not possible, I'd suggest replacing CF cards with micro drives - these are regular hard drives in a CF card format. I'm not up to date on storage space, but should be sufficient for your needs. To test the cards I'd put them in a card reader format it and fill it completely up with zeros. When a flash card erases a byte, it sets all bits to ones and upon write clears those, which need to be zero. So to test all bits you really need to zero out the entire card. This will also give the controller in the card a chance to remap bad sectors with spares. Finally you determine the file size of the card, when you receive the first write error. This is (approximately) the number of bytes the card can store (at that point in time) and falling. It seems some cards even return read errors, when they hit a defective sector upon read. Maybe the actual error code just gets lost/mangled on the way up and the actual error is just a simple read error ;) I've seen reports about this with some digital cameras, which would not even let people view the pictures taken a minute ago. Mike -Ursprüngliche Nachricht- Von: John Stanton [mailto:[EMAIL PROTECTED] Gesendet: Freitag, 13. April 2007 23:44 An: sqlite-users@sqlite.org Betreff: Re: [sqlite] Still getting Insertion failed because database is full. errors You might find some joy in the baby disk drives such as installed in the original ipods. Can you substitute RAM with a battery backup if the memory card is always in the device? Joel Cochran wrote: Thanks John and Dennis, At least now I have something to look at. I will look into the CF problem next. The database itself gets generated on a PC and then transferred to the CF Card. During testing and development, this could have been 20-30 times a day, constantly erasing and recreating the existing DB. We have also sent large numbers of JPGs along with the database in the past (there are none now, but have been before). So these cards have been written over a lot, perhaps that is the problem. I think to test this, I will send the device back to the field with a brand new card and see if the problem persists. If the user can go several days of normal use without the problem, then I'll be convinced that it is the card. Out of curiosity I just checked the CF cards we've been using: on the development machine (which has NEVER shown the error) I have a SanDisk CF Card. On the Testing machine that is having the problem, there is a PNY Technologies CF Card. I wouldn't be surprised if the SanDisk card isn't simply better than the PNY card, so there is something else to consider. Once actual field use begins, the database will be
Re: [sqlite] Insert order maintained?
Ok, I'll use ORDER BY. Thanks! Alberto On 4/17/07, Samuel R. Neff [EMAIL PROTECTED] wrote: We got bit by this when moving from MSSQL 2000 to MSSQL 2005. MSSQL returned rows by default in PK order and one of our former developers depended on this so when the behavior changed in MSSQL 2005 (which is fine 'cause it wasn't documented or expected behavior) our app broke in unexpected ways. With any db it's always best to specify an ORDER BY if you care about the order. Sam --- We're Hiring! Seeking a passionate developer to join our team building products. Position is in the Washington D.C. metro area. If interested contact [EMAIL PROTECTED] -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 17, 2007 11:18 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Insert order maintained? I know this is the behavior for MySQL, but not sure about SQLite. I'v heard about some version of mysql that didn't return rows in the same order (but haven't seen it myselt). So unless this behaviour is documented in mysql manual, it's not a good idea to rely on this. Actually I'v seen only 1 database that allways returns rows in random order, but it doesn't mean that other databases guarantee anything. -- Jak bedzie wygladac koniec swiata? Zobacz http://link.interia.pl/f1a38 - To unsubscribe, send email to [EMAIL PROTECTED] - -- Alberto Simões - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Is this a valid syntax
I don't know if this is the problem, but, for some reason you're mixing C/C++ syntax in with SQL there. You don't use '==', you should just use '=' You don't use '!=', you should use '' thanks Paul, but although I can never find this information when I need it :-( AFAIK, both notations are allowed. Besides that I tried both and it doesn't change the situation. cheers, Stef - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: Is this a valid syntax
Igor Tandetnik wrote: Stef Mientki [EMAIL PROTECTED] wrote: I use the following syntax, and I get 7 records back, (which is not correct in my opinion) SELECT PO.* FROM Koppel LEFT JOIN PO WHERE (Koppel.K_App == PO.App) AND (Koppel.K_naam == 'MVE') AND (PO.ALL_answered == '0') If I leave the last line out, I get 16 records (which might be ok, I can't check it) Left join would produce records with all NULLs in the PO half where no record in PO matches that in Koppel. Thanks Igor, I think you hit the nail on it's head. Because the tables were quit large, I imported some of tables just partially. I'll check tomorrow. The test of (PO.ALL_answered == '0') then filters out those records where PO.ALL_answered is NULL. Make it SELECT PO.* FROM Koppel LEFT JOIN PO ON (Koppel.K_App == PO.App) AND (Koppel.K_naam == 'MVE') AND (PO.ALL_answered == '0') Now if I only invert the last line, and suposing the above results where ok (which isn't), I should get 16-7= 9 records back. When PO.ALL_answered is NULL, both (PO.ALL_answered == '0') and (PO.ALL_answered != '0') are false. But this just returns 0 records Apparently, in all rows PO.ALL_answered is either '0' or NULL. That's indeed the case , all '0' ;-) cheers, Stef Mientki - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Still getting Insertion failed because database is full. errors
Perhaps you need to design an experiment to detect the problem. Part of it might be to log activity. Relying on a debugger rather than logical analysis can waste a lot of time. Joel Cochran wrote: I've had him sit beside my in my office and attempt to recreate it, both using his device and mine, but it never happens. Actually, I did get it to happen once on his machine, but I was not connected to my PC, so I connected and tried to recreate it through DEBUG but could not. The last time it happened in the field, I had him stop using the device and bring it to me so that I could see the Stack Trace (which I sent to the list). With neither his device nor mine can I recreate the problem in DEBUG. It is very frustrating. Thanks, Joel On 4/17/07, Jonas Sandman [EMAIL PROTECTED] wrote: What is the guy on in the field doing that you are not? Are you using his device for the testing? Since it takes minutes for him to encounter the error it can't be that hard to recreate. Follow him around for an hour or so and see how he uses the program. It could easily be something he's doing that you aren't... On 4/17/07, Joel Cochran [EMAIL PROTECTED] wrote: The saga continues... I was very excited by the idea that there was something wrong with the CF Card. The theory fits all the facts: it explains why the original database threw unspecified errors, it explains why now SQLite is throwing errors, it explains why I can't reproduce the problem in house or on my machine. It just seemed to explain everything, so yesterday I went out and bought a brand-spankin' new SanDisk CF card. I loaded it up with the database, installed it on my tester's machine, and this morning it went back out to the field for testing. Within minutes, he encountered the same error. Now I just don't believe the problem is with the card, so I feel that I am right back at square one. I'm really at my wits end and don't know what to do next. I am going to go ahead and install the database on the device memory instead of removable media, just to test it out, but I have no faith that it will change anything. When that fails, I will send the tester out with another device entirely, but again I expect the same results. I'm convinced now that the problem is with the application architecture, but I have no idea what to look at anymore. I've stared and fiddled with this code so much that I'm ready to throw in the towel. But since I'd like to keep my job that isn't an option. If I had hair, I'd pull it out. Any help at all would be appreciated. -- Joel Cochran On 4/13/07, Michael Ruck [EMAIL PROTECTED] wrote: Unless things have changed recently, the following should still be valid for Windows Mobile/Windows CE devices: Usually these devices do not power off, but stay in a standby state where the memory is always powered. Check if that's the case with your system and move as much as possible into RAM or a RAM disk, if that feature is provided by the windows mobile kernel built for your device. If that's not possible, I'd suggest replacing CF cards with micro drives - these are regular hard drives in a CF card format. I'm not up to date on storage space, but should be sufficient for your needs. To test the cards I'd put them in a card reader format it and fill it completely up with zeros. When a flash card erases a byte, it sets all bits to ones and upon write clears those, which need to be zero. So to test all bits you really need to zero out the entire card. This will also give the controller in the card a chance to remap bad sectors with spares. Finally you determine the file size of the card, when you receive the first write error. This is (approximately) the number of bytes the card can store (at that point in time) and falling. It seems some cards even return read errors, when they hit a defective sector upon read. Maybe the actual error code just gets lost/mangled on the way up and the actual error is just a simple read error ;) I've seen reports about this with some digital cameras, which would not even let people view the pictures taken a minute ago. Mike -Ursprüngliche Nachricht- Von: John Stanton [mailto:[EMAIL PROTECTED] Gesendet: Freitag, 13. April 2007 23:44 An: sqlite-users@sqlite.org Betreff: Re: [sqlite] Still getting Insertion failed because database is full. errors You might find some joy in the baby disk drives such as installed in the original ipods. Can you substitute RAM with a battery backup if the memory card is always in the device? Joel Cochran wrote: Thanks John and Dennis, At least now I have something to look at. I will look into the CF problem next. The database itself gets generated on a PC and then transferred to the CF Card. During testing and development, this could have been 20-30 times a
[sqlite] PRAGMA short_column_names ignored when GROUP BY is used
It looks like short_column_names pragma is ignored when GROUP BY is used in a query. Is this considered expected behavior? I hope not.. :-) Thanks, Sam sqlite pragma short_column_names; short_column_names -- 1 sqlite pragma full_column_names; full_column_names - 0 sqlite select u.userid from users u limit 1; UserID -- 1 sqlite select u.userid from users u group by u.userid limit 1; u.userid -- 1 sqlite --- We're Hiring! Seeking a passionate developer to join our team building products. Position is in the Washington D.C. metro area. If interested contact [EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Still getting Insertion failed because database is full. errors
Either add a trace-log which shows the flow of the program (entering, exiting methods, database commands accessed). It's not so simple that when you send the program to him in the field, it's a release build and when you test you are using a debug build? On 4/17/07, Joel Cochran [EMAIL PROTECTED] wrote: I've had him sit beside my in my office and attempt to recreate it, both using his device and mine, but it never happens. Actually, I did get it to happen once on his machine, but I was not connected to my PC, so I connected and tried to recreate it through DEBUG but could not. The last time it happened in the field, I had him stop using the device and bring it to me so that I could see the Stack Trace (which I sent to the list). With neither his device nor mine can I recreate the problem in DEBUG. It is very frustrating. Thanks, Joel On 4/17/07, Jonas Sandman [EMAIL PROTECTED] wrote: What is the guy on in the field doing that you are not? Are you using his device for the testing? Since it takes minutes for him to encounter the error it can't be that hard to recreate. Follow him around for an hour or so and see how he uses the program. It could easily be something he's doing that you aren't... On 4/17/07, Joel Cochran [EMAIL PROTECTED] wrote: The saga continues... I was very excited by the idea that there was something wrong with the CF Card. The theory fits all the facts: it explains why the original database threw unspecified errors, it explains why now SQLite is throwing errors, it explains why I can't reproduce the problem in house or on my machine. It just seemed to explain everything, so yesterday I went out and bought a brand-spankin' new SanDisk CF card. I loaded it up with the database, installed it on my tester's machine, and this morning it went back out to the field for testing. Within minutes, he encountered the same error. Now I just don't believe the problem is with the card, so I feel that I am right back at square one. I'm really at my wits end and don't know what to do next. I am going to go ahead and install the database on the device memory instead of removable media, just to test it out, but I have no faith that it will change anything. When that fails, I will send the tester out with another device entirely, but again I expect the same results. I'm convinced now that the problem is with the application architecture, but I have no idea what to look at anymore. I've stared and fiddled with this code so much that I'm ready to throw in the towel. But since I'd like to keep my job that isn't an option. If I had hair, I'd pull it out. Any help at all would be appreciated. -- Joel Cochran On 4/13/07, Michael Ruck [EMAIL PROTECTED] wrote: Unless things have changed recently, the following should still be valid for Windows Mobile/Windows CE devices: Usually these devices do not power off, but stay in a standby state where the memory is always powered. Check if that's the case with your system and move as much as possible into RAM or a RAM disk, if that feature is provided by the windows mobile kernel built for your device. If that's not possible, I'd suggest replacing CF cards with micro drives - these are regular hard drives in a CF card format. I'm not up to date on storage space, but should be sufficient for your needs. To test the cards I'd put them in a card reader format it and fill it completely up with zeros. When a flash card erases a byte, it sets all bits to ones and upon write clears those, which need to be zero. So to test all bits you really need to zero out the entire card. This will also give the controller in the card a chance to remap bad sectors with spares. Finally you determine the file size of the card, when you receive the first write error. This is (approximately) the number of bytes the card can store (at that point in time) and falling. It seems some cards even return read errors, when they hit a defective sector upon read. Maybe the actual error code just gets lost/mangled on the way up and the actual error is just a simple read error ;) I've seen reports about this with some digital cameras, which would not even let people view the pictures taken a minute ago. Mike -Ursprüngliche Nachricht- Von: John Stanton [mailto:[EMAIL PROTECTED] Gesendet: Freitag, 13. April 2007 23:44 An: sqlite-users@sqlite.org Betreff: Re: [sqlite] Still getting Insertion failed because database is full. errors You might find some joy in the baby disk drives such as installed in the original ipods. Can you substitute RAM with a battery backup if the memory card is always in the device? Joel Cochran wrote: Thanks John and Dennis, At least now I
AW: [sqlite] Still getting Insertion failed because database is full. errors
Unfortunately DEBUG builds change timing entirely on windows platforms. I would suggest creating a release build with symbols. Mike -Ursprüngliche Nachricht- Von: Joel Cochran [mailto:[EMAIL PROTECTED] Gesendet: Dienstag, 17. April 2007 20:59 An: sqlite-users@sqlite.org Betreff: Re: [sqlite] Still getting Insertion failed because database is full. errors I've had him sit beside my in my office and attempt to recreate it, both using his device and mine, but it never happens. Actually, I did get it to happen once on his machine, but I was not connected to my PC, so I connected and tried to recreate it through DEBUG but could not. The last time it happened in the field, I had him stop using the device and bring it to me so that I could see the Stack Trace (which I sent to the list). With neither his device nor mine can I recreate the problem in DEBUG. It is very frustrating. Thanks, Joel On 4/17/07, Jonas Sandman [EMAIL PROTECTED] wrote: What is the guy on in the field doing that you are not? Are you using his device for the testing? Since it takes minutes for him to encounter the error it can't be that hard to recreate. Follow him around for an hour or so and see how he uses the program. It could easily be something he's doing that you aren't... On 4/17/07, Joel Cochran [EMAIL PROTECTED] wrote: The saga continues... I was very excited by the idea that there was something wrong with the CF Card. The theory fits all the facts: it explains why the original database threw unspecified errors, it explains why now SQLite is throwing errors, it explains why I can't reproduce the problem in house or on my machine. It just seemed to explain everything, so yesterday I went out and bought a brand-spankin' new SanDisk CF card. I loaded it up with the database, installed it on my tester's machine, and this morning it went back out to the field for testing. Within minutes, he encountered the same error. Now I just don't believe the problem is with the card, so I feel that I am right back at square one. I'm really at my wits end and don't know what to do next. I am going to go ahead and install the database on the device memory instead of removable media, just to test it out, but I have no faith that it will change anything. When that fails, I will send the tester out with another device entirely, but again I expect the same results. I'm convinced now that the problem is with the application architecture, but I have no idea what to look at anymore. I've stared and fiddled with this code so much that I'm ready to throw in the towel. But since I'd like to keep my job that isn't an option. If I had hair, I'd pull it out. Any help at all would be appreciated. -- Joel Cochran On 4/13/07, Michael Ruck [EMAIL PROTECTED] wrote: Unless things have changed recently, the following should still be valid for Windows Mobile/Windows CE devices: Usually these devices do not power off, but stay in a standby state where the memory is always powered. Check if that's the case with your system and move as much as possible into RAM or a RAM disk, if that feature is provided by the windows mobile kernel built for your device. If that's not possible, I'd suggest replacing CF cards with micro drives - these are regular hard drives in a CF card format. I'm not up to date on storage space, but should be sufficient for your needs. To test the cards I'd put them in a card reader format it and fill it completely up with zeros. When a flash card erases a byte, it sets all bits to ones and upon write clears those, which need to be zero. So to test all bits you really need to zero out the entire card. This will also give the controller in the card a chance to remap bad sectors with spares. Finally you determine the file size of the card, when you receive the first write error. This is (approximately) the number of bytes the card can store (at that point in time) and falling. It seems some cards even return read errors, when they hit a defective sector upon read. Maybe the actual error code just gets lost/mangled on the way up and the actual error is just a simple read error ;) I've seen reports about this with some digital cameras, which would not even let people view the pictures taken a minute ago. Mike -Ursprüngliche Nachricht- Von: John Stanton [mailto:[EMAIL PROTECTED] Gesendet: Freitag, 13. April 2007 23:44 An: sqlite-users@sqlite.org Betreff: Re: [sqlite] Still getting Insertion failed because database is full. errors You might find some joy in the baby disk drives such as installed in the original ipods. Can you substitute RAM with a battery backup if the memory
Re: [sqlite] Is this a valid syntax
Stef Mientki wrote: I don't know if this is the problem, but, for some reason you're mixing C/C++ syntax in with SQL there. You don't use '==', you should just use '=' You don't use '!=', you should use '' thanks Paul, but although I can never find this information when I need it :-( AFAIK, both notations are allowed. Besides that I tried both and it doesn't change the situation. Paul and Stef, Being a C programmer, Richard extended SQLite to allow C syntax for equality and inequality comparisons as shown at http://www.sqlite.org/lang_expr.html even though it is non standard. If you want your SQL code to be portable to other database engines you should use the standard syntax that Paul suggested. HTH Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] what do I compile with -dthreadsafe=1
configure --enable-threadsafe should do it. Rafi Cohen [EMAIL PROTECTED] wrote: Hi, when I read the article about sqlite and multithread in cvstrac, I understood that I need to compile my source files using sqlite functions with -DTHREADSAFE=1. Now, due to a pproblem I had in my project I re-readad this article and began to think I should compile sqlite source files using -DTHREADSAFE=1, which I did not do yet. So I'm confused. Where do i use this? compile sqlite sources? my sources? both? In case of sqlite sources, where do I put this -DTHREADSAFE=1? in a specific makefile? in all makefiles? Please help. I have linux and using sqlite 3.3.12. Thanks, Rafi.
Re: [sqlite] Is this a valid syntax
Dennis Cote wrote: Stef Mientki wrote: I don't know if this is the problem, but, for some reason you're mixing C/C++ syntax in with SQL there. You don't use '==', you should just use '=' You don't use '!=', you should use '' thanks Paul, but although I can never find this information when I need it :-( AFAIK, both notations are allowed. Besides that I tried both and it doesn't change the situation. hi Dennis, Paul and Stef, Being a C programmer, Richard extended SQLite to allow C syntax for equality and inequality comparisons as shown at http://www.sqlite.org/lang_expr.html Aha, that was the page I was looking for !! even though it is non standard. If you want your SQL code to be portable to other database engines you should use the standard syntax that Paul suggested. I agree, thanks. But it doesn't solve my problem :-( I've the feeling that despite the suggestions of Igor, the problem still exists, caused by the zero values ?? I'll try tomorrow again with some other values. cheers, Stef HTH Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Is this a valid syntax
Stef Mientki wrote: But it doesn't solve my problem :-( I've the feeling that despite the suggestions of Igor, the problem still exists, caused by the zero values ?? I'll try tomorrow again with some other values. Stef, Oh... I though Igor had solved your problem so I didn't give it much thought. My reading of your query would suggest that you should try this: SELECT PO.* FROM Koppel LEFT JOIN PO ON (Koppel.K_App == PO.App) WHERE (Koppel.K_naam == 'MVE') AND (PO.ALL_answered == '0') The last two conditions should select the result rows and not affect the join operation. Only the comparisons of the fields from both tables should affect the join. HTH Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Is this a valid syntax
Dennis Cote wrote: Stef Mientki wrote: But it doesn't solve my problem :-( I've the feeling that despite the suggestions of Igor, the problem still exists, caused by the zero values ?? I'll try tomorrow again with some other values. Stef, Oh... I though Igor had solved your problem so I didn't give it much thought. My reading of your query would suggest that you should try this: SELECT PO.* FROM Koppel LEFT JOIN PO ON (Koppel.K_App == PO.App) WHERE (Koppel.K_naam == 'MVE') AND (PO.ALL_answered == '0') The last two conditions should select the result rows and not affect the join operation. Only the comparisons of the fields from both tables should affect the join. Thanks Dennis, but I tried that already :-( Tomorrow is a new day, with new possibilities. cheers, Stef HTH Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Is this a valid syntax
Dennis Cote [EMAIL PROTECTED] wrote: Being a C programmer, Richard extended SQLite to allow C syntax for equality and inequality comparisons as shown at http://www.sqlite.org/lang_expr.html even though it is non standard. Actually, the reason I did this was because PostgreSQL did it before me and I used the PostgreSQL documentation as a guide while developing SQLite. :-) -- D. Richard Hipp [EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Still getting Insertion failed because database is full. errors
At first I thought this had solved the problem, because all in house testing runs beautifully. However, as soon as the device is sent to the field, the error starts again. Unfortunately, it means that I have never been able to catch this in debug. I did, however, change the error reporting a little and got some more information. The SQLiteException I am not getting includes this information: Insertion failed because the database is full That message is from the wrapper. database or disk is full And the above is from sqlite3. The corresponding return code is SQLITE_FULL. Search source file os_win.c for where SQLITE_FULL errors can be generated - there's only a couple of places. Odds on it's one of them. Looks like on windows, any error writing or seeking a file is reported as SQLITE_FULL. at System.Data.SQLite.SQLite3.Reset() at System.Data.SQLite.SQLite3.Step() at System.Data.SQLite.SQLiteDataReader.NextResult() at System.Data.SQLite.SQLiteDataReader.ctor() at System.Data.SQLite.SQLiteCommand.ExecuteReader() at System.Data.SQLite.SQLiteCommand.ExecuteDbDataReader() at ... etc etc So is this really a SELECT? Probably good to check that. If it is a SELECT, why would it be filling up the database? Is it rolling back a journal file and running out of space like that? Or the user rolling back the journal file doesn't have permission to write the database file and SQLite is reporting the error incorrectly. Check for a journal file on the device after the error. Also run the SQLite integrity-check on the database. Dan. - To unsubscribe, send email to [EMAIL PROTECTED] -
