SQLite Forum


36 forum posts by user Clothears occurring on or before 2020-07-30 13:50:21.

More ↑
13:50 Reply: SQLITE python 3 issue, please help (artifact: 0b927a6d6d user: Clothears)

Yes, but if the OP can find where the Python version is, he can save the one that's there, and then copy the one I indicated to that spot.

13:28 Edit reply: SQLITE python 3 issue, please help (artifact: 578ee0812e user: Clothears)

Looks like it's: /usr/lib/sqlite3 which is a soft link to /System/Library/Tcl/libtclsqlite3.dylib

(at least under Mojave).

13:28 Reply: SQLITE python 3 issue, please help (artifact: 5f654ccc70 user: Clothears)

Looks like it's: /usr/lib/sqlite3 which is a soft link to /System/Library/Tcl/libtclsqlite3.dylib

20:50 Reply: download and install - complete newbie (artifact: be0805061c user: Clothears)

Tell us what your machine is, and which programming language you are expecting to use. It is quite possible that SQLite is already installed.

Also don't confuse the SQLite3 application, a program which you run at the command line in your computer, with the SQLite3 library which will become part of your application.

16:35 Reply: Endianness help (artifact: 52abf21ae1 user: Clothears)

Ahem. PDP11 was little-endian. Try Sigma 7 instead.

15:23 Reply: Endianness help (artifact: d5edde22f2 user: Clothears)

I imagine that's true today, but I've largely given up looking at the bits. See, however:


This is a machine (or a clone of which, anyway) I worked on back in the day. Look at P7 on Information Format for their view of things :-)

14:12 Reply: Endianness help (artifact: 6b62ba9e48 user: Clothears)

No it won't. If you have:

uint8_t c[] = {'0x01', '0x02'};

then that will be stored as 00000001 00000010 on what ever machine you have.

The question of endian-ness affects ONLY the order of bytes within a larger item, such as a 16, 32, or 64-bit integer.

If I have four bytes, thus:

uint8_t c[] = {0x01, 0x02, 0x03, 0x04};

then on a big endian machine they will be loaded into a 32-bit register as:


whereas on a little-endian machine they will be loaded into a 32-bit register as:


The bit order within a byte is NOT reversed at all. The bits may be numbered in the opposite order, but they are stored the same.

09:31 Reply: Endianness help (artifact: 43bd7a5e36 user: Clothears)

If you're saving a single byte. what has endianness, which has to do with byte order, got to do with it?

20:35 Reply: Decimal128 (artifact: f27b71b2b2 user: Clothears)

"Add up a hundred 1e-4 epsilons, and you get a possible 1e-2 error. That is basic math"

I'm not sure this is right. These epsilons do not all have the same sign, so some will cancel out. I did study this stuff (about error propagation) but that was 50 years ago and I've forgotten it all now. Suggest you consult a mathematician.

07:35 Reply: Performance issues with query parameters vs. static strings (artifact: 6fee60c8c0 user: Clothears)

Worse than false. It's misleading, patronisng nonsense. Having tried an iPhone, thinking I could do email and Twitter on it, I've removed the Twitter app and and stopped with the email as it's not secure. I'm quite likely to scrap the iPhone altogether and go back to a cheap clamshell with a much better battery life.

We see the same with cars: too many models with only a touch-screen to operate the heating controls, for example. These lack tactile feedback so I have to look at a screen to see what I'm doing as I drive along. You'd think the safety people would have forbidden that.

09:20 Reply: storing number with 2 decimal place (artifact: 9893a33e02 user: Clothears)

Why not store it as a number? Displaying to the user is not the business of the storage layer.

13:38 Reply: ALTER TABLE ADD COLUMN problem (artifact: 24aa9be23b user: Clothears)

Is the sqlite_master table and its columns covered by the 2050 SQLite lifetime warranty?

11:24 Reply: ALTER TABLE ADD COLUMN problem (artifact: 3fda139170 user: Clothears)

Unfortunately, it seems that according to the docs for my language (Xojo), the error from the prepare is not reported unless one attempts the SELECT. And I just verified that this is the case. So I may as well just do the SELECT directly.

That might be good enough for the moment, but perhaps the PTB will consider adding IF NOT EXISTS.

