SQLite Forum

Unicode and CLI for Mac
> 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/