Anyone have any ideas how to speed up GROUP BY on huge views
in recent versions of SQLite?
http://www.sqlite.org/cvstrac/tktview?tn=1809
The older versions of SQLite (prior to SQLite 3.2.6) used to
perform GROUP BY operations in the main table loop, grouping
rows as it went along. But the
On 5/23/06, Cory Nelson <[EMAIL PROTECTED]> wrote:
> I'm not fully sure about this as I never had to do any programming for
> 64 bits, yet, so correct me if I'm wrong.
GCC's int is also 32bit on x64 (likely because x64 supports 32bit
arithmetic in 64bit apps without penalty, while 64bit
Hello,
SQLite version 3.3.4
Linux ** 2.4.31 #4 SMP Wed Apr 19 16:30:29 CDT 2006 i686 unknown unknown
GNU/Linux
I am just curious about SQLite's date and time manipulation functions. I am
using the sqlite3 command line interface on the above described platform.
When I select a
Why do you connect twice to the DB? You then run into synchronization
issues.
JS
Ran wrote:
Hi all,
Could someone help me with the script below? I get an "SQL logic error or
missing database" and cannot find what I do wrong.
I use sqlite 3.3.4 on Linux.
What I do there is:
1. Open
[EMAIL PROTECTED] wrote:
> Dennis Jenkins <[EMAIL PROTECTED]> wrote:
>
>> Has the crypto extension been updated since last summer?
>>
>>
>
> Yes. The latest code has been sent to you by private email.
> --
> D. Richard Hipp <[EMAIL PROTECTED]>
>
>
Thank you. The code seems to
Dennis Jenkins <[EMAIL PROTECTED]> wrote:
>
> Has the crypto extension been updated since last summer?
>
Yes. The latest code has been sent to you by private email.
--
D. Richard Hipp <[EMAIL PROTECTED]>
I would like to thank all of you. Although it all sounds logic to me, I was
very far from finding the problem alone.
So thanks again,
Ran
On 5/23/06, Jay Sprenkle <[EMAIL PROTECTED]> wrote:
On 5/23/06, Igor Tandetnik <[EMAIL PROTECTED]> wrote:
>
> When a new connection is opened, SQLite
Ran <[EMAIL PROTECTED]> wrote:
> Indeed if I reset after the first step failed, and than prepare again, the
> select works. But I guess this is not the usual way to do things right? I
> mean - shouldn't the first prepare be aware of the fact that the database
> was changed? Or maybe CREATE TABLE
On 5/23/06, Igor Tandetnik <[EMAIL PROTECTED]> wrote:
When a new connection is opened, SQLite reads and caches the schema.
Prepare operation uses this cached schema, without reading from disk.
This is why prepare doesn't know that schema has changed.
It would be pretty pointless to have
Ran <[EMAIL PROTECTED]> wrote:
>
> rc = sqlite3_step(pStmt3);
> if (rc != SQLITE_DONE) { // if we failed, we log it.
> printf("Failed to step statement: %s\n", sqlite3_errmsg(db1));
> }
The sqlite3_errmsg() API does not return the correct error
message text until after you do
Jay Sprenkle <[EMAIL PROTECTED]> wrote:
It was prepared after the schema was changed and written to disk.
That seems pretty unintuitive to me. When you prepare the statement
it evidently doesn't use the current schema, though it knows the
schema has changed...
When a new connection is opened,
* A. Pagaltzis <[EMAIL PROTECTED]> [2006-05-23 09:40]:
> Now we can group together the conditions which do not involve
> the `bounds` table:
>
> (r.qi = 5604 AND r.ri <= 5468) OR (r.ri = 5468 AND r.qi <= 5604)
> AND r.qi >= b.bqis
> AND r.ri >= b.bris
> AND b.bi =
> -Original Message-
> From: Dennis Jenkins [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, May 23, 2006 11:49 AM
> To: sqlite-users@sqlite.org
> Subject: [sqlite] Having troubles with Sqlite3 Crypto
> extension on v3.3.5
>
> Hello,
>
> I'm going to be light on the details here because
* Adrian Ho <[EMAIL PROTECTED]> [2006-05-23 16:05]:
> On Tue, May 23, 2006 at 08:50:56AM +0200, A. Pagaltzis wrote:
> > * [EMAIL PROTECTED] <[EMAIL PROTECTED]> [2006-05-23 02:35]:
> > > What you have to do is:
> > >
> > >SELECT qi, ri, drl, score
> > > FROM ...
> > > WHERE
> -Original Message-
> From: Ran [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, May 23, 2006 11:47 AM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] "SQL logic error or missing database"
>
> Indeed if I reset after the first step failed, and than
> prepare again, the select works.
Ran wrote:
Indeed if I reset after the first step failed, and than prepare again, the
select works. But I guess this is not the usual way to do things right? I
mean - shouldn't the first prepare be aware of the fact that the database
was changed? Or maybe CREATE TABLE is a special case?
If I
Hi Brannon,
* Brannon King <[EMAIL PROTECTED]> [2006-05-23 20:05]:
> Thank you for taking the time to sort out my query!
NP. I have only recently studied SQL in depth, and this was an
interesting exercise.
> This one above was as slow as the original.
Yes, as expected – it is exactly the same
> -Original Message-
> From: Jay Sprenkle [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, May 23, 2006 11:27 AM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] "SQL logic error or missing database"
>
> It was prepared after the schema was changed and written to disk.
> That seems pretty
Hello,
I'm going to be light on the details here because I'm not sure how
much I can publically discuss (with respect to the Sqlite3 crypto license).
Out project has been using Sqlite3 v 3.2.1 for a long time, with Dr.
Hipp's encryption extension (purchased 2005-7-11). I am about to
Indeed if I reset after the first step failed, and than prepare again, the
select works. But I guess this is not the usual way to do things right? I
mean - shouldn't the first prepare be aware of the fact that the database
was changed? Or maybe CREATE TABLE is a special case?
On 5/23/06, Jay
On 5/23/06, Robert Simpson <[EMAIL PROTECTED]> wrote:
> That doesn't seem right.
> The change was made and committed then the database statement
> prepared.
> The change should have already been written so the prepare
> should have gotten the latest stuff. Does it need to be
> closed and
> -Original Message-
> From: Jay Sprenkle [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, May 23, 2006 10:55 AM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] "SQL logic error or missing database"
>
> On 5/23/06, Robert Simpson <[EMAIL PROTECTED]> wrote:
> > After sqlite3_step() fails,
Thank you for taking the time to sort out my query! (The meat is at the
bottom.)
> SELECT
> r.qi,
> r.ri,
> r.drl,
> r.score
> FROM
> results_1 r
> INNER JOIN bounds b ON
> r.qis = b.bqis AND r.ris = b.bris
> WHERE
>
On 5/23/06, Robert Simpson <[EMAIL PROTECTED]> wrote:
After sqlite3_step() fails, you should call sqlite3_reset() on the
statement. This is what will give you the SQLITE_SCHEMA error, indicating
you need to re-prepare your statement.
That doesn't seem right.
The change was made and committed
> -Original Message-
> From: Robert Simpson [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, May 23, 2006 10:30 AM
> To: sqlite-users@sqlite.org
> Subject: RE: [sqlite] "SQL logic error or missing database"
>
[snip]
> rc = sqlite3_step(pStmt3);
> if (rc) rc = sqlite3_reset(pStmt3);
> if
> -Original Message-
> From: Ran [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, May 23, 2006 10:08 AM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] "SQL logic error or missing database"
>
> Oh! Did you run it with a parameter so:
>
> ./bug 1
>
No I missed that little gem. After
Oh! Did you run it with a parameter so:
./bug 1
(otherwise the second connection is not created - sorry, I guess this is
confusing - I just wanted to show that the second connection or the second
table create the problem).
In any case, I reinstalled sqlite-3.3.4 on my Linux - and the bug is
On 5/23/06, Robert Simpson <[EMAIL PROTECTED]> wrote:
I pasted your code into my Windows environment and ran it. It completed
successfully with no errors.
which version of sqlite are you using?
> -Original Message-
> From: Jay Sprenkle [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, May 23, 2006 9:53 AM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] "SQL logic error or missing database"
>
> On 5/23/06, Ran <[EMAIL PROTECTED]> wrote:
> > Thanks for your replies.
> >
> >
> -Original Message-
> From: Ran [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, May 23, 2006 8:37 AM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] "SQL logic error or missing database"
>
> On 5/23/06, Jay Sprenkle <[EMAIL PROTECTED]> wrote:
> > hmmm...
> > it gives a schema changed
On 5/23/06, Ran <[EMAIL PROTECTED]> wrote:
Thanks for your replies.
Actually, in my last email (probably you got it after sending yours), there
is a script which exec the create statements, and check the prepare returned
value of a SELECT (and not delete). Still I get the same bug. So the
The workaround would be to build the statement some other way
(sqlite3_mprintf(), for example) for each individual ALTER TABLE command.
At that point you may want to use sqlite3_exec() instead of
sqlite3_prepare(), depending on how you'll be using the statement. You'll
also have to be more
This may be way off-topic (or really a BIRT bug), but I'm trying to use
eclipse BIRT to create a report off a sqlite DB:
1) add jni dll path to eclipse startup parameters
2) setup sqlite driver in BIRT data sources
3) create new report datasource pointing to existing sqlite DB and test
connection
> -Original Message-
> From: Jay Sprenkle [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, May 23, 2006 8:13 AM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] "SQL logic error or missing database"
>
> hmmm...
> it gives a schema changed because 'delete * from x' actually
> drops the
Kevin Piciulo wrote:
Can I add a column using a variable for the column name? Below is
the prepare statement, which is returning an error.
sqlite3_prepare(m_dbDataBase, "ALTER TABLE users ADD COLUMN ?
varchar;", -1, , NULL);
I'm pretty sure my syntax is correct which leads me to believe
> -Original Message-
> From: Ran [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, May 23, 2006 6:38 AM
> To: sqlite-users
> Subject: [sqlite] "SQL logic error or missing database"
>
[snip]
> Here is the script:
[snip]
> rc = sqlite3_prepare(db1,// Database handle
>
A. Pagaltzis wrote:
I can’t interpret the `EXPLAIN` output well enough to tell
whether this is likely to be faster, I’m afraid. (Actually I
don’t even understand how to tell whether/which indices are being
used; I tried creating a few and they didn’t seem to make a
discernible difference.)
On 5/23/06, Jay Sprenkle <[EMAIL PROTECTED]> wrote:
hmmm...
it gives a schema changed because 'delete * from x' actually drops the
table
but I'm not sure why it gave an error since the prepare was done after the
other change was committed...
...
Thanks for your efforts!
I am afraid that the
I don't see any reason why this should not work.
I can reproduce it with vc6. You should ask DRH
if he can explain what's up.
kamil wrote:
I have to create a database schema in such case.
Thanks in advance,
Kamil
Kamil,
You could use the PRAGMA user_version command (see
http://www.sqlite.org/pragma.html). If you set the user version when you
initialize your schema, you can check if it has been set or not when
hmmm...
it gives a schema changed because 'delete * from x' actually drops the table
but I'm not sure why it gave an error since the prepare was done after the
other change was committed...
program output:
Opened the database.
Opened the database.
Failed to step statement: database schema has
On 5/23/06, Craig Morrison <[EMAIL PROTECTED]> wrote:
[EMAIL PROTECTED] wrote:
> If you do not care to see the warnings, there is probably
> some way to turn them off in your compiler.
To the OP:
In VC7 and above.. Open the project settings for your project, navigate
to: Configuration
[EMAIL PROTECTED] wrote:
If you do not care to see the warnings, there is probably
some way to turn them off in your compiler.
To the OP:
In VC7 and above.. Open the project settings for your project, navigate
to: Configuration Properties -> C/C++ -> Disable Specific Warnings. Plug
in the
Thanks for your answer.
Actually, I tried to have resets there although I think that finalize is as
good in releasing the locks on tables. It didn't help.
I do it with prepare and step because this script is a demo of a bug I have
in my code, where I use all over a certain function that prepare
On Tue, May 23, 2006 at 08:50:56AM +0200, A. Pagaltzis wrote:
> * [EMAIL PROTECTED] <[EMAIL PROTECTED]> [2006-05-23 02:35]:
> > What you have to do is:
> >
> >SELECT qi, ri, drl, score
> > FROM ...
> > WHERE score=(SELECT max(score) FROM ...)
>
> Actually, in cases such as this, the
I asked a similar question to this about accessing columns using a
variable name, and the answer was you cannot. Sadly I cannot find the
email explaining why so I'll ask this similar question:
Can I add a column using a variable for the column name? Below is the
prepare statement, which
On 5/23/06, Ran <[EMAIL PROTECTED]> wrote:
Hi all,
rc = sqlite3_prepare(db1,// Database handle
"create table bla(a int,b int)",
-1, // Length of the statement
, // OUT: Statement handle
Hi all,
Could someone help me with the script below? I get an "SQL logic error or
missing database" and cannot find what I do wrong.
I use sqlite 3.3.4 on Linux.
What I do there is:
1. Open connection to a new database.
2. Create table bla in a transaction.
3. Open another connection to the
On 5/23/06, Anish Enos Mathew <[EMAIL PROTECTED]> wrote:
Hi Michael,
I am retrieving records from the data base randomly. I want
to perform 1,000,000 retrieval of 1,000,000 random records from the data
base. When I am retrieving 15 bytes records from the data base, it works
fine. It
Hi Michael,
I am retrieving records from the data base randomly. I want
to perform 1,000,000 retrieval of 1,000,000 random records from the data
base. When I am retrieving 15 bytes records from the data base, it works
fine. It just takes around 20 seconds for 1,000,000 retrievals. But
"Cory Nelson" <[EMAIL PROTECTED]> wrote:
>
> To get back on track: Brannon, submit a patch. An alarmingly high
> number of people believe those warnings are superfluous and from what
> I understand drh is one of them, so I imagine a patch is the only way
> proper casting will be put in.
>
* Brannon King <[EMAIL PROTECTED]> [2006-05-23 01:40]:
> It seems that I yet need help with another query. This one is just too slow.
> I've included the "explain" and the table schema. I've been using the
> prepare/step model directly. What should I change on my indexing to make it
> faster?
>
>
* [EMAIL PROTECTED] <[EMAIL PROTECTED]> [2006-05-23 02:35]:
> What you have to do is:
>
>SELECT qi, ri, drl, score
> FROM ...
> WHERE score=(SELECT max(score) FROM ...)
Actually, in cases such as this, the easiest approach is to use
`LIMIT`:
SELECT qi, ri, drl, score
FROM
On 5/23/06, Andrew Ward <[EMAIL PROTECTED]> wrote:
Hi,
How can I perform an insert that contains linefeeds, such as
INSERT INTO X VALUES (123, 'a'||chr(10)||'b');
You can insert it as a hex string:
INSERT INTO t VALUES (123, 'a' || x'0A' || 'b');
--
Nemanja Corlija <[EMAIL PROTECTED]>
54 matches
Mail list logo
