[sqlite] sqlite3_wal_checkpoint_v2() returning SQLITE_LOCKED: in what circumstances?

I think you may mean "database connection" instead of "statement handle". When you are finished with processing a statement, you should either reset (if you intead to use it again later) or finalize it. Otherwise SQLite must assume that you want to continue later and needs to keep around

[sqlite] Can SQLite know from the statement string if it is row producing or not?

You may also like to consider sqlite3_stmt_readonly() which will return TRUE for all statments that do not (directly) modify the db contents -Urspr?ngliche Nachricht- Von: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-bounces at mailinglists.sqlite.org] Im Auftrag

[sqlite] Missing documentation about BLOB encoding conversions

What is the exact sequence of calls? If you call sqlite3_column_text() on a blob value, the new type will be text and a subsequent call to sqlite_column_text16() must by definition perform transcoding. -Urspr?ngliche Nachricht- Von: sqlite-users-bounces at mailinglists.sqlite.org

[sqlite] bug when columns are missing in embedded subselect

This has been discussed several times on the list. SQLite (and all other databases) try very hard to resolve the names you refer to in your query and will search all the tables you mention to find *unqualified* references. They give up if they do not find exactly one definition. Try " delete

[sqlite] 回复： Why SQLITE_BUSY?

It looks like you have unfinalized statements in your transaction. You are preparing statements inside the loop, but finalizing only 1 (the last) statement. And attempting to commit even before finalizing only the last statement. So sqlite3_close() is complaining about improper call sequence,

[sqlite] sqlite3_free needed when calling sqlite3_result_text ?

omes from and how to deal with it. As you already stated, a local variable in your callback procedure goes out of scope automatically. I have no idea how VB6 implements local variables; in C they are located on the stack, which may be overwritten by other function calls. On Tue, Dec 15, 2015 a

[sqlite] sqlite3_free needed when calling sqlite3_result_text ?

The rules are quite simple: If the pointer refers to static memory (preallocated string constants, global variables that you can guarantee won't change while SQLite uses them) use SQLITE_STATIC If the pointer refers to memory obtained from sqlite3_malloc (directly or indirectly e.g. via

[sqlite] Is rowid the fastest?

Does it revert back to slower speed after dropping the index? Can you compare the EXPLAIN output produced with and without the index? There is no difference on my machine (Version 3.7.14); if yours behaves the same way then whatever changes speed is definitely not because SQLite is doing

[sqlite] Is rowid the fastest?

You are probably falling into the cache effect trap again. There is no point in indexing on the primary key, it only wastes space and CPU cycles -Urspr?ngliche Nachricht- Von: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-bounces at mailinglists.sqlite.org] Im

[sqlite] Bug with DATETIME('localtime')

>... >We need a metric calendar. I propose redefining the second so that a day is >100,000 seconds long... ;) > >-- >Scott Robison And while we are already redefining the fundamental constants of measuring, we could redefine the meter to be exactly three feet and the kilogram to be exactly two

[sqlite] ABOUT ROWID

1) disregard the results of the first query timing (this one has to read the data into the cache) 2) run each query in a new process (so each one will need to read the data from disk) -Urspr?ngliche Nachricht- Von: sqlite-users-bounces at mailinglists.sqlite.org

[sqlite] ABOUT ROWID

There is always an implicit index on the SQLite rowid and this is the fastest method to locate a row. The next best thing for retrieval speed is an index that matches the where clause. If you do not have one, SQLite may decide to create a temporary index anyway, but this depends on the query.

[sqlite] Create table while a dbdatareader is open

7, 2015, at 3:30 AM, Hick Gunter wrote: > > Maybe you are looking for the INSERT INTO ... SELECT or CREATE TABLE ... AS > SELECT syntax? This would be one write transaction instead of two separate, > incompatible transactions. > > -Urspr?ngliche Nachricht--

[sqlite] Create table while a dbdatareader is open

Maybe you are looking for the INSERT INTO ... SELECT or CREATE TABLE ... AS SELECT syntax? This would be one write transaction instead of two separate, incompatible transactions. -Urspr?ngliche Nachricht- Von: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-bounces

