unicode not working in nvarchar column

[Former Member]

we are using sqlanywhere 12, I am trying to get some unicode characters displaying in our app

to start I though i would get it working in isql first

SELECT DB_PROPERTY( 'NcharCharSet' ); = 'UTF-8'

SELECT CONNECTION_PROPERTY( 'NcharCharSet' ); = 'UTF-8';

I have defined a column in the table as nvarchar(100);

If I insert something via isql into the table, for example

insert into mytable (mycol) values ('pokēmon go');

Select via isql, the nvchar columns are in a different font

and the accents are shown as spaces

What am I doing wrong?

[Former Member · Accepted solution]

Hi Alex,

How did you create your database? Make sure it uses UTF8BIN for both CHAR and NCHAR data types (dbinit utf8test.db -z UTF8BIN -zn UTF8BIN).

Happy hunting,

Jinwoo

[Former Member · Accepted solution]

The database is old, I was hoping I wouldn't have to unload/reload the whole thing.

I thought I could just convert the few columns I need to, to nvarchar to support UTF-8, do char columns need to be converted to UTF-8 as well?

[Former Member · Accepted solution]

Alex,

Yes, that was the case. I have tested with:

1. Default 1252LATIN1 for Char and UTF8BIN for NCHAR
None of char, nchar, varchar, nvarchar properly saves ē and replaces it with substitution character (appears as a blank character)

2. dbinit test.db -zn UTF8BIN

This is the same as case 1. Result is also the same

3. dbinit test.db -z UTF8BIN -zn UTF8BIN

Refer to the attached screenshots:

[Former Member · Accepted solution]

Thanks Jinwoo, it takes me a couple of hours to unload/reload, do you know if there is any special way to make sure i get the right charset conversion on the reload. (I haven't researched yet, I just thought I would check incase you know off hand)

[jeff_albion · Accepted solution]

Hi Alex,

>> I need to, to nvarchar to support UTF-8, do char columns need to be converted to UTF-8 as well?

Not necessarily - but it depends on how you are planning to insert the data into the NVARCHAR columns.

See: http://dcx.sap.com/index.html#sqla170/en/html/814817656ce21014adbcaaa8dda46684.html

     >> Character set conversion causes all SQL statements to be converted to the database character set before parsing and execution.

So this means if you use a direct SQL statement:

     INSERT INTO t1 (nvarchar1) VALUES ('<data>')

we will always interpret <data> in the character set of the database. Unless the default character set is changed to UTF-8 as Jinwoo indicated, you will get character set conversion. You will need to rebuild your database to change the default encoding to UTF-8.

However, if you use a prepared statement with a bound parameter (something you can't do in Interactive SQL):

See: http://dcx.sap.com/index.html#sqla170/en/html/3bd40bc26c5f1014a2c5d100ae5b773e.html

     INSERT INTO t1 (nvarchar1) VALUES (?)

you can then directly bind UTF-8 data to that statement, and we will insert the UTF-8 data directly without translation.

Best regards,

- Jeff

[Former Member · Accepted solution]

Hi Jeff,

We are using the sqlanywhere php driver, I have done some tests, on both an nvarchar column in the existing db, and in a new db as per Jinwoo's suggestion.

$conn = sasql_connect('DBN=mydatabase;ServerName=myserver;uid=dba;pwd=password1;charset=utf8');

$sql = "insert into mytable (mycol) values (?)";

$stmt = sasql_prepare($conn, $sql);

$success = sasql_stmt_bind_param($stmt, 's', $val);

$success = sasql_stmt_execute($stmt);

It works fine when the db charset is UTF-8, but not on my existing database.

Is this what you meant by using prepared statements? Maybe I am missing a setting somewhere in the connection string?

[jeff_albion · Accepted solution]

Hi Alex,

Yes, that is using prepared statements - this should work against an nvarchar column.

PHP doesn't natively format data using UTF-8. You can find lots of references for this elsewhere. Try wrapping $val in a utf8_encode call.

- Jeff

[Former Member · Accepted solution]

I played around with it some more, it turns out if i indicate that the value i am binding is a blob,

it doesn't re-encode the string when i pass it to the database.

I just thought i would post that here, incase someone else comes looking at this thread

$success = sasql_stmt_bind_param($stmt, 'b', $val);

So I can store utf-8 in an nvchar column as long as it is sent as a blob