[sqlite] synchronous issue

Hi, In order to reduce the operation time, I set synchronous as OFF and journal_mode as MEMORY by PRAGMA command. However I found that the data may be lost after inserting a BLOB data, even when I use a commit action. I think the reason is synchronous is off. How can I know that there is some

Re: [sqlite] synchronous issue

On 9 Aug 2010, at 7:13am, Lei, Rick (GE EntSol, SensInsp) wrote: > In order to reduce the operation time, I set synchronous as OFF and > journal_mode as MEMORY by PRAGMA command. However I found that the data > may be lost after inserting a BLOB data, even when I use a commit > action. Yes,

Re: [sqlite] synchronous issue

Hi, Simon, Yes, I agree that a journal file in memory will lead some risk when I set synchronous as off. However the speed is a much critical requirement for me. I have tried to close and reopen the file. However the time to open a file and prepare the first query need more time. Maybe I need

Re: [sqlite] Slowdown when adding terms to query

Thank you all for your responses, Igor - using a UNION made a huge difference, thank you. I have a quick query on from the suggestion of adding indices for anything that appears either side of an equals sign. I have many user-selectable terms that can be added to the query. Is it worth

[sqlite] typo fixing patch for http://www.sqlite.org/version3.html

Greetings! I hope you'll find this little patch useful. I'm not sure whether this webpage is auto-generated. In that case the patch should still be useful to spot the typos. Thanks for this great SQL database engine! -- Adrian Aichner mailto:adrian.aich...@gmail.com

Re: [sqlite] typo fixing patch for http://www.sqlite.org/version3.html

On 8 Aug 2010, at 5:00pm, Adrian Aichner wrote: > I hope you'll find this little patch useful. Attachments are stripped from messages to this list. Please describe what your patch does in your message, and post the URL where interested people can download it. Simon.

Re: [sqlite] typo fixing patch for http://www.sqlite.org/version3.html

On Mon, Aug 9, 2010 at 7:23 AM, Simon Slavin wrote: > > On 8 Aug 2010, at 5:00pm, Adrian Aichner wrote: > > > I hope you'll find this little patch useful. > > Attachments are stripped from messages to this list. Please describe what > your patch does in your message, and

Re: [sqlite] typo fixing patch for http://www.sqlite.org/version3.html

On 9 Aug 2010, at 12:37pm, Richard Hipp wrote: > On Mon, Aug 9, 2010 at 7:23 AM, Simon Slavin wrote: > >> On 8 Aug 2010, at 5:00pm, Adrian Aichner wrote: >> >>> I hope you'll find this little patch useful. >> >> Attachments are stripped from messages to this list.

Re: [sqlite] Substring (LIKE "%key%") searches, would FTS3 with suffix-tree tokenizer be the fast way?

First, permit me a little rant. As a user, I dislike this kind of incremental search feature if there's no easy way to toggle it or to configure it and the list of items will be large enough to cause a typing lag. The feature can become an intrusive nuisance, the opposite of what is intended.

Re: [sqlite] Substring (LIKE "%key%") searches, would FTS3 with suffix-tree tokenizer be the fast way?

Sounds to me like Boyer-Moore is needed http://en.wikipedia.org/wiki/Boyer%E2%80%93Moore_string_search_algorithm And...I would probably pre-load the database table into 26 seperate memory tables to avoid any SQL interactivity at all other than the initial loading. Adding the SQL layer slows

[sqlite] Mistake in documentation and question

In page http://www.sqlite.org/datatype3.html under section 3.2 Affinity Of Comparison Operands 2nd bullet point should read: An expression "CAST(expr AS type)" instead of "CAST(expr TO type)" In addition a quick question. Is there a way to perform a division of two columns (real type)

[sqlite] foreign key error 01

Hello environment --- OS : Win XP (every patch) sqlite : 3.7.0.1 reproduction CREATE TABLE staff_01( idINTEGER PRIMARY KEY AUTOINCREMENT, id_staff_editor

Re: [sqlite] foreign key error 01

Oliver Peters writes: sorry, my FK-clause was wrong (forgot the _01 & 0_2) my mistake (but the next thread will show the true error) Oliver ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] foreign key error 01

Instead of REFERENCES staff(id) Try REFERENCES staff_01(id) That spelling thing will get you every time...:-) Michael D. Black Senior Scientist Advanced Analytics Directorate Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org on

Re: [sqlite] foreign key error 01