[sqlite] Exporting

You can always write a virtual table for exporting to whatever flavor of CSV you like. Mine exports/imports strings/blobs containing nonprintable characters in x'' notation. Basically it implements: For export: CREATE VIRTUAL TABLE _csv_exp USING CSV (,); -> create a CSV table with the

[sqlite] [Sqlite3] segfault in sqlite3_step()

You give no indication of the schema you are using or the statement that went wrong. If you can reproduce the error using the sqlite3 shell, then it is probably within sqlite3. If not, then it is most probably your own program which is causing the fault, maybe by passing incorrect (stale or

[sqlite] Information passing between xBestIndex and xFilter

It is up to your xBestIndex method to confern this information to your xFilter method, e.g. by setting the idxStr return parameter in a way these methods understand (e.g. leave it pointing to a character that encodes the required comparison). The idxStr is passed to xFilter unchanged from what

[sqlite] ***suspected spam*** Re: Simple Math Question

>> Another good good way to think of IEEE I was presented once with, and >> which kind of gave me a ah-ah moment, is the fact that numbers with >> exact representation fall on the nodes of grid, and there's plenty of >> "space" in between the nodes for values which cannot be exactly >>

[sqlite] ***suspected spam*** Re: Simple Math Question

>17 -> 0x10001 mantissa >-1 -> 0xf exponent ( or however many bits the exponent is represented by >exact Still thinking in decimal... Value = mantissa * 2 ^ exponent And not Vale = mantissa * 10 ^ exponent ___ Gunter Hick Software Engineer

[sqlite] Problem with sqlite3_create_function

>> Can you reproduce the problem using the sqlite shell? >This won't be easy as the UDF is in an ActiveX dll, not in sqlite3.dlll > >This is the output from explain, run on this SQL: >SELECT XXX(F1, F2) as A, XXX(F1, F2) as B FROM UDF_TEST limit 3 XXX will just >add the results of the integer

[sqlite] Problem with sqlite3_create_function

not be > > a > "statement handle". > > Yes, you are right there. Not sure now how I thought it was a > statement handle, but this doesn't alter the problem. > Thanks in any case for correcting this mis-conception. > > RBS > > On Mon, Oct 19, 2015 at 1:40 PM,

[sqlite] Problem with sqlite3_create_function

The "context" passed to a user defined function is not and cannot be a "statement handle". How would an aggregate function tell the difference between Select SUM(x) ... and Select SUM(x), SUM(y) ... if not by virtue of sqlite3_aggregate_context() returning different adresses?

[sqlite] Order with another sequence

It should be possible with a custom collation sequence. -Urspr?ngliche Nachricht- Von: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-bounces at mailinglists.sqlite.org] Im Auftrag von Cecil Westerhof Gesendet: Freitag, 09. Oktober 2015 12:08 An: sqlite-users at

[sqlite] [sqlite-dev] SQLite version 3.8.12 enters testing

I expect users running SQLite on embedded devices would be thrilled... -Urspr?ngliche Nachricht- Von: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-bounces at mailinglists.sqlite.org] Im Auftrag von Filip Navara Gesendet: Donnerstag, 08. Oktober 2015 15:55 An:

[sqlite] Strange behaviour of IN/NOT IN subqueries?

Indeed you did reproduce in that you selected all of the 1 entries you inserted into table item and inadvertently used the field name from the table in the subquery in the NOT IN case ;) A subquery "select itemcode from tmp_salesitm", is allowed to refer to fields from the outer "select * from

[sqlite] Multiple connections to in-memory DB and virtual tables

AFAICT the FROM clause is superflous, as the function has no (supported) way of detecting which table(s) the FROM clause contains. What is your "reindex_virtual_table()" function (I assume it is a user implemented function) supposed to do? -Urspr?ngliche Nachricht- Von: Eduardo Morras

[sqlite] Multiple connections to in-memory DB and virtual tables

1) Each connection needs to load the image(s) that contain(s) the virtual module code (unless you have already linked it into a single image). 2) Each connection needs to declare the virtual module(s) by calling sqlite3_create_module(_v2). 3) Each connection needs to declare the tables using

