Re: [sqlite] UPDATE statement without FROM clause

Would not Replace into A () select ,, from A [cross] join B on( ); do the trick? If a.rowid has an alias (i.e. integer primary key) then the modified rows would be deleted, but reinserted with their respective previous rowids. This may required switching foreign keys off fort he duration of

Re: [sqlite] Latin-1 characters cannot be supported for Unicode

Yes, I missed the trailing 00 -Ursprüngliche Nachricht- Von: sqlite-users-boun...@mailinglists.sqlite.org [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Jean-Christophe Deschamps Gesendet: Mittwoch, 08. Juni 2016 09:37 An: SQLite mailing list

Re: [sqlite] Latin-1 characters cannot be supported for Unicode

That the same character is found in both encodings is no surprise. You need to look at the actual sequence of bytes. Comparing a file containing just the "capital A with diaresis" yields A 1 Byte sequence 0xC4 in ANSI A 2 Byte sequence 0xC384 in en_US.UTF8 on a RH5 linux system A 3 Byte

Re: [sqlite] Latin-1 characters cannot be supported for Unicode

As already stated, this is not a problem of SQLite. SQLite assumes all input to be correctly encoded in UTF (unicode), the precise flavor of which may be set (once, between creating a db file and the first insert) by a pragma. If you insert ISO (latin) encoded strings, SQLite will faithfully

Re: [sqlite] struct SrcList

I estimate that you have about a 1 : 2^^32 chance of assigning the correct value. I guess it is an index into a table of cursors required for processing the statement and there will be assertions to satisfy. -Ursprüngliche Nachricht- Von: Prakash Premkumar

Re: [sqlite] Unbinding parameters

http://www.sqlite.org/c3ref/clear_bindings.html -Ursprüngliche Nachricht- Von: Baruch Burstein [mailto:bmburst...@gmail.com] Gesendet: Donnerstag, 23. Oktober 2014 13:47 An: General Discussion of SQLite Database Betreff: [sqlite] Unbinding parameters It says here

Re: [sqlite] quasi-bug related to locking, and attached databases....

SQLite treats each attached database as a separate entity. Attaching the same file twice is just asking for problems. The query specifies that the destination db be locked for write and the source db for read; which translates to two locks that cannot coexist on one underlying db file.

Re: [sqlite] quasi-bug related to locking, and attached databases....

2014 09:43 An: General Discussion of SQLite Database Betreff: Re: [sqlite] quasi-bug related to locking, and attached databases On Mon, Oct 27, 2014 at 9:23 AM, Hick Gunter <h...@scigames.at> wrote: > SQLite treats each attached database as a separate entity. Attaching > the same fil

Re: [sqlite] quasi-bug related to locking, and attached databases....

TEMP tables get created in database temp; which is located in "a file" or "in memory" depending on the SQLITE_TEMP_STORE preprocessor symbol and the pragma temp_store. -Ursprüngliche Nachricht- Von: Stephan Beal [mailto:sgb...@googlemail.com] Gesendet: Montag, 27. Oktober 2014 11:44 An:

[sqlite] Table qualification not supported in some cases (was: quasi-bug related to locking, and attached databases)

Betreff: Re: [sqlite] quasi-bug related to locking, and attached databases On Mon, Oct 27, 2014 at 11:59 AM, Stephan Beal <sgb...@googlemail.com> wrote: > On Mon, Oct 27, 2014 at 11:53 AM, Hick Gunter <h...@scigames.at> wrote: > >> TEMP tables get created in database

Re: [sqlite] Index without backing table

Can you give an example of what such a beast would look like? CREATE TABLE not_exist (A, B, C, D, E);-- for illustrative purposes CREATE INDEX no_table ON not_exist (A,B,C,D,E); -- has to be covering index The only efficient order to return and/or select rows is by {A}, {A,B}, {A,B,C}, ...

Re: [sqlite] SQLite as a meta database

Hi, we have extensive experience with respect to the use of virtual tables in SQLite. In fact, the only native SQLite tables we use are in a configuration checking tool. We have "providers" from in-memory indexed tables, CTree (r) files, Oracle tables (read only), structured disk files,

Re: [sqlite] SQLite as a meta database

ive me an idea of what a minimal xBestIndex/xFilter skeleton might look like? I walked though "ext/misc/amatch.c" from the www.sqlite.org/src/artifact tree, but I'm a little lost. Thanks, Mike Beddo -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-user

Re: [sqlite] Does using e.g. LTRIM function remove collation?

I get the following results for the second select: A B a (lowercase!!!) Are you sure you ran the exact query stated? -Ursprüngliche Nachricht- Von: James K. Lowden [mailto:jklow...@schemamania.org] Gesendet: Samstag, 08. November 2014 01:52 An: sqlite-users@sqlite.org Betreff: Re:

Re: [sqlite] SQLite where clause tree

Your tree is wrong. I would expect that operator precedence is handled in the parser. The code generator will happily implement any tree, regardless of how insane it may be. -Ursprüngliche Nachricht- Von: Prakash Premkumar [mailto:prakash.p...@gmail.com] Gesendet: Montag, 10. November

Re: [sqlite] Does using e.g. LTRIM function remove collation?

11. November 2014 00:05 An: sqlite-users@sqlite.org Betreff: Re: [sqlite] Does using e.g. LTRIM function remove collation? On Mon, 10 Nov 2014 08:43:24 + Hick Gunter <h...@scigames.at> wrote: > I get the following results for the second select: > > A > B > a (lowercase!!!) > &g

Re: [sqlite] Multiple threads reading different data

This is the I (Isolation) in ACID. WAL mode allows the writer to pretend that no transactions are outstanding and begin and even commit a write transaction. This change sits in the Wal file until all prior transactions have been completed and the change can be copied to the db. As long as your

Re: [sqlite] [SQLite] Support for 23,10 Precision number format

Data types are 64bit integer (~18 decimal digits) and 64 Bit IEEE Float(11 bit exponent, 52 bit fraction), so no. Store the numbers as TEXT (human readable) or BLOB (e.g. 128Bit binary) and write user-defined functions to manipulate them. -Ursprüngliche Nachricht- Von: Dinesh Navsupe

Re: [sqlite] [SQLite] Support for 23,10 Precision number format

IIRC there was a programmer working for a bank that managed to siphon off the sub-unit fractions that the interest calculating software generated (how much interest is owed for $1 at 0,25% p.a. for 2 days*) onto his own account and temporarily got rich quick. $1 * 0,25% = $25 (interest

Re: [sqlite] Discrepancy with indexing and WHERE clause with AND/OR

I would agree with the suspicion that your data is "changing shape" i.e. the cardinality of index fields is becoming very different from what ANALYZE stored. As for bypassing the query planner/code generator you might want to contact Prakash Premkumar who is apparently

Re: [sqlite] Column name as a variable

SELECT table_name FROM sqlite_master; And then, in your programming language of choice, execute SELECT count() FROM For each received table name. You cannot use a variable instead of a table name in SQL. -Ursprüngliche Nachricht- Von: Paul Sanderson

Re: [sqlite] "database is locked" for SQLITE_BUSY

I think the error messages are distinct enough as is. SQLITE_BUSY means that some connection is BUSY with a write transaction and has locked the database file; presumably, it will be possible to write to the database when the current writer has finished, just not now or within the specified

Re: [sqlite] replace many rows with one

Both, I guess Insert into ... select a,b,sum(theCount) group by a,b; -Ursprüngliche Nachricht- Von: Simon Slavin [mailto:slav...@bigfraud.org] Gesendet: Mittwoch, 10. Dezember 2014 12:39 An: General Discussion of SQLite Database Betreff: [sqlite] replace many rows with one Dear folks,

Re: [sqlite] '.timer on' in the shell tool

Units are "CPU Seconds". "user" time is spent within user code, i.e. SQLite, "sys" time is spent within system calls, i.e. reading/writing files. The balance between the times depends on various parameters, including the state of the disc cache and the complexity of your INSERT...SELECT

Re: [sqlite] Query Planner for Virtual Tables: link table evaluation & transitive property of constraints not used

I would concur in that SQLite is asking "which subset of the given constraints yields the most efficient access". The possible query plans are 1) A() -> B(ID) -> C(LINKID) 2) C() -> B(LINKID) -> A(ID) 3) B() -> A(ID) + C(LINKID) or B() -> C(LINKID) + A(ID) 4) A() -> C() -> B(ID,LINKID) or

