SQLite Forum


24 forum posts by user jking

07:01 Reply: TEXT values can contain arbitrary binary data (artifact: dae1f9d17c user: jking)

While I agree it's a surprising result the documentation for expressions also states in part:

The result of any binary operator is either a numeric value or NULL, except for the || concatenation operator which always evaluates to either NULL or a text value.

Thus concatenating any two non-null values will always yield text even if the value is invalid for the database encoding.

19:02 Reply: DROP COLUMN feature with indexes (artifact: deba00a87c user: jking)

The errors thrown are pretty clear, and are enough to suggest what index(es) need to be dropped before a retry. Dropping an index seems a bit much to me.

I agree. I would not want to silently lose an index because I forgot it was using a column I no longer care about.

03:24 Reply: Question about SELECT in PHP (artifact: 4f190d77cb user: jking)

This is a PHP question more than it is an SQLite question, I think. I don't suppose you're using the built-in PHP Web server for this? It's single-threaded and can only serve one request at a time.

13:24 Reply: Check date when not null (artifact: 195ddb0623 user: jking)

It sounds like this might have been discussed before?

16:08 Reply: Interesting question: Why aren't you using SQLite more? (artifact: a1f8f48aaa user: jking)

I won't comment on anonymous' experience other than to say that is starkly different from mine.

Generally speaking I use SQLite quite a lot both in software I have written and software I have used, saving myself lots of pain in managing grants and backups for heavier database systems, not to mention time unit testing thanks to in-memory database which are trivial to build up and then tear down.

I'm not sure I could actually be using SQLite more, but as a PHP programmer I find myself unable to use more of SQLite (like fts5 or json1) because one can generally only rely on the core library being available. That's not really Hwaci's problem, but it is an answer to the question.

21:06 Reply: Date & Time Data Type (artifact: be0a0b5f0f user: jking)

There is something that the CLI is using to detarmine that the first value is a datetime value and the other is a floating point value.

Incorrect. The first value is text, because that's what the datetime() function outputs. It just happens to be recognizably a date to you and me as humans used to dealing with timestamps in that representation. There is no magic here.

19:28 Reply: Date & Time Data Type (artifact: 7dd73f93aa user: jking)

Since SQLite will happily accept any type identifier you'd care to dream up, you could use int_datetime, real_datetime, and text_datetime (or whatever you want, though these types would give you the expected affinities). You can then use sqlite3_column_decltype and other interfaces (like PRAGMA table_info) to retrieve the type if needed.

01:30 Reply: SQLite - CMD prompt - How to exit Statement/Expression after messing up? (artifact: f22a9b255c user: jking)

You can enter as many newlines as you want until you complete a statement, terminated with a semicolon; it's not an error, just a form of multi-line input which is common to command-line SQL interpreters.

I'm not sure why entering a semicolon didn't work for you. Maybe you had an unterminated string literal or identifier?

I'm not sure how to cancel a statement, though. Ctrl+D? Ctrl+C? Someone else surely knows.

02:25 Reply: Is `JSON` a valid `create table` type? (artifact: d7e15fdac2 user: jking)

You can also use an affinity_logical-type construction e.g. text_json, giving you the best of both worlds.

11:12 Reply: Can DEFAULT be the only possible value? (artifact: 44d4d84087 user: jking)

Sounds like you want the newly-implemented generated columns feature.

12:31 Reply: sqlite3_normalized_sql(stmt) inconsistency? (artifact: d456d5c9b9 user: jking)


If a keyword in single quotes (ex: 'key' or 'glob') is used in a context where an identifier is allowed but where a string literal is not allowed, then the token is understood to be an identifier instead of a string literal.

04:09 Reply: Documentation does not show when a feature was added (artifact: 4ba145bb2c user: jking)

The SQLite documentation's primary audience is C programmers who will compile the library into their own program, or people otherwise using the latest version. If your distribution provides an older version, you should be consulting your distribution's documentation (sqlite-doc package for Fedora, I believe).

That said, the SQLite Web site does have a changelog that lists all notable feature additions over the last two decades. Searching that has usuall been fruitful for me.

10:29 Edit: Documentation typo (artifact: 74844bf1df user: jking)

Section 4.5 of the json1 documentation begins its third paragraph with:

