ODBC: Configuration File Variables

A4GL-COMMIT-COUNT

The value of A4GL-COMMIT-COUNT indicates the conditions under which you want to issue an automatic COMMITWORK operation. Valid values are:

-1 no automatic commit
0 commit when no locks are held
n commit after n file operations (positive values only)

Examples

A4GL-COMMIT-COUNT 0

A commit will be issued when no locks are held, either because all files that had locked records have been closed, or because a COBOL COMMIT verb has been issued. This is the default value.

Note that some data sources lose the current row when a commit or rollback is executed. For these data sources, a setting of 0 for A4GL-COMMIT-COUNT can adversely affect performance.

A4GL-COMMIT-COUNT n

A commit will be issued after n operations. WRITE, REWRITE and DELETE count towards n; READ, START, and READ NEXT do not.

A4GL-COMMIT-COUNT -1

No commit will be issued by the Acu4GL interface. When COMMIT-COUNT is set to -1, the only way to perform a commit or rollback is to use the COBOL verbs COMMIT and ROLLBACK.

A4GL-COMMIT-COUNT is set to -1 internally when you use the transaction management facilities available in ACUCOBOL-GT.

A COMMIT will, however, be issued on exit from the runtime (for example, on execution of a STOP RUN).

4GL-IGNORED-SUFFIX-LIST

This variable is a space-delimited list of case-independent suffixes to ignore when opening ODBC tables.

By setting this variable (which must be set in the configuration file, and is not settable via SET ENVIRONMENT), the Acu4GL for ODBC interface will ignore the listed suffixes. There can be up to 10 ignored suffixes (more than that are not kept internally, so will be ignored.) The default value for this variable is "dat".

Example

4GL-IGNORED-SUFFIX-LIST dat idx txt

A-ODBC-COMMIT-ON-BEGIN

ODBC has no START TRANSACTION method. Everything since the last COMMIT or ROLLBACK is considered part of the current transaction.

To ensure that the previous transaction has been ended before a new one begins, set A-ODBC-COMMIT-ON-BEGIN to a non-zero value. This causes each COBOL START TRANSACTION to first issue a COMMIT to all applicable ODBC drivers in use. This ensures that the previous transaction has been ended before the new one starts.

If this variable is not set, or is set to 0, then a COBOL ROLLBACK may affect file I/O that occurred before the most recent COBOL START TRANSACTION. The default value is zero.

A-ODBC-DATASOURCE

Set A-ODBC-DATASOURCE to the exact name of the data source that you established in the Windows ODBC driver manager. You can set this variable in your COBOL configuration file if you will be using only one data source.

For example, if the data source you want to select is listed in the Windows ODBC driver manager window as:

MS Access Databases (Access Data (*.mdb))

you should add the following line to your configuration file:

A-ODBC-DATASOURCE MS Access Databases

If you do not know the data source name in advance, or if you intend to use more than one data source, you may set the data source name dynamically at runtime. In your COBOL program, you would add code similar to this prior to the statement that opens the file:

SET ENVIRONMENT "A-ODBC-DATASOURCE" TO
"data source name"

There is no default value for this variable. If you do not enter a name, ODBC will use the default ODBC driver.

A-ODBC-ERROR-MAP-FILE

Because there are so many drivers available for ODBC, you may find that data source error codes don't necessarily map well to COBOL error codes. To solve this problem, Acu4GL for ODBC allows you to create an error map file to map native database errors to COBOL errors. Create the file using the guidelines described on the following page, and then use the configuration file variable, A-ODBC-ERROR-MAP-FILE, to indicate the name and location of the file you created.

Example

If the file used for mapping is called MAP and this file is located in the directory C:\ODBC, you would specify:

A-ODBC-ERROR-MAP-FILE c:\ODBC\MAP

in the runtime configuration file. There is no default value for this variable.

Guidelines for Creating Map File

Although you can check your data source documentation for error code information, the easiest way to determine what error codes need to be mapped to more appropriate COBOL codes is through trial and error. As users use Acu4GL for ODBC, they may report receiving error messages that don't make sense based on their situation. Research these errors and try to determine a more appropriate COBOL error response.

When you create your error map file, use the following guidelines:

* Begin comment lines with #. Blank lines are also considered comments.

* Break the rest of the file into sections, with each section header consisting of all the information enclosed in brackets from the data source error function.

For example, if the data source returns this error:

OdbcOneInfo: State: S1000, Native Error: -346
[Visigenic][ODBC Informix 5 Driver][Informix]Could not update a row in the table.