Re: [sqlite] Row filtering prior to aggregate function execution

SELECT ...,sum(...),count() FROM ... WHERE security(...) ... With a user defined function security(). -Ursprüngliche Nachricht- Von: Roland Martin [mailto:rolandsmar...@gmail.com] Gesendet: Donnerstag, 18. Dezember 2014 17:09 An: sqlite-users@sqlite.org Betreff: [sqlite] Row filtering

Re: [sqlite] Row filtering prior to aggregate function execution

.@gmail.com] Gesendet: Freitag, 19. Dezember 2014 11:28 An: General Discussion of SQLite Database Betreff: Re: [sqlite] Row filtering prior to aggregate function execution Would this work? SELECT SUM(...),COUNT(...), ... FROM ... WHERE ... GROUP BY ... HAVING security(...) ... Staffan On Fri,

Re: [sqlite] Partial index to find maximum

create the primary key index ordered properly CREATE TABLE t (..., PRIMARY KEY ( a ASC, b DESC)...); SELECT b FROM t WHERE a = ? LIMIT 1; If you insist on using a partial index for this (for example if each a has a lot of b entries) you could add a field b_is_max and keep it current using

Re: [sqlite] SQL newbie, how to implement a delete correctly.

Select * from child01 where p01_id in (select rowid from parent01 where ...); Or Select c.* from parent01 p join child01 c on p.rowid=c.p01_id where ...; -Ursprüngliche Nachricht- Von: Jonathan Leslie [mailto:jlesli...@yahoo.com] Gesendet: Dienstag, 30. Dezember 2014 15:58 An:

