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

[P Kishor]

On 10/8/08, Igor Tandetnik <[EMAIL PROTECTED]> wrote:
> "P Kishor" <[EMAIL PROTECTED]> wrote in
>  message
>
> news:[EMAIL PROTECTED]
>
> > Imagine a db where the column 'foo' that you are querying against has
>  > a different value for every single row. What possible good an index
>  > would do?
>
>
> It would let you find a row with a specific value in this column in logN
>  steps, as opposed to O(N) a full table scan would take.
>
>
>  > The db would spend as much time looking through an index,
>  > and then going back and looking through the db... well, ok, once it
>  > has found the matching entry in the index, it would jump to the right
>  > row in the db, but it won't gain anything.
>
>
> Think of an index at the end of a book. You need to find all pages in a
>  book where a particular term occurs. You don't read the whole index
>  sequentially: since the index is arranged in alphabetical order, you can
>  quickly look up your term, then you can quickly look up those pages by
>  their numbers. Database index works similarly.
>
>  Now imagine a book written with only two words, say "yes" and "no" (yes,
>  such a book would be a pretty boring read). Suppose it, too, has an
>  index at the end. However, an entry for "yes" lists pretty much every
>  page in the book (and so does an entry for "no"). If you need to list
>  all pages that contain the word "yes" (together with, say, the number of
>  words on this page, so that you couldn't cheat and use the index alone),
>  it would be easier to just scan every page in the book, rather than
>  going through the index and looking up each page number separately.
>
>
..

Kudos! what a lovely and clear example.

Many thanks,

Puneet.
___
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?

[Igor Tandetnik]

"P Kishor" <[EMAIL PROTECTED]> wrote in
message
news:[EMAIL PROTECTED]
> Imagine a db where the column 'foo' that you are querying against has
> a different value for every single row. What possible good an index
> would do?

It would let you find a row with a specific value in this column in logN 
steps, as opposed to O(N) a full table scan would take.

> The db would spend as much time looking through an index,
> and then going back and looking through the db... well, ok, once it
> has found the matching entry in the index, it would jump to the right
> row in the db, but it won't gain anything.

Think of an index at the end of a book. You need to find all pages in a 
book where a particular term occurs. You don't read the whole index 
sequentially: since the index is arranged in alphabetical order, you can 
quickly look up your term, then you can quickly look up those pages by 
their numbers. Database index works similarly.

Now imagine a book written with only two words, say "yes" and "no" (yes, 
such a book would be a pretty boring read). Suppose it, too, has an 
index at the end. However, an entry for "yes" lists pretty much every 
page in the book (and so does an entry for "no"). If you need to list 
all pages that contain the word "yes" (together with, say, the number of 
words on this page, so that you couldn't cheat and use the index alone), 
it would be easier to just scan every page in the book, rather than 
going through the index and looking up each page number separately.

Igor Tandetnik 



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

[P Kishor]

On 10/8/08, Igor Tandetnik <[EMAIL PROTECTED]> wrote:
> "P Kishor" <[EMAIL PROTECTED]> wrote in
>  message news:[EMAIL PROTECTED]
>
> > On 10/8/08, James Pringle <[EMAIL PROTECTED]>
>  > wrote:
>
> >> 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!?!
>  >
>
> > Most likely because it doesn't have much it can optimize (you note
>  > above that "no specific value matching more than 0.5% of the data" --
>  > with 32 million rows, that is a lot of variation).
>  >
>  > Think of it like image compression -- if you have too much variation
>  > in your image... imagine the worst case -- every pixel is a different
>  > color. You can't really compress it. It is sort of like that (dunno if
>  > that is a good analogy).
>
>
> With database indexes, it's the reverse. The index is supposed to work
>  best when all or most values in a column are distinct, and worst when a
>  column has just a few distinct values. The rule of thumb is, when the
>  WHERE clause selects about 10% or more of all the records, the query is
>  better off not using the index but doing a linear table scan. But here,
>  the data seems to have a very good cardinality.

