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] 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] 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,

[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] 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

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-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-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
- 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
ftime('%Y', " & strField & ") - 1) * 12 + " & _ "(strftime('%m', 'now') + (12 - strftime('%m', " & strField & "))) - 1 " & _ "else " & _ &quo

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] 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] 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] 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] 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] 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

[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] 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] 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] 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,

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

[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] 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

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: 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: 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: 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: 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: 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?

[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-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

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

[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] 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

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] [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] 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] 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] 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] 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] 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

[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] 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] 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] 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] 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] 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

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] 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

[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

[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] 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

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] What is the best way to connect to SQLite from VB/VBA?

2006-11-12 Thread RB Smissaert
Have spent 2 days looking at all the different wrappers and the one ODBC driver and maybe the best one is the commercial dll from Terra... Still, I would be very interested what opinions are about the best (speed, ease of use, so methods etc. close to ADO) way to connect. This is from VB6/VBA. The

RE: [sqlite] What is the best way to connect to SQLite from VB/VBA?

2006-11-13 Thread RB Smissaert
driver makes things easier indeed. RBS -Original Message- From: Carlos Avogaro [mailto:[EMAIL PROTECTED] Sent: 13 November 2006 12:04 To: sqlite-users@sqlite.org Subject: Re: [sqlite] What is the best way to connect to SQLite from VB/VBA? With the odbc driver, is ease and faster RB

RE: [sqlite] INSERT INTO with SELECT

2006-11-13 Thread RB Smissaert
PROTECTED] Sent: 14 November 2006 03:24 To: sqlite-users@sqlite.org Subject: Re: [sqlite] INSERT INTO with SELECT On 11/13/06, RB Smissaert <[EMAIL PROTECTED]> wrote: > Trying to move data from Interbase to SQLite via the ODBC driver and ADO and > having trouble to get the

RE: [sqlite] LEFT OUTER JOIN + INNER JOIN problem

2006-11-14 Thread RB Smissaert
> This is fixed in 3.3.6. 3.3.8 is current. A very basic question: What do these figure refer to? I can see the commandline SQLite utility, but no other .exe files. I am connecting in VB/VBA with a third-party dll. Do I have to do anything when there is an update in SQLite? Great software this

RE: [sqlite] INSERT INTO with SELECT

