SQLite Forum

Timeline
Login

50 most recent forum posts by user Trudge

2021-09-14
16:09 Reply: counter (artifact: 681950c63c user: Trudge)

What an excellent piece of coding. I can figure several ways I can use this immediately. Thank you.

2021-08-29
16:16 Post: A good reason to choose SQLite (artifact: 6af46b2d95 user: Trudge)

I have several web sites that I use SQLite for to manage collections of books. Recently my host was hit with a major incident wiping out several of their shared drives along with backups. But in my case it's a matter of uploading a single file to get each database back up. Quite a change from MySQL.

2021-07-31
15:06 Reply: Odd CL behaviour (artifact: ede3f5ccfc user: Trudge)

Excellent points and good advice. Thank you. I ran into that situation with the native installation of sqlite3 so have gotten into the habit of renaming mine 'sqlite' and moving it into my PATH. I would be wise to stick with the Homebrew versions and re-compile if needed.

15:02 Reply: Odd CL behaviour (artifact: 117955119d user: Trudge)

If I recall the original executable did come from the sqlite repository, and was not compiled by me. But I have used Homebrew versions prior to this on other versions. But this behaviour came 'out of the blue' after working fine since the d/l.

And I have also had to re-compile various versions to get specific behaviour. Perhaps I should follow the advice of @wyoung and stick with the Homebrew compiles. And if something is needed I can easily compile myself.

Thank you all who responded and provided (as usual) some good instruction and advice.

2021-07-30
16:07 Post: Odd CL behaviour (artifact: b6698dcae7 user: Trudge)

Funny thing happened on the way to my Mac Terminal window.

Normally when I use sqlite and scroll through the command history, I can move back and forth one word-at-a-time on the previous command by using Alt-left arrow and Alt-right arrow. Today that stopped working and instead of moving the cursor those keys now produced either a 'b' or an 'f' in the line.

So first thing to do is close the Terminal window and open a new one - that restarts bash. Then run sqlite again. Same thing.

So next thing to do is reboot. Did that. Same thing.

So next thing to do is google for an answer. Did that. Nothing helped.

So next thing to do is d/l a new version of sqlite. No new version is available, but grabbed the source code anyway.

Then recompiled it - make install; make. That finally worked.

But now the question is why was that necessary? The executable I have should not have to be re-compiled. But that is what worked.

So fire put out and moving on (using Alt-left arrow / Alt-right arrow). 😎

SQLite version 3.37.0 2021-07-19 14:00:29

2021-07-16
22:18 Edit: (Deleted) (artifact: 049fdec4e1 user: Trudge)
Deleted
21:42 Post: (Deleted) (artifact: d659cd362a user: Trudge)

I'm trying to populate a new table with values from another table, but I need to insert only 1 row at a time in order to put things in a particular order.

Schema for both tables:

CREATE TABLE IF NOT EXISTS "input" (
	text	TEXT CHECK(text != ''),
	keystrokes	TEXT,
	Alt_keys	TEXT,
	id	INTEGER NOT NULL,
	PRIMARY KEY(id)
);

CREATE TABLE alphauni(id integer,char text,keys text);

I'm copying from 'input' to 'alphauni' using something like:

insert into alphauni select id,text,keystrokes from input where keystrokes like ("%alt 0144%");

The 'like' phrase holds a unique string from that row in the table.

Every row of 'input' has unique values which I am using for the like, and this has worked for 24 rows. Then suddenly it stops working - no entries in the 'alphauni' table at all.

Any ideas what is going on please?

2021-07-08
20:43 Reply: edit() function usage (artifact: 7678fbf7e5 user: Trudge)

I did not know about the CLI for BBEdit but -w does work in Terminal. Tried it in sqlite and worked there as well: update books set notes=edit(notes,'bbedit -w') WHERE id=1;

Thanks @RandomCoder eh!

20:36 Reply: edit() function usage (artifact: 1a56699841 user: Trudge)

Yes, the file exists in /private/var/.... and I can edit it. After saving I then close that file (editor BBEdit). But when I check 'notes' it has not changed.

Using 'TextEdit' it tells me: sh: TextEdit: command not found Error: EDITOR returned non-zero

Then tried 'update books set notes=edit(notes,'nano') WHERE id=1;' which DID work.

So it seems BBEdit is doing something wonky but at least I got it working. I had set Thanks to all for the suggestions.

17:48 Post: edit() function usage (artifact: fbe39f955e user: Trudge)