Interesting. If I stare at your explanation long enough, it starts to
make sense. Then I quickly reverse back to my own (faulty) logic (I am
assuming that stuff that comes from Igor has a 100% chance of being
correct as opposed to stuff that comes from me).

Imagine a db where the column 'foo' that you are querying against has
a different value for every single row. What possible good an index
would do? The db would spend as much time looking through an index,
and then going back and looking through the db... well, ok, once it
has found the matching entry in the index, it would jump to the right
row in the db, but it won't gain anything.

I guess I should go back to reading some db indexing theory here.


>
>  From James' description, the index should have helped. There must be
>  more to the story.
>
>
>  Igor Tandetnik
>
>
>
>
___
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?

[Igor Tandetnik]

"P Kishor" <[EMAIL PROTECTED]> wrote in
message news:[EMAIL PROTECTED]
> On 10/8/08, James Pringle <[EMAIL PROTECTED]>
> wrote:
>> 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!?!
>
> Most likely because it doesn't have much it can optimize (you note
> above that "no specific value matching more than 0.5% of the data" --
> with 32 million rows, that is a lot of variation).
>
> Think of it like image compression -- if you have too much variation
> in your image... imagine the worst case -- every pixel is a different
> color. You can't really compress it. It is sort of like that (dunno if
> that is a good analogy).

With database indexes, it's the reverse. The index is supposed to work 
best when all or most values in a column are distinct, and worst when a 
column has just a few distinct values. The rule of thumb is, when the 
WHERE clause selects about 10% or more of all the records, the query is 
better off not using the index but doing a linear table scan. But here, 
the data seems to have a very good cardinality.

>From James' description, the index should have helped. There must be 
more to the story.

Igor Tandetnik 



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

[P Kishor]

On 10/8/08, 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.

Most likely because it doesn't have much it can optimize (you note
above that "no specific value matching more than 0.5% of the data" --
with 32 million rows, that is a lot of variation).

Think of it like image compression -- if you have too much variation
in your image... imagine the worst case -- every pixel is a different
color. You can't really compress it. It is sort of like that (dunno if
that is a good analogy).

The db goes through the index wasting time. You drop the index and it
does its thing anyway. Look through the archives with talk about
possible controlling the use of an index in a query... just a few days
ago.

>
>  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] Adding index to table makes SELECT much slower. Why?

[Stephen Woodbridge]

James,

I'm not sure I can answer your questions about speed issues other than 
to suggest the you read up on the rtree index which is designed for this 
type of query and should be extremely fast.

-Steve

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] from access to sqlite

[Olaf Schmidt]

"Fabio Stranieri" <[EMAIL PROTECTED]>
schrieb im Newsbeitrag
news:[EMAIL PROTECTED]
> The program is write in vb6. The original statement with access was:
>
> rsParam.Open "SELECT * FROM " & nome_tab & " WHERE [data_ora] BETWEEN #" &
> Format(ieri_inizio, "/mm/dd hh:nn:ss") & "# and #" & Format(ieri_fine,
> "/mm/dd hh:nn:ss") & "# ORDER BY [data_ora];", cnAdoCom, adOpenStatic,
> adLockReadOnly
>
> the i have buyed  a wrapper for vb6 and sqlite from here:
> http://www.eztools-software.com/tools/sqliteplus/default.asp
>
> then the statement is become:
>
> Set ds = dbConfig.Exec ("SELECT * FROM " & nome_tab & " WHERE data_ora
> BETWEEN " & Format(ieri_inizio, "/mm/dd hh:nn:ss") & " and " &
> Format(ieri_fine, "/mm/dd hh:nn:ss") & " ORDER BY data_ora"
>
> but there is something that does not like to sqlite because it goes in
> error. But i don't understand what!!!

Between your VB-Code and the sqlite-engine is your
(COM-)wrapper and I cannot guarantee that the following
works, because I don't know how the wrapper you bought
was transferring the Access-Date-Format into the sqlite-DB
whilst importing it.

In my wrapper the implementation of the cConverter-class is
changing these "ADO-Dates" to "normal" SQLite-Text-Dates,
which have the form:
ShortDate:'-mm-dd'
DateTime: '-mm-dd hh:nn:ss'
DateTime + milliseconds: '-mm-dd hh:nn:ss.123'

In your VB-Format-Function you should probably use
a format-string like shown below:
Dim D as Date
D = Now
Debug.Print Format(D, "'-mm-dd hh:nn:ss'")
which in this example gives:
'2008-10-09 02:49:09'
...so you could use these return-values for your String-
Concats in your Select-Between-Statement for a quick test.

If that does not work, then you should read in the docu
of your wrapper-provider, in which other format the dates
are stored in SQLite after importing from other Databases.

Olaf Schmidt





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

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

[James Pringle]

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] from access to sqlite

