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.
To obtain ODBC support, you must:
ODBC support for your COBOL development system cannot work until you have set up a data source name (DSN) in the ODBC Manager. You can access the ODBC Manager via Administrative Tools in the Control Panel on your Windows desktop. Click on the appropriate 32bit ODBC icon. Click on the System DSN tab.
The ODBC Data Source Administrator dialog box opens. For details on how to assign DSNs, click on the Help button in the ODBC Data Source Administrator dialog box.
Note: If you are running under a 64-bit version of Windows, to create a DSN for a 32-bit application, you must execute the 32-bit version of the ODBC Administrator utility. The command to start this utility is odbcad32. The odbcad32.exe file is located in your windowsdir\syswow64 folder.
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:
You can use OpenESQL as an effective migration tool, particularly when you migrate from the mainframe. OpenESQL provides the following functionality that makes migration transparent, yet efficient:
For more information, see the SQL Compiler Directive topics BEHAVIOR and TRACELEVEL, and the demo application behavior.app listed under Demonstration Applications later in this chapter.
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)
For setting them from the IDE, see the topic To set the SQL Compiler Directive options .
Note: While you can specify the SQL directive more than once, we do not recommend it. Multiple sets of SQL directive options can conflict with one other causing certain options to be inadvertantly cancelled or overridden.
The SQL Compiler directive consists of a set of options. See the Compiler Directive topic SQL for more details. Available options are listed in the topic SQL Compiler Directive Options.
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 source under your Net Express base installation directory and can be included in your program in the normal way.
When you build your .exe, using your COBOL development system, all the necessary object files are linked in for you automatically unless you compile your program with directive SQL(GEN-CC2).
Note: If you move your application to another system, ensure that the appropriate file is available on that system:
| Environment | File |
|---|---|
| 32-bit | odbcrw32.dll |
| 64-bit | odbcrw64.dll |
A number of demonstration applications are supplied in the Examples\Net Express IDE\ODBCESQL directory, which is a 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, and created DSNs to use with the demonstrations.
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:
Connects to a data source name LocalServer using the Microsoft SQL Server ODBC driver. To do this, see the Help topic To set up an ODBC data source name.
The program creates and populates a table used by behavior.cbl and behavsub.cbl. After the table is created, we show how the BEHAVIOR directive makes the same ambiguous COBOL cursor declaration read only in behavior.cbl and updateable in behavsub.cbl.
You can verify this by looking at the trace file OpenESQLTrace.processID.log, in the project directory. The log file is generated by the TRACELEVEL directive in behavior.cbl.
Displays an SQL Data Sources dialog. Enter or select a name and click on OK. A Login dialog is displayed. Enter a login name of "admin", leave the password blank and click on OK. Performs three data dictionary queries and outputs the results.
Prompts for a data source, user name and password. Enter the data source name you created, a user name of "admin" and leave the password blank (just press return). Four tests which perform connects and disconnects using a variety of syntax options are run. The fifth test displays an SQL Data Sources dialog. Select the appropriate name from the Machine Data Source list and click on OK. A Login dialog is displayed. Enter a login name of "admin", leave the password blank and click on OK. The fifth test is run and the program terminates.
Connects to the sample database and prompts for a customer code. Enter BLUEL (as the prompt suggests). Two fields from the customer record are displayed and the program prompts for another customer code. Just press the return key this time. The program prompts for a region. Enter CA (as the prompt suggests). The program displays a list of customers in that region and prompts for another region. This time, press the return key to terminate the program.
Both applications run the same sequence of tests, but using different SQL syntax options. They both start by prompting for a data source and user name. The test sequence is:
Step two may output an error message - this is expected and the programs will continue. The final stage should produce an error message, and again this is not treated as a genuine error (though the absence of an ODBC error is treated as a test failure).
Attempts to connect and displays an error message. Displays an SQL Data Sources dialog. Select the DSN name you created and click on OK. A Login dialog is displayed. Enter a login name of "admin", leave the password blank and click on OK. Tests error handling and outputs two error messages.
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 source 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, literal parameters should be avoided and only host variable parameters should be used, and a given call should use either only normal, positional parameters or keyword parameters, but not both. Some servers support a mixture, but keyword parameters should occur after all positional parameters. Keyword parameters are useful as an aid to readability and where the server supports default parameter values and optional parameters.
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 fom 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 numer of rows to be passed.
Note: The online help provides the structure and examples for both the CALL and EXECSP statements. (Click Help Topics on the Help menu. Then, on the Index tab, click CALL or click EXECSP.)
The demonstration application, dynamic.app is located in the following directory:
Examples\Net Express IDE\odbcesql
Open this project and select Step from the Debug menu (you may need to rebuild the project first) in order to step through the application for a demonstration of how to use dynamic SQL in your COBOL programs.
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.
This section describes what you must do to use OpenESQL in environments controlled by Web and Application Servers, such as IIS, MTS, COM+, CICS and Tuxedo.
All servers differ, but the principle described here is common to them all.
OpenESQL is thread safe. Normally all threads in an application share SQL resources such as connections and cursors. When running with an application server, however, threads will be scheduled to handle requests from different users. Therefore, you must use the directive:
SQL(THREAD=ISOLATE)
to ensure that each thread's resources are isolated from each other.
Note: OpenESQL has an overhead of 72K for each thread created, and this memory is not released until the application terminates.
In most environments, the application server will manage a pool of connections, which means that actual connect and disconnect requests to the database will be rare. When an application runs, it will reuse an existing connection. In most cases, connection pooling will be managed by the ODBC Driver Manager and will be transparent to the application. In other cases, the application server itself will manage the connection pool, and the application must use a "set connection" statement before it does anything else.
Where the application uses OpenESQL connect and disconnect statements, and it is not clear that the application server itself enables ODBC connection pooling, it might be worth experimenting with the SQL(CONNECTIONPOOL=...) directive. However, you are unlikely to need to do this.
In many cases, the application server will provide transaction management. This will be determined when your component is placed under the control of the application server. If the application server is not providing transaction management, it must use OpenESQL COMMIT and ROLLBACK statements to manage transactions. If, however, the application server is providing transaction management, you must do the following:
If you are using MTS or COM+, the default transaction isolation level, when the application server is managing transactions, can be serialized. This might cause excessive locking, and reduce concurrency. Your application must be prepared to deal with its transaction being aborted when the application server attempts to resolve a deadlock. To ease these problems, you can use the following statement:
exec sql set transaction isolation read committed end-exec
to set a less stringent isolation level. In this case, the statement must be the first statement to be executed after the CONNECT statement. Note that when SQL(AUTOCOMMIT) is not used, a commit or rollback is required immediately prior to executing a SET TRANSACTION ISOLATION statement.
When running in an application server environment, the user account in which your application executes might be different from the account used for development. ODBC data sources set up as user data sources might, therefore, not be available. You will probably find it more convenient to set up data sources as system data sources on deployment systems. When a file-based data source is used, the database files must be accessible by the account used by the application server. When accessing the database, particularly if integrated security is used (that is, when the DBMS uses the same account number as the operating system), the default schema might be different from that used during development. This might well be what is intended, if different schema names are used for development and deployment. Alternatively, it might mean that tables are no longer visible to the application. It must either use explicit owner qualification, or execute a database-specific statement, which will select the correct schema to be the default.
The following is an example of an transaction wrapper generated by the OCX Wizard modified to include the OpenESQL logic to handle the following scenarios using an MS SQL Server data source:
$set ooctrl(+p) sql(thread=isolate autocommit)
*>-----------------------------------------------------------
*> Class description
*>-----------------------------------------------------------
class-id. cblsqlwrapper
inherits from olebase.
object section.
class-control.
cblsqlwrapper is class "cblsqlwrapper"
*> OCWIZARD - start list of classes
objectcontext is class "objectcontext"
olebase is class "olebase"
oleSafeArray is class "olesafea"
oleVariant is class "olevar"
*> OCWIZARD - end list of classes
*>---USER-CODE. Add any additional class names below.
*>-----------------------------------------------------------
working-storage section. *> Definition of global data
*>-----------------------------------------------------------
*>-----------------------------------------------------------
class-object. *> Definition of class data and methods
*>-----------------------------------------------------------
object-storage section.
*> OCWIZARD - start standard class methods
*>-----------------------------------------------------------
*> Return details about the class.
*> If you have a type library, theClassId and theInterfaceId
*> here MUST match.
*> theProgId must match the registry entry for this class
*> (a zero length string implies using the class file name)
*> theClassId must match the CLSID stored in the registry.
*> theVersion is currently ignored (default 1 used).
*>-----------------------------------------------------------
method-id. queryClassInfo.
linkage section.
01 theProgId pic x(256).
01 theClassId pic x(39).
01 theInterfceId pic x(39).
01 theVersion pic x(4) comp-5.
01 theDescription pic x(256).
01 theThreadModel pic x(20).
procedure division using by reference theProgId
by reference theClassId
by reference theInterfceId
by reference theVersion
by reference theDescription
by reference theThreadModel.
move z"{3EADD92C-06C5-46F2-A2E0-7EB0794C14DF}"
to theClassId
move z"{5BF3F966-9932-4835-BFF6-2582CA2592AD}"
to theInterfceId
move z"Description for class cblsqlwrapper"
to theDescription
move z"Apartment" to theThreadModel
exit method.
end method queryClassInfo.
.
*>-----------------------------------------------------------
*> Return details about the type library - delete if unused.
*> theLocale is currently ignored (default 0 used).
*> theLibraryName is a null terminated string used for auto
*> registration, and supports the following values:
*> <no string> - Library is embedded in this binary
*> <number> - As above, with this resource number
*> <Path> - Library is at this (full path)
*> location
*>-----------------------------------------------------------
method-id. queryLibraryInfo.
linkage section.
01 theLibraryName pic x(512).
01 theMajorVersion pic x(4) comp-5.
01 theMinorVersion pic x(4) comp-5.
01 theLibraryId pic x(39).
01 theLocale pic x(4) comp-5.
procedure division using by reference theLibraryName
by reference theMajorVersion
by reference theMinorVersion
by reference theLibraryId
by reference theLocale.
move 1 to theMajorVersion
move 0 to theMinorVersion
move z"{24207F46-7136-4285-A660-4594F5EE7B87}"
to theLibraryId
exit method.
end method queryLibraryInfo.
*>-----------------------------------------------------------
*> OCWIZARD - end standard class methods
end class-object.
*>-----------------------------------------------------------
object. *> Definition of instance data and methods
*>-----------------------------------------------------------
object-storage section.
*> OCWIZARD - start standard instance methods
*> OCWIZARD - end standard instance methods
*>-----------------------------------------------------------
method-id. "RetrieveString".
working-storage section.
01 mfsqlmessagetext pic x(400).
01 ESQLAction pic x(100).
COPY DFHEIBLK.
COPY SQLCA.
*>...your transaction program name
01 transactionPgm PIC X(7) VALUE 'mytran'.
local-storage Section.
01 theContext object reference.
01 transactionStatusFlag pic 9.
88 transactionPassed value 1.
88 transactionFailed value 0.
*>---USER-CODE. Add any local storage items needed below.
01 ReturnValue pic x(4) comp-5.
88 IsNotInTransaction value 0.
01 transactionControlFlag pic 9.
88 TxnControlledByMTS value 0.
88 TxnNotControlledByMTS value 1.
linkage Section.
*>...Info passed to transaction
01 transaction-Info.
05 transaction-Info-RC pic 9.
05 transaction-Info-data pic x(100).
*>...Info returned from transaction via
01 transaction-Info-Returned pic x(100).
procedure division using by reference transaction-Info
returning transaction-Info-Returned.
*>...initialisation code
perform A-Initialise
perform B-ConnectToDB
if TxnNotControlledByMTS
perform C-SetAutoCommitOff
end-if
*>...set isolation level to override SQLServer default,
*>...serialize
perform D-ResetDefaultIsolationLevel
*>...set cursor type to overrde the OpenESQL default,
*>...dynamic+lock
perform E-ResetDefaultCursorType
*>...call the transaction
perform F-CallTransaction
*>...finalisation code - issue Commit/Rollback if not
*>...controlled by MTS/COM+
if TxnNotControlledByMTS
if transactionPassed
perform X-Commit
else
perform X-Rollback
end-if
end-if
perform Y-Disconnect
*>...Transaction Server - use setAbort if the method fails:
if theContext not = null
if transactionPassed
invoke theContext "setComplete"
else
invoke theContext "setAbort"
end-if
invoke theContext "finalize" returning theContext
end-if
exit method
.
A-Initialise.
*>...Transaction Server - get the context we are running in
invoke objectcontext "GetObjectContext"
returning theContext
*>...check if this component is enlisted in an MTS transation
if theContext = null
set TxnNotControlledByMTS to true
else
invoke theContext "IsInTransaction"
returning ReturnValue
if IsNotInTransaction
set TxnNotControlledByMTS to true
else
set TxnControlledByMTS to true
end-if
end-if
*>...initialise program variables
set transactionPassed to true
INITIALIZE DFHEIBLK
.
B-ConnectToDB.
*>...connect to data source
EXEC SQL
CONNECT TO 'SQLServer 2000' USER 'SA'
END-EXEC
if sqlcode zero
move z"connection failed " to ESQLAction
perform Z-ReportSQLErrorAndExit
end-if
.
C-SetAutoCommitOff.
EXEC SQL
SET AUTOCOMMIT OFF
END-EXEC
if sqlcode zero
move z"Set Autocommit Off failed " to ESQLAction
perform Z-ReportSQLErrorAndExit
end-if
perform X-Commit
.
D-ResetDefaultIsolationLevel.
*> the default isolation level for SQLServer is "Serialized",
*> so here we reset it to something more appropriate
EXEC SQL
SET TRANSACTION ISOLATION READ COMMITTED
END-EXEC
if sqlcode zero
move z"set transaction isoation failed "
to ESQLAction
perform Z-ReportSQLErrorAndExit
end-if
.
E-ResetDefaultCursorType.
*> the default cursor type for OpenESQL is dynamic + lock
*> the most efficient is a "client" or "firehose" cursor -
*> this is a cursor declared as forward + read only - doing
*> this here will set it as a default from now on. If
*> Forward causes a problem, change the concurrency to fast
*> forward (but note that it will then no longer be a client
*> cursor)
EXEC SQL
SET CONCURRENCY READ ONLY
END-EXEC
if sqlcode zero
move z"Set Concurrency Read Only" to ESQLAction
perform Z-ReportSQLErrorAndExit
end-if
EXEC SQL
SET SCROLLOPTION FORWARD
END-EXEC
if sqlcode zero
move z"Set Concurrancy Read Only" to ESQLAction
perform Z-ReportSQLErrorAndExit
end-if
.
F-CallTransaction.
*>...call the program to process the transaction
move 0 to transaction-Info-RC
call tranactionPgm using dfheiblk transaction-Info
*>...check if processing was okay
if transaction-Info-RC = 0
set transactionPassed to true
else
set transactionFailed to true
end-if
.
X-Commit.
EXEC SQL
COMMIT
END-EXEC
if sqlcode zero
move z"Commit failed " to ESQLAction
perform Z-ReportSQLErrorAndExit
end-if
.
X-Rollback.
EXEC SQL
ROLLBACK
END-EXEC
if sqlcode zero
move z"Rollback failed " to ESQLAction
perform Z-ReportSQLErrorAndExit
end-if
.
Y-Disconnect.
EXEC SQL
DISCONNECT CURRENT
END-EXEC
if sqlcode zero
move z"Disconnect failed " to ESQLAction
perform Z-ReportSQLErrorAndExit
end-if
.
Z-ReportSQLErrorAndExit.
move spaces to transaction-Info-Returned
string ESQLAction delimited by x"00"
"SQLSTATE = "
SQLSTATE
" "
mfsqlmessagetext
into transaction-Info-Returned
end-string
exit method
.
exit method.
end method "RetrieveString".
*>-----------------------------------------------------------
end object.
end class cblsqlwrapper.
See the Distributed Computing book for additional details on setting up MTS/COM+ or WebSphere transactions.
If you have an XML ODBC driver, you can set it up to access XML files just like any ODBC data source. You can then build SQL statements using the OpenESQL Assistant from the XML data source.
To help you in converting information to XML, OpenESQL has added the PERSIST statement which allows you to save information defined in a cursor SELECT statement as XML files. The syntax is:
PERSIST cursor_name TO xml_destination
where xml_destination may be an identifier, a host variable or a literal enclosed in single or double quotes. The cursor must also have SCROLLOPTION set to static. For example:
01 hv pic x(50).
procedure-division.
*> set whenever clause to handle sql errors
exec sql whenever sqlerror goto sql-error end-exec
exec sql whenever sqlwarning perform sql-warning end-exec
*> connect to data source
exec sql connect to "data source" end-exec
*> declare static cursor with column info you want to save to xml file
exec sql
declare c static cursor for
select * from emp
end-exec
*> open cursor
exec sql open c end-exec
*> save data to xml file using double quoted literal
exec sql
persist c to "c:\XML Files\xmltest1.xml"
end-exec
*> save data to xml file using single quoted literal
exec sql
persist c to 'c:\XML Files\xmltest2.xml'
end-exec
*> save data to xml file using a host variable
move "c:\XML Files\xmltest3.xml" to hv
exec sql
persist c to :hv
end-exec
*> close the cursor
exec sql close c end-exec
*> disconnect from datasource
exec sql disconnect current end-exec
goback.
Note: If you are using Data Direct Connect ODBC drivers, you need to use version 3.70 or later.
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:
Note: If an application uses Static SQL, it returns standard data types. If, however, an application uses Dynamic SQL, it returns the new data types listed above.
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.
Copyright © 2009 Micro Focus (IP) Ltd. All rights reserved.