Re: [sqlite] Quick FTS3 question: partial matches

[Jonathon]

Thanks for the reply.   I was just curious if anyone has found a viable
workaround for this issue?  Using the dreaded "Like" is a bit too slow for
what I need.

Jon

On Thu, Oct 9, 2008 at 8:50 PM, Alexandre Courbot <[EMAIL PROTECTED]> wrote:

> > I would like to look up partial strings across several columns using FTS3
> > and MATCH.   For example, if I have a column that has data,
> > 'helloThisIsATest'... and I do:
> >
> > select * from myTable where myTable match '*This*';
>
> You can only use wildcards as a suffix - this is due to the nature of
> the indexes fts3 uses.
>
> I don't know of a possible workaround for this while still using fts3.
> I'd say that for this kind of request, you are condemned to use '...
> like "%This%".
>
> Alex.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite version 3.6.4 planned for 2008-10-15

[D. Richard Hipp]

On Oct 9, 2008, at 9:11 PM, dcharno wrote:

>> If you have issues or concerns with any aspect of the upcoming
>> release, now would be a good time to raise them.
>
> Is there any way to have both the BNF and syntax diagrams in the SQL  
> Syntax?


Not really.  Explain to me again why you want BNF instead of syntax  
diagrams?  Most people find the syntax diagrams to be much easier to  
understand.


D. Richard Hipp
[EMAIL PROTECTED]



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Adding index to table makes SELECT much slower. Why?

[Ken]

Sure post it data and the website for download.

I'd like to see if it's reproducible on other systems.

Thanks,
Ken



--- On Thu, 10/9/08, James Pringle <[EMAIL PROTECTED]> wrote:

> From: James Pringle <[EMAIL PROTECTED]>
> Subject: Re: [sqlite] Adding index to table makes SELECT much slower. Why?
> To: sqlite-users@sqlite.org
> Date: Thursday, October 9, 2008, 11:16 AM
> Hi all-
> 
>Thanks for your many suggestions.  I have tried many of
> your suggestions,
> and found the following:
> 
> 
>1. If I create the database with depth ordered from
> least to greatest,
>and then create an index, the searches run an order of
> magnitude faster than
>without an index.  I.e. the search for matching entries
> in the data base
>concludes in about 9 seconds, instead of the 90 seconds
> it had before.
>Thanks to "developir" for this suggestion!
>2. searching via "rowid" makes no difference.
>3. doing vacuum makes no difference, since this is a
> write once, read
>many database.
>4. ulimit on my machine is unlimited, so this was not
> important for me.
>5. Cache size seems to make no difference -- and the
> SQLite documentation
>says it should only be important for DELETE's and
> UPDATE's.
>6. I like the idea of using RTREE, however, I don't
> want each of the
>students who use this database to have to recompile the
> version of SQLite
>that comes with their version of python.
> 
> Several of you, in particular Ken, suggested that I run SQL
> queries that
> would provide some output that would be helpful in figuring
> out what was
> going on.The database is now more than fast enough for
> me, so I would
> not do this on my own.  However, if others on the mailing
> list would like to
> see this output in order to make SQLite better (or just for
> their own
> curiosity), please feel free to let me know, and I can make
> the runs.
> 
> I have a small subset of the data (450Mb) which exhibits
> the same
> behaviour.  The data is public (it is from the National
> Ocean Database), and
> so if anyone wants to see it I would be happy to put it on
> my web server.
> 
> Cheers,
> and thanks to everyone who helped me!
> Jamie Pringle
> 
> On Wed, Oct 8, 2008 at 6:50 PM, James Pringle
> <[EMAIL PROTECTED]> wrote:
> > Hi-
> >
> >  I am new to sqlite, and am having a puzzling problem.
>  I have read
> > that adding an INDEX to a sqlite table can never make
> a SELECT
> > statement slower.  However, in my case, it seems to be
> making the
> > select statement an order of magnitude slower, which
> is not what I
> > wanted!  What could be going on?
> >
> >  I am calling SQLite from python 2.5 from fink useing
> the sqlite3
> > module on OS X 10.5 with 2Gb of memory.  My data base
> contains 32
> > million lines of ocean hydrographic data, stored on
> disk, with the
> > table created with the following statement:
> >
> >CREATE TABLE hydro (lat REAL, lon REAL, year
> INTEGER,
> >month INTEGER, day INTEGER, time REAL, cast_id
> INTEGER,
> >depth REAL, T REAL, S REAL, water_depth REAL)
> >
> > When I perform a SELECT that returns about 0.6% of the
> data in the
> > database (about 200,000 records)
> >
> >SELECT lat, lon, depth FROM hydro WHERE depth>=
> 49.01 AND depth <=
> 50.99
> >
> > It selects and returns the records in 82 seconds.  I
> wished to improve
> > on this time, so I created an index with the following
> command:
> >
> >CREATE INDEX hydro_indx ON hydro (depth)
> >
> > I then tried the SELECT command again, and read the
> results into
> > memory, and it took 717 seconds!?!
> >
> > The "depth" field contains many distinct
> numeric values, with no
> > specific value matching more then 0.5% of the data
> base.  When I DROP
> > the index with the command
> >
> >DROP INDEX hydro_indx
> >
> > The SELECT time returns to about 80 seconds,
> confirming that it is the
> > index which is slowing things down.  What is going on?
>  I have
> > repeated and confirmed these timings.
> >
> > I have listened for disk chatter and monitored the
> system, and it does
> > not seem to be thrashing swap, or otherwise becoming
> unresponsive.
> >
> > I have two questions:
> >
> >1) Why is the index making things slower?
> >2) How can I make my SELECT statement faster?  The
> primary
> > selection will be done
> >   on the "depth" and
> "water_depth" keys.
> >
> > I thank you for your time.
> >
> > Cheers,
> > Jamie Pringle
> >
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Quick FTS3 question: partial matches

[Jonathon]

Hello all,

I've looked through the archived mailing list and although I am sure my
question is common, for some reason, I couldn't find an answer.

I would like to look up partial strings across several columns using FTS3
and MATCH.   For example, if I have a column that has data,
'helloThisIsATest'... and I do:

select * from myTable where myTable match '*This*';

I would like to return the row above.  However, I've tried this and it
doesn't seem to work.  It seems that prefix matching works though.  So, if I
do:  select * from myTable where myTable match 'hello*';  that seems to
return the right result.

I am currently using FTS3 in version 3.6.2 and I have all the columns that I
would like to index in the same virtual table.

Thanks,
Jon
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite version 3.6.4 planned for 2008-10-15

[dcharno]

> If you have issues or concerns with any aspect of the upcoming  
> release, now would be a good time to raise them.

Is there any way to have both the BNF and syntax diagrams in the SQL Syntax?

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] assert() in sqlite3_initialize() when SQLITE_DEBUG is defined

[D. Richard Hipp]

On Oct 9, 2008, at 7:42 PM, Dave Toll wrote:
>
> I noticed that similar tests elsewhere in the code check that the size
> of u64 is either 8 or 4. I'm building SQLite 3.6.3 with the following
> defines: -DSQLITE_INT64_TYPE=long -DSQLITE_32BIT_ROWID=1


There are various #defines such as the above that attempt to tell  
SQLite to use a 32-bit integer where it is expecting to have a 64-bit  
integer.  But such configurations are untested.  You will likely  
encounter bugs.  I recommend that you only use SQLite on platforms  
that have a working 64-bit integer capability.

D. Richard Hipp
[EMAIL PROTECTED]



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite3 command line Up Arrow Not Working

[Mark Easton]

I see that others have had this probkem in the past:

Ie
http://www.mail-archive.com/[EMAIL PROTECTED]/msg03398.html