Oliver Peters wrote: > reproduction > > > CREATE TABLE staff_01( > idINTEGER PRIMARY KEY AUTOINCREMENT, > id_staff_editor INTEGER NOT NULL, > code CHAR(2) NOT NULL, >

Re: [sqlite] Round was Mistake in documentation and question

> In addition a quick question. > Is there a way to perform a division of two columns (real > type) and force the result to be presented/rounded in 2 > decimal places ? > ROUND should do the trick ;-) http://www.sqlite.org/lang_corefunc.html#round Please note http://www.sqlite.org/faq.html#q16

[sqlite] foreign key the true error - sqlite 3.7.0.1

This sql code works in the two cases but AFAIK ;-) it shouldn't in the second Am I right? CREATE TABLE staff_01( idINTEGER PRIMARY KEY AUTOINCREMENT, id_staff_editor INTEGER NOT NULL,

Re: [sqlite] foreign key the true error - sqlite 3.7.0.1

and the added , after UNIQUE(code) doesn't change anything (oh what a day ;-) ) Oliver ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] foreign key the true error - sqlite 3.7.0.1

Oliver Peters wrote: > This sql code works in the two cases but AFAIK ;-) it shouldn't in the second > > PRAGMA foreign_keys = ON; > > CREATE TABLE staff_02( > idINTEGER PRIMARY KEY AUTOINCREMENT, > id_staff_editor INTEGER

Re: [sqlite] foreign key the true error - sqlite 3.7.0.1

Igor Tandetnik writes: > > Oliver Peters wrote: > > This sql code works in the two cases but AFAIK it shouldn't in the second > > > > PRAGMA foreign_keys = ON; > > > > CREATE TABLE staff_02( > > idINTEGER PRIMARY KEY

Re: [sqlite] foreign key the true error - sqlite 3.7.0.1

>From http://www.sqlite.org/foreignkeys.html Although I believe I compiled my shell WITHOUT these defined but I still don't get enforcement of the foreign key constraint. It appears that foreign keys are fully constrained by default. Is there any way in the shell to find out if it's

Re: [sqlite] foreign key the true error - sqlite 3.7.0.1

On 9 August 2010 14:56, Black, Michael (IS) wrote: > From http://www.sqlite.org/foreignkeys.html > > > > Although I believe I compiled my shell WITHOUT these defined but I still > don't get enforcement of the foreign key constraint. > > It appears that foreign keys are

Re: [sqlite] foreign key the true error - sqlite 3.7.0.1

I use the standard windows binary from http://www.sqlite.org/sqlite-3_7_0_1.zip and if I do the recommended test I get >sqlite3 SQLite version 3.7.0.1 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> pragma foreign_keys; 0 sqlite> pragma foreign_keys=ON; sqlite>

Re: [sqlite] foreign key the true error - sqlite 3.7.0.1

I found the (apparent) problem. This works and throws the constraint violation: PRAGMA foreign_keys=OFF; CREATE TABLE staff_01( id INTEGER PRIMARY KEY AUTOINCREMENT, id_staff_editor INTEGER NOT NULL, code CHAR(2) NOT NULL, FOREIGN KEY(id_staff_editor) REFERENCES staff(id) ); INSERT INTO

Re: [sqlite] Mistake in documentation and question

On Mon, Aug 9, 2010 at 8:38 AM, Ioannis Epaminonda wrote: > > In page http://www.sqlite.org/datatype3.html > under section 3.2 Affinity Of Comparison Operands 2nd bullet point should > read: > An expression "CAST(expr AS type)" instead of "CAST(expr TO > type)" > >

Re: [sqlite] foreign key the true error - sqlite 3.7.0.1

[...] To my mind the simplified question is: why returns the "INSERT INTO a" not an error while the "INSERT INTO b" does? How corresponds this behaviour to the concept of FOREIGN KEYS? I wrote the code into file.sql (encoding=utf8, if this matters) and started sqlite3 -bail test.db3 <

Re: [sqlite] foreign key the true error - sqlite 3.7.0.1

