Re: NVARCHAR2 and Pro*C

From: Peter Dickmann <peter.dickmann_at_sema-telecoms.com>
Date: Wed, 13 Jun 2001 10:58:20 +0200
Message-ID: <3b271d09@news.lhsgroup.com>

I must admit I don't have much experience with multibyte, but I took a look into the manual:

-------------------------8<---------------------------

CHARACTER SET [IS] NCHAR_CS
To specify which host variables hold National Character Set data, insert the clause "CHARACTER SET [IS] NCHAR_CS" in character variable declarations. Then you are able to store National Character Set data in those variables. You can omit the token IS. NCHAR_CS is the name of the National Character Set.

For example:

char character set is nchar_cs *str = "<Japanese_string>";

In this example, <Japanese_string> consists of double-byte characters which are in the National Character Set JA16EUCFIXED, as defined by the variable NLS_NCHAR. You can accomplish the same thing by entering NLS_CHAR=str on the command line, and coding in your application:

char *str = "<Japanese_string>"

Pro*C/C++ treats variables declared this way as of the character set specified by the environment variable NLS_NCHAR. The variable size of an NCHAR variable is specified as a byte count, the same way that ordinary C variables are.

To select data into str, use the following simple query:

EXEC SQL
   SELECT ENAME INTO :str FROM EMP WHERE DEPT = n'<Japanese_string1>';

Or, you can use str in the following SELECT:

EXEC SQL
   SELECT DEPT INTO :dept FROM DEPT_TAB WHERE ENAME = :str;

Environment Variable NLS_NCHAR
Pro*C/C++ supports multi-byte character sets with database support when NLS_LOCAL=NO. When NLS_LOCAL=NO, and the new environmental variable NLS_NCHAR is set to a valid fixed-width National Character Set, the database server supports NCHAR. See NLS_NCHAR in the Oracle8i Reference.

NLS_NCHAR must have a valid fixed-width character set specification (not a language name, that is set by NLS_LANG) at both precompile-time and runtime. SQLLIB performs a runtime check when the first SQL statement is executed. If the precompile-time and runtime character sets are different, SQLLIB will return an error code.

----------------------8<-----------------------------

With method4 (with wich I'm more familiar) I think you need to react on the described column type by adding such stuff to the host variable declaration / allocation.

Peter

Atul Pinge <apinge_at_carl.org> wrote in message news:3B2516F8.11F7762B_at_carl.org...
> Hello everybody,
>
> I have a table called CSISBN in my database defined as following:
>
> ISBN NVARCHAR2(10)
> BID NUMBER(10)
>
> Now in SQL*Plus I can access the records by query:
>
> select * from csisbn where isbn = N'1234';
>
> The output is:
>
> ISBN BID
> ---------- ---------
> 1234 1234
>
> But how can I access the same record using Pro*C? I'm using Oracle
> Dynamic SQL method 4. If I use the sample program and use the following
> query:
>
> select * from csisb where isbn = :isbn;
>
> the program prompts for the value of ISBN and if I enter 1234, I get
> following error:
>
> ORA-12704: character set mismatch
>
> The NLS settings on my database are as following:
>
> PARAMETER VALUE
> ------------------------------ ------------------------------
> NLS_LANGUAGE AMERICAN
> NLS_TERRITORY AMERICA
> NLS_CURRENCY $
> NLS_ISO_CURRENCY AMERICA
> NLS_NUMERIC_CHARACTERS .,
> NLS_CALENDAR GREGORIAN
> NLS_DATE_FORMAT DD-MON-YY
> NLS_DATE_LANGUAGE AMERICAN
> NLS_CHARACTERSET US7ASCII
> NLS_SORT BINARY
> NLS_NCHAR_CHARACTERSET US7ASCII
> NLS_RDBMS_VERSION 8.0.5.0.0
>
> Any help would be appreciated.
>
> Thanks in advance.
> Atul
>
Received on Wed Jun 13 2001 - 03:58:20 CDT