> -Original Message-
> From: John Stanton [mailto:[EMAIL PROTECTED] 
> Sent: Friday, October 10, 2008 11:27 AM
> To: [EMAIL PROTECTED]; General Discussion of SQLite Database
> Subject: Re: [sqlite] Sqlite3 command line Up Arrow Not Working
> 
> Looks like you do not have readline linked into sqlite3.
> 
> Mark Easton wrote:
> > Hi everyone,
> > 
> > Since I have installed the new version of sqlite3, I have 
> found that 
> > when I am in command line (sqlite3) I can no longer use up arrow to 
> > search through my command history.
> > 
> > An up arrow just produces the characters ^[[A
> > 
> > Anyone know what is going on? It is very tedious having to 
> retype long 
> > sql statements all the time!
> > 
> > Cheers
> > Mark
> > 
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite3 command line Up Arrow Not Working

[Mark Easton]

Ok, will take a look at that - but I just used the standard makefile that
came  with sqlite3 when I built it. Why would it not be linked in? My old
version of sqlite3 worked fine. Did it also use readline or is readline
something new for sqlite3?

I see that readline is standard on most GNU/Linux systems and as I am using
Ubuntu Feisty I am sure I have it.

A bit strange ...





> -Original Message-
> From: John Stanton [mailto:[EMAIL PROTECTED] 
> Sent: Friday, October 10, 2008 11:27 AM
> To: [EMAIL PROTECTED]; General Discussion of SQLite Database
> Subject: Re: [sqlite] Sqlite3 command line Up Arrow Not Working
> 
> Looks like you do not have readline linked into sqlite3.
> 
> Mark Easton wrote:
> > Hi everyone,
> > 
> > Since I have installed the new version of sqlite3, I have 
> found that 
> > when I am in command line (sqlite3) I can no longer use up arrow to 
> > search through my command history.
> > 
> > An up arrow just produces the characters ^[[A
> > 
> > Anyone know what is going on? It is very tedious having to 
> retype long 
> > sql statements all the time!
> > 
> > Cheers
> > Mark
> > 
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] assert() in sqlite3_initialize() when SQLITE_DEBUG is defined

[Dave Toll]

Hello list

 

I'd like to define SQLITE_DEBUG to help trace some problems I am seeing,
but this causes sqlite3_initialize() to assert on a 32-bit system. Does
SQLite support 32-bit systems?

 

  /* The following is just a sanity check to make sure SQLite has
  ** been compiled correctly.  It is important to run this code, but
  ** we don't want to run it too often and soak up CPU cycles for no
  ** reason.  So we run it once during initialization.
  */
#ifndef NDEBUG
  /* This section of code's only "output" is via assert() statements. */
  if ( rc==SQLITE_OK ){
u64 x = (((u64)1)<<63)-1;
double y;
assert(sizeof(x)==8);
assert(sizeof(x)==sizeof(y));
memcpy(, , 8);
assert( sqlite3IsNaN(y) );
  }
#endif

 

I noticed that similar tests elsewhere in the code check that the size
of u64 is either 8 or 4. I'm building SQLite 3.6.3 with the following
defines: -DSQLITE_INT64_TYPE=long -DSQLITE_32BIT_ROWID=1

 

Cheers,

Dave.

 

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite3 command line Up Arrow Not Working

[John Stanton]

Looks like you do not have readline linked into sqlite3.

Mark Easton wrote:
> Hi everyone,
> 
> Since I have installed the new version of sqlite3, I have found that when I
> am in command line (sqlite3) I can no longer use up arrow to search through
> my command history. 
> 
> An up arrow just produces the characters ^[[A
> 
> Anyone know what is going on? It is very tedious having to retype long sql
> statements all the time!
> 
> Cheers
> Mark
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] concurrent users?

[John Stanton]

Sqlite works well as the DBMS core of a WWW server.  It is not a 
competitor for Oracle. DB2 or PostGreSQL but can support a large number 
of users and has the advantage of being embedded.  It is not a good idea 
to use it for a large number of concurrent long queries.  Sqlite works 
best with short queries when concurrency is required.
JS

Shawn Anderson wrote:
> Hi,
> I've read many things, some conflicting, about concurrent users on a Sqlite 
> database.
> The feeling I get is that Sqlite is good for a standalone desktop database, 
> but should not be used in a concurrent user situation (like a website with 
> many users).
> Is there a definitive answer?
> thanks!
> 
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] malloc.c compile error on Fedora 9

[Robert L Cochran]

While attempting to compile sqlite 3.6.3 on a Fedora 9 system which was
freshly built (the system is based on the 20081004 Fedora Unity respin),
I got this error in malloc.c. Can anyone suggest a fix? I'm wondering if
I forgot to install a needed RPM package? I installed the "software
development" packages.



./libtool --mode=compile --tag=CC gcc -g -O2 -DSQLITE_OS_UNIX=1 -I.
-I../src -D_HAVE_SQLITE_CONFIG_H -DNDEBUG -DSQLITE_THREADSAFE=1
-DSQLITE_THREAD_OVERRIDE_LOCK=-1 -DSQLITE_OMIT_LOAD_EXTENSION=1
-DSQLITE_TEMP_STORE=1 -c ../src/malloc.c
libtool: compile: gcc -g -O2 -DSQLITE_OS_UNIX=1 -I. -I../src
-D_HAVE_SQLITE_CONFIG_H -DNDEBUG -DSQLITE_THREADSAFE=1
-DSQLITE_THREAD_OVERRIDE_LOCK=-1 -DSQLITE_OMIT_LOAD_EXTENSION=1
-DSQLITE_TEMP_STORE=1 -c ../src/malloc.c -fPIC -DPIC -o .libs/malloc.o
../src/malloc.c: In function ‘sqlite3_memory_highwater’:
../src/malloc.c:178: warning: call to ‘sqlite3_status’ declared with
attribute warning: is experimental
../src/malloc.c: In function ‘sqlite3_memory_used’:
../src/malloc.c:165: warning: call to ‘sqlite3_status’ declared with
attribute warning: is experimental
libtool: compile: gcc -g -O2 -DSQLITE_OS_UNIX=1 -I. -I../src
-D_HAVE_SQLITE_CONFIG_H -DNDEBUG -DSQLITE_THREADSAFE=1
-DSQLITE_THREAD_OVERRIDE_LOCK=-1 -DSQLITE_OMIT_LOAD_EXTENSION=1
-DSQLITE_TEMP_STORE=1 -c ../src/malloc.c -o malloc.o >/dev/null 2>&1

[Note: compile goes into a hang at the line above and is stuck there.
Then when you press CTRL-C, you get the output which appears below.]

^Cmake: *** [malloc.lo] Error 1


Thanks

Bob Cochran
Greenbelt, Maryland, USA

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Adding index to table makes SELECT much slower. Why?

[[EMAIL PROTECTED]]

  SELECT lat, lon, depth FROM hydro 
  WHERE depth>= 49.01 AND depth <=50.99;

The SELECT may be faster still if you use this index instead:

  CREATE INDEX hydro_indx2 ON hydro (depth, lat, lon);

as the query can find all the information in the index without 
hitting the pages of the main table. (At the expense of having 
a larger database file.)

The data in the hydro table need not be presorted at population
unless you need to pull out additional columns not listed in the
index.

Always try to create INDEXes after you populate the table
(where possible) in order to have contiguous table and index
pages.

If you have the inclination you could also experiment with 
various page sizes.



-- 
View this message in context: 
http://www.nabble.com/Adding-index-to-table-makes-SELECT-much-slower.-Why--tp19889143p19908299.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] howto setup SQLite with Powershell ?

[Wilson, Ron P]

I think it would be cool to use System.Data.SQLite with powershell; I'm waiting 
until you figure it out before I try it.  I don't have a compelling reason to 
try it myself yet, but the neato factor has me paying attention.  Some of the 
readers seem to think you should ask these questions elsewhere because this is 
neither the powershell list nor the System.Data.SQLite list, but please stick 
around if only to give the answer once you figure it out.

RW

Ron Wilson, S/W Systems Engineer III, Tyco Electronics, 434.455.6453

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED]
Sent: Thursday, October 09, 2008 3:58 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] howto setup SQLite with Powershell ?

> access ... dll from PowerShell ... PowerShell docs 

the procedure given there is to register the dll with installutil.
When I first ran it, it looks ok.
As I rerun it during my efforts I got an error:
No public installers with the RunInstallerAttribute.Yes attribute could be 
found in the ...\System.Data.SQLite.dll assembly.


What I'm wondering is, am I the only one who wants to use SQLite together with 
Powershell?



 Original-Nachricht 
> Datum: Thu, 9 Oct 2008 15:26:04 -0400
> Von: "Brad Stiles" <[EMAIL PROTECTED]>
> An: "General Discussion of SQLite Database" 
> Betreff: Re: [sqlite] howto setup SQLite with Powershell ?

> > The original problem is given in the topic.
> > The System.Data.SQLite provider (which I currently can't get running) is
> hopefully only one solution.
> 
> You mentioned the System.Data.SQLite provider in every message you
> posted, and presented it in such a way that I interpreted you to have
> asked "howto setup SQLite with Powershell using System.Data.SQLite".
> That's what I was responding to.
> 
> > SQLite provides sqlite3.exe and sqlite3.dll.
> 
> However you'd access any other non-.NET, non-COM, dll from PowerShell
> is probably going to be the way you access this one.  Are the
> PowerShell docs not any help with this?
> 
> > How can I use that with powershell?
> 
> Not a clue, other than to execute the sqlite3.exe program with the
> appropriate command line parameters to accomplish what I want.
> 
> Brad
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

-- 
Pt! Schon vom neuen GMX MultiMessenger gehört? Der kann`s mit allen: 
http://www.gmx.net/de/go/multimessenger
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] howto setup SQLite with Powershell ?

[HLachmann]

> access ... dll from PowerShell ... PowerShell docs 

the procedure given there is to register the dll with installutil.
When I first ran it, it looks ok.
As I rerun it during my efforts I got an error:
No public installers with the RunInstallerAttribute.Yes attribute could be 
found in the ...\System.Data.SQLite.dll assembly.


What I'm wondering is, am I the only one who wants to use SQLite together with 
Powershell?



 Original-Nachricht 
> Datum: Thu, 9 Oct 2008 15:26:04 -0400
> Von: "Brad Stiles" <[EMAIL PROTECTED]>
> An: "General Discussion of SQLite Database" 
> Betreff: Re: [sqlite] howto setup SQLite with Powershell ?

> > The original problem is given in the topic.
> > The System.Data.SQLite provider (which I currently can't get running) is
> hopefully only one solution.
> 
> You mentioned the System.Data.SQLite provider in every message you
> posted, and presented it in such a way that I interpreted you to have
> asked "howto setup SQLite with Powershell using System.Data.SQLite".
> That's what I was responding to.
> 
> > SQLite provides sqlite3.exe and sqlite3.dll.
> 
> However you'd access any other non-.NET, non-COM, dll from PowerShell
> is probably going to be the way you access this one.  Are the
> PowerShell docs not any help with this?
> 
> > How can I use that with powershell?
> 
> Not a clue, other than to execute the sqlite3.exe program with the
> appropriate command line parameters to accomplish what I want.
> 
> Brad
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

-- 
Pt! Schon vom neuen GMX MultiMessenger gehört? Der kann`s mit allen: 
http://www.gmx.net/de/go/multimessenger
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] howto setup SQLite with Powershell ?

[Brad Stiles]

> The original problem is given in the topic.
> The System.Data.SQLite provider (which I currently can't get running) is 
> hopefully only one solution.

You mentioned the System.Data.SQLite provider in every message you
posted, and presented it in such a way that I interpreted you to have
asked "howto setup SQLite with Powershell using System.Data.SQLite".
That's what I was responding to.

> SQLite provides sqlite3.exe and sqlite3.dll.

However you'd access any other non-.NET, non-COM, dll from PowerShell
is probably going to be the way you access this one.  Are the
PowerShell docs not any help with this?

> How can I use that with powershell?

Not a clue, other than to execute the sqlite3.exe program with the
appropriate command line parameters to accomplish what I want.

Brad
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] howto setup SQLite with Powershell ?

[HLachmann]

I have no idea of unix shell scripts. Is there a startup guide?
But it's hard to imagine to use a cmdline-tool in an object-orientated manner.

@Jeffrey:
which steps have you performed before your LoadWithPartialName succeeds?


 Original-Nachricht 
> Datum: Thu, 9 Oct 2008 15:06:41 -0400
> Von: "Jeffrey Becker" <[EMAIL PROTECTED]>
> An: "General Discussion of SQLite Database" 
> Betreff: Re: [sqlite] howto setup SQLite with Powershell ?

> Technically there's nothing stopping you from using sqlite3.exe in the
> same way you'd use it in a unix shell script.
> 
> On Thu, Oct 9, 2008 at 2:54 PM, Helga Lachmann <[EMAIL PROTECTED]> wrote:
> > The original problem is given in the topic.
> > The System.Data.SQLite provider (which I currently can't get running) is
> hopefully only one solution.
> >
> > Do you have an answer to the original question?
> > SQLite provides sqlite3.exe and sqlite3.dll.
> > How can I use that with powershell?
> >
> >
> >  Original-Nachricht 
> >> Datum: Thu, 9 Oct 2008 14:40:47 -0400
> >> Von: "Brad Stiles" <[EMAIL PROTECTED]>
> >> An: "General Discussion of SQLite Database" 
> >> Betreff: Re: [sqlite] howto setup SQLite with Powershell ?
> >
> >> > btw: if I rerun InstallUtil I now get a message:
> >> > No public installers with the RunInstallerAttribute.Yes attribute
> could
> >> be found in the ...\System.Data.SQLite.dll assembly.
> >>
> >> You should really be asking these questions on the forum/list
> >> dedicated to the support of the product you are working with, i.e. the
> >> producer of the System.Data.SQLite provider.
> >>
> >> You will likely need to either install the assembly to the GAC
> >> yourself, or use some other method to tell PowerShell where the
> >> assembly is located.
> >> ___
> >> sqlite-users mailing list
> >> sqlite-users@sqlite.org
> >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
> > --
> > GMX startet ShortView.de. Hier findest Du Leute mit Deinen Interessen!
> > Jetzt dabei sein:
> http://www.shortview.de/[EMAIL PROTECTED]
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

-- 
Ist Ihr Browser Vista-kompatibel? Jetzt die neuesten 
Browser-Versionen downloaden: http://www.gmx.net/de/go/browser
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] howto setup SQLite with Powershell ?

[Jeffrey Becker]

Technically there's nothing stopping you from using sqlite3.exe in the
same way you'd use it in a unix shell script.

On Thu, Oct 9, 2008 at 2:54 PM, Helga Lachmann <[EMAIL PROTECTED]> wrote:
> The original problem is given in the topic.
> The System.Data.SQLite provider (which I currently can't get running) is 
> hopefully only one solution.
>
> Do you have an answer to the original question?
> SQLite provides sqlite3.exe and sqlite3.dll.
> How can I use that with powershell?
>
>
>  Original-Nachricht 
>> Datum: Thu, 9 Oct 2008 14:40:47 -0400
>> Von: "Brad Stiles" <[EMAIL PROTECTED]>
>> An: "General Discussion of SQLite Database" 
>> Betreff: Re: [sqlite] howto setup SQLite with Powershell ?
>
>> > btw: if I rerun InstallUtil I now get a message:
>> > No public installers with the RunInstallerAttribute.Yes attribute could
>> be found in the ...\System.Data.SQLite.dll assembly.
>>
>> You should really be asking these questions on the forum/list
>> dedicated to the support of the product you are working with, i.e. the
>> producer of the System.Data.SQLite provider.
>>
>> You will likely need to either install the assembly to the GAC
>> yourself, or use some other method to tell PowerShell where the
>> assembly is located.
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
> --
> GMX startet ShortView.de. Hier findest Du Leute mit Deinen Interessen!
> Jetzt dabei sein: http://www.shortview.de/[EMAIL PROTECTED]
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] howto setup SQLite with Powershell ?

[Helga Lachmann]

The original problem is given in the topic.
The System.Data.SQLite provider (which I currently can't get running) is 
hopefully only one solution.

Do you have an answer to the original question?
SQLite provides sqlite3.exe and sqlite3.dll.
How can I use that with powershell?


 Original-Nachricht 
> Datum: Thu, 9 Oct 2008 14:40:47 -0400
> Von: "Brad Stiles" <[EMAIL PROTECTED]>
> An: "General Discussion of SQLite Database" 
> Betreff: Re: [sqlite] howto setup SQLite with Powershell ?

> > btw: if I rerun InstallUtil I now get a message:
> > No public installers with the RunInstallerAttribute.Yes attribute could
> be found in the ...\System.Data.SQLite.dll assembly.
> 
> You should really be asking these questions on the forum/list
> dedicated to the support of the product you are working with, i.e. the
> producer of the System.Data.SQLite provider.
> 
> You will likely need to either install the assembly to the GAC
> yourself, or use some other method to tell PowerShell where the
> assembly is located.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

-- 
GMX startet ShortView.de. Hier findest Du Leute mit Deinen Interessen!
Jetzt dabei sein: http://www.shortview.de/[EMAIL PROTECTED]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] howto setup SQLite with Powershell ?

[Brad Stiles]

> btw: if I rerun InstallUtil I now get a message:
> No public installers with the RunInstallerAttribute.Yes attribute could be 
> found in the ...\System.Data.SQLite.dll assembly.

You should really be asking these questions on the forum/list
dedicated to the support of the product you are working with, i.e. the
producer of the System.Data.SQLite provider.

You will likely need to either install the assembly to the GAC
yourself, or use some other method to tell PowerShell where the
assembly is located.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] howto setup SQLite with Powershell ?

[HLachmann]

If I run it I get...
nothing

So I would like to know which steps you have done before.

btw: if I rerun InstallUtil I now get a message:
No public installers with the RunInstallerAttribute.Yes attribute could be 
found in the ...\System.Data.SQLite.dll assembly.



 Original-Nachricht 
> Datum: Thu, 9 Oct 2008 12:38:14 -0400
> Von: "Jeffrey Becker" <[EMAIL PROTECTED]>
> An: "General Discussion of SQLite Database" 
> Betreff: Re: [sqlite] howto setup SQLite with Powershell ?

> When I run the LoadWithPartialName I get:
> 
> GACVersionLocation
> ------
> True   v2.0.50727
> C:\WINDOWS\assembly\GAC_32\System.Data.SQLite\1.0.60.0__db937bc2d44ff139\System.Data.SQLite.dll
> 
> printed on my console.  Does this happen for you?  If not that would
> lead me to believe that the System.Data.SQLite.dll file isnt properly
> installed in the GAC.
> 
> On Thu, Oct 9, 2008 at 11:25 AM,  <[EMAIL PROTECTED]> wrote:
> > if that is "bad" how can it be done correct/better?
> >
> >  [System.Reflection.Assembly]::LoadWithPartialName("System.Data.SQLite")
> > itself produces no error, but also
> >  [System.Reflection.Assembly]::LoadWithPartialName("thisDoesntMatter")
> > produces no error,
> > but the following
> >  $conn = New-Object System.Data.SQLite.SQLiteConnection($Conn)
> > gives the error
> >  Type [System.Data.SQLite.SQLiteConnection] cannot be found.
> >  Make sure assembly containing this type is loaded.
> >
> > [messages retranslated from german]
> >
> >
> >  Original-Nachricht 
> >> Datum: Thu, 9 Oct 2008 11:04:36 -0400
> >> Von: "Jeffrey Becker" <[EMAIL PROTECTED]>
> >> An: "General Discussion of SQLite Database" 
> >> Betreff: Re: [sqlite] howto setup SQLite with Powershell ?
> >
> >> Oh I see.  System.Data.SQLite doesn't provide a Snap-in so
> >> Add-PSSnapin wont work.  You could use:
> >> [System.Reflection.Assembly]::LoadWithPartialName("System.Data.SQLite")
> >> But from what I see this method is considered 'bad' because this could
> >> lead to versioning issues.  Since the System.Data.Common interfaces
> >> are very stable I'd personally feel safe doing this but ymmv.
> >>
> >> On Thu, Oct 9, 2008 at 10:15 AM, Helga Lachmann <[EMAIL PROTECTED]>
> wrote:
> >> > I don't know.
> >> > How should I do this?
> >> > I think that's the point.
> >> >
> >> > As I mentioned I tried manually with Add-PSSnapin, but that doesn't
> >> work.
> >> > (... PowerShell-Snap-In "System.Data.SQLite" is not installed ...)
> >> >
> >> >
> >> >
> >> >  Original-Nachricht 
> >> >> Datum: Thu, 9 Oct 2008 10:03:07 -0400
> >> >> Von: "Jeffrey Becker" <[EMAIL PROTECTED]>
> >> >> An: "General Discussion of SQLite Database"
> 
> >> >> Betreff: Re: [sqlite] howto setup SQLite with Powershell ?
> >> >
> >> >> IIRC powershell doesnt automatically load every assembly in the GAC.
> >> >> How are you loading the System.Data.SQLite assembly?
> >> >>
> >> >> On Thu, Oct 9, 2008 at 8:51 AM,  <[EMAIL PROTECTED]> wrote:
> >> >> > Hello,
> >> >> >
> >> >> > I looked unavailingly around for hours to get SQLite and
> Powershell
> >> >> working together.
> >> >> >
> >> >> > Is there any little installation/configuration guide for this?
> >> >> >
> >> >> > The problem seems to be how to get SQLite available to PS.
> >> >> >
> >> >> >
> >> >> > I used installutil, which reported "successful".
> >> >> > AddPSSnapin failed with error "...DLL not installed"
> >> >> >
> >> >> > I tried to connect via Database Connection and even with the
> >> >> DBProviderFactory method.
> >> >> > But Powershell always reports an error ("Unable to find the
> requested
> >> >> .Net Framework Data Provider.  It may not be installed.")
> >> >> >
> >> >> > I installed SQLite for ADO.NET 2.0 from sourceforge.
> >> >> > The result seems ok (finished successfully with no errors,
> SQLite.NET
> >> >> program-folder)
> >> >> > The documentation tells also about using DbProviderFactories.
> >> >> > It says to modify the app.config file. This seems to be a DotNet
> >> >> scenario for VisualStudio but doesn't work for PS.
> >> >> >
> >> >> > In my desperation I copied the SQLite-DLL around in various
> >> locations,
> >> >> added manually a line to the machine.config file, but nothing
> worked.
> >> >> >
> >> >> >
> >> >> > I'm using XPPro, SP3.
> >> >> >
> >> >> >
> >> >> > thanks a lot
> >> >> >
> >> >> > H.L.
> >> >> >
> >> >> > --
> >> >> > GMX startet ShortView.de. Hier findest Du Leute mit Deinen
> >> Interessen!
> >> >> > Jetzt dabei sein:
> >> >> http://www.shortview.de/[EMAIL PROTECTED]
> >> >> > ___
> >> >> > sqlite-users mailing list
> >> >> > sqlite-users@sqlite.org
> >> >> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >> >> >
> >> >> ___
> >> >> sqlite-users mailing list
> >> >> sqlite-users@sqlite.org
> >> >> 

Re: [sqlite] howto setup SQLite with Powershell ?

[Jeffrey Becker]

As for the code being 'bad' I saw a lot of blog posts that said it
caused problems but very few that had reasonable replacements.  There
was one post that gave a hundred lines or so of code to do the same
thing.

On Thu, Oct 9, 2008 at 11:25 AM,  <[EMAIL PROTECTED]> wrote:
> if that is "bad" how can it be done correct/better?
>
>  [System.Reflection.Assembly]::LoadWithPartialName("System.Data.SQLite")
> itself produces no error, but also
>  [System.Reflection.Assembly]::LoadWithPartialName("thisDoesntMatter")
> produces no error,
> but the following
>  $conn = New-Object System.Data.SQLite.SQLiteConnection($Conn)
> gives the error
>  Type [System.Data.SQLite.SQLiteConnection] cannot be found.
>  Make sure assembly containing this type is loaded.
>
> [messages retranslated from german]
>
>
>  Original-Nachricht 
>> Datum: Thu, 9 Oct 2008 11:04:36 -0400
>> Von: "Jeffrey Becker" <[EMAIL PROTECTED]>
>> An: "General Discussion of SQLite Database" 
>> Betreff: Re: [sqlite] howto setup SQLite with Powershell ?
>
>> Oh I see.  System.Data.SQLite doesn't provide a Snap-in so
>> Add-PSSnapin wont work.  You could use:
>> [System.Reflection.Assembly]::LoadWithPartialName("System.Data.SQLite")
>> But from what I see this method is considered 'bad' because this could
>> lead to versioning issues.  Since the System.Data.Common interfaces
>> are very stable I'd personally feel safe doing this but ymmv.
>>
>> On Thu, Oct 9, 2008 at 10:15 AM, Helga Lachmann <[EMAIL PROTECTED]> wrote:
>> > I don't know.
>> > How should I do this?
>> > I think that's the point.
>> >
>> > As I mentioned I tried manually with Add-PSSnapin, but that doesn't
>> work.
>> > (... PowerShell-Snap-In "System.Data.SQLite" is not installed ...)
>> >
>> >
>> >
>> >  Original-Nachricht 
>> >> Datum: Thu, 9 Oct 2008 10:03:07 -0400
>> >> Von: "Jeffrey Becker" <[EMAIL PROTECTED]>
>> >> An: "General Discussion of SQLite Database" 
>> >> Betreff: Re: [sqlite] howto setup SQLite with Powershell ?
>> >
>> >> IIRC powershell doesnt automatically load every assembly in the GAC.
>> >> How are you loading the System.Data.SQLite assembly?
>> >>
>> >> On Thu, Oct 9, 2008 at 8:51 AM,  <[EMAIL PROTECTED]> wrote:
>> >> > Hello,
>> >> >
>> >> > I looked unavailingly around for hours to get SQLite and Powershell
>> >> working together.
>> >> >
>> >> > Is there any little installation/configuration guide for this?
>> >> >
>> >> > The problem seems to be how to get SQLite available to PS.
>> >> >
>> >> >
>> >> > I used installutil, which reported "successful".
>> >> > AddPSSnapin failed with error "...DLL not installed"
>> >> >
>> >> > I tried to connect via Database Connection and even with the
>> >> DBProviderFactory method.
>> >> > But Powershell always reports an error ("Unable to find the requested
>> >> .Net Framework Data Provider.  It may not be installed.")
>> >> >
>> >> > I installed SQLite for ADO.NET 2.0 from sourceforge.
>> >> > The result seems ok (finished successfully with no errors, SQLite.NET
>> >> program-folder)
>> >> > The documentation tells also about using DbProviderFactories.
>> >> > It says to modify the app.config file. This seems to be a DotNet
>> >> scenario for VisualStudio but doesn't work for PS.
>> >> >
>> >> > In my desperation I copied the SQLite-DLL around in various
>> locations,
>> >> added manually a line to the machine.config file, but nothing worked.
>> >> >
>> >> >
>> >> > I'm using XPPro, SP3.
>> >> >
>> >> >
>> >> > thanks a lot
>> >> >
>> >> > H.L.
>> >> >
>> >> > --
>> >> > GMX startet ShortView.de. Hier findest Du Leute mit Deinen
>> Interessen!
>> >> > Jetzt dabei sein:
>> >> http://www.shortview.de/[EMAIL PROTECTED]
>> >> > ___
>> >> > sqlite-users mailing list
>> >> > sqlite-users@sqlite.org
>> >> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>> >> >
>> >> ___
>> >> sqlite-users mailing list
>> >> sqlite-users@sqlite.org
>> >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>> >
>> > --
>> > Pt! Schon vom neuen GMX MultiMessenger gehört? Der kann`s mit
>> allen: http://www.gmx.net/de/go/multimessenger
>> > ___
>> > sqlite-users mailing list
>> > sqlite-users@sqlite.org
>> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>> >
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
> --
> GMX startet ShortView.de. Hier findest Du Leute mit Deinen Interessen!
> Jetzt dabei sein: http://www.shortview.de/[EMAIL PROTECTED]
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing 

Re: [sqlite] howto setup SQLite with Powershell ?

[Jeffrey Becker]

When I run the LoadWithPartialName I get:

GACVersionLocation
------
True   v2.0.50727
C:\WINDOWS\assembly\GAC_32\System.Data.SQLite\1.0.60.0__db937bc2d44ff139\System.Data.SQLite.dll

printed on my console.  Does this happen for you?  If not that would
lead me to believe that the System.Data.SQLite.dll file isnt properly
installed in the GAC.

On Thu, Oct 9, 2008 at 11:25 AM,  <[EMAIL PROTECTED]> wrote:
> if that is "bad" how can it be done correct/better?
>
>  [System.Reflection.Assembly]::LoadWithPartialName("System.Data.SQLite")
> itself produces no error, but also
>  [System.Reflection.Assembly]::LoadWithPartialName("thisDoesntMatter")
> produces no error,
> but the following
>  $conn = New-Object System.Data.SQLite.SQLiteConnection($Conn)
> gives the error
>  Type [System.Data.SQLite.SQLiteConnection] cannot be found.
>  Make sure assembly containing this type is loaded.
>
> [messages retranslated from german]
>
>
>  Original-Nachricht 
>> Datum: Thu, 9 Oct 2008 11:04:36 -0400
>> Von: "Jeffrey Becker" <[EMAIL PROTECTED]>
>> An: "General Discussion of SQLite Database" 
>> Betreff: Re: [sqlite] howto setup SQLite with Powershell ?
>
>> Oh I see.  System.Data.SQLite doesn't provide a Snap-in so
>> Add-PSSnapin wont work.  You could use:
>> [System.Reflection.Assembly]::LoadWithPartialName("System.Data.SQLite")
>> But from what I see this method is considered 'bad' because this could
>> lead to versioning issues.  Since the System.Data.Common interfaces
>> are very stable I'd personally feel safe doing this but ymmv.
>>
>> On Thu, Oct 9, 2008 at 10:15 AM, Helga Lachmann <[EMAIL PROTECTED]> wrote:
>> > I don't know.
>> > How should I do this?
>> > I think that's the point.
>> >
>> > As I mentioned I tried manually with Add-PSSnapin, but that doesn't
>> work.
>> > (... PowerShell-Snap-In "System.Data.SQLite" is not installed ...)
>> >
>> >
>> >
>> >  Original-Nachricht 
>> >> Datum: Thu, 9 Oct 2008 10:03:07 -0400
>> >> Von: "Jeffrey Becker" <[EMAIL PROTECTED]>
>> >> An: "General Discussion of SQLite Database" 
>> >> Betreff: Re: [sqlite] howto setup SQLite with Powershell ?
>> >
>> >> IIRC powershell doesnt automatically load every assembly in the GAC.
>> >> How are you loading the System.Data.SQLite assembly?
>> >>
>> >> On Thu, Oct 9, 2008 at 8:51 AM,  <[EMAIL PROTECTED]> wrote:
>> >> > Hello,
>> >> >
>> >> > I looked unavailingly around for hours to get SQLite and Powershell
>> >> working together.
>> >> >
>> >> > Is there any little installation/configuration guide for this?
>> >> >
>> >> > The problem seems to be how to get SQLite available to PS.
>> >> >
>> >> >
>> >> > I used installutil, which reported "successful".
>> >> > AddPSSnapin failed with error "...DLL not installed"
>> >> >
>> >> > I tried to connect via Database Connection and even with the
>> >> DBProviderFactory method.
>> >> > But Powershell always reports an error ("Unable to find the requested
>> >> .Net Framework Data Provider.  It may not be installed.")
>> >> >
>> >> > I installed SQLite for ADO.NET 2.0 from sourceforge.
>> >> > The result seems ok (finished successfully with no errors, SQLite.NET
>> >> program-folder)
>> >> > The documentation tells also about using DbProviderFactories.
>> >> > It says to modify the app.config file. This seems to be a DotNet
>> >> scenario for VisualStudio but doesn't work for PS.
>> >> >
>> >> > In my desperation I copied the SQLite-DLL around in various
>> locations,
>> >> added manually a line to the machine.config file, but nothing worked.
>> >> >
>> >> >
>> >> > I'm using XPPro, SP3.
>> >> >
>> >> >
>> >> > thanks a lot
>> >> >
>> >> > H.L.
>> >> >
>> >> > --
>> >> > GMX startet ShortView.de. Hier findest Du Leute mit Deinen
>> Interessen!
>> >> > Jetzt dabei sein:
>> >> http://www.shortview.de/[EMAIL PROTECTED]
>> >> > ___
>> >> > sqlite-users mailing list
>> >> > sqlite-users@sqlite.org
>> >> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>> >> >
>> >> ___
>> >> sqlite-users mailing list
>> >> sqlite-users@sqlite.org
>> >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>> >
>> > --
>> > Pt! Schon vom neuen GMX MultiMessenger gehört? Der kann`s mit
>> allen: http://www.gmx.net/de/go/multimessenger
>> > ___
>> > sqlite-users mailing list
>> > sqlite-users@sqlite.org
>> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>> >
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
> --
> GMX startet ShortView.de. Hier findest Du Leute mit Deinen Interessen!
> Jetzt dabei sein: http://www.shortview.de/[EMAIL PROTECTED]
> ___
> sqlite-users 

[sqlite] ATTACH problem

[rrrado2]

Ok it seems last time I've posted too many errors so I'll try to ask about this 
one. Maybe other are just consequences of this. I've finally received this 
error also on my computer, but only in release build.

- windows XP, sqlite 3.6.3, VS 2005
- sqlite3_threadsafe() -> 1
- 20 worker threads, each opens db, attaches another db, does some writes (into 
first db only), closes, thread exit, new thread start

After somer time the "ATTACH" fails with error code SQLITE_MISUSE and error 
message is that database is locked.
Why does it return MISUSE and not LOCKED or busy?

Any ideas what can be wrong? Is this bug in SQLite or compiler or am I missing 
something?

Thank you.



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Adding index to table makes SELECT much slower. Why?

[James Pringle]

Hi all-

   Thanks for your many suggestions.  I have tried many of your suggestions,
and found the following:


   1. If I create the database with depth ordered from least to greatest,
   and then create an index, the searches run an order of magnitude faster than
   without an index.  I.e. the search for matching entries in the data base
   concludes in about 9 seconds, instead of the 90 seconds it had before.
   Thanks to "developir" for this suggestion!
   2. searching via "rowid" makes no difference.
   3. doing vacuum makes no difference, since this is a write once, read
   many database.
   4. ulimit on my machine is unlimited, so this was not important for me.
   5. Cache size seems to make no difference -- and the SQLite documentation
   says it should only be important for DELETE's and UPDATE's.
   6. I like the idea of using RTREE, however, I don't want each of the
   students who use this database to have to recompile the version of SQLite
   that comes with their version of python.

Several of you, in particular Ken, suggested that I run SQL queries that
would provide some output that would be helpful in figuring out what was
going on.The database is now more than fast enough for me, so I would
not do this on my own.  However, if others on the mailing list would like to
see this output in order to make SQLite better (or just for their own
curiosity), please feel free to let me know, and I can make the runs.

I have a small subset of the data (450Mb) which exhibits the same
behaviour.  The data is public (it is from the National Ocean Database), and
so if anyone wants to see it I would be happy to put it on my web server.

Cheers,
and thanks to everyone who helped me!
Jamie Pringle

On Wed, Oct 8, 2008 at 6:50 PM, James Pringle <[EMAIL PROTECTED]> wrote:
> Hi-
>
>  I am new to sqlite, and am having a puzzling problem.  I have read
> that adding an INDEX to a sqlite table can never make a SELECT
> statement slower.  However, in my case, it seems to be making the
> select statement an order of magnitude slower, which is not what I
> wanted!  What could be going on?
>
>  I am calling SQLite from python 2.5 from fink useing the sqlite3
> module on OS X 10.5 with 2Gb of memory.  My data base contains 32
> million lines of ocean hydrographic data, stored on disk, with the
> table created with the following statement:
>
>CREATE TABLE hydro (lat REAL, lon REAL, year INTEGER,
>month INTEGER, day INTEGER, time REAL, cast_id INTEGER,
>depth REAL, T REAL, S REAL, water_depth REAL)
>
> When I perform a SELECT that returns about 0.6% of the data in the
> database (about 200,000 records)
>
>SELECT lat, lon, depth FROM hydro WHERE depth>= 49.01 AND depth <=
50.99
>
> It selects and returns the records in 82 seconds.  I wished to improve
> on this time, so I created an index with the following command:
>
>CREATE INDEX hydro_indx ON hydro (depth)
>
> I then tried the SELECT command again, and read the results into
> memory, and it took 717 seconds!?!
>
> The "depth" field contains many distinct numeric values, with no
> specific value matching more then 0.5% of the data base.  When I DROP
> the index with the command
>
>DROP INDEX hydro_indx
>
> The SELECT time returns to about 80 seconds, confirming that it is the
> index which is slowing things down.  What is going on?  I have
> repeated and confirmed these timings.
>
> I have listened for disk chatter and monitored the system, and it does
> not seem to be thrashing swap, or otherwise becoming unresponsive.
>
> I have two questions:
>
>1) Why is the index making things slower?
>2) How can I make my SELECT statement faster?  The primary
> selection will be done
>   on the "depth" and "water_depth" keys.
>
> I thank you for your time.
>
> Cheers,
> Jamie Pringle
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Why is the VDBE testing for NULL?

[D. Richard Hipp]

On Oct 9, 2008, at 11:46 AM, Ken wrote:

> DRH,
> I noticed that the VDBE is doing a check for null Can this be removed?
>
> test script to show output:
> create table t (id integer not null, id2 integer);
> create index  t_idx on t (id);
> explain select id from t where id = 5;
>
>
> 0|Trace|0|0|0|explain select id from t where id = 5;|00|
> 1|Integer|5|1|0||00|
> 2|Goto|0|15|0||00|
> 3|SetNumColumns|0|2|0||00|
> 4|OpenRead|1|6|0|keyinfo(1,BINARY)|00|
> 5|SCopy|1|3|0||00|
> 6|IsNull|3|13|0||00|<---  
> IS this needed ???
> 7|Affinity|3|1|0|db|00|
> 8|MoveGe|1|13|3|1|00|
> 9|IdxGE|1|13|3|1|01|
> 10|Column|1|0|8||00|
> 11|ResultRow|8|1|0||00|
> 12|Next|1|9|0||00|
> 13|Close|1|0|0||00|
> 14|Halt|0|0|0||00|
> 15|Transaction|0|0|0||00|
> 16|VerifyCookie|0|7|0||00|
> 17|TableLock|0|5|0|t|00|
> 18|Goto|0|3|0||00|
>

It is needed when the query is something like:

 SELECT id FROM t WHERE id = ?

We could add lots of new logic to the code generator to try and  
suppress the OP_IsNull in cases where the non-nullness of the value  
could be determined at compile-time.  But OP_IsNull takes no  
measurable amount of time, so why clutter up the code base to do so?

You will get much better output from EXPLAIN if you first run the  
shell macro ".explain"

D. Richard Hipp
[EMAIL PROTECTED]



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Why is the VDBE testing for NULL?

[Ken]

DRH, 
I noticed that the VDBE is doing a check for null Can this be removed?

test script to show output:
create table t (id integer not null, id2 integer);
create index  t_idx on t (id);
explain select id from t where id = 5;


0|Trace|0|0|0|explain select id from t where id = 5;|00|
1|Integer|5|1|0||00|
2|Goto|0|15|0||00|
3|SetNumColumns|0|2|0||00|
4|OpenRead|1|6|0|keyinfo(1,BINARY)|00|
5|SCopy|1|3|0||00|
6|IsNull|3|13|0||00|    <--- IS this 
needed ???
7|Affinity|3|1|0|db|00|
8|MoveGe|1|13|3|1|00|
9|IdxGE|1|13|3|1|01|
10|Column|1|0|8||00|
11|ResultRow|8|1|0||00|
12|Next|1|9|0||00|
13|Close|1|0|0||00|
14|Halt|0|0|0||00|
15|Transaction|0|0|0||00|
16|VerifyCookie|0|7|0||00|
17|TableLock|0|5|0|t|00|
18|Goto|0|3|0||00|

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Adding index to table makes SELECT much slower. Why?

[Ken]

Id suggest running a 

 explain query plan  SELECT lat, lon, depth FROM hydro WHERE depth>= 49.01 AND 
depth  <= 50.99 ;

Both with and without the index. That will give you an idea of what is going on.

Maybe watch the IO statistics while running with and without the index as well 
and see if it doing significantly more I/O... Check the memory/paging/swapping 
as well.

Do you have the temporary area set to memory or disk? 

Can you also try running this directly from the sqlite3 command instead of 
python.
HTH

--- On Thu, 10/9/08, Daniel Önnerby <[EMAIL PROTECTED]> wrote:
From: Daniel Önnerby <[EMAIL PROTECTED]>
Subject: Re: [sqlite] Adding index to table makes SELECT much slower. Why?
To: "General Discussion of SQLite Database" 
Date: Thursday, October 9, 2008, 6:18 AM

Have you tried running VACUUM and ANAYZE on the indexed table?
Not that I think it will matter, but who knows :)

Daniel


James Pringle wrote:
> Hi-
>
>   I am new to sqlite, and am having a puzzling problem.  I have read
> that adding an INDEX to a sqlite table can never make a SELECT
> statement slower.  However, in my case, it seems to be making the
> select statement an order of magnitude slower, which is not what I
> wanted!  What could be going on?
>
>   I am calling SQLite from python 2.5 from fink useing the sqlite3
> module on OS X 10.5 with 2Gb of memory.  My data base contains 32
> million lines of ocean hydrographic data, stored on disk, with the
> table created with the following statement:
>
> CREATE TABLE hydro (lat REAL, lon REAL, year INTEGER,
> month INTEGER, day INTEGER, time REAL, cast_id INTEGER,
> depth REAL, T REAL, S REAL, water_depth REAL)
>
> When I perform a SELECT that returns about 0.6% of the data in the
> database (about 200,000 records)
>
> SELECT lat, lon, depth FROM hydro WHERE depth>= 49.01 AND depth
<= 50.99
>
> It selects and returns the records in 82 seconds.  I wished to improve
> on this time, so I created an index with the following command:
>
> CREATE INDEX hydro_indx ON hydro (depth)
>
> I then tried the SELECT command again, and read the results into
> memory, and it took 717 seconds!?!
>
> The "depth" field contains many distinct numeric values, with no
> specific value matching more then 0.5% of the data base.  When I DROP
> the index with the command
>
> DROP INDEX hydro_indx
>
> The SELECT time returns to about 80 seconds, confirming that it is the
> index which is slowing things down.  What is going on?  I have
> repeated and confirmed these timings.
>
> I have listened for disk chatter and monitored the system, and it does
> not seem to be thrashing swap, or otherwise becoming unresponsive.
>
> I have two questions:
>
> 1) Why is the index making things slower?
> 2) How can I make my SELECT statement faster?  The primary
> selection will be done
>on the "depth" and "water_depth" keys.
>
> I thank you for your time.
>
> Cheers,
> Jamie Pringle
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>   
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] howto setup SQLite with Powershell ?

