SQLite converts all unicode characters into ANSI

[ZikO]

I have a slight problem with sqlite and its text encoding. I read from
documents that sqlite handles UTF-8 by using a command PRAGMA encoding =
"UTF-8"; etc. My database is to store Polish text. The database is going to
be used with Qt later on. I have a script with two commands: CREATE TABLE
... and INSERT INTO ... This file is encoded in UTF-8. However, when I build
and fill database via a command *sqlite3 myname.db < the_file.sql*, I create
both database and the table but all specific characters such as ą, ć, ź, Ż
etc. are automatically converted into a, c, z, Z etc. I thought it would be
a problem with the command line. So I downloaded SQLite Manager 2009 and
when I copy / pasted the whole script to execute it in SQLite Manager, I
noticed the effect is exactly the same. Characters are automatically
converted during copy / pasting. Is the SQLite limitted to use only with
ANSI characters?

Can someone please help me? The application will be international and not
only will I need Polish characters but Spanish etc.

Thanks




--
View this message in context: http://sqlite.1065341.n5.nabble.com/SQLite-converts-all-unicode-characters-into-ANSI-tp65589.html
Sent from the SQLite mailing list archive at Nabble.com.

[Gert Van Assche]

All I can say is this is not normal behavior. I use SQLite always with
UTF-8 texts, even on command line (windows).
As a test I suggest you create the db with the SQLite Expert.

gert

Post by ZikO
I have a slight problem with sqlite and its text encoding. I read from
documents that sqlite handles UTF-8 by using a command PRAGMA encoding =
"UTF-8"; etc. My database is to store Polish text. The database is going to
be used with Qt later on. I have a script with two commands: CREATE TABLE
... and INSERT INTO ... This file is encoded in UTF-8. However, when I build
and fill database via a command *sqlite3 myname.db < the_file.sql*, I create
both database and the table but all specific characters such as ą, ć, ź, Ż
etc. are automatically converted into a, c, z, Z etc. I thought it would be
a problem with the command line. So I downloaded SQLite Manager 2009 and
when I copy / pasted the whole script to execute it in SQLite Manager, I
noticed the effect is exactly the same. Characters are automatically
converted during copy / pasting. Is the SQLite limitted to use only with
ANSI characters?
Can someone please help me? The application will be international and not
only will I need Polish characters but Spanish etc.
Thanks
--
http://sqlite.1065341.n5.nabble.com/SQLite-converts-all-unicode-characters-into-ANSI-tp65589.html
Sent from the SQLite mailing list archive at Nabble.com.
_______________________________________________
sqlite-users mailing list
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

...

[Simon Slavin]

Post by ZikO
I have a slight problem with sqlite and its text encoding. I read from
documents that sqlite handles UTF-8 by using a command PRAGMA encoding =
"UTF-8"; etc. My database is to store Polish text. The database is going to
be used with Qt later on. I have a script with two commands: CREATE TABLE
... and INSERT INTO ... This file is encoded in UTF-8. However, when I build
and fill database via a command *sqlite3 myname.db < the_file.sql*, I create
both database and the table but all specific characters such as ą, ć, ź, Ż
etc. are automatically converted into a, c, z, Z etc.

...

Try using the SQLite shell tool by typing instead of by putting your text into text files. Type exactly the same things into it as you put in the text file. Does it work properly when you do this ?

Your problem is usually to do with the readline() command which the shell tool calls. Depending on what operating system you're using, this does different things. Which operating system are you using, and where did you get the version of the shell tool you're using ?

Simon.

[kyan]

Post by ZikO
I have a slight problem with sqlite and its text encoding. I read from
documents that sqlite handles UTF-8 by using a command PRAGMA encoding =
"UTF-8"; etc. My database is to store Polish text. The database is going to
be used with Qt later on. I have a script with two commands: CREATE TABLE
... and INSERT INTO ... This file is encoded in UTF-8. However, when I build
and fill database via a command *sqlite3 myname.db < the_file.sql*, I create
both database and the table but all specific characters such as ą, ć, ź, Ż
etc. are automatically converted into a, c, z, Z etc. I thought it would be
a problem with the command line. So I downloaded SQLite Manager 2009 and
when I copy / pasted the whole script to execute it in SQLite Manager, I
noticed the effect is exactly the same. Characters are automatically
converted during copy / pasting. Is the SQLite limitted to use only with
ANSI characters?

