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.

I guess your console does not use UTF-8.

What is the output of SELECT x'c3a1', hex('á');

I have to use Perl to enter and retrieve correctly rendered data.

What's the platform? Details, please! (If you've posted it before, you can't expect us to remember from thread to thread.)

Can you post a snippet of this "rendering" code? I specifically mean your use of the Encode::* modules, I/O binmodes, etc. Relevant stuff, you know? Maybe a tiny bit of the innermost DBI code, just showing where the data transits the boundary.

This is far from trivial:

scale = 0.75
box "DB" fit
arrow 0.25
box "back end" fit
arrow same
box "template HTML" fit
arrow same
box "Perl code" fit
arrow same
box "web server" fit
arrow same
box "browser" fit

All five transition points have to be covered to get this right:

1. SQLite to whatever back-end service is continuously running, which may or may not be part of your Perl code. (Could be a C server offering a different API than SQLite's in-process API, for instance.)

2. The Perl HTML templating engine has to be told what Unicode encoding to use. Hopefully you're using something like Xslate or Mason rather than hand-rolling this. It's file I/O, and Perl doesn't do Unicode file I/O by default, partly because Perl predates Unicode, and also because there isn't just one Unicode encoding, so you have to be explicit about what encoding you need.

3. The Perl code processing all this data also has to be Unicode-clean for the same reason. It's what marries steps 1 and 2, and it may have other text it injects into the stream besides.

4. There are many ways for your Perl app to provide HTTP service: HTTP::Server::Simple, Starman, Plack bound to an external server like Apache, or some combination via a reverse proxy server. Point is, this has to be Unicode-clean, too.

5. If the final rendered HTML delivered by HTTP isn't marked up properly, the browser still won't render it as you expect, even if all 4 prior steps are done properly.

Now, I realize this is somewhat tangential to your original question, but once you work through all of this, you shouldn't need ICU or anything special in SQLite to achieve your stated goal. You'll be storing the data in a format your local console will interpret correctly, so your manual SELECT statements in the sqlite3 shell will produce "correct" output, however you define that term.

...Assuming you aren't using Windows, in which case, add a sixth step, since Unicode in Windows Console is a mess. "Here’s a nickel, kid. Get yourself a better computer." (Joking; WSL or Cygwin + MinTTY would fix this.)

I tried recompiling sqlite3 using '-DSQLITE_ENABLE_ICU'

I don't think the ICU extension provides anything you need for your stated purpose. (Source) You might still want it for Unicode-aware LIKE, sorting, and such, but it doesn't affect how the data are presented to the console.

It also appears to be a loadable extension, so I'd expect that you need to explicitly load it at run time before it takes effect.

That failed because some .h source files were missing.

You will need the ICU development files installed, of course. See the README.

sqlite> SELECT x'c3a1', hex('

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

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.

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<--

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.

That came back to the bash prompt without showing me anything

The example wasn't given as a Bash command but as a SQLite shell prompt.

However, it works here on 10.15.7 with the stock sqlite3 shell both ways:

$ sqlite3
SQLite version 3.28.0 2019-04-15 14:49:49
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
sqlite> ^D
$ echo "SELECT x'c3a1', hex('á');" | sqlite3
á|C3A1

It also works fine with SQLite 3.34.0 from Homebrew.

So, you've apparently got something weird going on there. Nonstandard shell? Third-party SQLite binaries?

As mentioned in the Topic header I'm on a Mac running High Sierra.

No, the topic header just says "Mac". That word spans almost 40 years of history in computing. That isn't "details".

This is perl 5, version 18, subversion 2 (v5.18.2)

I've been successfully doing Unicode in Perl since 5.6, so it can be done. Indeed, the diagram above is a re-drawing of one of my early posts on the topic, from another forum.

it doesn't seem my console is UTF-8 compatible.

What does the locale command give?

Are you using a terminal other than Apple's own Terminal.app?

I will assume that you used. the precompiled shell tool available from the SQLite site, and that you. ran it in Apple's Terminal program without doing anything to change character settings.

The shell tool supplied by the sqlite development team is the ultimate guide about how to use sqlite. If it looks wrong in the shell tool, it's wrong. The fact that the shell shows the contents of your string as incorrect Unicode characters shows that the string is wrong. You. have put the wrong thing in the database and are, naturally, getting the wrong thing back out again. Your perl code appears to get the 'right' thing because it's doing one thing to get characters into the database and the opposite thing to get them back out again.

I have no trouble at all entering a unicode character using the shell tool. Which unicode character are you trying to enter and what keys are you using to enter it ?

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.

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

############# 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.

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].

If you cannot decipher what link in the chain is funky, you need to see the binary being passed. A debugger is good for some of that. And in the SQLite shell, you can cast your strings to blob and display then as hex.

I've grabbed a new d/l from sqlite....SQLite version 3.19.3

The current stable SQLite is 3.34.1. If you thought you put the current binary into the PATH, the system one is apparently still ahead of it.

Try:

  $ type -a sqlite3

If you get more than one line of output, and the later one is the one you want, then you need to either rearrange your PATH or use an explicit path to the updated version.

If your updated version isn't listed at all, then that's why your command is running under the old 3.19.3 version of SQLite.

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"

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.

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

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.

On my system, running Mac OS 11.3 Beta (Big Sur) and a downloaded precompiled version of SQLite3.

SQLite version 3.32.3 2020-06-18 14:16:19 Enter ".help" for usage hints. sqlite> SELECT x'c3a1', hex('á'); á|C3A1 sqlite> CREATE TABLE TestTable (testString TEXT); sqlite> INSERT INTO TestTable VALUES (x'c3a1'); sqlite> INSERT INTO TestTable VALUES ('á'); sqlite> SELECT * FROM TestTable; á á sqlite> SELECT * FROM TestTable WHERE testString IS 'á'; á sqlite> SELECT typeof(testString),hex(testString),testString FROM TestTable; blob|C3A1|á text|C3A1|á sqlite> .quit simon@190 ~ %

For some reason, the way I'd normally type 'á' on my Mac (hold down 'a', then choose '1') doesn't work in Terminal. So in order to get the 'á' character into the Terminal window I pasted it in.

You can type it in Terminal with Opt-E, a.

If the iOS “hold a and select choice” method worked, you’d lose key auto repeat, making things like hjkl navigation in vi fail. There’s a command to flip the behavior, but I believe the default is correct for Terminal. I’ve found a variation of that command useful for allowing repeat-on-hold for use with the Vim extension for VSCode, though.

This Alt-hex code stuff sounds like Linux, not macOS.

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.

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

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] : 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.