[P Kishor]

On 10/8/08, Fabio Stranieri <[EMAIL PROTECTED]> wrote:
> The program is write in vb6. The original statement with access was:
>
>  rsParam.Open "SELECT * FROM " & nome_tab & " WHERE [data_ora] BETWEEN #" &
>
> Format(ieri_inizio, "/mm/dd hh:nn:ss") & "# and #" & Format(ieri_fine,
>
> "/mm/dd hh:nn:ss") & "# ORDER BY [data_ora];", cnAdoCom, adOpenStatic,
>  adLockReadOnly
>
>  the i have buyed  a wrapper for vb6 and sqlite from here:
>  http://www.eztools-software.com/tools/sqliteplus/default.asp
>
>  then the statement is become:
>
>  Set ds = dbConfig.Exec ("SELECT * FROM " & nome_tab & " WHERE data_ora
>
> BETWEEN " & Format(ieri_inizio, "/mm/dd hh:nn:ss") & " and " &
>  Format(ieri_fine, "/mm/dd hh:nn:ss") & " ORDER BY data_ora"
>
>
> but there is something that does not like to sqlite because it goes in
>  error. But i don't understand what!!!

Rule #1, when seeking help on a mailing list -- don't just say that
you got an error; actually state the error. Help others help you.

Rule #2. Try and provide both specific as well as generic info on your
case. Not everyone in this list uses whatever program you are trying
to use, but they can advise you on the SQLite aspect of it.

That said, seems your query is really of the form

SELECT * FROM table WHERE date_column BETWEEN some_date AND some_other_date;

There is no function called Format(column, ...). The function is strftime.

You can get a lot by looking at the relevant page on the wiki





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


-- 
Puneet Kishor http://punkish.eidesis.org/
Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/
Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] from access to sqlite

[Fabio Stranieri]

The program is write in vb6. The original statement with access was:

rsParam.Open "SELECT * FROM " & nome_tab & " WHERE [data_ora] BETWEEN #" &
Format(ieri_inizio, "/mm/dd hh:nn:ss") & "# and #" & Format(ieri_fine,
"/mm/dd hh:nn:ss") & "# ORDER BY [data_ora];", cnAdoCom, adOpenStatic,
adLockReadOnly

the i have buyed  a wrapper for vb6 and sqlite from here:
http://www.eztools-software.com/tools/sqliteplus/default.asp

then the statement is become:

