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:
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.