Re: [sqlite] latest sqlite 3 with Delphi 5 professional

dean gwilliam wrote: > I'm just wondering what my options are here? Many. What exactly do you want to know? What is your goal? Regards, Clemens ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] Serialization and threading

Reza Housseini wrote: > The execution looks as follow: > > sqlite3_step(beginstmt); > sqlite3_step(vectorstmt); > sqlite3_step(intrstmt); > sqlite3_step(commitstmt); So your program does not check error codes? Regards, Clemens ___

Re: [sqlite] shell crashes when there is no column name

Klaas V wrote: > Clemens wrote: >> $ sqlite3 >> SQLite version 3.8.3 2014-01-11 12:52:25 >> Enter ".help" for instructions >> Enter SQL statements terminated with a ";" >> sqlite> .header on >> sqlite> values(1); >> (null) >> 1 >> sqlite> .mode html >> sqlite> values(1); >> Segmentation

[sqlite] shell crashes when there is no column name

Hi, the shell does not always check the return value of sqlite3_column_name() for being NULL: $ sqlite3 SQLite version 3.8.3 2014-01-11 12:52:25 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> .header on sqlite> values(1); (null) 1 sqlite>

Re: [sqlite] Do I really need rollback after sqlite3_step returns BUSY?

Woody Wu wrote: > The manual says that it should to rollback after sqlite3_step() returns > SQLITE_BUSY as long as > the current statment is in a transaction. Is this true? Yes. If you have a transaction, you must _eventually_ commit or rollback. > Why I cannot just sleep for a while and redo

Re: [sqlite] Sqlite as a platform performance comparison tool

Max Vlasov wrote: > A thought came to compare two computers of different platforms (ie > i386 vs ARM) using uniform approach. We take two binaries of the same > sqlite version compiled with the best c compilers for both platforms > and compare the time spent for identical operations using memory

Re: [sqlite] Hook / Trigger for Primary Key / Unique Error

Raheel Gupta wrote: >INSERT INTO checksums (1000, "abcdefghijklmnop") ON CONFLICT IGNORE BUT >UPDATE data SET isUnique = OLD.checksums.i > >Is this possible with HOOKS / triggers or anything at all ? It is possible with the obvious and simple solution: check for uniqueness with a SELECT, then

Re: [sqlite] memcpy usage in SQLITE

jitendar kumar wrote: >Actually it has been customized in case of the memcpy() with checks for Src >= NULL and length = 0. and whever at run-time it encounters, alarm is >generated. > >So, we had a concern for future use such that any case where such condition >can occur and also segfault crash

Re: [sqlite] virtual tables and group by: how could we prevent to retrieve all rows from a vtab and sort them later ?

James K. Lowden wrote: > Clemens Ladisch <clem...@ladisch.de> wrote: >>> Is there a way to force rows to be dispatched (using the group by) >>> and aggregated on the fly instead of being stored, sorted and then >>> aggregated? >> >> SQLite can

Re: [sqlite] nested query doesn't like aggregates in subquery?

James K. Lowden wrote: > Simon wrote: >> select datetime(bucket*plen,'unixepoch','-5 hour') dt, >>(select price from last_trades where tid=opentr) open, > > Unless I misread it, that subquery returns the price for every row in > last_trades whose tid is equal to

Re: [sqlite] Can I use any valid expression in a SQL ATTACH statement

Simon Slavin wrote: > On 19 Dec 2013, at 2:47pm, Joseph Mokos wrote: >> ATTACH fn() AS somedb; >> >> I receive: SQL prepare error: invalid name: "fn" >> >> If I run "SELECT fn()", I receive the expected results so it seems my >> function is working properly. > > If it

Re: [sqlite] Unexpected SELECT results

Louis Jean-Richard wrote: > I run into an unexpected result from a SELECT on a view > in one of my schema for which ... I have created the following minimal example: sqlite> create table t(x,y); sqlite> insert into t values(1,'a'); sqlite> insert into t values(2,'b'); sqlite> select min(x), y

Re: [sqlite] virtual tables and group by: how could we prevent to retrieve all rows from a vtab and sort them later ?

