[sqlite] UNIQUE index not working as expected - what am I overlooking?

Gidday guys I'm working with SQLite in Flash. I have this unique index: CREATE UNIQUE INDEX songsIndex ON songs ( DiscID, Artist, Title ) I have a parametised recursive functionset up to insert any new rows (single or multiple). It works fine if I try to insert a row with the same DiscID,

Re: [sqlite] SQLITE_ERROR (SQL logic error or missing database) onWinRT/ WP8 when querying large (!) result sets using 3.8.4.1

No indices for PRD_DETAILS table. This table does not contain any blobs. The blob issue mentioned is happening with another table in another application. The reason it was mentioned because we have the feeling that whenever resulting data reaches to a certain size, SQLITE_ERROR is reported on

Re: [sqlite] SQLITE_ERROR (SQL logic error or missing database) onWinRT/ WP8 when querying large (!) result sets using 3.8.4.1

On Wed, Mar 26, 2014 at 10:26 PM, Richard Hipp wrote: > > > > On Wed, Mar 26, 2014 at 10:21 PM, Muharrem Bilgin (Bright Software) < > mbil...@brightsoft.com.au> wrote: > >> The table in question is created by using the following statement. >> >> CREATE TABLE PRD_DETAILS

Re: [sqlite] SQLITE_ERROR (SQL logic error or missing database) onWinRT/ WP8 when querying large (!) result sets using 3.8.4.1

