SQLite Forum

[BUG] Precompiled sqlite shell for windows can't open filenames with unicode chars
Login

[BUG] Precompiled sqlite shell for windows can't open filenames with unicode chars

(1) By Zhang Boyang (zby1234) on 2021-02-26 17:08:37 [link] [source]

Hello,

Thank you for developing SQLite! However, I found the precompiled sqlite shell for windows which is downloaded from offical site (sqlite-tools-win32-x86-3340100.zip), can't open db files with unicode characters in its file name.

Steps to reproduce:

  1. Create a test database 'test.db'
  2. Rename 'test.db' to '你好世界.db' (or try some strange names such as emojis)
  3. Try to open '你好世界.db' with sqlite shell and do some query.

What happened:

  1. If the unicode char in filename can be represented in current code page:

The shell will silently create a new db file named '�������.db' and operate on that new file.

  1. If the unicode char in filename can't be represented in current code page:

The shell will try to create a new db file named '????.db' and say filename is illegal.

My thoughts:

It's seems like unicode file name support in sqlite shell is broken. Although I found shell.c uses wmain() and is unicode aware, it seems the precompiled binary continue to use the ANSI versions of API. May be unicode support isn't compiled in?

(2) By anonymous on 2021-02-26 19:47:11 in reply to 1 [link] [source]

Try chcp 65001

SQLite shell uses utf-8 internally, but converts between it and active code page of the console when using console functions.

chcp 65001 sets active code page of the current console window to utf-8.

c:\TEMP>chcp 65001
Active code page: 65001

c:\TEMP>sqlite3.exe 你好世界.db
SQLite version 3.35.0 2021-02-26 15:20:17
Enter ".help" for usage hints.
sqlite> select * from sqlite_master;
table|xx|xx|2|CREATE TABLE xx (i integer primary key, x text)
sqlite>

(3) By Zhang Boyang (zby1234) on 2021-02-27 10:25:41 in reply to 2 [link] [source]

Thank you very much!

But I'm using a old version of windows, which doesn't have well support of 'chcp 65001', and I'd like drag database to the icon of sqlite.exe. It would be appreciated if precompiled sqlite shell have native unicode support.

(4) By Warren Young (wyoung) on 2021-02-27 12:21:17 in reply to 3 updated by 4.1 [link] [source]

> chcp 65001

This advice is a distraction. It changes the code page of the *current* Windows console, which means it can't affect the default, as you'd need for your particular use case, where the console isn't even up yet when the problem occurs: during program launch.

There are ways to change the Windows console's default code page, but it'll probably break all your other apps that assume UTF-16, so it's bad advice across the board.

It's a solution to a different problem anyway. It would allow output in the `sqlite3`  shell to show up in the console properly, and it would let you type input that gets sent to the shell as UTF-8. It isn't going to change how `sqlite3` interprets file names in its `argv`.

Incidentally, that feature was added in Windows 7, which is so old it's a year out of support now. You must be using Windows XP or older, which means you should be very careful about throwing accusations of "bug" about tools running on it. Chances are good that any problems you encounter are from using such an outdated platform in the first place. You can't expect external free software to support such things indefinitely.

Now, if you wanted to get involved and start working on fixing such problems yourself, that'd be different, though in this particular case, I think you'd end up with a fork of SQLite rather than getting your fixes for this sort of problem into SQLite proper.

----

I called this `chcp` stuff a distraction, because the core problems go much deeper.

On Windows 10 with all the toys, it *still* doesn't work as you want, and it's not entirely SQLite's fault.

I copied your Chinese database name from your forum post to the clipboard and induced SQLite to create a database of that name with a basic schema inside so I could tell when I'd opened it successfully. I ran into a whole series of failures along the way:

