Re: [sqlite] UPDATE statement without FROM clause

At 18:18 04/06/2016, you wrote: Hi, why? At the moment I have to run something like: UPDATE A SET item1=(SELECT B.item FROM B WHERE B.ID=A.ID),... itemN=... WHERE EXISTS (SELECT 1 FROM B WHERE B.ID=A.ID); Using a FROM clause I just need one scan through B (at least in principle). Now, I

Re: [sqlite] UPDATE statement without FROM clause

At 23:34 04/06/2016, you wrote: On 4 Jun 2016, at 10:15pm, Jean-Christophe Deschamps <j...@antichoc.net> wrote: > Can't the same update be done more efficiently with a CTE? The command inside the WITH has to be a SELECT command. Definitely not as Ryan pointed out, and as the

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

At 09:22 08/06/2016, you wrote: A 3 Byte Sequence 0xFFFEC4 when converting 0xC4 to UTF-8 in UltraEdit This 3-byte sequence is neither UTF8 or UTF16 even if the BOM would make us believe it is UTF16-LE. UTF16 implies 16-bit encoding units, so an odd byte length is impossible. You probably

Re: [sqlite] Will someone be able to explain this weird outcome...

select 7,915 - 5,021 is: select 7, 915 - 5, 021 giving 7 91021 just like select 'a', 915 - 5, 'b' ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Feature Request - RowCount

At 03:14 14/12/2014, you wrote: ´¯¯¯ I take the point that the only possible improvements seem to need alteration to the file structure or added maintenance which may use up cycles for something that just isn't that important to DB use in general - and I have to agree, I too have zero want for

Re: [sqlite] Feature Request - RowCount

Hi Simon, A) In that sqlite_sequence table you mentioned, as an additional column. Always up-to-date. But sqlite_sequence isn't always created. AFAIK it only exists when one or more table exists with an integer primary key autoincrement. B) In the tables prepared by SQLite ANALYZE. If

Re: [sqlite] Messages posted on Nabble not getting to list

At 03:46 27/05/2016, you wrote: If SQLite goes this route, I will probably (as with the others) stop reading it too. Seconded. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org

[sqlite] CSV excel import

At 16:36 01/08/2015, Igor wrote: >There are many real problems with CSV - no need to make up imaginary ones. `--- Indeed and I'm surprised noone mentionned this from the very start of this thread: Nulls. There is no real provision in the RFC to represent Null, or distinguish Null from an

[sqlite] CSV excel import

At 18:38 01/08/2015, you wrote: >Nobody mentions it because it is as irrelevant as bemoaning the fact >that CSV cannot store lawn-chairs or Java objects. It wasn't intended >to do so. Exactly. All I mean is that with only very few additional strict rules it can be changed into a basic type

[sqlite] Database sybchronisation

Hi Simon, >Method 2 > > >Ignore Method 1. In each copy of your database keep a log of all >INSERT and UPDATE commands executed since the last 'synchronize': > >CREATE TABLE commandsSinceLastSynch (theCommand TEXT) > >To synchronise the two copies, play back the log for copy A to copy B

[sqlite] Database sybchronisation

simon, >This is normally resolved by logging the time each command was issued Not "when it was issued" but when exactly it was finally commited. When one or more update statements are part of a transcation, you now have to use sqlite3_profile to log when exactly the transaction ended (i.e.

[sqlite] Proper way to abort

> >I have some queries that may take 5-15 seconds to complete. Sometimes >the situation changes shortly after starting the query where my >program does not need those results anymore and the program wants to >abort and begin a different query instead. > >My question is: What is the proper way

[sqlite] Why sqlite show qualified column names when selecting from views ?

At 22:28 25/08/2015, you wrote: >In the next version of SQLite3 the names of all columns will be >'columnname' "Don't trust me" could be more apropriate.

[sqlite] order by not working in combination with random()

At 16:00 27/08/2015, you wrote: > >An *ORDER BY* clause in SQL specifies >that a SQL SELECT statement >returns a result set with the >rows being sorted by the values of one

[sqlite] order by not working in combination with random()

>I can see both sides of this debate, whether or not random() should be >evaluated twice in this context: > >select random() from blah order by random() There are 2 distinct and volontary function invokations, so I don't see how SQL engine would decide not to perform the second call. >So let

[sqlite] order by not working in combination with random()

At 21:11 27/08/2015, you wrote: > > There are 2 distinct and volontary function invokations, so I don't see > > how SQL engine would decide not to perform the second call. > >Agreed, though I'm pretty sure I've read messages in this thread at >advocate the same function should return the same

[sqlite] autoincrement field

At 23:25 27/08/2015, you wrote: >--- >I have a table structure like this: > >CREATE TABLE padstack ( > id INTEGER PRIMARY KEY AUTOINCREMENT, > pin_number INTEGER, > name TEXT >); > >Is there any way to get the 'id' of newly inserted row? My insert of >course >not contains the 'id'

[sqlite] order by not working in combination with random()

At 09:14 28/08/2015, you wrote: >--- > > Looks like "How many zillion devices are going to misbehave if this > is fixed?" > >We will find out, because SQLite was changed two days ago: >http://www.sqlite.org/cgi/src/info/c2f3bbad77850468 >--- Fine, let's sit down and watch the world collapse.

[sqlite] explain plan change between SQLite 3.8.3.1 and 3.8.11.1

At 11:58 31/08/2015, you wrote: >--- >But the only sure way to prevent anyone else from installing its own >authorizer is to change the function name in the SQLite library. >--- That wouldn't really work under Windows: GetProcAddress can provide access to a DLL function by index (ordinal

[sqlite] With recursive question

Dear list, After trying a number of ways I'm at loss solving the seemingly simple problem. For a simplified example say I have a list of individual filesystem directories with FK pointing to their parent: PRAGMA foreign_keys=ON; CREATE TABLE "Dirs" ( "DirID" INTEGER NOT NULL PRIMARY KEY,

[sqlite] With recursive question (addendum)

Forgot to mention that I sees the issue as fundamentally distinct from the Mandelbrot example in the SQLite docs: it uses a construct -outlined in procedural statements- like: For x in xRange For y in yRange compute something like f(x, y) -- here, y is independant of x Next y Next x

[sqlite] With recursive question

Once again thank you very much Igor. I was making my life miserable trying to scan the "tree" from the other end. At 00:37 01/03/2015, you wrote: >It's a bit unfortunate that you made the root a parent of itself. >Forces the query to make an extra check to avoid infinite recursion. `---

