The OpenESQL preprocessor enables you to access a relational database via an ODBC driver by embedding SQL statements within your COBOL program.
Unlike separate preprocessors, OpenESQL is controlled by specifying the SQL directive when you compile your application.
OpenESQL provides an alternative for developers using ORACLE data sources, in the form of the ORACLE OCI interface. To use this interface, you must compile your applications with the following directive:
sql(targetdb=ORACLEOCI)
When connecting to the Oracle server, use an Oracle Net8 service name in place of an ODBC data source name in the CONNECT statement. See your Oracle documentation on how to set up ORACLE Net8 services.
When you use Oracle OCI, be aware that the following OpenESQL functions are not supported:
When you compile a program that includes Embedded SQL statements, you must specify the SQL Compiler directive and appropriate options. The ODBC driver that your program calls depends on the particular data source that you are accessing.
You can specify the SQL directive anywhere where you can specify any other Compiler directive. For example:
$set sql(dbman=odbc, autocommit)
cob -V testconn.cbl -C"anim SQL(DBMAN==ODBC)"
See the section Specifying cob Options in the chapter COBOL System Interface in your User's Guide for details of setting compiler directives.
Note: You cannot specify the SQL directive more than once, so must only specify it in one of the possible places.
The SQL Compiler directive consists of a set of options. See the topic SQL Compiler directive for more details. Available options are listed here.
Allows programs to use PIC X(n) host variables, and to select/insert/update hexadecimal characters in CHAR columns without changing source to use SQL TYPE BINARY host variables.
ALLOWNULLCHAR
If this is set, OpenESQL conforms to the SQL ANSI'92 entry level standard.
[NO]ANSI92ENTRY
| Default: | NOANSI92ENTRY |
If this is set, each SQL statement is treated as a separate transaction and is committed immediately upon execution. If this is not set, and the ODBC driver you are using supports transactions, statements must be explicitly committed (or rolled back) as part of a transaction.
[NO]AUTOCOMMIT
| Default: | NOAUTOCOMMIT |
Sets the AUTOFETCH attribute on SELECT statements that are run on Microsoft SQL Server data sources. Compiling with this directive can help the performance of your application. This directive works only if the program is also compiled with directive SQL(TARGETDB=MSSQLSERVER).
Note: BEHAVIOR=MAINFRAME for MS SQL Server enables AUTOFETCH. In this case errors on a cursor OPEN are deferred until the subsequent FETCH.
[NO]AUTOFETCH
| Default: | NOAUTOFETCH |
If this is set, each SQL statement is sent to the database at compilation time. If you specify statement checking at compilation time, you must also set DB and PASS.
Note: This option is dependent upon your driver and/or DBMS and in certain circumstances, invalid SQL statements will not be flagged.
[NO]CHECK
| Default: | NOCHECK |
If this is set, OpenESQL returns SQLCODE -19516 if the cursor is already open and the program is compiled with directive NOANSI92ENTRY. The default behavior when a program is compiled with NOANSI92ENTRY is to automatically close the cursor and then re-open it.
CHECKDUPCURSOR
| Default: | NOCHECKDUPCURSOR |
Causes OpenESQL to check if singleton SELECTs return more than one row when executed. If this occurs, OpenESQL sets SQLCODE to a -811.
CHECKSINGLETON
| Default: | NOCHECKSINGLETON |
Specifies the ASCII character code to use for the CONCAT symbol (|). Use this directive only if you need to change the default.
CONCAT=ascii-character-code
| Default: | CONCAT=33. |
Enables use of ODBC 3.0 connection pooling. When a connection is closed, the Driver Manager actually keeps it alive for a timeout period, and saves the overhead of re-establishing a connection from scratch if the application re-opens an identical connection. ODBC allows you to choose between having a pooling for an ODBC environment or for each driver. See your ODBC documentation for details.
This option is only useful for applications that frequently open and close connections. Note that some environments, such as Microsoft Transaction Server (MTS), control connection pooling themselves. This option will probably improve the performance of ISAPI applications that are not running under MTS.
CONNECTIONPOOL={DRIVER | ENVIRONMENT | NONE}
| Default: | CONNECTIONPOOL=NONE. |
Causes debug information to be put into the file sqltrace.txt.
[NO]CTRACE
| Default: | NOCTRACE. |
If ESQLVERSION is 2.0, CURSORCASE is implied. NOCURSORCASE means that cursor names are not case sensitive. CURSORCASE means that they are case sensitive. Note that in previous versions of OpenESQL, cursor names have been case sensitive.
[NO]CURSORCASE
| Default: | NOCURSORCASE |
The name of the data source to connect to. This option works in conjunction with the INIT and/or CHECK options. The .int file generated is the same except for the TARGETDB number embedded in the file.
[NO]DB
| Default: | NODB |
Specifies the preprocessor to use. This should always be set to DBMAN=ODBC. This directive is not required when compiling programs with OpenESQL.
DBMAN=preprocessor
| Default: | DBMAN=ODBC |
Eliminates the need for OpenESQL to call the routine GetLocaleInfo for any variable that would store decimal data. This routine is called every time the variable is accessed to return the decimal delimiter used. In most cases, this is a period or comma. The options for this directive are:
| DECDEL=PERIOD | Always use a period as a decimal delimiter |
| DECDEL=COMMA | Always use a comma as a decimal delimiter |
| DECDEL=LOCAL | Call GetLocaleInfo one time to get the decimal delimiter |
DECDEL={PERIOD | COMMA | LOCAL}
NODECDEL
| Default: | NODECDEL, which causes OpenESQL to call GetLocaleInfo every time a decimal variable is referenced. |
If DETECTDATE is set, OpenESQL inspects character host variables for the following ODBC escape sequences:
And binds the parameter appropriately, rather than as a character column. This is necessary if your server does not have a suitable native character string date representation (for example, Microsoft Access). It is also useful for generic applications. It can, however, cause problems if you have other character columns that can legitimately contain data that starts with "{d", "{t" or "{ts".
[NO]DETECTDATE
| Default: | NODETECTDATE. |
Sets OpenESQL syntax level.
[NO]ESQLVERSION
| Default: | NOESQLVERSION |
Enables FIPS flagging (NIST certification requirement only).
[NO]FIPSFLAG
| Default: | NOFIPSFLAG. |
Generates the SQL-INIT-FLAG variable for DB2 for OS/390 compatibility. The flag is updated when the SQL environment is initialized. If you define this variable in your application, compile your application with this directive set to NOGEN-INIT-FLAG and the variable will not be generated by the precompiler.
[NO]GEN-INIT-FLAG
| Default: | NOGEN-INIT-FLAG. |
In nested programs, this is the program-id at which to start generating the database interface code. If the program file name matches the program-id, you can just specify IGNORE-NESTED.
IGNORE-NESTED=program-id
NOIGNORENESTED
| Default: | NOIGNORE-NESTED. |
If this is set, the preprocessor automatically generates code to make the connection to the database. If you specify INIT, you must also specify DB and PASS. We highly recommend that you use EXEC SQL CONNECT statements in your application instead.
[NO]INIT
| Default: | NOINIT |
If this is set, OpenESQL will conform to the NIST interpretation of the SQL ANSI 92 entry level standard.
[NO]NIST
| Default: | NONIST |
Specifies the character code to use for NOT symbol (¬). Use this only if you need to change the default setting.
NOT=ascii-character-code
| Default: | NOT=170. |
ODBCTRACE=USER enables you to control ODBC tracing via odbc.ini from which you can specify the file that the trace goes into.
ALWAYS lets you control OBDC tracing via a directive, which is more convenient from within the IDE. ALWAYS generates the trace into MFSQLTRACE.LOG in the current directory, regardless of the settings in odbc.ini.
Under normal development conditions, and depending on the project's build setting, this is the Debug or Release directory of the current project. NEVER means that the application will never be traced and overrides odbc.ini. As ODBC trace files can contain sensitive information, use NEVER in production applications in secure environments. For more information see the database driver documentation.
ODBCTRACE={ALWAYS | NEVER | USER}
| Default: | ODBCTRACE=USER. |
This directive causes an application to register itself as an ODBC Version 3 application. If ODBCV3 is not specified, the application registers itself as an ODBC Version 2 application. There can be a small performance benefit in registering as an ODBC 3 application. However, registering as an ODBC Version 3 application can result in error and warning conditions returning different values for SQLCODE and SQLSTATE. We recommend that you use this directive with care.
ODBCV3
If PARAMARRAY is set, ODBC array binding is used, if it is supported by the ODBC driver, for all input parameters.
[NO]PARAMARRAY
| Default: | PARAMARRAY. |
The login to use to connect to the data source. This option works in conjunction with the INIT and/or CHECK options.
[NO]PASS
| Default: | NOPASS |
An application can use this directive to request that OpenESQL use block fetches for cursors. This can provide performance benefits, similar to array fetching, without having to change program logic. The performance benefit depends on the value of n and on whether the ODBC driver in use is already configured to use prefetching.
If n is less than 1000, it controls the number of rows to be fetched per batch and the same number of rows is fetched for all cursors. If n is greater than or equal to 1000, it sets the size of the prefetch buffer for each cursor. All cursors will have the same buffer size but the number of rows prefetched will depend on the overall size of the row returned by the query for each cursor.
When PREFETCH=n is used with Microsoft SQL Server, AUTOFETCH is also used for read only cursors. Cursors which are not read only are forced to be keyset cursors and can be used for positioned updates. PREFETCH=n is only supported with DB2, Oracle and MS SQL Server.
PREFETCH=n
| Default: | PREFETCH=8 |
Causes the preprocessor to append three characters to the name of the host variables when declaring them to ODBC.
[NO]QUALFIX
| Default: | QUALFIX. |
If RESULTARRAY is set, ODBC array binding is used, if it is supported by the ODBC driver, for all output parameters.
[NO]RESULTARRAY
| Default: | RESULTARRAY. |
Sets the cache size for prepared statements used by OpenESQL. The default is 20. Depending upon your application and data source, performance improvements or data errors can result if this value is set higher than that.
STMTCACHE
| Default: | NOSTMTCACHE |
Set this directive if you want to optimize performance for a specific data source or have the application generate database calls using ORACLE OCI rather than ODBC calls.
TARGETDB={MSQLSERVER | ORACLEOCI | ORACLE | INFORMIX |
SYBASE | DB2 | ORACLE7}
NOTARGETDB
| Default: | NOTARGETDB |
If THREAD is set to ISOLATE, all connections, cursors and so on are local to the thread that creates them. This is required for multi-threaded application server environments such as IIS/ISAPI. With THREAD=SHARE, if you have a hard-coded CONNECT statement and thread 1 executes it and then thread 2 executes it, thread 2 gets an error because the connection is already open. With THREAD=ISOLATE, each thread gets its own connection.
THREAD={SHARE | ISOLATE}
| Default: | THREAD=SHARE. |
Controls use of the ODBC's Cursor Library. The Cursor Library can provide support for scrolling cursors when the underlying driver doesn't, and can also allow "simulated" positioned updates. With USECURLIB=YES, the Cursor Library will always be used. With USERCURLIB=NO, it will never be used. With the default USERCURLIB=IFNEEDED, it will be used if the application tries to do something the driver manager thinks the driver doesn't support. To use a scrolling cursor with the Cursor Library, you must use a STATIC cursor. To do positioned updates using the Cursor Library, you must use OPTCCVAL concurrency.
Warning: A "simulated" positioned update might hit more than one row. We recommend including the primary key in the select for this reason.
USECURLIB={NO | YES | IFNEEDED}
NOUSECURLIB
| Default: | USECURLIB=IFNEEDED |
If an error occurs when compiling a program that requires technical support, your support representative might ask you to provide additional debug files to help in determining the cause of the problem. You obtain these debug files by specifying extra SQL Compiler directives. You might want to specifiy some of these directives to help in your own debugging efforts. The directives are:
| Directive | File created | Information within file |
|---|---|---|
| CHKECM(CTRACE) | ecmtrace.txt | Pseudo COBOL code that shows the code generated to replace the EXEC SQL statements. This file is equivalent to output from the OpenESQL ODBC precompiler. |
| CHKECM(TRACE) | ecmtrace.txt | Detailed information as to what information is passed between the ODBC ECM and the Compiler. If an error occurs that generates invalid syntax, this file will be needed to help isolate where the problem occurred. |
| SQL(CTRACE) | sqltrace.txt | Detailed list of information passed to OpenESQL Precompiler Services, and the results. This file is very useful if an error might involve a bug in the OpenESQL run time as well as the OpenESQL ECM. |
| ECMLIST | program-name.lst | The standard COBOL list file, with pseudo COBOL code that shows the code generated to replace the EXEC SQL statements. You must also compile the program with the CHKECM(CTRACE) and LIST directives. |
Before your program can access any data in a database, it must make a connection to the database.
There are two methods your program can use to connect to a database.
The CONNECT statement is used typically if either of the following is true:
This is generally used if your program is only going to connect to one database which is known at compilation time. If you specify the INIT option of the SQL Compiler directive, the compiler inserts a call at the start of the program to automatically connect the program to the data source specified in the DB option of the SQL Compiler directive using the login information specified in the PASS option.
When your application has finished working with a database, the application should disconnect from the database. This is done using the DISCONNECT statement.
If implicit connection is being used, OpenESQL automatically disconnects from the data source when the program terminates.
If you want OpenESQL to perform an implicit disconnect and rollback in the event of abnormal program termination this can be achieved by specifying the INIT=PROT option of the SQL Compiler directive.
A number of keywords are recognized by OpenESQL and should not therefore be used within your program for other purposes. A full list of reserved keywords is given in the topic Keywords.
To build an OpenESQL application, you need to:
The copyfiles sqlca.cpy and sqlda.cpy are provided in the directory $COBDIR/cpylib. You should ensure that this directory is included in the environment variable COBCPY.
A number of demonstration applications are supplied in the openesql directory which is located in the demo directory under your system's base installation directory.
Before you can use any of the demonstration applications, you need to have installed at least one ODBC driver.
Some of the demonstration applications expect that a table called EMP exists on the database to which you are connecting.
The OpenESQL demonstration applications all produce a console log displaying their progress and, possibly, query results. They all terminate on receipt of an error, after displaying an error message.
The following applications are provided:
The file testconn.cbl is an OpenESQL demonstration program that shows how to use different formats of the CONNECT and DISCONNECT statements.
The file catalog.cbl is an OpenESQL demonstration program that shows how to use the ODBC catalog functions from COBOL.
The file static.cbl is an OpenESQL demonstration program that shows how to use basic SQL functions from COBOL. It demonstrates the use of INSERT, UPDATE, DELETE, SELECT INTO and SELECT using a cursor. It also shows the use of COMMIT and ROLLBACK.
The file dynquery.cbl is an OpenESQL demonstration program that shows how to use dynamic SQL functions from COBOL. It provides the capability for the user to enter any SQL statement and the program will execute them. It shows how to prepare a SQL statement and how to use the SQLDA structure to retrieve data from columns when the type of the column is not known when the program is compiled.
If you are accessing the EMP table described above, a sample query would be:
select FIRST_NAME, LAST_NAME, HIRE_DATE, SALARY from EMP
With OpenESQL, you can use the COMMIT and ROLLBACK statements to exploit the transaction control facilities of ODBC. Although ODBC specifies transaction AUTOCOMMIT after each statement as the default mode of operation, OpenESQL turns this off for greater compatibility with other SQL systems. If you require this functionality, specify the AUTOCOMMIT option of the SQL Compiler directive.
Note: Not all ODBC drivers implement transaction processing and those that do not may make updates to the database permanent immediately. For more information see your database driver documentation.
The topic SQL/COBOL Data Type Mappings includes a table which shows the mappings used by OpenESQL when converting between SQL and COBOL data types.
The format of an ODBC date is yyyy-mm-dd, and an ODBC time is hh:mm:ss. These may not correspond to the native date/time formats for the data source in use. For input character host variables, native data source date/time formats can be used. For most data sources, we recommend a picture clause of PIC X(29), for example:
01 mydate PIC x(29).
...
EXEC SQL
INSERT INTO TABLE1 VALUES (1,'1997-01-24 12:24')
END-EXEC
...
EXEC SQL
SELECT DT INTO :mydate FROM TABLE1 WHERE X = 1
END-EXEC
display mydate
Alternatively, you can use ODBC escape sequences. ODBC defines escape sequences for date, time and timestamp literals. These escape sequences are recognized by ODBC drivers which replace them with data source specific syntax.
The escape sequences for date, time and timestamp literals take the form:
|
date |
{d 'yyyy-mm-dd'}
|
|
time |
{t 'hh:mm:ss'} |
|
timestamp |
{ts yyyy-mm-dd hh:mm:ss[.f...] |
The example program below shows date, time and timestamp escape sequences being used:
working-storage section.
EXEC SQL INCLUDE SQLCA END-EXEC
01 date-field1 pic x(29).
01 date-field2 pic x(29).
01 date-field3 pic x(29).
procedure division.
EXEC SQL
CONNECT TO 'Net Express 4.0 Sample 1' USER 'admin'
END-EXEC
* If the Table is there drop it.
EXEC SQL
DROP TABLE DT
END-EXEC
* Create a table with columns for DATE, TIME, and DATE/TIME
* NOTE: Access uses DATETIME column for all three.
* Some databases will have dedicated column types.
* If you are creating DATE/TIME columns on another data
* source, refer to your database documentation to see how to
* define the columns.
EXEC SQL
CREATE TABLE DT ( id INT,
myDate DATE NULL,
myTime TIME NULL,
myTimestamp TIMESTAMP NULL)
END-EXEC
* INSERT into the table using the ODBC Escape sequences
EXEC SQL
INSERT into DT values (1 ,
{d '1961-10-08'}, *> Set just the date part
{t '12:21:54' }, *> Set just the time part
{ts '1966-01-24 08:21:56' } *> Set both parts
)
END-EXEC
* Retrieve the values we just inserted
EXEC SQL
SELECT myDate
,myTime
,myTimestamp
INTO :date-field1
,:date-field2
,:date-field3
FROM DT
where id = 1
END-EXEC
* Display the results.
display 'where the date part has been set :'
date-field1
display 'where the time part has been set :'
date-field2
display 'NOTE, most data sources will set a default '
'for the date part '
display 'where both parts has been set :'
date-field3
* Remove the table.
EXEC SQL
DROP TABLE DT
END-EXEC
* Disconnect from the data source
EXEC SQL
DISCONNECT CURRENT
END-EXEC
stop run.
Alternatively you can use host variables defined with SQL TYPEs for date/time variables. Define the following host variables:
01 my-id pic s9(08) COMP-5. 01 my-date sql type is date. 01 my-time sql type is time. 01 my-timestamp sql type is timestamp.
and replace the INSERT statement with the following code:
*> INSERT into the table using SQL TYPE HOST VARS
move 1 to MY-ID
move "1961-10-08" to MY-DATE
move "12:21:54" to MY-TIME
move "1966-01-24 08:21:56" to MY-TIMESTAMP
EXEC SQL
INSERT into DT value (
:MY-ID
,:MY-DATE
,:MY-TIME
,:MY-TIMESTAMP )
END-EXEC
The SQLCA data structure is included in the file sqlca.cpy in the cpylib directory under your system's base installation directory. To include it in your program, use the following statement in the data division:
EXEC SQL INCLUDE SQLCA END-EXEC
If you do not include this statement, the COBOL Compiler automatically allocates an area, but it is not addressable from within your program. However, if you declare either of the data items SQLCODE or SQLSTATE separately, the COBOL Compiler generates code to copy the corresponding fields in the SQLCA to the user-defined fields after each EXEC SQL statement.
If you declare the data item MFSQLMESSAGETEXT, it is updated with a description of the exception condition whenever SQLCODE is non-zero. MFSQLMESSAGETEXT must be declared as a character data item, PIC X(n), where n can be any legal value. This is particularly useful as ODBC error messages often exceed the 70-byte SQLCA message field.
Note: You do not need to declare SQLCA, SQLCODE, SQLSTATE or MFSQLMESSAGETEXT as host variables.
OpenESQL supports two statements that are used with stored procedures:
Provides generic support for ODBC stored procedure calls.
Provides backwards compatibility with the Micro Focus Embedded SQL Toolkit for Microsoft SQL Server.
A stored procedure can:
Note: The features provided by different database vendors vary considerably, and any given vendor will offer only a subset of the features listed above. For this reason, stored procedure calls are much less portable between data sources than other OpenESQL statements.
When a stored procedure is called, any parameters are passed as a comma separated list, optionally enclosed in parentheses. A parameter can be a host variable or a literal, or the keyword CURSOR. The keyword CURSOR causes the parameter to be unbound, and should only be used with Oracle 8 stored procedures which return result sets.
If the parameter is a host variable it can be followed by one of the following words, which indicate the parameter type: IN, INPUT, INOUT, OUT, OUTPUT. If no parameter type is specified, INPUT is assumed.
Host variable parameters can be passed as keyword parameters, by preceding the host variable with the formal parameter name and an equals sign:
EXEC SQL CALL myProc (keyWordParam = :hostVar) END-EXEC
For maximum portability:
If a stored procedure call returns a result set, it must be used in a cursor declaration, thus:
EXEC SQL DECLARE cursorName CURSOR FOR storedProcecureCall
The stored procedure is then called by OPENing the cursor and FETCHing result set rows, like any other type of cursor.
Currently OpenESQL supports only a single result set.
ODBC parameters differ from Oracle array parameters. The effect of using a parameter array is the same as repeating the statement for each element of the array. On a stored procedure call, if one parameter is passed as an array, then all parameters must be arrays with the same number of elements. The stored procedure will "see" one call for each "row" of parameters. The number of rows passed can be limited to less than the full array size by preceding the call with the phrase FOR :hvar where :hvar is an integer host variable containing a count of the number of rows to be passed.
ODBC supports positioned update, which updates the row most recently fetched by using a cursor. However, not all drivers provide support for positioned update.
Note: You cannot use host arrays with positioned update.
With some ODBC drivers, the select statement used by the cursor must contain a FOR UPDATE clause to enable positioned update. Most data sources require specific combinations of SCROLLOPTION and CONCURRENCY to be specified either by SET statements or in the DECLARE CURSOR statement. If this fails to work, the ODBC Cursor Library provides a restricted implementation of positioned update which can be enabled by compiling with the directive SQL(USECURLIB=YES) and using SCROLLOPTION STATIC and CONCURRENCY OPTCCVAL (or OPTIMISTIC). To avoid multiple rows being updated when using the ODBC Cursor Library, the cursor query should include the primary key column(s) for the table to be updated.
$SET SQL(usecurlib=yes)
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 Products END-EXEC
EXEC SQL END DECLARE SECTION END-EXEC
PROCEDURE DIVISION.
EXEC SQL
WHENEVER SQLERROR perform OpenESQL-Error
END-EXEC
*> Demo for positioned updates using ACCESS datasource
EXEC SQL
CONNECT TO 'Inventory' USER 'admin'
END-EXEC
*> Put your program logic/SQL statements here
EXEC SQL
DECLARE CSR679 CURSOR
FOR SELECT
A.ProductID
,A.ProductName
,A.UnitPrice
FROM Products A
WHERE ( A.ProductID < 3 )
END-EXEC
EXEC SQL SET SCROLLOPTION static END-EXEC
EXEC SQL SET CONCURRENCY optccval END-EXEC
EXEC SQL OPEN CSR679 END-EXEC
PERFORM UNTIL SQLSTATE >= "02000"
EXEC SQL
FETCH CSR679 INTO
:ProductID
,:ProductName:ProductName-NULL
,:UnitPrice:UnitPrice-NULL
END-EXEC
*> Process data from FETCH
IF SQLSTATE = "00000"
*> increase price by 10%
compute unitprice = unitprice * 1.10
EXEC SQL
UPDATE Products
SET UnitPrice = :UnitPrice:UnitPrice-NULL
WHERE CURRENT OF CSR679
END-EXEC
END-IF
END-PERFORM
EXEC SQL CLOSE CSR679 END-EXEC
EXEC SQL COMMIT 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.
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 Net Express 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.
Copyright © 2005 Micro Focus International Limited. All rights reserved.