1. Drag file into stock `cmd.exe` window: garbage text that doesn't work even when attempted.
2. Drag file into `cmd.exe` window after `chcp 65001`. Ditto.
3. Drag file into same window already running `sqlite3.exe`. Ditto.
4. Give up and try all of the above with PowerShell: `DIR` shows mangled file name. Attempting to use it anyway either crashes `sqlite3` or creates a new DB with an incorrect file name.
5. Install [Windows Terminal][1]. Now the name shows up properly in `DIR` under PowerShell, but the native `sqlite3` still can't open it due to the non-UTF-8 `argv` problem called out above. At least now we've stripped away all of the non-SQLite parts of the problem, so success?
6. Try Ubuntu-on-WSL in Windows Terminal. Now we're getting somewhere. `ls` shows the file name properly and copy-pasting it into SQLite commands works. I can even copy the first character and use Tab-completion to fill in the rest of the name, which means if I could write Chinese, I could type that first character instead and use Tab completion. Not that this helps you, since WSL and Windows Terminal both require Windows 10, but it shows success is in principle *possible* under Windows.
7. Or not. Drag file name to Windows Terminal; it refuses. [Known bug, scheduled for a fix in 2.0][2], which is currently "24% complete" according to GitHub, so we may have a fix this time next year. Maybe.
8. Copy the file name from the directory listing from attempt #5 or #6 and use it to construct `sqlite3` commands under Windows Terminal with both PowerShell and WSL. *Now* it works.

Always remember people, Windows is the easy operating system, the one that gives people the least problems. 🙄

----

**tl;dr:** Install Linux on the system and be happy. :)


[1]: https://www.microsoft.com/en-us/p/windows-terminal/9n0dx20hk701
[2]: https://github.com/microsoft/terminal/issues/7754

(4.1) By Warren Young (wyoung) on 2021-02-27 12:28:32 edited from 4.0 in reply to 3 updated by 4.2 [link] [source]

> chcp 65001

This advice is a distraction. It changes the code page of the *current* Windows console, which means it can't affect the default, as you'd need for your particular use case, where the console isn't even up yet when the problem occurs: during program launch.

There are ways to change the Windows console's default code page, but it'll probably break all your other apps that assume UTF-16, so it's bad advice across the board.

It's a solution to a different problem anyway. It would allow output in the `sqlite3`  shell to show up in the console properly, and it would let you type input that gets sent to the shell as UTF-8. It isn't going to change how `sqlite3` interprets file names in its `argv`.

Incidentally, that feature was added in Windows 7, which is so old it's a year out of support now. You must be using Windows XP or older, which means you should be very careful about throwing accusations of "bug" about tools running on it. Chances are good that any problems you encounter are from using such an outdated platform in the first place. You can't expect external free software to support such things indefinitely.

Now, if you wanted to get involved and start working on fixing such problems yourself, that'd be different, though in this particular case, I think you'd end up with a fork of SQLite rather than getting your fixes for this sort of problem into SQLite proper.

----

I called this `chcp` stuff a distraction, because the core problems go much deeper.

On Windows 10 with all the toys, it *still* doesn't work as you want, and it's not entirely SQLite's fault.

I copied your Chinese database name from your forum post to the clipboard and induced SQLite to create a database of that name with a basic schema inside so I could tell when I'd opened it successfully. I ran into a whole series of failures along the way:

1. Drag file into stock `cmd.exe` window: garbage text that doesn't work even when attempted.
2. Drag file into `cmd.exe` window after `chcp 65001`. Ditto.
3. Drag file into same window already running `sqlite3.exe`. Ditto.
4. Give up and try all of the above with PowerShell: `DIR` shows mangled file name. Attempting to use it anyway either crashes `sqlite3` or creates a new DB with an incorrect file name.
5. Install [Windows Terminal][1]. Now the name shows up properly in `DIR` under PowerShell, but the native `sqlite3` still can't open it due to the non-UTF-8 `argv` problem called out above. At least now we've stripped away all of the non-SQLite parts of the problem, so success?
6. Try Ubuntu-on-WSL in Windows Terminal. Now we're getting somewhere. `ls` shows the file name properly and copy-pasting it into SQLite commands works. I can even copy the first character and use Tab-completion to fill in the rest of the name, which means if I could write Chinese, I could type that first character instead and use Tab completion. Not that this helps you, since WSL and Windows Terminal both require Windows 10, but it shows success is in principle *possible* under Windows.
7. Or not. Drag file name to Windows Terminal; it refuses. [Known bug, scheduled for a fix in 2.0][2], which is currently "24% complete" according to GitHub, so we may have a fix this time next year. Maybe.


