[sqlite] Long retrieval times
Hi, I have a DB containing about 6 million records with my indexes properly created. When I run a query against a particular column where a particular (varchar) value is present, it returns very quickly. e.g. select * from myTable where column2='abcd'; (Returns pretty quick) However if I use something like: select * from myTable where column2!=''; (Takes a long time to return). I guess because the column in that row isn't indexed? Any alternatives? Thanks!
Re: [sqlite] Segmentation fault on large selects
Dear Kervin, > Can you run the sqlite3 under dbx? You may have better luck > getting a backtrace that way instead of reading the core file > after the crash. eg. 'dbx -r sqlite3' or something similar. Thanks for the suggestion, but I already tried that with exactly the same results. Kind regards Derek
Re: [sqlite] using tcl/tk and sqlite
In addition to what D.R. Hipp said, you are also creating and closing your
db before you get the chance to enter any data, what you really need is to
create a procedure to (1) open db (2) enter data and (3) close db every
time the "execute" button is pressed. Below is a complete working example
of what you want to do. Notice when you use the entry widget you don't
have to worry about using any types of quotes or slashes.
#- create your DB -
load c:/tcla/lib/sqlite/tclsqlite3 sqlite3
sqlite3 db c:/test.sql
db eval {
CREATE TABLE t1(a,b,c);
}
set a 0
set b 0
set c 0
# fire up this proc when the execute button is hit -
proc updateDB {a b c} {
sqlite3 db c:/test.sql ;# open db
db eval {insert into t1 values ($a,$b,$c)} ;# enter data
db close ;# close db
}
#--- create & pack your buttons in tcl --
entry .entry1 -width 20 -textvariable a
entry .entry2 -width 20 -textvariable b
entry .entry3 -width 20 -textvariable c
button .button1 -text "execute" -command {updateDB $a $b $c}
pack .entry1 .entry2 .entry3 .button1
#--- the end --
"D. Richard Hipp"
<[EMAIL PROTECTED]> To:
sqlite-users@sqlite.org
cc:
08/01/2005 03:30 Subject: Re: [sqlite] using
tcl/tk and sqlite
PM
Please respond to
sqlite-users
See my comments below.
On Mon, 2005-08-01 at 21:06 +0200, Peter Berkel wrote:
> Hallo,
>
> I am not an expert in programming and I try make a frontend
> for sqlite using tcl/tk. See example code below.
>
> I have the following problem. I try to entry data which I want to insert
in a sqlite database. The Values of the textvariables a b and c in the
entry widget are set in the insert statement.
>
> What do I wrong and how can I solve the problem so that I can use the
entry widget to insert, modify and delete dat from a sqlite database.
>
> Thanks for helping me out.
>
> Peter Berkel
>
>
> load tclsqlite3 sqlite3
> sqlite3 db test.db
> db eval {
> CREATE TABLE t1(a,b,c);
> }
>
> set a 0
> set b 0
> set c 0
>
>
> entry .entry1 -width 20 -textvariable a
> entry .entry2 -width 20 -textvariable b
> entry .entry3 -width 20 -textvariable c
>
>
> db eval {insert into t1 values ($a,$b,$c)}
The statement above does the insert, but it does
so immediately, not in response to the button
press. To run this command in response to the
button pressed, do this:
button .button1 -text execute -commmand {
db eval {insert into t1 values($a,$b,$c)}
}
>
> button .button1 -text "execute"
>
> pack .entry1 .entry2 .entry3 .button1
>
> db close
The information transmitted is intended only for the person(s)or entity
to which it is addressed and may contain confidential and/or legally
privileged material. Delivery of this message to any person other than
the intended recipient(s) is not intended in any way to waive privilege
or confidentiality. Any review, retransmission, dissemination or other
use of, or taking of any action in reliance upon, this information by
entities other than the intended recipient is prohibited. If you
receive this in error, please contact the sender and delete the
material from any computer.
For Translation:
http://www.baxter.com/email_disclaimer
RE: AW: [sqlite] VB6 question
Steve,
I works very similar to ADO. Except no Data Control
Ray
Steve O'Hara <[EMAIL PROTECTED]> wrote:
How does a C++ wrapper help someone using VB6?
Steve
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]
rg]On Behalf Of RAY BORROR
Sent: 29 July 2005 05:22
To: sqlite-users@sqlite.org
Subject: Re: AW: [sqlite] VB6 question
I am using SQLite Plus sucessfully
Ray Borror
Gregory Letellier wrote:
all the wrapper ar for 2.8 and i wan't use the 3.0
anyone known 3.0 wrapper ?
Steve O'Hara a écrit :
>Don't want to dampen your enthusiasm for this route but. why do you
>think there are so many VB wrappers for SQLite?
>
>It's because you can't use the SQLite DLL directly from VB - some functions
>will work but the essential ones won't. It's because the DLL returns things
>like pointers to arrays of pointers which is not very groovy in VB (in
fact,
>you would have to resort to some OS calls to unravel that).
>
>Use one of the wrappers.
>
>Steve
>
>
>
>
>
>
>-Original Message-
>From: [EMAIL PROTECTED]
>[mailto:[EMAIL PROTECTED]
>rg]On Behalf Of Gregory Letellier
>Sent: 27 July 2005 11:17
>To: sqlite-users@sqlite.org
>Subject: Re: AW: [sqlite] VB6 question
>
>
>Ok Thank's for your help i will trying this !!!
>
>[EMAIL PROTECTED] a écrit :
>
>
>
>>Hi,
>>
>>unfortunately this has to do with the C calling convention used by
>>
>>
>sqlite3.dll. By default DLLs compiled with C have the cdecl calling
>convention, but VB only supports the stdcall calling convention.
>
>
>>You must recompile sqlite using MS Visual C++ or other compiler and switch
>>
>>
>the default calling convention from cdecl to stdcall in the compiler/linker
>settings.
>
>
>>HTH
>>Michael
>>
>>
>>
>>
>>
>>>hello i'm trying to open a database with VB6 without wrapper
>>>
>>>i'm using sqlite3.dll
>>>
>>>and it's my code :
>>>
>>>Option Explicit
>>>Private Declare Function sqlite3_open Lib "sqlite3.dll" (ByVal filename
>>>As String, ByRef dbHandle As Long) As Long
>>>Private Declare Function sqlite3_open16 Lib "sqlite3.dll" (ByVal
>>>filename As String, ByRef dbHandle As Long) As Long
>>>Private Declare Sub sqlite3_close Lib "sqlite3.dll" (ByVal DB_Handle As
>>>Long)
>>>
>>>Private Sub Form_Load()
>>> Dim lRet As Long
>>> Dim lDbHandle As Long
>>> Dim sFilename As String
>>>
>>> sFilename = "c:\toto.db"
>>> sqlite3_open sFilename, lDbHandle
>>> MsgBox ("lRet=" & lRet)
>>> MsgBox ("ldbhandle=" & lDbHandle)
>>> sqlite3_close (lDbHandle)
>>>End Sub
>>>
>>>when i launch it, i've an error 49 : Bad DLL calling convention
>>>
>>>anyone can help me ? where is my fault ?
>>>
>>>thx
>>>Gregory Letellier
>>>
>>>
>>>
>>>
>>>
>>>
>>
>>
>>
>>
>
>
>
>
>
>
>
Re: [sqlite] Segmentation fault on large selects
Am 01.08.2005 um 21:41 schrieb Kervin L. Pierre: scunacc wrote: I have built with debugging on, and can't do anything with the core dump: dbx Type 'help' for help. enter object file name (default is `a.out', ^D to exit): sqlite3 reading symbolic information ... [using memory image in core] Illegal instruction (reserved addressing fault) in . at 0x0 ($t1) warning: Unable to access address 0x0 from core 0x warning: Unable to access address 0x0 from core Can you run the sqlite3 under dbx? You may have better luck getting a backtrace that way instead of reading the core file after the crash. eg. 'dbx -r sqlite3' or something similar. May or may not be related to a problem we have which I am just trying to track down this minute: when our "create view as select..." statement becomes too large (in terms of characters in the statement), we get an assertion (no crash here, though) in btree.c and the backtrace looks similar to the one scunacc provided, which made me think the two might be related... Anyway, I found that if I go beyond 866 characters in the query statement, I get the assertion. Your statement seem to be shorter, though...
Re: [sqlite] Segmentation fault on large selects
scunacc wrote: I have built with debugging on, and can't do anything with the core dump: dbx Type 'help' for help. enter object file name (default is `a.out', ^D to exit): sqlite3 reading symbolic information ... [using memory image in core] Illegal instruction (reserved addressing fault) in . at 0x0 ($t1) warning: Unable to access address 0x0 from core 0x warning: Unable to access address 0x0 from core Can you run the sqlite3 under dbx? You may have better luck getting a backtrace that way instead of reading the core file after the crash. eg. 'dbx -r sqlite3' or something similar. Regards, Kervin
Re: [sqlite] using tcl/tk and sqlite
See my comments below.
On Mon, 2005-08-01 at 21:06 +0200, Peter Berkel wrote:
> Hallo,
>
> I am not an expert in programming and I try make a frontend
> for sqlite using tcl/tk. See example code below.
>
> I have the following problem. I try to entry data which I want to insert in a
> sqlite database. The Values of the textvariables a b and c in the entry
> widget are set in the insert statement.
>
> What do I wrong and how can I solve the problem so that I can use the entry
> widget to insert, modify and delete dat from a sqlite database.
>
> Thanks for helping me out.
>
> Peter Berkel
>
>
> load tclsqlite3 sqlite3
> sqlite3 db test.db
> db eval {
> CREATE TABLE t1(a,b,c);
> }
>
> set a 0
> set b 0
> set c 0
>
>
> entry .entry1 -width 20 -textvariable a
> entry .entry2 -width 20 -textvariable b
> entry .entry3 -width 20 -textvariable c
>
>
> db eval {insert into t1 values ($a,$b,$c)}
The statement above does the insert, but it does
so immediately, not in response to the button
press. To run this command in response to the
button pressed, do this:
button .button1 -text execute -commmand {
db eval {insert into t1 values($a,$b,$c)}
}
>
> button .button1 -text "execute"
>
> pack .entry1 .entry2 .entry3 .button1
>
> db close
[sqlite] using tcl/tk and sqlite
Hallo,
I am not an expert in programming and I try make a frontend
for sqlite using tcl/tk. See example code below.
I have the following problem. I try to entry data which I want to insert in a
sqlite database. The Values of the textvariables a b and c in the entry widget
are set in the insert statement.
What do I wrong and how can I solve the problem so that I can use the entry
widget to insert, modify and delete dat from a sqlite database.
Thanks for helping me out.
Peter Berkel
load tclsqlite3 sqlite3
sqlite3 db test.db
db eval {
CREATE TABLE t1(a,b,c);
}
set a 0
set b 0
set c 0
entry .entry1 -width 20 -textvariable a
entry .entry2 -width 20 -textvariable b
entry .entry3 -width 20 -textvariable c
db eval {insert into t1 values ($a,$b,$c)}
button .button1 -text "execute"
pack .entry1 .entry2 .entry3 .button1
db close
Re: [sqlite] Segmentation fault on large selects
Dear Jay, > Are you running out of memory? The machine has 6GB... I don't think so. It's possible. Actually, since the query will run with the 64-bit command line version I don't *think* so. Thanks for the thought though. Kind regards Derek Jones.
Re: [sqlite] Data/time storing?
> ... gave me enough fodder to waste a bit more time ... FWIW, the SQLite date/time functions were designed so that dates could be stored as astronomical julian-day numbers (floating point). That's the only form that gives millisec precision, but some other form may be more convenient for a particular application. Regards
[sqlite] DB locked on prepare?
Is it me doing something wrong (again :) )? I have a transaction and renaming a table like table1 to let's say table1_tmp (whatever) I open table1_tmp using prepare. Right after that i create a table named table1, i get an error 6. Previously i used gettable instead of prepare and left it open and worked fine.
Re: [sqlite] Data/time storing?
I have seen the possible format's sqlite seems to understand.
However MMDDTHH:MM:SS having the T is not prefereable to me.
This would be my favourite:
MMDD HH:MM:SS
Maybe a more recognizable fieldtype like "DATE", "DATE-TIME" or "TIME" might
help also.
It's better sqlite enforces a rule than we all come up with our custom
types.
Afaik there is no date fieldtype..
- Original Message -
From: "Henry Miller" <[EMAIL PROTECTED]>
To:
Sent: Monday, August 01, 2005 4:34 PM
Subject: Re: [sqlite] Data/time storing?
On 8/1/2005 at 08:41 Puneet Kishor wrote:
I am curious about this as well... not about the various functions,
but
what is, if at all, a better way to store the values -- as '-MM-DD
HH:MM:SS' strings (are they not stored internally as just strings?) or
as unixepoch time (which would likely be stored as an int)?
Is not the latter (unixepoch) faster than the former ('-MM-DD
HH:MM:SS' strings)?
Is one more malleable than the other for conversion into various other
display forms as well as for DATETIME calculations?
This depends on what you want. There is no best for all situations.
Either form is convertible to the other.
MMDDHHMMSS tends to be readable anyone, while epoch is not. This
often makes for simpler code for simple projects.However when you
need to manipulate time in your code it is much easier to do arithmetic
on epoch time. Common programing languages tend to have good
libraries for turning epoch time into something readable, but it is
more code than a simple print on MMDDHHMMSS time.Epoch will run
out of time in 2036 (2038? one of those two), which is creeping up fast
- many current programers will still be working then! (But 64 bit
platforms are coming fast, and that will solve this problem for our
lifetimes, while introducing many other problems)
If your field techs will use some tool to dump the database, MMDD
format is much better, as they can understand it. This is a large win
in many cases. Field debugging is often more expensive than programmer
coding, so if dates are useful in field debugging it can be worth the
pain of using this format in code in the long run. However this method
fails on daylight savings time if you are in the repeated hour and need
to know if it is the first or second.
Epoch is based on UTC, so and the built in libraries handle time zones,
leap years, daylight savings time, and sometimes leap seconds (there
may be more factors I can't recall).This is a hard problem to
solve, and the libraries were written by smarter people than you, and
are well debugged by now. Governments change the exact date or
daylight savings time fairly often, with epoch you don't have to worry
about updating your program for these new dates..
Remember, it is easy to convert between the two (so long as daylight
savings time isn't involved). There is no one best for everyone, so
quit looking for it! Remember business considerations are often bigger
than technical considerations. Sometimes a critical issue will be
subtile for years. (day light savings time for instance may force use
of epoch despite the cost in field debugging time)
Make a choice and move on. This is one of those issues where it is
fairly easy to understand all the concerns, so people like to debate it
in depth to prove they are paying attention. Doing so is a waste of
time.
Re: [sqlite] Segmentation fault on large selects
On 7/31/05, scunacc <[EMAIL PROTECTED]> wrote: > Dear all, > > SQLite is wonderful. Thank you for this piece of software. > > I have a problem however with large tables > 1M rows. Are you running out of memory?
Re: [sqlite] Data/time storing?
Henry Miller wrote:
On 8/1/2005 at 08:41 Puneet Kishor wrote:
I am curious about this as well... not about the various functions, but
what is, if at all, a better way to store the values -- as '-MM-DD
HH:MM:SS' strings (are they not stored internally as just strings?) or
as unixepoch time (which would likely be stored as an int)?
Is not the latter (unixepoch) faster than the former ('-MM-DD
HH:MM:SS' strings)?
Is one more malleable than the other for conversion into various other
display forms as well as for DATETIME calculations?
This depends on what you want. There is no best for all situations.
Either form is convertible to the other.
that, I knew.
and the libraries were written by smarter people than you,
;-). That I didn't.
Make a choice and move on. This is one of those issues where it is
fairly easy to understand all the concerns, so people like to debate it
in depth to prove they are paying attention. Doing so is a waste of
time.
well, you answered my question in depth, and gave me enough fodder to
waste a bit more time thinking about it now rather than regretting later.
Many thanks for your detailed reply.
--
"measure twice, cut once"
Re: [sqlite] Data/time storing?
On 8/1/2005 at 08:41 Puneet Kishor wrote:
>I am curious about this as well... not about the various functions,
but
>what is, if at all, a better way to store the values -- as '-MM-DD
>HH:MM:SS' strings (are they not stored internally as just strings?) or
>as unixepoch time (which would likely be stored as an int)?
>
>Is not the latter (unixepoch) faster than the former ('-MM-DD
>HH:MM:SS' strings)?
>
>Is one more malleable than the other for conversion into various other
>display forms as well as for DATETIME calculations?
>
This depends on what you want. There is no best for all situations.
Either form is convertible to the other.
MMDDHHMMSS tends to be readable anyone, while epoch is not. This
often makes for simpler code for simple projects.However when you
need to manipulate time in your code it is much easier to do arithmetic
on epoch time. Common programing languages tend to have good
libraries for turning epoch time into something readable, but it is
more code than a simple print on MMDDHHMMSS time.Epoch will run
out of time in 2036 (2038? one of those two), which is creeping up fast
- many current programers will still be working then! (But 64 bit
platforms are coming fast, and that will solve this problem for our
lifetimes, while introducing many other problems)
If your field techs will use some tool to dump the database, MMDD
format is much better, as they can understand it. This is a large win
in many cases. Field debugging is often more expensive than programmer
coding, so if dates are useful in field debugging it can be worth the
pain of using this format in code in the long run. However this method
fails on daylight savings time if you are in the repeated hour and need
to know if it is the first or second.
Epoch is based on UTC, so and the built in libraries handle time zones,
leap years, daylight savings time, and sometimes leap seconds (there
may be more factors I can't recall).This is a hard problem to
solve, and the libraries were written by smarter people than you, and
are well debugged by now. Governments change the exact date or
daylight savings time fairly often, with epoch you don't have to worry
about updating your program for these new dates..
Remember, it is easy to convert between the two (so long as daylight
savings time isn't involved). There is no one best for everyone, so
quit looking for it! Remember business considerations are often bigger
than technical considerations. Sometimes a critical issue will be
subtile for years. (day light savings time for instance may force use
of epoch despite the cost in field debugging time)
Make a choice and move on. This is one of those issues where it is
fairly easy to understand all the concerns, so people like to debate it
in depth to prove they are paying attention. Doing so is a waste of
time.
[sqlite] assertion in balance_nonroot
We get a strange assertion in the sqlite3 code in our app which is multithreaded and heavily uses sqlite. The assertion we get is from within balance_nonroot in btree.c, line 4085: assert( cntNew[0]>0 ); We use separate database connections from each thread (actually, more likely, even multiple connections per thread) or they are synchronized using mutexes where a shared connection has to be used. We get the assertions at a point where we drop a temporary view, so I suspect the problem is not that statement, but rather some other corruption happening before. However, if we ignore this assertion, we can continue afterwards and things mostly work in the same database, except for similar exceptions from the same database connection. I still suspect that some thread is corrupting the database's internal management, but could not yet find a place where this would happen. Unfortunately, there are many things going on with our sqlite database due to the multithreaded nature of our app, so it's not really easy to track this down to a simpler case. If I had any idea what kind of problem causes this assertion, this might help tracking down the problem to a simpler problem to find out what's really going wrong. This happens on Windows and Mac OS X, we're using sqlite 3.2.2 (but it also happens when using 3.2.0) Has anybody else seen this assertion and/or knows what's causing it? Thanks,
Re: [sqlite] Data/time storing?
On Aug 1, 2005, at 7:57 AM, Griggs, Donald wrote:
Re:
Wonder what the best way is to store a date (and time).
Hello Edwin,
If you haven't already, you'll want to read about the sqlite date/time
functions at:
http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions
I am curious about this as well... not about the various functions, but
what is, if at all, a better way to store the values -- as '-MM-DD
HH:MM:SS' strings (are they not stored internally as just strings?) or
as unixepoch time (which would likely be stored as an int)?
Is not the latter (unixepoch) faster than the former ('-MM-DD
HH:MM:SS' strings)?
Is one more malleable than the other for conversion into various other
display forms as well as for DATETIME calculations?
--
Puneet Kishor
RE: [sqlite] Data/time storing?
Re: Wonder what the best way is to store a date (and time). Hello Edwin, If you haven't already, you'll want to read about the sqlite date/time functions at: http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions Donald Griggs Opinions are not necessarily those of Misys Healthcare Systems nor its board of directors. -Original Message- From: Edwin Knoppert [mailto:[EMAIL PROTECTED] ...
[sqlite] Data/time storing?
Wonder what the best way is to store a date (and time). At this time i use "mmdd" but is there a by sqlite recommended method? This aplies time as well, not sure what i'll use, prob HH:MM:SS. But then it lacks ms's.
Re: [sqlite] quoting
I wrote a byte to hex converter which is very fast.
I'm very pleased with the poss. of using the X'' notation.
Saves all kinds of (future?) problems.
- Original Message -
From: "Cory Nelson" <[EMAIL PROTECTED]>
To:
Sent: Sunday, July 31, 2005 7:58 PM
Subject: Re: [sqlite] quoting
strings should be surrounded by single quotes, so only single quotes
and null bytes need to be quoted. but, it would be faster to use
binding and not have to deal with quoting.
On 7/31/05, Jakob Hirsch <[EMAIL PROTECTED]> wrote:
Hi,
SQLite happens to be a nice piece of software, so it is about to be
integrated into the Exim MTA.
Exim provides a quoting function for every database it supports, so will
be for SQLite.
Is it correct that the only character that needs to be encoded is the
single quote (')?
While testing I saw the double quote is somewhat equivalent to the
single quote, which gives a little trouble. Exim's quoting function
cannot know, which type of quotes surround, so it will possibly fail if
someone uses double quotes. Is there probably a better way to do that?
(like escaping with \ all possible quoting chars, i.e.)
--
Cory Nelson
http://www.int64.org
