create UTF16 environment

[tvr]

Hello,

is it possible to create environment in UTF16 (like specify OCI_UTF16 mode) and have sql statements coded in UTF16? If yes, then from which version?

Since we want to have some columns as nchar/nvarchar, we now declare variable charset as CurrentNCharSetID and run into problem, when statement contains string literal - as f.e. insert into table(column) values(coalescePar1, 'something')).
Does it help to work under UTF16 environment or only possibility is to prepend string literal with n?

Thanks

 

[Marco Kalter]

If you set the client character set to UTF8 through the NLS_LANG registry key or environment variable, you can pass SQL statements encoded in UTF16 format.

 

[tvr]

Even when I set NLS_LANG to CZECH_CZECH REPUBLIC.AL32UTF8 it didnt help.
Example:

1. This will throw exception ORA-12704: character set mismatch
UPDATE TestingCountries SET Code = coalesceCode, '00000000') WHERE ID = :OBJID AND ObjVersion = :ObjVersion

2. This will execute without problem
UPDATE TestingCountries SET Code = coalesceCode, n'00000000') WHERE ID = :OBJID AND ObjVersion = :ObjVersion

So I thought by using OCI_UTF16 mode and call
OCIStmtPrepare(stmthp, errhp, PAnsiChar(ParsedSQL), length(ParsedSQL), OCI_NTV_SYNTAX, OCI_DEFAULT))
with ParsedSQL encoded as UTF16 will probably solve this error. But maybe I am mistaken.

 

[Marco Kalter]

Does this work?

select '' as unicode from dual

 

[tvr]

Not, I had sent some azbuka letters, but got back question marks.

As I can tell from debug, SQL is indeed converted to UTF8 when sent to oracle, and field of returned TOracleQuery has set CharSetForm:1; CharSetID:873; CharSize:34; IsUTF16:False; IsUTF8:True).

What I suppose should tell before, but didn't occur to me - we have set database NLS_CHARACTERSET to EE8MSWIN1250 and NLS_NCHAR_CHARACTERSET to AL16UTF16. So I suppose that SQL command is translated to NLS_CHARACTERSET on server, correct?

 

[Marco Kalter]

That is indeed correct. You will need a database with character set UTF8.