Re: [sqlite] SQLite converts all unicode characters into ANSI
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+s1065341n65615...@n5.nabble.com] Sent: 19 November 2012 18:35 To: ZikO Subject: Re: SQLite converts all unicode characters into ANSI On 19 Nov 2012 at 01:57, ZikO <[hidden email]> wrote: > 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'); 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. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite converts all unicode characters into ANSI
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 > 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. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite converts all unicode characters into ANSI
From: Igor Tandetnik [via SQLite] [mailto:ml-node+s1065341n65606...@n5.nabble.com] Sent: 19 November 2012 14:07 To: ZikO Subject: Re: SQLite converts all unicode characters into ANSI ZikO <[hidden email]> wrote: > 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┼éawaMieczys┼é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. > 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. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite converts all unicode characters into ANSI
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+s1065341n65608...@n5.nabble.com] Sent: 19 November 2012 14:33 To: ZikO Subject: Re: SQLite converts all unicode characters into ANSI On 19 Nov 2012, at 1:57am, ZikO <[hidden email]> wrote: > 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] 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. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite converts all unicode characters into ANSI
From: Clemens Ladisch [via SQLite] [mailto:ml-node+s1065341n65607...@n5.nabble.com] Sent: 19 November 2012 14:20 To: ZikO Subject: Re: SQLite converts all unicode characters into ANSI ZikO wrote: > 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 > 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 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. > sqlite3 imieniny.db < test.sql > Error: near line 1: near "´╗┐CREATE": syntax error You should just use plain UTF-8 text files without BOM. > 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┼éawaMieczys┼é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 > 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. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite converts all unicode characters into ANSI
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. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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. 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┼éawaMieczys┼éawa Mieszka 2 16/01/2012 Marcelego Walerii W┼éodzimie 3 17/09/2012 Franciszka Lamberty Narcyza 4 01/01/2012 Mas┼éawaMieczys┼é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. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite converts all unicode characters into ANSI
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. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users