Hick Gunter wrote: > You are in error on number 4. > > SQLite reads your CFL table once and performs the aggregation in > a temporary table that holds 1 entry per "contract" i.e. about > 1000 rows of max 16 byte records which I estimate to using less than > 64k. > > For each record read, SQLite

Re: [sqlite] order by col desc limit 1 logically equivalent to max(col)

Dominique Devienne wrote: > In the two queries below, there's a 5x performance difference. > select max(id) from t100m limit 1; > select id from t100m order by id desc limit 1; > But logically, it seems to me that the limit 1 on the order by is logically > equivalent to a min or max depending on

Re: [sqlite] virtual tables and group by: how could we prevent to retrieve all rows from a vtab and sort them later ?

Perrin, Lionel wrote: > I plan to use sqlite to implement an 'aggregation tool'. Basically, > the design would be the following: > > 1. I implement a virtual table CFL(contract, amount) which may provide >up to 1 billion unsorted rows. > > 2. The aggregation phasis will be defined at run time

Re: [sqlite] Support for read-only DB on Windows Phone SD Card

Gavin Harriss wrote: > I'm wondering if there's plans for the Windows Phone implementation to > support read-only databases on the SD card? > > > I expect that the SQLite implementation for the phone tries to open the > > database using standard C file API rather than using the Storage > > objects

Re: [sqlite] Can FTS snippet function return multiple fragments from one column in one row?

Lubos Staracek wrote: > From what I've seen here: > http://www.mail-archive.com/sqlite-users@sqlite.org/msg57661.html I assume > that snippet would return one text fragment for each column that have > matched searched phrase. Snippet is called once for each matching record. So it returns at most

Re: [sqlite] RTrees and query speed

skywind mailing lists wrote: > Assume I have the following tables: > > CREATE TABLE A (ID INTEGER PRIMARY KEY, Latitude, Longitude, Altitude); > CREATE VIRTUAL TABLE B USING RTree (ID, FromLatitude, TillLatitude, > FromLongitude,TillLongitude); > > According to the RTree documentation this query

Re: [sqlite] Strange comparison with CAST behavior

Luís Simão wrote: > SELECT 123='123'; // 0 > SELECT CAST(123 AS NUMERIC)='123'; // 1 ??? > > How is this possible? Plain 123 or '123' has affinity NONE. CAST(123 AS NUMERIC) has affinity NUMERIC, so the string gets automatically converted for the comparison. See

Re: [sqlite] Is this a proper call?

Igor Korot wrote: > Is this a proper call to execute: > > if( sqlite3_exec( m_handle, "PRAGMA foreign_keys = OFF", NULL, NULL, 0 ) != > SQLITE_OK ) Yes. > The problem is that after successful execution of such code, I am > still getting error: > "foreign key constraint failed" when executing

Re: [sqlite] Concrete example of corruption

L. Wood wrote: > /Users/lwood/Desktop/folder/db.sqlite > > Suppose I can only move/rename the *folder*. Suppose I never mess with the > folder's contents. > > Can you name me a concrete example of corruption that could occur? 1. Open "/Users/lwood/Desktop/folder/db.sqlite". 2. Rename the folder.

Re: [sqlite] Bug in sqlite.exe?

Marcus Grimm wrote: > You have the DB file in ProgramData, maybe you are a victim of the > windows file virtualization ? Given the symptoms, this is very likely. See . Regards, Clemens ___ sqlite-users mailing

Re: [sqlite] Need advice on using nested selects in JOIN statements as a logical 'AND'

Tristan Van Berkom wrote: > Are the JOIN statements equal to the logical AND statements, Yes. > for all practical purposes ? If you drop all those superfluous LEFT OUTER and IS NOT NULL parts, the database will be able to optimize the first query (the one without subqueries) better. Regards,

Re: [sqlite] Need advice on using nested selects in JOIN statements as a logical 'AND'

