[sqlite] Find non-numeric character in text field

2009-11-04 Thread RB Smissaert
I would like to do a where on a text field and check if the values have non-numeric characters, which is in this case is anything other than 1,2,3,4,5,6,7,8,9,0 or a space character. Is this possible without using a UDF or a very long OR construction? RBS

Re: [sqlite] Need Help SQL

2009-10-12 Thread RB Smissaert
> I'm using Olaf Schmidt's VB SQLite binder. That does use parameterized statements. Look at the methods and properties of the cCommand object in the object browser. Also look at the demo code that comes with dhRichClient3. RBS -Original Message- From: sqlite-users-boun...@sqlite.org

Re: [sqlite] Sqlite profiler

2009-09-23 Thread RB Smissaert
OK, thanks. In that case I do that in my application code. RBS -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Christian Schwarz Sent: 23 September 2009 14:33 To: General Discussion of SQLite Database Subject: Re: [sqlite]

Re: [sqlite] Sqlite profiler

2009-09-23 Thread RB Smissaert
Maybe, what is it? RBS -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of mcnamaragio Sent: 23 September 2009 14:16 To: sqlite-users@sqlite.org Subject: [sqlite] Sqlite profiler Hello, Would anyone be interested in sqlite

Re: [sqlite] Any way to speed up this SQL?

2009-09-12 Thread RB Smissaert
: 12 September 2009 14:56 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Any way to speed up this SQL? RB Smissaert wrote: > Have 2 tables with both one text field called term and need to run a > SQL like this, to count the records in table1 where the start of term > in table1 equa

Re: [sqlite] Any way to speed up this SQL?

