Values and Description

<RDBMStype>_SELECT_FOR_UPDATE

Where <RDBMStype> is set to one of SQLSERVE or POSTGRES.

Set to TRUE if the chosen ODBC driver supports the ‘SELECT FOR UPDATE’ statement.

Set to FALSE if the SQLSetPos statement is to be used to facilitate record locking.

If not set, the ODBC driver will be interrogated by the kernel to determine which locking method to use.

<RDBMStype>_STARTUP_ARGS

Where <RDBMStype> is set to SQLSERVE:

Allows DBMS specific startup options to be set. For example, SQLSERVE_STARTUP_ARGS='SET LOCK TIMEOUT 5000; SET LANGUAGE us_english'

CONNECTION
(Oracle Only)

Username/Password@Host - This is used instead of SQL_USERNAME and SQL_PASSWORD. If using Oracle SQLNet you can specify the host database which is to be used and must be setup in the TNSNAMES.ORA file on the machine.

FILETYPE

Set the file type based on the database. For example,

FILETYPE = SQLSERVE / POSTGRES / ORACLE.

SQLTIMEOUT

LOCKED_ROWS_RETURNED

SQLSERVE_TABLE_LOCKING

These three PROIV Environment Variables must be enabled for SQL Server to use the enhanced record lock mechanism.

The settings for SQLTIMEOUT are as follows:

SQLTIMEOUT      -1      - Report record lock immediately the SQL command is issued to the database

SQLTIMEOUT       0      - Block indefinitely until the database releases the record lock

SQLTIMEOUT     >0      - Block for the specified amount of seconds before raising a record lock message

@SQL_TIMEOUT defined in PROIV Logic has higher precedence over SQLTIMEOUT

To ensure the break key works LOCKED_ROWS_RETURNED=Y and SQLSERVE_TABLE_LOCKING =Y must be defined.  If SQLSERVE_TABLE_LOCKING is not specified, it is enabled by default from PROIV v6.1.57.0 release onwards.

Click here for more information about @SQL_TIMEOUT.

PRODB_CHARSET

A, Z, a, z, ZERO, and nine (7, 8 are also supported for legacy reasons.)

The variable determines the possible collating sequence for a database and sets the character padding needed to retrieve the highest possible key in a range. When a partial key is supplied, PROIV will concatenate the value set in the PRODB_CHARSET variable to the end of the partial key to ensure that the last key in the file is read and the correct records are returned. Provided the correct collating sequence is used on the database,  then either the default value or z results in the correct rows being returned from the database for ACSII encoding. For EBCDIC encoding the value of 0 should be used.

7, 8 are also supported for legacy reasons and use a 7-bit or 8-bit character set. This affects the value of the 'high' padding character used within the WHERE cause. A value of 7 will generate a 'high' padding character of 7F hexadecimal, and a value of 8 generates a value of FF hexadecimal. This needs to match up with the collating sequence and CodeSet specified for the database at the time of RDBMS installation.

SQL_ADD_UPDLOCK

When using SQL Server 2008, you must enable the SQL_ADD_UPDLOCK variable to maintain behaviour consistent with SQL Server 2000, and prevent unwanted problems with file locks. This is due to changes in the locking mechanisms in the database. This inserts WITH(UPDLOCK) into the SELECT FOR UPDATE queries.

SQL_CURSORS

Valid values are 10 – 128 or AUTO - This specifies the number of open cursors to be used per connection.  Generally the higher the number of cursors the better the performance but there is a resource overhead on the database using more cursors.  It is impractical to just use one cursor.  If not set, the default is 128 cursors.

SQL_DBNAME

RDMS database name

Specifies the name of the database to be used. When using an ODBC data source (SQLSERVER or POSTGRES) this is the data source name as defined in the ODBC driver manager. For example:

Using the Bourne shell:

$SQL_DBNAME = test
$export SQL_DBNAME

Using the 'C' shell:

$setenv SQL_DBNAME test.

This database will be used as the default for full function SQL. If multiple databases are being accessed, define the default database using this variable (together with the SQL_DBTYPE, SQL_USERNAME, AND SQL_PASSWORD) and use the CONNECTION string and FILETYPE in a logical database section for each database.

SQL_DBTYPE

ORACLE, POSTGRES, SQLSERVE

Mandatory. Specifies the database type (which must be in upper case) to connect to for Full Function mode SQL operations where there is no associated SQL table (via a PROIV File Definition of external type).  This environment variable MUST be set when genning or running a function, which will access an SQL database. On Windows server kernel, the FILETYPE is used which is located in the pro4v[n].ini file.

SQL_DISABLE_FILESTAMP_CHECK

If set to Y, the PROIV Virtual Machine ignores minor mismatches between the timestamp of the File Definition of a file in the Genfile entry of a function, and the timestamp of the File Definition itself. If not set and the SQL File Definition is modified, it displays a file mismatch error message.

SQL_NOSIG

Accepts any value. If this variable is set, when long delays occur on calling database services, the system alarm signals and 'WAITING FOR DATABASE RESPONSE' message are disabled.

SQL_PASSWORD

The password of the named user (if required). For PC operations, it must be included with the connection option in the initialisation file. On Windows this is specified in the connection string in the pro4v[n].ini file. See above. Mandatory for Oracle if a password has been set up.

SQL_TRANSACTION_ERROR

Y/N - If set to Y the database interface will give an error if a transaction boundary has been broken.

For example LS1 locks a record and LS2 does an explicit COMMIT, then the lock on the record in LS1 is released and the application transaction has been broken.

SQL_USERNAME

A valid user name to use when connecting to the database. For PC operations,it must be included with the connection option in the initialisation file. On Windows, it is specified in the connection string in the pro4v[n].ini file see above. (Mandatory)

LD_LIBRARY_PATH
(Unix only)

Set up to point to the runtime link libraries for the RDBMS. (Mandatory) The variable must be set before the PROIV Virtual Machine starts executing, therefore it is set in the kernel's operating system environment not in the PROIV configuration file.