Set ds = dbConfig.Exec ("SELECT * FROM " & nome_tab & " WHERE data_ora
BETWEEN " & Format(ieri_inizio, "/mm/dd hh:nn:ss") & " and " &
Format(ieri_fine, "/mm/dd hh:nn:ss") & " ORDER BY data_ora"

but there is something that does not like to sqlite because it goes in
error. But i don't understand what!!!


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

Re: [sqlite] getting data by the column name.

[P Kishor]

On 10/8/08, Tim Streater <[EMAIL PROTECTED]> wrote:
> At 14:56 -0400 08/10/08, Igor Tandetnik wrote:
>  >Shaun R. <[EMAIL PROTECTED]> wrote:
>  >>  Anybody help me out here, trying to grab column info based on the
>  >>  columns name.  Examples i keep seeing people seam to use the colunm
>  >>  number but cant you access these values by the name?
>  >
>  >Not in general. Consider:
>  >
>  >select 1, 2, 3*5;
>  >
>  >What do you think the column names are here?
>  >
>  >However, see sqlite3_column_name[16], sqlite3_column_origin_name[16].
>  >With these, you can enumerate all the columns, get the name of each,
>  >figure out whether it's the one you want and thus obtain its number.
>
>
> In PHP I'm doing something like this:
>
>  $dbh= new PDO ("sqlite:mydb");
>  $resorg = $dbh->query ("select * from my_table where absid='$PTR_org'");
>  $orgs   = $resorg->fetch (PDO::FETCH_ASSOC);
>  $organisation = $orgs["name"];
>  $address = $orgs["address"];
>
>  I never use the column number.

That is a facility provided by your specific driver. Perl's DBD (and
so, I am assuming, most other mature drivers) allow the same facility.
Nevertheless, Igor's point is worth underscoring... the column name is
a moving concept. If you use the AS keyword, then the column name is
what you alias it to. See

Last login: Wed Oct  8 09:54:19 on console
[02:30 PM] ~$sqlite3
SQLite version 3.5.9
Enter ".help" for instructions
sqlite> .m col
sqlite> .h on
sqlite> SELECT 1 AS foo, 2 AS elephant, 3 * 4 AS stars, 12;
foo elephantstars   12
--  --  --  --
1   2   12  12
sqlite>




-- 
Puneet Kishor http://punkish.eidesis.org/
Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/
Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] getting data by the column name.

[Igor Tandetnik]

Tim Streater <[EMAIL PROTECTED]>
wrote:
> At 14:56 -0400 08/10/08, Igor Tandetnik wrote:
>> Shaun R. <[EMAIL PROTECTED]>
>> wrote:
>>>  Anybody help me out here, trying to grab column info based on the
>>>  columns name.  Examples i keep seeing people seam to use the colunm
>>>  number but cant you access these values by the name?
>>
>> Not in general. Consider:
>>
>> select 1, 2, 3*5;
>>
>> What do you think the column names are here?
>>
>> However, see sqlite3_column_name[16], sqlite3_column_origin_name[16].
>> With these, you can enumerate all the columns, get the name of each,
>> figure out whether it's the one you want and thus obtain its number.
>
> In PHP I'm doing something like this:
>
> $dbh= new PDO ("sqlite:mydb");
> $resorg = $dbh->query ("select * from my_table where
> absid='$PTR_org'"); $orgs   = $resorg->fetch (PDO::FETCH_ASSOC);
> $organisation = $orgs["name"];
> $address = $orgs["address"];
>
> I never use the column number.

And your point is...?

Igor Tandetnik 



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

Re: [sqlite] getting data by the column name.

[Tim Streater]

At 14:56 -0400 08/10/08, Igor Tandetnik wrote:
>Shaun R. <[EMAIL PROTECTED]> wrote:
>>  Anybody help me out here, trying to grab column info based on the
>>  columns name.  Examples i keep seeing people seam to use the colunm
>>  number but cant you access these values by the name?
>
>Not in general. Consider:
>
>select 1, 2, 3*5;
>
>What do you think the column names are here?
>
>However, see sqlite3_column_name[16], sqlite3_column_origin_name[16].
>With these, you can enumerate all the columns, get the name of each,
>figure out whether it's the one you want and thus obtain its number.

In PHP I'm doing something like this:

$dbh= new PDO ("sqlite:mydb");
$resorg = $dbh->query ("select * from my_table where absid='$PTR_org'");
$orgs   = $resorg->fetch (PDO::FETCH_ASSOC);
$organisation = $orgs["name"];
$address = $orgs["address"];

I never use the column number.
-- 
-- tim
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] getting data by the column name.