[sqlite] Problem sqlite

I suspect you are having a chracter encoding problem. SQLite supports UTF encoding. -Urspr?ngliche Nachricht- Von: Jonathan [mailto:jonathanmejiaa at hotmail.com] Gesendet: Dienstag, 06. Oktober 2015 15:27 An: sqlite-users at mailinglists.sqlite.org Betreff: [sqlite] Problem sqlite >

[sqlite] Multiple connections to in-memory DB and virtual tables

We are using multiple processes accessing the same on-disk db with almost exclusively virtual tables. Once the tables have been declared (CREATE VIRTUAL TABLE...), all other connections need only to load the modules. The xConnect method gets called on first access. -Urspr?ngliche

[sqlite] Is it legal SQL to select a specific row of a group via a HAVING clause?

The main difference between HAVING and WHERE ist that WHERE operates on the input set and HAVING operates on the output set. If your condition requires computing an aggregate, then HAVING is a viable method of avoiding a subquery that needs to repeatedly scan the input table: SELECT

[sqlite] why I don't get an error ?

Nope. The reason to define datatype in SQLite is because other databases do so. The difference ist hat SQLite does not enforce typing but uses the concept of affinity instead. -Urspr?ngliche Nachricht- Von: Nicolas J?ger [mailto:jagernicolas at legtux.org] Gesendet: Donnerstag, 27.

[sqlite] explain plan change between SQLite 3.8.3.1 and 3.8.11.1

But you have to run the query as opposed to just parsing EXPLAIN -Urspr?ngliche Nachricht- Von: Richard Hipp [mailto:drh at sqlite.org] Gesendet: Donnerstag, 27. August 2015 15:37 An: General Discussion of SQLite Database Betreff: Re: [sqlite] explain plan change between SQLite 3.8.3.1

[sqlite] design problem involving trigger

Are you looking for "NOT NULL DEFAULT 0"? -Urspr?ngliche Nachricht- Von: Will Parsons [mailto:varro at nodomain.invalid] Gesendet: Freitag, 21. August 2015 04:47 An: sqlite-users at mailinglists.sqlite.org Betreff: [sqlite] design problem involving trigger I'm working on a program that

[sqlite] Strange behaviour of sqlite3_stmt_busy

SQLITE_DONE means that there are no (more) rows to be retrieved. -Urspr?ngliche Nachricht- Von: Stephan Beal [mailto:sgbeal at googlemail.com] Gesendet: Freitag, 31. Juli 2015 10:12 An: General Discussion of SQLite Database Betreff: Re: [sqlite] Strange behaviour of sqlite3_stmt_busy On

[sqlite] ATTACH Problem

Not quite. Try printing the generated statement. It should read ATTACH myfilepath AS UPD; Intead of ATTACH 'myfilepath' AS UPD; -Urspr?ngliche Nachricht- Von: Chris Parsonson [mailto:z2668856 at gmail.com] Gesendet: Freitag, 31. Juli 2015 09:10 An: General Discussion of SQLite

[sqlite] datetime start of day result

You are converting the "start of day" (UTC) to "localtime". Your timezone is obviously 4 hours behind UTC... asql> SELECT datetime('now'), datetime('now','localtime'),datetime('now','start of day'),datetime('now','localtime','start of day'),datetime('now','start of day','localtime');

[sqlite] changed time zone

AFAICT the windows implementation of localtime() will honour the settings of the environment variables TZ, _timezone, _daylight and _tzname. The environment variables of a process are set (copied from the parent process) on process creation. Subsequent changes in the parent process are NOT

[sqlite] create temporary virtual table

Caveat: SQLite may call the xDisconnect method at other times too. I expect this will happen if the schema changes while a statement is prepared. -Urspr?ngliche Nachricht- Von: Peter Aronson [mailto:pbaronson at att.net] Gesendet: Dienstag, 21. Juli 2015 01:20 An: General Discussion of

[sqlite] Unable to find the file 'memleak.txt'

