InstantSQL Configuration

Configuration

InstantSQL has several configuration options. Registry entries contain the option settings as string values under the registry key:

HKEY_LOCAL_MACHINE\Software\Liant Software Corporation\-
RM/COBOL\CurrentVersion\InstantSQL.

The file lisql.reg is provided to insert the registry entries for the configuration options of InstantSQL. This file may be edited to set the desired configuration settings and the entries updated in the registry by running regedit with lisql.reg as input or simply by double clicking the lisql.reg file from Windows Explorer. (Click on the button in the topic header to see the contents of the default lisql.reg file.)

Option value names and values are case insensitive. For true/false valued options, "True" (any case) is accepted as true and any other value is treated as false. Numeric values must be specified as a string of numeric decimal digits. String values other than true/false valued options must be spelled correctly; otherwise, InstantSQL will display a message box describing the unrecognized value and then terminate.

InstantSQL reads the option values from the registry during the initialization that takes place on the first InstantSQL statement execution in the run unit. Subsequent changes to the registry will not affect that run unit, but will affect later run units that use InstantSQL.

The InstantSQL configuration options are as follows:

AllowErrorMessageOnly. This option value determines if the SQL DESCRIBE ERROR statement may be used to return the error message text alone instead of the complete error description group. If set to "True", the SQL DESCRIBE ERROR statement allows the ErrorDescriptionGroup argument to be a different size than an InstantSQL error description group and, in this case, returns only the text of the error message in the argument data item. If set to "False", the ErrorDescriptionGroup argument of the SQL DESCRIBE ERROR statement must be the same size as an InstantSQL error description group; otherwise, the statement fails with an error condition. The default value is "False".

ConnectionCompletion. This option value establishes how connections are to be completed by the ODBC driver that supports the connection for an SQL CONNECT DATASOURCE statement. The default value is "Complete". There are four values supported:

Complete. The driver uses the data source name, user-name, and user-password values supplied in the SQL CONNECT DATASOURCE and SQL SET USER statements to connect to the data source. If this information is complete and correct, then the connection is made. If any of this information is missing or incorrect, then the driver takes the same actions as it does when "Prompt" is specified.

Complete_Required. The driver takes the same actions as when "Complete" is specified, except that the driver disables in the dialog box any controls not required to complete the connection.

Prompt. The driver uses the data source name, user-name, and user-password values supplied in the SQL CONNECT DATASOURCE and SQL SET USER statements as initial values and displays a dialog box to the user. The user may make any desired changes in the dialog box. When the user exits the dialog box, the driver connects to the data source using the specified values in the dialog box. In this case, the data source connection can be to a different data source than the data source named in the SQL CONNECT DATASOURCE statement.

NoPrompt. The driver uses the data source name, user-name, and user-password values supplied in the SQL CONNECT DATASOURCE and SQL SET USER statements to connect to the data source. If any of the information required to connect to the data source is missing or incorrect, the SQL CONNECT DATASOURCE statement fails with an error message.

DebugLogFileName. This option value specifies the file name for the debug log file. The default value is "InstantSQL.log". The debug log file is a binary file intended to aid Liant customer support in evaluating a customer problem with InstantSQL.

DebugLogLevel. This option value specifies the level of information to be written to the debug log file. It is a numeric value from 0 to 15, formed as the sum of values from the list below. The default value is "0" (no debug logging). The value "15" indicates full debug logging.

1. Log errors.

2. Log input argument values.

4. Log output argument values.

8. Log internal buffers after each statement completes.

Note When arguments are logged, that is, when DebugLogLevel includes 2 or 4 in its sum, password arguments specified in the SQL CONNECT DATASOURCE or SQL SET USER statement are replaced with "**********" in the log file. The log file does not contain any information about passwords, but does contain user names.

DisplayErrorMessageLevel. This option value specifies an error number for which all errors greater than or equal to this number will cause a message box to be displayed when the error occurs. The default value is "5000", which is higher than any InstantSQL error number, so no message boxes are produced (but the application can display errors using any desired mechanism and the SQL DESCRIBE ERROR statement to obtain the error information). The error message numbers referred to here are the InstantSQL return code values as defined in lisqldef.cpy and also described in the topic InstantSQL Error Handling.