make your section header:

[Visigenic][ODBC Informix 5 Driver][Informix]

* Include two fields in each line in the section: the internal error number, and an ACUCOBOL-GT mapping string.

Using the same example, if you wanted to map the Visigenic Informix driver error, -346 Could not update a row in the table, to the COBOL error, Not found, you would include this line in the section:

-346 E_NOT_FOUND

(E indicates errors in ACUCOBOL-GT source code.)

Other Visigenic Informix error maps would follow in the same section. If you use other drivers, you could use multiple sections.

The valid values for the second field are as follows:

E_SYS_ERR
E_PARAM_ERR
E_TOO_MANY_FILES
E_MODE_CLASH
E_REC_LOCKED
E_BROKEN
E_DUPLICATE
E_NOT_FOUND
E_UNDEF_RECORD
E_DISK_FULL
E_FILE_LOCKED
E_REC_CHANGED
E_MISMATCH
E_NO_MEMORY
E_MISSING_FILE
E_PERMISSION
E_NO_SUPPORT
E_NO_LOCKS

Through experience, Acucorp has discovered specific ways to better map errors for some drivers. For a list of these driver error mapping suggestions, look at the file odbcerrs on your installation disks.

A-ODBC-LOCK-METHOD

Use this variable to specify the locking method that the interface should use when accessing your data source. Possible values are:

none
SETPOS
SETSTMTOPTION
UPDATECOLUMN

Example:

A-ODBC-LOCK-METHOD UPDATECOLUMN

The default value is none, meaning that the interface will do nothing special to lock your data. This does not necessarily mean that your data won't be locked. Locking depends mainly on your data source and driver. No value simply tells Acu4GL for ODBC not to perform any locking functions.

NOTE:

Please note that we cannot guarantee that setting this variable will have any effect for a particular ODBC driver. Locking depends mainly on the data source and the driver. You can use this variable to attempt to induce row locking. If none of the settings causes any rows to be locked, or if the machine hangs, please report this behavior to Acucorp Technical Support.

SETPOS

Specify SETPOS as your lock method to tell Acu4GL for ODBC to perform the following locking sequence:

1. When setting up a statement handle for accessing the ODBC data source, the interface calls a function called SQLSetScrollOptions, with values (SQL-CONCUR-LOCK, SQL-SCROLL-KEYSET-DRIVER, 1).

2. When fetching rows from the data source, the interface calls SQLExtendedFetch instead of SQLFetch.

3. Finally, it calls SQLSetPos, with the values (1, SQL-POSITION, SQL-LOCK-EXCLUSIVE).

If any of these functions do not exist in the ODBC driver, then A-ODBC-LOCK-METHOD reverts to none.

NOTE:

Even if these functions do exist, there is no guarantee that any rows will be locked using this sequence of calls. Contact your driver vendor to determine whether or not this sequence will be effective for your data source.

SETSTMTOPTION

Specify SETSTMTOPTION as your lock method for ODBC version 2.0 (and later) drivers. This option is similar to SETPOS, except ODBC version 2 defines a new function called SQLSetStmtOption which performs the task of SQLSetScrollOptions, but has more functionality.

In particular, when using this method of locking, the interface will call SQLSetStmtOption a number of times, with values (SQL-CONCURRENCY, SQL-CONCUR-LOCK), (SQL-CURSOR-TYPES, SQL-CURSOR-KEYSET-DRIVEN), and (SQL_KEYSET-SIZE, 1). Again, if the function does not exist, then A-ODBC-LOCK-METHOD reverts to none.

UPDATECOLUMN

Specifying UPDATECOLUMN as your lock method performs an entirely different type of locking. Instead of trying to lock a row while reading it, this method creates a new statement handle for the data source. Then, after fetching the data from the data source, it resubmits an SQL query to select the same row (based on the primary key) and adds an UPDATE clause. Last, it fetches the data from the data source. Because this method has the most overhead, it is not recommended for slow drivers such as Microsoft Access, but it is much more likely to succeed in locking records.

A-ODBC-LOGIN

A-ODBC-LOGIN indicates the user name under which you want to connect to the database system. Not all data sources require a user login name. Those that do may have case requirements. Check your data source documentation to determine if login is case sensitive.

Example

To connect to the database with the user name MYNAME, you could specify:

A-ODBC-LOGIN MYNAME

or

A-ODBC-LOGIN myname

in the runtime configuration file. There is no default value for this variable. If no login is specified, none will be used.

