mORMot Open Source

sheratongroup

Member

Registered: 2015-06-03

Posts: 5

Proper way to insert Unicode data into NVARCHAR2 column

Hi,

I am new to mORMot and Synopse, I hope I am posting my question to the correct place.

I am trying to figure out why my Unicode characters are not being saved to my oracle db properly.  I ran the following statments in SynDBExplorer.exe, compiled under DXE4.  This is in the Samples folder.  Specifically, "..\mORMot\SQLite3\Samples\12 - SynDB Explorer"

Here is my table creation statement.  Just one column.

CREATE TABLE nvarchar2_test (col1 NVARCHAR2(100));

Here is my insert.
INSERT INTO nvarchar2_test VALUES ( N'Ŧĥε ｑùíçķ ƀřǭŵņ fôｘ ǰűmpεď ōvêŗ ţħě łáƶÿ ďơǥ');

Here is my query and result.
SELECT * FROM nvarchar2_test;

COL1
-----------
'The qùíçk brown fôx jumped ovêr the lázÿ dog'

The Unicode characters were lost or remapped.  Given that this is an NVARCHAR2 column, I would have expected the data to be stored exactly as inserted.  What am I doing wrong?

A bit more information about my environment...  My NLS_NCHAR_CHARACTERSET is AL16UTF16.  The version of Oracle Client is 11.1, and the Oracle Server is "Oracle Database 11g Release 11.2.0.3.0 - 64bit Production"

My NLS_CHARACTERSET is WE8MSWIN1252.  Migrating to al32utf8 is not an option for me.  Since I am writing to an NVARCHAR2, it should be using the NLS_NCHAR_CHARACTERSET anyway, right?

I also checked the log SynDBExplorer.history, and I do see the Unicode characters there.  Eg,

2015-06-02 19:55:47 CREATE TABLE nvarchar2_test (col1 NVARCHAR2(100));
2015-06-02 19:58:21 INSERT INTO nvarchar2_test VALUES ( N'Ŧĥε ｑùíçķ ƀřǭŵņ fôｘ ǰűmpεď ōvêŗ ţħě łáƶÿ ďơǥ');
2015-06-02 19:58:38 select * from nvarchar2_test;

I am sure I am missing something simple.  Thanks so much for your help with this.

Last edited by sheratongroup (2015-06-03 01:30:56)

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,661

Website

Re: Proper way to insert Unicode data into NVARCHAR2 column

All SynDB content is UTF-8 encoded on the Delphi size, so uses Unicode with UTF-8 encoding.

In TDBExplorerFrame.BtnExecClick, the string=UnicodeString VCL content is converted into RawUTF8, then executed via Props.Execute().
Returned content is also received as UTF-8 JSON, then converted back into a string=UnicodeString.

See TSQLDBOracleConnection.Connect about how the connection is opened.
OciEnvNlsCreate() uses OCI_UTF8, and fOCICharSet should recognize AL16UTF16 as code page CP_UTF16.
So everything should be fine.

Which version of the framework source code are you using?
Do not use the 1.17 'stable' version, but the 1.18 'unstable' version.

sheratongroup

Member

Registered: 2015-06-03

Posts: 5

Re: Proper way to insert Unicode data into NVARCHAR2 column

Thanks for getting back to me so quickly.  Before posting, I tried many different UTF parameter combinations with OCIEnvNLSCreate()... I began flailing and even tried combinations I knew would not work.  See below. 

I believe the parameters provided in the source, should work with my settings, no? 
EnvNlsCreate(fEnv,Props.EnvironmentInitializationMode, nil,nil,nil,nil,0,nil,OCI_UTF8,OCI_UTF8);

I also want to confirm that when performing inserts/updates I should prefix with the letter N. 
Eg, INSERT INTO nvarchar2_test VALUES ( N'Ŧĥε ｑùíçķ ƀřǭŵņ fôｘ ǰűmpεď ōvêŗ ţħě łáƶÿ ďơǥ');

