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
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
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,
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'))
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
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
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
, 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
- 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
ftime('%Y', " &
strField & ") - 1) * 12 + " & _
"(strftime('%m', 'now') + (12 - strftime('%m', "
& strField & "))) - 1 " & _
"else " & _
&quo
ld & ")) * 12 + " & _
"(" & strCM & " - strftime('%m', " & strField & "))) - 1 " & _
"else " & _
"((" & strCY & " - strftime('%Y', " & strField & ")) * 12 + " &
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
> 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
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
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
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
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
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
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
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
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
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
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
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
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
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
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: 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
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?
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
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
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
>
>
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
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
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
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
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
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
> 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
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
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
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
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
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
>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:
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
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
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
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 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
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
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
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
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
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
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
> 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
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
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
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
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
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
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
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
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 =
, 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
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
-
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
.
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
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
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
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
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
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
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*';
>
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
);
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
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
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;
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
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
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
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
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
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
> 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,
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
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&
-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
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 (
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
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
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
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
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
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
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:
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
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
add a custome function
to Sqlite to achieve it from your SQL statement.
RB Smissaert wrote:
> Just thought of one reason why it I need something else in SQLite than
> mmdd in the date field and that is because I need date comparisons
> between different tables. So, I need to do: is f
1 - 100 of 289 matches
Mail list logo