Unicode Support in OpenESQL

Some applications want to retrieve or store unicode data in Microsoft SQL Server data sources without it being converted to ANSI. Previous versions of OpenESQL did not have a way of accessing data without it being automatically converted. Now, however, OpenESQL and your COBOL development system can work directly with unicode data without having it converted to ANSI, by using a new type of host variable. Microsoft SQL Server supports three unicode column types:

• NCHAR
• NVARCHAR
• NTEXT

To access these columns without the data being automatically converted, define a host variable with a definition of:

PIC N(xx) USAGE NATIONAL

where xx is the size of the column. This format is currently supported for both fixed and variable length data. Variable length data can be terminated with nulls to signify end of data for column when inserting or updating data. When data is retrieved from the data source, it will be space filled to the end of the host variable.

For example, the following program will retrieve employee information from the Northwind sample database that comes with Microsoft SQL Server 2000 product:

$SET UNICODE(NATIVE)
$SET SQL
 WORKING-STORAGE SECTION.

 EXEC SQL INCLUDE SQLCA  END-EXEC

* after an sql error this has the full message text
 01 MFSQLMESSAGETEXT  PIC X(250).
 01 IDX         PIC X(04)  COMP-5.

 EXEC SQL BEGIN DECLARE SECTION  END-EXEC
* Put your host variables here if you need to port
* to other COBOL compilers
 EXEC SQL INCLUDE Employees END-EXEC

 EXEC SQL END DECLARE SECTION END-EXEC

 PROCEDURE DIVISION.

     EXEC SQL
         WHENEVER SQLERROR perform OpenESQL-Error 
     END-EXEC

     EXEC SQL
         CONNECT TO 'LocalServer'
     END-EXEC

* Put your program logic/SQL statements here
     EXEC SQL
         DECLARE CSR135 CURSOR FOR SELECT
               A.FirstName
              ,A.LastName
              ,A.EmployeeID
              ,A.HireDate
             FROM Employees A
     END-EXEC
     EXEC SQL OPEN CSR135 END-EXEC
     PERFORM UNTIL SQLSTATE >= "02000"
         EXEC SQL
             FETCH CSR135 INTO
                :Employees-FirstName
               ,:Employees-LastName
               ,:Employees-EmployeeID
               ,:Employees-HireDate:Employees-HireDate-NULL
         END-EXEC

        *> Process data from FETCH
         IF SQLSTATE < "02000"

*      for array fetches, field sqlerrd(3) contains the 
*      number of rows returned
*            PERFORM VARYING IDX FROM 1 BY 1 
*                    UNTIL IDX > SQLERRD(3)

*     you will need to add code here to process the array

*            END-PERFORM
         END-IF
     END-PERFORM
     EXEC SQL CLOSE CSR135 END-EXEC

     EXEC SQL DISCONNECT CURRENT END-EXEC
     EXIT PROGRAM.
     STOP RUN.

* Default sql error routine - modify to stop program 
* if needed
 OpenESQL-Error Section.
     display "SQL Error = " sqlstate " " sqlcode
     display MFSQLMESSAGETEXT
*    stop run
     exit.

This is the same code that would retrieve the data in ANSI except for the definitions in the INCLUDE copybook Employees which now looks like:

* -----------------------------------------------------------
* COBOL DECLARATION FOR TABLE Employees
* -----------------------------------------------------------
 01  DCLEmployees.
     03 Employees-EmployeeID       PIC S9(09)  COMP-5.
     03 Employees-LastName         PIC N(20) USAGE NATIONAL.
     03 Employees-FirstName        PIC N(10) USAGE NATIONAL.
     03 Employees-Title            PIC N(30) USAGE NATIONAL.
     03 Employees-TitleOfCourtesy  PIC N(25) USAGE NATIONAL.
     03 Employees-BirthDate        PIC X(23).
     03 Employees-HireDate         PIC X(23).
     03 Employees-Address          PIC N(60) USAGE NATIONAL.
     03 Employees-City             PIC N(15) USAGE NATIONAL.
     03 Employees-Region           PIC N(15) USAGE NATIONAL.
     03 Employees-PostalCode       PIC N(10) USAGE NATIONAL.
     03 Employees-Country          PIC N(15) USAGE NATIONAL.
     03 Employees-HomePhone        PIC N(24) USAGE NATIONAL.
     03 Employees-Extension        PIC N(4) USAGE NATIONAL.
     03 Employees-Photo            PIC X(64000).
     03 Employees-Notes          PIC N(32000) USAGE NATIONAL.
     03 Employees-ReportsTo        PIC S9(09)  COMP-5.
     03 Employees-PhotoPath        PIC N(255) USAGE NATIONAL.

OpenESQL Assistant has also been enhanced to support unicode data. See the chapter OpenESQL Assistant for details.