[Igor Tandetnik]

Shaun R. <[EMAIL PROTECTED]> wrote:
> Anybody help me out here, trying to grab column info based on the
> columns name.  Examples i keep seeing people seam to use the colunm
> number but cant you access these values by the name?

Not in general. Consider:

select 1, 2, 3*5;

What do you think the column names are here?

However, see sqlite3_column_name[16], sqlite3_column_origin_name[16]. 
With these, you can enumerate all the columns, get the name of each, 
figure out whether it's the one you want and thus obtain its number.

Igor Tandetnik 



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

Re: [sqlite] from access to sqlite

[Timothy A. Sawyer]

With Access, dates have to be surrounded by # and the date format has to be 
compatible with not only the language you are using but SQL as well.

I recommend checking the SQLite docs on acceptable date formats as I have done 
this with success 
--Original Message--
From: Fabio Stranieri
Sender: 
To: sqlite-users@sqlite.org
ReplyTo: General Discussion of SQLite Database
Sent: Oct 8, 2008 14:14
Subject: [sqlite] from access to sqlite

I'm trying to replace db access from my vb6 program to sqlite.

But this query no work

"SELECT * FROM " & nome_tab & " WHERE data_ora BETWEEN " &
Format(ieri_inizio, "/mm/dd hh:nn:ss") & " and " &
Format(ieri_fine, "/mm/dd hh:nn:ss") & " ORDER BY data_ora"

where

ieri = Date - 1
ieri_inizio = ieri & " 00:00:00"
ieri_fine = ieri & " 23:59:59"

can we help me?

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


Timothy A. Sawyer, CISSP
Managing Director
MBD Solutions
Phone: (603) 546-7132
Web: http://www.mybowlingdiary.com
Email: [EMAIL PROTECTED]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Exporting to MySQL?

[Kees Nuyt]

On Wed, 08 Oct 2008 13:14:43 +0200, Gilles Ganault
<[EMAIL PROTECTED]> wrote in General Discussion of
SQLite Database :

>Hello
>
>I'm no MySQL expert, and am having some syntax errors when trying to
>export data from SQLite 3 into MySQL 5.

What's the error number and text?

>Before I dive into MySQL's
>documentation, does someone know how I should rewrite this type of SQL
>to keep MySQL happy?
>
>BEGIN TRANSACTION;
>CREATE TABLE dummy (id TEXT PRIMARY KEY NOT NULL, address TEXT);
>CREATE TABLE support (id INTEGER PRIMARY KEY AUTOINCREMENT, name
>TEXT);
>COMMIT;

I think MySQL uses AUTO_INCREMENT. You will have to dive
into the documentation to make sure. 
A mailing list is not a manual lookup service.

>Thank you.
-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] getting data by the column name.

[Shaun R.]

Anybody help me out here, trying to grab column info based on the columns 
name.  Examples i keep seeing people seam to use the colunm number but cant 
you access these values by the name?

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, just a example showing what 
i'm after */
   myname = plineInfo[fname];
   printf("the column fname has the value of %s stored in it\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

[sqlite] from access to sqlite

[Fabio Stranieri]

I'm trying to replace db access from my vb6 program to sqlite.

But this query no work

"SELECT * FROM " & nome_tab & " WHERE data_ora BETWEEN " &
Format(ieri_inizio, "/mm/dd hh:nn:ss") & " and " &
Format(ieri_fine, "/mm/dd hh:nn:ss") & " ORDER BY data_ora"

where

ieri = Date - 1
ieri_inizio = ieri & " 00:00:00"
ieri_fine = ieri & " 23:59:59"

can we help me?

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

Re: [sqlite] Record locking

[Ken]

I don't think sqlite is meant to be used that way. The locking is at the 
database level or in the case of "shared_cache" mode the locks are held at the 
table level. So locking is pretty much nothing or everything approach. If you 
look at the journal design you'll see it applies to the entire file.

Locking at the row level would be unpractical without a major overhaul.

I am not the author, nor a developer. The authors do suggest that if you need 
row locking or other "heavy weight" DBMS features that there are other DB's 
availble.


--- On Tue, 10/7/08, Steven Charest <[EMAIL PROTECTED]> wrote:
From: Steven Charest <[EMAIL PROTECTED]>
Subject: [sqlite] Record locking
To: sqlite-users@sqlite.org
Date: Tuesday, October 7, 2008, 7:22 PM

Are there any plans to put record locking in Sqlite?
___
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] END TRANSACTION failed with "database is locked"