Always remember people, Windows is the easy operating system, the one that gives people the least problems. 🙄

----

**tl;dr:** Install Linux on the system and be happy. :)


[1]: https://www.microsoft.com/en-us/p/windows-terminal/9n0dx20hk701
[2]: https://github.com/microsoft/terminal/issues/7754

(4.2) By Warren Young (wyoung) on 2021-02-27 12:52:48 edited from 4.1 in reply to 3 updated by 4.3 [link] [source]

> chcp 65001

This advice is a distraction. It changes the code page of the *current* Windows console, which means it can't affect the default, as you'd need for your particular use case, where the console isn't even up yet when the problem occurs: during program launch.

There are ways to change the Windows console's default code page, but it'll probably break all your other apps that assume UTF-16, so it's bad advice across the board.

It's a solution to a different problem anyway. It would allow output in the `sqlite3`  shell to show up in the console properly, and it would let you type input that gets sent to the shell as UTF-8. It isn't going to change how `sqlite3` interprets file names in its `argv`.

Incidentally, that feature was added in Windows XP, which is so old it's *successor* (Windows 7) is a year out of support now. You must be using Windows 2000 or older, which means you should be very careful about throwing accusations of "bug" about tools running on it. Chances are good that any problems you encounter are from using such an outdated platform in the first place. You can't expect external free software to support such things indefinitely.

Now, if you wanted to get involved and start working on fixing such problems yourself, that'd be different, though in this particular case, I think you'd end up with a fork of SQLite rather than getting your fixes for this sort of problem into SQLite proper.

----

I called this `chcp` stuff a distraction, because the core problems go much deeper.

On Windows 10 with all the toys, it *still* doesn't work as you want, and it's not entirely SQLite's fault.

I copied your Chinese database name from your forum post to the clipboard and induced SQLite to create a database of that name with a basic schema inside so I could tell when I'd opened it successfully. I ran into a whole series of failures along the way:

1. Drag file into stock `cmd.exe` window: garbage text that doesn't work even when attempted.
2. Drag file into `cmd.exe` window after `chcp 65001`. Ditto.
3. Drag file into same window already running `sqlite3.exe`. Ditto.
4. Give up and try all of the above with PowerShell: `DIR` shows mangled file name. Attempting to use it anyway either crashes `sqlite3` or creates a new DB with an incorrect file name.
5. Install [Windows Terminal][1]. Now the name shows up properly in `DIR` under PowerShell, but the native `sqlite3` still can't open it due to the non-UTF-8 `argv` problem called out above. At least now we've stripped away all of the non-SQLite parts of the problem, so success?
6. Try Ubuntu-on-WSL in Windows Terminal. Now we're getting somewhere. `ls` shows the file name properly and copy-pasting it into SQLite commands works. I can even copy the first character and use Tab-completion to fill in the rest of the name, which means if I could write Chinese, I could type that first character instead and use Tab completion. Not that this helps you, since WSL and Windows Terminal both require Windows 10, but it shows success is in principle *possible* under Windows.
7. Or not. Drag file name to Windows Terminal; it refuses. [Known bug, scheduled for a fix in 2.0][2], which is currently "24% complete" according to GitHub, so we may have a fix this time next year. Maybe.


Always remember people, Windows is the easy operating system, the one that gives people the least problems. 🙄

----

**tl;dr:** Install Linux on the system and be happy. :)


[1]: https://www.microsoft.com/en-us/p/windows-terminal/9n0dx20hk701
[2]: https://github.com/microsoft/terminal/issues/7754

(4.3) By Warren Young (wyoung) on 2021-02-27 12:56:55 edited from 4.2 in reply to 3 updated by 4.4 [link] [source]

> chcp 65001

This advice is a distraction. It changes the code page of the *current* Windows console, which means it can't affect the default, as you'd need for your particular use case, where the console isn't even up yet when the problem occurs: during program launch.

There are ways to change the Windows console's default code page, but it'll probably break all your other apps that assume UTF-16, so it's bad advice across the board.

It's a solution to a different problem anyway. It would allow output in the `sqlite3` shell to show up in the console properly, and it would let you type input that gets sent to the shell as UTF-8. It isn't going to change how `sqlite3` interprets file names in its `argv`.