Re: [sqlite] SQL newbie, how to implement a delete correctly.

table:child02.xxx_id = 2432... etc. the idea is without knowing all the names of all the tables, find all references to parent01.p01_id (where value is xxx)  From: Hick Gunter <h...@scigames.at> To: 'Jonathan Leslie' <j...@jonathanleslie.com>; 'General Discussion of SQLite Database' &

Re: [sqlite] Suggestion for syntax enhancement for virtual tables

Temporary virtual tables sounds like an interesting concept. Does the xDestroy() function get called on such a beast (as opposed to xDisconnect() when the connection is closed)? Should that function delete the backing store (even if a non-temporary virtual table is still connected)?

Re: [sqlite] New column in select will not mask column of the same name in having clause and sqlite won't warn

This is completely legal and well defined. HAVING is applied to the RESULT set of a SELECT. The select asks to count the "distinct kontrola" in each group of kvadrat and datum, the HAVING clause specifies returning only those records with pocet > 1. If there were no pocet column in table b,

Re: [sqlite] New column in select will not mask column of the same name in having clause and sqlite won't warn

Ursprüngliche Nachricht- >Von: James K. Lowden [mailto:jklow...@schemamania.org] >Gesendet: Donnerstag, 08. Jänner 2015 03:56 >An: sqlite-users@sqlite.org >Betreff: Re: [sqlite] New column in select will not mask column of the same >name in having clause and sqlite won't warn

Re: [sqlite] Error while executing query: no such column: t1.*B.Switch-Tower-Sector

Maybe you mean (assuming there is not more than one record in t2 for a given SSID-CELLID-SECTOR) UPDATE t1 ... -Ursprüngliche Nachricht- Von: MikeSnow [mailto:michael.sab...@gmail.com] Gesendet: Freitag, 09. Jänner 2015 00:12 An: sqlite-users@sqlite.org Betreff: [sqlite] Error while

Re: [sqlite] xBestIndex/XFilter and virtual tables