[Alexandre Courbot]

> 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.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Manoj Marathayil]

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
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite on flash devices

[Paul]

We're using linux.

Data loss is not critical. If anything is lost due to unexpected power
downs, we can rebuild it and add it to the database at a later stage.

Apart from using :memory: another idea is to use linux ramfs and
periodically copy this to flash.

The other thing I'm assuming is to compile SQLITE with
-DSQLITE_TEMP_STORE=3 to keep sqlite temporary files in memory.

-  
-Paul


On Thu, 2008-10-02 at 13:36 -0600, J Glassy wrote:
> Paul,
>  here are a few odd thoughts on this:
> --Flash memory devices in general are subject to 'finite' numbers of
> I/O events, mostly affecting writes; more recent flash memory devices
> ease this constraint a bit, if only by supporting more I/O events
> before they hit their useful life-limit.  I think you're asking the
> right questions though; can you model the likely no. of writes over
> the intended life-cycle based on anticipated activity etc?
> --Some of how you implement this depends on what embedded OS you're
> working with (is it a RTOS, a Linux hybrid, WinCE, Win Mobiles 5 or
> 6.x etc?. I'd want to make sure I could implement the software that
> manages the Sqlite as a daemon or background-service, for better
> flexibility.
> --Your idea of posting transactions from near-real-time event traffic
> to an in-memory definition (":memory:") may be good, certainly bears
> some study. It complicates how you might implement periodic handoffs
> from the ":memory:" instance to the non-volatile 'disk' instance of
> sqlite though, particularily when a serious exception (say, something
> really severe like a power-outage) might need to be trapped almost
> instantaneously. This is coincidentally (precisely) when you may need
> assurance that the most-recent data is safely stored on the
> non-volatile side of the embedded system.  This in turn raises the
> question of your assessment on how much data can you afford to lose?
> (if any) a metric that governs how often you schedule your
> synchronization-transactions; I'm guessing you'll use a delta-oriented
> sync logic to avoid messy redundancy.
> --To try to address your question on how intimately you might need to
> know the insides of sqlite3, you can get away with knowing less in
> proportion to how closely your implementation strategy resembles a
> standard use-case (writing to 'disk'); the more multi-tier mechanism
> you decide to support, the more you'll benefit from knowing sqlite
> better on the inside (IMHO); at least you can study the code anytime
> etc.
> --Overall, always writing to the flash memory as a default strategy
> would be safer, simpler.. presuming you can afford the amount of time
> the I/O takes (some SD devices are notoriously slow at write-side I/O,
> so watch that).  I'm assuming that each transaction is fairly small
> and atomic.  I would prototype any of this using Python's binding to
> sqlite, to give yourself a good feel for the dynamics.
> --My own experience in this sort of implementation is using SyBASE
> Anywhere's mobile database,(along with MobiLink for device-to-host
> synchronization) on WinCE,Win Mobile 5 (not yet v6). I've used sqlite
> primarily on several non-embedded application area, but am looking
> hard myself at just the questions you are addressing.
> 
> I don't if any of this helped.. but good luck.
> 
> joe
> 
> On Thu, Oct 2, 2008 at 2:20 AM, Paul McMahon <[EMAIL PROTECTED]>
> wrote:
> What's the consensus on using sqlite with flash based storage
> such as sd
> cards on embedded devices?
> 
> Has anyone done this successfully yet with a real product
> (that needs to
> last a few years in the field)?
> 
> How does one solve the problem of minimizing writes to flash?
> I've done
> a search through the user lists but there seems to be no
> generally
> agreed way to do this. Does it require intimate knowledge of
> the inner
> workings of sqlite?
> 
> One scheme I thought of was to use an in memory sqlite
> database in
> conjunction with a flash based one.
> 
> New entries go in the in memory database (using ":memory:" on
> filename).
> Searches search both the in memory and flash based database.
> Every now
> and then the in memory database entries get written to the
> flash based
> database.
> 
> Any ideas would be greatly appreciated!
> 
> 
> 
> ___
> 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] Exporting to MySQL?

