SQLite Forum

Timeline
Login

50 most recent forum posts by user Clothears

2021-10-15
08:51 Reply: Error Code : Error while executing SQL query on database ‘test’: no such column: price (artifact: 2f7b7873e7 user: Clothears)

The table structure image is not visible.

2021-10-09
15:07 Reply: x86_64/amd64 sqlite3 binaries (artifact: ddee2f54de user: Clothears)

macOS has gone 64-bit only. I didn't find the transition particularly onerous: I had to get a more recent version of Office, and a new usenet client, but everything else was already 64-bit.

2021-09-12
21:24 Reply: counter (artifact: f61ce977e6 user: Clothears)

Create the table with one column, your id. Then insert one row setting id to its initial value. All of that is initialisation for your app.

Then when you come to use it, just do:

update file_number set num = num + 1 returning num;

2021-09-04
09:55 Reply: The characters to be escaped in Sqlite3 to prevent Sql Injection (artifact: 51d9c44b80 user: Clothears)

You can avoid all this by using prepared statements.

2021-09-03
21:49 Reply: A suggestion: temp0000, temp0001.. (artifact: 4ca5a2a3ec user: Clothears)

I just enquire if a file with the given name exists, and if so construct a new name by appending a random number from 10000 and 99999 to the filename and trying again until I'm told that this new name doesn't exist.

2021-08-20
20:24 Reply: Updated Website Idea (artifact: 3647732d43 user: Clothears)

Horrible font.

2021-08-03
17:15 Reply: null character sorts greater than 0x7f and less than 0x80 (artifact: f02fa8cf50 user: Clothears)
"I had supposed this was one of the big advantages of SQLite over, i.e. PostgreSQL."

Perhaps you meant e.g. instead of i.e.

i.e. - that is
e.g. - for example
2021-08-02
20:47 Reply: null character sorts greater than 0x7f and less than 0x80 (artifact: 3158bdaef9 user: Clothears)

Errm, no. From the Wikipedia article on UTF-8:

https://en.wikipedia.org/wiki/UTF-8

"The standard specifies that the correct encoding of a code point uses only the minimum number of bytes required to hold the significant bits of the code point. Longer encodings are called overlong and are not valid UTF-8 representations of the code point. This rule maintains a one-to-one correspondence between code points and their valid encodings, so that there is a unique valid encoding for each code point. This ensures that string comparisons and searches are well-defined."

The minimum number of bytes to encode a null is one.

2021-07-14
21:19 Reply: Help with a query (artifact: a40e60364d user: Clothears)
A number of the types you specify don't actually exist in SQLite, see https://www.sqlite.org/datatype3.html
2021-07-13
21:14 Reply: More math help and what I've learned (artifact: 4c44136b73 user: Clothears)

Would putting in a dummy first row with all zeros help, as part of the initialisation?

2021-07-10
16:09 Reply: Can this SQL be simplified? (artifact: af3952b4df user: Clothears)

Thanks. That allowed me to replace six selects with two.

13:58 Reply: Can this SQL be simplified? (artifact: 3c8b171cd2 user: Clothears)
Not sure I understand this syntax:

    (select count(*) cnt from T2) t2_cnt,
    (select maxrows from T1) max_t1

which seems to be defining new tables, as far as I can see from the later usage such as:

   ... when t2_cnt.cnt > max_t1.maxrows ...

but I can't get that from the SQLite syntax diagrams. In any case, I get this error in the sqlite CLI:

Error: no such column: t2_cnt.cnt

so I'll have a look at max().
12:03 Post: Can this SQL be simplified? (artifact: c8c91ca8d6 user: Clothears)
I have this which gives me expected results:

select id from T2 order by mydate limit case when (select count(*) from T2)>(select maxrows from T1) then (select count(*) from T2)-(select maxrows from T1) else 0 end;

This returns the excess oldest rows in T2 (each row has a date stamp) if there are more than maxrows in T2, otherwise no rows.

Is there a way to avoid repeating the selects in the 'then' portion? I tried using 'as' but can't see how to use the column-aliases.
2021-07-01
22:04 Reply: Clustered vs Non Clustered Indexes in SQLite (artifact: c0cb82e301 user: Clothears)

Perhaps, since SQLite is not a server-based engine, it doesn't matter. But since I don't know what you are talking about, I'll await input from those who do.

