NVARCHAR2 support with non utf-8 database encoding

[wilkolazki]

We have an Oracle database which is running for many years now.
The db was set up with NLS_CHARACTERSET EE8ISO8859P2. Migration to AL32UTF8 is not possible due to incompatible ERP software.
We need to store some Unicode data, so we created a test table with NVARCHAR2 column. NLS_NCHAR_CHARACTERSET is AL16UTF16.

With SQLPlus we have inserted some test polish and russian text in the nvarchar2 column and we can select it correctly.
When selected with node oracledb, the result is malformed, any character which cannot be represented in the iso-8859-2 set is replaced by question mark.

Answer the following questions:

1. What error(s) you are seeing?

The nvarchar2 column contains following text:

По́йдзьце о дзятки, по́йдзьце вшистке разэм
За място, подъ слупъ на взго́рэкъ,
Тамъ пр̌едъ цудовнымъ клęкнийце образэмъ,
Побожне змо́вце пацёрэкъ.

Po polsku po polsku po polsku.
zażółć gęślą jaźń

Ă, Â, Î, Ș, Ț

After selecting:
SELECT nvchar_column FROM nvchar_table;

'????????? ? ??????, ????????? ??????? ?????\n?? ?????, ???? ????? ?? ?????????,\n???? ?????? ????????? ??ę?????? ????????,\n??????? ??????? ????????.\n \nPo polsku po polsku po polsku.\nzażółć gęślą jaźń\n\nĂ, Â, Î, ?, ?'

1. What environment variables did you set? How exactly did you set them?

NLS_LANG=POLISH_POLAND.AL32UTF8

1. What is your version of Node.js?
   v.4.4.4
2. What is your version of the Oracle client (e.g. Instant Client)? How was it installed? Where it is installed?
   1.11.0 installed locally with npm install.
3. What is your OS and version?
   Linux domx1st 3.2.0-4-amd64 Make node-oracledb installable via standard 'npm' install #1 SMP Debian 3.2.78-1 x86_64 GNU/Linux
4. What compiler version did you use?
   I assume that the gyp used:
   $ cpp --version
   cpp (Debian 4.7.2-5) 4.7.2

What do we need to change to turn on support for nvarchar2 values?

[cjbj]

There's no nchar/nvarchar support in node-oracledb (yet). It's possible that selecting nchar/nvarchar works if the DB charset is big enough to hold the N* characters but this hasn't been verified.

[dmcghan]

@wilkolazki Perhaps you could manually convert on the fly?

https://docs.oracle.com/cd/B28359_01/server.111/b28286/functions027.htm

[wilkolazki]

@dmcghan , I cant convert on the fly, as the DB charset is single byte ISO-8859-2 and the node-oracledb driver tries to convert the nvarchar values to iso befeore converting them back to the NLS_LANG charset which is UTF-8. Any character that is possible to be repesented in ISO is converted correctly. All other characters are replaced by question marks.

I have found that PHP driver has the same problem. It could be worked around by converting the unicode nvarchar2 values to RAW, which when selected, are represented as correct UTF-8 strings in PHP script.
In node, however, I get a Buffer and I am not sure how to convert it to string.

SELECT 
    utl_raw.cast_to_raw(convert(NVARCHAR2_COLUMN 'AL32UTF8')) as nvarchar_raw 
 FROM NVARCHAR_TABLE;

console.log prints the result as <Buffer d0 9f d0 be cc 81 d0 b9 d0 b4 d0 ... >

I think it would be also possible to convert that raw values to hex.
Then we would need to manually convert that hex codes to string in javascript.
And do reverse things for DMLs.
And always be aware if the column is nvarchar2 and requires such magic or it is simple varchar2 and we can pass plain values to the db. That will be pain.

I have multibyte column, multibyte client charset and yet client-side driver malforms data because of single byte database charset.

Do you know any open-source OCI driver for other languages which supports NVARCHAR2 column, so I could compare it's source code with the node-oracledb driver and maybe find out some native solution?

[dmcghan]

@wilkolazki Buffers have a toString method that accepts the encoding:
https://nodejs.org/dist/latest-v4.x/docs/api/buffer.html#buffer_buf_tostring_encoding_start_end

[dmcghan]

By the way, I ran some tests yesterday and wasn't able to reproduce the problem. Could be that my database character set wasn't what you use (too much effort to change that). But I was able to fetch and display multibyte characters just fine. I'm not saying it's guaranteed to work, just that I couldn't reproduce.

[cjbj]

@wilkolazki PHP OCI8 also doesn't have support for nchar/nvarchar.

Python's cx_Oracle does support these types; maybe @anthony-tuininga can put together a sample script?

Support in node-oracledb is somewhere down the todo list. The relevant OCI manual section is http://docs.oracle.com/database/121/LNOCI/oci05bnd.htm#LNOCI16411 Patches welcome.

[anthony-tuininga]

I am able to reproduce the problem. I created a database with the same character sets as the OP and created a table with an NVARCHAR2 column in it containing the data noted above. Because the data is being converted to EE8ISO8859P2 first (as requested by the node-oracledb driver) a lot of characters cannot be represented and turn up as the upside down question mark.

As Chris noted, cx_Oracle does support these types. For query no additional work needs to be done because cx_Oracle allows the retrieval as NCHAR. For bind, cx_Oracle does need to be told to bind as NCHAR or the data will get mangled because the default is CHAR and there is no ability to differentiate between the two in Python. Here is an example as requested. DATA refers to the data noted above. NLS_LANG and/or NLS_NCHAR need to be set appropriately.

cursor.setinputsizes(cx_Oracle.NCHAR) # tell cx_Oracle the data is NCHAR
cursor.execute("insert into TestTable values (1, :1)", (DATA,))
conn.commit()

for row in cursor.execute("select * from TestTable"):
    print(row) # data is printed as expected

Similar support could be added to the node-oracledb driver. I'll let Chris comment on where that is in the list of priorities!

[cjbj]

The driver should be / is converting it to UTF8. It's the DB char set vs DB nchar char set size difference that is likely the base cause in the given configuration.

[anthony-tuininga]

Yes, the driver is converting to UTF8. But the database is first converting to the DB char set because the driver (currently) says it wants it that way. That is what needs to be changed (allow the DB nchar set to be used) before the query will return the correct data.

[krismohan]

We will enhance node-oracledb. Does your node application do inserts also into the NVARCHAR column? Or does it do only queries?

[cjbj]

Closing this old question. NVARCHAR support is on the (long) to-do list.