When I paste those three characters into the Game -> Test Trigger window using the "Insert Unicode" button (as decimal) I get the following UTF-8 codes:
\E5\A4\A7\E5\AE\B6\E5\A5\BD
I make that to be 9 bytes long (3 Chinese characters, namely 大家好). According to Google Translate that means "Hello everyone".
I can't reproduce your problem with DatabaseStep - perhaps it is something to do with Perl or the way you are converting the UTF-8?
Here is my test code (run in the Immediate window using Lua):
DatabaseOpen ("db", GetInfo (66) .. "utf8_test.db", 6)
DatabaseExec ("db", [[
DROP TABLE IF EXISTS test;
CREATE TABLE test(
name TEXT NOT NULL
);
]])
hello = utils.fromhex ("E5A4A7E5AEB6E5A5BD") -- utf8: 大家好
print ("length before add=", #hello)
-- insert a record
DatabasePrepare ("db", "INSERT INTO test (name) VALUES ('" .. hello .. "')")
DatabaseStep ("db")
DatabaseFinalize ("db")
-- prepare a query
DatabasePrepare ("db", "SELECT * from test")
-- execute to get the first row
rc = DatabaseStep ("db") -- read first row
-- now loop, displaying each row, and getting the next one
while rc == 100 do
values = DatabaseColumnValues ("db")
print ("length after add=", #values [1])
print ("name=", utils.tohex (values [1]))
rc = DatabaseStep ("db") -- read next row
end -- while loop
-- finished with the statement
DatabaseFinalize ("db")
DatabaseClose ("db") -- close it
Output is:
length before add= 9
length after add= 9
name= E5A4A7E5AEB6E5A5BD
As you can see I have sent in the 9 bytes of UTF-8 data, and after adding it, and retrieving it with a SELECT, I get 9 bytes back, and they are the same 9.
I can't reproduce your problem with DatabaseStep - perhaps it is something to do with Perl or the way you are converting the UTF-8?
Here is my test code (run in the Immediate window using Lua):
Output is:
length before add= 9
length after add= 9
name= E5A4A7E5AEB6E5A5BD
As you can see I have sent in the 9 bytes of UTF-8 data, and after adding it, and retrieving it with a SELECT, I get 9 bytes back, and they are the same 9.
When i used your test code , I got another output :
length before add= 9
length after add= 8
name= E5A4A7E5AEB6E5A5
Let's start by understanding your results. In Lua, this code:
hello = utils.fromhex ("E5A4A7E5AEB6E5A5BD")
for i = 1, #hello do
local c = string.byte (hello:sub (i, i))
print (string.format ("%02X = %i", c, c))
end
I then copied and pasted your example from the above post without changes, and ran it. I got:
length before insert 9
1 > not an error
2 > unknown error
3 > not an error
whole word length : 9
1 : 229
2 : 164
3 : 167
4 : 229
5 : 174
6 : 182
7 : 229
8 : 165
9 : 189
大家好,
So you can see it worked perfectly for me. I don't think it is a DatabaseStep bug, as you said yourself you could add the data ok and if you used SQLiteSpy it showed the correct string. DatabaseStep is used for adding as well as retrieving, so that function clearly works.
Since your problem seems to be retrieving the data, perhaps avoiding using a table would help.
For the first test string it is "230 184 172 232 169 166 228 184 128". This is the only one string which work correct in my test.
I am confused it worked well but for another string the last char is '0'. I guess the problem is the last char only can be from 1 - 128, if it is not in this range will auto-change to 0.
Unfortunately, for the first test string it work well. But for other string it met the same problem too.
function "DatabaseColumnValue" looks returned the correct number 9, but the last char is '0x00'.
Can you give an example of a bad string?
The function DatabaseColumnValue, if given a string, turns it into a null-terminated string. Thus it will always have a 0x00 at the end, however that is the terminator. That should not count as part of the length. UTF-8 never has the value 0x00 in its strings (except for the value 0x00 itself, the string terminator).
Other strings, which have a Unicode code point of 128 or above, always have the high-order bit set.
I suspect the problem lies in the way Perl is handling Unicode. However one thing you could try is change the type of the string in SQLite from TEXT to BLOB. The BLOB type says it never changes the data internally (eg. you can store images or sounds in it). This may possibly help.
However I think it is more likely Perl is to blame. If possible, I would use Lua, as my examples worked with Lua.
However if you want to keep using Perl you can Google "perl utf-8".
Some results I got were:
"The use utf8 pragma tells the Perl parser to allow UTF-8 ..."
"Unicode-processing issues in Perl and how to cope with it ..."
Can you give me a snippet which reproduces the problem? On page one, I made a test which could not reproduce it.
I am assuming we are dealing in UTF-8, which should be handled OK by SetUpVariantString, however if you can give me some test data I can investigate further.
This was my test (in Lua) modified to show the SQL length:
DatabaseOpen ("db", GetInfo (66) .. "utf8_test.db", 6)
DatabaseExec ("db", [[
DROP TABLE IF EXISTS test;
CREATE TABLE test(name TEXT NOT NULL);
]])
hello = utils.fromhex ("E5A4A7E5AEB6E5A5BD") -- utf8: 大家好
print ("length before add=", #hello)
-- insert a record
DatabasePrepare ("db", "INSERT INTO test (name) VALUES ('" .. hello .. "')")
DatabaseStep ("db")
DatabaseFinalize ("db")
-- prepare a query
DatabasePrepare ("db", "SELECT name, length(name) from test")
-- execute to get the first row
rc = DatabaseStep ("db") -- read first row
-- now loop, displaying each row, and getting the next one
while rc == 100 do
values = DatabaseColumnValues ("db")
print ("length after add=", #values [1])
print ("name=", utils.tohex (values [1]))
print ("length from SQL=", values [2])
rc = DatabaseStep ("db") -- read next row
end -- while loop
-- finished with the statement
DatabaseFinalize ("db")
DatabaseClose ("db") -- close it
Output was:
length before add= 9
length after add= 9
name= E5A4A7E5AEB6E5A5BD
length from SQL= 3
Note that the SQL length(name) function correctly reports 3 characters (that is, 3 Unicode characters) rather than 9 bytes.
This was my test (in Lua) modified to show the SQL length:
DatabaseOpen ("db", GetInfo (66) .. "utf8_test.db", 6)
DatabaseExec ("db", [[
DROP TABLE IF EXISTS test;
CREATE TABLE test(name TEXT NOT NULL);
]])
hello = utils.fromhex ("E5A4A7E5AEB6E5A5BD") -- utf8: 大家好
print ("length before add=", #hello)
-- insert a record
DatabasePrepare ("db", "INSERT INTO test (name) VALUES ('" .. hello .. "')")
DatabaseStep ("db")
DatabaseFinalize ("db")
-- prepare a query
DatabasePrepare ("db", "SELECT name, length(name) from test")
-- execute to get the first row
rc = DatabaseStep ("db") -- read first row
-- now loop, displaying each row, and getting the next one
while rc == 100 do
values = DatabaseColumnValues ("db")
print ("length after add=", #values [1])
print ("name=", utils.tohex (values [1]))
print ("length from SQL=", values [2])
rc = DatabaseStep ("db") -- read next row
end -- while loop
-- finished with the statement
DatabaseFinalize ("db")
DatabaseClose ("db") -- close it
Output was:
length before add= 9
length after add= 9
name= E5A4A7E5AEB6E5A5BD
length from SQL= 3
Note that the SQL length(name) function correctly reports 3 characters (that is, 3 Unicode characters) rather than 9 bytes.
I have run above lua testing code, but got following result:
length before add= 9
length after add= 8
name= E5A4A7E5AEB6E5A5
length from SQL= 3
In fact, I think following code caused this problem:
CString is based on the TCHAR data type. If the symbol _UNICODE is defined for a build of your program, TCHAR is defined as type wchar_t, a 16-bit character encoding type; otherwise, it is defined as char, the normal 8-bit character encoding. Under Unicode, then, CStrings are composed of 16-bit characters. Without Unicode, they are composed of characters of type char.
Above message is copied from:
http://msdn.microsoft.com/en-us/library/ey142t48(v=VS.80).aspx
I think I'm wrong before.
But please check this problem again, above quote message is the reference that about this problem's causing(what I can find):
After fairly extensive investigations, which were slowly driving me insane[1], I have made some modifications to the Lua interface such that it bypasses the conversion to/from CString, and thus works around the problem. This applies to DatabaseColumnText, DatabaseColumnValue, and DatabaseColumnValues.
Now the Lua interface directly sets the values (without using the BSTR values) and avoids this problem.
My testing for the test program on pages 1 and 2 shows it now returns the correct data.
However there may be other areas which have similar problems, due to the use of CString in many places.
Improvements in version 4.66.
---
1. ... Because I kept getting different results. I was supposed to get 9 bytes out, but was getting 8. That shouldn't be too hard to fix, huh? But it jumped to 11, then 22, then down to 3, then up to 6. And then the data was just completely wrong. This will teach me to write a non-Unicode application in the future. But when I started (15 years ago, when I was young kek), it was just a little program to help me play MUSH games. In English.
MUSHclient is not a Unicode application (when I wrote it, I wasn't that familiar with Unicode). So internally it uses 8-bit strings. More recently it uses UTF-8 to encode Unicode, in some places.
However the WSH (Windows Script Host) uses BSTR to communicate between scripts and the program. The BSTR type is 16-bit Unicode data.
Now to convert from the output of a script call to BSTR the internal libraries assume the data is in the current code page (normal ANSI for me) and do a lookup to convert characters like 0xBD from the code page to the Unicode equivalent. Since it seems to work for me, I presume that there is a one-to-one mapping for them.
But in the case of Chinese code pages, some characters, like 0xBD must translate into something else.
Then when it is time to convert them back into 8-bit strings (eg. for Lua) the process is reversed. All seems to work fine providing each 8-bit character can be translated into Unicode, and back again, without changing or discarding it. With some code pages enabled, obviously this isn't happening.
One additional point - why did the data get onto the database OK, but not off it? Well the answer to that is that in the Lua -> Database direction, the data is not encoded into BSTR. It is simply copied across as "const char *" and thus is not fiddled with in any way.
It is in the reverse direction the problem applies.
However, enough talk. In about 15 minutes the new version should be available.
I'm using English Version of Windows XP, with Simplified Chinese Language Pack, so I can change my language encoding setting for non-Unicode programs, when I used English(US) as the encoding setting, the SQLITE3 database's accessing is ok, but, MushClient's layout, fonts, paths, ... is fail; when I used Chinese(PRC) as the encoding setting, everything is ok, except SQLITE3 database's accessing(Chinese content).
So, I have to decide to give up using the Scripting Function of SQLITE3 in MushClient, infact, I spend some time on LuaSQL in yesterday evening(Beijing time), it is working ok with MushClient.
Any way, a lot of thanks for your help. Your great working let me learn a lot.
Instead of storing UTF-8, store base-64 encoded UTF-8. That adds slightly to the amount stored, but means you are not storing bytes with the high-order bit set. That should work for everyone. Example code:
-- helper function to convert Unicode sequences
function unicode_convert (s)
return utils.utf8encode (tonumber (string.match (s, "^&#(%d+);$")))
end -- unicode_convert
DatabaseOpen ("db", GetInfo (66) .. "utf8_test.db", 6)
DatabaseExec ("db", "CREATE TABLE IF NOT EXISTS test (name TEXT NOT NULL);")
hello = string.gsub ("大家好", "&#%d+;", unicode_convert)
print ("original string=", hello)
-- insert a record
DatabaseExec ("db", "INSERT INTO test (name) VALUES ('" .. utils.base64encode (hello) .. "')")
-- prepare a query
DatabasePrepare ("db", "SELECT * from test")
-- execute to get the first row
rc = DatabaseStep ("db") -- read first row
-- now loop, displaying each row, and getting the next one
while rc == 100 do
values = DatabaseColumnValues ("db")
print ("string from database=", values [1])
print ("string converted back=", utils.base64decode ( values [1]) )
rc = DatabaseStep ("db") -- read next row
end -- while loop
-- finished with the statement
DatabaseFinalize ("db")
DatabaseClose ("db") -- close it
Example of that code in operation:
What we actually store in the database is "5aSn5a625aW9", which is actually hex E5A4A7E5AEB6E5A5BD encoded in base-64.
But because the text we are dealing with is just letters and numbers, we don't have the issue of it being wrongly decoded. At least I hope not. That code works for me with the encoding set to English, and also Chinese.
The Lua SQLite3 interface is built into MUSHclient anyway, and that might help, if you aren't using it already. For example, using the base64-encoding:
function unicode_convert (s)
return utils.utf8encode (string.match (s, "^&#(%d+);$"))
end -- unicode_convert
db = sqlite3.open(GetInfo (66) .. "utf8_test.db") -- open
db:exec "CREATE TABLE IF NOT EXISTS test (name TEXT NOT NULL);"
hello = string.gsub ("大家好", "&#%d+;", unicode_convert)
print ("original string=", hello)
-- insert a record
db:exec ("INSERT INTO test (name) VALUES ('" .. utils.base64encode (hello) .. "')")
for row in db:nrows ("SELECT * from test") do
print ("string from database=", row.name)
print ("string converted back=", utils.base64decode ( row.name ) )
end -- for loop
db:close() -- close
And without base-64 encoding:
function unicode_convert (s)
return utils.utf8encode (string.match (s, "^&#(%d+);$"))
end -- unicode_convert
db = sqlite3.open(GetInfo (66) .. "utf8_test.db") -- open
db:exec "CREATE TABLE IF NOT EXISTS test (name TEXT NOT NULL);"
hello = string.gsub ("大家好", "&#%d+;", unicode_convert)
print ("original string=", hello)
-- insert a record
db:exec ("INSERT INTO test (name) VALUES ('" .. hello .. "')")
for row in db:nrows ("SELECT * from test") do
print ("string from database=", row.name)
end -- for loop
db:close() -- close
That is shorter anyway. And since it sticks to Lua, you avoid the CString problems.