I'm running on a Mac and am having some issues with the edit() function. BBEdit is my default editor but I also have the native TextEdit available. I can get it almost working with 'update books set notes=edit(notes,'BBEDIT') WHERE id=1;' which does open BBEdit with the requested document loaded. But when I save and quit the editor it does not update the text in the DB.

Is there something I'm missing?

2021-06-11
16:52 Reply: Using an alias in queries (artifact: b0c9444644 user: Trudge)

SOLVED I've managed to get what I need by saving the data from the query into an array: Now the array ... @kwtop has 10 elements $NumKWBooks: 173 Programming 67 red 38.73% Mathematics 47 orange 27.17% Perl 24 yellow 13.87% Knots 22 green 12.72% Calculus 13 blue 7.51%

This is WITHOUT using an alias. Tried it with a hash (no alias) but still something wonky, maybe with the way I'm coding it. Either way I can now move forward with the SVG process.

Thank you to all who burrowed into this. Very much appreciated.

14:38 Reply: Using an alias in queries (artifact: 14540d4bf6 user: Trudge)

Thank you for the in-depth analysis and apologies for the confusion. When I mentioned CL (Command Line) I was referring to Perl running in a shell window. Here is the actual SQL statement that I run: sqlite> select count(*) as NUM,keyword from keywords group by keyword order by count(keyword) desc limit 5; NUM keyword


67 Programming 47 Mathematics 24 Perl
22 Knots
13 Reference
sqlite>
That output is correct. I only want the top five keywords used in a library DB. There is obviously no Perl hash involved here.

Note that the results are within the query loop. But when I try to access the hash %KeyWords from a perl loop, I have problems. I am trying create a donut chart with SVG of the top 5 keywords. For that I need certain calculations, one of which is the percentage of each keyword to the total number of keywords.

But notice that in the SQL query I use an alias for the count(*). I have to use an alias because I don't know the keyword before hand - the library DB changes all the time. I could say count(keyword) as NUM but that would not make any difference - the key to the whole issue is using an alias.

Apparently in SQLite an alias is only available during the query. So when I try to access the hash outside of the query loop, it fails.

The line you refer to why do you say "$records[1]" but "records[0]" without the dollar sign? is wrong. I had pasted code in from a previous version. Many apologies.

I'm not sure if showing the insert statements would be any help to solving the issue. As mentioned the DB grows as I add / delete books etc. and that is all done in another Perl script. But if you're interested: $sth=$dbh->prepare(qq{insert into keywords(authorid,bookid,keyword) values ($AuthorID,$TitleID,"$kw")}); As further explanation, I am trying to create a dynamic Perl hash from an SQLite query using an alias. So not sure whether this is an SQLite issue or a Perl issue. I've posted on a Perl forum and still no joy.

2021-06-10
14:32 Edit reply: Using an alias in queries (artifact: 299a9c9961 user: Trudge)

Apologies for the confusion, and thank you for taking some time with this. CL is Command Line.

schema: CREATE TABLE sqlite_sequence(name,seq); CREATE TABLE IF NOT EXISTS "Keywords" ( id INTEGER, authorid INTEGER, bookid INTEGER, keyword TEXT, PRIMARY KEY(id AUTOINCREMENT) ); CREATE TABLE test ( text TEXT ); CREATE TABLE IF NOT EXISTS "Books" ( id INTEGER, authorid INTEGER, bookid INTEGER, pubyear INTEGER, title TEXT, notes TEXT, author TEXT, PRIMARY KEY(id AUTOINCREMENT) ); CREATE TABLE IF NOT EXISTS "Authors" ( id INTEGER, authorid INTEGER NOT NULL, author TEXT NOT NULL, PRIMARY KEY(id AUTOINCREMENT) );

Here is a typical bit of the Keywords table: id authorid bookid keyword


2122 1 330 Mathematics 2123 1 331 Mathematics 2124 1 323 Philosophy 2125 2 404 Knots 2126 5 82 Document Design 2127 5 152 Web Design 2128 6 550 Scalable Vector Graphics 2129 7 179 Physics
2130 7 178 Quantum Theory 2131 8 294 bash

A CL version of the code: @SubColors=("red","orange","yellow","green","blue"); $ndx=0;