I do believe I am using the 1.17 version, though I cannot remember for sure.  Where is the version information specified (after I unpack and delete the original zip?). 

Thank you.

For entertainment here, are some of my flailing attempts.

      EnvNlsCreate(fEnv,Props.EnvironmentInitializationMode,       //NO CRASH, doesn't work though.
        nil,nil,nil,nil,0,nil,OCI_WE8MSWIN1252,OCI_AL32UTF8);

//     EnvNlsCreate(fEnv,Props.EnvironmentInitializationMode,       //NO CRASH, Replacement Chars
//        nil,nil,nil,nil,0,nil,OCI_AL32UTF8,OCI_WE8MSWIN1252);

//      EnvNlsCreate(fEnv,Props.EnvironmentInitializationMode,       //NO CRASH, Replacement Chars
//        nil,nil,nil,nil,0,nil,OCI_UTF8,OCI_WE8MSWIN1252);

//      EnvNlsCreate(fEnv,Props.EnvironmentInitializationMode,       //NO CRASH, UNUSUAL CHARS GET STORED... Ŧĥε ｑùíçķ ƀřǭŵņ fôｘ ǰűmpεď ōvêŗ ţħě łáƶÿ ďơǥ
//        nil,nil,nil,nil,0,nil,OCI_WE8MSWIN1252,OCI_WE8MSWIN1252);

//      EnvNlsCreate(fEnv,Props.EnvironmentInitializationMode,       //SSE NO CRASH, UNUSUAL CHARS GET STORED... Ŧĥε ｑùíçķ ƀřǭŵņ fôｘ ǰűmpεď ōvêŗ ţħě łáƶÿ ďơǥ
//        nil,nil,nil,nil,0,nil,OCI_WE8MSWIN1252,OCI_UTF16ID);

//      EnvNlsCreate(fEnv,Props.EnvironmentInitializationMode,     //NO CRASH, UNUSUAL CHARS GET STORED... Ŧĥε ｑùíçķ ƀřǭŵņ fôｘ ǰűmpεď ōvêŗ ţħě łáƶÿ ďơǥ ---Gets Inserted As---> Ŧĥε ｑùíçķ ƀřǭŵņ fôｘ ǰűmpεď ōvêŗ ţħě łáƶÿ ďơǥ
//        nil,nil,nil,nil,0,nil,OCI_WE8MSWIN1252,OCI_UTF8);

//      EnvNlsCreate(fEnv,Props.EnvironmentInitializationMode,      //CRASH
//        nil,nil,nil,nil,0,nil,OCI_UTF16ID,OCI_UTF8);

//      EnvNlsCreate(fEnv,Props.EnvironmentInitializationMode,      //DOESN'T CRASH, Replacement Chars
//        nil,nil,nil,nil,0,nil,OCI_UTF8,OCI_UTF16ID);

//      EnvNlsCreate(fEnv,Props.EnvironmentInitializationMode,
//        nil,nil,nil,nil,0,nil,OCI_UTF16ID,OCI_UTF16ID);  //CRASH

//      EnvNlsCreate(fEnv,Props.EnvironmentInitializationMode,        //ORIGINAL CODE, NO CRASH, but uses Replacement Chars
//        nil,nil,nil,nil,0,nil,OCI_UTF8,OCI_UTF8);

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,661

Website

Re: Proper way to insert Unicode data into NVARCHAR2 column

Do not use the N prefix but parametrized queries.

sheratongroup

Member

Registered: 2015-06-03

Posts: 5

Re: Proper way to insert Unicode data into NVARCHAR2 column

How's this for an even simpler test.  Can the following be done?  select 'The qùíçk brown fôx jumped ovêr the lázÿ dog' from dual.  If I could get just this to work I would be happy.

My questions are specific to both the API and the sample code provided in SynDBExplorer.dproj.  I don't believe you answered them.  