Tristan Van Berkom wrote: > When using an INNER join, the engine does something like this: > > o Create a data set that is table_1 * table_2 * table_3 rows > large > > o Run the constraints on what might be multiple matching rows > in the resulting huge data set (even if I nest the

Re: [sqlite] count (*) performance

veeresh kumar wrote: > I see that in release history for 3.8.1 below item. > "Estimate the sizes of table and index rows and use the smallest applicable > B-Tree for full scans and "count(*)" operations." > > Does it mean that performance of count(*) has been improved in 3.8.1 Yes. > and if yes

Re: [sqlite] Failed test on aarch64

Jan Staněk wrote: > I'm trying to build sqlite for aarch64 (ARMv8) and one of the expression > tests is failing (specifically e_expr-31.2.4) with: >> Expected: [integer -9223372036854775808] >> Got: [integer 9223372036854775807] > From the comment, I gather that this should test correct

Re: [sqlite] Nested transactions

Igor Korot wrote: > If I understand correctly, I can do this (pseudo-code): > > BEGIN TRANSACTION; > // some SQL statements > BEGIN TRANSACTION; sqlite> begin; begin; Error: cannot start a transaction within a transaction > This scenario will not end up with with unfinished transaction and I >

Re: [sqlite] WAL and long-lived prepared statements

Sascha Sertel wrote: > While our long lived prepared statements are reset many times throughout > their lifecycle, there are several of them in use at the same time, and > probably never in a state where all of them are reset. The statements feed > a UI and are constantly refreshed, requeried, and

Re: [sqlite] WAL and long-lived prepared statements

Simon Slavin wrote: > All statements are entirely enclosed in a transaction. No, automatic transactions start with the first sqlite3_step() and end with either sqlite3_reset() or sqlite3_finalize(). Regards, Clemens ___ sqlite-users mailing list

Re: [sqlite] 2 more questions

Igor Korot wrote: > 1. I am working with C++ using C API to access the DB. > What I did was: > > upon startup - read the data in the std::vector<>, then use > std::sort() to sort this vector appropriately. > > Now, today it hit me that I can use "ORDER BY" SELECT clause to > retrieve data as

Re: [sqlite] query optimization

d b wrote: > 1. delete from emp where key = '123'; > 2. delete from emp where key = '123' and name = 'abc'; > > if Key available, execute 1st query. if key and name available, execute 2nd > query. What do you mean with "available"? Regards, Clemens

Re: [sqlite] Trouble with Journal_Mode Pragma

Kevin Benson wrote: > Maybe it should be: > > TempDB.ExecSQL('PRAGMA journal_mode = OFF;'); > > (Note the additional semicolon in the string literal) No, semicolons are needed only as delimiter between statements (and only if multiple statements are allowed at all). Regards, Clemens

Re: [sqlite] Trouble with Journal_Mode Pragma

Stephen Chrzanowski wrote: > I'm not sure why this code is breaking: > > procedure SaveDatabaseTo(fName:string); > var > TempDB:tsqlitedatabase; > begin > TempDB:=TSQLiteDatabase.Create(fName); > TempDB.ExecSQL('PRAGMA journal_mode = OFF'); > db.Backup(TempDB); > tempdb.free; > end; > >

Re: [sqlite] Intended use case for 'without rowid'?

Stephen Chrzanowski wrote: > The lack of the last_insert function is kind of concerning, especially if > you're making a "Person" list and you're simultaneously adding their > contact info in the next query in your program. You've got no reliable > method of getting information back on the person

Re: [sqlite] GROUP BY

Giuseppe Costanzi wrote: > order_idissuedcompany dep_id qty,dispatch surplus > "1""12-11-2013" "Siemens" "1" "6""4""2" > "1""12-11-2013" "Siemens" "1" "2""2""0" > "2""13-11-2013" "Siemens" "2" "10" "10"

Re: [sqlite] If prepare failed do I have to call finalize?

Igor Korot wrote: > if( ( result = sqlite3_prepare_v2( handle, query, -1 , 0 ) ) != > SQLITE_OK ) > // Should there be call to sqlite3_finalize. I.e. can I safely assume that > since it failed all memory is not being allocated says: | *ppStmt is left

Re: [sqlite] Disparity between query time in SQLite Administrator and Flash Pro

SongbookDB wrote: > The database has an index that is being used according to EXPLAIN QUERY > PLAN in SQLite Administrator. Does Flash use the same SQL code? Probably not. Try "SELECT sqlite_version();". Regards, Clemens ___ sqlite-users mailing list

Re: [sqlite] Which constraint is being violating??

Rafa de Miguel wrote: > Is there a way to know which constraint is being violated when you receive > the message constraint error 19: > > In example: my_column_name UNIQUE constraint Upgrade SQLite: sqlite> create table t(x unique); sqlite> insert into t values(1); sqlite> insert into t

Re: [sqlite] Date function accepts only DD for date string

Navaneeth K N wrote: > select date('2013-11-04') -> Works well > select date('2013-11-4') -> Not working > > Is there a way to make the second form working? Only by inserting a zero into the string (which isn't easy with the built-in SQL functions). Regards, Clemens

Re: [sqlite] Second ORDER BY statement

SongbookDB wrote: > I'd now like to order the Language = "" rows by another column, "Artist", > but cannot crack how to restructure the query to accommodate this. > > SELECT * FROM > (SELECT * > FROM table1 > WHERE Language !="" COLLATE NOCASE > ORDER BY Language COLLATE NOCASE) > UNION ALL >

Re: [sqlite] Extra Space Required for Index ?

Raheel Gupta wrote: > CREATE TABLE users ( > uid INTEGER PRIMARY KEY AUTOINCREMENT, > username VARCHAR(100) UNIQUE NOT NULL DEFAULT '', > email VARCHAR(255) UNIQUE NOT NULL > > I wanted to know if I create an INDEX for the column "email" what isg going > to be the extra space the index will occupy

Re: [sqlite] Trigger slows down application start-up

Igor Korot wrote: > After those triggers had been created the application start-up time > significantly increased. > > On start-up it opens connection to the database and queries the table > that is not part of the trigger. > > Any idea what to look for? Are you creating one connection, or do you

Re: [sqlite] Is there a function that return an autoimcremented value?

Stephan Beal wrote: > On Fri, Oct 25, 2013 at 12:51 PM, Clemens Ladisch <clem...@ladisch.de>wrote: >> CREATE TEMP TABLE t(playerid, leagueid, auto_rank INTEGER PRIMARY KEY); >> INSERT INTO t(playerid, leagueid) SELECT players.playerid, %d FROM ...; >> INSERT INTO leagu

Re: [sqlite] Is there a function that return an autoimcremented value?

Igor Korot wrote: > On Fri, Oct 25, 2013 at 2:12 AM, Clemens Ladisch <clem...@ladisch.de> wrote: >> Igor Korot wrote: >>> what I'm looking for is a way to populate the current_rank and >>> original_rank in the leagueplayers table. They should come

Re: [sqlite] Is there a function that return an autoimcremented value?

Igor Korot wrote: > what I'm looking for is a way to populate the current_rank and > original_rank in the leagueplayers table. They should come up as > auto-incremented values. Autoincrementing works only for INTEGER PRIMARY KEY columns. > Is there a way to do that or I will have to change the

Re: [sqlite] Getting the row count when using the sqlite3 library

Tim Streater wrote: > select count(*),x,y,z from sometable where …; > > or is that a bad idea? An aggregate function prevents you from getting the individual records: sqlite> create table sometable(x,y,z); sqlite> insert into sometable values (1,2,3), (4,5,6); sqlite> select count(*),x,y,z

Re: [sqlite] Force float (instead of double) for storage

Michael Foss wrote: > The processor where I am running my instance of SQLite has a problem in > that it serializes double-precision floating point values incorrectly. If > the 8-byte double should be stored as 0x123456789ABCDEF0, it is instead > stored as 0x9ABCDEF012345678. > > Other solutions I

Re: [sqlite] SQLite keeps on searching endlessly

Raheel Gupta wrote: > > CREATE INDEX map_index ON map (n, s, d, c, b); > > > > SELECT n, c, b, s FROM map WHERE s <= '326' AND s NOT IN (0) AND d = '15' > > AND n >= '15591116' ORDER BY n ASC LIMIT 0, 32768 > > 0|0|0|SEARCH TABLE map USING COVERING INDEX map_index (n>?) (~4166 rows) >

Re: [sqlite] Table constraints

Joseph L. Casale wrote: > CREATE TABLE t ( > id INTEGER NOT NULL, > a VARCHAR NOT NULL COLLATE 'nocase', > b VARCHAR COLLATE 'nocase', > c VARCHAR CHECK (c IN ('foo', 'bar', NULL)) COLLATE 'nocase', > PRIMARY KEY (id) > ); > > How does one

Re: [sqlite] Feature Request: Binding Arrays

Dominique Devienne wrote: > select * from some_table where some_column in (...) > > 2) In other places we synthesize the query text by splicing list.join(", ") > in the in (list) where clause. > > Both solutions are unsatisfactory, because ... 2) constantly reparse and > prepare queries, which can

Re: [sqlite] Trigger SQL and database schema

Sqlite Dog wrote: > seems like SQLite is not checking trigger SQL for invalid column names > until execution? No. > Is there a way to force this check? Not without compiling the respective INSERT/UPDATE/DELETE statement. > The problem: trying to find out which indices, triggers and views

Re: [sqlite] Using several .commands from a Script

John wrote: > I am having problems creating a "do shell script" command which includes > both the import command and the insert command. The reason they need to be > combined is that the shells instance ends with the command. > > do shell script ("sqlite3 " & databasePath & " .separator \"||\" ;

Re: [sqlite] COMMIT in SQLite

Paul Harris wrote: > Many years ago, Igor mentioned that you should always reset/finalize any > prepared statements before calling COMMIT. > > I am wondering, is this still true? Yes. There was a change in 3.7.11, but not with COMMIT itself: | * Pending statements no longer block ROLLBACK.

Re: [sqlite] How to use my own python function in a SQLite WHERE clause?

Bao Niu wrote: > SELECT * FROM myTable WHERE MyDate > MyModule.ChineseDate("兔年八月十五") You cannot use Python function directly in SQL. You have to convert from the Python type to the corresponding SQL type. I don't know how the type mapping works exactl, but it should probably look like this:

Re: [sqlite] Major performance difference when joining on FTS4 table's docid column versus custom id column

Per Vognsen wrote: > Am I wrong to think that joining on docid should be as fast as joining on > indexed integer columns in other tables? Looking up a record by docid is faster than non-FTS lookups on other columns, but the virtual table implementation still has to go through a separate query to

Re: [sqlite] EncFs + sqlite3

Paolo Bolzoni wrote: > What do you mean with "if you do not ATTTACH databases"? EncFS does not implement .fsyncdir, which is used by SQLite when deleting the master journal file. Such a file is used for transactions when there are multiple database files, i.e., when you have used ATTACH. But I

Re: [sqlite] EncFs + sqlite3

Paolo Bolzoni wrote: > I was wondering, is using sqlite3 under EncFs safe? > For "safe" I mean is the db is strong against data corruption > as in a usual filesystem? EncFS implements the .fsync callback but not .fsyncdir, so the deletion of the master journal is not synchronized, so your data

Re: [sqlite] WAL files and PERL question

Larry Brasfield wrote: > If I go into the firefox add on and run a checkpoint , the 2 new invoices > get flushed from the WL to the db file and then my program sees them > what am I doing wrong where my script is only looking at the db file and not > the WAL file? An explanation would be that

Re: [sqlite] COMMIT or ROLLBACK failure

Igor Korot wrote: > I need to check if the COMMIT is successful. > But what should I do if it fails? If the database is currently locked, you should try again later. (But this would be better handled with a busy handler.) If there is some I/O error that prevents you from writing, the COMMIT

Re: [sqlite] select with date

jwzumwalt wrote: > I have valid entries for the current month, what am I doing wrong? > > SELECT * FROM "entry" WHERE > bankdate > date('now','end of month','-1 month') > AND bankdate < date('now','start of month','+1 month') What you are doing wrong is that you have not

Re: [sqlite] Query preperation time does not scale linearly with growth of no. of tables

Harmen de Jong - CoachR Group B.V. wrote: > http://www.coachrdevelopment.com/share/callstack_tree.html > > This shows most time is spend on sqlite3CodeRowTriggerDirect. I'd guess the actual culprit is the loop in getRowTrigger (which does not show up because it is inlined): /* It may be that

Re: [sqlite] Question about aggregate functions used multiple times

James Powell wrote: > SELECT MAX(X) AS MaxX, MAX(X)/MIN(X) AS RatioX > > does the MAX(X) get calculated twice, or does SQLite identify that it > is the same number and do it only once? At least in version 3.8.0.2, it gets calculated only once. Please note that SQLite can optimize MIN/MAX

Re: [sqlite] FTS4 + INSERT OR REPLACE = Not replacing but adding item

klo wrote: > Just changed a table of mine to support fts4 so that I can do searches > on it Please note that, depending on the implementation, virtual tables are not a full replacement for 'normal' SQLite tables and will not support all features. > and noticed that INSERT OR REPLACE is not

Re: [sqlite] sqlite support not recognized on debian lighttpd

GoogleWell wrote: > I installed sqlite on my debian lighttpd (sqeeze) server. It is needed for > an application (business directory) used on a Joomla installation. This > particular component (SOBIPro) keeps telling me that sqlite is not > installed on my server. SQLite is an embedded database

Re: [sqlite] SQLite - potential bug with multiple leftjoin/groupby/count

Harry Beezhold wrote: > Sqlite - What a cool product! Do you really think buggy products are cool? ;-) > The following is a description of an apparent bug in > the calculation of a row count of a left joined table. > The leftjoin/count technique seems to work for each join/count, separately.

Re: [sqlite] Is SQLite a DBMS?

Simon Slavin wrote: > On 2 Sep 2013, at 1:17pm, itli...@schrievkrom.de wrote: >> At university I learned, that a RDBMS also have these management >> functionality like "user access", "user roles", "user password", >> access restrictions ... and all that stuff. > > Whoever told you that was wrong.

Re: [sqlite] to encrypt sqlite db

Ulrich Telle wrote: > Am 02.09.2013 06:11, schrieb Etienne: >> wxSQLite3 does implement AES in ECB mode > > Wrong. CBC mode is used. Inside one page. If we ignored the actual block size, and viewed the entire database as a stream to be encrypted by a cipher with a block size identical with the

Re: [sqlite] to encrypt sqlite db

Ulrich Telle wrote: > Am 31.08.2013 22:01, schrieb Etienne: >> I simply wanted to warn the OP that wxSQLite, while free, does NOT use >> salts: > > Well, that's not completely true. The encryption extension coming with > wxSQLite3 uses a different IV (initial vector) for each database page. > True

Re: [sqlite] Question about index usage

Doug Nebeker wrote: > CREATE UNIQUE INDEX Ind_DevProps_CompIDPropID on DevProps (CompID, PropID); > > When I check the query plan for this statement: > > SELECT CompID FROM DevProps WHERE PropID=33 > > it looks like it will be doing a table scan. My version of SQLite uses a covering index, but is

Re: [sqlite] Changed behavior from 3.7.17 to 3.8.0.

Peter Aronson wrote: > a SAVEPOINT command fails with an open statement handle used to > execute PRAGMA journal_mode = off at SQLite 3.8.0, but appears to > succeed at SQLite 3.7.17. This does not appear to be documented. says: | An implicit

Re: [sqlite] Splitting a monolithic table into two related ones.

Flakheart wrote: > I need to split a table into two as the current monolithic structure is > incredibly wasteful but don't know how to do such a possibly complicated > thing. I remember an analyser available years ago for MS Access database > that did this but none found for SQLite. > > My bloated

Re: [sqlite] Table Creation Behaviour!!!

techi eth wrote: > I have open the connection, created the table by using CREATE TABLE IF NOT > EXISTS , doing some operation & closing the connection. > > If I again open the connection & try to do table creation again (With same > table name & same database file) then what will be the behavior?

Re: [sqlite] T-SQL to retrieve needed records

jdp12383 wrote: > This table stores webcam recordings. Each record is a recording up to 3 min. > I am trying to retrieve one record per continuous recording. If there is > gap more than 3 min then it would be considered as a separate recording. > > CREATE TABLE recordings ( > [key]

Re: [sqlite] auto_vacuum default setting

Stephen Chrzanowski wrote: > On Thu, Aug 29, 2013 at 3:03 AM, Clemens Ladisch <clem...@ladisch.de> wrote: >> 3. auto_vacuum will increase fragmentation > > I thought it did a complete dump to a new file? <http://www.sqlite.org/pragma.html#pragma_auto_vacuum&g

Re: [sqlite] auto_vacuum default setting

techi eth wrote: > When I look through details about PRAGMA auto_vacuum,I think it is always > beneficial to have auto_vaccum set to Full so that whenever delete happens we > have shrink of size. > > I wanted to know is their any benefit to have this to none in default > instead of full. 1.

Re: [sqlite] Update field from standard input with sqlite3 command line utility

luis montes wrote: > I'm trying to do a simple database update from a bash script. It seems > to me that I should be able to do something like this from the command > line: > > cat file.xml|sqlite3 database.db 'update table1 set column3=? where > column1="some name";' Escaping arbitrary data in

Re: [sqlite] BETWEEN and explicit collation assignment

Niall O'Reilly wrote: > What Simon Slavin seems (to me) to be pointing out is that the > counter-intuitive behaviour observed by the OP (Clemens Ladisch) > needs either to be corrected or explicitly documented It worked correctly (i.e., as documented) before 3.7.15, so it's a b

[sqlite] BETWEEN and explicit collation assignment

Hi, the documentation says (on ): | The expression "x BETWEEN y and z" is logically equivalent to two | comparisons "x >= y AND x <= z" and works with respect to collating | functions as if it were two separate comparisons. However, this is not

Re: [sqlite] Create DB in SDRAM FOLDER

Gianni Sassanelli wrote: > I need to create DB in a SDRAM Folder This has nothing to do with SQLite itself. > 2) I don't want delete db If I uninstall my main app So you think your app is more important than your users think? Whatever, the SD card can be erased easily ... > I solve this

Re: [sqlite] How to "upgrade" a DB without write access to its file, or to the direction it's in?

Dominique Devienne wrote: > We can of course copy the db file somewhere else with r/w access, or copy > the DB into an in-memory DB (for each table, create table memdb.foo as > select * from dskdb.foo) and upgrade and read that one instead, but I was > wondering whether there's another better

Re: [sqlite] Query on database back-up:

> techi eth wrote: >> 1. "Any database clients wishing to write to the database file while a >> backup is being created must wait until the shared lock is >> relinquished." > > Example 1: Loading and Saving In-Memory Databases will not fulfill > this shortcoming. In-memory databases cannot be

Re: [sqlite] Comparing two tables column by column

Simon Slavin wrote: > On 29 Jul 2013, at 4:03am, Fehmi Noyan ISI wrote: >> One point I forgot to mention; the number of columns is unknown. > > There is no way in SQL to say "Give me the contents of all the columns of a > row of table in an unambiguous format.". Well, just

Re: [sqlite] sqlite on ESXI hypervisor

1 1 wrote: > I've tried to run the latest version of sqlite3 on esxi 5.0 (VMware > hypervisor), but unsuccessfully. Strace shows "fcntl function not > implemended". Apparently, VMFS does not implement file locking. Try using the unix-dotfile VFS, or unix-none if you can guarantee that the

Re: [sqlite] Is REAL the best universal data type?

Max Vlasov wrote: > On Tue, Jul 23, 2013 at 1:32 PM, Clemens Ladisch <clem...@ladisch.de> wrote: >> But REAL will sort the strings '1', '10', '2' wrong. > > What do you mean by "wrong"? > > 1, 2, 10, something > that's what I wanted So you actually want

Re: [sqlite] Is REAL the best universal data type?

Max Vlasov wrote: > I've created a kind of triple storage base with Sqlite db as the container. > Basically it's several tables implementing Object-Propery-Value metaphor. > There's only one field for data so thinking about generality I assumed that > the type for the data field should be TEXT of

Re: [sqlite] FTS4 search for terms inside a word

Marco Bambini wrote: > I have a virtual FTS4 table and I would like to search for some terms inside > a word, is that possible? No. > For example if a column contains "mysuperword mytestword" I would like to > find it using the subword: "super". > > While with the MATCH operator it does not

Re: [sqlite] Anything like "select 7 as a, 8 as b, a / b as c; " possible whatsoever?

RSmith wrote: > On 2013/07/21 12:01, E.Pasma wrote: >> Only the execution plan of this query is not optimal: >> 0|0|0|SCAN TABLE categories (~100 rows) >> 0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 1 >> 1|0|0|SCAN TABLE ot AS ot1 (~33 rows) >> 0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 2 >>

Re: [sqlite] Anything like "select 7 as a, 8 as b, a / b as c; " possible whatsoever?

Mikael wrote: > Is anything like "select 7 as a, 8 as b, a / b as c;" possible? Not directy, but you could use a subquery: SELECT *, a / b AS c FROM (SELECT 7 AS a, 8 AS b); Regards, Clemens ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] Unstable ON DELETE actions