Try "locate memleak" if the file is not present in the working directory of the process running the test. -Urspr?ngliche Nachricht- Von: Sairam Gaddam [mailto:gaddamsairam at gmail.com] Gesendet: Freitag, 17. Juli 2015 08:44 An: General Discussion of SQLite Database Betreff: [sqlite]

[sqlite] best way to have a constraint over 2 fields

A UNIQUE constraint is implemented in terms of a UNIQUE index anyway. The difference is that you can DROP INDEX but cannot DROP CONSTRAINT. -Urspr?ngliche Nachricht- Von: Michele Pradella [mailto:michele.pradella at selea.com] Gesendet: Freitag, 17. Juli 2015 08:14 An: sqlite-users at

[sqlite] Schema-less JSON SQLite DB?

The record sort order is: NULLs Numeric by value Text by collating function Blob by memcmp order So your result set will contain all rows having a FooColumn with numeric value greater than 50, a text value or a blob value. -Urspr?ngliche Nachricht- Von: Hayden Livingston

[sqlite] Suggestions for Fast Set Logic?

Consider creating a bitmapped index on the fields you expect to be queried the most. Bitmaps may be stored and manipulated (combined by logical operations) very efficiently as long as the field values conform to certain criteria (usually "managable cardinality of distinct values"). Bitmap

[sqlite] Question about getting size of stored data

AFAIKT you are attempting to determine the "size" of one row by the difference in the file size. This must fail, because SQLite allocates and writes the database file in units of "database pages". Does your definition of "size" include the index entries pertaining to a row? Does it include the

[sqlite] Contstant WHERE terms still require table scan?

Database Betreff: Re: [sqlite] Contstant WHERE terms still require table scan? On Fri Jun 12, 2015 at 09:49:29AM +, Hick Gunter wrote: > Seems the correct code is already generated... Thanks Hick, that shows a bit more detail I didn't think to look for. It seems that this only works for b

[sqlite] Contstant WHERE terms still require table scan?

Seems the correct code is already generated... asql> explain select rowid from x where 1=?; addr opcode p1p2p3p4 p5 comment - - -- - 0 Trace 0 0 000 NULL 1

[sqlite] confusing error msgs

You are creating each table in a separate file; a foreign key may only reference a table in the same file. Your type declarations are faulty in that you are omitting an opening parenthesis in a DECIMAL 4,3) declaration. SQLite does not constrain sizes, a TEXT(10) or a CHAR(1) variable my

[sqlite] implicit vs explicit joins

An INNER join (signified by the join operators "," (comma), JOIN or INNER JOIN) is a very different beast than an OUTER join (signified by the join operators LEFT JOIN or LEFT OUTER JOIN). The first returns that subset of the cartesian product of the two tables where the join condition is met.

[sqlite] static sqlite database

Apart from the fact that latitude and longitude have defined ranges +-90 and +-180 respectively, why are you choosing SQLite as the storage format for densely populated single table of constant if all you want to do is read the values? If you are looking for a minimum space, uncompressed

[sqlite] DROP statement on Virtual Tables

Which method returns an error for a table that is missing it's backing store determines what can be done. xBestIndex: prevents SQLite from preparing a statement that requires reading the table (even no rows will be retrieved) xOpen: prevents SQLite from opening a cursor on the table (i.e. the

[sqlite] DROP statement on Virtual Tables

>-Urspr?ngliche Nachricht- >Von: James K. Lowden [mailto:jklowden at schemamania.org] >On Tue, 9 Jun 2015 15:13:47 +0000 >Hick Gunter wrote: > >> xConnect is called whenever SQLite decides it needs to do something >> with the existing virtual table. There

[sqlite] DROP statement on Virtual Tables

Removing the persistant instance of a virtual table is the most common way of tripping up a virtual table implementation. Just because xCreate and xConnect have identical interfaces does not mean that they are identical (although, with care, you can use the same routine to perform both tasks).

[sqlite] Virtual Table query - why isn't SQLite using my indexes?

Can you try changing LHS and RHS in the first ON expression? The older, larger query has inventory_id on the LHS and film_id on the RHS. Now you have all fields on the RHS. It would seem the QP may be inferring LEFT OUTER JOIN ON ( = ) And placing the fields first in the argument list And