Note Setting a low number for this configuration option is useful during development of an application that uses InstantSQL. Setting a value of "2501" will cause all ODBC errors to display the message box. Setting a value of 0 will cause InstantSQL to display all errors, including ODBC informational messages that do not result in a nonzero result value for the InstantSQL statement. (See the configuration value ReturnInfoErrorCode for details regarding ODBC informational messages and their effect on the statement return code.)

FetchRowTruncErrors. This option value specifies whether the SQL FETCH ROW statement is to return an error if a data item is truncated when converted from the data source to C data format. The architecture of InstantSQL is such that this truncation can only occur for LongVarChar and LongVarBinary data types. Regardless of the setting of this configuration option, the truncation will cause an error if the data is transferred to a COBOL data item either because it is bound to a COBOL data item with the SQL BIND COLUMN statement or because it is transferred with an SQL GET DATA statement. If set to "True", the truncation is detected during the execution of the SQL FETCH ROW statement even if the data is not transferred to the COBOL program. If set to "False", the truncation is ignored if the data is not transferred to the COBOL program. The default value is "False".

GetOutputParamsEachRow. This option value specifies whether output parameter values for stored procedures are to be transferred to the COBOL program upon each SQL FETCH ROW statement execution. If set to "True", the output parameter values are transferred to the COBOL program after each SQL FETCH ROW statement completes, even though these values may not be valid until all rows of the result set, if any, have been fetched. If set to "False", the output parameter values are transferred to the COBOL program only when SQL FETCH ROW sets sql-EndOfData to true. The default value is "False".

GetOutputParamsOnStart. This option value specifies whether output parameter values for stored procedures are to be transferred to the COBOL program upon each SQL START QUERY statement execution. If set to "True", the output parameter values are transferred to the COBOL program after each SQL START QUERY statement completes, even though these values may not be valid until all rows of the result set, if any, have been fetched. If set to "False", the output parameter values are transferred to the COBOL program only when SQL FETCH ROW sets sql-EndOfData to true. The default value is "False".

LongVarBinaryDataSize. This option value specifies the size of the largest LongVarBinary data item that is to be supported. Larger result values will be truncated when an SQL FETCH ROW statement is executed. This value can be overridden for a particular LongVarBinary result column by using the SQL BIND COLUMN statement to bind a larger COBOL data item to the column in the query result set. This value can be overridden for a particular LongVarBinary parameter by using the SQL BIND PARAMETER statement to bind a larger COBOL data item to the query parameter. The default value is "4096".

LongVarCharDataSize. This option value specifies the size of the largest LongVarChar data item that is to be supported. Larger result values will be truncated when an SQL FETCH ROW statement is executed. This value can be overridden for a particular LongVarChar result column by using the SQL BIND COLUMN statement to bind a larger COBOL data item to the column in the query result set. This value can be overridden for a particular LongVarChar parameter by using the SQL BIND PARAMETER statement to bind a larger COBOL data item to the query parameter. The default value is "4096".

Note This configuration value also affects those data types not directly supported by InstantSQL since such data types are effectively treated as being converted to LongVarChar. For more information on supported data types, see the topic SQL Data Types.

MatchNameCase. This option value specifies whether column names specified in the InstantSQL statements SQL BIND COLUMN, SQL DESCRIBE COLUMN and SQL GET DATA must match column names in the data source case sensitively. If set to "True", the column names must match in case as well as length and spelling. If set to "False", the column names must match only in length and spelling, ignoring the case of letters. The default value is "False".

OracleDbmsName. This option value specifies the leading portion of the DBMS name used by InstantSQL to detect an Oracle database during a connection. The DBMS name returned by ODBC is compared to this string only for the number of characters specified in the string. The comparison is done case insensitively. The default value is "Oracle".

