Issues with OpenESQL

Compiling

To compile OpenESQL applications that use a 64-bit Data Source Name (DSN) for 32-bit platforms, and for which you specify the CHECK or DB compiler directive options, you must explicitly create a 32-bit DSN of the same name as the 64-bit DSN. Do this using either the ODBC Data Source Administrator (32-bit) or the ADO.NET Connection Editor. The execution process continues to use the 64-bit DSN.

Linking

If you use CHARSET(EBCDIC) when compiling COBOL code that contains SQL host variables that are of the display numeric type defined as XXXXXX, you must link to the oesqlebc library to prevent link errors.

You can either set up the link from the IDE and recompile, or run the required commands from a command prompt after compiling.

From the IDE, before compiling or recompiling:

1. From the Solution Explorer, double-click the Properties entry listed in your project.
2. On the COBOL Link tab, click the browse button that corresponds to the Link with libs field .
3. Browse to the installDir\lib directory, where installDir is the path to your Visual COBOL installation, which by default is %ProgramFiles(x86)%\Micro Focus\Enterprise Developer.
4. Double-click oesqlebc.lib to select it and populate the field.
5. Click to save the changes.

From a command prompt, after compiling:

1. Start a Visual COBOL command prompt. If you need instructions, see To start a Visual COBOL command prompt.
2. At the command prompt, switch to the directory that contains your COBOL code.
3. Enter the following commands consecutively:

   set lib=installDir\lib;%lib%
   cbllink myapp.obj oesqlebc.lib

   Where installDir is the path to your Visual COBOL installation (by default, this is %ProgramFiles(x86)%\Micro Focus\Enterprise Developer, and myapp is the name of your COBOL object file.

Embedded SQL Statements

Any EXEC SQL statement that specifies a table or column name containing one or more special characters must have that table or column name enclosed in quotes to prevent a preprocessor error. A special character is an ASCII character with a value greater than 128 or a hexadecimal character with a value greater than x"80".

MySQL

• Supported for ODBC only.
• OpenESQL does not support scrollable cursors.
• OpenESQL uses the MySQL _rowid field to support the use of positioned updates and deletes in cursors. The _rowid field is available in MySQL only when a table is defined with a unique constraint or primary key on an integer column.
  Note: When a table is not defined with a unique constraint or primary key on an integer column, you must replace EXEC SQL UPDATE/DELETE WHERE CURRENT OF statements with corresponding searched EXEC SQL UPDATE/DELETE statements.
• When a row in MySQL is updated but the row contents do not change, MySQL might return an affected row count of 0 (zero) instead of 1 as is generally returned by other DBMSs. To work around this, consider modifying the MySQL ODBC driver Configuration Options as follows:
  1. Invoke the MySQL/Connector/ODBC Data Source Configuration.
  2. Click Cursors/Results.
  3. Check Return matched rows instead of affected rows
• The following restrictions apply when using MySQL 5.7 with the ODBC 5.3 driver:
  • Unqualified SELECT * statements must not have any other items in the projection list
  • ODBC escape sequences are not recognized
  • MySQL does not permit deferred constraint checking for an SQL statement, or for SQL statements within a transaction boundary
  • SQL TYPE BLOB host variables are restricted to 3M bytes in length

ODBC

When using Oracle's ODBC driver, array fetches and some scrolling fetches might produce inconsistent behavior if you change the array size between fetches. To overcome this, build your application with SQL(NORESULTARRAY). When this is set, OpenESQL performs single row fetches under the covers, bypassing this limitation.

OpenESQL ODBC Native Runtime

OpenESQL applications are optimized for performance by default, with OpenESQL now implicitly setting the BEHAVIOR compiler directive to OPTIMIZED; with previous products, the BEHAVIOR compiler directive was implicitly set to UNOPTIMIZED. This change can impact program behavior as follows:

• Programs that include the SET SCROLLOPTION or SET CONCURRENCY EXEC SQL statements fail to compile. For affected programs, do one of the following:
  • Before compiling, set the BEHAVIOR compiler directive to UNOPTIMIZED. While this is the simplest solution, we do not recommended it due to the possibility of degraded performance.
  • Remove all SET SCROLLOPTION and SET CONCURRENCY statements and, if necessary, modify the DECLARE CURSOR statement to include appropriate parameters to yield the same behavior. For example, to elicit the same behavior as the SET SCROLLOPTION statement, use the following DECLARE CURSOR statement:

    EXEC SQL DECLARE cursorname SCROLL LOCK CURSOR FOR…

    By default, cursors are forward and read only. If this is the desired behavior, you can safely remove the SET statements with no further action required. However, we recommend as a matter of good practice to always include FOR READ ONLY or FOR UPDATE in cursor declarations to ensure that the default behavior is always explicit. We recommended this solution.

• The OPTIMIZED setting does not make ambiguous cursors updateable. Therefore, applications that expect cursors to be updateable, according to the default ANSI standards for Embedded SQL, are affected. In this case, we recommend that you set the BEHAVIOR compiler directive option to ANSI.
• The OPTIMIZED setting does not assume scrollable cursors. Therefore, applications that expect cursors to be scrollable are affected. In these cases, we recommend that you modify your DECLARE CURSOR statements to compensate, as specified above.

PostgreSQL Recommendations

• PostgreSQL does not accept NULL date, time, or timestamp values from PIC X host variables. You must either use CAST(:hostvar as TIMESTAMP) in the SQL or use SQL TYPE TIMESTAMP in the application.
• PostgreSQL supports embedded NULL characters in binary columns only; it does not support embedded NULL characters in character columns.
• When using PostgreSQL with ODBC for MBDT SQLTUB, PostgreSQL maps bytea data type to LongVarBinary or BLOB unless the default behavior of the ODBC driver is changed.
• When using PostgreSQL with ODBC and a negative SQL code is returned to a calling application, PostgreSQL aborts transactions implicitly unless you change the default behavior of the ODBC driver. In addition, PostgreSQL does not support the ODBC API call SQLSetPos, which impedes the performance of updateable cursors.

  To work around these shortcomings, consider modifying the psqlODBC driver Configuration Options. To do this:

  1. Invoke the PostgreSQL ANSI or UNICODE ODBC Driver (psqlODBC) Setup dialog box.
  2. Click Datasource.
  3. On Advanced Options page 1, check Use Declare/Fetch.
  4. Click Page 2.
  5. On Advanced Options page 2, set the Level of rollback on errors option to Statement, and uncheck Updatable Cursors.
  6. Uncheck bytea as LOG to resolve the behavior described in the third bullet point above.