SQLite is asking your virtual table questions: 1) what is the cost of a full table scan? (your answer: 1000) 2) what is the cost of a lookup by "id"? (your answer: 1) 3) when performing a key lookup, do you promise to return only rows matching the key? (your answer in returned in the "omit"

Re: [sqlite] xBestIndex/XFilter and virtual tables

BTW: SQLite will also ask your virtual table about GROUP BY/ORDER BY capabilities if either of the clauses is included in the SELECT. -Ursprüngliche Nachricht- Von: Hick Gunter [mailto:h...@scigames.at] Gesendet: Montag, 12. Jänner 2015 08:02 An: 'General Discussion of SQLite Database

Re: [sqlite] help with query

Step 1: count the occurrences: SELECT data1,count() AS count FROM table GROUP BY data1; Step 2: get the rows with a count above the limit SELECT data1,count() AS count FROM table GROUP BY data1 HAVING count >= 3; Step 3: get the keys from the rows SELECT data1 FROM (SELECT data1,count() AS

Re: [sqlite] database is locked

Use the following code snippet (add error checking, set v_file to the full path name of your SQLite db file) to check. It attempts to take the same locks as SQLite would, but prints the pid of the blocking process. It also prints the journal mode. #include #include #ifdef AIX64 #include

Re: [sqlite] Inserting/removing large number of rows with index

It depends in how you define "update the index". If you mean "write to disk" then this happens "once, at the end of the transaction" (the exact process differs depending on the journal mode). If you mean "change the index structure in memory" then (as already noted) the changes will happen

Re: [sqlite] Page_size increase from 1k to 4k made my "REPLACE INTO" slower. why?

From what little you reveal I assume this is some kind of datalogging application. I also assume there will be a primary key (call ?) and also suspect that there are a number of secondary indices for data retrieval. Since you make no mention of transactions, I must infer that you are using

Re: [sqlite] Malformatted output by sqlite3

Works as specified. The .width command sets the output width of a column to a certain number of characters; output that is shorter is padded on the right (or the left, if the width is negative); output that is too long is truncated. Use the printf() function to define the format of real

Re: [sqlite] Update Statements using Sub query

You are requesting the field Column Name (with embedded whitestpace in the field name) from a query with a where clause that forces it to be the string 'Date'. Perhaps your are looking for SELECT Date -Ursprüngliche Nachricht- Von: MikeSnow [mailto:michael.sab...@gmail.com]

Re: [sqlite] Shell tool question

Yes. I'm using several layers of .read files to load the appropriate subset of extensions for each class of process (OLTP, user query, subsystem, specific tools,...) -Ursprüngliche Nachricht- Von: Simon Slavin [mailto:slav...@bigfraud.org] Gesendet: Mittwoch, 21. Jänner 2015 17:35 An:

Re: [sqlite] System.Data.SQLite - Exception Calling SQLiteModule.DeclareTable

I have always wondered why people will insist on using human readable column names (with embedded spaces and special characters) in the implementation layer (SQL code) instead of the presentation layer (user interface). The clutter introduced into queries by having to quote the column names by

Re: [sqlite] database locked in PHP

Maybe you can use the (linux, c) code I posted recently to determine which process/thread is locking the database file. -Ursprüngliche Nachricht- Von: Lev [mailto:leventel...@gmail.com] Gesendet: Sonntag, 25. Jänner 2015 01:36 An: sqlite-users@sqlite.org Betreff: Re: [sqlite] database

Re: [sqlite] Invalid column prefix returned in SELECT with joined subquery

It is never a good idea to rely on automatically assigned column names. If you want reproducible, predictable, release independant column names then please assign them with the AS clause. -Ursprüngliche Nachricht- Von: Marcus Bergner [mailto:marcusberg...@gmail.com] Gesendet: Sonntag,

Re: [sqlite] Invalid column prefix returned in SELECT with joined subquery

[mailto:t...@clothears.org.uk] Gesendet: Montag, 26. Jänner 2015 13:00 An: General Discussion of SQLite Database Betreff: Re: [sqlite] Invalid column prefix returned in SELECT with joined subquery On 26 Jan 2015 at 07:33, Hick Gunter <h...@scigames.at> wrote: > It is never a good ide

Re: [sqlite] regarding looping in vdbe for sqlite table joins!

SCAN in the query plan = Rewind...Next LOOP in opcodes SEARCH in the query plan = Column...Seek in opcodes SQLite has determined that creating an automatic index on the referenced tables should be faster than performing a full table scan for the general case. asql> explain query plan select *

Re: [sqlite] Multi-thread mode question

In serialized mode, SQLite will acquire the mutex when it detects you are "starting to use" the database handle (somewhere between entering sqlite3_prepare and the first sqlite3_step) and then HANG ON TO IT, NOT LETTING GO until the calling thread is "finished" (like when sqlite3_step returns

[sqlite] Sqlite Virtual Table Example with non-trivial xBestIndex (was: Porting SQLite to another operating system (not supported out of the box))

Having personally written about a dozen virtual table implementations I can confirm that those implementations needing a nontrivial xBestIndex function are all based on building an SQLite interface on substantial proprietary storage subsystems like an in-memory ISAM table (with configurable

Re: [sqlite] SELECT ... FOR UPDATE

SQLite does not use row level locking, only file level locking. You can use BEGIN IMMEDIATE to exclude writers or BEGIN EXCLUSIVE to exclude readers and writers. If you do not explicitly start a transaction, each statement constitutes it's own transaction. -Ursprüngliche Nachricht-

[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] 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] 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] 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] 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] 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] 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] 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] [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] 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] 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] 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] 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] 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] 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] 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] 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] 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] 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] 回复： 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] 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] 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] Can this be sorted?