Note The SQL DESCRIBE CONNECTION statement with an extended connection description group can be used to determine the DBMS name returned by ODBC for a particular connection. The DBMS name is returned in the sql-ConDBMSName data item. For example, an Oracle8 database driver might return "Oracle8" in this data item. Since the first six characters of this value match the default value of "Oracle", InstantSQL would assume an Oracle database for the connection.

OracleVarCharMinSize. This option value specifies the minimum size of a VarChar data item for Oracle databases (see VarCharMinSize below for information on configuring for non-Oracle databases). This configuration value only affects connections to Oracle databases as determined by using the OracleDbmsName configuration value described above. Most Oracle databases have a minimum size of 1 and treat zero length strings as equivalent to NULL (which has different semantics than an empty string). InstantSQL normally converts COBOL data items that contain only spaces to zero length strings. Setting this configuration value to "1" causes InstantSQL to convert a nonnumeric data item containing all spaces to a single space when converting to a VarChar for an Oracle database. If your version of Oracle supports zero length strings as different than NULL or you want COBOL items that contain all spaces to be treated as NULL on Oracle, then set the value to "0". The default value is "1".

QueryConcurrency. This option value specifies the default cursor concurrency to use when the SQL PREPARE QUERY statement does not specify the cursor concurrency argument. The default value is "Read_Only", which is the ODBC default. There are four values supported:

Read_Only. The cursor for the query is read-only. No updates are allowed.

Lock. The cursor uses the lowest level of locking sufficient to ensure that the row can be updated.

RowVer. The cursor uses optimistic concurrency control, comparing row versions, such as SQLBase ROWID or Sybase TIMESTAMP.

Values. The cursor uses optimistic concurrency control, comparing row values.

Setting a default cursor concurrency other than Read_Only should be done only when all prepared SQL statements are to be executed within transactions, that is, when in manual-commit mode because of an SQL START TRANSACTION statement. When in auto-commit mode, the other cursor concurrency values cause unnecessary temporary locking and therefore unnecessary waits on locks held by other connections.

Since some of these options are not supported by some data sources, an ODBC error may occur when a value other than Read_Only is specified.

QuerySensitivity. This option value specifies the cursor sensitivity to use for prepared statements. The default value is "Unspecified", which is the ODBC default. There are three values supported:

Unspecified. The cursor sensitivity for the query is unspecified. It is unspecified what the cursor type is and whether cursors for the query make visible the changes made to a result set by another cursor. Cursors for the query may make visible none, some, or all such changes.

Insensitive. All cursors on the statement handle show the result set without reflecting any changes made to it by any other cursor. Insensitive cursors are read-only. This corresponds to a static cursor, which has a concurrency that is read-only.

Sensitive. All cursors for the query make visible all changes made to a result set by another cursor.

Note Any value for this configuration option other than "Unspecified" requires ODBC 3.0 or later support and the data source must support the specified cursor sensitivity.

SQL Server 7 supports only a single active cursor when cursors have their default attributes. Changing QuerySensitivity to "Insensitive" for read-only applications or to "Sensitive" for read/write applications causes SQL Server 7 to use API server cursors, which support multiple active cursors. For more information on cursors in SQL Server, refer to the topic "API Server Cursors" in your SQL Server Books Online provided by Microsoft.

QueryTimeout. This option value specifies the default query timeout value to use when the SQL START QUERY statement does not specify the query timeout argument. The default value is 0 (no timeout).

Note While developing an application, it can be helpful to set a nonzero timeout value. This will avoid the problem of having to kill the task when an inadvertent deadlock occurs because of an application error. After the application is debugged and deployed, the default value of 0 (no timeout) is appropriate in many cases. However, when locks may be held for a long time by another application, setting a nonzero query timeout allows an application to regain control from waiting on the lock and choose what action to take. Some data sources, for example, Access 97, do not support setting a query timeout value; for those data sources, an ODBC error will occur if a timeout value other than 0 is specified.