Incidentally, that feature was added in Windows XP, which is so old it's *successor* (Windows 7) is a year out of support now. You must be using Windows 2000 or older, which means you should be very careful about throwing accusations of "bug" about tools running on it. Chances are good that any problems you encounter are from using such an outdated platform in the first place. You can't expect external free software to support such things indefinitely.

Now, if you wanted to get involved and start working on fixing such problems yourself, that'd be different, though in this particular case, I think you'd end up with a fork of SQLite rather than getting your fixes for this sort of problem into SQLite proper.

----

I called this `chcp` stuff a distraction, because the core problems go much deeper.

On Windows 10 with all the toys, it *still* doesn't work as you want, and it's not entirely SQLite's fault.

I copied your Chinese database name from your forum post to the clipboard and induced SQLite to create a database of that name with a basic schema inside so I could tell when I'd opened it successfully. I ran into a whole series of failures along the way:

1. Drag file into stock `cmd.exe` window: garbage text that doesn't work even when attempted.
2. Drag file into `cmd.exe` window after `chcp 65001`. Ditto.
3. Drag file into same window already running `sqlite3.exe`. Ditto.
4. Give up and try all of the above with PowerShell: `DIR` shows mangled file name. Attempting to use it anyway either crashes `sqlite3` or creates a new DB with an incorrect file name.
5. Install [Windows Terminal][1]. Now the name shows up properly in `DIR` under PowerShell, but the native `sqlite3` still can't open it due to the non-UTF-8 `argv` problem called out above. At least now we've stripped away all of the non-SQLite parts of the problem, so success?
6. Try Ubuntu-on-WSL in Windows Terminal. Now we're getting somewhere. `ls` shows the file name properly and copy-pasting it into SQLite commands works. I can even copy the first character and use Tab-completion to fill in the rest of the name, which means if I could write Chinese, I could type that first character instead and use Tab completion. Not that this helps you, since WSL and Windows Terminal both require Windows 10, but it shows success is in principle *possible* under Windows.
7. Or not. Drag file name to Windows Terminal; it refuses. [Known bug, scheduled for a fix in 2.0][2], which is currently "24% complete" according to GitHub, scheduled for release by May 31, 2021. We'll see.

**EDIT:** Per knu's [deleted reply][3], I then tried this on a Windows XP test VM I have, and it still doesn't work, even with `chcp 65001`. The feature's there, but it doesn't help, essentially for the reasons given above, though there's additional brokenness atop it that's since been fixed in later versions of Windows.

Always remember people, Windows is the easy operating system, the one that gives people the least problems. 🙄

----

**tl;dr:** Install Linux on the system and be happy. :)


[1]: https://www.microsoft.com/en-us/p/windows-terminal/9n0dx20hk701
[2]: https://github.com/microsoft/terminal/issues/7754
[3]: https://sqlite.org/forum/forumpost/da23e11181?hist

(4.4) By Warren Young (wyoung) on 2021-02-27 12:57:32 edited from 4.3 in reply to 3 updated by 4.5 [source]

> chcp 65001

This advice is a distraction. It changes the code page of the *current* Windows console, which means it can't affect the default, as you'd need for your particular use case, where the console isn't even up yet when the problem occurs: during program launch.

There are ways to change the Windows console's default code page, but it'll probably break all your other apps that assume UTF-16, so it's bad advice across the board.

It's a solution to a different problem anyway. It would allow output in the `sqlite3` shell to show up in the console properly, and it would let you type input that gets sent to the shell as UTF-8. It isn't going to change how `sqlite3` interprets file names in its `argv`.

Incidentally, that feature was added in Windows XP, which is so old its *successor* (Windows 7) is a year out of support now. You must be using Windows 2000 or older, which means you should be very careful about throwing accusations of "bug" about tools running on it. Chances are good that any problems you encounter are from using such an outdated platform in the first place. You can't expect external free software to support such things indefinitely.

Now, if you wanted to get involved and start working on fixing such problems yourself, that'd be different, though in this particular case, I think you'd end up with a fork of SQLite rather than getting your fixes for this sort of problem into SQLite proper.

----

I called this `chcp` stuff a distraction, because the core problems go much deeper.