Edits occurs sequentially…

The second word should be "occur". This typo also occurs in the second paragraph of Section 4.8.

The third paragraph of Section 4.6 also contains a typo:

An argument with SQL type TEXT it is normally…

The word "it" does not belong.

The first paragraph of Section 4.8 reads in part:

… returns a new JSON value that is the X with all the elements…

"the X" should probably be "the input X".

10:13 Post: Documentation typo (artifact: 38e30fada0 user: jking)

Section 4.5 of the json1 documentation begins its third paragraph with:

Edits occurs sequentially…

The second word should be "occur".

23:58 Reply: RAISE (ROLLBACK | ABORT | FAIL) differences? (artifact: b3f75f979c user: jking)

See the documentation on ON CONFLICT for details on what each does.

01:58 Reply: Using WAL mode with multiple processes (artifact: 57a83dda27 user: jking)

That doesn't answer the question that was posed, though. The WAL documentation does however state:

The WAL journal mode will be set on all connections to the same database file if it is set on any one connection.

Presumably this is done by incrementing the schema version, or via some similar marker in the database header that SQLite checks before preparing a statement.

12:25 Reply: Endianness help (artifact: aba71130f1 user: jking)

If Linux Journal said that, it's a sign that they're scraping the bottom of the barrel for articles and/or writers.

Linux Journal is long past that: they ceased publication for the second (and seemingly final) time a year ago. I was never a reader, but it stands to reason quality suffered at the end.

18:21 Reply: When will/were recent "sqlite3 new security issues CVEs" be addressed? (artifact: 691650b847 user: jking)

Small typo in the first paragraph:

Thought the SQLite developers do not consider CVEs...

First word should be "though".

11:41 Reply: How do create the neat flow diagrams for SQLite? (artifact: 64ec17d6a0 user: jking)

I tried searching for "display(1)" and found a manual page, which stated it was part of imagemagick. Convert may be the same. The names are indeed problematically generic.

19:59 Reply: SQL compliance on Wikipedia (artifact: 026b23f302 user: jking)

This is a pretty strange page. It isn't an encyclopedic article describing SQL compliance as a concept, isn't named "comparison of SQL implementations" as you'd expect for such a table, and even then has no front-matter or descriptive text of any kind. It's also dangerously low on references, making any current claims in the table all but meaningless.

I'm not sure it's worthwhile filling this in even if you do have the SQL specification and SQLite documentation sitting in front of you.

01:31 Reply: Select Query will not work in PHP program (artifact: 1119642f41 user: jking)

$database in the constructor is undefined. If you set your error_reporting() to show notices, PHP will advise you of this problem (and probably others).

12:54 Reply: Quoting is hard (artifact: df5801e855 user: jking)

When using Markdown, you simply copy/paste the piece you want to quote, and precede the quoted text with a single ">".

Yes, and in trying to quote your one paragraph, Richard, it took over a minute of poking at my touchscrren because text is microscopic. And that was only one paragraph. Note that I had already quoted elsewhere, so I was not asking how, but rather saying that the forum as rendered in my browser (Firefox for Android) makes it almost impossible to quote someone else. Here's a screenshot for reference:


What makes it doubly hard is that there is no margin to the left of the textarea. Tapping precisely at the extreme edge of a touchscreen is harder than tapping elsewhere, making manually prefixing lines (as well as selecting the whole line) extremely fiddly.

My suggestions:

  • Add some left/right margin to the textarea
  • Scale the textarea down on mobile (I think that should help)
  • Have a dedicated "quote" function in addition to "reply" which will quote the message in full for easier editing
12:12 Post: Quoting is hard (artifact: e1d109e0a4 user: jking)

Maybe I've simply missed how, but so far I've found quoting of excerpts prohibitively difficult. Particularly on a pocket computer it's downright exhausting since the text-entry widget zooms out rather than in when I go to type.

Coming from a mailing list (and from Ars Technica, where quoting messages is also considered good form), I suspect I'll find this not only a disincentive to write, but a difficulty in keeping up with the trail of a discussion.

01:03 Reply: RSS feed not working (artifact: 6e27041929 user: jking)

I believe the URL you want for this is


The key is the parameter at the end.

You can read more about this in the online docs.

Ironic, perhaps, that the online docs don't document y=f...