ODBC and NLS_LANG

Question

Let's say that I've created two different program executables, e.g. in C++.

For some reason, the two programs internals representation of text are different from each other. Let's say the first program is using text representation A and the other text representation B. It could be a specific 8-bit ANSI codepage, Unicode/UTF-8 or Unicode/UTF-16 or whatever.

Now each program want to communicate text (add/retrieve data) to/from the same database table on a (database) server. Each program communicates with the database through ODBC. So the programs do not know what database system they they are communicating with.

In this specific case though the database is actually a Oracle RDMS database and the database server administrator has setup the database to use UTF-8.

On the system on which the programs are running an appropriate ODBC driver is available, so that the programs can connect through ODBC. Each program will treat and convert from the ODBC data type SQL_C_CHAR to its internal text representation appropriately. I assume that the programs cannot do no other than to assume a specific encoding returned for SQL_C_CHAR text. If not the programs has to be told which encoding that is.

For Oracle, I know that the NLS_LANG environment variable can be used on the client. I assume it affects the ODBC driver (related to SQL_C_CHAR) to convert from a specific encoding (as given by NLS_LANG) to the internal encoding of the database (in this example UTF-8) and vice-versa.

If the machine running my programs are having a NLS_LANG this setting will affect the byte sequences returned for SQL_C_CHAR so my programs cannot suddenly assume a specific encoding for the text returned via SQL_C_CHAR.

Is it possible to setup the ODBC connection (preferably programmatically at runtime), so that it takes care of text conversions appropriately for the two programs, i.e. from/to representation to/from UTF-8 and from/to representation B to/from UTF-8?

Regards, /Michael

PS. As the programs are connecting through ODBC I don't think it would be nice that they should now anything about NLS_LANG as this is a Orcacle specific environment variable.

Answer 1

You need to build your app with the UNICODE macro defined (see sql header files like sqltypes.h and sqlucode.h). This changes the calls to SQLxxx which normally map to SQLxxxA (ANSI) to SQLxxxW and so they are using the so called Wide Character APIs. This means you can pass unicode (well UCS2 encoded data) to SQL APIs and combined with fetching string columns as SQL_WCHAR you'll get wide data from fetches. I may have missed it but I don't see you mention a platform (Windows or UNIX) and that may make a small difference if for instance you are on unix and are not using the unixODBC Driver Manager. There is loads on the MS site about unicode and ODBC.

There is a reasonable explanation of Oracle/Unicode/ODBC at Using the Easysoft ODBC-Oracle Driver with Unicode Data. It explains ODBC and Unicode wrt Oracle/NLS_LANG in a C context.