I wrote: > Roland Wilczek wrote: >> CREATE TABLE track (artist, >> FOREIGN KEY (artist) REFERENCES artist(id) ON DELETE CASCADE >> FOREIGN KEY (artist) REFERENCES artist(id) ON DELETE RESTRICT); > > This particular statement creates a table with a single foreign key > constraint. Sorry, I was

Re: [sqlite] Unstable ON DELETE actions

Roland Wilczek wrote: > - If two foreign keys come into conflict, SQLite silently ignores one of them > instead of raising an error. > > CREATE TABLE track (artist, > FOREIGN KEY (artist) REFERENCES artist(id) ON DELETE CASCADE > FOREIGN KEY (artist) REFERENCES artist(id) ON DELETE RESTRICT);

Re: [sqlite] Android : UNIQUE makes my DB go crazy

Sorin Grecu wrote: > I'm having an issue with my app. Already solved: ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] skype artifacts and sqlite records.

Salvatore Fiorillo wrote: > The issue I am facing is on how I can identificate a sqlite record if I > have miss the database headers? Did you read ? Regards, Clemens ___ sqlite-users mailing list

Re: [sqlite] UPDATE of many rows is very slow

Hinrich Aue wrote: > I believe I have used many ways to speed up the UPDATE of many rows, > But nothing helped so far. > > We open one connection, one transaction, and then we update many rows on the > database. > > var command = new SQLiteCommand(Queries.SQLUpdateDocument, connection); > foreach