[sqlite] How to get length of all columns in a table

] How to get length of all columns in a table On 6/2/2015 2:28 AM, Hick Gunter wrote: > Sqlite3_column_bytes will convert numeric values to strings and return the > length of that "string representation" (excluding the terminating \0), not > the byte size required to store the n

[sqlite] Best way to handle time slicing of SQL data.

What is the reason for wanting the id of a record to be fixed at the unique record number of the original insertion? Do you need to access historical data regularly or only for specific inquiries? For rarely required historical data, you could use a "history table" to hold historic copies of

[sqlite] How to get length of all columns in a table

Sqlite3_column_bytes will convert numeric values to strings and return the length of that "string representation" (excluding the terminating \0), not the byte size required to store the numeric value itself. -Urspr?ngliche Nachricht- Von: J Decker [mailto:d3ck0r at gmail.com] Gesendet:

[sqlite] Query containing correlated subquery gets "stuck"

Which SQLite version? Which operating system? Where is the database file located? Local storage or network drive? WAL mode or journal mode? What does "pragma integrity_check;" return? Is there a busy handler involved (in either process)? Is there an interconnection between the two processes (e.g.

[sqlite] BestIndex problem

None of the queries named requires more than 1 parameter to xFilter. Why should they? xBestIndex will be called once with no usable constraints and once with (value1, "="). The difference is that xFilter will be called once for each constraint value, i.e. once for query 1 and twice for

[sqlite] Copy data between to databases unsing windows embedded compact 7, .net 3.5

ATTACH is the command you are looking for -Urspr?ngliche Nachricht- Von: afriendandmore [mailto:afriendandmore at ymail.com] Gesendet: Donnerstag, 21. Mai 2015 15:15 An: sqlite-users at mailinglists.sqlite.org Betreff: [sqlite] Copy data between to databases unsing windows embedded

[sqlite] ALTER TABLE ADD COLUMN

Try alter table [database] add column [real_length] numeric; -Urspr?ngliche Nachricht- Von: Christoph P.U. Kukulies [mailto:kuku at kukulies.org] Gesendet: Donnerstag, 21. Mai 2015 08:51 An: General Discussion of SQLite Database Betreff: [sqlite] ALTER TABLE ADD COLUMN I used

[sqlite] Virtual Table query - why isn't SQLite using my indexes?

-Urspr?ngliche Nachricht- Von: Eric Hill [mailto:Eric.Hill at jmp.com] Gesendet: Dienstag, 19. Mai 2015 22:44 An: General Discussion of SQLite Database Betreff: Re: [sqlite] Virtual Table query - why isn't SQLite using my indexes? Hey, Gunter, ... But then what about a query like this:

[sqlite] Virtual Table query - why isn't SQLite using my indexes?

Eric Working backwards from your query, I think your schema would be similar to (foreign keys omitted) CREATE TABLE rental ( rentalID INTEGER PRIMARY KEY NOT NULL, rental_date TEXT, inventory_id INTEGER, customer_id INTEGER, ...); CREATE TABLE inventory ( inventory_id INTEGER PRIMARY KEY NOT

[sqlite] Virtual Table query - why isn't SQLite using my indexes?

Are you setting the constraintUsage return parameters correctly? SQLite ist asking two questions: 1) What is the cost of a full table scan on table inventory (your answer is 4581; I personally would leave idxNum=0 for this case) 2) What is the least cost of doing a partial table scan with any

[sqlite] Regarding SQLITE_PRIVATE

Regarding SQLITE_PRIVATE On Fri, May 15, 2015 at 3:53 PM, Hick Gunter wrote: > SQLITE_PRIVATE means that the function is PRIVATE. How they achieved PRIVATE functions in C? You are not allowed to call this function, it is not supported as part of > the SQLite API. Because you are not

[sqlite] Regarding SQLITE_PRIVATE

SQLITE_PRIVATE means that the function is PRIVATE. You are not allowed to call this function, it is not supported as part of the SQLite API. Because you are not allowed to call the function directly, it is not made available to the linker. You can call it indirectly through the EXPLAIN feature.

[sqlite] xBestIndex() implementation question