See also

A-ODBC-PASSWD runtime configuration file variable.

A-ODBC-PASSWD

The variable A-ODBC-PASSWD should be set to the password assigned to the database account associated with the user name specified by A-ODBC-LOGIN. Not all data sources require a password. Those that do may have case requirements. Check your data source documentation to determine if password is case sensitive.

Examples

If the account with the user name in A-ODBC-LOGIN has the associated password CW021535, you would specify:

A-ODBC-PASSWD CW021535

in the runtime configuration file.

For better security, you can accept a password from the user during program execution; set the A-ODBC-PASSWD variable based on the response:

ACCEPT RESPONSE NO-ECHO.
SET ENVIRONMENT "A-ODBC-PASSWD" TO RESPONSE.

NOTE:

If the user has been set up without a password, this variable need not be set. There is no default value for this variable. If no password is specified, none will be used.

A-ODBC-PRINT-LOG

This variable is used for debugging only, and is best used under the direction of an Acucorp Technical Support representative.

A-ODBC-QUOTE-IDENTIFIERS

This configuration variable that tells the interface whether to test for the IDENTIFIER-QUOTE-CHAR, and to use it if it is found. The variable is A-ODBC-QUOTE-IDENTIFIERS. If this variable is non-zero, and if the driver returns information about the IDENTIFIER-QUOTE-CHAR, then all identifiers sent in SQL to the driver will be quoted. This is useful for using MS Excel, for example, if the spreadsheet has column names with quotes or other strange characters. Notice that some drivers mistakenly report that they can handle quoted identifiers, which is why this variable is needed. If you set this variable to a non-zero value, and start getting errors about table not found or column not found, then this variable should not be used when communicating with that driver.

NOTE:

We have found that with Excel, this variable is useful. With Oracle, this variable is not recommended.

A-ODBC-UNSIGNED-TINYINT

Some ODBC drivers may assign the TINYINT data type, a signed 1-byte variable that can store values from -128 to 127, to an internal unsigned type that can store values from 0 to 255. To determine if your ODBC driver does this, set a PIC s99 variable to a negative value and then write a record. If a value out of range message is returned, then you must set A-ODBC-UNSIGNED-TINYINT to a nonzero value. As a result, PIC s99 variables are stored in a larger type (usually INT) that allows negative values. The default value for this configuration variable is 0.

A-ODBC-USE-CHAR-FOR-BINARY

Some data sources have restrictions on the number of binary large objects (BLOBs) that can be placed into a single table. If your data source has such restrictions, specify A-ODBC-USE-CHAR-FOR-BINARY in the configuration file. The possible values for this variable are:

0 use BINARY type
1 use CHAR type

A non-zero value for A-ODBC-USE-CHAR-FOR-BINARY lets you store data that uses the BINARY directive as hexadecimal encoded CHAR types. This will allow you to work around your data source restriction. The default value is zero.

Example

A-ODBC-USE-CHAR-FOR-BINARY 1

A-ODBC-USE-SPACE-IN-DATES

Some ODBC drivers require spaces when sending dates or timestamps to the data source. For data sources that require spaces between the d and the quote ('), set the configuration variable A_ODBC_USE_SPACE_IN_DATES.

For example:

Set MYDATE={d '1999-3-20'}
(note that there is a space between d and ')

Setting the A_ODBC_USE_SPACE_IN_DATES configuration variable to 1 causes the Acu4GL for ODBC interface to use syntax as above, instead of:

Set MYDATE={d'1999-3-20'}
(note that there is not a space between d and ')

Keep in mind that it is possible that all data sources may allow a space between the and the ', and that it is always safe to set this variable.

Example

A_ODBC_USE_SPACE_IN_DATES 1

A4GL-MAX-DATE

A4GL-MAX-DATE sets the maximum allowable date for your data source. Because there are so many different databases and drivers available, and no standards about what the maximum allowable date for a Date-type variable, this configuration variable can be used to check for illegal dates. Set the variable to an 8-digit number which signifies the maximum allowable date, in the format yyyymmdd.

The default date for A4GL-MAX-DATE is 20991231.

A4GL-MIN-DATE

A4GL-MIN-DATE sets the minimum allowable date for your data source. Because there are so many different databases and drivers available, and no standards about what the minimum allowable date for a Date-type variable, this configuration variable can be used to check for illegal dates. Set the variable to an 8-digit number which signifies the minimum allowable date, in the format yyyymmdd.

The default date for A4GL-MIN-DATE is 19000101.