2021-06-14
11:00 Reply: create table (artifact: aeac6d1d05 user: Clothears)

As Gunter said.

Also you can't spell CURRENT_TIMESTAMP.

2021-06-01
13:37 Reply: How to relate a row with more than one topic? See example (artifact: ef8a41bbde user: Clothears)

5000 rows in an SQLite table is nothing. And which column format does SQLite not support?

2021-05-31
21:29 Reply: How to relate a row with more than one topic? See example (artifact: b2a788f06f user: Clothears)

I'd make another table to link the two together, with two columns: topicid and quoteid. Add a row to this table for each connection.

2021-05-28
14:53 Reply: partial file load? (artifact: a7db319e8b user: Clothears)

Have you tried doing the same using the SQLite shell program? (sqlite3.exe, sqlite3.app, ...)

13:29 Reply: nested case statement not working (artifact: 10b9bba665 user: Clothears)

Why is this important for SQLite?

2021-04-20
18:31 Reply: Why no release announcement in this Forum anymore? (artifact: 4eb6bf9cd3 user: Clothears)

No thanks about announcments on the forum. Let people sign up to get them if they want them.

2021-04-18
08:13 Reply: SQL query help for between times and grouping times (artifact: b9ee85aa7f user: Clothears)

Personally I'd be asking them to convert to storing date and time in one column, the number of seconds since the epoch. That way you properly separate data storage from data presentation and comparisons then become trivial and comprehensible.

2021-04-11
21:57 Reply: What does, if many threads and/or processes need to write the database at the same instant, mean? (artifact: 538711653d user: Clothears)

Include it in a wrapper function that also takes care of error handling and reporting when setting up the connection. That way you don't have to worry about it. AFAIK you always have to set the timeout each time you connect, or perhaps supply your own timeout function (not sure about that one).

12:22 Reply: What does, if many threads and/or processes need to write the database at the same instant, mean? (artifact: 4336367abb user: Clothears)

That's only 0.5 sec. Next try with 20 secs.

2021-03-27
21:47 Edit reply: Error: near "returning": syntax error (artifact: 167d1851c9 user: Clothears)

Third-Mini% sqlite3

SQLite version 3.35.2 2021-03-17 19:07:21

Enter ".help" for usage hints.

Connected to a transient in-memory database.

Use ".open FILENAME" to reopen on a persistent database.

sqlite> create table test(id integer primary key);

sqlite> insert into test default values returning id;

1

sqlite>

Third-Mini%

What's yer problem?

21:45 Reply: Error: near "returning": syntax error (artifact: a4dde39b61 user: Clothears)

Third-Mini% sqlite3 SQLite version 3.35.2 2021-03-17 19:07:21 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> create table test(id integer primary key); sqlite> insert into test default values returning id; 1 sqlite>

Third-Mini%

What's yer problem?

2021-03-14
19:56 Reply: Feature request: pragma for changing the precision / format of CURRENT_TIMESTAMP to YYYY-MM-SSTHH:MM:SS.SSS (artifact: 27b96c22cd user: Clothears)

Accuracy and precision have different but precise meanings where scientific measurement is concerned. Accuracy should be specified by its +/- limits, such as 3.3 +/- 0.1, that is, with an error figure.

Precision indicates how many significant figures (digits) a value is specified to. So, two in the above example. If the error is in the second digit, there is not much point in specifying greater precision. Thus, 3.31234 +/- 0.111 adds no utility and no extra information to the result.

Personally if I'm doing timing measurements, I'd ask the OS directly rather than expect SQLite to help.

2021-03-11
21:43 Reply: How to READ and WRITE an image to an SQLITE BLOB FIELD (artifact: 82d85c27bc user: Clothears)
  1. I don't see any PHP there.

  2. Why are you doing a readfile inside an INSERT?

  3. Why are you giving an example using the sqlite3 CLI program? If you want to do it in PHP, do it in PHP.

Something like this:

 $sql = 'update mytable set imgdata = ? where id = ?';
 $sth = $dbh->prepare ($sql);
 $result = $sth->bindValue (1, $blobdata, SQLITE3_BLOB);
 $result = $sth->bindValue (2, $id, SQLITE3_INTEGER);
 $res = $sth->execute ();

Adjust as appropriate. I've removed checks on $result to simplify.

2021-02-15
17:43 Reply: Unicode and CLI for Mac (artifact: ef6adb6187 user: Clothears)