On Windows 10 with all the toys, it *still* doesn't work as you want, and it's not entirely SQLite's fault.

I copied your Chinese database name from your forum post to the clipboard and induced SQLite to create a database of that name with a basic schema inside so I could tell when I'd opened it successfully. I ran into a whole series of failures along the way:

1. Drag file into stock `cmd.exe` window: garbage text that doesn't work even when attempted.
2. Drag file into `cmd.exe` window after `chcp 65001`. Ditto.
3. Drag file into same window already running `sqlite3.exe`. Ditto.
4. Give up and try all of the above with PowerShell: `DIR` shows mangled file name. Attempting to use it anyway either crashes `sqlite3` or creates a new DB with an incorrect file name.
5. Install [Windows Terminal][1]. Now the name shows up properly in `DIR` under PowerShell, but the native `sqlite3` still can't open it due to the non-UTF-8 `argv` problem called out above. At least now we've stripped away all of the non-SQLite parts of the problem, so success?
6. Try Ubuntu-on-WSL in Windows Terminal. Now we're getting somewhere. `ls` shows the file name properly and copy-pasting it into SQLite commands works. I can even copy the first character and use Tab-completion to fill in the rest of the name, which means if I could write Chinese, I could type that first character instead and use Tab completion. Not that this helps you, since WSL and Windows Terminal both require Windows 10, but it shows success is in principle *possible* under Windows.
7. Or not. Drag file name to Windows Terminal; it refuses. [Known bug, scheduled for a fix in 2.0][2], which is currently "24% complete" according to GitHub, scheduled for release by May 31, 2021. We'll see.

**EDIT:** Per knu's [deleted reply][3], I then tried this on a Windows XP test VM I have, and it still doesn't work, even with `chcp 65001`. The feature's there, but it doesn't help, essentially for the reasons given above, though there's additional brokenness atop it that's since been fixed in later versions of Windows.

Always remember people, Windows is the easy operating system, the one that gives people the least problems. 🙄

----

**tl;dr:** Install Linux on the system and be happy. :)


[1]: https://www.microsoft.com/en-us/p/windows-terminal/9n0dx20hk701
[2]: https://github.com/microsoft/terminal/issues/7754
[3]: https://sqlite.org/forum/forumpost/da23e11181?hist

(4.5) By Warren Young (wyoung) on 2021-02-27 13:45:37 edited from 4.4 in reply to 3 [link] [source]

chcp 65001

This advice is a distraction. It changes the code page of the current Windows console, which means it can't affect the default, as you'd need for your particular use case, where the console isn't even up yet when the problem occurs: during program launch.

There are ways to change the Windows console's default code page, but it'll probably break all your other apps that assume UTF-16, so it's bad advice across the board.

It's a solution to a different problem anyway. It would allow output in the sqlite3 shell to show up in the console properly, and it would let you type input that gets sent to the shell as UTF-8. It isn't going to change how sqlite3 interprets file names in its argv.

Now, if you wanted to get involved and start working on fixing such problems yourself, that'd be different, though in this particular case, I think you'd end up with a fork of SQLite rather than getting your fixes for this sort of problem into SQLite proper.


I called this chcp stuff a distraction, because the core problems go much deeper.

On Windows 10 with all the toys, it still doesn't work as you want, and it's not entirely SQLite's fault.

I copied your Chinese database name from your forum post to the clipboard and induced SQLite to create a database of that name with a basic schema inside so I could tell when I'd opened it successfully. I ran into a whole series of failures along the way:

  1. Drag file into stock cmd.exe window: garbage text that doesn't work even when attempted.
  2. Drag file into cmd.exe window after chcp 65001. Ditto.
  3. Drag file into same window already running sqlite3.exe. Ditto.
  4. Give up and try all of the above with PowerShell: DIR shows mangled file name. Attempting to use it anyway either crashes sqlite3 or creates a new DB with an incorrect file name.
  5. Install Windows Terminal. Now the name shows up properly in DIR under PowerShell, but the native sqlite3 still can't open it due to the non-UTF-8 argv problem called out above. At least now we've stripped away all of the non-SQLite parts of the problem, so success?
  6. Try Ubuntu-on-WSL in Windows Terminal. Now we're getting somewhere. ls shows the file name properly and copy-pasting it into SQLite commands works. I can even copy the first character and use Tab-completion to fill in the rest of the name, which means if I could write Chinese, I could type that first character instead and use Tab completion. Not that this helps you, since WSL and Windows Terminal both require Windows 10, but it shows success is in principle possible under Windows.
  7. Or not. Drag file name to Windows Terminal; it refuses. Known bug, scheduled for a fix in 2.0, which is currently "24% complete" according to GitHub, scheduled for release by May 31, 2021. We'll see.

