SQLite Forum

Unicode and CLI for Mac
Login

Unicode and CLI for Mac

(1) By Trudge on 2021-02-10 14:32:47 [link]

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.

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

The database schema:
<code>
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)
);
</code>
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 <a href="https://https://sqlite.org/quirks.html">Quirks, Caveats, and Gotchas In SQLite</a> page.

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

(2) By Clemens Ladisch (cladisch) on 2021-02-10 18:03:20 in reply to 1 [link]

I guess your console does not use UTF-8.

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

(3.1) By Warren Young (wyoung) on 2021-02-10 18:49:32 edited from 3.0 in reply to 1 [link]

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

``` pikchr
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][1]. "[Here’s a nickel, kid. Get yourself a better computer.][4]" (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][2]) 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][3] 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][2].

[1]: https://stackoverflow.com/questions/388490/how-to-use-unicode-characters-in-windows-command-line
[2]: https://www.sqlite.org/src/doc/trunk/ext/icu/README.txt
[3]: https://www.sqlite.org/loadext.html
[4]: https://wiert.me/2020/06/24/herea-nickel-kid-get-yourself-a-better-computer-25th-anniversary/

(4) By Trudge on 2021-02-10 21:54:59 in reply to 2 [link]

sqlite> SELECT x'c3a1', hex('

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

(5) By Trudge on 2021-02-10 22:13:35 in reply to 3.1 [link]

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:
<code>
use 5.012; # https://perldoc.perl.org/perluniintro
use Encode qw(encode decode);
binmode(STDOUT, "encoding(UTF-8)");
use open ":std", ":encoding(UTF-8)";
</code>
When doing a select:
<code>
$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();

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

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:
<code>
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
</code>

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

(6) By Trudge on 2021-02-10 22:26:17 in reply to 2 [link]

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.
<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<--</p>};
}
</code>
That showed me:
-->á<--

(7) By Trudge on 2021-02-10 23:05:52 in reply to 3.1 [link]

Some more information regarding my environment. Perl uses a DBI module to talk to sqlite. My connection code:
<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
</code>
Locally a run Apache server.

(8) By Warren Young (wyoung) on 2021-02-11 05:06:55 in reply to 6 [link]

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

``` shell
$ 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?

(9.1) By Warren Young (wyoung) on 2021-02-11 05:13:20 edited from 9.0 in reply to 5 [link]

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

(10.1) By Simon Slavin (slavin) on 2021-02-11 15:56:49 edited from 10.0 in reply to 7 [link]

Deleted

(11) By Simon Slavin (slavin) on 2021-02-11 16:02:42 in reply to 1 [link]

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 ?

(12) By Trudge on 2021-02-11 16:12:47 in reply to 8 [link]

Hmm. Very odd indeed. Tried again from sqlite:
<code>
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] 
</code>

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

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.

(13) By Trudge on 2021-02-11 16:30:08 in reply to 9.1 [link]

Apologies. Yes I'm running High Sierra 10.13.6 on an iMac. I've added the following to my scripts:
<code>
############# 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";
#############
</code>
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.

(14.2) By Trudge on 2021-02-11 17:03:39 edited from 14.1 in reply to 11 [link]

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

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

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

(15) By Larry Brasfield (larrybr) on 2021-02-11 18:48:33 in reply to 14.2 [link]

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.

(16) By Warren Young (wyoung) on 2021-02-11 23:08:41 in reply to 14.2 [link]

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

(17) By Warren Young (wyoung) on 2021-02-11 23:09:33 in reply to 13 [link]

> use locale;

I was asking you to run the `locale` command at the terminal prompt. Here's what I get:

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

-----

And you?

(18) By Trudge on 2021-02-12 12:13:55 in reply to 17 [link]

Woops. Sorry.
<code>
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"
</code>

(19) By Trudge on 2021-02-12 12:23:07 in reply to 16 [link]

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

The recent d/l executable is just 'sqlite'.
<code>
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.
</code>

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

(20) By Trudge on 2021-02-12 12:38:54 in reply to 8 [link]

Now trying with a newer d/l:
<code>
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
</code>

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.

(21) By Simon Slavin (slavin) on 2021-02-12 16:07:16 in reply to 14.2 [link]

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

<code>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 ~ % </code>

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.

(22.1) By Warren Young (wyoung) on 2021-02-12 20:37:38 edited from 22.0 in reply to 21 [link]

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](https://www.howtogeek.com/267463/how-to-enable-key-repeating-in-macos/), but I believe the default is correct for Terminal. I’ve found [a variation of that command](https://stackoverflow.com/a/44010683/142454) 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.

(23.1) By Trudge on 2021-02-13 16:01:29 edited from 23.0 in reply to 22.1 [link]

This is also where I seem to be having issues - between the OSX shell and sqlite CLI.
Using the Hex codes on <a href="https://utf8-chartable.de/unicode-utf8-table.pl?number=512&utf8=string-literal">this page</a> 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.

(24) By Trudge on 2021-02-13 15:59:38 in reply to 15 [link]

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

(25) By Trudge on 2021-02-13 17:33:47 in reply to 22.1 [link]

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'.
<code>
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
</code>

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

(26) By Warren Young (wyoung) on 2021-02-13 17:45:25 in reply to 23.1 [link]

It turns out that there [is a method for 4-digit Unicode hex code input on macOS][1], 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.

[1]: https://www.webnots.com/how-to-use-unicode-hex-input-method-in-mac/

(27.1) By Simon Slavin (slavin) on 2021-02-14 08:36:52 edited from 27.0 in reply to 25 [link]

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.

<code>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$ </code>

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.

(28) By Trudge on 2021-02-14 15:07:31 in reply to 26 [link]

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.

(29.1) By Trudge on 2021-02-14 15:22:17 edited from 29.0 in reply to 22.1 [link]

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.

(30) By Tim Streater (Clothears) on 2021-02-14 15:42:11 in reply to 29.1 [link]

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.

(31) By Keith Medcalf (kmedcalf) on 2021-02-14 20:13:14 in reply to 28 [link]

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.

(32) By Trudge on 2021-02-15 15:16:00 in reply to 30

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.

(33) By Trudge on 2021-02-15 15:24:52 in reply to 31 [link]

OK, then I need to understand more about shells. For example, when I enter
<code>
echo "SELECT x'c3a1', hex('á');" | sqlite3
á|C3A1
</code>
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.

(34) By Tim Streater (Clothears) on 2021-02-15 17:43:50 in reply to 32 [link]

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.

(35) By Keith Medcalf (kmedcalf) on 2021-02-15 21:20:41 in reply to 33 [link]

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.

(36) By Trudge on 2021-02-16 15:49:00 in reply to 34 [link]

Then you are lucky I guess, because when I do that (Alt - backtick a) on High Sierra I get
<code>
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
</code>
This is with Character Viewer in Unicode Hex Input mode.

(37) By Trudge on 2021-02-16 16:01:37 in reply to 35 [link]

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

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

</code>

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.

(38) By Larry Brasfield (larrybr) on 2021-02-16 16:40:48 in reply to 37 [link]

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

(39) By Trudge on 2021-02-17 15:27:55 in reply to 38 [link]

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