[sqlite] User-defined types -- in Andl

At 08:27 09/06/2015, you wrote: >Andl is at a slightly higher level than SQL for writing simple queries. >Where it shines is writing complex queries that involve user-defined >types, >custom transformations and custom aggregations. For complex relational >operations there is nothing I know

[sqlite] User-defined types -- in Andl

At 13:50 09/06/2015, you wrote: >BTW I don't remember the last time I saw SQL like this. Understanding it >might be the challenge `--- Most probably! I can imagine that you don't encounter such style in common business-like environments. Take your time, this SQL piece is clearly beyond

[sqlite] Mozilla wiki 'avoid SQLite'

I've refrained to comment about the OP linked page but I can't resist that long. I won't enter the C, C--, C++, C#, Java, Python, COBOL, Basic, assembler, Ruby, YouNameIt sub-debate. I found the rant on MozillaWiki way too wrong on too many points to keep quiet. While I can agree with some of

[sqlite] Mozilla wiki 'avoid SQLite'

Marco, >There's never been that kind of tone, the article has been written to >point >new Mozilla codebase contributors at possible pitfalls we already hit in >the past, and actually help them making informed decisions. That's not how one reads it. Start with only the title:

[sqlite] full table scan ignores PK sort order?

At 11:27 03/03/2015, you wrote: >- the full table scan returns rows in rowID order, which is the order >in which the rows were added to the table `--- No and no. An SQL engine doesn't guarantee any row "order" unless you explicitely force an ORDER BY clause. Think of row order as random,

[sqlite] With recursive question

At 04:05 01/03/2015, you wrote: >On 2/28/2015 7:22 PM, Jean-Christophe Deschamps wrote: >>Once again thank you very much Igor. I was making my life miserable >>trying to scan the "tree" from the other end. > >That, too, could be arranged. Something along these li

[sqlite] sqlite3 and Unicode

At 09:22 05/05/2015, you wrote: >On 4-5-2015 20:54, Richard Hipp wrote: >>On 5/4/15, Peter Haworth wrote: >>>When using the .dump command with .output to a filename, what >>>encoding does >>>sqlite3 for the file? Same as the database encoding? Is it possible to >>>change whatever encoding

[sqlite] VBA Sqllite blob data

Isn't that the actual size limit of Excel strings? At 15:27 11/05/2015, you wrote: >I have a db3 config file that contains several different records. I >need to select one specific record and store it in an excel worksheet >cell. I have been able to create a routine to extract the

[sqlite] VBA Sqllite blob data

Hi Simon, >On 11 May 2015, at 3:25pm, Jean-Christophe Deschamps > wrote: > > > Isn't that the actual size limit of Excel strings? > >Sort of. Although Excel cells can contain text longer than 255 >characters, VBA functions ignore text parameters after the 255th >

[sqlite] Fwd: bulk upload from Excel

>-- Forwarded message -- >From: "maksood alam" <786maksood at gmail.com> >Date: May 11, 2015 11:21 AM >Subject: bulk upload from Excel >To: >Cc: > >Hi Team, > >Do we have any option like below for the bulk upload. > >INSERT INTO tbl_test1 SELECT * FROM [asd$] IN '" &

[sqlite] AUTOINC vs. UUIDs

At 22:50 20/05/2015, you wrote: >Just to chime in here: I think using UUIDs for primary keys is an >absolutely >terrible idea, instead. First off, how are you going to generate them? How >will you avoid collisions? Why should it be necessary to using that much >storage space for a primary key

[sqlite] AUTOINC vs. UUIDs

e* using one. Validating Global >uniqueness is, quite obviously, a very difficult problem. > > > -Original Message- > > From: sqlite-users-bounces at mailinglists.sqlite.org > [mailto:sqlite-users- > > bounces at mailinglists.sqlite.org] On Behalf Of Jean-Chris

[sqlite] ALTER TABLE ADD COLUMN

At 08:51 21/05/2015, you wrote: >I used sqlite3.exe by invoking it on the command line with the name of >a database. >Next I typed > > alter table database add column real_length numeric; > >Next I typed .fullschema >and I'm getting: > >sqlite> .fullschema >CREATE TABLE [database] (

Re: [sqlite] collation for german "Umlaute"

Search the Internet for an SQLite extension called "unifuzz.c" and see if that does what you want in the way of character folding. I have a copy of the code on my other computer if you cannot find the original authors original code. Here is the download link:

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

At 15:45 15/11/2013, you wrote: last_insert_rowid() is needed for autoincremented keys. WITHOUT ROWID tables do not have an autoincrementing key. Your program has to generate or get the key in some other way, so it knows the value even before the record is inserted. last_insert_primary_key

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

How so? An AFTER INSERT trigger has the value in new.yourPKColumn. Can you show a scenario where this is insufficient, and a hypothetical last_insert_primary_key would save the day? You're correct, there is no situation where such a thing would make sense.

Re: [sqlite] Concrete example of corruption

At 08:45 11/12/2013, you wrote: My case wasn't about messing anything up, but simply moving a document. Just like moving the canvas while the artist is painting? Or is it moving the notebook while the schoolkid writes on it? This is prone to produce dribbles or worse, in my view. I fail to

[sqlite] How should I use parenthesis?

Dear list, I'm trying to find the correct syntaxt for this, but I hit a syntax error each time: either SQLite shokes on outer parenthesis or on union all. ( select * from A where x in (subselectA) left outer join select * from B where y in (subselectB) ) union all ( -- <-- error select *

Re: [sqlite] How should I use parenthesis?

Perhaps you want: select * from ( select * from A where x in (subselectA) left outer join ( select * from B where y in (subselectB) ) as a on -- something ) as A1 UNION ALL select * from ( select * from B where y in (subselectC) left

Re: [sqlite] Why would batched write operations NOT be faster than individual ones

It's how RAID5 works. Check this page docs http://baarf.com/ about it. This is utter BS. Serious RAID controllers perform parallel I/O on as many drives that are making up a given array. Of course I'm talking of SAS drives here with battery backed-up controller. Kid sister RAID5-6

Re: [sqlite] Why would batched write operations NOT be faster than individual ones

At 21:35 03/03/2014, you wrote: ´¯¯¯ RAID3-4-5 was great when disks were expensive, in 80's an 90's. Now not. A minimal RAID5 needs 3 disks. A minimal RAID10 4. An enterprise disk SAS 15Krpm 146 GB 6G is $350, and a not enterprise grade cheaper and bigger. Now RAID1E and RAID10E give more

Re: [sqlite] Is sqlite case-sensitive?

At 22:36 18/05/2014, you wrote: The more I think of it, though, I think that the solution is as simple as converting all letters to lower(/upper) case and converting all whitespace to a single space each, except for within matching [ ], " ", ' ' or ` `. After that, I can do a memcmp().

Re: [sqlite] Hexadecimal integer literals

Conversion of oversized hex into FP would break easily and reveal hardly reproductible across many platforms. Being a support for some languages fora I observe daily how FP inaccuracies is a real-world problem in simple-looking code. The only reasonable thing I can foresee is treat hex as

Re: [sqlite] Insert optimization

How to improve the write speed then. I've found that in most cases using "chained" insertion speeds up things dramatically: insert into mytable (x, y, z) values (1, 2, 3), (4, 5, 6), (7, 8, 9), ... If you insert K rows at a time this way your total insert time can by cut by a significant

Re: [sqlite] Divide by 0 not giving error

Dear forum, MySQL does it too unless ERROR_FOR_DIVISION_BY_ZERO mode is enabled: SQL Server does it too unless SET ARITHABORT is ON: PostgreSQL doesn't do

Re: [sqlite] Divide by 0 not giving error

What the SQL standard calls an "exception" is not necessarily exactly the same as an exception in other programming languages. Granted. If SQLite were to change the division-by-zero handling, it could be reported exactly like most other errors, by returning SQLITE_ERROR from sqlite3_step().

Re: [sqlite] Divide by 0 not giving error

> This would means that if ever an SQL statement encounters divide by > zero, the application will crash with no way handle the situation > gracefully, nor to locate the source of the problem. Seriously, what are you talking about? Why is there "no way to handle" the error, gracefully

Re: [sqlite] Crash when binding default column value

At 21:53 24/09/2014, you wrote: > If the default cannot be represented exactly, its rounding will be the > least of the difficulties. Not always: in scientific applications I've had column values default to plenty of irrational numbers: fractions of pi, sqrt(2.0) etc. My main gripe is when

Re: [sqlite] Sorting of Korean Language Characters

At 09:43 23/03/2011, you wrote: >I am working on one application which require sorting for Korean Language. > >The Korean Characters sort by Jamo(Hangul Jamo) ie based on KSX1001 >character code. > >Does sqlite3 or any other package support this type of sorting ? >If not , then any clue to carry

Re: [sqlite] storing big numbers into NUMERIC, DECIMAL columns

At 18:46 23/03/2011, you wrote: >Current US national debt is 16 digits. Nothing less? That's where the bug lies. OK, OK, I'm out ;-) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Lack of "decimal" support

>There are many cases where people are doing calculations or using >numbers expecting them to retain all digits. This would allow the BCD >type to be used for that if they really need it. Currency conversions (rarely exact!) or tax (or margin) calculations come to mind as very common uses

Re: [sqlite] Lack of "decimal" support

>This page has a lot of info about >Decimal Number support, including >a set of libraries: > >http://speleotrove.com/decimal/ Yes! IBM and Intel are two of the big names having done significant work in this direction and made research and/or results publicly available.

Re: [sqlite] sqlite bug? -- inconsistent time searches

>It's a pain in the arse that one has to pay for copies of the >ISOs. What do they think we're paying them for ? I also find this a perverse effect totally contrary to their mission or at least its spirit: produce good standards for public use. Without free access to reference up-to-date

Re: [sqlite] read full txt file in one record

>3. edit the database file with a hex editor, replacing "~~" with "0D 0A". That seems pretty dangerous! Rather update the table using the standard replace() function. ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] Trouble inserting null value from txt file

>It's impossible when you use standard sqlite3 command line utility. In >your particular case you have 2 options: either write your own >application that will recognize some kind of value as null and insert >it instead of plain text, or you can use temporary table like this: Another way is to

Re: [sqlite] insert help

>Newbie here. i'm trying to insert multiple values into a table by a >certain >date and when I use where clause it fails. This is my code "insert >into db >(table) values ('value') where date = 'date range'". Thanks for any help. There is no where clause in insert statements, it wouldn't

Re: [sqlite] insert help

>My date column is set when the program starts and i do not want it to >change. How is this date column set in the database without inserting anything? > So I have my with and two columns and >. I have say 5 values (1 2 3 4 5) that I wanted inserted >into mytable where the date is equal

Re: [sqlite] How to Use an Apostrophe in a Text Field?

> The apostrophes are escaped by apostrophes. Correct. http://www.sqlite.org/faq.html#q14 > One more way you can do. > >insert into () values ("*Goin' Down > >> the Road Feelin' Bad*"); > >It is double quotes before and after *. Similarly double quotes will >be escaped by one more double

Re: [sqlite] How to Use an Apostrophe in a Text Field?

>True. I will get rid of the habit of using double quotes for string >literals. >Thanks for information. But most of the databases support this non >standard >behavior. Yeah ... until things break under your feet due to a new version not sticking to the "non-standard" behavior anymore or

Re: [sqlite] Feature request: Fuzzy searching

I apologize if double-post, the first one didn't make it to the list. - Hi, >Hi all, I am interested in seeing "fuzzy searching" in SQLite, for >lack of a better term. This type of search would return more results >than LIKE

Re: [sqlite] Issue 608 in sqlite-manager: select date('2011-04-29', quote(-3) || 'day'); shows null after upgrading of firefox to 3.6.17

Change that into: select date('2011-04-29', quote(-3) || ' day'); (note the space before day). Looks like a parsing change. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Determining how many columns were returned in a query

>How does one go about finding out how many rows a query returns? This is the number of time sqlite3_step can be called successfully until it returns SQLITE_DONE. >Is there a way to find out the id of a particular column? AFAICT column don't have ids. You can read column names or alias using

Re: [sqlite] Determining how many columns were returned in a query

>How about: > >SELECT count() FROM (); You can do that (and variations) but this is a completely distinct statement. I meant that there is no possibility to recover the row count of a result set before it goes to completion (by iterating step), just because the SQLite engine has no idea

Re: [sqlite] Determining how many columns were returned in a query

>I happen to have a code path such that the select statement can return >1, 3 >or 5 columns. I know I could go based on count, but if I could do it by >name that would be safer. I had not considered the point that multiple >columns could have the same name, though, so I fully understand why

Re: [sqlite] SQLite as a Logger: How to mimic "rotation of logs"?

Without a view (but with a trigger) and certainly open to improvement (9 is the MAX_ENTRIES parameter): CREATE TABLE "log" ( "id" INTEGER NOT NULL PRIMARY KEY ON CONFLICT REPLACE AUTOINCREMENT, "seq" INTEGER CONSTRAINT "ix1Seq" UNIQUE ON CONFLICT REPLACE, "data" CHAR); CREATE TRIGGER

Re: [sqlite] SQLITE return codes for insert/delete/update/select

>I agree with what you stated but it would have been more clearer if >the result of the update statement was a "RECORD NOT FOUND" return >value since it did not find any that met the query's criteria. How can >you say that the UPDATE was successful when the record you were >looking for does

[sqlite] Caveat in parsing create table statement

Dear list, Is there a way to make SQLite accept this kind of constraint: CREATE TABLE tab ( id INTEGER NOT NULL, data INTEGER, CHECK(data = 0 or not exists (select 1 from tab where id = data))); This toy exemple doesn't mean much as it is and the actual situation is a bit more

Re: [sqlite] SQL Statement Help(selecting days).

>On Wed, May 18, 2011 at 4:10 PM, Petite Abeille > wrote: > > On May 18, 2011, at 10:50 PM, Danilo Cicerone wrote: > >> How can I simulate a > >> calendar table(maybe using the strftime funtion)? > > > > Well, you have two broad options: > > > > (1) materialize the

Re: [sqlite] SQL Statement Help(selecting days).

> > Since we use recursive triggers, set recursive_triggers pragma > > beforehand if not yet done. > >Cunning. A bit of a Rube Goldberg apparatus though, no? Huh? Still way more flexible than having to modify C source of a vtable module, should you have to adapt anything. Yeah, it's kind of

[sqlite] Usefulness of FKs to multiple tables

Let me ask this by mere curiosity. SQLite will accept and process the following: CREATE TABLE x (a CHAR PRIMARY KEY); CREATE TABLE y (a CHAR PRIMARY KEY); CREATE TABLE z (a CHAR REFERENCES x(a) REFERENCES y(a)); I didn't check if the last FK is even valid normative SQL and that isn't the heart

[sqlite] (no subject)

Anoher (silly) question about what SQLite considers valid input, again out of mere curiosity. A statement like: CREATE TABLE a (a CHAR COLLATE NOCASE COLLATE BINARY, b INTEGER DEFAULT 1 DEFAULT 2); doesn't cause any error: SQLite applies only the last constraint of each type, namely COLLATE

Re: [sqlite] (no subject)

>Ah, there is a way to write it so that you can have at most one of >those constraints where only one makes sense, and not require a >specific order of constraints, but it'd require listing all the >possible orderings, which would be impractical. > >So if one wanted to enforce that there's at

Re: [sqlite] Possibly Alias Bug

>I have problems with column aliasing using AS when I select >rows with the same string: > >.headers ON >CREATE TABLE TEST1 (name TEXT,value TEXT); >INSERT INTO TEST1 VALUES ("name1","value1"); >SELECT value AS name1 FROM TEST1 WHERE name="name1"; // gives no result >SELECT value AS name1_ FROM

Re: [sqlite] date field with default current date

> I resolved with: >CREATE TRIGGER insert_nameTable after insert on >name_tabl begin >update set ww = datetime('now','localtime') >where rowid = new.rowid; end" You don't have to fire a trigger for such default: CREATE TABLE "test" ( "mydate" CHAR DEFAULT

Re: [sqlite] [BUG] Silent change in intergeral overflow handling in 3.7.6

Richard, At 13:07 25/05/2011, you wrote: >It turns out that the "expected" behavior does not happen in modern C >compilers. Overflow of signed integers is undefined behavior in C. So if >you have a signed integer overflow, it might wrap the result (the >"expected" >result) or it might

Re: [sqlite] Is it possible to optimize the read performanceof a C++ app using sqlite pragma journal_mode = wal & pragmawal_checkpoint?

> Simon Slavin, Thank you for your suggestion. Our deduper prototoype > uses fuzzy matching methods such as the Levenshtein Distance to > detect duplicates. We have found that these fuzzy matching methods > are best implemented in C++ for processing time requirements. > We would

Re: [sqlite] Unlocking the database

>I understand the need for integrity when locking a database, but in >this case I knew that the problem was caused by a (in all honesty, my) >bug. I tried rebooting the machine and it did not unlock the table. Yeah, sometimes Windows will keep a lock on a file beyond reboot, which is

Re: [sqlite] What is so slow on dropping tables?

>Quoth Roger Binns , on 2011-06-01 00:21:44 -0700: > > On 05/31/2011 12:18 PM, Jan Hudec wrote: > > > - Is there any way to speed it up? > > > > Another way (somewhat hinted at by Nico) is that you can create > these tables > > in separate databases and use attach to

Re: [sqlite] Proper way to escape table name and column name

>What is the official way to escape table name that contains a space >and column name that contain a spaces? You can use square brakets or double-quotes: [This is a long name for a small table] "This is a long name for a small table as well" ___

Re: [sqlite] Handling of IEEE-754 nan and +/-inf in SQLite?

Allow me to add a humble bit to what Jay just posted. SQLite, as well as most other RDBMS around, allow you to perform FP calculations in SQL statements. I assume no-one imagines an extended FP fine-grain support of hundreds of computation options and status reporting be part of SQL or

Re: [sqlite] Handling of IEEE-754 nan and +/-inf in SQLite?

Nico, >There is one thing that makes numerical support in RDBMSes important: >aggregate functions. > >Aggregate functions are critical because they allow one to do much >analysis at the data source, instead of having to transport it >elsewhere for analysis. I agree with you, totally. Read me

Re: [sqlite] Handling of IEEE-754 nan and +/-inf in SQLite?

>Sure, if you're just computing average() then you'll not get any NaNs. NaNs, NO (if we don't have NaNs in the set) but issues, YES. It all depends. No one knows. You don't even know what you're computing exactly. SQL interpret avg() as "take this data as a set, sum up these numerical

Re: [sqlite] sqlitebrowser - anyone compiled a recent one?

>Thanks. I was prepared to receive alternate suggestions :) >I wanted to have hands on and control over a browser with which >can follow sqlite versions quicker. > >Sqlitebrowsers tend to lag behind the Sqlite development. Then give SQLite Expert a try! http://www.sqliteexpert.com/

Re: [sqlite] Implementing busy-waiting on SQLite

>As was being discussed yesterday, I have four processes accessing the >same database file. When they perform an sqlite action, I wish them to >block if the DB is not available. SQLite does not block if it finds the >db busy or locked, it returns an error code. You can have SQLite do all this

Re: [sqlite] Implementing busy-waiting on SQLite

Hi Jay, > > Invoke sqlite3_busy_timeout() with a safe timeout for every connection > > to the DB. > > Use IMMEDIATE transactions everywhere. > > > > That's all you have to do. > > Well, yes and no. You still have to deal with the case of > SQLITE_BUSY being returned due to deadlocks.

Re: [sqlite] Is there a difference between DELETE and UPDATE/INSERT in terms of syncing to disk?

Kevin, >I have confirmed that the DELETE does indeed delete the record, but >only after Django's delete callback has completed. If what you say is >true, that DELETEs are proceed immediately, then the only other >explanation is that Django, upon sending of the post_delete signal, >has not

Re: [sqlite] unexpected effect of view nesting on type conversion

>I've run into a phenomenon I don't understand where view nesting affects >types. Give me a try: Form what I understand, views don't have their own types, so default affinity applies. 12.0 gets converted to 12 as an integer in v2 when the value gets picked from v1. Please someone correct me

Re: [sqlite] Query with UNION on large table

Rense, >As for the ranges of n1 and n1: they are both roughly between 6 >and 1200 . > >Here are the results of EXPLAIN QUERY PLAN SELECT n1, n2 FROM table1 >Where n1 < n2 INTERSECT SELECT n2, n1 FROM table1 Where n2 < n1; > >1|0|0|SCAN TABLE table1 (~437976176 rows) >2|0|0|SCAN TABLE

Re: [sqlite] Query with UNION on large table

Hi Rense, >Thanks for this idea. In fact, the purpose of my original query is >exactly to reduce the database. The 800 mln rows were exported from >another source, and I was hoping to be able to use sqlite to manage >this massive amount of data (e.g., removing redundant information) >before I

Re: [sqlite] Continuous exclusive lock

Cecil, >Is good enough for me. My only problem is that between reading the >data and >writing the changes, I want to be sure that no one has changed the data. >For me that is enough. For this, a simple "Begin immediate;" ... "Commit;" embrassing you read-modify-write block will do. Doesn't

Re: [sqlite] Continuous exclusive lock

Cecil, >Do I understand it correctly that after a commit the database is writeable >again for others? Yes. > In that case it is maybe better to do a: > *PRAGMA locking_mode = EXCLUSIVE; >*followed by an update of the database. As long as the application is >running, I want to be sure that

Re: [sqlite] Continuous exclusive lock

> > If you _need_ exclusiveaccess all along, then start app, "begin > > exclusive", do your stuf, "commit" and exit. > > > >The 'problem' is that the application can run for the whole day. Granted. And the 'problem' is ??? > > What I don't get is you later say it's a single-user, single-app > >

Re: [sqlite] How to search for multiple values in a column including null without repeating column name ?

Michael, >Are you saying that "1" below doesn't show up in the first query result? > >sqlite> create table t(i int,name string); >sqlite> insert into t values(1,NULL); >sqlite> insert into t values(2,'two'); >sqlite> select * from t where i in (1,2,3,null); >i|name >1| >2|two >sqlite> select *

Re: [sqlite] Hidding records from the application

>I want to make things clear. there are some rules in my system, such >as : process whose name is proc_host can see all the records, and >process whose name is proc_client1 can see all the records except >rowid 1. First, keep away of relying on rowid values since they may change if you don't

Re: [sqlite] Strange result using JOIN

>I understand that but why the same query, with the same data, return two >different result using different SQLite-based Win32 applications ? SQL is essentially based on set theory. Sets don't have intrinsic ordering and may be enumerated in whatever order. What you think are distinct

Re: [sqlite] null handling import

Mattew, > Obviously I am not as well versed in sqlite as you are. I hate to > hate myself, but am still continuing with reading somewhat ancient > files into a somewhat cohorrent database. > >Thanking you for your time and trouble, > > Matthew > >p.s. shame about the parsing of comma

Re: [sqlite] SQLite + unicode

>I try this function. Do you have Visual Studio. i show you my example. > >NOCaut wrote: > > > > > > char * unicode_to_1251(wchar_t *unicode_string) Why are you converting Unicode to 1251? This is a lossy conversion in the general case. Work with Unicode strings end-to-end, using the UTF

Re: [sqlite] Add/modify a table in the existing Sqlite database in client side.

>I am using C# windows application(.Net2010) with sqlite, and i need >to change the database table. >Can you pls let me know, how to add or modify a new column/ > table in >the existing Sqlite database that is in my client desktop machine. >I want the existing database to have its data,

  1   2   3   4   5   >