[sqlite] Trigger Variables

I'm porting an Interbase DB to SQLIte and wondered if there is any way to store temporary values within a trigger? Any help appreciated.Slurcher. ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] Bug? LIMIT in compound statement with UNION ALL seems to affect only the first SELECT statement if it has ORDER BY

Thanks, Igor. It works like a charm now. 2010/10/6 Igor Tandetnik > Yuri G wrote: > > This looks like a bug to me: > > > > --sql: > > > > CREATE TABLE t(a INTEGER); > > > > INSERT INTO "t" VALUES(1); > > INSERT INTO "t" VALUES(2); > > INSERT INTO "t"

Re: [sqlite] Bug? LIMIT in compound statement with UNION ALL seems to affect only the first SELECT statement if it has ORDER BY

Yep, it turned out to be a bug (http://www.sqlite.org/src/info/38cb5df375). Thanks for the info, Pavel! I didn't know that. 2010/10/6 Pavel Ivanov > I can't say anything about your particular issue with the LIMIT > clause, maybe that's a bug. But > > > Another solution is

Re: [sqlite] unable to open database file/Disc I/O error

Hi, Just wanted to say we have the same issue, using sqlite v3.6.15 (not java) running on *some* windows vista machines accessing databases on *some* networked drives. I don't think this is an sqlite problem, because vista is obviously randomly locking files and this can be seen when using

Re: [sqlite] unable to open database file/Disc I/O error

On Thu, Oct 7, 2010 at 1:03 PM, Serena Lien wrote: > > If you find any vista OS patches that solve this problem, please let > me know, we are looking ourselves in the hopes that microsoft has > resolved this issue.. > > Serena, How about this patch :

[sqlite] [BUG] JOIN subquery in FROM with FTS3 table

Hi, Given this simple query with a subquery in FROM and a join with a FTS3 table: SELECT news1.number, fts_news.title FROM (SELECT number FROM news LIMIT 50) as news1, fts_news WHERE news1.number=fts_news.docid The query runs in 15ms in 3.6.23. The same query runs in *8 seconds* in 3.7.2. If I

Re: [sqlite] Trigger Variables

> I'm porting an Interbase DB to SQLIte and wondered if there is any way to > store temporary values within a trigger? No. You should use (temporary) tables created outside the trigger for that. Or you can move the trigger logic into your application. Pavel On Thu, Oct 7, 2010 at 4:26 AM,

[sqlite] [BUG] JOIN subquery in FROM with FTS3 table

Hi, Given this simple query with a subquery in FROM and a join with a FTS3 table: SELECT news1.number, fts_news.title FROM (SELECT number FROM news LIMIT 50) as news1, fts_news WHERE news1.number=fts_news.docid The query runs in 15ms in 3.6.23. The same query runs in *8 seconds* in 3.7.2. If I

Re: [sqlite] [BUG] JOIN subquery in FROM with FTS3 table

On Thu, Oct 7, 2010 at 2:50 PM, Shopsland gmail wrote: > Hi, > > Given this simple query with a subquery in FROM and a join with a FTS3 > table: > > SELECT news1.number, fts_news.title > FROM (SELECT number FROM news LIMIT 50) as news1, fts_news > WHERE

Re: [sqlite] [BUG] JOIN subquery in FROM with FTS3 table

On Oct 7, 2010, at 5:50 PM, Shopsland gmail wrote: > Hi, > > Given this simple query with a subquery in FROM and a join with a > FTS3 table: > > SELECT news1.number, fts_news.title > FROM (SELECT number FROM news LIMIT 50) as news1, fts_news > WHERE news1.number=fts_news.docid > > The query

Re: [sqlite] [BUG] JOIN subquery in FROM with FTS3 table

On Thu, Oct 7, 2010 at 8:00 AM, Dan Kennedy wrote: > > > > It seems that something changed in the query optimizer. > > Thanks for the report. We think this has been fixed in > fossil already. 3.7.3 should be the same as 3.6.23 for > this query. > The 3.7.3 release will be

Re: [sqlite] [BUG] JOIN subquery in FROM with FTS3 table

On Thu, Oct 7, 2010 at 4:05 PM, Richard Hipp wrote: > On Thu, Oct 7, 2010 at 8:00 AM, Dan Kennedy wrote: > > > > > > > It seems that something changed in the query optimizer. > > > > Thanks for the report. We think this has been fixed in > > fossil

Re: [sqlite] [BUG] JOIN subquery in FROM with FTS3 table

Max, Thank you for your fast answer. I already knew the thread you suggest. The issue here is that the same join works fine on 3.7.2 with a normal table: SELECT news1.number, news2.title FROM (SELECT number FROM news LIMIT 50) as news1, news2 WHERE news1.number=news2.docid When you join with

[sqlite] Speed up DELETE of a lot of records

Hi all, I have a question about how to speed up a DELETE statement. I have a DB of about 3GB: the DB has about 23 millions of records. The DB is indexed by a DateTime column (is a 64 bit integer), and suppose you want to delete all records before a date. Now I'm using a syntax like this (I try

Re: [sqlite] Accented characters and ODBC

Still hoping someone can help me with this. I dug into it some with a hex editor, and it may be the app mode, which is multi-byte (and makes extensive use of libraries, so is not about to change). If I put the string Andé in, it is (41 6e 64 e9) in the debugger, but looking at the sqlite

Re: [sqlite] Speed up DELETE of a lot of records

On 7 Oct 2010, at 5:05pm, Michele Pradella wrote: > The DB is indexed by a DateTime column (is a 64 bit integer) Do make sure that that column is declared as INTEGER and that there is an index on it. > DELETE FROM table_name WHERE DateTime > the statement is trying to delete about 5 millions

Re: [sqlite] Speed up DELETE of a lot of records

On Thu, Oct 07, 2010 at 05:22:19PM +0100, Simon Slavin scratched on the wall: > On 7 Oct 2010, at 5:05pm, Michele Pradella wrote: > > > The DB is indexed by a DateTime column (is a 64 bit integer) > > Do make sure that that column is declared as INTEGER and that there > is an index on it.

Re: [sqlite] Speed up DELETE of a lot of records

On 7 Oct 2010, at 5:36pm, Jay A. Kreibich wrote: > On Thu, Oct 07, 2010 at 05:22:19PM +0100, Simon Slavin scratched on the wall: >> On 7 Oct 2010, at 5:05pm, Michele Pradella wrote: >> >>> The DB is indexed by a DateTime column (is a 64 bit integer) >> >> Do make sure that that column is

Re: [sqlite] Speed up DELETE of a lot of records

> But the rows he wants to delete are those with DateTime without an index on that column SQL can't find which rows to delete quickly ! "Quickly" is appropriate for one row. For several rows SQLite will sequentially scan the index and for each rowid found there it will traverse the table's b-tree

Re: [sqlite] Speed up DELETE of a lot of records

On Thu, Oct 7, 2010 at 11:05 AM, Michele Pradella wrote: >  Hi all, I have a question about how to speed up a DELETE statement. > I have a DB of about 3GB: the DB has about 23 millions of records. > The DB is indexed by a DateTime column (is a 64 bit integer), and >

Re: [sqlite] Speed up DELETE of a lot of records

On Oct 7, 2010, at 6:05 PM, Michele Pradella wrote: > I have a DB of about 3GB: the DB has about 23 millions of records. [..] > the statement is trying to delete about 5 millions records and it takes > about 4-5minutes. > Is there a way to try to speed up the DELETE? Considering that you want

Re: [sqlite] create View for four tables

On Oct 7, 2010, at 5:49 AM, Redhot wrote: > I need to pull information from 4 different tables. I read taht using the > "Create View" is that best way for this. Non sequitur :) > Can you let me now if my code is correct? http://en.wikipedia.org/wiki/Join_(SQL)

