sqlite does not work with Multiple Byte Characters like Chinese in the table

You must Login before you can answer or comment on any questions.

I need to pre-load chinese characters in the sqlite database within my titanium app. Looks like it doesn't work. If the table contains pure English, it works fine in the app. But when it contains chinese, the query returns nothing. I can use sqlite browser to look at the table and do query and everything is working fine with the Chinese character. It just does not work in the titanium app. I ran the app in ios simulator, it returns null. Please advise.

— asked 10 months ago by Yiguang Hu
0 Comments

8 Answers

Accepted Answer

Hi Yiguang

I have created this sample code that shows that I can store and retrieve Chinese from a text field into the database and then retrieve it back out without issue. 

var txt = Ti.UI.createTextField({
    height: 40,
    value: 'Enter chinese here',
    width: Ti.UI.FILL
});
win.add(txt);
 
var btnTest = Ti.UI.createButton({
    title: 'test'
});
win.setLeftNavButton(btnTest);
btnTest.addEventListener('click', function (e) {
    var db = Ti.Database.open('test');
    db.execute('DROP TABLE IF EXISTS Chinese;');
    db.execute('CREATE TABLE IF NOT EXISTS Chinese (ID INTEGER, Title TEXT, Description BLOB);');
    db.execute('INSERT INTO Chinese (ID, Title, Description) values (?, ?, ?);', 1, txt.value, txt.value);
    var rowsc = db.execute("select * from Chinese;");
 
    alert(rowsc.fieldByName('title'));
    alert(rowsc.fieldByName('description'));
})
It will create a text field on the window and add a test button in the navigation, change to suite your needs.

I added a Chinese keyboard and used that to swap the value in the text box and then pressed the test button. This stores the entered text and then retrieves it back - all using standard code.

Did you use the parameter syntax of inserting the values as I have in my example, if not - it is very likely that reason. The database can handle variables better this way. Simply add I question mark '?' for each field and then in the SAME order add them after the string as extra parameters.

The example works with both standard text fields and blobs.

I hope this points you in the right direction.

I agree with your answer. But the problem is the titanium does not work with SQLite table with Chinese letters in it. The query result is null. Please advice. The query is fine in SQLite browser.

Hi Yiguang

Do the same characters work when shown in a standard label as the text attribute?

If not you may find that you have encode them using the unicode format \u0000 with the four 0000 representing the location in the unicode table.

This even happens with the GBP £ symbol it has to be encoded.

Let know how the test goes with the label. If you can show them in the label report back, either way report back with an example chinese string you are saving and I will create a test app to see if there are any short cuts or solutions to something that otherwise maybe somewhat painful to process.

Hardcoded Chinese characters work with in the standard title and label without problem even without using union code. The problem is the db execute query result set is null when the table has Chinese characters. I suspect the problem is because Chinese is multiple byte characters, similar problem may happen to japanese and Korean characters. When I insert data into SQLite outside titanium, I used utf-8 encoding.

I am not near my normal development computer to test if this helps in any way, but could you try using the following function - shown here as a literal example. 

var encoded = encodeURIComponent(str);
Where encoded is the result of the encoding of str.

Try wrapping a couple of test insert field with this and see what happen. This is a guess, but worth a quick go.

This works. var tab1 = Titanium.UI.createTab({
icon:'KS_nav_views.png', title:'??', window:win1 }); But the problem is here:

var rowsc = db.execute("select * from catalog where version='Chinese' and id="+i);

rowsc.isValidRow() is false when there are chinese in the table. But it works fine when the return contains pure English.

Thank you for your answer. It works now. But there is another hidden matter that contributed to the problem. When I initially created the table outside of Titanium, I didn't specify the column type and they all work fine from sqlite browser. But after I added the column types and regenerated the db and table and inserted data. Everything still works in sqlite browser but still not work in Titanium. Then I changed the dbpath param in the following statement and rebuild and then it works: var db=Titanium.Database.install('mytest.db','mypacktData');

So looks like the old database was not overwritten by newer version db file? This may be designed this way intentionally to preserve the history data.

Your Answer

Think you can help? Login to answer this question!