Re: [sqlite] Maintaing a Last updated timestamp column using triggers

David Blake wrote: > CREATE TRIGGER my_update_trigger BEFORE UPDATE ON My_table > FOR EACH ROW WHEN NEW.lastupdated < OLD. lastupdated > BEGIN > UPDATE song SET lastupdated = DATETIME('now') WHERE id = NEW.id > END > > The WHEN clause is an attempt to avoid infinite recursion that UPD

Re: [sqlite] Performance problem with DELETE FROM/correlated subqueries

Jürgen Baier wrote: > CREATE TABLE main ( ATT1 INT, ATT2 INT, PRIMARY KEY (ATT1,ATT2) ); > CREATE TABLE staging ( ATT1 INT, ATT2 INT ); > > Then I execute > > DELETE FROM main WHERE EXISTS (SELECT 1 FROM staging WHERE main.att1 = > staging.att1 AND main.att2 = staging.att2) > > which takes a

Re: [sqlite] New word to replace "serverless"

Jim Dodgen wrote: > I vote for ignoring the marketing types and stick with "serverless" The word is intended to communicate a specific meaning to readers. Ignoring that the marketing types have changed the common meaning of "serverless" will just lead to confusion. Originally, "serverless" was a

Re: [sqlite] How can I detect rows with non-ASCII values?

Rocky Ji wrote: > I am asked to highlight rows containing strange characters. All data were > ingested by a proprietary crawler. > > By strange, I mean, question marks, boxes, little Christmas Trees, solid > arrows, etc. kind of symbols; these appear suddenly in flow of normal ASCII > English lett

Re: [sqlite] Capturing the changes in columns in a table

David Raymond wrote: > My brain started yelling that that needed a "limit 1" on the subquery so that > it would only return 1 row. > > How is that handled by other databases? SQL-92 says: | 6.11 | | General Rules | | 2) If a is a and the | result of the is empty, then the result o

Re: [sqlite] How to get rowid for select query?

Andy wrote: > I try "select rowid, field1,field2 from table" but first value was not > number rowid but literary string "rowid". Please show the actual code (not SQL, but your program) that you're executing. Regards, Clemens ___ sqlite-users mailing li

Re: [sqlite] Bug? SQLite command-line result is different from Perl DBI::Sqlite result