Re: [sqlite] SQLite database sync

On Oct 6, 2010, at 5:50 PM, David Haymond wrote: > If I copy, I don't want to transfer EVERY record to the server each time I > sync, because that would be a waste of bandwidth. What is the best way to > copy only those records that have changed to the server? Perhaps you could simply rsync

Re: [sqlite] SQLite database sync

On Thu, Oct 07, 2010 at 09:09:19PM +0200, Petite Abeille wrote: > On Oct 6, 2010, at 5:50 PM, David Haymond wrote: > > If I copy, I don't want to transfer EVERY record to the server each time I > > sync, because that would be a waste of bandwidth. What is the best way to > > copy only those

Re: [sqlite] SQLite database sync

On Oct 7, 2010, at 9:35 PM, Nicolas Williams wrote: > A general tool for hands-off bi-di synchronization of arbitrary DBs is > not really feasible, not in a way that would satisfy most users. Perhaps we should ask the author of diffkit [1] to solve that hairy problem for the rest of us :))

Re: [sqlite] Accented characters and ODBC - Fixed

I had the right idea, but the wrong code page. So, going from MBCS project in visual c++, there is a magic ANSI to UTF8 conversion happening when the data is stored, but not one coming back out. I could not find a direct UTF8 to ANSI code page conversion, presumably since they are both

[sqlite] how do I load a csv file or what is the simplest text file to load into sqlite3?

how do I load a csv file or what is the simplest text file to load into sqlite3? what would be the best format to use I use text pad and open office calc. Also open office base. -- thanks for your time, Joshua W ___ sqlite-users mailing list

[sqlite] Involving in sqlite development

Hi, I'm interested involving in sqlite development. How can I start it? Regards Steve ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Involving in sqlite development

On Thu, Oct 7, 2010 at 4:26 PM, sjtirtha wrote: > Hi, > > I'm interested involving in sqlite development. > How can I start it? You really need to explain further and more clearly what you want to do before anyone will be able to guide you. > > Regards > Steve >

[sqlite] Porting SQLite 3.7.2 to vxWorks 6.7

I searched through all the mail archives to see if I could find someone that has ported SQLite to vxWorks in kernel mode. Apparently, there are a few folks attempting it. And fewer succeeding at it. I found an article published by ZhiHua Huang where he describes the mods he made to port

Re: [sqlite] SQLite and C++ in Linux

On Thu, Oct 7, 2010 at 5:47 PM, Dan Sabin wrote: > Hi, > > I'm trying to embed SQLite into a Linux application. It's written in C++ > and I'm having trouble getting the code to compile with the SQLite .c and .h > files included. I get some of the following errors: > >

Re: [sqlite] SQLite database sync

Do you really require a local copy of the entire database? Can you not just create a service that queries the database and gives you results on demand, caching the results? -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of

[sqlite] Confitional IF in triggers

This may be a really dumb question, but I've searched and can't find an answer. Do SQLite triggers support any conditional expressions, like IF, or is there only the WHEN statement? If the latter, does that mean that multiple conditions must be in separate triggers? Any help appreciated.Stopgap.

Re: [sqlite] Confitional IF in triggers

Quoth Russell A , on 2010-10-07 22:24:23 -0700: > This may be a really dumb question, but I've searched and can't find an > answer. > Do SQLite triggers support any conditional expressions, like IF, or is there > only the WHEN statement? If the latter, does that mean that