You are not supposed to know the value on the RHS because that would lure you into performing lots of work that you should not be doing in the query planning step. Remember that xBestIndex may be called multiple times with different combinations of constraints. Running the equivalent of "select

[sqlite] SQLite queries

I prefer my integers unsinged ;) SQLIte: - only signed integers up to 64 bit - arrays and structures only if you are willing to extract them from blobs via user written virtual tables - no date/time type, but you can store microsecond resolution timestamps in 64bit integers -Urspr?ngliche

[sqlite] Possible bug with locking/retying

The obvious solution would be to statically link beets to a properly compiled SQLite library instead of relying on everybody else to get it "right" -Urspr?ngliche Nachricht- Von: Simon Slavin [mailto:slavins at bigfraud.org] Gesendet: Sonntag, 03. Mai 2015 02:18 An: General Discussion of

[sqlite] Does column order matter for performance?

Actually it does. Sort of. The beginning of the record is the "manifest" (actual type of data list) area. This contains tokens representing the actual type/contents of the fields. Sticking with the example this will be 99 bytes containing the tokens "null", "zero", "one" and "integer"

[sqlite] Segfault during FTS index creation from huge data

Getting "NoMem" sounds very much like a memory leak somewhere, with the most likely place being your own application, followed by the wrapper you are using, the FTS code and lastly the SQLite core. Lastly because the SQLite core is extensively tested with an explicit emphasis on not leaking

[sqlite] Best way to temporarily store data before processing

For native SQLite tables, DROP TABLE is much faster than DELETE FROM. -Urspr?ngliche Nachricht- Von: Jonathan Moules [mailto:J.Moules at hrwallingford.com] Gesendet: Dienstag, 14. April 2015 11:40 An: 'sqlite-users at mailinglists.sqlite.org' Betreff: [sqlite] Best way to temporarily

[sqlite] possible Bug