ReturnInfoErrorCode. This option value specifies whether ODBC functions that are used by an InstantSQL statement and that return the status of success with information (SQL_SUCCESS_WITH_INFO) will cause the InstantSQL statement to set a return code value that indicates informational messages are present. If set to "True", the InstantSQL statement will return a return code value that sets sqe-ODBCInfo to true when an ODBC function call returns SQL_SUCCESS_WITH_INFO. If set to "False", the InstantSQL statement will ignore SQL_SUCCESS_WITH_INFO for purposes of setting the return code for the statement. Regardless of the setting of this option, the SQL DESCRIBE ERROR statement can be used to obtain the ODBC informational messages if there were any. The default value is "False".

TableType. This option value specifies the table types to be browsed when an SQL QUERY TABLES statement omits the TableType argument. It may be a string of up to 99 characters in length containing a comma separated list of quoted (single quotes) or unquoted table types to be returned. Valid table types are 'TABLE', 'VIEW', 'SYSTEM TABLE', 'GLOBAL TEMPORARY', 'LOCAL TEMPORARY', 'ALIAS', and 'SYNONYM'. The meanings of 'ALIAS' and 'SYNONYM' are driver-specific. If the data source does not support a specified table type, the SQL QUERY TABLES statement does not return any results for that type. The table types should always be specified in upper case. The default value is "TABLE".

TransactionIsolation. This option value establishes how transactions are to be isolated when the SQL START TRANSACTION statement omits the TxnIsolationLevel argument. The default value is "Repeatable_Read". There are four values supported:

Read_Uncommitted. The transaction isolation level is set to read uncommited for the connection. With this transaction isolation level, dirty reads, nonrepeatable reads and phantoms are possible. This isolation level is sometimes referred to as degree 1 (1º) isolation or browse isolation. This isolation level should not be used for transactions that update data.

Note Since Read_Uncommitted isolation defeats the main purpose of transaction support in InstantSQL, that is, defeats allowing consistent updates for concurrent transactions, it would usually be a mistake to configure this value. Setting this value is allowed only for completeness and for cases where a computer is used only for applications with browse access to the database.

Read_Committed. The transaction isolation level is set to read commited for the connection. With this transaction isolation level, dirty reads are not possible, but nonrepeatable reads and phantoms are possible. This isolation level is sometimes referred to as cursor stability and is approximately degree 2 (2º) isolation.

Repeatable_Read. The transaction isolation level is set to repeatable read for the connection. With this transaction isolation level, dirty reads and nonrepeatable reads are not possible, but phantoms are possible. This isolation level is sometimes referred to as degree 2.9999 (2.9999º) isolation (that is, 3º isolation without phantom protection).

Serializable. The transaction isolation level is set to serializable for the connection. With this transaction isolation level, dirty reads, nonrepeatable reads, and phantoms are not possible. This isolation level is sometimes referred to as degree 3 (3º) isolation or true isolation.

UserName. This option value specifies a default user name value to be used when the SQL CONNECT DATASOURCE statement omits the UserName argument and no SQL SET USER statement has been executed to set a user name. The default value is "" (an empty string).

VarCharMinSize. This option value specifies the minimum size of a VarChar data item for databases other than Oracle (see OracleVarCharMinSize above for information on configuring for Oracle databases). Normally this value should be 0, but some popular databases have a minimum size of 1. Databases with a minimum size of 1 often do so because a size of 0 is used for NULL and thus such databases cannot distinguish between an empty string and NULL. Since InstantSQL converts COBOL data items that contain only spaces to zero length strings, InstantSQL conflicts with such databases. Setting this configuration value to "1" causes InstantSQL to convert all spaces to a single space for compatibility with such databases other than Oracle. The default value is "0". This configuration value affects all connections made by InstantSQL other than connections to Oracle databases.

InstantSQL uses CodeBridge for data conversion. Therefore, conversion errors will cause a message box to be displayed if the runtime is in development mode, as explained in the CodeBridge documentation. Setting the environment variable RM_DEVELOPMENT_MODE to "Y" or "y" before starting the runtime will place the runtime in development mode. Development mode can also be turned on and off by the RM/COBOL application with the C$SetDevelopmentMode and C$ClearDevelopmentMode library subprograms. InstantSQL checks the setting of the development mode on each statement execution.