> why returns the "INSERT INTO a" not an error while the "INSERT INTO > b" does? How > corresponds this behaviour to the concept of FOREIGN KEYS? > CREATE TABLE a( >idINTEGER PRIMARY > KEY > AUTOINCREMENT, >id_staff_editor

Re: [sqlite] foreign key the true error - sqlite 3.7.0.1

Am 09.08.2010 16:57, schrieb Oliver Peters: > [...] > > To my mind the simplified question is: > > why returns the "INSERT INTO a" not an error while the "INSERT INTO b" does? > How > corresponds this behaviour to the concept of FOREIGN KEYS? > > I wrote the code into file.sql (encoding=utf8,

Re: [sqlite] foreign key the true error - sqlite 3.7.0.1

Dan Kennedy writes: > > > why returns the "INSERT INTO a" not an error while the "INSERT INTO > > b" does? How > > corresponds this behaviour to the concept of FOREIGN KEYS? > > > CREATE TABLE a( > >idINTEGER PRIMARY > >

[sqlite] commit transaction/savepoints on program crash

Hello all, I'm new to the list and had a question. I know the default behavior for savepoints (or any transactions) is that if they have not been committed, if the program crashes, they are lost. Is there any way to have them committed by default? Basically I *only* want the transaction rolled

Re: [sqlite] commit transaction/savepoints on program crash

> Is there any way to have them committed by > default? Basically I *only* want the transaction rolled back in case of an > explicit rollback statement, not due to program crash/power failure, etc. Does > anyone know of a way of doing this? You can avoid transaction begin/commit statements, so

Re: [sqlite] commit transaction/savepoints on program crash

On 9 Aug 2010, at 5:05pm, Josh wrote: > I'm new to the list and had a question. I know the default behavior for > savepoints (or any transactions) is that if they have not been committed, if > the program crashes, they are lost. Is there any way to have them committed > by > default?

Re: [sqlite] foreign key the true error - sqlite 3.7.0.1

Oliver Peters wrote: > Please believe me I really try hard to understand - but what I don't > understand > is the fact that you can insert in this case: > > > PRAGMA foreign_keys = ON; > > /* > **success INSERT = yes > */ > > CREATE TABLE a( >id

Re: [sqlite] foreign key the true error - sqlite 3.7.0.1

Igor Tandetnik writes: [...] > > Why would you want to fail statements that end up not violating any > constraints? The idea is to prevent that somebody can change the table staff that is not already in the table. Therefore I create the first user by not switching on foreign

Re: [sqlite] commit transaction/savepoints on program crash

Thanks for the answers, but I guess I should have made my question more clear. I knew that you can commit every sql statement individually, then the question would be, how can I roll them back? In other words I'd like something like savepoint and rollback to savepoint, while not loosing the

Re: [sqlite] commit transaction/savepoints on program crash

[New text /below/ any text you want to quote, please.] On 9 Aug 2010, at 9:09pm, Josh wrote: > Thanks for the answers, but I guess I should have made my question more > clear. I knew that you can commit every sql statement individually, then > the question would be, how can I roll them back? >

[sqlite] Incremental row number associated to the current Query?

Is there a way to create an incremental row or show a row number that is linked to the current select query that can be used in calculations? Basically a row counter for the current query that can be used in calculations. Notes: 1. The query can be change at any time. 2. The application is

Re: [sqlite] Incremental row number associated to the current Query?

Mike Henshaw wrote: > Is there a way to create an incremental row or show a row number that is > linked to the current select query that can be used in > calculations? > > Basically a row counter for the current query that can be used in > calculations. Your

Re: [sqlite] Incremental row number associated to the current Query?

>> Is there a way to create an incremental row or show a row number that is >> linked to the current select query that can be used in >> calculations? > >> Basically a row counter for the current query that can be used in >> calculations. >Your application makes a series of sqlite3_step

Re: [sqlite] Incremental row number associated to the current Query?

Mike Henshaw wrote: > 1. The application is from a third party with no access to the source code > but the SQL query can be updated since the SQL query > is used in a custom HTML template which can also be updated. > 2. The calculations to the RowNum would then

Re: [sqlite] typo fixing patch for http://www.sqlite.org/version3.html

Here is the little patch verbatim. Regards, Adrian diff -u c:\Hacking\sqlite3\version3.html.~1~ c:\Hacking\sqlite3\version3.html --- c:\Hacking\sqlite3\version3.html.~1~ 2010-08-08 17:54:51.12500 +0200 +++ c:\Hacking\sqlite3\version3.html 2010-08-08 17:54:51.140625000 +0200 @@ -235,8 +235,8

[sqlite] SQLite bug report: Bad truncation in column mode with non ASCII characters

In ".mode column", the function used to compute the length of the string to print does not account for multi-byte characters, resulting in a shorter string, and all remaining fields in the line shifted to the left. Example: sqlite> select * from test limit 3; namename2 name3

Re: [sqlite] Process memory space exhausted in 3.7.0

As it turns out, I can reproduce the failure using a single huge insert. The code that I'm including below compiles under bcc32 from Embarcadero's C++ Builder 2007 and cl from vs2005. Since it's more likely that people have MS compilers available, the compilation line having this source file and