OpenEsql Problem with NCHAR column and Oracle

[Migrated content. Thread originally posted on 15 March 2012]

Hi,
I'm having trouble with a simple sql select statement and the where clause is an
NCHAR(20) column.

EXEC SQL SELECT 'x' FROM TABLE WHERE NCHAR20_COL = :WS-VARIABLE END-EXEC

If WS-VARIABLE is > or
for Oracle.

We are using Netexpress 5.104.0083, I have directive NSYMBOL "NATIONAL" set and we're using Oracle 11g

Any advice would be much appreciated, Thanks

You don't say how the actual column is defined in the database but I am assuming in Oracle it is VARCHAR or VARCHAR2.

I am also assuming that you are using OpenESQL and ODBC for both SQL Server and Oracle.

The problem most likely has to do with how trailing spaces are handled in the compare as the column in the database most likely is stored without the trailing spaces meaning that the compare would only work if you used a data item of exactly the same size.

Try changing the definition of ws-variable from pic n(20) to a variable length definition as follows:

01 ws-variable.
49 variable-len PIC S9(4) COMP.
49 variable-data PIC N(20).

move 'test' to variable-data
move 4 to variable-len

EXEC SQL SELECT 'x' FROM TABLE WHERE NCHAR20_COL = :WS-VARIABLE END-EXEC

[Migrated content. Thread originally posted on 15 March 2012]

Hi,
I'm having trouble with a simple sql select statement and the where clause is an
NCHAR(20) column.

EXEC SQL SELECT 'x' FROM TABLE WHERE NCHAR20_COL = :WS-VARIABLE END-EXEC

If WS-VARIABLE is > or
for Oracle.

We are using Netexpress 5.104.0083, I have directive NSYMBOL "NATIONAL" set and we're using Oracle 11g

Any advice would be much appreciated, Thanks

Thanks for the reply, yes I am using OpenEsql for Sqlserver & Oracle but the column is not defined as a varchar/varchar2. Just to be clear, the column is defined as an NCHAR(20) on the database.

When a column is defined as a char(20) I can use a PIC X(10) variable to select rows from it (Sqlserver & oracle) but when it's defined as an nchar(20) and I use a PIC N(10) variable it works for sqlserver but returns no rows for oracle.

Thanks in advance.

[Migrated content. Thread originally posted on 15 March 2012]

Hi,
I'm having trouble with a simple sql select statement and the where clause is an
NCHAR(20) column.

EXEC SQL SELECT 'x' FROM TABLE WHERE NCHAR20_COL = :WS-VARIABLE END-EXEC

If WS-VARIABLE is > or
for Oracle.

We are using Netexpress 5.104.0083, I have directive NSYMBOL "NATIONAL" set and we're using Oracle 11g

Any advice would be much appreciated, Thanks

I tested this here and there does appear to be a problem if you use a PIC N field in the compare that is smaller than the size of the NCHAR column.

If you use a PIC N field that is the same size as the NCHAR description then it works fine.

You can also use a PIC X field for the compare against the NCHAR column and it does work if its size is smaller than the NCHAR description.

I tried a test with Oracle 11.2 and Oracle ODBC Driver 11.02.00.01.

I created a table named TESTTABLE with two columns:
TESTNAME NCHAR(10)
TESTCOMPANY VARCHAR2(20)

The column value I was searching for was "CHRIS"

This worked when I used the following:

01 compare-field pic n(10) value "CHRIS".
01 compare-field pic x(10) value "CHRIS".
01 compare-field pic x(5) value "CHRIS".

or just the literal "CHRIS".

it failed when I tried:
01 compare-field pic n(5) value "CHRIS".


If you would like to send me your serial number and contact information I will create an RPI (bug report) for this issue.

send to chris.glazier@microfocus.com

Thanks.