[Gilles Ganault]

Hello

I'm no MySQL expert, and am having some syntax errors when trying to
export data from SQLite 3 into MySQL 5. Before I dive into MySQL's
documentation, does someone know how I should rewrite this type of SQL
to keep MySQL happy?

BEGIN TRANSACTION;
CREATE TABLE dummy (id TEXT PRIMARY KEY NOT NULL, address TEXT);
CREATE TABLE support (id INTEGER PRIMARY KEY AUTOINCREMENT, name
TEXT);
COMMIT;

Thank you.

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

[sqlite] Random errors

[rrrado2]

Hi, my post is quit long so I hope somebody will read to end :)

I've wrote about my problem before but I still cannot find a solution. My 
windows app is using sqlite, it is compiled using VS 2005. I've tried more 
sqlite versions, including 3.6.3. I've tried also to compile sqlite static 
library with optimizations disabled.
There is one process which opens the database and 1-20 worker threads where 
each also opens the database (each has own connection).
Main thread is mostly reading the database and worker threads are reading DB 
and later making writes once per 1-10 seconds. (Begin>delete some 
range>insert>commit) So there is really not heavy load on the database.
Database consists of 2 files, 1st is open and 2nd is attached using ATTACH sql 
- this is used only for reading.

Some customers are reporting crashes caused by random database errors. Some 
examples:

1.sql="begin immediate", code=SQLITE_CANTOPEN msg="unable to open database file"

2. sql="ATTACH ..." code=SQLITE_MISUSE msg="database is locked" (after 20 
retries with sleep(0)). Why SQLITE_MISUSE and not SQLITE_LOCKED ???

3. sql="commit" code=SQLITE_ERROR msg="sql logic error or missing database"

4. sql="delete ..."-after successful BEGIN, code=SQLITE_CANTOPEN msg="unable to 
open database file"

5. sql="select count(*) ..." code=SQLITE_NOMEM 

There were more kind of errors (esp. SQLITE_IOERR_DELETE) but not with latest 
version 3.6.3 AFAIK.

My exec function become quite complex to deal with some error but is not 
sufficient. What can be the problem?
Thank you.

int Exec()
{
...
while(1)
{
rc = _sqlite3_step(m_stmt);

if (rc == SQLITE_ROW)
return 1;

if (rc == SQLITE_DONE)
{
_sqlite3_reset(m_stmt);
return 0;
}

if (( rc == SQLITE_BUSY) || (rc == SQLITE_LOCKED))
{
Sleep(0);
continue;
}

if ( (rc & 0xff) == SQLITE_IOERR )
{
if (++errCnt > 20)
break;
Sleep(100);
continue;
}



if ( rc == SQLITE_MISUSE )
{
if (++errCnt > 20)
break;

_sqlite3_reset(m_stmt);
Sleep(20);
continue;
}

break;
}

// print error
return -1;
}

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