2006-11-14 Thread RB Smissaert
the .db file and the table nil happened, so no data moved. RBS -Original Message- From: Jay Sprenkle [mailto:[EMAIL PROTECTED] Sent: 14 November 2006 14:06 To: sqlite-users@sqlite.org Subject: Re: [sqlite] INSERT INTO with SELECT On 11/13/06, RB Smissaert <[EMAIL PROTECTED]> wrote

[sqlite] Importing text file via .bat file

2006-11-14 Thread RB Smissaert
Have figure out now what the quickest way is to move data from Interbase to a SQLite db file: IB to ADO recordset Recordset to text Import the text file with the .import command. Now I am trying to figure out how to automate the last step with a .bat file. What I got sofar is: Have a SQL file

RE: [sqlite] Importing text file via .bat file

2006-11-15 Thread RB Smissaert
Hi Donald, Thanks, but I don't quite get it yet. What is in this file MyCommand.tmp? Is there no way to put the whole thing in on .bat file or even better run the whole sequence from VB? RBS -Original Message- From: Griggs, Donald [mailto:[EMAIL PROTECTED] Sent: 15 November 2006 15:02

RE: [sqlite] Importing text file via .bat file

2006-11-15 Thread RB Smissaert
Kees, Just one other thing needed. In the .sql file is there a way to notify VB that the text import is finished? I run the .bat file now from VBS, so it won't be visible. I couldn't see anything suitable in the dot commands to tell VB. RBS -Original Message- From: Kees Nuyt

RE: [sqlite] Importing text file via .bat file

2006-11-15 Thread RB Smissaert
Subject: RE: [sqlite] Importing text file via .bat file Echo .mode csv >MyCommands.tmp Echo .import ReadCode.txt ReadCode >>MyCommands.tmp Sqlite3 ReadCode.db ".read myCommands.tmp" = -Original Message- From: RB Smiss

RE: [sqlite] Importing text file via .bat file

2006-11-15 Thread RB Smissaert
via .bat file Echo .mode csv >MyCommands.tmp Echo .import ReadCode.txt ReadCode >>MyCommands.tmp Sqlite3 ReadCode.db ".read myCommands.tmp" = -Original Message- From: RB Smissaert [mailto:[EMAIL PROTECTED] Sent: Wed

RE: [sqlite] Importing text file via .bat file

2006-11-15 Thread RB Smissaert
Didn't know about .cmd scripts (not that I know much about .bat) and will have a look at that. There is a slim chance that some of my users are still on Win98, so that could be a problem. Will have a look at Sqlite3Explorer as well. Only started with SQLite a few days ago, but I can see it is

RE: [sqlite] Importing text file via .bat file

2006-11-15 Thread RB Smissaert
There is no actual VBS file. I run it like this from VBA: Sub UpdateReadSQLite2(bShowSQL As Boolean) Dim oShell UpdateReadTextFile bShowSQL, True Application.StatusBar = _ "transferring the data from ReadCodeNoQuotes.txt to the SQLite DB" Set oShell =

RE: [sqlite] Importing text file via .bat file

2006-11-15 Thread RB Smissaert
, monitor the specific file for a Rename event. Of course, I don't know that VB 6 supports this, but I know that VB.Net does. -- Eric Pankoke Founder / Lead Developer Point Of Light Software http://www.polsoftware.com/ -- Original message -- From: "RB Smis

RE: [sqlite] Importing text file via .bat file

2006-11-15 Thread RB Smissaert
insert into ReadCode values (...); ... commit; --END SQL Then issue the same command from VB to start the job: sqlite3 c:\sqlite\ReadCode.db ".read c:\sqlite\ReadCode.sql" Either way, you would be able to eliminate the batch file, and handle everything from within VB. -Clark -

RE: [sqlite] Importing text file via .bat file

2006-11-15 Thread RB Smissaert
Message From: RB Smissaert <[EMAIL PROTECTED]> To: sqlite-users@sqlite.org Sent: Wednesday, November 15, 2006 2:16:32 PM Subject: RE: [sqlite] Importing text file via .bat file > sqlite3 c:\sqlite\ReadCode.db ".read c:\sqlite\ReadCode.sql" Not sure how that would work fro

RE: [sqlite] INSERT INTO with SELECT

2006-11-15 Thread RB Smissaert
. What format is your text file in? What separates one field from another? On 11/14/06, RB Smissaert <[EMAIL PROTECTED]> wrote: > Thanks and in fact I had a look at your text importer about an hour ago. > Couldn't figure out the parameters though. > How would these be if I wanted t

[sqlite] select from commandprompt with output to file

2006-11-16 Thread RB Smissaert
How do I do this: >From the command prompt issue a simple select query to a specified database and direct the output to a file. I can see there is the .output FILENAME option, but nothing seems to happen. .output stdout works fine with output to the screen. Must be overlooking something simple

RE: [sqlite] select from commandprompt with output to file

2006-11-16 Thread RB Smissaert
Hi Kees, Not sure what I did wrong, but I got it working now. Just wondering now if I actually need the VB wrapper. Looks all can be done with command-line work. RBS -Original Message- From: Kees Nuyt [mailto:[EMAIL PROTECTED] Sent: 16 November 2006 21:36 To: sqlite-users@sqlite.org

RE: [sqlite] select from commandprompt with output to file

2006-11-16 Thread RB Smissaert
Kees, How would I run these 4 commands via a .bat file or via whatever means: cd c:\test\ReadCodes c:\test\Program\sqlite3 c:\test\ReadCodes\ReadCode.db" .output testfile.txt select * from readcode where read_code glob 'G2*'; It must be simple, but I can't see it. RBS -Original

RE: [sqlite] select from commandprompt with output to file

2006-11-17 Thread RB Smissaert
To: sqlite-users@sqlite.org Subject: Re: [sqlite] select from commandprompt with output to file "RB Smissaert" <[EMAIL PROTECTED]> writes: > How would I run these 4 commands via a .bat file or via whatever means: > > cd c:\test\ReadCodes > c:\test\Program\sqlite3 c

RE: [sqlite] select from commandprompt with output to file

2006-11-17 Thread RB Smissaert
Latest SQLite, 2000 to XP. I have the output to text working now when I type it at the command prompt, but I now need to figure out how to do the whole sequence without any user action. I has to run from VB or maybe a VBS file or anything that can be initiated from VB/VBA. RBS -Original

RE: [sqlite] select from commandprompt with output to file

2006-11-17 Thread RB Smissaert
with output to file RB Smissaert wrote: > How would I run these 4 commands via a .bat file or via whatever means: > > cd c:\test\ReadCodes > c:\test\Program\sqlite3 c:\test\ReadCodes\ReadCode.db" > .output testfile.txt > select * from readcode where read_code glob 'G2*'; >

RE: [sqlite] select from commandprompt with output to file

2006-11-17 Thread RB Smissaert
to file RB Smissaert wrote: > How would I run these 4 commands via a .bat file or via whatever means: > > cd c:\test\ReadCodes > c:\test\Program\sqlite3 c:\test\ReadCodes\ReadCode.db" > .output testfile.txt > select * from readcode where read_code glob 'G2*'; > > It mu

RE: [sqlite] Create table / data types

2006-11-19 Thread RB Smissaert
); Is there any recommended way or does it just not matter? RBS -Original Message- From: RB Smissaert [mailto:[EMAIL PROTECTED] Sent: 19 November 2006 23:26 To: sqlite-users@sqlite.org Subject: [sqlite] Create table / data types New to SQLite and wondering what the correct syntax

RE: [sqlite] Create table / data types

2006-11-19 Thread RB Smissaert
Yes, SHORT should translate to Integer. How about the syntax with create table? Thanks. RBS -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: 19 November 2006 23:45 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Create table / data types "RB Smis

RE: [sqlite] Create table / data types

2006-11-19 Thread RB Smissaert
PROTECTED] Sent: 19 November 2006 23:49 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Create table / data types "RB Smissaert" <[EMAIL PROTECTED]> wrote: > I can see that doing this: > > create table ReadCode > (SUBJECT_TYPE varchar > READ_CODE varchar >

[sqlite] INSERT INTO with external database?

2006-11-22 Thread RB Smissaert
In VB with an ODBC connection to an Interbase database it is possible to move data from Interbase to Access with a SQL construction like this: INSERT INTO ACCESSTABLE (SUBJECT_TYPE, READ_CODE, TERM30, TERM60) SELECT IB.SUBJECT_TYPE, IB.READ_CODE, IB.TERM30, IB.TERM60 FROM IBTABLE IB IN [ODBC;

[sqlite] fastest way to transfer ADO recordset to SQLite db file

2006-11-22 Thread RB Smissaert
What is the fastest way in VB to transfer an ADO recordset to a SQLite .db file? I am using the dll from TerraInformatica, SQLiteDb.dll. This is what I have now: Set oSQLConn = New SQLiteDb.Connection With oSQLConn .ConnectionString = "Data Source=" & strReadSQLiteDB .Open

RE: [sqlite] select help

2006-11-24 Thread RB Smissaert
Should your query not be something like this: select count(*) from blocklists where blockval like '%alexandre%' RBS -Original Message- From: Alexandre Busquets Triola [mailto:[EMAIL PROTECTED] Sent: 24 November 2006 23:48 To: sqlite-users@sqlite.org Subject: [sqlite] select help

[sqlite] slow query

2006-11-26 Thread RB Smissaert
Although SQLite seems very fast, I now have come across a query that runs extremely slow and I would be interested why this is: There are 3 tables involved, all fairly small, some 25000 rows: PATIENT, ADDRESS and ADDRESSLINK All the relevant fields are indexed. This is the query: SELECT

RE: [sqlite] slow query

2006-11-26 Thread RB Smissaert
Message- From: RB Smissaert [mailto:[EMAIL PROTECTED] Sent: 26 November 2006 10:04 To: sqlite-users@sqlite.org Subject: [sqlite] slow query Although SQLite seems very fast, I now have come across a query that runs extremely slow and I would be interested why this is: There are 3 tables involved

RE: [sqlite] Saving tables

2006-11-27 Thread RB Smissaert
Just to clear up one thing that is not 100% clear to me. When you are using SQLite on your machine is it true that you don't need to have anything installed if you are using a VB wrapper dll. This wrapper can Create, update, select etc. and nil else is needed. Now, if I am using this wrapper and

RE: [sqlite] Saving tables

2006-11-27 Thread RB Smissaert
changes or is removed. -- Eric Pankoke Founder / Lead Developer Point Of Light Software http://www.polsoftware.com/ -- Original message -- From: "RB Smissaert" <[EMAIL PROTECTED]> > Just to clear up one thing that is not 100% clear to me. > Whe

RE: [sqlite] Saving tables

2006-11-27 Thread RB Smissaert
> sqlite3 mydatabase.db3 I always use the extension .db What is the difference between db3 and db or maybe db2 etc.? RBS -Original Message- From: Kees Nuyt [mailto:[EMAIL PROTECTED] Sent: 27 November 2006 22:49 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Saving tables On Mon,

RE: [sqlite] C++ SQLite

2006-11-28 Thread RB Smissaert
Will have a look, but I was looking for a text geared towards VB. I take it the documentation that comes with SQLite is all to do with C. In fact I already have a wrapper that seems to work well, the one from TerraInformatica, but maybe there was more control if I could write my own. RBS

RE: [sqlite] C++ SQLite

2006-11-28 Thread RB Smissaert
Message- From: John Stanton [mailto:[EMAIL PROTECTED] Sent: 28 November 2006 18:43 To: sqlite-users@sqlite.org Subject: Re: [sqlite] C++ SQLite If you can use the Win32 API you can use the Sqlite API. Just because they can be called from C programs does not make them "all to do with C&

RE: [sqlite] C++ SQLite

2006-11-28 Thread RB Smissaert
-users@sqlite.org Subject: RE: [sqlite] C++ SQLite RB Smissaert said: > You might be right, but with the Win32 API you have loads of nice > documents/programs (I like the API guide from KPD) that help you out. > All I have to do is copy their declares straight to VB and look at the > d

RE: [sqlite] C++ SQLite

2006-11-28 Thread RB Smissaert
have to do is to make yourself a list og VB types cross referenced to the fundamental type used in the Win32 and Sqlite APIs. Then you could link any library into your VB programs. RB Smissaert wrote: > You might be right, but with the Win32 API you have loads of nice > documents/programs (

[sqlite] When to run analyze?

2006-12-01 Thread RB Smissaert
Having a reasonably big SQLite database, 21 tables, one table with some millions of rows, overall file size about 1.3 Gb. This table will only get SELECT statements once it has been created. I have now run analyze on this file and it does speed queries up indeed. Do I run analyze after the table

RE: [sqlite] When to run analyze?

2006-12-01 Thread RB Smissaert
21:01 To: sqlite-users@sqlite.org Subject: Re: [sqlite] When to run analyze? "RB Smissaert" <[EMAIL PROTECTED]> wrote: > Having a reasonably big SQLite database, 21 tables, one table with some > millions of rows, overall file size about 1.3 Gb. This table will only get &g

RE: [sqlite] When to run analyze?

2006-12-01 Thread RB Smissaert
TED] [mailto:[EMAIL PROTECTED] Sent: 01 December 2006 21:01 To: sqlite-users@sqlite.org Subject: Re: [sqlite] When to run analyze? "RB Smissaert" <[EMAIL PROTECTED]> wrote: > Having a reasonably big SQLite database, 21 tables, one table with some > millions of rows, overa

RE: [sqlite] When to run analyze?

2006-12-01 Thread RB Smissaert
OK, have sent the file to [EMAIL PROTECTED] RBS -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: 02 December 2006 00:00 To: sqlite-users@sqlite.org Subject: Re: [sqlite] When to run analyze? "RB Smissaert" <[EMAIL PROTECTED]> wrote: > &g

[sqlite] Dealing with dates in the format yyyymmdd

2006-12-03 Thread RB Smissaert
When moving data from Interbase to SQLite I have to convert integer dates in the format mmdd to Excel dates. These are integer numbers counting the days past 31 December 1899. With substr I can make it dd/mm/ (I am in the UK and that is the normal way to format dates) but the problem is it

RE: [sqlite] Dealing with dates in the format yyyymmdd

2006-12-03 Thread RB Smissaert
Forgot to say that I could stick a single quote in front of the dates in SQLite and that would prevent the US date format, but it means I have no dates anymore in the sheet, but strings, so I can't sort properly and I can't do date calculations. RBS -Original Message- From: RB Smissaert

RE: [sqlite] Dealing with dates in the format yyyymmdd

2006-12-03 Thread RB Smissaert
nd enter "=today()" as a value in that cell. Have not fooled with Excel much lately, but I think you can even format a spreadsheet programmatically. Fred > -Original Message- > From: RB Smissaert [mailto:[EMAIL PROTECTED] > Sent: Sunday, December 03, 2006 8:21 AM > To:

RE: [sqlite] Dealing with dates in the format yyyymmdd

2006-12-03 Thread RB Smissaert
not fooled with Excel much lately, but I think you can even format a spreadsheet programmatically. Fred > -Original Message- > From: RB Smissaert [mailto:[EMAIL PROTECTED] > Sent: Sunday, December 03, 2006 8:21 AM > To: sqlite-users@sqlite.org > Subject: [sqlite] Dealing with d

RE: [sqlite] Dealing with dates in the format yyyymmdd

2006-12-03 Thread RB Smissaert
all the date fields in a VBA loop, but that might be a bit slow. So, if anybody has an idea how to convert integer mmdd to the Excel date format in SQLite I would be interested. RBS -Original Message- From: RB Smissaert [mailto:[EMAIL PROTECTED] Sent: 03 December 2006 15:37 To: sql

  1   2   3   >