Re: [sqlite] Getting Constraints Details

Vijay Khurdiya wrote: > How to get details of constraints associated with Data in SQLite3. There is no API to get individual properties; you have to look up the original SQL statement: SELECT sql FROM sqlite_master WHERE type = 'table' AND name = 'MyTable' > This e-mail and any files

Re: [sqlite] How to select from a temp table with same name as a main table.

Igor Tandetnik wrote: > On 6/19/2013 8:18 AM, Clemens Ladisch wrote: >> <http://www.sqlite.org/lang_attach.html> says: >> | If two or more tables in different databases have the same name and >> | the database-name prefix is not used on a table reference, then the &

Re: [sqlite] How to select from a temp table with same name as a main table.

dochsm wrote: > I would have thought having no database prefix would default to the > main says: | If two or more tables in different databases have the same name and | the database-name prefix is not used on a table reference, then the | table chosen is

Re: [sqlite] escape quote for csv import

Roland Hughes wrote: > How does one escape a in a CSV file so it will correctly import? The sqlite3 tool allows to configure the separator, but the quote character for delimiting fields is hardcoded. (There is no official CSV standard, and there is no widely supported escaping mechanism.) > I

Re: [sqlite] Simple Group By slowing queries by 6x

Iván de Prado wrote: > SELECT ddate, sum(hits) from a_idadvertiser_site_hostname_bench where ddate < > '2013-08-01' group by ddate; > > SCAN TABLE a_idadvertiser_site_hostname_bench (~33 rows) > USE TEMP B-TREE FOR GROUP BY > > [...] means that this query is running almost 6 times slower than

Re: [sqlite] Feature request: add support for COMMENT statement

Alexey Pechnikov wrote: > It's very important to have place to store table metainformation. In all > common DBMSs we can use TABLE/COLUMN COMMENT as meta description of table > but SQLite doesn't support it. SQLite saves comments in table/view/index/trigger definitions: sqlite> create table t(x

Re: [sqlite] finding records where a field only contains numbers

Gert Van Assche wrote: > is there a way to find all records where a field only contains non-alpha > numeric characters, or numbers? This description is rather vague. The following finds values that do not contain alphabetical characters: SELECT * FROM MyTable WHERE AField NOT GLOB '*[^A-Za-z]*'

<    5   6   7   8   9   10   11   12   >