On Wed, Mar 26, 2014 at 10:21 PM, Muharrem Bilgin (Bright Software) < mbil...@brightsoft.com.au> wrote: > The table in question is created by using the following statement. > > CREATE TABLE PRD_DETAILS (PRD_CODE TEXT COLLATE NOCASE NOT NULL, TERID > INTEGER NOT NULL, SEQ INTEGER, PRD_DESC TEXT

Re: [sqlite] SQLITE_ERROR (SQL logic error or missing database) onWinRT/ WP8 when querying large (!) result sets using 3.8.4.1

The table in question is created by using the following statement. CREATE TABLE PRD_DETAILS (PRD_CODE TEXT COLLATE NOCASE NOT NULL, TERID INTEGER NOT NULL, SEQ INTEGER, PRD_DESC TEXT COLLATE NOCASE, FRANCHISE_CODE INTEGER, PRD_STATUS TEXT COLLATE NOCASE, PRD_CLASS TEXT COLLATE NOCASE, TESTER TEXT

Re: [sqlite] Primary key used in compound index

On 2014/03/27 03:37, Pavel Vazharov wrote: Hi guys, I tried the thing that you proposed. The EXPLAIN QUERY PLAN showed selectid order fromdetail 0 0 0 SEARCH TABLE event AS e USING INDEX IDX_event_1 (deleted=? AND major=? AND rowid>?) as you expected. But the

Re: [sqlite] SQLITE_ERROR (SQL logic error or missing database) onWinRT/ WP8 when querying large (!) result sets using 3.8.4.1

On Wed, Mar 26, 2014 at 9:47 PM, Muharrem Bilgin (Bright Software) < mbil...@brightsoft.com.au> wrote: > Hi Joe, > > The following are the log entries, which confirm your suspicion (the log > entry format : time, error description, error code). > > As suggested, we are already setting the temp

Re: [sqlite] SQLITE_ERROR (SQL logic error or missing database) onWinRT/ WP8 when querying large (!) result sets using 3.8.4.1

Hi Joe, The following are the log entries, which confirm your suspicion (the log entry format : time, error description, error code). As suggested, we are already setting the temp store directory as soon as a database connection is opened (after sqlite3_open16). Now, the question is when to use

Re: [sqlite] Primary key used in compound index

Hi guys, I tried the thing that you proposed. The EXPLAIN QUERY PLAN showed selectid order fromdetail 0 0 0 SEARCH TABLE event AS e USING INDEX IDX_event_1 (deleted=? AND major=? AND rowid>?) as you expected. But the timing is still as in the old verions (two

Re: [sqlite] Primary key used in compound index

On Wed, Mar 26, 2014 at 7:12 PM, RSmith wrote: > > Double quotes are not part of the standard, just allowed by most systems > in lieu of single quotes, but you are correct about back-quotes not being > part of the standard either, but they will be understood by most SQL >

Re: [sqlite] Primary key used in compound index

On 2014/03/27 02:50, Scott Robison wrote: Double quotes are part of the SQL-92 standard. I'm pretty sure they are, anyway. They are used (along with other database specific means) to delimit identifiers that would otherwise be illegal. As for using backtick to delimit identifiers, it is not

Re: [sqlite] Primary key used in compound index

On Wed, Mar 26, 2014 at 6:12 PM, RSmith wrote: > > Also, why all the Double-quotes around your column names in the create > statement? I'm sure it will work but it isn't the right SQL compatible > method, either use SQL backquotes or no quotes. Single and double quotes >

Re: [sqlite] Primary key used in compound index

On 2014/03/27 02:30, Richard Hipp wrote: On Wed, Mar 26, 2014 at 8:12 PM, RSmith > wrote: In your case it does so using SQLite version 3.8.2 while you have the other app you are making write to the same DB using SQLite 3.6.17... I'm

Re: [sqlite] SQLITE_ERROR (SQL logic error or missing database) onWinRT/ WP8 when querying large (!) result sets using 3.8.4.1

Muharrem Bilgin wrote: > > Thank you for your input. We are not using the SQLite wrapper. The sqlite > source code is compiled into a Windows Store/Phone DLL and used in our > application. > That's fine, setting the temporary directory is always required on WinRT (and Windows Phone 8). > > I

Re: [sqlite] SQLITE_ERROR (SQL logic error or missing database) on WinRT/ WP8 when querying large (!) result sets using 3.8.4.1

Hi Joe, Thank you for your input. We are not using the SQLite wrapper. The sqlite source code is compiled into a Windows Store/Phone DLL and used in our application. I have tried your suggestion anyway, but no result. MB. -Original Message- From: sqlite-users-boun...@sqlite.org

Re: [sqlite] Primary key used in compound index

On Wed, Mar 26, 2014 at 8:12 PM, RSmith wrote: > In your case it does so using SQLite version 3.8.2 while you have the > other app you are making write to the same DB using SQLite 3.6.17... I'm > surprised you have not corrupted the DB yet, > It is possible to create a

Re: [sqlite] SQLITE_ERROR (SQL logic error or missing database) on WinRT/ WP8 when querying large (!) result sets using 3.8.4.1

Muharrem Bilgin wrote: > > We are trying to query a table with 7011 records (simply first calling > sqlite3_prepare16_v2 and then sqlite3_step, nothing complicated that is). It > appears sqlite3_step returns SQLITE_ERROR if result sets contain records > more than a certain size. The very 3.8.4.1

[sqlite] SQLITE_ERROR (SQL logic error or missing database) on Win RT/ WP8 when querying large (!) result sets using 3.8.4.1

Hello, We are trying to query a table with 7011 records (simply first calling sqlite3_prepare16_v2 and then sqlite3_step, nothing complicated that is). It appears sqlite3_step returns SQLITE_ERROR if result sets contain records more than a certain size. The very 3.8.4.1 version on Windows

Re: [sqlite] Primary key used in compound index

Hi Pavel, You wrote: *** A) What version of SQLite are you using ? - We are using SQLite version 3.6.17 - We are using SQLite through the C++ SOCI library - 3.1.0 and then: Also,

Re: [sqlite] Primary key used in compound index

Hi guys, I tried the proposed thing to change the index to this: CREATE INDEX IDX_event_1 ON event (deleted, major); Now EXPLAIN QUERY PLAN returns: explain query plan select e.type, e.id, e.rsub_id, e.person_id, e.timestamp, e.file_id, e.previous_name, e.previous_folder_id, e.transparent,

Re: [sqlite] Primary key used in compound index

Hi, *** A) What version of SQLite are you using ? - We are using SQLite version 3.6.17 - We are using SQLite through the C++ SOCI library - 3.1.0

Re: [sqlite] Primary key used in compound index

Simon Slavin wrote: > On 26 Mar 2014, at 1:20pm, Clemens Ladisch wrote: >> In this case, it appears that listing the rowid explicitly confuses >> SQLite somehow. > > That would be a bug, then ? > > Well, not a bug leading to incorrect results, but certainly one that slows >

Re: [sqlite] Primary key used in compound index

The assertion that AUTOINCREMENT has no effect (here and in another post) is incorrect.  Without it, SQLite MAY reuse a key from deleted rows; with it, this will not happen. Sent from Samsung Galaxy Note Original message From: Simon Slavin Date:

Re: [sqlite] Primary key used in compound index

On 26 Mar 2014, at 1:20pm, Clemens Ladisch wrote: > In this case, it appears that listing the rowid explicitly confuses > SQLite somehow. That would be a bug, then ? Well, not a bug leading to incorrect results, but certainly one that slows down searches. Simon.

Re: [sqlite] Primary key used in compound index

On Tue, 25 Mar 2014 20:19:40 + Pavel Vazharov wrote: > Hi guys, > > > I've the following scenario (I'm presenting a simplified version of > our scenario, but the main problem remains the same as in this > scenario): > > > CREATE TABLE test_event > ( > "id" INTEGER

Re: [sqlite] Primary key used in compound index

Pavel Vazharov wrote: > CREATE TABLE test_event > ( > "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, > ... > ); > CREATE INDEX IDX_test_event_1 ON test_event (deleted, major, id); > > explain query plan select * from test_event where deleted = 0 and major = 1 > and id > 5 order by id asc limit

Re: [sqlite] Primary key used in compound index

On 25 Mar 2014, at 8:19pm, Pavel Vazharov wrote: > "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, Can you try this as exactly the following: "id" INTEGER PRIMARY KEY, leaving everything else out. I assure you that it will obey the 'NOT NULL' and 'AUTOINCREMENT'

[sqlite] SQLiteParameter and VS Form Designer generated code problem

Hello, I have a problem when using SQLiteCommand in VC# Express. If I add some parameters to the command, the Form Designer automatically adds this lines: sqLiteParameter1.Precision = ((byte)(0)); sqLiteParameter1.Scale = ((byte)(0)); but this generates errors because SQLiteParameter doesn't

[sqlite] Primary key used in compound index

Hi guys, I've the following scenario (I'm presenting a simplified version of our scenario, but the main problem remains the same as in this scenario): CREATE TABLE test_event ( "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, "type" INTEGER NOT NULL, "some_integer_data" INTEGER NOT NULL,

Re: [sqlite] Noticed crash on windows

On 2014-03-25 18:52, Larry Brasfield wrote: > Going in, it is best to not read too much into your code running > "pretty well" on a Unix platform. Have you run your code with > Valgrind (or equivalent) on that platform? If not, doing so may help > you uncover a bug which affects behavior more