10:55 Reply: ALTER TABLE ADD COLUMN problem (artifact: a273841952 user: Clothears)

Stephan - thanks. I'll give that a try.

10:36 Post: ALTER TABLE ADD COLUMN problem (artifact: 373b88c9fe user: Clothears)

I'm looking for a robust way to solve this. I appear to be using 3.29.1.

I have an app that I distribute, which uses various SQLite databases. From time to time, as I add new features, I need to add columns here and there to some tables. So far I've been doing that by checking a value stored as a data-version-number in a globals table in a database, and when necessary I bump that number and execute a number of ALTER TABLE ADD COLUMN statements as required.

This is all done in the app startup, and normally works without issue. However, recently a user had an unrelated problem and I gave him an unreleased version to test. Unfortunately it seems I broke my update procedure for his set of databases and now he can't update to the next released version. I expect that some column I want to add is, now, already in a table.

Once I get hold of him it should be easy enough to diagnose and fix as he's comfortable with Terminal and the sqlite3 CLI. But I want to fix this for the future by being able to detect this specific error (duplicate column) if it happens and ignore it.

  1. There is unfortunately no IF NOT EXISTS for ADD COLUMN. Would this be difficult to add, as a feature request? Then I could simply do such as ALTER TABLE MYTABLE ADD COLUMN IF NOT EXISTS NEWCOL INTEGER. This would be the most robust method.

  2. As things stand, with "duplicate column" I get error numner 1 (generic error, not too useful) and a message such as "duplicate column name: newcol". I could parse this, but don't want to depend on error message content.

  3. I could parse the sqlite_master table but I'd rather not do that either.

Have I overlooked a better approach?

21:53 Reply: Confused about blobs (artifact: 4ce953a42c user: Clothears)
Not quite. If you build up a string yourself, using a user-supplied value abc, you might end up with, say:

update mytable set mystr='abc';

which is OK. But suppose the string the user supplies, instead of abc, is:

abc'; drop mytable;

Then you're in trouble. You avoid this by using a prepared statement like this:

update mytable set mystr=?;

Note the question-mark. Then you bind the user's value to that statement, and it doesn't matter what string the user gives you, it ALL goes into the database.

So it isn't just another way of doing the same thing.
07:39 Reply: ORDER BY not working for a specific DB/table (artifact: 0c6b711f75 user: Clothears)

I told you, nothing to do with SQLite and everything to do WITH HOW FLOATING POINT WORKS. You should read the reference that Keith provided.

You can't store 30.1 and 31.0 exactly, because they cannot be represented exactly as floating point numbers.

So this is not an issue at all.

16:05 Reply: ORDER BY not working for a specific DB/table (artifact: 24eb16f1f9 user: Clothears)

41.200000000000002843 is the closest to 41.2 that can be represented as a floating point number. I imagine that 41.199999999999995735 is the next lower number.

This sort of problem always arises when trying to compare floating point numbers, nothing to do with SQLite.

18:17 Reply: Using a numbered parameter (such as ?4) more than once in an SQL statement (artifact: b96c01012c user: Clothears)

On an unrelated note, when I mentioned this on the Xojo User Forum, the spell checker decided that you are, in reality, Richard Hippogriffs.

16:24 Reply: Using a numbered parameter (such as ?4) more than once in an SQL statement (artifact: 48aac3c7c2 user: Clothears)

Ah thanks. I read that page earlier, but missed that sentence. Must be "Lockdown leads to soggy brain" syndrome.

14:44 Post: Using a numbered parameter (such as ?4) more than once in an SQL statement (artifact: 962a6c2e0f user: Clothears)

Can a numbered parameter be used more than once in an SQL statement, such as:

select * from mytable where id1=?1 and id2=?1 and ...

I had a feeling I'd read somewhere that this works, but I can't find it now. I'd like to know that it's "works as intended and guaranteed", rather than "happens to work in this version but no promises for future versions".

Thanks for an insight.

11:38 Reply: Incremental Blob I/O and virtual tables (artifact: a262de07e6 user: Clothears)

Thus illustrating nicely why anonymous posts should be disallowed and posters should be obliged to use a unique handle.

18:25 Reply: SQL compliance on Wikipedia (artifact: f850d1e374 user: Clothears)