[HLachmann]

if that is "bad" how can it be done correct/better?

  [System.Reflection.Assembly]::LoadWithPartialName("System.Data.SQLite")
itself produces no error, but also
  [System.Reflection.Assembly]::LoadWithPartialName("thisDoesntMatter")
produces no error,
but the following
  $conn = New-Object System.Data.SQLite.SQLiteConnection($Conn)
gives the error
  Type [System.Data.SQLite.SQLiteConnection] cannot be found.
  Make sure assembly containing this type is loaded.

[messages retranslated from german]


 Original-Nachricht 
> Datum: Thu, 9 Oct 2008 11:04:36 -0400
> Von: "Jeffrey Becker" <[EMAIL PROTECTED]>
> An: "General Discussion of SQLite Database" 
> Betreff: Re: [sqlite] howto setup SQLite with Powershell ?

> Oh I see.  System.Data.SQLite doesn't provide a Snap-in so
> Add-PSSnapin wont work.  You could use:
> [System.Reflection.Assembly]::LoadWithPartialName("System.Data.SQLite")
> But from what I see this method is considered 'bad' because this could
> lead to versioning issues.  Since the System.Data.Common interfaces
> are very stable I'd personally feel safe doing this but ymmv.
> 
> On Thu, Oct 9, 2008 at 10:15 AM, Helga Lachmann <[EMAIL PROTECTED]> wrote:
> > I don't know.
> > How should I do this?
> > I think that's the point.
> >
> > As I mentioned I tried manually with Add-PSSnapin, but that doesn't
> work.
> > (... PowerShell-Snap-In "System.Data.SQLite" is not installed ...)
> >
> >
> >
> >  Original-Nachricht 
> >> Datum: Thu, 9 Oct 2008 10:03:07 -0400
> >> Von: "Jeffrey Becker" <[EMAIL PROTECTED]>
> >> An: "General Discussion of SQLite Database" 
> >> Betreff: Re: [sqlite] howto setup SQLite with Powershell ?
> >
> >> IIRC powershell doesnt automatically load every assembly in the GAC.
> >> How are you loading the System.Data.SQLite assembly?
> >>
> >> On Thu, Oct 9, 2008 at 8:51 AM,  <[EMAIL PROTECTED]> wrote:
> >> > Hello,
> >> >
> >> > I looked unavailingly around for hours to get SQLite and Powershell
> >> working together.
> >> >
> >> > Is there any little installation/configuration guide for this?
> >> >
> >> > The problem seems to be how to get SQLite available to PS.
> >> >
> >> >
> >> > I used installutil, which reported "successful".
> >> > AddPSSnapin failed with error "...DLL not installed"
> >> >
> >> > I tried to connect via Database Connection and even with the
> >> DBProviderFactory method.
> >> > But Powershell always reports an error ("Unable to find the requested
> >> .Net Framework Data Provider.  It may not be installed.")
> >> >
> >> > I installed SQLite for ADO.NET 2.0 from sourceforge.
> >> > The result seems ok (finished successfully with no errors, SQLite.NET
> >> program-folder)
> >> > The documentation tells also about using DbProviderFactories.
> >> > It says to modify the app.config file. This seems to be a DotNet
> >> scenario for VisualStudio but doesn't work for PS.
> >> >
> >> > In my desperation I copied the SQLite-DLL around in various
> locations,
> >> added manually a line to the machine.config file, but nothing worked.
> >> >
> >> >
> >> > I'm using XPPro, SP3.
> >> >
> >> >
> >> > thanks a lot
> >> >
> >> > H.L.
> >> >
> >> > --
> >> > GMX startet ShortView.de. Hier findest Du Leute mit Deinen
> Interessen!
> >> > Jetzt dabei sein:
> >> http://www.shortview.de/[EMAIL PROTECTED]
> >> > ___
> >> > sqlite-users mailing list
> >> > sqlite-users@sqlite.org
> >> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >> >
> >> ___
> >> sqlite-users mailing list
> >> sqlite-users@sqlite.org
> >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
> > --
> > Pt! Schon vom neuen GMX MultiMessenger gehört? Der kann`s mit
> allen: http://www.gmx.net/de/go/multimessenger
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

-- 
GMX startet ShortView.de. Hier findest Du Leute mit Deinen Interessen!
Jetzt dabei sein: http://www.shortview.de/[EMAIL PROTECTED]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sqlite3_open() problem for ARM

[Leandro Ribeiro]

Hello Guys.

I have a problem with running SQLite.
I am running linux 2.6.17 on *ARM* and basically problem is that my 
application
is crushing on *sqlite3_open*()  function while the sqlite3 command 
shell is
running without problems.
I added the sqlite3.h file to my project.
/**/
#include 
#include "sqlite3.h"

static int callback(void *NotUsed, int argc, char **argv, char **azColName){
  int i;
  for(i=0; i

[sqlite] SQLite version 3.6.4 planned for 2008-10-15

[D. Richard Hipp]

We are planning to release SQLite version 3.6.4 at approximately  
2008-10-15 12:00:00 UTC.  Additional information can be found on the  
draft website:

http://www.sqlite.org/draft/index.html

If you have issues or concerns with any aspect of the upcoming  
release, now would be a good time to raise them.

Note that the future of the INDEXED BY clause  is uncertain and it  
might be pulled from the release.

I suppose that announcements of this kind should really only be posted  
on the sqlite-dev mailing list, not on sqlite-users.  So I will likely  
reframe from making subsequent pre-release announcements on sqlite- 
users and use only sqlite-dev for such purposes.  If you think this  
revised policy is out of line with the usual practice on other  
projects, please let me know.

D. Richard Hipp
[EMAIL PROTECTED]



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] howto setup SQLite with Powershell ?

[Jeffrey Becker]

Oh I see.  System.Data.SQLite doesn't provide a Snap-in so
Add-PSSnapin wont work.  You could use:
[System.Reflection.Assembly]::LoadWithPartialName("System.Data.SQLite")
But from what I see this method is considered 'bad' because this could
lead to versioning issues.  Since the System.Data.Common interfaces
are very stable I'd personally feel safe doing this but ymmv.

On Thu, Oct 9, 2008 at 10:15 AM, Helga Lachmann <[EMAIL PROTECTED]> wrote:
> I don't know.
> How should I do this?
> I think that's the point.
>
> As I mentioned I tried manually with Add-PSSnapin, but that doesn't work.
> (... PowerShell-Snap-In "System.Data.SQLite" is not installed ...)
>
>
>
>  Original-Nachricht 
>> Datum: Thu, 9 Oct 2008 10:03:07 -0400
>> Von: "Jeffrey Becker" <[EMAIL PROTECTED]>
>> An: "General Discussion of SQLite Database" 
>> Betreff: Re: [sqlite] howto setup SQLite with Powershell ?
>
>> IIRC powershell doesnt automatically load every assembly in the GAC.
>> How are you loading the System.Data.SQLite assembly?
>>
>> On Thu, Oct 9, 2008 at 8:51 AM,  <[EMAIL PROTECTED]> wrote:
>> > Hello,
>> >
>> > I looked unavailingly around for hours to get SQLite and Powershell
>> working together.
>> >
>> > Is there any little installation/configuration guide for this?
>> >
>> > The problem seems to be how to get SQLite available to PS.
>> >
>> >
>> > I used installutil, which reported "successful".
>> > AddPSSnapin failed with error "...DLL not installed"
>> >
>> > I tried to connect via Database Connection and even with the
>> DBProviderFactory method.
>> > But Powershell always reports an error ("Unable to find the requested
>> .Net Framework Data Provider.  It may not be installed.")
>> >
>> > I installed SQLite for ADO.NET 2.0 from sourceforge.
>> > The result seems ok (finished successfully with no errors, SQLite.NET
>> program-folder)
>> > The documentation tells also about using DbProviderFactories.
>> > It says to modify the app.config file. This seems to be a DotNet
>> scenario for VisualStudio but doesn't work for PS.
>> >
>> > In my desperation I copied the SQLite-DLL around in various locations,
>> added manually a line to the machine.config file, but nothing worked.
>> >
>> >
>> > I'm using XPPro, SP3.
>> >
>> >
>> > thanks a lot
>> >
>> > H.L.
>> >
>> > --
>> > GMX startet ShortView.de. Hier findest Du Leute mit Deinen Interessen!
>> > Jetzt dabei sein:
>> http://www.shortview.de/[EMAIL PROTECTED]
>> > ___
>> > sqlite-users mailing list
>> > sqlite-users@sqlite.org
>> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>> >
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
> --
> Pt! Schon vom neuen GMX MultiMessenger gehört? Der kann`s mit allen: 
> http://www.gmx.net/de/go/multimessenger
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] howto setup SQLite with Powershell ?