In sqlite 3.7.14 in debug mode it raises a constraint error that states that the first argument is an invalid mem struct at location 7 in the trigger subprogram (gdb) print *pOp $1 = {opcode = 75 'K', p4type = -4 '\374', opflags = 21 '\025', p5 = 107 'k', p1 = 4, p2 = 11, p3 = 5, p4 = {i =

[sqlite] Prevent database file from being overwritten by other processes

Even if there were a simple way to protect an SQLite db file from being casually (or even maliciously) overwritten by a user (which there isn't), it is quite impossible to prevent a user with "root privileges" from accessing/altering/deleting/moving/renaming ANY file on any operating system

[sqlite] windows network and wal mode

Ad 1) You may be able to speed up deletion if you can partition your logging tables by time, e.g. each table holds the changes within a certain time frame or a fixed number of changes. When you jump back in time, dropping the tables created after the target timestamp is faster than deleting

[sqlite] What is wrong with this simple SQL?

The sub-select is within an "inner namespace" to the whole query. You are free to reference fields defined in all tables occuring in the subselect's FROM list IN ADDITION TO any fields defined in tables occurring in the main query's FROM list. This is a requirement for correlated subqueries.

[sqlite] Query times vary between 0.2 s and 30 s for very

SQLite creates an ephemeral table for the IN list,giving O(log n) performance for lookups. >-Urspr?ngliche Nachricht- >Von: James K. Lowden [mailto:jklowden at schemamania.org] >Gesendet: Samstag, 21. M?rz 2015 20:43 >An: sqlite-users at mailinglists.sqlite.org >Betreff: Re: [sqlite]

[sqlite] Clarification regarding Next opcode !

As is readily apparent the next at line 27 deals with the table "em". Given the constraint "l.fame=em.age" it is blindingly obvious that there is only one matching row from table "em". Therefore it is always correct to exit the innermost loop after only 1 iteration.

[sqlite] result of sqlite3_finalize zero, but sqlite3_errcode produces (rightly) SQLITE_CONSTRAINT

Your calling sequence should be - sqlite3_prepare() or sqlite3_prepare_v2() - sqlite3_bind_xxx() if you have any parameters - sqlite3_step() - sqlite3_reset() or sqlite3_finalize() depending on if you want to run the statement again or not You need to *check* the return status of *every* call.

[sqlite] Safe use of custom collations that are not available in all tools

How about coding the collation in C and statically linking it into the SQLite library you provide with your application? -Urspr?ngliche Nachricht- Von: Gerry Snyder [mailto:mesmerizerfan at gmail.com] Gesendet: Dienstag, 17. M?rz 2015 18:02 An: 'General Discussion of SQLite Database'

[sqlite] regarding loops in vdbe code

01 42 TableLock020 em00 43 TableLock090 lo00 44 TableLock070 mny 00 45 TableLock050 idv 00 46 Goto 010 00 How sqlite works in this c

[sqlite] regarding loops in vdbe code

If there can be not more than one row that satisfies the constraints (i.e. the constraints specifiy a unique key) and there is an index (express or implied or autocreated) available, then a simple index lookup will suffice. How many rows do you expect to have a rowid of 1? How many rows do you

[sqlite] When to disambiguate column names in queries?

When all names are disambiguated, simple typos and cut-and-paste errors become less likely to go undetected by the SQL Parser. See the "No diagnostic given for missing column" thread. -Urspr?ngliche Nachricht- Von: Keith Medcalf [mailto:kmedcalf at dessus.com] Gesendet: Dienstag, 17.

[sqlite] System.Data.SQLite and spellfix under VB.net

The zip should contain 4 files shell.c sqlite3.c sqlite3.h sqlite3ext.h sqlite-amalgamation-3080803.zip -Urspr?ngliche Nachricht- Von: sonypsx [mailto:sonypsx at gmx.net] Gesendet: Donnerstag, 12. M?rz 2015 18:56 An: 'General Discussion of SQLite Database' Betreff: Re: [sqlite]

[sqlite] Unexpected behaviour when use "IN" clause and double parentheses

The syntax diagram mandates 1 set of parentheses around the select for the IN operator. Putting a SELECT statement inside parentheses makes it a SCALAR SUBQUERY that returns (at most) 1 row with 1 column. Any extra data is ignored. Works as specified. -Urspr?ngliche Nachricht- Von:

[sqlite] data which when inserted into a table cannot be queried - a bug ?

You have swapped data and field names in the insert. -Urspr?ngliche Nachricht- Von: Jason Vas Dias [mailto:jason.vas.dias at gmail.com] Gesendet: Mittwoch, 11. M?rz 2015 13:08 An: sqlite-users at mailinglists.sqlite.org Betreff: [sqlite] data which when inserted into a table cannot be

[sqlite] Read Blob Returning bytes till NULL Terminated String

How are you inserting the blob? If you are using sqlite3_bind_blob, be sure to give the correct size (maybe you are inadvertently using strlen() or similar to compute a length?). Are you using SQLITE_STATIC, SQLITE_TRANSIENT or passing a destructor function? When retreiving the value, you

[sqlite] regarding loops in joins(VDBE)

index look up work without looping as indexes are also represented by cursor ? On Mon, Mar 9, 2015 at 4:37 PM, Hick Gunter wrote: > Are you trying to create tables with INTEGER PRIMARY KEY? You have to > write EXACTLY "integer primary key" (not case specific) to achieve this. >

[sqlite] regarding loops in joins(VDBE)

Are you trying to create tables with INTEGER PRIMARY KEY? You have to write EXACTLY "integer primary key" (not case specific) to achieve this. The em <=> lo join would probably profit from this. Your join specifies to compare em.name (a column with numeric affinity) to idv.name (a column with

[sqlite] Optimization Opportunity?

I personally would use "... EXISTS ( SELECT 1 ...", which requires no extra columns to be acessed at all. -Urspr?ngliche Nachricht- Von: Wolfgang Enzinger [mailto:sqlite at enzinger.net] Gesendet: Samstag, 07. M?rz 2015 19:25 An: sqlite-users at mailinglists.sqlite.org Betreff: [sqlite]

[sqlite] Virtual Table BestIndex Bug in system.data.sqlite/sqlite with 'like'

Using LIKE on a BLOB is not the problem. It is the LIKE optimization that is broken, because it requires a BLOB to sort AFTER a text, which is never the case, while the LIKE function compares an expression that may contain wildcards to the raw data, which may be the case. -Urspr?ngliche

[sqlite] Virtual Table BestIndex Bug in system.data.sqlite/sqlite with 'like'

And then there remain to be considered the effects of the pragma CASE_SENSITIVE_LIKE -Urspr?ngliche Nachricht- Von: Dominique Devienne [mailto:ddevienne at gmail.com] Gesendet: Freitag, 06. M?rz 2015 10:30 An: General Discussion of SQLite Database Betreff: Re: [sqlite] Virtual Table

[sqlite] Bitfield in Sqlite3-Table

An: sqlite-users at mailinglists.sqlite.org Betreff: Re: [sqlite] Bitfield in Sqlite3-Table On 2015-03-05 03:00 PM, Oskar Schneider wrote: > I just created for each day a seperate column is this worse than your > approach? > > > Hick Gunter schrieb am 8:01 Donnerstag,

[sqlite] Bitfield in Sqlite3-Table

entry + retrieve K rows). Which do you think will scale better? What happens when you reach the ?end of days? (i.e. less than 3 years after the first task is scheduled)? Von: Oskar Schneider [mailto:oskars93 at yahoo.com] Gesendet: Donnerstag, 05. M?rz 2015 14:00 An: Hick Gunter Cc: 'General

[sqlite] Bitfield in Sqlite3-Table

todo.dayno,task.* from task join todo on task.id = todo.task_id where ? [ORDER BY]; Von: Oskar Schneider [mailto:oskars93 at yahoo.com] Gesendet: Mittwoch, 04. M?rz 2015 18:57 An: Hick Gunter Betreff: Re: AW: [sqlite] Bitfield in Sqlite3-Table With normalize you mean i should create a column

[sqlite] Bitfield in Sqlite3-Table

In order of preference a) use FastBit software b) normalize your database design to eliminate the array c) use a BLOB of 125 bytes and user defined functions to operate on them d) use a string of 1000 characters ('0' or '1') and the SUBSTR() function -Urspr?ngliche Nachricht- Von: Oskar