$sth=$dbh->prepare(qq{ select count(keyword) as NUM,keyword from keywords group by keyword order by count(keyword) desc limit 5;}); $sth->execute(); while (@records=$sth->fetchrow_array()) { print qq{records[0] $records[1] $SubColors[$ndx]n}; $NumKWBooks+=$records[0]; $KeyWords{keyword} = $records[1]; # dynamic hash $KeyWords{numbooks} = $records[0], # dynamic hash $ndx+=1; } $sth->finish();

print qq{$NumKWBooks: $NumKWBooksn}; my $numKeys=keys %KeyWords; print "$numKeys: $numKeysn"; foreach $key(keys %KeyWords) { $percentKW=$KeyWords{numbooks} / $NumKWBooks * 100; print qq{$KeyWords{keyword}: $tab }; printf "%.2f%%n",$percentKW; }

which results in: records[0] Programming red records[0] Mathematics orange records[0] Perl yellow records[0] Knots green records[0] Calculus blue $NumKWBooks: 5 $numKeys: 2 Calculus: 20.00% Calculus: 20.00%

As you can see $records[0] is not getting populated since it is an alias in the query loop. Also the number of keywords in the 5 chosen is wrong - it should be over 100.

This is the bit that is crucial for my purposes since I am using these values to build an SVG donut chart of the top 5 keywords in this library. No joy so far.

14:17 Reply: Using an alias in queries (artifact: c9dbb9fce2 user: Trudge)

Apologies for the confusion, and thank you for taking some time with this. CL is Command Line.

schema: CREATE TABLE sqlite_sequence(name,seq); CREATE TABLE IF NOT EXISTS "Keywords" ( id INTEGER, authorid INTEGER, bookid INTEGER, keyword TEXT, PRIMARY KEY(id AUTOINCREMENT) ); CREATE TABLE test ( text TEXT ); CREATE TABLE IF NOT EXISTS "Books" ( id INTEGER, authorid INTEGER, bookid INTEGER, pubyear INTEGER, title TEXT, notes TEXT, author TEXT, PRIMARY KEY(id AUTOINCREMENT) ); CREATE TABLE IF NOT EXISTS "Authors" ( id INTEGER, authorid INTEGER NOT NULL, author TEXT NOT NULL, PRIMARY KEY(id AUTOINCREMENT) );

Here is a typical bit of the Keywords table: id authorid bookid keyword


2122 1 330 Mathematics 2123 1 331 Mathematics 2124 1 323 Philosophy 2125 2 404 Knots 2126 5 82 Document Design 2127 5 152 Web Design 2128 6 550 Scalable Vector Graphics 2129 7 179 Physics
2130 7 178 Quantum Theory 2131 8 294 bash

A CL version of the code: @SubColors=("red","orange","yellow","green","blue"); $ndx=0;

$sth=$dbh->prepare(qq{ select 1 as NUM,keyword from keywords group by keyword order by count(keyword) desc limit 5;}); $sth->execute(); while (@records=$sth->fetchrow_array()) { print qq{records[0] $records[1] $SubColors[$ndx]n}; $NumKWBooks+=$records[0]; $KeyWords{keyword} = $records[1]; # dynamic hash $KeyWords{numbooks} = $records[0], # dynamic hash $ndx+=1; } $sth->finish();

print qq{$NumKWBooks: $NumKWBooksn}; my $numKeys=keys %KeyWords; print "$numKeys: $numKeysn"; foreach $key(keys %KeyWords) { print qq{$KeyWords{keyword}: $tab }; printf "%.2f%%n",$percentKW; }

which results in: records[0] Programming red records[0] Mathematics orange records[0] Perl yellow records[0] Knots green records[0] Calculus blue $NumKWBooks: 5 $numKeys: 2 Calculus: 20.00% Calculus: 20.00%

As you can see $records[0] is not getting populated since it is an alias in the query loop. Also the number of keywords in the 5 chosen is wrong - it should be over 100.

This is the bit that is crucial for my purposes since I am using these values to build an SVG donut chart of the top 5 keywords in this library. No joy so far.

2021-06-09
18:20 Reply: Using an alias in queries (artifact: 39f0e77d02 user: Trudge)

On doing some research on using 'alias' I discovered that 'An alias only exists for the duration of that query.' Is this a fact for SQLite?

15:37 Post: Using an alias in queries (artifact: 172fccd5a2 user: Trudge)

I've noticed that using an alias in a query seems to behave different in a web edition of code compared to a CL version of the same code.

I am pulling data from a DB via Perl and trying to create a hash with dynamic keys. The database contains books, authors, and keywords. I am trying to get the top 5 keywords based on the number of times it appears in the DB.

I need the keys to be dynamic because as the DB grows the most popular keywords will change.

The web code: my $NumKWBooks=0; my @SubColors=("red","orange","yellow","green","blue"); $ndx=0; $sth=$dbh->prepare(qq{ select count(keyword) as NUM,keyword from keywords group by keyword order by NUM desc limit 5;}); $sth->execute(); while (@records=$sth->fetchrow_array()) { print qq{$records[0] $records[1] $SubColors[$ndx]
}; $NumKWBooks+=$records[0]; $KeyWords{keyword} = $records[1]; $KeyWords{numbooks} = $records[0], $ndx+=1; } $sth->finish(); print "
"; print qq{$NumKWBooks: $NumKWBooks
}; my $numKeys=keys %KeyWords; print "$numKeys: $numKeys
"; foreach $key(keys %KeyWords) { my $percentKW=$KeyWords{numbooks} / $NumKWBooks * 100; print qq{$KeyWords{keyword}: $tab }; printf "%.2f%%
",$percentKW; }
This renders in a browser: 64 Programming red 47 Mathematics orange 24 Perl yellow 22 Knots green 13 Calculus blue

$NumKWBooks: 170 $numKeys: 2 Calculus: 7.65% Calculus: 7.65%

However in CL I get the following: records[0] Programming red records[0] Mathematics orange records[0] Perl yellow records[0] Knots green records[0] Calculus blue $NumKWBooks: 170 $numKeys: 2 Calculus: 7.65% Calculus: 7.65%

Note the missing value for $records[0]. The only reason I can figure for this is the way the alias is rendered select count(keyword) NUM

And why are there only 2 keys in the hash, but both the same?

I would appreciate any advice on how to get that working.

2021-03-29
16:09 Reply: VPN log in problem (artifact: 780efab355 user: Trudge)

Well this is odd. Now it works fine. Apologies.

2021-03-27
16:24 Reply: Novice question about running UTF-8 on CLI from file. (artifact: 2c1b69927e user: Trudge)

I program on a Mac and use BBEdit exclusively for programming. It is available for Windows. 13.5.4 Release Notes

2021-03-22
14:54 Reply: HTML login/registration with SQLite db table (artifact: f3b3f561c6 user: Trudge)

This sounds like you may be looking for a CGI solution, a program that runs on your host server. Even using JavaScript I don't believe you can do what you want with HTML. You may be looking at learning a programming language such as Python, Perl, or something else. Good luck.

2021-03-12
16:09 Reply: How to READ and WRITE an image to an SQLITE BLOB FIELD (artifact: f7db2746be user: Trudge)

I've done several personal and client web sites using SQLite and MySQL / mariadb that need to store images, but I have always found from research and personal experience that it is better to save the PATH to the image in the database, rather that the actual image. For one thing you can't really 'search' an image - only whatever tags are placed on it. And it's usually better to let the OS handle files.

2021-02-17
15:27 Reply: Unicode and CLI for Mac (artifact: 538065f550 user: Trudge)

Ah, thank you for that. I won't be shy of reaching out again if needs be then.

2021-02-16
16:01 Reply: Unicode and CLI for Mac (artifact: 4f595a9a3f user: Trudge)

I've not used the -batch mode, so gave it a whirl. ~ [user] sqlite -batch select x'c3a1',hex(??); Error: near line 1: no such column: á

This is typed in, but even when pasted in it came back empty, but changed the pasted input: SELECT x'c3a1', hex('??');

It seems this thread (as helpful as it has been) is out of scope for this forum so I don't want to take up any more resources here. I've managed to get it working under Big Sur, and that will likely be my next migration.

15:49 Reply: Unicode and CLI for Mac (artifact: 2e16a39320 user: Trudge)

Then you are lucky I guess, because when I do that (Alt - backtick a) on High Sierra I get sqlite SQLite version 3.34.0 2020-12-01 16:14:00 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> a This is with Character Viewer in Unicode Hex Input mode.

2021-02-15
15:24 Reply: Unicode and CLI for Mac (artifact: eaa68928a0 user: Trudge)

OK, then I need to understand more about shells. For example, when I enter echo "SELECT x'c3a1', hex('á');" | sqlite3 á|C3A1 it obviously works as it should. I'm starting in bash, then piping to sqlite, then back to bash. This is what was confusing me: sqlite is working in this case but not from the stand-alone CLI.

15:16 Reply: Unicode and CLI for Mac (artifact: 24814cd284 user: Trudge)

I believe we may be talking about the same thing. In High Sierra it's available from my title bar, and has several options (ABC, ABC Extended, Canadian English, Unicode Hex Input, Show Emoji and Symbols, Show Keyboard Viewer, Open Keyboard Preferences). If I pick Show Keyboard Viewer it displays a full Mac keyboard.

But even then it doesn't give me any accented characters. As soon as I hit Alt it throws me back to the bash prompt.

2021-02-14
15:22 Edit reply: Unicode and CLI for Mac (artifact: a51a6e5306 user: Trudge)

Holding a key down just does a repeated character (I've set it that way). And this OSX iMac High Sierra on a full keyboard with numeric keypad, not iOS.

Just tried your idea but still no joy. I've toggled through the Character Viewer choices (ABC, ABC - Extended, Unicode Hex Input) while doing 'Alt-E a', Shift-Alt E a' but cannot get an accented a.

15:21 Reply: Unicode and CLI for Mac (artifact: 727e8c0605 user: Trudge)

Holding a key down just does a repeated character (I've set it that way). And this OSX High Sierra on a full keyboard with numeric keypad, not iOS.

Just tried your idea but still no joy. I've toggled through the Character Viewer choices (ABC, ABC - Extended, Unicode Hex Input) while doing 'Alt-E a', Shift-Alt E a' but cannot get an accented a.

15:07 Reply: Unicode and CLI for Mac (artifact: b626f9b713 user: Trudge)

That method is what I've been using though it took me a while to find it. And if you've been following this thread you'll see that I've not had any success with it on HIgh Sierra, but it is working on Big Sur.

I've decided it is NOT a problem with sqlite, rather the interaction between it and the current bash shell in High Sierra. So yes this out of scope.

I would like to thank you and all the others who had input and suggestions for me.

2021-02-13
17:33 Reply: Unicode and CLI for Mac (artifact: 35597152dc user: Trudge)

OK, I'm now on a different iMac running Big Sur 11.2.1 with zsh as the default shell. The default system sqlite3 lets me enter Unicode characters as long as Character Viewer is set to 'Unicode Hex input'. user [/Users/user] : which sqlite3 /usr/bin/sqlite3

user [/Users/user] : locale LANG="en_CA.UTF-8" LC_COLLATE="en_CA.UTF-8" LC_CTYPE="en_CA.UTF-8" LC_MESSAGES="en_CA.UTF-8" LC_MONETARY="en_CA.UTF-8" LC_NUMERIC="en_CA.UTF-8" LC_TIME="en_CA.UTF-8" LC_ALL=

user [/Users/user] : sqlite3 SQLite version 3.32.3 2020-06-18 14:16:19 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> select x'c3a1',hex('á'); á|C3A1

So it does appear to be a problem with the interaction between the bash shell and sqlite.

16:01 Edit reply: Unicode and CLI for Mac (artifact: 2e401650eb user: Trudge)

This is also where I seem to be having issues - between the OSX shell and sqlite CLI. Using the Hex codes on this page I can enter properly rendered characters in my bash shell, but not in the sqlite shell.

But piping a command to sqlite from the shell works! DUH?

I currently run High Sierra 10.13.6 on my main machine and have found using Perl to enter any hex codes to work well. Most of the work I do is in a web environment anyway and Perl is back-end choice, so that works fine. But I have another newer box running Big Sur 11.2.1 running zsh as the default shell. I will get sqlite running on it and see what transpires.

15:59 Reply: Unicode and CLI for Mac (artifact: 2800262a44 user: Trudge)

Being a newbie with sqlite I've not used 'cast' yet. You've given me another curve. Thank you.

15:53 Reply: Unicode and CLI for Mac (artifact: 2399e47a11 user: Trudge)

This is also where I seem to be having issues - between the OSX shell and sqlite CLI. Using the Hex codes on this page I can enter properly rendered characters in my bash shell, but not in the sqlite shell.

I currently run High Sierra 10.13.6 on my main machine and have found using Perl to enter any hex codes to work well. Most of the work I do is in a web environment anyway and Perl is back-end choice, so that works fine. But I have another newer box running Big Sur 11.2.1 running zsh as the default shell. I will get sqlite running on it and see what transpires.

2021-02-12
12:38 Reply: Unicode and CLI for Mac (artifact: b09ef175b7 user: Trudge)

Now trying with a newer d/l: SQLite version 3.34.0 2020-12-01 16:14:00 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> select x'c3a1',hex('???'); á|16EC168E16A1

echo "SELECT x'c3a1', hex('á');" sqlite
á C3A1

The first attempt above would not let me paste the command in. So I had to type 'c3a1' while holding Alt. But rendered as displayed with 3 question marks.

Definitely a glitch in my system somewhere.

12:23 Reply: Unicode and CLI for Mac (artifact: 99306d0c12 user: Trudge)

I had forgotten about the system version. Here is my result of the above: [user] type -a sqlite3 sqlite3 is /usr/bin/sqlite3

The recent d/l executable is just 'sqlite'. type -a ~/Downloads/sqlite-tools-osx-x86-3340000/sqlite /Users/user/Downloads/sqlite-tools-osx-x86-3340000/sqlite is /Users/user/Downloads/sqlite-tools-osx-x86-3340000/sqlite

/Volumes/femto/FemtoDocuments/MyDB [user] ~/Downloads/sqlite-tools-osx-x86-3340000/sqlite SQLite version 3.34.0 2020-12-01 16:14:00 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database.

So, yes I had better fix my path :) Thank you.

12:13 Reply: Unicode and CLI for Mac (artifact: f5031f26fb user: Trudge)

Woops. Sorry. LANG="en_US.UTF-8" LC_COLLATE="en_US.UTF-8" LC_CTYPE="en_US.UTF-8" LC_MESSAGES="en_US.UTF-8" LC_MONETARY="en_US.UTF-8" LC_NUMERIC="en_US.UTF-8" LC_TIME="en_US.UTF-8" LC_ALL="en_US.UTF-8"

2021-02-11
17:03 Edit reply: Unicode and CLI for Mac (artifact: e182961465 user: Trudge)

The version I'm using is one from sqlite, but I had recompiled it trying to get some ICU features. Everything else is standard Apple gear.High Sierra 10.13.6 running bash shell. According to some tests I've done the sqlite shell may be wonky. So I'll d/l a vanilla version and try that. Thank you for your input.

OK, I've grabbed a new d/l from sqlite. But same results. SQLite version 3.19.3 2017-06-27 16:48:08 Enter ".help" for usage hints. sqlite> select x'c3a1',hex('

2021-02-11 11:52:41 /Volumes/femto/FemtoDocuments/MyDB [user]

That command is pasted in, not typed, but as soon as it gets to the sqlite> select x'c3a1',hex(' it drops back to the Terminal shell. But piping it to sqlite3 works: /Volumes/femto/FemtoDocuments/MyDB [user] echo "SELECT x'c3a1', hex('á');" | sqlite3 á|C3A1 2021-02-11 11:57:50 /Volumes/femto/FemtoDocuments/MyDB [user]

So obviously something wonky with my system, not the sqlite executable. <sigh> My text editor is BBEdit and entering Unicode characters is fine using Alt [hex code].

17:00 Edit reply: Unicode and CLI for Mac (artifact: c6dd93e1dc user: Trudge)

The version I'm using is one from sqlite, but I had recompiled it trying to get some ICU features. Everything else is standard Apple gear.High Sierra 10.13.6 running bash shell. According to some tests I've done the sqlite shell may be wonky. So I'll d/l a vanilla version and try that. Thank you for your input.

OK, I've grabbed a new d/l from sqlite. But same results. SQLite version 3.19.3 2017-06-27 16:48:08 Enter ".help" for usage hints. sqlite> select x'c3a1',hex('

2021-02-11 11:52:41 /Volumes/femto/FemtoDocuments/MyDB [user]

That command is pasted in, not typed, but as soon as it gets to the sqlite> select x'c3a1',hex(' it drops back to the Terminal shell. But piping it to sqlite3 works: /Volumes/femto/FemtoDocuments/MyDB [user] echo "SELECT x'c3a1', hex('á');" | sqlite3 á|C3A1 2021-02-11 11:57:50 /Volumes/femto/FemtoDocuments/MyDB [user]

So obviously something wonky with my system, not the sqlite executable. <sigh>

16:36 Reply: Unicode and CLI for Mac (artifact: 1e85baa8f6 user: Trudge)

The version I'm using is one from sqlite, but I had recompiled it trying to get some ICU features. Everything else is standard Apple gear.High Sierra 10.13.6 running bash shell. According to some tests I've done the sqlite shell may be wonky. So I'll d/l a vanilla version and try that. Thank you for your input.

16:30 Reply: Unicode and CLI for Mac (artifact: 8f52be6359 user: Trudge)

Apologies. Yes I'm running High Sierra 10.13.6 on an iMac. I've added the following to my scripts:

############# must include for Unicode use 5.14.0; # https://perldoc.perl.org/perluniintro use Encode qw(encode decode); binmode(STDOUT, "encoding(UTF-8)"); use open ":std", ":encoding(UTF-8)"; use locale; use warnings FATAL => "utf8";

############# May be a bit of overkill, so if you see anything not necessary please advise. I've got all my scripts to properly handle adding and retrieving from the sqlite DB and rendering Unicode correctly. I'm using the standard Terminal app and bash is the shell.

16:12 Reply: Unicode and CLI for Mac (artifact: e5349095ba user: Trudge)

Hmm. Very odd indeed. Tried again from sqlite: SQLite version 3.34.0 2020-12-01 16:14:00 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> SELECT x'c3a1', hex('

2021-02-11 11:06:47 /Volumes/femto/FemtoDocuments/MyDB [user]

But surprisingly from bash shell: echo "SELECT x'c3a1', hex('á');" | sqlite3 á|C3A1

So you're correct about something weird there. The version I'm using is one I recompiled trying to get some ICU features. Perhaps that messed something up. Will d/l a vanilla version from sqlite and see what happens. Good catch. Thank you.

2021-02-10
23:05 Reply: Unicode and CLI for Mac (artifact: 1ff345e1da user: Trudge)

Some more information regarding my environment. Perl uses a DBI module to talk to sqlite. My connection code: sub DBconnect { my ($dsn,$dbhost,$dbname,$dbuser,$dbpasswd,$host_name); if ($Locale eq "Local") { $host_name="localhost"; $dbname="/Volumes/femto/FemtoDocuments/MyDB/tunes3.db"; $dsn = "DBI:SQLite:$dbname:$host_name"; # data source name $dbh=DBI->connect("dbi:SQLite:$dbname","","",{RaiseError=>1,PrintError=>1,sqlite_unicode=>1,sqlite_allow_multiple_statements=>1}) or die "Connect to database failed."; } } # DBconnect Locally a run Apache server.

22:26 Reply: Unicode and CLI for Mac (artifact: d746492c74 user: Trudge)

That came back to the bash prompt without showing me anything, so I thought I'd try it in a Perl script using the Unicode code. $sth=$dbh->prepare(qq{SELECT x'c3a1', hex('á');}); $sth->execute(); while (@records=$sth->fetchrow_array()) { $text=decode('UTF-8',$records[0]); print qq{-->$text<--

}; } That showed me: -->á<--

22:13 Reply: Unicode and CLI for Mac (artifact: 890324699a user: Trudge)

As mentioned in the Topic header I'm on a Mac running High Sierra. Perl: This is perl 5, version 18, subversion 2 (v5.18.2)

My scripts now have the following added: use 5.012; # https://perldoc.perl.org/perluniintro use Encode qw(encode decode); binmode(STDOUT, "encoding(UTF-8)"); use open ":std", ":encoding(UTF-8)"; When doing a select: $sth=$dbh->prepare(qq{select name,title,genre,cds.artistid,cdid from cds join artists on artists.artistid=cds.artistid order by cds.id desc limit 5;}); $sth->execute(); while (@records=$sth->fetchrow_array()) { $ArtistName=decode('UTF-8',$records[0]); . . } $sth->finish();

And when adding or updating: $text=encode('UTF-8',("Ali Farka Touré & Toumani Diabaté")); $sth=$dbh->do(qq{update artists set name=("$text") where artistid=1373});

I believe you are right in thinking I don't need ICU. But in response to another reply, it doesn't seem my console is UTF-8 compatible.

"What is the output of SELECT x'c3a1', hex('á'); " came back empty. And a select with CLI: sqlite> select name from artists where artistid in(1373,1786,1558,1653,1771); Ali Farka Touré & Toumani Diabaté Dvořák,A. Dvořák,Elgar Eilertsen,Fraanje,Strønen Café del Mar

I do have the DB updated but had to do it all via Perl.

21:54 Reply: Unicode and CLI for Mac (artifact: df5ab41911 user: Trudge)

sqlite> SELECT x'c3a1', hex('

So you may have it. This is worth looking into fer sure. Thanks.

14:32 Post: Unicode and CLI for Mac (artifact: 1e8b52c75e user: Trudge)

I have a database of artists and CDs and access it in a web environment using Perl. Finally got it converted to accept Unicode characters. However I have to use Perl to enter and retrieve correctly rendered data.

Using the CLI if I try to enter a Unicode character, the sqlite shell seems to hang, and returns to a bash shell after hitting Enter.

When retrieving data, it does not render correctly, yet my Perl script renders it correctly.

So my question is: am I missing something in the sqlite CLI? As far as I know I'm entering characters correctly using Alt [hex code] and the Character Viewer is set to Unicode Hex Input.

sqlite> select name from artists where artistid=1373; Ali Farka Touré & Toumani Diabaté

The database schema: CREATE TABLE sqlite_sequence(name,seq); CREATE TABLE IF NOT EXISTS "artists" ( id integer NOT NULL, artistid integer, name text, PRIMARY KEY(id AUTOINCREMENT) ); CREATE TABLE IF NOT EXISTS "cds" ( id integer, artistid integer, cdid INTEGER, title text, runtime text, notes text, 'genre' text, PRIMARY KEY(id AUTOINCREMENT) ); I tried recompiling sqlite3 using '-DSQLITE_ENABLE_ICU' after downloading a library from site.icu-project.org. That failed because some .h source files were missing. This after reading the Quirks, Caveats, and Gotchas In SQLite page.

Perhaps I downloaded an incorrect library? Can anyone shed some light on this please. Many thanks.

2021-01-26
17:24 Reply: hardlink created (artifact: d97f2a94c5 user: Trudge)

In any of the web development I've done using Perl I've found it is always preferable to give a fully qualified pathname. I'm also running a Mac under High Sierra and have done Perl scripts that write JavaScripts that write HTML, so familiar with some of your issues. In your case you might find it preferable as well.

2021-01-13
15:21 Reply: SELECT question (artifact: 6b0ba5415a user: Trudge)

"combined with skilled use of it." That's the part I'm missing. Working on it.

2021-01-12
15:08 Reply: SELECT question (artifact: 926f0e8da6 user: Trudge)

So many options! The more I use Sqlite the more I see why it is so popular. Many thanks again.

2021-01-11
15:52 Reply: SELECT question (artifact: fdcd09f4d5 user: Trudge)

Appreciate your experience in this matter. It has also been on my radar to 'fix' that situation. The way I handle multiple docs by the same author is to increment the docid for that author. I do have an 'authors' table

CREATE TABLE authors (
id integer not null primary key autoincrement,
author text,
authorid integer,
docid integer);
so essentially the same as your suggestion without the linkage tables. That's what I'm using authorid and docid for. Some typical Perl code:
$sth=$dbh->prepare(qq{select title,docs.authorid,docs.docid,pubyear,source,filename,author,
(
select group_concat(keyword)
from keywords
where keywords.docid = docs.docid
and keywords.authorid = docs.authorid
)
from docs
order by docs.id desc limit 5});
Now that I have this situation solved I can work on 'normalizing' the rest of the DB and any tweaking. Thank you for your experience.

2021-01-10
04:29 Reply: SELECT question (artifact: 5c1b10e2a9 user: Trudge)

Ah yes. I also had to remove the reference to 'D'.

sqlite> select docs.id, docs.title AS Title, authors.author AS Author, docs.source AS Source,
   ...> (
   ...> select group_concat(keyword)
   ...> from keywords
   ...> where docid = docs.docid
   ...> and authorid = docs.Authorid
   ...> )
   ...> from docs, authors
   ...> where docs.authorid = authors.authorid
   ...> and docs.docid = authors.docid
   ...> order by docs.id desc
   ...> limit 5;
49|Redeeming Charles Babbage's Mechanical Computer|Swade,Doron D.|Scientific American 1993-02|Computer Technology,Engineering,Difference Engine
48|A Partly True Story|Stewart,Ian|Scientific American 1993-02|Mathematics,Fuzzy Logic
47|Alphabet's Now Building Cities|Wright,Emily|Wired 2018-01-02|Smart City,Urban Design
46|The Talented Luthier|Gilchrist,Jim|Scotland Magazine 2021-01|Music - Violin,Luthier,Art
45|Sean Connery|Rowe,Jenny;Adolph,Anthony|Scotland Magazine 2021-01|Sean Connery,Biography,Scottish Ancestry

More ↓