It turns out that there is a method for 4-digit Unicode hex code input on macOS, but it's not the default, and enabling it messes up the normal Option-code method for inputting additional characters not on the keyboard.

Is this what you've done?

I found it to be a bit dodgy, prone to getting confused about the order of the hex digits if you fat-finger one. This on top of the document's other warnings about this method, I turned it right back off. Opt-E, a is a far easier method than holding down Option (not Alt!) and trying to remember 00E1.

I will get sqlite running on it

Big Sur ships with SQLite 3.32.3, and both the U+00E1 hex code and normal Opt-E, a methods for inputting á work with it under the stock terminal.

I think we've gone well beyond SQLite issues here now, so I think if you want to follow this up any further, you need to take this to a Perl or macOS forum.

It's something weird in your setup, or it's a version of the sqlite shell tool you've compiled specially, with options that aren't working on your setup.

simon@190 ~ % bash

The default interactive shell is now zsh. To update your account to use zsh, please run chsh -s /bin/zsh. For more details, please visit https://support.apple.com/kb/HT208050. bash-3.2$ uname -rs Darwin 20.4.0 bash-3.2$ sqlite3 ~/Desktop/temp.sqlite SQLite version 3.32.3 2020-06-18 14:16:19 Enter ".help" for usage hints. sqlite> select x'c3a1',hex('á'); á|C3A1 sqlite> .quit bash-3.2$

No problems. To type the accented a I used the method described upthread: hold down the option key while typing 'e', then type 'a'. Use that method. Don't bother testing again using the method of alt key and then typing hex characters. That's a non-standard way of entering characters on a Mac and people will find it difficult to reproduce your problems.

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.

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.

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.

The shell is not relevant.

The shell program is not involved after the "other program" (the SQLite3 CLI in this case) begins executing.

The shell is a program that exists merely for the convenience of allowing the user to interact with the Operating System in order to tell the operating system what program to run. Once the that other program begins executing the shell program is no longer involved in interactions between that program, the user, or the Operating System. Once the other program exits then the user is able to interact with the shell program once more.

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.

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.

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.

That would seem to indicate that the shell requesting (or that the OS has set) a "cooking mode" on SYSIN which is not being respected or maintained when the SYSIN input stream is inherited by the sub-process (sqlite3) and that sub-process is operating in "interactive" mode.

If you use the command sqlite3 -batch does it work properly (note that -batch will not present you with a prompt but will instead use a reduced "best user interface ever invented, by Ashton-Tate, without even the horror of a dot").

It will also use the SYSIN stream as-is and not try to set or unset any cooking.

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.

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.

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.

Yes, it's getting that way. But the general problem, of transferring coded characters into and out of a SQLite DB, via its CLI or other means, it on-topic and not uncommon. This thread is useful for solving your problem and showing methods for diagnosing others like it.

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