Richard Hipp wrote: > On 1/5/20, Keith Medcalf wrote: >> select * from a, b, c using (id); -- very strange result > > PostgreSQL and MySQL process the query as follows: > >SELECT * FROM a, (b JOIN c USING(id)); > > SQLite processes the query like this: > >SELECT * FROM (a,b) JOIN c USING (

Re: [sqlite] INSERT OR REPLACE in trigger body fails with UNIQUE constraint

Mike _ wrote: > The trigger documentation says: "An ON CONFLICT clause may be specified as > part of an UPDATE or INSERT action within the body of the trigger. However > if an ON CONFLICT clause is specified as part of the statement causing the > trigger to fire, then conflict handling policy of th

Re: [sqlite] Long long int constants in sources

Max Vlasov wrote: > bcc 5.5 compiler ... didn't like long long constants >such as -2251799813685248LL If you want to make this particular compiler happy, use -2251799813685248i64. Regards, Clemens ___ sqlite-users mailing list sqlite-users@mailinglists

Re: [sqlite] Result set column names

Keith Medcalf wrote: > If you do not provide as AS clause to give a result column a name, then > each implementation is free to provide whatever names it feels like (so > sayeth the standard). The SQL-92 standard actually says: |6.4 | | ::= [ ] | |7.9 | | ::= | SELEC

Re: [sqlite] Mixed ASC and DESC in single column

Hamish Allan wrote: > I want to get the uuids in order as if `foo`, `bar` and `bar` were > different columns, e.g. if the desired order were "bar ASC, foo DESC, baz > ASC" SELECT * FROM (SELECT uuid FROM Data WHERE info LIKE 'bar/%' ORDER BY info ASC) UNION ALL SELECT * FROM (SELECT uuid FROM Data

Re: [sqlite] Using application_id

Tobias Leupold wrote: > In the docs, a magic file is linked ( https://www.sqlite.org/src/ > artifact?ci=trunk&filename=magic.txt ) with "registered" formats. > > Is there another list with "taken" application ids? No. Apparently, authors or 'private' file formats do not bother to register their I

Re: [sqlite] What is wrong with this SQL?

Bart Smissaert wrote: > I did try left joins, but no data returned. All filters for outer-joined rows must be specified in the JOIN itself; in the WHERE clause, NULL values would make the comparison fail. Regards, Clemens ___ sqlite-users mailing list

Re: [sqlite] The best way to check if a file is a sqlite3 DB file?

Peng Yu wrote: > Is there a better way to just return an exit status of 0 for > a sqlite3 DB file and 1 otherwise? Extract the magic header string from a known DB file: dd bs=16 count=1 < some.db > sqlite3-signature Then you can compare it against the beginning of the file: cmp --bytes=16 s

Re: [sqlite] INSERT vs BEGIN

Rob Richardson wrote: > I didn't know it is possible to insert multiple rows into a table using a > command like this. Is this just an SQLite feature, or is this part of the > SQL standard? This is defined since SQL-92, but only at the Full SQL conformance level. Regards, Clemens __

Re: [sqlite] Trying to edit my profile on this list

g a sansom wrote: > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > That page has links for ... unsubscribing. That button is labelled "Unsubscribe or edit options". Regards, Clemens ___ sqlite-users mailing list sqlite-users@m

Re: [sqlite] specify CHECK clause to date text field

Martin wrote: > sqlite> select date('2019-02-29'); -- not a leap year > 2019-02-29 > I would appreciate any advice on the preferred way to specify a > CREATE TABLE .. CHECK clause > to guard inserting a -mm-dd date into a text field. sqlite> select date('2019-02-29', '+0 days'); 2019-03-

Re: [sqlite] SQLITE_BUSY (SQLITE_BUSY_SNAPSHOT) due to a simple SELECT query -- Documentation needed!

Kira Backes wrote: > Our code base does not use transactions at all When you do not use explicit transactions, SQLite will automatically create implicit transactions. says: | An implicit transaction (a transaction that is started automatically, | not

Re: [sqlite] How does errorLogCallback interrupt my running program?

test user wrote: > The error log takes a callback which is called when an error occurs from > any SQLite FFI function: What exactly do you mean with "FFI"? You did not mention any other language. > How does this interrupt my program whilst its running? As a library, SQLite is part of your progr

Re: [sqlite] Problem with int and DateTime types with EntityFrameWorkCore.Sqlite

Ainhoa B wrote: > My database has tables with int values and DateTime values. When I execute > the scaffold command to convert the tables of the database to models in > .NET Framework, my colums of type int are being converted to long SQLite's INTEGER type has 64 bits. The framework assumes that

Re: [sqlite] Determining valid statement placeholders.

test user wrote: > I want my library to be able to detect the problem programatically. > > I think SQLite internally knows how many placeholders are in the query at > parse time. > > My question is how can I get the data via the API At the moment, there is no such mechanism in the API. You could

Re: [sqlite] BLOB and TEXT comparisons

Charles Leifer wrote: > SELECT SUBSTR(?, 1, 3) == ? > > However, if I mix the types, e.g. sqlite3_bind_text("abcde") and > sqlite3_bind_blob("abc") then the comparison returns False. > > Fom a byte-to-byte perspective, this comparison should always return True. > > What's going on? Apparently, not

Re: [sqlite] "unable to use function highlight in the requested context" in group by

Damian Adrian wrote: > While using the FTS5 highlight() function in a group by query like this one: > > SELECT > group_concat(highlight(entries, 1, '>', '<')) > FROM entries > WHERE entries MATCH 'an*' > GROUP BY id; > > I get "Error: unable to use function highlight in the requested context".

Re: [sqlite] RFE: allow parameters in PRAGMA statements

Wout Mertens wrote: > I am using the user_version pragma for implementing an event-handling > database. I'd like to prepare the statement to update it, e.g. `PRAGMA > user_version = ?`. > > However, sqlite3 won't let me do that, so I just run the text query every > time with the number embedded. <

Re: [sqlite] Bug in table_info pragma

Simon Slavin wrote: > On 17 May 2019, at 1:33pm, Clemens Ladisch wrote: >> This keyword behaves magically. ... as far as the SQL standard is concerned. > Mmmm. In that case, to implement this properly you need to store > a default-type flag alongside the default value. Th

Re: [sqlite] Bug in table_info pragma

Simon Slavin wrote: > If you supply "default CURRENT_TIMESTAMP" I would expect SQLite to > evaluate CURRENT_TIMESTAMP, find a string value like > '2019-05-17 12:10:43', and store that string in the schema. This keyword behaves magically. ANSI SQL-92 says: | The default value inserted in the colum

Re: [sqlite] SQLite with single writer on Windows network share

Andrew Moss wrote: > ... an SQLite database hosted on a windows network share (using server > 2012 R2 or later). We are well aware this is not advisable There are three possible sources of network filesystem data corruption: 1) Bad locking implementations. Some Unix-y network filesystems prod

Re: [sqlite] SQLite with single writer on Windows network share

Simon Slavin wrote: > setting the journal mode of the database to WAL will ... certainly lead to data corruption; WAL requires shared memory, which cannot work over a network filesystem. Regards, Clemens ___ sqlite-users mailing list sqlite-users@maili

Re: [sqlite] Read/Write cycle

manojkumar schnell wrote: > What is the maximum read/write cycle? The database puts no limit on how often you can read or update data. Regards, Clemens ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cg

Re: [sqlite] Row values with IN

Simon Slavin wrote: > I think that the documentation doesn't sufficiently explain the problem. > Or maybe the use of IN or lists deserves its own page rather than being > buried in the extremely long page on expressions. says: | For a row-value IN operator, t

Re: [sqlite] Help with sqlite3_value_text

x wrote: >> As long as you use _value_bytes after _text you're fine... so if any >> conversion did take place the value will be right of the last returned >> string type. > > Could you explain that to me? I’m not sure why any conversion takes place > and, on reading the text below, I would’ve thoug

Re: [sqlite] How to use ORDER BY on FTS5 table ?

PM Nik Jain wrote: > A SCAN is being performed on a fts5 table. I am not sure but I > think that means no index. > > sqlite> explain query plan select * from productsfts p where p.attributes > match '50'limit 6; > `--SCAN TABLE productsfts AS p VIRTUAL TABLE INDEX 1: Everything except "INDE

Re: [sqlite] export tables

Mohsen Pahlevanzadeh wrote: > I need to export some tables with dot command, How I do it? https://www.sqlite.org/cli.html#csv_export Regards, Clemens ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-

Re: [sqlite] Clear sqlite3 terminal enhancement

Jeffrey Walton wrote: > When working in the Linux terminal we can clear the scrollback with > the 'clear' command; and we can delete all history and scrollback with > the 'reset' command. I am not able to do the same within the sqlite3 > terminal. Those are programs run from the shell. So you can

Re: [sqlite] Is there a way to select using cid?

Peng Yu wrote: > There are cid's for each table. Is there a way to use "select" with > cid's instead of their names? > > select * from pragma_table_info('test'); > cid nametypenotnull dflt_value pk > -- -- -- -- -- --

Re: [sqlite] filling a key/value table

Simon Slavin wrote: > I wanted to speak against including a BLOB field in a compound PRIMARY KEY. That depends on the size of the blob. If it contains 'normal'-sized values, it's just as efficient as other types. Regards, Clemens ___ sqlite-users mail

Re: [sqlite] bug report: UPSERT / INSERT ON CONFLICT PK Autoincrement

Stanislav Zabka wrote: > When conflict occurs, no import performs, but PK is incremented nevertheless. says: | Note that "monotonically increasing" does not imply that the ROWID | always increases by exactly one. One is the usual increment. However, | if an in

Re: [sqlite] Developer questions about the Online Backup API

Simon Slavin wrote: > If the source database is changed while the Online Backup API is > running, it returns to the beginning of the database and starts again. The backup API must create a consistent snapshot of the source database, i.e., the result must be the exact state at some point in time wh

Re: [sqlite] Recursive CTE on tree with doubly linked items

heribert wrote: > I've a tree with doubly linked items. I want to get all siblings of a tree > node. If you want them in order, you have to walk through the linked list: WITH SiblingsOf3 AS ( SELECT * FROM Tree WHERE ParentIDX = (SELECT ParentIDX FROM Tree

Re: [sqlite] Foreign Key constraint fails due to unrelated field

Jonathan Moules wrote: > UPDATE lookups set error_code=3 and exp_content_type='ogc_except' WHERE > content_hash = '0027f2c9b80002a6'; This fails because "3 and exp_content_type='ogc_except'" is interpreted as a boolean expression. To update multiple fields, separate them with commas: UPDATE l

Re: [sqlite] BigInt loss accuracy

Derek Wang wrote: > sqlite biggest int is supposedly 9,223,372,036,854,775,807 (9 and a bit > Quintillion), but when the number is larger than 1E+17, it loses some > accuracy when retrieving. In plain SQL, everything works fine up to the limit: create table t(i notoriously big integer); with

Re: [sqlite] How to refer to `this` table?

Rocky Ji wrote: > CREATE TABLE Aliases ( > alias_id INTEGER PRIMARY KEY AUTOINCREMENT, > real_name TEXT NOT NULL, > aka TEXT NOT NULL, > CONSTRAINT xyz UNIQUE (real_name, aka), > CONSTRAINT noCircularRef_A CHECK ( > real_name NOT IN (SELECT aka FROM Aliases) > ), > CONSTRAINT noCi

Re: [sqlite] How to get aggregate without reducing number of rows (repeats are ok)?

Rocky Ji wrote: > But everyone advices against nested select statements. Who? I've heard rumors that older version of the Oracle query optimizer did worse with subqueries than with joins, but such advice is not necessarily correct for SQLite. SQL is set-based language, and queries that are writt

Re: [sqlite] Retrieve tables accessed by query

Zach Wasserman wrote: > Is anyone aware of an API I can use to determine which tables are accessed > by a given query? https://www.sqlite.org/c3ref/set_authorizer.html Regards, Clemens ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org

Re: [sqlite] Choosing one value from the group with priority

Ivan Krylov wrote: > select * from test where id in (1,2) group by id; Please note that this is not standard SQL; SQLite allows to SELECT columns that are not mentioned in the GROUP BY clause, but they get their values from a random row in the group.

Re: [sqlite] sqlite 3.37.1: void function returns value

Peter da Silva wrote: > I am pretty sure that the code is not legal C Indeed; C99 and C11 say in 6.3.2.2: | The (nonexistent) value of a void expression (an expression that has | type void) shall not be used in any way [...] and in 6.8.6.4: | A return statement with an expression shall not appear

Re: [sqlite] Checking differences in tables

Jose Isaias Cabrera wrote: > t_20190208 (a PRIMARY KEY, b, c, d, e). > > I create a new table, > > t (a PRIMARY KEY, b, c, d, e) > > and insert a set of "new data", which contains changes that happened since > yesterday > after the new set of data was created. Right now, I bring the data out into

Re: [sqlite] Displaying hierarchical structure

Bart Smissaert wrote: > ID PARENT_ID FOLDER RANK > --- > 1 0 Main1 > 2 1 CC 1-02 > 3 1 BB 1-03 > 4 1

Re: [sqlite] WAL mode readonly errors to SELECT statements

Robert Searle wrote: > We have recently started trying to provide read-only access to the database > (service run as user with group/other read access permissions under Linux, > service not database owner) and occasionally get either > SQLITE_READONLY_RECOVERY or SQLITE_READONLY_CANTINIT responses

Re: [sqlite] SQLite linked to plugin binary

Carsten Müncheberg wrote: > A host application (which is a black box for me I cannot change) is loading > my plugin binaries which each have SQLite linked statically. When two > plugins now access the same database file from the process of the host > application none of the serialization mechanisms

Re: [sqlite] Query Planning Knowledge

Andy Bennett wrote: >> foreign key constraints > > my experience with other engines taught me that it makes experimenting at the > monitor harder. Then don't use them. :) But do you actually want 'wrong' data? > Are there any efficiency benefits or is it just there to enforce data > integrity?

Re: [sqlite] Query Planning Knowledge

Andy Bennett wrote: > I could use the inner join for the "entrys" join and the "items" join > but not the "entry-items" join because each entry can have more than > one item. WITH a(id, name) AS (VALUES (1, 'A')), b(id, name) AS (VALUES (1, 'B1'), (1, 'B2')) SELECT * FROM a INNER JOIN b

Re: [sqlite] SQLITE 3.26.0 compiler warning

Gary Sanders wrote: > shell.c(16466): warning C4996: 'strdup': The POSIX name for this item is > deprecated. strdup() is now in the dynamic memory TR: . The 'correct' way to get it is: #ifdef __STDC_ALLOC_LIB__ #define __STDC_WANT_LIB_EXT2

Re: [sqlite] sqlite trig/geometry error

Brent Wood wrote: > Is there an easy way to tell where the sqlite math functions are coming from? Are you using the sqlite3 command-line shell, or something else? Regards, Clemens ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http:

Re: [sqlite] ROLLBACK TO stmt within committed transaction cause write operation to the WAL file.

sanhua.zh wrote: >BEGIN IMMEDIATE; >SAVEPOINT s1; >INSERT INTO t VALUES(2); >ROLLBACK TO SAVEPOINT s1; >COMMIT; >SOMETHING appended into the end of WAL file. >BUT why? We should have nothing to write. It's information about the WAL file itself. The WAL file must be initialized at some time, so i

Re: [sqlite] i Know i should use 'AS', but ....

Luuk wrote: >sqlite> .mode column >sqlite> .headers on >sqlite> select 1 as X,date() as d union all select 2,date() union all >select 3,datetime(); >X   d >--  -- >1   2018-12-25 >2   2018-12-25 >3   2018-12-25 The value is longer than the column wi

Re: [sqlite] Claimed vulnerability in SQLite: Info or Intox?

Dominique Devienne wrote: > I'd like an official stance on SQLite itself please. Regards, Clemens ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/

Re: [sqlite] Library Files on my Computer

Giovanni Giusti wrote: > they are offsite What do you mean with that? Do you get an error message when you try to access them? > and end in .sqlite-wal If there is no corresponding .sqlite file (without the "-wal"), then those files are not actual SQLite database files. Regards, Clemens __

Re: [sqlite] HELP!

am...@juno.com wrote: > I work for a company that has a many locations with more than one > person in every location. I want to share the databases I have built > using SQLITE with some of the people in each location. Do any of you > good people know is SQLITE on the cloud? It's not; SQLite is fil

Re: [sqlite] Help!

am...@juno.com wrote: > how to take a database in database (not structure) form--and copy and paste > it into an e-mail A database file is binary; the easiest way would be to attach it. If you want to paste it into the e-mail itself, you have to convert it to text somehow. Either create a bunch

Re: [sqlite] forming sqlite3_statements using stored sql

David Fletcher wrote: > create table if not exists StmtSQL ( > StmtNameTEXT NOT NULL UNIQUE, > SQL TEXT NOT NULL); > > The GetPreparedStmt() function retrieves the SQL from table, creates a new > sqlite3_statement object (or retrieves this from a cache).

Re: [sqlite] Displaying row count

David Fletcher wrote:> Hi all, > Is there a mode in the sqlite shell, or some fancy extension, that will > display a row > number when outputting results? No. You'd have to modify the shell, or add the row_number() window function to the query. Regards, Clemens ___

Re: [sqlite] curious discovery about geopoly module

Graham Hardman wrote: > SQLiteForExcel [1] https://github.com/govert/SQLiteForExcel "sqlite3.dll is a copy of SQLite version 3.11.1" > "no such module: geopoly" > > I guess that makes sense in one way, but it begs the question of why the > shell and my version of SQLiteExpert find a way to unders

Re: [sqlite] Help!

am...@juno.com wrote: > how do I make a field wrap the text. In other words, rather than having > the text in a field keep going from right to left, when I hit the right > hand margin, how do I make the text automatically go to the next line. The purpose of SQLite is to store data, and to give it

Re: [sqlite] Exception handling during create table from a virtual one

Max Vlasov wrote: > I have a virtual table that raises an unhandled exception during a create > table SQLite is written in C. The C language does not have exceptions. > Аfter the exception is thrown > If I continue the execution You must return from the callback function normally, or

Re: [sqlite] Why operations with in-memory DB are much faster than with on-disk DB?

Yuri wrote: > I noticed that my DB import process is much slower when run on the DB on > disk, vs. in memory. It's possible that you forgot to wrap a single transaction around all changes. Otherwise, you get an automatic transaction for every command, which requires disk synchronization every tim

Re: [sqlite] Filtering groups by non-grouped field.

John Found wrote: > Also, it seems max(b = ?1) will do the trick as well as count(b = ?1) > > And here another question appears. What is more efficient? In SQLite, both are equally efficient. Use whatever makes the query easier to understand. Regards, Clemens ___

Re: [sqlite] Filtering groups by non-grouped field.

E.Pasma wrote: > select group_concat(b) as list > from t > group by a > having count(b=?1) > ; In SQLite, a boolean expression returns 0 when false, and count(0) is 1. You have to generate a NULL for failed matches, or use another function like sum() or max() that can filter out zeros. Regards,

Re: [sqlite] Filtering groups by non-grouped field.

I wrote: > But you need to find some aggregate function that can do the filtering. HAVING SUM(b = ?1) (In a database with proper booleans: "HAVING SUM(CASE WHEN b = ? THEN 1 END) > 0") Regards, Clemens ___ sqlite-users mailing list sqlite-users@maili

Re: [sqlite] Filtering groups by non-grouped field.

John Found wrote: > Clemens Ladisch wrote: >> select >> group_concat(b) as list >> from t >> where a in (select a >> from t >> where b = ?1) >> group by a; >> >> But you will not be able to avoid the

Re: [sqlite] Filtering groups by non-grouped field.

John Found wrote: > i.e. how to select only the groups that contain > some value in the set of values in a column not > specified in group by clause. > > select > (select group_concat(b) from t t1 where t1.a = t2.a) as list > from t t2 > where b = ?1; Similarly: select gro

Re: [sqlite] .separator and .mode commands interactions

Luc Charansonney wrote: > sqlite> .separator tabs sqlite> select 1, 2; 1tabs2 > sqlite> .import mydata.txt mytable > Error: multi-character column separators not allowed for import You should have used ".separator \t". > So I fall back on my feet by using .mode instead of .separator: > sqli

Re: [sqlite] Calling sqlite3_create_module from a DLL

Deon Brewis wrote: > I have a DLL that makes a series of sqlite3_create_function_v2 calls. > It all works fine and the DLL is usable. > > I've tried adding a sqlite3_create_module into the same DLL, but I get > an assert in: > > sqlite3_mutex_try > over here: > assert( sqlite3GlobalConfig.mutex

Re: [sqlite] sqlite3_get_table() failed with error "database is locked"

ldl wrote: > Multiple applications called sqlite3_get_table() to read data in a same db > file at the same time, got error "database is locked" Somebody has a write lock. > Why read-only access is locked? Because one access is not read only. > How to resolve the issue? Find out who locked it.

Re: [sqlite] sqlite 3.25.1 windows function. So it should be?

Djelf wrote: > SQLSTATE[42803]: Grouping error: 7 ERROR: column "t.v3" must appear in the > GROUP BY clause or be used in an aggregate function > > It seems to me that sqlite should issue a similar message. This is allowed for compatibility with MySQL. And there is a case with min()/max() where

Re: [sqlite] sqlite 3.25.1 windows function. So it should be?

Djelf wrote: > INSERT INTO t (v1,v2,v3,v4) VALUES > (1,1,1,0),(1,1,0,1),(2,1,0,10),(3,1,0,100),(3,1,0,1000); > > SELECT > v1,v2,sum(v3+v4) OVER (PARTITION BY v2 ORDER BY v1) as val > FROM t > GROUP BY v1,v2; > > v1v2 val > 1 1 1 > 2 1 10010 > 3 1

Re: [sqlite] Bug report: Window functions in VIEWs broken in 3.25.1

Bjoern Hoehrmann wrote: > Using the sqlite-tools-linux-x86-3250100 Linux binaries I find that > Window functions in VIEWS behave differently from PostgreSQL 9.6 and > from what I expect. > > DROP TABLE IF EXISTS example; > CREATE TABLE example(t INT, total INT); > INSERT INTO example VALUES

Re: [sqlite] How to import TSV table with double quote in it without having to escape double quote

Peng Yu wrote: > I don't want to escape the quote charaters in the input. Is there still > a way to import quote characters into a sqlite3 table? The CSV import hardcodes " as quote. You could try the ASCII import instead. Regards, Clemens ___ sqlite-

Re: [sqlite] foreign_keys = 0N with Entity Framework 6.2.0

Urs Wagner wrote: > I get three lines With some other tool, or executed through the EF? Regards, Clemens ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] foreign_keys = 0N with Entity Framework 6.2.0

Urs Wagner wrote: > Simon Slavin: >> On 12 Sep 2018, at 2:04pm, Urs Wagner wrote: >>> The following code is returning 0. Why? >>> >>> ExecuteStoreQuery("PRAGMA foreign_keys;").First(); >> >> You coerce the result of the call into an integer. Can you make the call >> and display (or use a debugge

Re: [sqlite] sqlite3_column_* with error handling

Keith Medcalf wrote: > In the case of a BLOB if a NULL pointer is returned the error code must > be retrieved and then if and only if the bytes counter is greater than > 0 is the error valid. > > Does this mean that if you are retrieving the value of a blob via the > colmn_blob interface you should

Re: [sqlite] sqlite3_column_* with error handling

Keith Medcalf wrote: > 6) If the column type is SQLITE_BLOB > a) Retrieve the column value pointer using column_blob > b) If the returned pointer is NULL, then an error has occurred "The return value from sqlite3_column_blob() for a zero-length BLOB is a NULL pointer." Regards, Cleme

Re: [sqlite] Strategies to reduce page-loads?

t...@qvgps.com wrote: > The larger the db, the slower is the fetching! > > My assumption is, that in the big db, these 1000 lines are just spread over a > much higher count of pages. > So more page-loads resulting in more time. Correct. > We changed page_size to the maximum value of 64k and it b

Re: [sqlite] Why trigger (UPDATE) does not work?

Csányi Pál wrote: > CREATE TRIGGER YearsRemainingMonthsDays AFTER UPDATE OF EndDate ON > MyLengthOfService BEGIN UPDATE MyLengthOfService SET Years = ( with ... says: | The WITH clause cannot be used within a CREATE TRIGGER. Regards, Clemens

Re: [sqlite] Invalid Blob Length Error Message

Casey Rodarmor wrote:> Hi all, > unrecognized token: "x'01234' > > I know now that the problem was that the blob's length was not a > multiple of two. However, the error message didn't give me an > indication of that. Would it be possible to make the error message > more explicit, to help users deb

Re: [sqlite] PRAGMA case_sensitive_like

Chris Locke wrote: > Is there a reason for it being write only? This pragma just installs a different LIKE() function, and there is no easy mechanism to read the function pointer back. Regards, Clemens ___ sqlite-users mailing list sqlite-users@mailing

Re: [sqlite] Safe sqlite over remote filesystem?

Simon Slavin wrote: > On 14 Aug 2018, at 3:09pm, Clemens Ladisch wrote: >> However, there are other file >> operations that are properly synchronized, e.g., it is not possible for two >> NFS clients to create a directory with the same name. > > You are correct. But t

Re: [sqlite] Safe sqlite over remote filesystem?

Wout Mertens wrote: > I know that multi-writer sqlite and NFS don't play well with each other. > > However, I wonder if some constraints could be added that would make this > situation safe. NFS locking implementations tend to be bad. However, there are other file operations that are properly syn

Re: [sqlite] Foreign Key error

J Decker wrote: > CREATE TABLE `option4_name` (`name_id` char(36) NOT NULL,`name` > varchar(255) NOT NULL default '' CONSTRAINT `name` UNIQUE) > FOREIGN KEY (`name_id`) REFERENCES `option4_name`(`name_id`) > foreign key mismatch - "option4_map" referencing "option4_name" name_id must be the pri

Re: [sqlite] Error: foreign key mismatch - "loan" referencing "user"

Markos wrote: > CREATE TABLE user ( > id_user integer PRIMARY KEY, > ... > CREATE TABLE loan ( > ... > FOREIGN KEY(id_admin_loan, id_admin_devolution) REFERENCES user(id_user, > id_user) I do not understand what this is trying to accomplish. Why not two single-column FK constraint

Re: [sqlite] sqlite on freertos/fatfs : SQLITE_NOTADB during create table

Bram Peeters wrote: > He reads a page from the file in sqlite3PagerSharedLock, but the file is > still 0 so the page is all zeros. An empty file and a file filled with zeros are two different things. Does the file system return SQLITE_IOERR_SHORT_READ? Regards, Clemens

Re: [sqlite] I'm getting a compiler error on a SQLIte build on Ubuntu 16.04 (which surprises me)

Rob Willett wrote: > I removed two instances of -O2 from the Makefile and, lo and behold, it > compiles. > > Sadly my database to check is approx 80GB which could be interesting. The bottleneck is I/O speed; it does not matter whether sqlite3_analyzer uses ten or twenty microseconds before waiti

Re: [sqlite] I'm getting a compiler error on a SQLIte build on Ubuntu 16.04 (which surprises me)

rob.sql...@robertwillett.com wrote: > gcc: internal compiler error: Killed (program cc1) This is a compiler bug. Check if updating gcc to a current version helps. > Please submit a full bug report, > with preprocessed source if appropriate. > See for instructions. Otherwise, do this. Regards

Re: [sqlite] To JSON or not to JSON

Cecil Westerhof wrote: > my SQLite database contains for example: > > "5BF19111-9FD5-48CA-B919-A09411346A87""[ > ""The journey of a thousand miles > must begin with a single step. > > - Lao Tzu"", > ""Welke stap kun je vandaag zetten, > om dat verre doel te bereiken?"" > ]""2018-07-07"

Re: [sqlite] CASE and NULL

Andy Goth wrote: > The expression "x = NULL" is meaningless since it will always evaluate > to NULL, which CASE interprets as untruth, hence "WHEN NULL THEN" will > never accomplish anything. > [...] > So I'm wondering: can we do better? The expression "x = x" will fail for NULL, but succeed for e

Re: [sqlite] insert or replace performance with self "references" column

Allen wrote: > create table Transactions (Id integer primary key not null, Parent references > Transactions(id), Body varchar); > create index Parent_Index on Transactions (Parent); > > EXPLAIN QUERY PLAN insert or replace into Transactions (Id, Parent, Body) > values (?1, ?2, ?3); > 23 0 0 SCAN

Re: [sqlite] Unexpected sqlite3_trace_v2 sqlite3_expanded_sql output.

Lodewijk Duymaer van Twist wrote: > I'm tracing queries that my application makes using sqlite3_trace_v2 and > sqlite3_expanded_sql in the callback. The application uses sqlite 3.22.0. > > At some point the software will call sqlite3_exec with the following > statement: > BEGIN TRANSACTION; > DELE

Re: [sqlite] Database is malformed but no further information

Chris Brody wrote: > My understanding is that mobile apps are not 100% predictable since > they may be randomly suspended or terminated, at any point of time. But SQLite is designed so that even killing the process will not corrupt the database, as long as the storage device works correctly. > On

Re: [sqlite] Performance of writing blobs

Dominique Devienne wrote: > In JOURNAL mode, new data goes to DB file directly, and modified pages go to > the JOURNAL file. > And since here this is INSERT-only, from empty tables, I assumed pages copied > to the JOURNAL > file should be minimal. Yes. You can check the journal size with PRAGMA

Re: [sqlite] Check Constraint

Cecil Westerhof wrote: > I want to create a field that only has values that consist of letters, > numbers end '-'. So no spaces, quotes or special characters like: '@%$!'. > What is the best way to write this check constraint? The GLOB operator has inverted character classes. So the field is vali

  1   2   3   4   5   6   7   8   9   10   >