[sqlite] FastBit-based vtable impl [WAS: Multi-table index ersatz?]

of SQLite Database Betreff: [sqlite] FastBit-based vtable impl [WAS: Multi-table index ersatz?] On Wed, Mar 4, 2015 at 10:08 AM, Hick Gunter wrote: > Properly implemented virtual tables do support indexing, but you have > to write the code to support that yourself. > > I have personall

[sqlite] SQLite error(5): database is locked in System.Data.SQLite

Only one writer may be active at any one time. The other(s) will recieve an error return status. You can either wait a while and retry this in your application or set a busy timeout to handle the "usual" cases for you. -Urspr?ngliche Nachricht- Von: Rohit Savaliya [mailto:rohit.savaliya

[sqlite] Multi-table index ersatz?

Properly implemented virtual tables do support indexing, but you have to write the code to support that yourself. I have personally implemented an index based on the fastbit package which is ideally suited to retrieving large data sets via equality and range constraints. See

[sqlite] full table scan ignores PK sort order?

Maybe an implicit ORDER BY random() ;) -Urspr?ngliche Nachricht- Von: Mohit Sindhwani [mailto:ml3p at onghu.com] Gesendet: Dienstag, 03. M?rz 2015 18:22 An: General Discussion of SQLite Database Betreff: Re: [sqlite] full table scan ignores PK sort order? On 3/3/2015 6:59 PM,

[sqlite] full table scan ignores PK sort order?

quot; from one database file to another? -Original Message- From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Hick Gunter Sent: dinsdag 3 maart 2015 13:08 To: 'General Discussion of SQLite Database' Subject: Re: [sq

[sqlite] No diagnostic given for missing column (since 3.7.10 up to 3.8.8.3)

Your expectation is wrong. What follows SELECT is the "result column list", which may contain arbitrary expressions. SQL will attempt to identify all the objects mentioned in the result column list by searching the defined objects of the statement (which includes the definitions of all the data

<    2   3   4   5   6   7   8   9   10   >