You are attempting to compute 0/0 which is NULL and happens to be smaller than 0/47 which is 0. -Urspr?ngliche Nachricht- Von: Bart Smissaert [mailto:bart.smissaert at gmail.com] Gesendet: Montag, 16. Februar 2015 09:49 An: General Discussion of SQLite Database Betreff: Re: [sqlite] Can

[sqlite] Appropriate Uses For SQLite

We are using SQLite as the catch-all data access method (via custom extensions) for - Oracle tables and views - Faircom CTree files - Shared memory record stores ("Data Dictionary") - Log file access - Blob to record translation (TLV structures) - Partitioned data stores (CTree and Data

[sqlite] Using incremental BLOB functions against a BLOB column in a virtual table

Incremental BLOB I/O makes certain assertions that cannot be guaranteed (much less verified at runtime) for virtual tables (e.g. 1: unique rowids that 2: can be used for access) and needs to know how to read/write them (which is under the control of the virtual table author).

[sqlite] Using incremental BLOB functions against a BLOB column in a virtual table

ions against a BLOB column in a virtual table Thanks to Richard Hipp and Hick Gunter for their replies on this topic. Given that support for support for BLOBs in virtual tables differs from that for BLOBs in physical tables, is there any method or function available to the sqlite3_x() caller that

[sqlite] Regarding creating a mem object and copying contents to it in SQLite

The mem object is internal to sqlite, it is not intended to be created/changed by user code. What are you trying to do that makes you think you need to manipulate internal structures? -Urspr?ngliche Nachricht- Von: Sairam Gaddam [mailto:gaddamsairam at gmail.com] Gesendet: Mittwoch,

[sqlite] Regarding creating a mem object and copying contents to it in SQLite

] Regarding creating a mem object and copying contents to it in SQLite I am trying to optimize certain operations of SQLite internally, so i created mem object. On Wed, Feb 25, 2015 at 12:36 PM, Hick Gunter wrote: > The mem object is internal to sqlite, it is not intended to be > created/c

[sqlite] Characters corrupt after importing a CSV file

Maybe you are falling into the character/byte trap. The SQL function length() returns the number of CHARACTERS in a string, which - for UTF encoded strings containing non-latin characters - is smaller than the number of BYTES required to represent them. Typically you will be losing bytes at

[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] 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] 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] 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] 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] 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] 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] 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] 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] 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] 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] 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

] 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] 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] 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] 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

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] 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] 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] 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

  1   2   3   4   5   6   7   8   9   10   >