SQLite is serving a different market to the others. That it is almost certainly mostly compatible with the other, server-based, SQL offerings is good enough. And I can't imagine doing a feature comparison by looking at some certain-to-be-outofdate page on Wikipedia.

20:46 Reply: Select Query will not work in PHP program (artifact: 616cb77fd3 user: Clothears)
How d'ye expect your insertRecord method to know what $db is? You need to pass that through to that method as a parameter:

insertRecord ($db, $query);                        // call to function to INSERT query to database

function insertRecord ($db, $sql)
     echo $sql; // for testing purposes only
     $ret = $db->exec($sql);
          echo $db->lastErrorMsg();
          echo "Records created successfully\n";

and why are you closing the database in your insertRecord method?

17:30 Reply: SQL compliance on Wikipedia (artifact: eb65be0274 user: Clothears)

You've read:


have you?

13:07 Reply: Return boolean if item EXISTS in database (artifact: fddd44cf7a user: Clothears)

Doesn't appear that you have tried the documentation. Here's an opener, look at the example:


11:20 Reply: Return boolean if item EXISTS in database (artifact: 2025a68fe0 user: Clothears)

No, it'll return a result set. You then need to look in the result set and see what is there.

19:48 Reply: Forum Critique (artifact: e0a108901d user: Clothears)

Fine here with Safari, too.

16:06 Reply: Anonymous posting should be disabled on SQLite Forum (artifact: 640d043870 user: Clothears)

Agree with dd. Ryan's point is the salient one.

19:21 Reply: Welcome (artifact: 730a36ecb2 user: Clothears)

I'll have a look there. It's unlikely I'd be contributing patches, it's nearly 30 years since I wrote any C. C-like languages, PHP, javascript, perhaps, but that's all.

I'll stick to suggestions and leave it at that.

19:19 Reply: Plan for an 64 bit binary for macosx catalina? (artifact: c39a5a9ae0 user: Clothears)

As Catalina is 64-bit only, the libsqlite.dylib that is supplied with the OS should b 64-bit only. The supplied sqlite3.app will use that library, or at least it used to.

What about /usr/lib/libsqlite3.dylib ? Isn't that what you need?

14:57 Reply: Welcome (artifact: 8a72036a3e user: Clothears)

The fundamental question is, is there any intent to develop the forum software, or not. If not, no point in making feature requests. If the management is open to putting in effort there, there'd be scope for having a feature request list.

11:42 Reply: Renaming a database (artifact: 4eb9d9f6ba user: Clothears)

Under macOS you can rename files when open for read or write; I do it frequently. But for this to work properly, AIUI it relies on that the filesystem notifies apps that this has happened. I imagine that apps have to register for this service and then have to update their internal info about filenames, but I don't know more about it than that. Even MS gets this nearly right in Office. In Office 2016 for macOS, Word could cope with a file being renamed under it and changed the filename it displays in the window title bar; Excel could not.

21:09 Reply: Forum emails are all going to the SPAM folder (artifact: 2e64506761 user: Clothears)

On 16 Mar 2020, at 19:14, Kees Nuyt noreply932f1faf5@sqlite.org wrote:

Forum post by knu on 2020-03-16 19:14:00 https://sqlite.org/forum/forumpost/4f5bbc9971

For the mailing list, the headers had Sender: sqlite-users sqlite-users-bounces@mailinglists.sqlite.org


Forum emails don't seem to have that sender line anywhere. Maybe just putting that back would be the simplest solution.

That sounds like a very good idea!

No need. Mails from the list have subject line starting with: [sqlite] while those from the forum have a subject line starting with [sqlite-forum]

Pretty simple to filter on [sqlite , which is what I do. The filter marks them as not-spam and sends them into my sqlite mailbox.

13:44 Reply: Forum emails are all going to the SPAM folder (artifact: 12645226a8 user: Clothears)

Make a rule:

Subject starts with [sqlite

and that will work with forum stuff and list stuff.

10:23 Reply: Welcome (artifact: da9aa392b7 user: Clothears)
Where are my preferences? I want to be able to set:

1) Always display posts in chronological order

2) Always formatted

3) For post composition and replies, always plain text.

Where do I set these?