EDIT: Per knu's deleted reply, I then tried this on a Windows XP test VM I have, and it still doesn't work, even with chcp 65001. The feature's there, but it doesn't help, essentially for the reasons given above, though there's additional brokenness atop it that's since been fixed in later versions of Windows.

Always remember people, Windows is the easy operating system, the one that gives people the least problems. 🙄


tl;dr: Install Linux on the system and be happy. :)

(6) By Zhang Boyang (zby1234) on 2021-02-28 09:19:11 in reply to 4.5 [link] [source]

Thank you for your reply. After some digging, I found the unicode argv[] support is just not compiled into offical binary. I downloaded the sqlite source repo and compiled sqlite shell myself, everything worked. (I'm using Visual Studio 2019 x64 native tools + self-compiled tclsh)

(7) By Keith Medcalf (kmedcalf) on 2021-02-28 15:35:38 in reply to 6 updated by 7.1 [link] [source]

Hrm.

I had to add `-municode -mconsole` options to get MinGW/gcc to produce a working unicode command line for Windows.  MSVC worked without changes.

(7.1) By Keith Medcalf (kmedcalf) on 2021-02-28 15:37:22 edited from 7.0 in reply to 6 [link] [source]

Deleted

(8) By Keith Medcalf (kmedcalf) on 2021-02-28 16:24:01 in reply to 6 [link] [source]

In order to get MinGW/GCC on Windows to produce a a working unicode (wmain) executable the -municode directive was required on the gcc command line.

Also -DSQLITE_SHELL_IS_UTF8=(0) because the current shell.c only sets this for MSVC compilers on Windows, not GCC.

The MSVC compiler seemed to drag in the correct unicode (wmain) correctly without making any changes.

Changing this block in shell.c.in

#ifndef SQLITE_SHELL_IS_UTF8
#  if (defined(_WIN32) || defined(WIN32)) && defined(_MSC_VER)
#    define SQLITE_SHELL_IS_UTF8          (0)
#  else
#    define SQLITE_SHELL_IS_UTF8          (1)
#  endif
#endif

to

#ifndef SQLITE_SHELL_IS_UTF8
#  if (defined(_WIN32) || defined(WIN32)) && (defined(_MSC_VER) || (defined(UNICODE) && defined(__GNUC__)))
#    define SQLITE_SHELL_IS_UTF8          (0)
#  else
#    define SQLITE_SHELL_IS_UTF8          (1)
#  endif
#endif

fixes this so that only the -municode compiler option is necessary to make this work with a GCC compiler targetting Windows, and without the option a narrow character version will be produced.

(5) By Kees Nuyt (knu) on 2021-02-27 12:37:53 in reply to 3 updated by 5.1 [link] [source]

> But I'm using a old version of windows, which doesn't have well support of 'chcp 65001', 

We can't solve that for you. Codepage 65001 was already available in Windows XP. [This article][1] may help you.

> and I'd like drag database to the icon of sqlite.exe. It would be appreciated if precompiled sqlite shell have native unicode support.

As far as I know, the precompiled shell has native unicode support, but it depends on the Windows "terminal".

Try this: Make a new shortcut (icon) which does not point to sqlite3.exe, but to a .cmd file that you write yourself, which sets up the environment for sqlite. Something like:

```sh
@echo off
chcp 65001
disk:\path\to\sqlite3.exe "%1"
```
(untested)

[1]:https://www.sqlsnippets.com/en/topic-13410.html

(5.1) By Kees Nuyt (knu) on 2021-02-27 12:48:17 edited from 5.0 in reply to 3 [link] [source]

Deleted