Me, I don't much care what it does in bash or zsh. Here is what I do:

Third-Mini% sqlite3 SQLite version 3.28.0 2019-04-16 19:49:53 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> select 'à', hex('à'); à|C3A0 sqlite>

The à character created in the usual way with Alt-` followed by a.

2021-02-14
15:42 Reply: Unicode and CLI for Mac (artifact: bdcf8ff8f4 user: Clothears)

Keyboard Viewer, not Character Viewer, on Mojave.

á was obtained by pressing and releasing Alt-e, then pressing a.

Note that these Viewers don't need to be showing while you press the keys, they just show what to do.

2021-01-14
20:49 Reply: Can't migrate auto-increment bigint columns (artifact: 275b73944d user: Clothears)

If you read the refernce I gave, you will understand why.

17:25 Reply: Can't migrate auto-increment bigint columns (artifact: acb14a1a34 user: Clothears)

Saying 'bigint' will get you an integer. See:

https://www.sqlite.org/datatype3.html

and look at sections 2 and 3.1.

2021-01-03
19:20 Reply: Statements outside of explicit transactions in SQLite (artifact: c5c281dce2 user: Clothears)

Far as I know, any isolated statement (select, insert, etc) is automatically surrounded by BEGIN/COMMIT unless you've already opened an explicit BEGIN. My app has a couple of hundred usually isolated statements to update this or that part of some db or other. If I had to BEGIN/COMMIT each of these life would be rather tedious.

I also have a couple of places where I know I'll be doing a lot of inserts, so I do all those inside a transaction, for speed.

That clear?

2020-12-10
16:31 Reply: Sqlite3 Trigger (artifact: e2304618e6 user: Clothears)

A better question is: "What is the point of varchar?"

2020-12-05
17:09 Reply: Support of unicode operators like ≠? (artifact: 6270a06418 user: Clothears)

You may also like to look here:

https://en.wikipedia.org/wiki/UTF-8

UTF-8 is just one way of encoding Unicode code-points for storage. There are others, but its main advantages are:

  1. Variable length units from one to four bytes. One byte units map exactly to ASCII.
  2. As its byte oriented it has no endianness issue.
  3. It's the most-used encoding.
2020-12-04
17:41 Reply: Support of unicode operators like ≠? (artifact: 26d4247867 user: Clothears)

Just make sure they are UTF-8 compliant, which most stuff is these days. That does not, however, mean that such as SQLite, in terms of its language syntax, has to support large numbers of UTF-8 characters. That would risk expanding the library size rather a lot.

I find this site useful:

https://www.utf8-chartable.de/unicode-utf8-table.pl?number=1024

I prefer UTF-8 to other encodings as its bottom page is the same as ASCII, and being a variable length encoding, there are no endianness issues.

2020-11-12
17:06 Reply: Why Sqlite parse error: line 1:94: expecting RPAREN, found 'OR' in CHECK constraint? (artifact: 5b6cf729a2 user: Clothears)

Use the sqlite3 terminal program that can be found at sqlite.org if it is not already on your machine.

2020-09-19
21:50 Reply: Why was there no error message at CREATE TABLE? (artifact: a8be57bc70 user: Clothears)

It might also be useful to indicate what would be the affinity of column a in:

create table xxx (a text b integer);

So perhaps somewhere in section 3.1 on:

https://www.sqlite.org/datatype3.html

would be the place.

17:06 Reply: Why was there no error message at CREATE TABLE? (artifact: 58d8b9b0b8 user: Clothears)

I see, thanks.

A note somewhere would be welcome.

15:20 Post: Why was there no error message at CREATE TABLE? (artifact: 6318067e5a user: Clothears)

I just came within a whisker of distributing an app with this error:

Third-Mini% 
Third-Mini% sqlite3
SQLite version 3.28.0 2019-04-16 19:49:53
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table xx (a text, b integer);
sqlite> .schema
CREATE TABLE xx (a text, b integer);
sqlite> .tables
xx
sqlite> create table bad (a text b integer);
sqlite> .schema
CREATE TABLE xx (a text, b integer);
CREATE TABLE bad (a text b integer);
sqlite> .tables
bad  xx 
sqlite> select a from bad;
sqlite> select b from bad;
Error: no such column: b
sqlite> 

Should there not have been a syntax error reported at the CREATE TABLE stage?

2020-09-12
17:27 Reply: Without rowid (artifact: e35060dd69 user: Clothears)

But if I've read the docs correctly, that's an internal matter which should mean, in my use case, a slight speedup and reduction in file size. Anything else?

16:35 Post: Without rowid (artifact: 872426be51 user: Clothears)

In an idle moment today, I happened to be looking at the WITHOUT ROWID docs, and seeing an example of when one might use it in a database I have, did the following. The original schema was this, in the BEFORE database:

create table info (token text primary key, ct1 integer, ct2 integer, ct3 integer); CREATE TABLE counts ( xtotal integer not null default 0, ytotal integer not null default 0 ); CREATE TABLE globals (app_version integer, data_version integer default 0);

I then created an AFTER database with the same schema, except that the info table was defined thus:

create table info (token text primary key, ct1 integer, ct2 integer, ct3 integer) without rowid;

I then copied the data from BEFORE to AFTER as follows, using the CLI:

sqlite3 AFTER SQLite version 3.28.0 2019-04-16 19:49:53 Enter ".help" for usage hints. sqlite> attach BEFORE as sp; sqlite> insert into main.info select * from sp.info; sqlite> insert into main.counts select * from sp.counts; sqlite> insert into main.globals select * from sp.globals; sqlite> detach database sp; sqlite3> ctrl-d

I then exchanged the filenames so that my app would use the new database. Note that info has circa 170k rows, and the other two tables one row each. BEFORE is about 6.95Mbytes, AFTER is about 3.87Mbytes. So neither is large.

Is there any reason that BEFORE and AFTER might not be functionally identical?

First time I ran my app using AFTER, it appeared to go into a loop and I got "database locked" when trying to look at AFTER with the CLI. It appears happier now.

2020-09-03
21:09 Reply: Pre-release Versions of System.Data.SQLite packages? (artifact: 1b42d988fa user: Clothears)

But:

**An enhancement such as .read ' currently in the pipeline can save hours of work.**

this has nothing to do with the SQLite library, but rather with the CLI application. You can download and run the most recent version of that at any time.

2020-08-31
07:39 Reply: HEX - What is the reverse function? (artifact: ec7ded4b38 user: Clothears)

I've occasionally found it useful when debugging and I want to know exactly what set of bytes is in a column, without that set being interpreted by some intermediate bit of software.

2020-08-30
07:37 Reply: start (artifact: c15a946d1a user: Clothears)

What OS are you running and where did you download "sqlite" from? Seems unlikely you are running the sqlite3 CLI, since you don't click on anything to add a table, you type an SQL statement.

Here's what I get when I do that, in a Terminal window:

Third-Mini% sqlite3

SQLite version 3.28.0 2019-04-16 19:49:53

Enter ".help" for usage hints.

Connected to a transient in-memory database.

Use ".open FILENAME" to reopen on a persistent database.

sqlite> create table xxx (yyy integer);

sqlite>

2020-08-27
20:02 Reply: SQLITE_ENABLE_STAT4 triggers failures in analyze3.test (artifact: 2487b24b3b user: Clothears)

Wouldn't it be more like at least 87! ??

Seems that 87! is around 2.1e132, according to the Giant Brain.

2020-08-18
21:42 Reply: Finding database file (artifact: 6b8cd5095a user: Clothears)

JavaScript typically has no access to the local file system, when run in a browser. How are you running this javscript?

2020-08-08
20:48 Reply: SQLite 3.33.0 beta-1 (artifact: 63375f383f user: Clothears)

Sounds to me like an unreasonable ask. Your "instead of" is actually four results' worth of output - or looks like that, anyway.

And nothing stops you downloading the source of the CLI and applying appropriate modifications.

2020-08-01
19:41 Reply: null character sorts greater than 0x7f and less than 0x80 (artifact: f24cc3f152 user: Clothears)

That's non-standard UTF-8, as described here:

https://en.wikipedia.org/wiki/UTF-8

See the section on Overlong Encodings. The following section to that also indicates why overlong encodings are not a good idea.

2020-07-30
13:54 Edit reply: SQLITE python 3 issue, please help (artifact: 40dc233bf9 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.

The version that the CLI has is:

SQLite version 3.28.0 2019-04-16 19:49:53

The CLI seems to be over a meg so I'm assuming the library is built in, possibly with different compile-time options than the .dylib.

These are the versions that come with Mojave, anyway.

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.

More ↓