...

I've used SQLite with Greek characters with success.

Just a wild guess: are your scripts saved as ANSI text without a BOM?
If so, you could try saving them as unicode or UTF-8 with a BOM and
try again. Use Notepad's save as command to do this. Also, make sure
that any tool you use to execute them is unicode-aware.

[Igor Tandetnik]

Post by ZikO
I have a slight problem with sqlite and its text encoding. I read from
documents that sqlite handles UTF-8 by using a command PRAGMA encoding =
"UTF-8"; etc. My database is to store Polish text. The database is going to
be used with Qt later on. I have a script with two commands: CREATE TABLE
... and INSERT INTO ... This file is encoded in UTF-8. However, when I build
and fill database via a command *sqlite3 myname.db < the_file.sql*, I create
both database and the table but all specific characters such as ą, ć, ź, Ż
etc. are automatically converted into a, c, z, Z etc.

...

It's exceedingly unlikely this is SQLite's problem. SQLite simply doesn't have enough information to do this, even if it wanted to. There's no way for it to tell that 'ą' is in any way related to 'a', or 'Ż' to 'z'.

Double-check your input file: my guess is, some tool that processed the file earlier ended up stripping accents somehow.

Post by ZikO
I thought it would be
a problem with the command line. So I downloaded SQLite Manager 2009 and
when I copy / pasted the whole script to execute it in SQLite Manager, I
noticed the effect is exactly the same. Characters are automatically
converted during copy / pasting. Is the SQLite limitted to use only with
ANSI characters?

You mean, you paste into SQLite Manager's window, and the text appears unaccented there, even *before* you actually run your statements? Then it can't possibly be SQLite's problem - the text hasn't even reached SQLite at this point. At best, it could be a problem with SQLite Manager (which is a third-party application not developed or maintained by SQLite developers). But again, double-check the source data - all signs point to accents not being there in the first place.

[Keith Medcalf]

Post by ZikO
I have a slight problem with sqlite and its text encoding. I read from
documents that sqlite handles UTF-8 by using a command PRAGMA encoding =
"UTF-8"; etc.

SQLite assumes UTF-8 unless you specify some other format, such as UTF16 (UTF-16le), UTF16le, or UTF16be

Is your text an SBCS file (using some SBCS codepage) rather than an MBCS file (eg UTF-8)?

---
() ascii ribbon campaign against html e-mail
/\ www.asciiribbon.org

[ZikO]

Hello,
thanks for your answers.

I downloaded both precompiled binaries and shell for windows from
www.sqlite.org.

The script looks like this:
CREATE TABLE IF NOT EXISTS imiona (
id INTEGER PRIMARY KEY,
data TEXT,
imie1 TEXT,
imie2 TEXT,
imie3 TEXT);

INSERT INTO imiona (data,imie1,imie2,imie3) VALUES
('01/01/2012','Masława','Mieczysława','Mieszka'),
('16/01/2012','Marcelego','Walerii','Włodzimierza'),
('17/09/2012','Franciszka','Lamberty','Narcyza');


As you can see, it contains accents. The script was written in Notepad++
that controls which charset coding is used but I also double checked it in
Notepad and it indicated UTF-8; it can also be confirmed by reading the
script text file in Hex editor that shows BOM at the beginning:
[*ef bb bf* 43 52 45 41 54 45 20 54 41 42 4c 45 20] [CREATE TABLE]

The sequence at start 0xEF, 0xBB, 0xBF confirms the file has been created
using UTF-8. I tested copy / paste between two text editors in windows 7 and
everything works ok; texts with accents are copy / pasted without losing
accents. I have noticed though that when I used command line I got this
error:

D:\Wydarzenia\Events>sqlite3 imieniny.db < test.sql
Error: near line 1: near "´╗┐CREATE": syntax error

I then used the conding UTF-8 without BOM and the command was accepted but
unfortunately I get the strange result:
sqlite> select * from imiona;
1 01/01/2012 Masława Mieczysława Mieszka
2 16/01/2012 Marcelego Walerii Włodzimie
3 17/09/2012 Franciszka Lamberty Narcyza
4 01/01/2012 Masława Mieczysława Mieszka
5 16/01/2012 Marcelego Walerii Włodzimie
6 17/09/2012 Franciszka Lamberty Narcyza