2009-09-12 Thread RB Smissaert
on tmptable2(shortest) -Run this sql select count(a.rowid) from tmptable1 a inner join tmptable2 b on a.shortest=b.shortest where (lower(b.term) = lower(substr(a.term,1,length(b.term RB Smissaert wrote: > > Have 2 tables with both one text field called term and need to run a SQL > like this,

[sqlite] Any way to speed up this SQL?

2009-09-12 Thread RB Smissaert
Have 2 tables with both one text field called term and need to run a SQL like this, to count the records in table1 where the start of term in table1 equals a term in table2: select count(a.rowid) from table1 a inner join table2 b on (lower(b.term) = lower(substr(a.term,1,length(b.term term

Re: [sqlite] Does SQLite have an Instr function?

2009-09-11 Thread RB Smissaert
of SQLite Database Subject: Re: [sqlite] Does SQLite have an Instr function? RB Smissaert wrote: > Does SQLite have a string function that produces the first position of a > string within another string? > For example select Instr('abcd', 'c') would produce 3 > Looked in the d

Re: [sqlite] Does SQLite have an Instr function?

2009-09-11 Thread RB Smissaert
] On Behalf Of Igor Tandetnik Sent: 11 September 2009 22:40 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Does SQLite have an Instr function? RB Smissaert <bartsmissa...@blueyonder.co.uk> wrote: > Does SQLite have a string function that produces the first position > of a string wi

[sqlite] Does SQLite have an Instr function?

2009-09-11 Thread RB Smissaert
Does SQLite have a string function that produces the first position of a string within another string? For example select Instr('abcd', 'c') would produce 3 Looked in the documentation and the forum, but couldn't see it. RBS ___ sqlite-users mailing

Re: [sqlite] tool to browse a sqlite database

2009-03-11 Thread RB Smissaert
-users-boun...@sqlite.org] On Behalf Of Ralf Junker Sent: 08 March 2009 14:38 To: General Discussion of SQLite Database Subject: Re: [sqlite] tool to browse a sqlite database RB Smissaert wrote: >What SQLite version produced the file World.db3? I am not 100% sure about the exact SQLite vers

Re: [sqlite] tool to browse a sqlite database

2009-03-08 Thread RB Smissaert
-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Ralf Junker Sent: 08 March 2009 14:38 To: General Discussion of SQLite Database Subject: Re: [sqlite] tool to browse a sqlite database RB Smissaert wrote: >What SQLite version produced the file World.db3? I am not 100% s

Re: [sqlite] tool to browse a sqlite database

2009-03-08 Thread RB Smissaert
What SQLite version produced the file World.db3? I ask as my wrapper doesn't pick correctly the fields of a table. This is Olaf Schmidt's VB wrapper dhRichClient with SQLite 3.6.11. BTW, SQLiteSpy looks a very nice GUI tool. RBS -Original Message- From: sqlite-users-boun...@sqlite.org

Re: [sqlite] Lock SQLite file by overwriting bytes and then un-lock?

2009-01-26 Thread RB Smissaert
Thanks; will do that. RBS -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of MikeW Sent: 26 January 2009 12:02 To: sqlite-users@sqlite.org Subject: Re: [sqlite]Lock SQLite file by overwriting bytes and then un-lock? RB

Re: [sqlite] Lock SQLite file by overwriting bytes and then un-lock?

2009-01-26 Thread RB Smissaert
by overwriting bytes and then un-lock? RB Smissaert <bartsmissa...@...> writes: > > For some reason this mail went to the junk mail folder. > That sounds good and I would be happy to buy this, but I am not sure about: > > All you need to do is replace the DLL >

Re: [sqlite] Lock SQLite file by overwriting bytes and then un-lock?

2009-01-25 Thread RB Smissaert
ock? On Jan 25, 2009, at 2:32 PM, RB Smissaert wrote: > Yes, you are right there. It won't be much good for anything else > then a > casual peek at the device. This is a clinical database, so it involves > patients, diagnoses, medications etc. The ID data and the clinical >

Re: [sqlite] Lock SQLite file by overwriting bytes and then un-lock?

2009-01-25 Thread RB Smissaert
-lock? On Sun, 25 Jan 2009 18:29:28 -, "RB Smissaert" <bartsmissa...@blueyonder.co.uk> wrote in General Discussion of SQLite Database <sqlite-users@sqlite.org>: >Would it be possible to make a SQLite file un-usable by overwriting bytes in >certain places and then (via

Re: [sqlite] Lock SQLite file by overwriting bytes and then un-lock?

2009-01-25 Thread RB Smissaert
: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Jay A. Kreibich Sent: 25 January 2009 18:48 To: General Discussion of SQLite Database Subject: Re: [sqlite] Lock SQLite file by overwriting bytes and then un-lock? On Sun, Jan 25, 2009 at 06:29:28PM -, RB

[sqlite] Lock SQLite file by overwriting bytes and then un-lock?

2009-01-25 Thread RB Smissaert
Would it be possible to make a SQLite file un-usable by overwriting bytes in certain places and then (via an encrypted password) make the file usable again by putting the right bytes back in the right places? I use a VB wrapper that can encrypt the database, but I am using this SQLite file on a

Re: [sqlite] Why the difference in these 2 SQLite files?

2009-01-10 Thread RB Smissaert
-boun...@sqlite.org] On Behalf Of RB Smissaert Sent: 09 January 2009 22:15 To: 'General Discussion of SQLite Database' Subject: Re: [sqlite] Why the difference in these 2 SQLite files? OK, any DB write to this file on the desktop (not the mobile device) will make the query go fast. A completely

Re: [sqlite] newbie question regarding my sqlite code

2009-01-10 Thread RB Smissaert
Try this: select avg(age) from acoda union all select avg(durata) from main union all select sum(età) from dipendenti RBS -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of silvio grosso Sent: 10 January 2009 10:41 To:

Re: [sqlite] Why the difference in these 2 SQLite files?

2009-01-09 Thread RB Smissaert
and hopefully it does. Maybe I need some app to compare the 2 db files to see what is going on here. Baffling me. RBS -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of RB Smissaert Sent: 09 January 2009 20:54 To: 'General Discussion

Re: [sqlite] Why the difference in these 2 SQLite files?

2009-01-09 Thread RB Smissaert
-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of RB Smissaert Sent: 09 January 2009 20:00 To: sqlite-users@sqlite.org Subject: [sqlite] Why the difference in these 2 SQLite files? Have a large (about half a Gb) SQLite db file, made with version 3.6.1. I make this file

[sqlite] Why the difference in these 2 SQLite files?

2009-01-09 Thread RB Smissaert
Have a large (about half a Gb) SQLite db file, made with version 3.6.1. I make this file on one PC (Windows XP) put the file on a USB stick, take it home, copy it to the home PC (Win XP) local drive and then from there copy the file to a Windows mobile (WM6) device, a Samsung Omnia. On that device

Re: [sqlite] delete to leave x rows per group

2008-07-03 Thread RB Smissaert
22:54 To: sqlite-users@sqlite.org Subject: Re: [sqlite] delete to leave x rows per group RB Smissaert <[EMAIL PROTECTED]> wrote: > Can this be done in SQLite SQL? > > ID Value > --- > 1 A > 1 B > 1 C > 1 D > 1 E > 2 A > 2 B > 2 C > 2 D > 2

[sqlite] delete to leave x rows per group

2008-07-03 Thread RB Smissaert
Can this be done in SQLite SQL? ID Value --- 1 A 1 B 1 C 1 D 1 E 2 A 2 B 2 C 2 D 2 E 2 F Delete rows to leave x rows per ID, say 3 rows, so we get: ID Value --- 1 C 1 D 1 E 2

[sqlite] PRAGMA journal_mode = OFF slower?

2008-05-20 Thread RB Smissaert
Done some testing with 3.5.9 with PRAGMA journal_mode = OFF and it seems that strangely it makes DB writing queries slower. I use SQLite from VB/VBA with the wrapper from Olaf Schmidt. Should it not be that PRAGMA journal_mode = OFF should make inserts, create index etc. faster rather than slower?

Re: [sqlite] sorting records in random order

2008-05-07 Thread RB Smissaert
I compared the speeds and found them to be the same. RBS -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Dennis Cote Sent: 07 May 2008 22:25 To: General Discussion of SQLite Database Subject: Re: [sqlite] sorting records in random order Samuel Neff

Re: [sqlite] temp tables and PRAGMA temp_store

2008-04-30 Thread RB Smissaert
: [sqlite] temp tables and PRAGMA temp_store RB Smissaert <[EMAIL PROTECTED]> writes: > > Using the latest SQLite and trying to speed up the making of some > intermediate tables. I thought I could do that by doing > PRAGMA temp_store = MEMORY and CREATE TEMP TABLE etc. >

[sqlite] temp tables and PRAGMA temp_store

2008-04-29 Thread RB Smissaert
Using the latest SQLite and trying to speed up the making of some intermediate tables. I thought I could do that by doing PRAGMA temp_store = MEMORY and CREATE TEMP TABLE etc. I do run the PRAGMA directly after establishing the SQLite connection. So far I haven't seen any speed increase yet and I

Re: [sqlite] Implicit INDEX?

2008-04-14 Thread RB Smissaert
Have tested this now on a table of some 30 rows (no indexes at all) and with 100 rows to find in the middle of the table, sorted asc on time-stamp field. It gave me a speed increase of about 25%. If I looked for rows at the beginning of the table the speed increase was more, some 50% faster.

Re: [sqlite] Any way to disable journaling & rollback?

2008-04-11 Thread RB Smissaert
DRH, I would be seriously interested in a PRAGMA to disable/avoid a journal file as in my application I don't need it at all and it only slows down my DB writes. Would it be possible to add this? If so, thanks in advance. RBS -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL

Re: [sqlite] PHP Code That Can Store and Retrieve Images

2008-03-12 Thread RB Smissaert
Kees, Thanks for the interest in this and replied off-list. Bart -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Kees Nuyt Sent: 12 March 2008 22:06 To: General Discussion of SQLite Database Subject: Re: [sqlite] PHP Code That Can Store and Retrieve

Re: [sqlite] PHP Code That Can Store and Retrieve Images

2008-03-12 Thread RB Smissaert
Kees, Would you be interested to do a project for me for a fee? I need to upload/download data to/from a hosted SQLite 3 DB. This has to be done from VBA or from a VB6 AX dll. I have posted this to RAC, but there seems little interest/progress. If interested then could you contact me off-list? I

Re: [sqlite] PHP Code That Can Store and Retrieve Images

2008-03-11 Thread RB Smissaert
Funny you ask that as just 2 days ago I posted a little project on RAC to do exactly this. In my case it has to be called from VBA or VB. Unfortunately and surprisingly no takers yet. RBS -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Robert L Cochran

Re: [sqlite] when to analyze?

2008-02-06 Thread RB Smissaert
to take care of this myself, particularly with a table rename. RBS -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED] Sent: 06 February 2008 20:27 To: General Discussion of SQLite Database Subject: Re: [sqlite] when to analyze? &qu

[sqlite] when to analyze?

2008-02-06 Thread RB Smissaert
Suppose we have a table with some 10 million rows and this table was analysed, so sqlite_stat1 has the stats of this table then is it worth it to analyze again after adding say 1000 more rows? The indexing is still the same, so no indexes are dropped or created. Also the data of the added rows

[sqlite] Duplicates in sqlite_stat1

2008-02-03 Thread RB Smissaert
Noticed that sqlite_stat1 can have duplicates on tbl, idx: tbl idx stat --- table1 idx190 2 1 table1 idx290 2 table1 idx12577 2 1 table1 idx22577 2 Is there any harm in this, so would SQLite know that it has to look at the last

[sqlite] strange problem with DELETE

2008-02-03 Thread RB Smissaert
SQLite 3.5.4, Win XP, VBA with the wrapper dhRichClient Running a query like this: delete from sqlite_stat1 where not tbl in ('table1', 'table2', 'table3') The strange thing is that rows are deleted where tbl is one of the listed tables. Have tried all kind of alterations, such as making it

RE: [sqlite] Re: how to do this case when?

2008-01-31 Thread RB Smissaert
There isn't much in it, but it looks the one with IFNULL is the fastest. Will stick to that one. RBS -Original Message- From: RB Smissaert [mailto:[EMAIL PROTECTED] Sent: 31 January 2008 21:17 To: sqlite-users@sqlite.org Subject: RE: [sqlite] Re: how to do this case when? Thanks; I

RE: [sqlite] Re: how to do this case when?

2008-01-31 Thread RB Smissaert
Thanks; I came up with number 3, but I like your number 1. Any idea what could be the fastest or will it all be the same? RBS -Original Message- From: Igor Tandetnik [mailto:[EMAIL PROTECTED] Sent: 31 January 2008 20:59 To: SQLite Subject: [sqlite] Re: how to do this case when? RB

RE: [sqlite] how to do this case when?

2008-01-31 Thread RB Smissaert
Message- From: RB Smissaert [mailto:[EMAIL PROTECTED] Sent: 31 January 2008 20:15 To: sqlite-users@sqlite.org Subject: [sqlite] how to do this case when? How do I alter this SQL, so that the original field remains the same when there is no match? A case when else end should do it, but I can't

[sqlite] how to do this case when?

2008-01-31 Thread RB Smissaert
How do I alter this SQL, so that the original field remains the same when there is no match? A case when else end should do it, but I can't get it right. UPDATE Table1 SET Field1 = (SELECT Field2 FROM Table2 WHERE Table1.Field1 = Table2.Field1) Thanks for any advice. RBS

RE: [sqlite] Number of elements in IN clause

2008-01-28 Thread RB Smissaert
>SELECT * FROM maintable WHERE key IN stuff; Thanks for that tip. Didn't know you could do that. Can't remember this as standard SQL. RBS -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: 28 January 2008 12:40 To: sqlite-users@sqlite.org Subject: Re:

[sqlite] Could this cause slow queries?

2008-01-24 Thread RB Smissaert
Latest SQLite version with the VB wrapper from Olaf Schmidt, dhRichClient.dll. Running this in VBA Excel on Windows XP. Have a suspicion that maybe you could get slow queries if a table repeatedly gets a DELETE FROM TABLE followed by re-populating the table with inserts, so cyling this

RE: [sqlite] Slow query on one machine

2008-01-19 Thread RB Smissaert
at that particular point on that particular machine, all else behaves normal. RBS -Original Message- From: Jay Sprenkle [mailto:[EMAIL PROTECTED] Sent: 19 January 2008 14:12 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Slow query on one machine On Jan 18, 2008 3:32 PM, RB Smissaert <[EMAIL PROTEC

[sqlite] Slow query on one machine

2008-01-18 Thread RB Smissaert
Trying to figure out why (with one particular customer) some queries have very different timings on one machine compared to another machine. It has to do with updating one particular SQLite table with more recent data from an Interbase table. I give the database (S for SQLite and I for

RE: [sqlite] Helping with table definition?

2007-12-25 Thread RB Smissaert
This is code I used a while ago. Don't use it anymore as I have a better way to do this via my VB wrapper. There are some lines that deal with code in other parts of my application, but I take it you can see that. In case you didn't know this is VB(A). Function GetSQLiteTableInfo2(strDB As

RE: [sqlite] DeviceSQL

2007-12-12 Thread RB Smissaert
Couldn't find anywhere how much this costs. Newsgroup search shows nil. Has anybody downloaded and tried the demo? RBS -Original Message- From: Joe Wilson [mailto:[EMAIL PROTECTED] Sent: 12 December 2007 17:10 To: sqlite-users@sqlite.org Subject: Re: [sqlite] DeviceSQL Be careful about

RE: [sqlite] DB managers that do searches?

2007-10-31 Thread RB Smissaert
> happy user of sqliteman Thanks for the tip, it is quite nice. Two things: Help doesn't launch from the interface and it always seems to give Row(s) returned: 256 even when there are lot more. Another nice one is SQL2006 Pro from OsenXPSuite. RBS -Original Message- From: Bernie

RE: [sqlite] Wrapper ADO like for VB6

2007-09-26 Thread RB Smissaert
and it seams to be really interesting. I will try it for sure. Did you also try the dhRPCServer that should act like a server/client? - Original Message - From: "RB Smissaert" <[EMAIL PROTECTED]> To: <sqlite-users@sqlite.org> Sent: Wednesday, September 26, 2007 2:47 PM Subject

RE: [sqlite] Wrapper ADO like for VB6

2007-09-26 Thread RB Smissaert
I am using dhSQLite, which you can download from here: http://www.thecommon.net/2.html I have tried several wrappers for VB(A) (about 4 or 5) and this is the best one. Good support as well. RBS -Original Message- From: Giuliano [mailto:[EMAIL PROTECTED] Sent: 26 September 2007 13:35

RE: [sqlite] [Visual Basic] How do you work with SQLite?

2007-08-10 Thread RB Smissaert
Several reasons. Main one is that we won't be allowed as this is a third party application clinical database. The other one is that it would cause too much slow-down of the regular clinical front-end application. This is reporting software and apart from some rare exceptions there is no writing

RE: [sqlite] [Visual Basic] How do you work with SQLite?

2007-08-09 Thread RB Smissaert
I use it mainly to manipulate data obtained from an Interbase database. All the data will eventually be dumped to Excel. I use 2 ways to move data from Interbase to SQLite. One, via an ADO recordset after connecting to Interbase with ODBC. This recordset will then be dumped to SQLite via the free

RE: [sqlite] Multiple fields update

2007-08-06 Thread RB Smissaert
Yes, thanks, I just found out. It can work without the WHERE clauses. RBS -Original Message- From: Gerry Snyder [mailto:[EMAIL PROTECTED] Sent: 06 August 2007 21:59 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Multiple fields update RB Smissaert wrote: > I am sure this SQL u

[sqlite] Multiple fields update

2007-08-06 Thread RB Smissaert
I am sure this SQL used to be fine with SQLite: update table1 set field1 = 0 where field1 = 2, field2 = 3 where field2 = 2 Now however I get a syntax error near , Has this changed? RBS - To unsubscribe, send email

RE: [sqlite] Re: Re: Re: Re: Re: Re: Re: How does SQLite choose the index?

2007-08-05 Thread RB Smissaert
to optimal at the first attempt. RB Smissaert wrote: > Yes, I suppose you are right there. > I will see if I can put together a report that runs all possible types of > queries (sequentially) and then see if I have left anything out that would > cause problems. > > RBS > >

RE: [sqlite] Re: Re: Re: Re: Re: Re: Re: How does SQLite choose the index?

2007-08-05 Thread RB Smissaert
2007 03:35 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Re: Re: Re: Re: Re: Re: Re: How does SQLite choose the index? RB Smissaert wrote: > I think an application that > would produce all the needed indexes based on the table and all the possible > queries would be helpful. Any

[sqlite] Problem with glob '137*' ?

2007-08-05 Thread RB Smissaert
Queries where there is a glob comparison on a string that could be interpreted as a number always seem a bit slower than when comparing to a string that can't be compared to a number. So for example: select f from t where f glob '137*' is slower than: select f from t where f glob 'abc*' Is this

RE: [sqlite] Re: Re: Re: Re: Re: Re: Re: How does SQLite choose the index?

2007-08-04 Thread RB Smissaert
t I am getting close now to having it all covered and thanks again for all the assistance. RBS -Original Message- From: Igor Tandetnik [mailto:[EMAIL PROTECTED] Sent: 04 August 2007 23:45 To: SQLite Subject: [sqlite] Re: Re: Re: Re: Re: Re: Re: How does SQLite choose the index?

RE: [sqlite] Re: Re: Re: Re: Re: Re: How does SQLite choose the index?

2007-08-04 Thread RB Smissaert
] Re: Re: Re: Re: Re: Re: How does SQLite choose the index? RB Smissaert <[EMAIL PROTECTED]> wrote: > OK, will have a look at the wiki. > >> There's no "m" on the right hand side. >> m equals N divided by logarithm of N. > > What is the base of that logarith

RE: [sqlite] Re: Re: Re: Re: Re: How does SQLite choose the index?

2007-08-04 Thread RB Smissaert
ect: [sqlite] Re: Re: Re: Re: Re: How does SQLite choose the index? RB Smissaert <[EMAIL PROTECTED]> wrote: > Thanks; I have seen this O(N) etc. explanations a lot, but not sure > what they exactly mean. http://en.wikipedia.org/wiki/Big_O_notation Roughly, we say that an algorithm

RE: [sqlite] Re: Re: Re: Re: How does SQLite choose the index?

2007-08-04 Thread RB Smissaert
index? RB Smissaert <[EMAIL PROTECTED]> wrote: > One thing I am not sure about yet is when an index would be helpful > in the > first place in relation to the data in the field. > I understand an index is going to help little if the values in a > particular > field

RE: [sqlite] Re: Re: How does SQLite choose the index?

2007-08-04 Thread RB Smissaert
with this or is it just the field order in the query and field order in the index that matter? RBS -Original Message- From: Igor Tandetnik [mailto:[EMAIL PROTECTED] Sent: 04 August 2007 15:49 To: SQLite Subject: [sqlite] Re: Re: How does SQLite choose the index? RB Smissaert <[EM

RE: [sqlite] Re: Re: How does SQLite choose the index?

2007-08-04 Thread RB Smissaert
15:49 To: SQLite Subject: [sqlite] Re: Re: How does SQLite choose the index? RB Smissaert <[EMAIL PROTECTED]> wrote: > So, basically it is best to make one large index (apart from the > primary > integer key?) that includes all fields that could be in a WHERE > clause or a > JOI

RE: [sqlite] Re: How does SQLite choose the index?

2007-08-04 Thread RB Smissaert
or a JOIN or a GROUP BY or a HAVING or an ORDER BY? RBS -Original Message- From: Igor Tandetnik [mailto:[EMAIL PROTECTED] Sent: 04 August 2007 15:18 To: SQLite Subject: [sqlite] Re: How does SQLite choose the index? RB Smissaert <[EMAIL PROTECTED]> wrote: > I get this query plan

[sqlite] How does SQLite choose the index?

2007-08-04 Thread RB Smissaert
Given this table: CREATE TABLE AMorb37F6_E ([PATIENT_ID] INTEGER, [ENTRY_ID] INTEGER PRIMARY KEY, [READ_CODE] TEXT, [ADDED_DATE] TEXT, [START_DATE] TEXT) And these indexes: IDX10$ENTRY$PATIENT_ID IDX11$ENTRY$TERM_TEXT IDX12$ENTRY$READ_CODE$ADDED_DATE

Re: [sqlite] strategy adding indexes

2007-07-31 Thread RB Smissaert
Re: [sqlite] strategy adding indexes drh Tue, 31 Jul 2007 03:12:54 -0700 T <[EMAIL PROTECTED]> wrote: > Hi RBS, > > > - indexes that include all possible combinations of fields that may > > appear > > in a WHERE clause. > > As an aside, note that, AFAIK, indexes are only used: > > 1. To

RE: [sqlite] strategy adding indexes

2007-07-30 Thread RB Smissaert
Hi Tom, Thanks for that; useful to know. Didn't know about point 1 and 2 and that will complicate matters a bit further. RBS -Original Message- From: T [mailto:[EMAIL PROTECTED] Sent: 31 July 2007 00:39 To: sqlite-users@sqlite.org Subject: Re: [sqlite] strategy adding indexes Hi RBS,

[sqlite] strategy adding indexes

2007-07-30 Thread RB Smissaert
What would be a good strategy in adding indexes to the various tables? I know SQLite can only use one index in simple (not intersect etc.) queries, so is it usually best to make: - indexes that include all possible combinations of fields that may appear in a WHERE clause. - make one very large

RE: [sqlite] Interrupt SQLite

2007-07-18 Thread RB Smissaert
Thanks, that is a very useful tip! RBS -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: 18 July 2007 00:56 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Interrupt SQLite "RB Smissaert" <[EMAIL PROTECTED]> wrote: > Is it somehow po

[sqlite] Interrupt SQLite

2007-07-17 Thread RB Smissaert
Is it somehow possible to interrupt an ongoing INSERT operation? I made a mistake in an index and now got into a very long process that I would like to stop. I am running this from VBA via the dll from Olaf Schmidt, dhSQLite.dll. I don't want to kill Excel as I would lose some work. Thanks for any

RE: [sqlite] Re: inner join

2007-07-16 Thread RB Smissaert
That is an interesting one. Where could I find documentation about coalesce? RBS -Original Message- From: Igor Tandetnik [mailto:[EMAIL PROTECTED] Sent: 16 July 2007 12:49 To: SQLite Subject: [sqlite] Re: inner join Andre du Plessis <[EMAIL PROTECTED]> wrote: > I would like to be able

RE: [sqlite] Re: How to store 128 bit values

2007-07-11 Thread RB Smissaert
e else. RBS -Original Message- From: Igor Tandetnik [mailto:[EMAIL PROTECTED] Sent: 11 July 2007 20:18 To: SQLite Subject: [sqlite] Re: How to store 128 bit values RB Smissaert <[EMAIL PROTECTED]> wrote: > It seems if you do inserts on a table it is faster if you have no > INT

RE: [sqlite] How to store 128 bit values

2007-07-11 Thread RB Smissaert
m: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: 11 July 2007 20:17 To: sqlite-users@sqlite.org Subject: Re: [sqlite] How to store 128 bit values "RB Smissaert" <[EMAIL PROTECTED]> wrote: > > Looking up a record by INTEGER PRIMARY KEY is always twice as > > fast as lo

RE: [sqlite] How to store 128 bit values

2007-07-11 Thread RB Smissaert
> Looking up a record by INTEGER PRIMARY KEY is always twice as > fast as looking up the same record by any other key Didn't realize that, but I have a question in connection with this. It seems if you do inserts on a table it is faster if you have no INTEGER PRIMARY KEY on that table and then

RE: [sqlite] error in round-function?

2007-06-10 Thread RB Smissaert
Just checked my code and luckily I don't round in SQLite. I suppose an easy work-around for now would be to do something like: Select round(field + 0.001, 1) as it will be unlikely you are dealing with 0.949 RBS -Original Message- From: Olaf Schmidt [mailto:[EMAIL

RE: [sqlite] Age calculation on literal

2007-06-03 Thread RB Smissaert
ld & ")) * 12 + " & _ "(" & strCM & " - strftime('%m', " & strField & "))) - 1 " & _ "else " & _ "((" & strCY & " - strftime('%Y', " & strField & ")) * 12 + " &

RE: [sqlite] Age calculation on literal

2007-06-01 Thread RB Smissaert
ftime('%Y', " & strField & ") - 1) * 12 + " & _ "(strftime('%m', 'now') + (12 - strftime('%m', " & strField & "))) - 1 " & _ "else " & _ &quo

RE: [sqlite] Age calculation on literal

2007-06-01 Thread RB Smissaert
- 1) * 12 + (strftime('%m', 'now') + (12 - strftime('%m', '2006-10-14'))) end end RBS -Original Message- From: Dennis Cote [mailto:[EMAIL PROTECTED] Sent: 01 June 2007 19:53 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Age calculation on literal On 6/1/07, RB Smissaert <[EMA

RE: [sqlite] Age calculation on literal

2007-06-01 Thread RB Smissaert
, RB Smissaert <[EMAIL PROTECTED]> wrote: > > Got this nearly worked out now, but somehow I can't get the nested case > when > syntax right: > > SELECT > case > when > date('2006-10-14', '+' || (strftime('%Y', 'now') - strftime('%Y', > '2006-10-14')) || ' years

RE: [sqlite] Age calculation on literal

2007-06-01 Thread RB Smissaert
006-10-14'))) end end It will give me an error (from my VB wrapper) syntax error near else. Any idea what is wrong here? RBS -Original Message- From: Dennis Cote [mailto:[EMAIL PROTECTED] Sent: 31 May 2007 22:17 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Age calculation on li

RE: [sqlite] Age calculation on literal

2007-05-31 Thread RB Smissaert
ind of things ... RBS -Original Message- From: Dennis Cote [mailto:[EMAIL PROTECTED] Sent: 31 May 2007 22:17 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Age calculation on literal RB Smissaert wrote: > Thanks to Dennis Cote I got a nice way to get the age from the date in the

RE: [sqlite] Age calculation on literal

2007-05-31 Thread RB Smissaert
then DOB Age in months -- 2007-05-01 0 2007-04-30 1 2007-01-01 4 Etc. RBS -Original Message- From: Dennis Cote [mailto:[EMAIL PROTECTED] Sent: 31 May 2007 22:17 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Age calculation on literal RB Smissaert

[sqlite] Age calculation on literal

2007-05-31 Thread RB Smissaert
Thanks to Dennis Cote I got a nice way to get the age from the date in the form '-nmm-dd'. It works fine when I run it on a field, but when I run it on a literal date it gives me 100 too much: select case when date('2002-01-01', '+' || (strftime('%Y', 'now') - strftime('%Y', '2002-01-01'))

RE: [sqlite] Longest "real" SQL statement

2007-05-10 Thread RB Smissaert
This is one of my biggest and it is part of a number of queries to transpose a table: INSERT OR REPLACE INTO A3BP619_J(PATIENT_ID, ENTRY_ID_E1, START_DATE_E1, ADDED_DATE_E1, SYST_E1, DIAST_E1, ENTRY_ID_E2, START_DATE_E2, ADDED_DATE_E2, SYST_E2, DIAST_E2, ENTRY_ID_E3, START_DATE_E3, ADDED_DATE_E3,

RE: [sqlite] excel and sqlite

2007-05-07 Thread RB Smissaert
I have been using SQLite in Excel for the last half year now (in a commercial application) and I think I will be able to help. Currently I am using the wrapper written by Olaf Schmidt and this works very well: www.datenhaus.de/Downloads/dhSQLite-Demo.zip If you are interested then I can send you

RE: [sqlite] Search on Age, from DOB column

2007-05-03 Thread RB Smissaert
I am also working with a clinical application, using SQLite and VBA. I use this function to produce the SQL to convert dates in the ISO8601 format to an integer age. Function ISO8601Date2Age(strField, Optional strAlias As String) As String Dim strAS As String If Len(strAlias) > 0 Then

RE: [sqlite] Difference in these indices?

2007-03-28 Thread RB Smissaert
To: sqlite-users@sqlite.org Subject: Re: [sqlite] Difference in these indices? RB Smissaert wrote: > Does this only apply to table and column names? > I will never use double quote characters in my identifier > names, so there should be no problem there. > > It applies to all the ide

RE: [sqlite] Difference in these indices?

2007-03-28 Thread RB Smissaert
: 28 March 2007 15:41 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Difference in these indices? RB Smissaert wrote: > As to quotes etc. > As my code works fine as it is I probably will leave this as the double > quotes look ugly and it will be a reasonably big job to alter all this. &g

RE: [sqlite] Difference in these indices?

2007-03-27 Thread RB Smissaert
Difference in these indices? RB Smissaert wrote: > Is it right that this won't affect the speed of any subsequent inserts or > deletes? > Well inserts will be done in id order. If you have predefined ids assigned by some outside source and specify them when you insert into sqlite, it

RE: [sqlite] Difference in these indices?

2007-03-27 Thread RB Smissaert
] Sent: 27 March 2007 23:34 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Difference in these indices? "RB Smissaert" <[EMAIL PROTECTED]> wrote: > Thanks for that. > So if I can then I should create the table with INTEGER PRIMARY KEY. > Is it right that this won'

RE: [sqlite] Difference in these indices?

2007-03-27 Thread RB Smissaert
PRIMARY KEY)" RBS -Original Message- From: Dennis Cote [mailto:[EMAIL PROTECTED] Sent: 27 March 2007 22:51 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Difference in these indices? RB Smissaert wrote: > Is there any difference in an index created like this: > > Create

[sqlite] Difference in these indices?

2007-03-27 Thread RB Smissaert
Is there any difference in an index created like this: Create table 'table1'([ID] INTEGER PRIMARY KEY) with this: Create table 'table1'([ID] INTEGER) Create unique index idx_table1_ID on table1(ID) I tended to use the first form, but as that can make subsequent table inserts or deletes slower

RE: [sqlite] Any way to do this faster?

2007-03-26 Thread RB Smissaert
Had a good look at this now and doing: delete from tableB where not exists (select id from tableA where tableA.id = tableB.id) Is indeed quite a bit faster than doing: delete from tableB where id not in (select tableA.id from tableA) In my case about 3 times as fast. Looking at the query plan

[sqlite] Any way to do this faster?

2007-03-25 Thread RB Smissaert
Simplified I have the following situation: 2 tables, tableA and tableB both with an integer field, called ID, holding unique integer numbers in tableA and non-unique integer numbers in tableB. Both tables have an index on this field and for tableA this is an INTEGER PRIMARY KEY. Now I need to

RE: [sqlite] Question about speed of CASE WHEN

2007-03-18 Thread RB Smissaert
[mailto:[EMAIL PROTECTED] Sent: 18 March 2007 23:46 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Question about speed of CASE WHEN RB Smissaert wrote: > Ok, now done some better testing and the method with CASE WHEN is indeed, as > expected a bit faster To me the lookup table method

RE: [sqlite] Question about speed of CASE WHEN

2007-03-18 Thread RB Smissaert
Message- From: RB Smissaert [mailto:[EMAIL PROTECTED] Sent: 18 March 2007 17:17 To: sqlite-users@sqlite.org Subject: RE: [sqlite] Question about speed of CASE WHEN Done some testing now and surprisingly, it seems the lookup method with a join to a lookup table is very slightly faster than the CASE

RE: [sqlite] Question about speed of CASE WHEN

2007-03-18 Thread RB Smissaert
'Issue > when 2 then 'Note' Etc But didn't get that to work as it always produced the first WHEN option. RBS -Original Message- From: Christian Smith [mailto:[EMAIL PROTECTED] Sent: 18 March 2007 15:29 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Question about speed of CASE

RE: [sqlite] Question about speed of CASE WHEN

2007-03-18 Thread RB Smissaert
Sorry, had to rush off and missed your alternative. Will do some testing now. RBS -Original Message- From: T [mailto:[EMAIL PROTECTED] Sent: 18 March 2007 14:55 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Question about speed of CASE WHEN Hi RBS, >> Perhaps the alternative form

  1   2   3   >