| Dynamic SQL | OpenESQL Assistant | |
The OpenESQL preprocessor enables you to access a relational database via an ODBC driver by embedding SQL statements within your COBOL program.
Note: OpenESQL applications cannot be deployed on UNIX platforms.
To obtain ODBC support, you must:
At the beginning of the NetExpress installation, you should indicate that you wish to install a number of ODBC drivers by selecting ODBC drivers from the given list.
Note: Most of these drivers will only work if the Client Software for the specific database is present.
The Net Express ODBC support cannot work until you have set up a data source name (DSN) in the ODBC Manager. You can access the ODBC Manager via the Control Panel on Windows 95, Windows 98, or Windows NT desktop. Click on the appropriate 16bit ODBC or 32bit ODBC icon. Click on the System DSN tab.
The ODBC Data Source Administrator dialog box opens. All those drivers that are installed as a part of NetExpress appear with names that begin NetExpress .... In addition, NetExpress installs the Microsoft Access driver. This driver appears as NetExpress Microsoft Access.
For details of how to assign DSNs, click on the Help button in the ODBC Data Source Administrator dialog box.
Note: You may use these drivers to develop NetExpress applications but you may not distribute the drivers to your users. See List of Key Features in your Getting Started for more detailed licensing information.
When you compile your program, you must specify the SQL Compiler directive and its appropriate options such that the preprocessor converts the embedded SQL statements into function calls to the data source. The ODBC driver that your program calls will depend on the particular data source that you are accessing.
There are two ways of specifying options for the SQL Compiler directive:
Note: You cannot use a mixture of these methods - you must use one or the other.
The table below lists the SQL Compiler directive options.
| Option | Description |
| ESQL Preprocessor | Must be set to OpenESQL. |
| [NO]ANSI92ENTRY | If this is set, OpenESQL conforms to the SQL ANSI 92 entry level standard. |
| [NO]AUTOCOMMIT | 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]CHECK | 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. |
| CONNECTIONPOOL=[DRIVER | ENVIRONMENT | NONE] | Default is NONE. 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. |
| [NO]CURSORCASE | If ESQLVERSION is 2.0, CURSORCASE is implied. Default is NOCURSORCASE which means that cursor names are case insensitive. CURSORCASE means that they are case sensitive. Note that in previous versions of OpenESQL, cursor names have been case sensitive. |
| [NO]DB | The name of the data source to connect to. This option works in conjunction with the INIT and/or CHECK options. |
| [NO]DETECTDATE | Default is NODETECTDATE. If DETECTDATE is set, OpenESQL inspects
character host variables for ODBC escape sequences: {d<data>} - date {t<data>} - time {ts<data>} - timestamp 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]ESQLVERSION | Set OpenESQL syntax level. |
| [NO]INIT | 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. |
| [NO]NIST | If this is set, OpenESQL will conform to the NIST interpretation of the SQL ANSI 92 entry level standard. |
| ODBCTRACE= [ALWAYS | NEVER | USER] | Default is USER. ODBCTRACE=USER enables you to control ODBC tracing via the OBDC control panel 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 on the ODBC Control Panel. 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 the control panel. As ODBC trace files can contain sensitive information, use NEVER in production applications in secure environments. |
| [NO]PARAMARRAY | Default is PARAMARRAY. If PARAMARRAY is set, ODBC array binding is used, if it is supported by the ODBC driver, for all input parameters. |
| [NO]PASS | The login to use to connect to the data source. This option works in conjunction with the INIT and/or CHECK options. |
| [NO]RESULTARRAY | Default is RESULTARRAY. If RESULTARRAY is set, ODBC array binding is used, if it is supported by the ODBC driver, for all output parameters. |
| [NO]TARGETDB | If you are using either Microsoft SQL Server or Oracle 7.0 as your target database you can set this option in order to optimize performance. |
| THREAD=[SHARE | ISOLATE] | Default is SHARE. 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. |
When you install NetExpress two Data Source Names (DSNs) are created automatically. These are NetExpress Sample1 and NetExpress Sample2 and they point to the sample Access databases (demo.mdb and sample.mdb) that are installed as part of NetExpress in the \demo\smpldata directory.
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 typically used if the program is designed to access different data sources whose names are not known at compilation time or if the program is going to access multiple databases.
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, it 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 online help file. Look under "OpenESQL" in the help file index.
To build an OpenESQL application, you need to:
The copyfiles sqlca.cpy and sqlda.cpy are located in the source directory under your NetExpress base installation directory and can be included in your program in the normal way.
When you build your .exe, using NetExpress, all the necessary object files are linked in for you automatically.
Note: If you move your application to another system you will need to ensure that the files odbcrw32.dll and _sqlodbc.dll are available on that system.
A number of demonstration applications are supplied in the odbcesql directory which is located in the demo directory under your NetExpress base installation directory.
Before you can use any of the demonstration applications, you need to have installed at least one ODBC driver. A number of ODBC drivers are installed automatically with NetExpress including a Microsoft Access driver. In addition, two sample Access databases are supplied in the \demo\smpldata directory and two data source names which point to them are created automatically when you install NetExpress. You can run the demonstration applications against the sample database pointed to by the data source name NetExpress Sample2.
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.
Prompts for a data source, user name and password. Enter a data source name of "NetExpress Sample2", 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 "NetExpress Sample2" (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. Enter "NetExpress Sample2" and "admin" respectively. The test sequence is:
connect
drop test table
create test table
insert a row
commit
update the row
read and verify
rollback
read and verify
drop test table
disconnect
create test table
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 "NetExpress Sample2" 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.
Displays an SQL Data Sources dialog. Select "NetExpress Sample2" 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.
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.
The online help includes a table which shows the mappings used by OpenESQL when converting between SQL and COBOL data types. Look up Reference, Database Access, OpenESQL, Data Types in the online help contents.
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(26), for example:
01 mydate PIC x(26). ... 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:
{d 'yyyy-mm-dd'} - for date.
{t 'hh:mm:ss'} - for time.
{ts yyyy-mm-dd hh:mm:ss[.f...] - for timestamp.
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(26).
01 date-field2 pic x(26).
01 date-field3 pic x(26).
procedure division.
* Connect to the data source. This is one of the Sample
* datasources supplied with NetExpress
EXEC SQL
CONNECT TO 'NetExpress Sample1' 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 and TIME
* NOTE: Access uses DATETIME column for all three.
* Some databases will have dedicated column tyoes.
* 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.
The SQLCA data structure is included in the file sqlca.cpy in the source directory under your NetExpress 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.
The demonstration application, dynamic.app is located in the odbcesql directory which is in the demo directory under your base NetExpress installation directory. Open this project and select Step from the Animate 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.
OpenESQL supports two statements that are used with stored procedures: CALL, which provides generic support for ODBC stored procedure calls and EXECSP, which provides backwards compatibility with the Micro Focus Embedded SQL Tookit 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 maxiumun 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.
Copyright © 1998 Micro Focus Limited. All rights reserved.
This document and the proprietary marks and names
used herein are protected by international law.
| Dynamic SQL | OpenESQL Assistant | |