Now, I don't know how to test if the text is OK because SQLite2009Pro
displays everything converted to ANSI even ithough I set it to UTF-8 :/
Perhaps this program is not very good then. What would you recommend,
instead?

once again thanks for your effort towards this. I am really really surprised
this behaviour.




--
View this message in context: http://sqlite.1065341.n5.nabble.com/SQLite-converts-all-unicode-characters-into-ANSI-tp65589p65602.html
Sent from the SQLite mailing list archive at Nabble.com.

[Kevin Benson]

Post by ZikO
Hello,
thanks for your answers.
I downloaded both precompiled binaries and shell for windows from
www.sqlite.org.
CREATE TABLE IF NOT EXISTS imiona (
id INTEGER PRIMARY KEY,
data TEXT,
imie1 TEXT,
imie2 TEXT,
imie3 TEXT);
INSERT INTO imiona (data,imie1,imie2,imie3) VALUES
('01/01/2012','Masława','Mieczysława','Mieszka'),
('16/01/2012','Marcelego','Walerii','Włodzimierza'),
('17/09/2012','Franciszka','Lamberty','Narcyza');
As you can see, it contains accents. The script was written in Notepad++
that controls which charset coding is used but I also double checked it in
Notepad and it indicated UTF-8; it can also be confirmed by reading the
[*ef bb bf* 43 52 45 41 54 45 20 54 41 42 4c 45 20] [CREATE TABLE]
The sequence at start 0xEF, 0xBB, 0xBF confirms the file has been created
using UTF-8. I tested copy / paste between two text editors in windows 7 and
everything works ok; texts with accents are copy / pasted without losing
accents. I have noticed though that when I used command line I got this
D:\Wydarzenia\Events>sqlite3 imieniny.db < test.sql
Error: near line 1: near "´╗┐CREATE": syntax error
I then used the conding UTF-8 without BOM and the command was accepted but
sqlite> select * from imiona;
1 01/01/2012 Masława Mieczysława Mieszka
2 16/01/2012 Marcelego Walerii Włodzimie
3 17/09/2012 Franciszka Lamberty Narcyza
4 01/01/2012 Masława Mieczysława Mieszka
5 16/01/2012 Marcelego Walerii Włodzimie
6 17/09/2012 Franciszka Lamberty Narcyza
Now, I don't know how to test if the text is OK because SQLite2009Pro
displays everything converted to ANSI even ithough I set it to UTF-8 :/
Perhaps this program is not very good then. What would you recommend,
instead?
once again thanks for your effort towards this. I am really really surprised
this behaviour.

...

I suspect your problem is not with the importer (sqlite3.exe), but instead
with the exporter (cmd.exe). See here:
http://stackoverflow.com/questions/1259084/what-encoding-code-page-is-cmd-exe-using

--
--
--
--Ô¿Ô--
K e V i N

[ZikO]

Hi again :)

I solved the problem before coming to this forum today thanks to Bert. Also,
I found Kevin's answer correct, that is, the problem was with cmd.exe. By
default, it uses code page 850. When I have changed the code page to 65001,
an equivalence of UTF-8 (I think), everything has started working correctly.

I have tested two approaches now.
1.
With proper code page in console, I have created and imported table via
console using sqlite3 imieniny.db < myfile.sql. It however requires that
coding is UTF-8. Otherwise, the default coding 850 has this fantastic
(*caugh) property that converts all non ANSI characters into their closets
equivalence--this exactly happened to me.
2.
Bert has also suggested to use .read command inside sqlite3. The text was
imported untouched into database, even though the code page was set to
default 850. However, reading from tables gave an output with unrecognised
characters.

I've changed the code page to 65001 by executing command
chcp 65001
at start.

Because it would be annoying to changed the code page at each start, I
decided to make it available every time I run the command processor. I have
found the way how to do it here:
http://superuser.com/questions/269818/change-default-code-page-of-windows-console-to-utf-8

I've added extra variable
"Autorun"="@chcp 65001"

to windows register (Windows 7 Home Premium 64bit) in following register
key:
[HKEY_CURRENT_USER\Software\Microsoft\Command Processor]

Thanks for your assistance with that :)




--
View this message in context: http://sqlite.1065341.n5.nabble.com/SQLite-converts-all-unicode-characters-into-ANSI-tp65589p65609.html
Sent from the SQLite mailing list archive at Nabble.com.

[Igor Tandetnik]