[Helga Lachmann]

I don't know.
How should I do this?
I think that's the point.

As I mentioned I tried manually with Add-PSSnapin, but that doesn't work.
(... PowerShell-Snap-In "System.Data.SQLite" is not installed ...)



 Original-Nachricht 
> Datum: Thu, 9 Oct 2008 10:03:07 -0400
> Von: "Jeffrey Becker" <[EMAIL PROTECTED]>
> An: "General Discussion of SQLite Database" 
> Betreff: Re: [sqlite] howto setup SQLite with Powershell ?

> IIRC powershell doesnt automatically load every assembly in the GAC.
> How are you loading the System.Data.SQLite assembly?
> 
> On Thu, Oct 9, 2008 at 8:51 AM,  <[EMAIL PROTECTED]> wrote:
> > Hello,
> >
> > I looked unavailingly around for hours to get SQLite and Powershell
> working together.
> >
> > Is there any little installation/configuration guide for this?
> >
> > The problem seems to be how to get SQLite available to PS.
> >
> >
> > I used installutil, which reported "successful".
> > AddPSSnapin failed with error "...DLL not installed"
> >
> > I tried to connect via Database Connection and even with the
> DBProviderFactory method.
> > But Powershell always reports an error ("Unable to find the requested
> .Net Framework Data Provider.  It may not be installed.")
> >
> > I installed SQLite for ADO.NET 2.0 from sourceforge.
> > The result seems ok (finished successfully with no errors, SQLite.NET
> program-folder)
> > The documentation tells also about using DbProviderFactories.
> > It says to modify the app.config file. This seems to be a DotNet
> scenario for VisualStudio but doesn't work for PS.
> >
> > In my desperation I copied the SQLite-DLL around in various locations,
> added manually a line to the machine.config file, but nothing worked.
> >
> >
> > I'm using XPPro, SP3.
> >
> >
> > thanks a lot
> >
> > H.L.
> >
> > --
> > GMX startet ShortView.de. Hier findest Du Leute mit Deinen Interessen!
> > Jetzt dabei sein:
> http://www.shortview.de/[EMAIL PROTECTED]
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

-- 
Pt! Schon vom neuen GMX MultiMessenger gehört? Der kann`s mit allen: 
http://www.gmx.net/de/go/multimessenger
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] howto setup SQLite with Powershell ?

[Jeffrey Becker]

IIRC powershell doesnt automatically load every assembly in the GAC.
How are you loading the System.Data.SQLite assembly?

On Thu, Oct 9, 2008 at 8:51 AM,  <[EMAIL PROTECTED]> wrote:
> Hello,
>
> I looked unavailingly around for hours to get SQLite and Powershell working 
> together.
>
> Is there any little installation/configuration guide for this?
>
> The problem seems to be how to get SQLite available to PS.
>
>
> I used installutil, which reported "successful".
> AddPSSnapin failed with error "...DLL not installed"
>
> I tried to connect via Database Connection and even with the 
> DBProviderFactory method.
> But Powershell always reports an error ("Unable to find the requested .Net 
> Framework Data Provider.  It may not be installed.")
>
> I installed SQLite for ADO.NET 2.0 from sourceforge.
> The result seems ok (finished successfully with no errors, SQLite.NET 
> program-folder)
> The documentation tells also about using DbProviderFactories.
> It says to modify the app.config file. This seems to be a DotNet scenario for 
> VisualStudio but doesn't work for PS.
>
> In my desperation I copied the SQLite-DLL around in various locations, added 
> manually a line to the machine.config file, but nothing worked.
>
>
> I'm using XPPro, SP3.
>
>
> thanks a lot
>
> H.L.
>
> --
> GMX startet ShortView.de. Hier findest Du Leute mit Deinen Interessen!
> Jetzt dabei sein: http://www.shortview.de/[EMAIL PROTECTED]
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] howto setup SQLite with Powershell ?

[HLachmann]

Hello,

I looked unavailingly around for hours to get SQLite and Powershell working 
together.

Is there any little installation/configuration guide for this?

The problem seems to be how to get SQLite available to PS.


I used installutil, which reported "successful".
AddPSSnapin failed with error "...DLL not installed"

I tried to connect via Database Connection and even with the DBProviderFactory 
method.
But Powershell always reports an error ("Unable to find the requested .Net 
Framework Data Provider.  It may not be installed.")

I installed SQLite for ADO.NET 2.0 from sourceforge.
The result seems ok (finished successfully with no errors, SQLite.NET 
program-folder)
The documentation tells also about using DbProviderFactories.
It says to modify the app.config file. This seems to be a DotNet scenario for 
VisualStudio but doesn't work for PS.

In my desperation I copied the SQLite-DLL around in various locations, added 
manually a line to the machine.config file, but nothing worked.


I'm using XPPro, SP3.


thanks a lot

H.L.

-- 
GMX startet ShortView.de. Hier findest Du Leute mit Deinen Interessen!
Jetzt dabei sein: http://www.shortview.de/[EMAIL PROTECTED]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] END TRANSACTION failed with "database is locked"

[Manoj M]

Hi, Thanks for your replies.

Actually I have only one thread writing to the table and there can be 'n'
number of threads reading the table. I am not yet found why this "database
locked" error came in my application.

Since the database is locked even "End TRANSACTION" also won't help, right?

I will comeback with more details 2row as today is holiday here.

Regards,
Manoj Marathayil


On Wed, Oct 8, 2008 at 9:30 PM, <[EMAIL PROTECTED]> wrote:

> Send sqlite-users mailing list submissions to
>sqlite-users@sqlite.org
>
> To subscribe or unsubscribe via the World Wide Web, visit
>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> or, via email, send a message with subject or body 'help' to
>[EMAIL PROTECTED]
>
> You can reach the person managing the list at
>[EMAIL PROTECTED]
>
> When replying, please edit your Subject line so it is more specific
> than "Re: Contents of sqlite-users digest..."
>
>
> Today's Topics:
>
>   1. END TRANSACTION failed with "database is locked"
>  (Manoj Marathayil)
>   2. Re: END TRANSACTION failed with "database is locked"
>  (Alexandre Courbot)
>   3. Re: END TRANSACTION failed with "database is locked" (Ken)
>   4. Re: Record locking (Ken)
>
>
> --
>
> Message: 1
> Date: Wed, 8 Oct 2008 19:20:02 +0530
> From: "Manoj Marathayil" <[EMAIL PROTECTED]>
> Subject: [sqlite] END TRANSACTION failed with "database is locked"
> To: sqlite-users@sqlite.org
> Message-ID:
><[EMAIL PROTECTED]>
> Content-Type: text/plain; charset=ISO-8859-1
>
> Hi,
> I am executing some operations in a loop and all the operations are wrapped
> inside a transaction. During one iteration the "END TRANSACTION" returned
> with an error "database is locked". Since this is in a loop my next request
> to "BEGIN TRASACTION" failed with "cannot start a transaction within a
> transaction". What is the normal way to get rid of this situation? Is it
> right to check the status with "sqlite3_get_autocommit" and issue a
> "ROLLBACK TRASACTION" if it inside a transaction before issuing BEGIN?
>
> Regards,
> Manoj
>
>
> --
>
> Message: 2
> Date: Wed, 8 Oct 2008 22:58:59 +0900
> From: "Alexandre Courbot" <[EMAIL PROTECTED]>
> Subject: Re: [sqlite] END TRANSACTION failed with "database is locked"
> To: "General Discussion of SQLite Database" 
> Message-ID:
><[EMAIL PROTECTED]>
> Content-Type: text/plain; charset=ISO-8859-1
>
> > I am executing some operations in a loop and all the operations are
> wrapped
> > inside a transaction. During one iteration the "END TRANSACTION" returned
> > with an error "database is locked". Since this is in a loop my next
> request
> > to "BEGIN TRASACTION" failed with "cannot start a transaction within a
> > transaction". What is the normal way to get rid of this situation? Is it
> > right to check the status with "sqlite3_get_autocommit" and issue a
> > "ROLLBACK TRASACTION" if it inside a transaction before issuing BEGIN?
>
> This is probably because you still have one active query when during
> the commit. Try to sqlite3_finalize all your queries before the
> commit.
>
> Alex.
>
>
> --
>
> Message: 3
> Date: Wed, 8 Oct 2008 08:46:39 -0700 (PDT)
> From: Ken <[EMAIL PROTECTED]>
> Subject: Re: [sqlite] END TRANSACTION failed with "database is locked"
> To: General Discussion of SQLite Database 
> Message-ID: <[EMAIL PROTECTED]>
> Content-Type: text/plain; charset=iso-8859-1
>
> The second error problem is due to the first.
> ?Another process has begun a transaction Prior to you running the commit.
> /End transaction. At this point you have no choice but to rollback and re
> run the loop.
>
> A Proper fix might include changing the begin transaction to a "begin
> immediate",? Then Either The begin will either fail and not be able to start
> a TXN. Or it will succeed.. If it fails simply wait a while and retry.
>
> I would not follow the other posters advice regarding finalize. You want to
> use sqlite3_reset on all of the prepared statements. Before you close the
> database you should finalize or if your unlikely to need the statement in a
> while then finalize.
>
> HTH
> Ken
>
>
> --- On Wed, 10/8/08, Manoj Marathayil <[EMAIL PROTECTED]> wrote:
> From: Manoj Marathayil <[EMAIL PROTECTED]>
> Subject: [sqlite] END TRANSACTION failed with "database is locked"
> To: sqlite-users@sqlite.org
> Date: Wednesday, October 8, 2008, 8:50 AM
>
> Hi,
> I am executing some operations in a loop and all the operations are wrapped
> inside a transaction. During one iteration the "END TRANSACTION"
> returned
> with an error "database is locked". Since this is in a loop my next
> request
> to "BEGIN TRASACTION" failed with "cannot start a transaction
> within a
> transaction". What is the normal way to get rid of this situation? Is it
> right to check the status with 

Re: [sqlite] Adding index to table makes SELECT much slower. Why?

[Konrad J Hambrick]

On 10/08/2008 05:50 PM, James Pringle wrote:
> Hi-
> 
>   I am new to sqlite, and am having a puzzling problem.  I have read
> that adding an INDEX to a sqlite table can never make a SELECT
> statement slower.  However, in my case, it seems to be making the
> select statement an order of magnitude slower, which is not what I
> wanted!  What could be going on?
> 
>   I am calling SQLite from python 2.5 from fink useing the sqlite3
> module on OS X 10.5 with 2Gb of memory.  My data base contains 32
> million lines of ocean hydrographic data, stored on disk, with the
> table created with the following statement:
> 


James --

I am not familiar with the Mac OSX OS ...

Have you checked the ulimit settings for the user doing the query ?

How large is your DB with and without the INDEX ?

I come up with a swag of ~ 2.2 GiB for the contents of the data in
your table, not counting the necessary overhead for a sqlite .db :

CREATE TABLE hydro
(
   lat REAL, --  1 -  8 Bytes
   lon REAL, --  2 -  8 Bytes
   yearINTEGER,  --  3 -  8 Bytes
   month   INTEGER,  --  4 -  8 Bytes
   day INTEGER,  --  5 -  8 Bytes
   timeREAL, --  6 -  8 Bytes
   cast_id INTEGER,  --  7 -  8 Bytes
   depth   REAL, --  8 -  8 Bytes
   T   REAL, --  9 -  8 Bytes
   S   REAL, -- 10 -  8 Bytes
   water_depth REAL  -- 11 -  8 Bytes
)  ; --  88 Bytes  * 32E6 = 2,816,000,000 Bytes

I do know when I exceed my ulimit settings, sqlite3 query performance suffers...

HTH.

-- kjh
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sqlite3_step and column name

[Shaun R.]

I'm trying to understand a bit how this works.  For my example i just simply 
want to set a column value to a char.  If i know the name of the column how 
can id o this.  For example the column name i want is 'fname'.  I know the 
section below with setting myname and then printing it doesnt really make 
sense since i could do both of these in one line, just doing that to get  a 
idea.  The `myname = plineInfo[fname];` i'm sure is wrong, it's just there 
to give you guys a idea of what i'm trying to get.  Were am i going wrong 
here?


int main(void)
{
   char myname[50];
   sqlite3 *db;
   sqlite3_stmt *plineInfo = 0;

   if(sqlite3_open("my.db", ) != SQLITE_OK) {
  return 0;
   }

   if(sqlite3_prepare(db, "SELECT * FROM mytable", -1, , 0) != 
SQLITE_OK) {
  return 0;
   }

   while((rc = sqlite3_step(plineInfo)) == SQLITE_ROW) {
   /* i know this doesnt make much sense to do but it's only a test */
  myname = plineInfo[fname];
  printf("fname column was set to %s\n", myname);
   }

   sqlite3_finalize(plineInfo);

   sqlite3_close(db);

   return 1;
}



~Shaun



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Adding index to table makes SELECT much slower. Why?

[Daniel Önnerby]

Have you tried running VACUUM and ANAYZE on the indexed table?
Not that I think it will matter, but who knows :)

Daniel


James Pringle wrote:
> Hi-
>
>   I am new to sqlite, and am having a puzzling problem.  I have read
> that adding an INDEX to a sqlite table can never make a SELECT
> statement slower.  However, in my case, it seems to be making the
> select statement an order of magnitude slower, which is not what I
> wanted!  What could be going on?
>
>   I am calling SQLite from python 2.5 from fink useing the sqlite3
> module on OS X 10.5 with 2Gb of memory.  My data base contains 32
> million lines of ocean hydrographic data, stored on disk, with the
> table created with the following statement:
>
> CREATE TABLE hydro (lat REAL, lon REAL, year INTEGER,
> month INTEGER, day INTEGER, time REAL, cast_id INTEGER,
> depth REAL, T REAL, S REAL, water_depth REAL)
>
> When I perform a SELECT that returns about 0.6% of the data in the
> database (about 200,000 records)
>
> SELECT lat, lon, depth FROM hydro WHERE depth>= 49.01 AND depth <= 50.99
>
> It selects and returns the records in 82 seconds.  I wished to improve
> on this time, so I created an index with the following command:
>
> CREATE INDEX hydro_indx ON hydro (depth)
>
> I then tried the SELECT command again, and read the results into
> memory, and it took 717 seconds!?!
>
> The "depth" field contains many distinct numeric values, with no
> specific value matching more then 0.5% of the data base.  When I DROP
> the index with the command
>
> DROP INDEX hydro_indx
>
> The SELECT time returns to about 80 seconds, confirming that it is the
> index which is slowing things down.  What is going on?  I have
> repeated and confirmed these timings.
>
> I have listened for disk chatter and monitored the system, and it does
> not seem to be thrashing swap, or otherwise becoming unresponsive.
>
> I have two questions:
>
> 1) Why is the index making things slower?
> 2) How can I make my SELECT statement faster?  The primary
> selection will be done
>on the "depth" and "water_depth" keys.
>
> I thank you for your time.
>
> Cheers,
> Jamie Pringle
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>   
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] visual basic and sqlite

[Gilles Ganault]

On Thu, 9 Oct 2008 18:05:25 +0800, "Raziedahanin KASIM"
<[EMAIL PROTECTED]> wrote:
>I want to develop a simple application using VB 6.0 and sqlite Where should
>i start?

www.sqlite.org/cvstrac/wiki?p=SqliteWrappers

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] visual basic and sqlite

[Raziedahanin KASIM]

I want to develop a simple application using VB 6.0 and sqlite Where should
i start?

I want to craete a embedded database into this small application. Anyone can
advice and guide me.

Thank you in advance



-- 
Hanni
ONYX Enterprise Sdn Bhd
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Performance - SQLite ODBC against SQLite C API on SLES10

[Darko Filipovic]

Hello,

I'm doing some performance tests comparing 
SQLite ODBC and SQLite C API (both 3.5.2 
version) on SLES10 (64bit).

I'm inserting 1000 rows into 4 columns table 
(int, int64, double, int64) out of transaction 
and I'm getting much lower performance using 
SQLite API (about 10 seconds slower).
In transaction, result are almost same, but sometimes
it happens that ODBC is faster then SQLite C API.

On Windows I'm getting much better performance 
using SQLite C API, especially in transaction.

Have you any idea what could cause this, or
have you any reasonable explanation for this
behavior?

Darko F.


  
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Adding index to table makes SELECT much slower. Why?

[[EMAIL PROTECTED]]

When used with the index, the query may be producing too
many random (slow) disk seeks into your 2GB+ database table.
Increasing the page cache substantially may help.

But if the rows of the main table were accessed in order it might
also reduce the number of page seeks. Out of curiosity, without 
changing the page cache size, how long does this query take
with the index in place?

 SELECT lat, lon, depth FROM hydro where rowid in (
   select rowid from hydro 
   WHERE depth>= 49.01 AND depth <= 50.99
 );

If that doesn't work, you might try populating the hydro table 
with data sorted by depth in order to keep the data locality in 
check and disk seeks to a minimum.


-- 
View this message in context: 
http://www.nabble.com/Adding-index-to-table-makes-SELECT-much-slower.-Why--tp19889143p19894289.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users