I am trying to read/write data in an NVARCHAR2 column in a database with NLS_CHARACTERSET = WE8MSWIN1252 and NLS_NCHAR_CHARACTERSET = AL16UTF16, is the syntax in SynDBOracle.pas, the part that calls EnvNLSCreate correct?  Here is your code...

EnvNlsCreate(fEnv,Props.EnvironmentInitializationMode, nil,nil,nil,nil,0,nil,OCI_UTF8,OCI_UTF8);  //from your sample. 
Is OCI_UTF8 correct as the last two parameters for my database?

Why must I use paramaterized queries, and how do I do this with SynDB Explorer?  I would prefer to use parameterized queries (eventually).  But why is this required? 

Anyway, I tried entering parameterized queries in SynDBExplorer, but couldn't figure out how to bind the variables from the GUI.  How do I bind variables in SynDBExplorer?

Eg,?
  INSERT INTO nvarchar2_test VALUES ( :uStr);
instead of
  INSERT INTO nvarchar2_test VALUES ('The qùíçk brown fôx jumped ovêr the lázÿ dog');

I don't think SynDBExplorer has a mechanism to bind variables, if it does how?  Must I use an anonymouse block?
Eg,
declare
  uStr nvarchar2(100);
begin
  uStr := 'The qùíçk brown fôx jumped ovêr the lázÿ dog';
  INSERT INTO nvarchar2_test VALUES ( :uStr);
end;

Thank you.

Last edited by sheratongroup (2015-06-04 23:40:52)

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,661

Website

Re: Proper way to insert Unicode data into NVARCHAR2 column

No, of course SynDBExplorer does not allow to bind variables.

I still do not understand why you have issues with encoding.
IMHO OCI_UTF8 is correct, and independent from the NLS encoding used.
See http://docs.oracle.com/database/121/LNO … LNOCI17114
AFAIK using UTF-8 on the client-side should have no problem with UTF-16 encoding on the server side.

I do not have any possibility to do the testing with your configuration.
Could you use the debugger, and see what is happening in SynDBOracle?

sheratongroup

Member

Registered: 2015-06-03

Posts: 5

Re: Proper way to insert Unicode data into NVARCHAR2 column

>>No, of course SynDBExplorer does not allow to bind variables.
  I didn't think it could, but you suggested using parameterized queries. Not sure how you expected me to follow your suggestion?

>>I still do not understand why you have issues with encoding.
  Me neither.

>>IMHO OCI_UTF8 is correct, and independent from the NLS encoding used.
  I wonder if OCI_UTF8 is correct for both params, charset and for ncharset? 

>>See http://docs.oracle.com/database/121/LNO … LNOCI17114
  True, the only way NLSLANG would impact ENVNLSCreate, is if the 'charset' and 'ncharset' parameters are zero.  Thank you for confirming this. 

>>AFAIK using UTF-8 on the client-side should have no problem with UTF-16 encoding on the server side.
>>I do not have any possibility to do the testing with your configuration.
>>Could you use the debugger, and see what is happening in SynDBOracle?
  I would be happy to continue to try.  Though I am losing hope.  If anyone else out there has my configuration, could you try?

Last edited by sheratongroup (2015-06-20 03:21:51)

sheratongroup

Member

Registered: 2015-06-03

Posts: 5

Re: Proper way to insert Unicode data into NVARCHAR2 column

For the sake of posterity and for future users, I corrected my problem by adding code to initialize OCI_ATTR_CHARSET_FORM. 

OCI_ATTR_CHARSET_FORM is set to SQLCS_IMPLICIT by default, this means that characters get encoded to the database character set first, before getting set to the national character set.  The implication is that if your database character set is a non-unicode set (like mine), then you will get replacement characters in your NVARCHAR columns.  The key is to set OCI_ATTR_CHARSET_FORM to SQLCS_NCHAR.

Hope this is helpful to someone.