Post by ZikO
I then used the conding UTF-8 without BOM and the command was accepted but
sqlite> select * from imiona;
1 01/01/2012 Masława Mieczysława Mieszka

This looks like correct UTF-8, it's just that the console window doesn't know about that, and treats it as a string in current OEM codepage (so each byte of a two-byte sequence is rendered as a separate character).

ł (U+0142, Latin Small Letter L with Stroke) is represented in UTF-8 as two bytes C5 82, which happen to correspond to characters ┼ é in codepage 852 (Central European OEM codepage), as well as several others.

Post by ZikO
Now, I don't know how to test if the text is OK because SQLite2009Pro
displays everything converted to ANSI even ithough I set it to UTF-8 :/
Perhaps this program is not very good then. What would you recommend,
instead?

http://www.sqlite.org/cvstrac/wiki?p=ManagementTools

I used SQLite 3 Explorer (http://www.singular.gr/sqlite/) and SQLite Manager (http://sqlite-manager.googlecode.com/)

[ZikO]

From: Igor Tandetnik [via SQLite] [mailto:ml-node+***@n5.nabble.com]
Sent: 19 November 2012 14:07
To: ZikO
Subject: Re: SQLite converts all unicode characters into ANSI

Post by ZikO
I then used the conding UTF-8 without BOM and the command was accepted but
sqlite> select * from imiona;
1 01/01/2012 Masława Mieczysława Mieszka

This looks like correct UTF-8, it's just that the console window doesn't know about that, and treats it as a string in current OEM codepage (so each byte of a two-byte sequence is rendered as a separate character).

ł (U+0142, Latin Small Letter L with Stroke) is represented in UTF-8 as two bytes C5 82, which happen to correspond to characters ┼ é in codepage 852 (Central European OEM codepage), as well as several others.

Post by ZikO
Now, I don't know how to test if the text is OK because SQLite2009Pro
displays everything converted to ANSI even ithough I set it to UTF-8 :/
Perhaps this program is not very good then. What would you recommend,
instead?

http://www.sqlite.org/cvstrac/wiki?p=ManagementTools

I used SQLite 3 Explorer (http://www.singular.gr/sqlite/) and SQLite Manager (http://sqlite-manager.googlecode.com/)
--
Igor Tandetnik

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



_____

If you reply to this email, your message will be added to the discussion below:

http://sqlite.1065341.n5.nabble.com/SQLite-converts-all-unicode-characters-into-ANSI-tp65589p65606.html

To unsubscribe from SQLite converts all unicode characters into ANSI, click here <http://sqlite.1065341.n5.nabble.com/template/NamlServlet.jtp?macro=unsubscribe_by_code&node=65589&code=emViaWtAb3AucGx8NjU1ODl8MTA0OTg0NDEw> .
<http://sqlite.1065341.n5.nabble.com/template/NamlServlet.jtp?macro=macro_viewer&id=instant_html%21nabble%3Aemail.naml&base=nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.NodeNamespace&breadcrumbs=notify_subscribers%21nabble%3Aemail.naml-instant_emails%21nabble%3Aemail.naml-send_instant_email%21nabble%3Aemail.naml> NAML



Thanks for your answer. It explained a lot. I have removed SQlite2009 as it was showing all texts in similar faulty manner as cmd.exe. I’ll try different managers. Thanks.







--
View this message in context: http://sqlite.1065341.n5.nabble.com/SQLite-converts-all-unicode-characters-into-ANSI-tp65589p65613.html
Sent from the SQLite mailing list archive at Nabble.com.

[Clemens Ladisch]

Post by ZikO
INSERT INTO imiona (data,imie1,imie2,imie3) VALUES
('01/01/2012','Masława','Mieczysława','Mieszka'),
As you can see, it contains accents. The script was written in Notepad++
that controls which charset coding is used but I also double checked it in
Notepad and it indicated UTF-8; it can also be confirmed by reading the
[*ef bb bf* 43 52 45 41 54 45 20 54 41 42 4c 45 20] [CREATE TABLE]

The BOM ("byte-order mark") was intended to differentiate between the
little- and big-endian forms of UTF-16. Using it for UTF-8 is somewhat
common but is not recommended because it will break many programs.

Post by ZikO
sqlite3 imieniny.db < test.sql
Error: near line 1: near "´╗┐CREATE": syntax error

You should just use plain UTF-8 text files without BOM.

Post by ZikO
I then used the conding UTF-8 without BOM and the command was accepted but
sqlite> select * from imiona;
1 01/01/2012 Masława Mieczysława Mieszka

So you are running sqlite3.exe in a Windows console?

If you redirect the output to a file:
sqlite3 imieniny.db "select * from imiona;" > output.txt
you will see that the output is correct; it's just the console display
that is wrong.

Configure the console to show a TrueType font (the raster fonts do not
support Unicode), and change the codepage in that console to UTF-8 by
executing "chcp 65001".

(It might be useful for shell.c to automatically call SetConsoleCP and
SetConsoleOutputCP when running on Windows. When I tried this, output
worked correctly, but inputting non-ASCII characters crashed, but it's
possible that my old compiler (BCC 5.5) doesn't support multibyte
characters correctly.)

Post by ZikO
Sent from the SQLite mailing list archive at Nabble.com.

Please do not use services that insert ads into your posts.


Regards,
Clemens

[ZikO]

From: Clemens Ladisch [via SQLite] [mailto:ml-node+***@n5.nabble.com]
Sent: 19 November 2012 14:20
To: ZikO
Subject: Re: SQLite converts all unicode characters into ANSI

Post by ZikO
INSERT INTO imiona (data,imie1,imie2,imie3) VALUES
('01/01/2012','Masława','Mieczysława','Mieszka'),
As you can see, it contains accents. The script was written in Notepad++
that controls which charset coding is used but I also double checked it in
Notepad and it indicated UTF-8; it can also be confirmed by reading the
[*ef bb bf* 43 52 45 41 54 45 20 54 41 42 4c 45 20] [CREATE TABLE]

The BOM ("byte-order mark") was intended to differentiate between the
little- and big-endian forms of UTF-16. Using it for UTF-8 is somewhat
common but is not recommended because it will break many programs.

Post by ZikO
sqlite3 imieniny.db < test.sql
Error: near line 1: near "´╗┐CREATE": syntax error

You should just use plain UTF-8 text files without BOM.

Post by ZikO
I then used the conding UTF-8 without BOM and the command was accepted but
sqlite> select * from imiona;
1 01/01/2012 Masława Mieczysława Mieszka

So you are running sqlite3.exe in a Windows console?

If you redirect the output to a file:
sqlite3 imieniny.db "select * from imiona;" > output.txt
you will see that the output is correct; it's just the console display
that is wrong.

Configure the console to show a TrueType font (the raster fonts do not
support Unicode), and change the codepage in that console to UTF-8 by
executing "chcp 65001".

(It might be useful for shell.c to automatically call SetConsoleCP and
SetConsoleOutputCP when running on Windows. When I tried this, output
worked correctly, but inputting non-ASCII characters crashed, but it's
possible that my old compiler (BCC 5.5) doesn't support multibyte
characters correctly.)

I redirected output to a text file and it contained all correct characters. It was definitely cmd.exe’s fault. Also, I just added an additional variable “Autorun” with value “chcp 65001” into a register key Command Processor and now I have default coding utf-8. Importing and exporting also work this way J

Post by ZikO
Sent from the SQLite mailing list archive at Nabble.com.

Please do not use services that insert ads into your posts.


Regards,
Clemens



Thanks !



_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



_____

If you reply to this email, your message will be added to the discussion below:

http://sqlite.1065341.n5.nabble.com/SQLite-converts-all-unicode-characters-into-ANSI-tp65589p65607.html

To unsubscribe from SQLite converts all unicode characters into ANSI, click here <http://sqlite.1065341.n5.nabble.com/template/NamlServlet.jtp?macro=unsubscribe_by_code&node=65589&code=emViaWtAb3AucGx8NjU1ODl8MTA0OTg0NDEw> .
<http://sqlite.1065341.n5.nabble.com/template/NamlServlet.jtp?macro=macro_viewer&id=instant_html%21nabble%3Aemail.naml&base=nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.NodeNamespace&breadcrumbs=notify_subscribers%21nabble%3Aemail.naml-instant_emails%21nabble%3Aemail.naml-send_instant_email%21nabble%3Aemail.naml> NAML





--
View this message in context: http://sqlite.1065341.n5.nabble.com/SQLite-converts-all-unicode-characters-into-ANSI-tp65589p65612.html
Sent from the SQLite mailing list archive at Nabble.com.

[ZikO]

Hi,

I am afraid I have done it again but I had no idea I was sending mails with ads. I found your mailing list on this website

http://sqlite.1065341.n5.nabble.com/SQLite-converts-all-unicode-characters-into-ANSI-td65589.html

and I thought it was a part of this mailing lists. The ads showed after I have written messages. Next time, I will use email service only. Apologies for that.

Ziko

Post by ZikO
Sent from the SQLite mailing list archive at Nabble.com.

Please do not use services that insert ads into your posts.


Regards,
Clemens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



_____

If you reply to this email, your message will be added to the discussion below:

http://sqlite.1065341.n5.nabble.com/SQLite-converts-all-unicode-characters-into-ANSI-tp65589p65607.html

To unsubscribe from SQLite converts all unicode characters into ANSI, click here <http://sqlite.1065341.n5.nabble.com/template/NamlServlet.jtp?macro=unsubscribe_by_code&node=65589&code=emViaWtAb3AucGx8NjU1ODl8MTA0OTg0NDEw> .
<http://sqlite.1065341.n5.nabble.com/template/NamlServlet.jtp?macro=macro_viewer&id=instant_html%21nabble%3Aemail.naml&base=nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.NodeNamespace&breadcrumbs=notify_subscribers%21nabble%3Aemail.naml-instant_emails%21nabble%3Aemail.naml-send_instant_email%21nabble%3Aemail.naml> NAML





--
View this message in context: http://sqlite.1065341.n5.nabble.com/SQLite-converts-all-unicode-characters-into-ANSI-tp65589p65614.html
Sent from the SQLite mailing list archive at Nabble.com.

[Simon Slavin]

Post by ZikO
The script was written in Notepad++
that controls which charset coding is used but I also double checked it in
Notepad and it indicated UTF-8; it can also be confirmed by reading the
[*ef bb bf* 43 52 45 41 54 45 20 54 41 42 4c 45 20] [CREATE TABLE]

If you try the command

INSERT INTO imiona (data,imie1,imie2,imie3) VALUES
('01/01/2012','Masława','Mieczysława','Mieszka');

(type it, do not use copy-and-paste) directly into the shell tool and then

SELECT * FROM imiona;

does it show the characters correctly or is it converting those too ?

Simon.

[ZikO]

Idid not check this way but I believe it would work now. I have hanged code page of cmd.exe into 65001 which is UTF-8. Now everything works J



Thanks for your effort.

ZikO





From: Simon Slavin-3 [via SQLite] [mailto:ml-node+***@n5.nabble.com]
Sent: 19 November 2012 14:33
To: ZikO
Subject: Re: SQLite converts all unicode characters into ANSI

Post by ZikO
The script was written in Notepad++
that controls which charset coding is used but I also double checked it in
Notepad and it indicated UTF-8; it can also be confirmed by reading the
[*ef bb bf* 43 52 45 41 54 45 20 54 41 42 4c 45 20] [CREATE TABLE]

If you try the command

INSERT INTO imiona (data,imie1,imie2,imie3) VALUES
('01/01/2012','Masława','Mieczysława','Mieszka');

(type it, do not use copy-and-paste) directly into the shell tool and then

SELECT * FROM imiona;

does it show the characters correctly or is it converting those too ?

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



_____

If you reply to this email, your message will be added to the discussion below:

http://sqlite.1065341.n5.nabble.com/SQLite-converts-all-unicode-characters-into-ANSI-tp65589p65608.html

To unsubscribe from SQLite converts all unicode characters into ANSI, click here <http://sqlite.1065341.n5.nabble.com/template/NamlServlet.jtp?macro=unsubscribe_by_code&node=65589&code=emViaWtAb3AucGx8NjU1ODl8MTA0OTg0NDEw> .
<http://sqlite.1065341.n5.nabble.com/template/NamlServlet.jtp?macro=macro_viewer&id=instant_html%21nabble%3Aemail.naml&base=nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.NodeNamespace&breadcrumbs=notify_subscribers%21nabble%3Aemail.naml-instant_emails%21nabble%3Aemail.naml-send_instant_email%21nabble%3Aemail.naml> NAML





--
View this message in context: http://sqlite.1065341.n5.nabble.com/SQLite-converts-all-unicode-characters-into-ANSI-tp65589p65611.html
Sent from the SQLite mailing list archive at Nabble.com.

[Bert Huijben]

-----Original Message-----
Sent: maandag 19 november 2012 02:57
Subject: Re: [sqlite] SQLite converts all unicode characters into ANSI
Hello,
thanks for your answers.
I downloaded both precompiled binaries and shell for windows from
www.sqlite.org.
CREATE TABLE IF NOT EXISTS imiona (
id INTEGER PRIMARY KEY,
data TEXT,
imie1 TEXT,
imie2 TEXT,
imie3 TEXT);

<snip>

D:\Wydarzenia\Events>sqlite3 imieniny.db < test.sql
Error: near line 1: near "´╗┐CREATE": syntax error
I then used the conding UTF-8 without BOM and the command was accepted but
sqlite> select * from imiona;
1 01/01/2012 Masława Mieczysława Mieszka
2 16/01/2012 Marcelego Walerii Włodzimie
3 17/09/2012 Franciszka Lamberty Narcyza
4 01/01/2012 Masława Mieczysława Mieszka
5 16/01/2012 Marcelego Walerii Włodzimie
6 17/09/2012 Franciszka Lamberty Narcyza
Now, I don't know how to test if the text is OK because SQLite2009Pro
displays everything converted to ANSI even ithough I set it to UTF-8 :/
Perhaps this program is not very good then. What would you recommend,
instead?
once again thanks for your effort towards this. I am really really surprised
this behaviour.

...

Could you try .read in sqlite3, instead of using a pipe. With the pipe the shell is responsible for reading your file and the forwarding to sqlite3, while using .read <filename> makes sqlite read the file, which should be fully transparent to whatever encoding you use.

Bert

[Tim Streater]

Post by ZikO
CREATE TABLE IF NOT EXISTS imiona (
id INTEGER PRIMARY KEY,
data TEXT,
imie1 TEXT,
imie2 TEXT,
imie3 TEXT);
INSERT INTO imiona (data,imie1,imie2,imie3) VALUES
('01/01/2012','Masława','Mieczysława','Mieszka'),
('16/01/2012','Marcelego','Walerii','Włodzimierza'),
('17/09/2012','Franciszka','Lamberty','Narcyza');

This works exactly as it should on my Mac, both inputting and displaying, so it's not sqlite.

[ZikO]

Thank you for this Tim,

I found the problem is cmd.exe in windows 7. I cannot believe that in 2012 the command processor still operate using old codepage OEM 850 by default. I have changed that to utf-8 and now everything’s correct. I am glad you have confirmed it works on MAC as well J



Best regards,

ZikO





From: Tim Streater-3 [via SQLite] [mailto:ml-node+***@n5.nabble.com]
Sent: 19 November 2012 18:35
To: ZikO
Subject: Re: SQLite converts all unicode characters into ANSI

Post by ZikO
CREATE TABLE IF NOT EXISTS imiona (
id INTEGER PRIMARY KEY,
data TEXT,
imie1 TEXT,
imie2 TEXT,
imie3 TEXT);
INSERT INTO imiona (data,imie1,imie2,imie3) VALUES
('01/01/2012','Masława','Mieczysława','Mieszka'),
('16/01/2012','Marcelego','Walerii','Włodzimierza'),
('17/09/2012','Franciszka','Lamberty','Narcyza');

This works exactly as it should on my Mac, both inputting and displaying, so it's not sqlite.
--
Cheers -- Tim

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



_____

If you reply to this email, your message will be added to the discussion below:

http://sqlite.1065341.n5.nabble.com/SQLite-converts-all-unicode-characters-into-ANSI-tp65589p65615.html

To unsubscribe from SQLite converts all unicode characters into ANSI, click here <http://sqlite.1065341.n5.nabble.com/template/NamlServlet.jtp?macro=unsubscribe_by_code&node=65589&code=emViaWtAb3AucGx8NjU1ODl8MTA0OTg0NDEw> .
<http://sqlite.1065341.n5.nabble.com/template/NamlServlet.jtp?macro=macro_viewer&id=instant_html%21nabble%3Aemail.naml&base=nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.NodeNamespace&breadcrumbs=notify_subscribers%21nabble%3Aemail.naml-instant_emails%21nabble%3Aemail.naml-send_instant_email%21nabble%3Aemail.naml> NAML





--
View this message in context: http://sqlite.1065341.n5.nabble.com/SQLite-converts-all-unicode-characters-into-ANSI-tp65589p65617.html
Sent from the SQLite mailing list archive at Nabble.com.