Chapter 7: Embedded SQL Applications

This chapter lists differences in OpenESQL compared to earlier Embedded SQL systems.

COBSQL

COBSQL can be used with Embedded SQL preprocessors supplied by Oracle, Sybase and Informix in the same way as it is in other Micro Focus products. In addition, we provide OpenESQL, which provides Embedded SQL support using ODBC. Depending on application requirements, there may be benefits in migrating to OpenESQL.

This section lists differences between OpenESQL and other Embedded SQL implementations.

Summary of Changes

The PIC x VARYING syntax supported in Oracle's ESQL is not supported in OpenESQL.

Embedded SQL Toolkit

Embedded SQL Toolkit for Microsoft SQL Server cannot be used with Net Express. Instead you should use OpenESQL, the ODBC-based Embedded SQL support in Net Express.

This section lists differences in OpenESQL compared to Embedded SQL Toolkit for Microsoft SQL Server.

Summary of Changes

Compiler directives for SQL must use a new format. There is one directive SQL, which takes options equivalent to the separate directives in the Embedded SQL Toolkit. For example:

SQL(MSSQL) NOSQLDB NOSQLPASS NOSQLACCESS

becomes:

SQL(DBMAN=ODBC,TARGETDB=MSSQLSERVER,NOACCESS)

(NODB and NOPASS are assumed by default.)

For example:

SQL(MSSQL) SQLDB(server.database)
    SQLPASS(user.pwd) SQLINIT

becomes:

SQL(DBMAN=ODBC,TARGETDB=MSSQLSERVR, DB=datasourcename.db,
    PASS=user.pwd,INIT=PROT)

For example:

SQL(MSSQL)SQLPROT

becomes:

SQL(DBMAN=ODBC,TARGETDB=MSSQLSERVER, INIT=PROT)

Other differences are:

By default, OpenESQL manages transactions according to the ANSI SQL 92 standard. To start a transaction, you do not need a BEGIN TRAN statement. A new transaction starts immediately after a CONNECT, COMMIT or ROLLBACK statement. To provide compatability with the Toolkit, use either the ESQLVERSION directive or the AUTOCOMMIT directive:
- AUTOCOMMIT disables manual transaction mode.
- ESQLVERSION sets AUTOCOMMIT and, depending on the Toolkit version, sets additional compatability options.

The SQL Server "bit" data type is not supported.
BROWSE mode cursors are not supported. The cursor support available is of a higher level. Where possible, BROWSE mode syntax is mapped to the new cursor support.
You cannot EXECUTE IMMEDIATE on a stored procedure. Instead, use EXEC SQL EXEC stored_procedure END-EXEC
The text of some error messages has changed. Where possible the SQLCODE and SQLSTATE are the same as before.
Returning a SQL Server CHAR column into an Embedded SQL INTEGER field gives different results.
Cursors are closed on a COMMIT, instead of remaining OPEN. The new behavior is consistent with an ANSI implementation.
Doing a COMMIT on an empty transaction does not return an error status.
A COMPUTE statement is not allowed in a cursor declaration.
DATETIME fields are returned in a different format. You can insert in the same format as with the Embedded SQL Toolkit, but data is returned in YYYY-MM-DD format. This is the only format the ODBC driver supports.
Use of DISTINCT in a cursor declaration makes the cursor READ ONLY.
FETCHBUFFER of size 1 is the only size supported in this release.
Positioning a DELETE on a row that no longer exists gives a error status rather than SQLCODE = 100 ("End of results").
SCROLLOPTION MIXEDn is not supported.
The use of '??' as a marker for binary data is supported for backward compatibility. It should not be used in new applications.