Using Natural with Entire Access

#### Search Results

Search

# Using Natural with Entire Access

This section covers the following topics:

- Generating Natural DDMs

- Setting Natural Profile Parameters

- Natural DML Statements

- Natural SQL Statements

- Flexible SQL

- RDBMS-Specific Requirements and Restrictions

- Data-Type Conversion

- Date/Time Conversion

- Obtaining Diagnostic Information

Entire Access supports Natural SQL statements and most Natural DML statements. Natural DML and SQL statements can be used in the same Natural program. At compilation, if a DML statement references a DDM for a data source defined in `NATCONF.CFG` with DBMS type "SQL", Natural translates the DML statement into an SQL statement.

Natural converts DML and SQL statements into calls to Entire Access. Entire Access converts the requests to the data formats and SQL dialect required by the target RDBMS and passes the requests to the database driver.

For more information about Natural DML and SQL statements, see the Natural documentation.

## Generating Natural DDMs

A Natural program can access a table or view in a relational database only if the structure has been defined to Natural. This is accomplished by creating a Natural data definition module (DDM) from the table or view.

To generate DDMs from SQL tables or views, you use the Natural DDM editor.

For details about generating DDMs and the DDM editor, see the Natural documentation.

## Setting Natural Profile Parameters

### ETEOP Parameter

This parameter can be set only by Natural administrators.

The Natural profile parameter `ETEOP` controls transaction processing during a Natural session. It is required, for example, if a single logical transaction is to span two or more Natural programs. In this case, Natural must not issue an `END TRANSACTION` command (that is, not "commit") at the termination of a Natural program.

If the `ETEOP` parameter is set to:

- `ON` - Natural issues an `END TRANSACTION` statement (that is, automatically "commits") at the end of a Natural program if the Natural session is not at ET status;

- `OFF` (the default) - Natural does not issue an `END TRANSACTION` command (that is, does not "commit") at the end of a Natural program. This setting thus enables a single logical transaction to span more than one Natural program.

Note
The `ETEOP` parameter applies to Natural Version 6.1 and above. With previous Natural versions, the Natural profile parameter `OPRB` has to be used instead of `ETEOP` (`ETEOP=ON` corresponds to`OPRB=OFF`, `ETEOP=OFF` corresponds to `ORPB=NOOPEN`).

## Natural DML Statements

The following table shows how Natural translates DML statements into SQL statements:

DML Statement | SQL Statement
--- | ---
`BACKOUT TRANSACTION` | `ROLLBACK`
`DELETE` | `DELETE WHERE CURRENT OF cursor-name`
`END TRANSACTION` | `COMMIT`
`EQUAL ... OR` | `IN (...)`
`EQUAL ... THRU ...` | `BETWEEN ... AND ...`
`FIND ALL` | `SELECT`
`FIND NUMBER` | `SELECT COUNT (*)`
`HISTOGRAM` | `SELECT COUNT (*)`
`READ LOGICAL` | `SELECT ... ORDER BY`
`READ PHYSICAL` | `SELECT ... ORDER BY`
`SORTED BY ... [DESCENDING]` | `ORDER BY ... [DESCENDING]`
`STORE` | `INSERT`
`UPDATE` | `UPDATE WHERE CURRENT OF cursor-name`
`WITH` | `WHERE`

Note
Boolean and relational operators function the same way in DML and SQL statements.

Entire Access does not support the following DML statements and options:

DML Statement
`CIPHER`
`COUPLED`
`FIND FIRST`, `FIND UNIQUE`, `FIND ... RETAIN AS`
`GET`, `GET SAME`, `GET TRANSACTION DATA`, `GET RECORD`
`PASSWORD`
`READ BY ISN`
`STORE USING/GIVING NUMBER`

The following DML statements are covered in detail below:

- BACKOUT TRANSACTION

- DELETE

- END TRANSACTION

- FIND

- HISTOGRAM

- READ

- STORE

- UPDATE

### BACKOUT TRANSACTION

Natural translates a `BACKOUT TRANSACTION` statement into an SQL `ROLLBACK` command. This statement reverses all database modifications made after the completion of the last recovery unit. A recovery unit may start at the beginning of a session or after the last `END TRANSACTION (COMMIT)` or `BACKOUT TRANSACTION (ROLLBACK)` statement.

Because all cursors are closed when a logical unit of work ends, do not place a `BACKOUT TRANSACTION` statement within a database loop; place it outside the loop or after the outermost loop of nested loops.

### DELETE

The `DELETE` statement deletes a row from a database table that has been read with a preceding `FIND`, `READ`, or `SELECT` statement. It corresponds to the SQL statement `DELETE WHERE CURRENT OF cursor-name`, which means that only the last row that was read can be deleted.

#### Example:

    FIND EMPLOYEES WITH NAME = 'SMITH'
         AND FIRST_NAME = 'ROGER'
    DELETE

Natural translates the Natural statements above into the following SQL statements and assigns a cursor name (for example, CURSOR1). The `SELECT` statement and the `DELETE` statement refer to the same cursor.

    SELECT FROM EMPLOYEES
       WHERE NAME = 'SMITH' AND FIRST_NAME = 'ROGER'
    DELETE FROM EMPLOYEES
       WHERE CURRENT OF CURSOR1

Natural translates a `DELETE` statement into an SQL `DELETE` statement the way it translates a `FIND` statement into an SQL `SELECT` statement. For details, see the `FIND` statement description below.

You cannot delete a row read with a `FIND SORTED BY` or `READ LOGICAL` statement. For an explanation, see the `FIND` and `READ` statement descriptions below.

### END TRANSACTION

Natural translates an `END TRANSACTION` statement into an SQL `COMMIT` command. The `END TRANSACTION` statement indicates the end of a logical transaction, commits all modifications to the database, and releases data locked during the transaction.

Because all cursors are closed when a logical unit of work ends, do not place an `END TRANSACTION` statement within a database loop; place it outside the loop or after the outermost loop of nested loops.

The `END TRANSACTION` statement cannot be used to store transaction (ET) data when used with Entire Access.

Note
Entire Access does not issue a `COMMIT` automatically when the Natural program terminates.

### FIND

Natural translates a `FIND` statement into an SQL `SELECT` statement. The `SELECT` statement is executed by an `OPEN CURSOR` command followed by a `FETCH` command. The `FETCH` command is executed repeatedly until all records have been read or the program exits the `FIND` processing loop. A `CLOSE CURSOR` command ends the `SELECT` processing.

#### Example:

Natural statements:

    FIND EMPLOYEES WITH NAME = 'BLACKMORE'
       AND AGE EQ 20 THRU 40
    OBTAIN PERSONNEL_ID NAME AGE

Equivalent SQL statement:

    SELECT PERSONNEL_ID, NAME, AGE
      FROM EMPLOYEES
        WHERE NAME = 'BLACKMORE'
          AND AGE BETWEEN 20 AND 40

You can use any table column (field) designated as a descriptor to construct search criteria.

Natural translates the `WITH` clause of a `FIND` statement into the `WHERE` clause of an SQL `SELECT` statement. Natural evaluates the `WHERE` clause of the `FIND` statement after the rows have been selected using the `WITH` clause. View fields may be used in a `WITH` clause only if they are designated as descriptors.

Natural translates a `FIND NUMBER` statement into an SQL `SELECT` statement containing a `COUNT(*)` clause. When you want to determine whether a record exists for a specific search condition, the `FIND NUMBER` statement provides better performance than the `IF NO RECORDS FOUND` clause.

A row read with a `FIND` statement containing a `SORTED BY` clause cannot be updated or deleted. Natural translates the `SORTED BY` clause of a `FIND` statement into the `ORDER BY` clause of an SQL `SELECT` statement, which produces a read-only result table.

### HISTOGRAM

Natural translates the `HISTOGRAM` statement into an SQL `SELECT` statement. The `HISTOGRAM` statement returns the number of rows in a table that have the same value in a specific column. The number of rows is returned in the Natural system variable `*NUMBER`.

#### Example:

Natural statements:

    HISTOGRAM EMPLOYEES FOR AGE
    OBTAIN AGE

Equivalent SQL statements:

    SELECT AGE, COUNT(*) FROM EMPLOYEES
      GROUP BY AGE
      ORDER BY AGE

### READ

Natural translates a `READ` statement into an SQL `SELECT` statement. Both `READ PHYSICAL` and `READ LOGICAL` statements can be used.

A row read with a `READ LOGICAL` statement (Example 1) cannot be updated or deleted. Natural translates a `READ LOGICAL` statement into the `ORDER BY` clause of an SQL `SELECT` statement, which produces a read-only result table.

A `READ PHYSICAL` statement (Example 2) can be updated or deleted. Natural translates it into a SQL `SELECT` statement without an `ORDER BY` clause.

#### Example 1:

Natural statements:

    READ PERSONNEL BY NAME
    OBTAIN NAME FIRSTNAME DATEOFBIRTH

Equivalent SQL statement:

    SELECT NAME, FIRSTNAME, DATEOFBIRTH FROM PERSONNEL
      WHERE NAME >= ' '
        ORDER BY NAME

#### Example 2:

Natural statements:

    READ PERSONNEL PHYSICAL
    OBTAIN NAME

Equivalent SQL statement:

    SELECT NAME FROM PERSONNEL

When a `READ` statement contains a `WHERE` clause, Natural evaluates the `WHERE` clause after the rows have been selected according to the search criterion.

### STORE

The `STORE` statement adds a row to a database table. It corresponds to the SQL `INSERT` statement.

#### Example:

Natural statement:

    STORE RECORD IN EMPLOYEES
      WITH PERSONNEL_ID = '2112'
           NAME         = 'LIFESON'
           FIRST_NAME   = 'ALEX'

Equivalent SQL statement:

    INSERT INTO EMPLOYEES (PERSONNEL_ID, NAME, FIRST_NAME)
      VALUES ('2112', 'LIFESON', 'ALEX')

### UPDATE

The DML `UPDATE` statement updates a table row that has been read with a preceding `FIND`, `READ`, or `SELECT` statement. Natural translates the DML `UPDATE` statement into the SQL statement `UPDATE WHERE CURRENT OF cursor-name` (a positioned `UPDATE` statement), which means that only the last row that was read can be updated. In the case of nested loops, the last row in each nested loop can be updated.

#### UPDATE with FIND/READ

When a DML `UPDATE` statement is used after a Natural `FIND` statement, Natural translates the `FIND` statement into an SQL `SELECT` statement with a `FOR UPDATE OF` clause, and translates the DML `UPDATE` statement into an `UPDATE WHERE CURRENT OF cursor-name` statement.

#### Example:

    FIND EMPLOYEES WITH SALARY < 5000
      ASSIGN SALARY = 6000
      UPDATE

Natural translates the Natural statements above into the following SQL statements and assigns a cursor name (for example, CURSOR1). The `SELECT` and `UPDATE` statements refer to the same cursor.

    SELECT SALARY FROM EMPLOYEES WHERE SALARY < 5000
      FOR UPDATE OF SALARY
    UPDATE EMPLOYEES SET SALARY = 6000
      WHERE CURRENT OF CURSOR1

You cannot update a row read with a `FIND SORTED BY` or `READ LOGICAL` statement. For an explanation, see the `FIND` and `READ` statement descriptions above.

An `END TRANSACTION` or `BACKOUT TRANSACTION` statement releases data locked by an `UPDATE` statement.

#### UPDATE with SELECT

The DML `UPDATE` statement can be used after a `SELECT` statement only in the following case:

    SELECT *
      INTO VIEW view-name

Natural rejects any other form of the `SELECT` statement used with the DML `UPDATE` statement. Natural translates the DML `UPDATE` statement into a non-cursor or "searched" SQL `UPDATE` statement, which means than only an entire Natural view can be updated; individual columns cannot be updated.

In addition, the DML `UPDATE` statement can be used after a `SELECT` statement only in Natural structured mode, which has the following syntax:

`UPDATE``[RECORD] [IN] [STATEMENT] [(`r`)]`

#### Example:

    DEFINE DATA LOCAL
    01 PERS VIEW OF SQL-PERSONNEL
      02 NAME
      02 AGE
    END-DEFINE

    SELECT *
      INTO VIEW PERS
      FROM SQL-PERSONNEL
      WHERE NAME LIKE 'S%'
      OBTAIN NAME

        IF NAME = 'SMITH'
          ADD 1 TO AGE
        UPDATE
        END-IF

    END-SELECT

In other respects, the DML `UPDATE` statement works with the `SELECT` statement the way it works with the Natural `FIND` statement (see the section UPDATE with FIND/READ above).

## Natural SQL Statements

The SQL statements available within the Natural programming language comprise two different sets of statements: the common set and the extended set.

The common set can be handled by each SQL-eligible database system supported by Natural. It basically corresponds to the standard SQL syntax definitions.

This section describes considerations and restrictions when using the common set of Natural SQL statements with Entire Access.

For a detailed description of the common set of Natural SQL statements, see the Natural documentation. For information about the extended set, see the documentation of the Natural interface for your database system.

The extended set is supported by Natural on mainframe computers only.

The following SQL statements are covered in detail below:

- DELETE

- INSERT

- PROCESS SQL

- SELECT

- SELECT SINGLE

- UPDATE

### DELETE

The Natural SQL `DELETE` statement deletes rows in a table without using a cursor.

Whereas Natural translates the DML `DELETE` statement into a positioned `DELETE` statement (that is, an SQL `DELETE WHERE CURRENT OF cursor-name` statement), the Natural SQL `DELETE` statement is a non-cursor or searched `DELETE` statement. A searched `DELETE` statement is a stand-alone statement unrelated to any `SELECT` statement.

### INSERT

The `INSERT` statement adds rows to a table; it corresponds to the Natural `STORE` statement.

### PROCESS SQL

The `PROCESS SQL` statement issues SQL statements in a "statement-string" to the database identified by a ddm-name.

It is not possible to run database loops using the `PROCESS SQL` statement.

See the Natural documentation for more information.

#### Parameters

Natural Version 4.1 supports the `INDICATOR` and `LINDICATOR` clauses. As an alternative, the statement-string may include parameters. The syntax item parameter is syntactically defined as follows:

| `:U` |  | :host-variable
`:G`

A host-variable is a Natural program variable referenced in an SQL statement.

#### SET SQLOPTION option = value

With Entire Access, you can also specify `SET SQLOPTION option=value` as statement-string. This can be used to specify various options for accessing SQL databases. The options apply only to the database referenced by the `PROCESS SQL` statement.

Supported `options` are:

- `DATEFORMAT`

- `EXIO_RESULT_SETS` (for Db2 only)

- `RAW_DATETIME`

- `DYNAMIC_CURSOR`

#### DATEFORMAT

This option specifies the format used to retrieve SQL Date and Datetime information into Natural fields of type A. The option is obsolete if Natural fields of type D or T are used. A subset of the Natural date and time edit masks can be used:

`YYYY` | Year (4 digits)
`YY` | Year (2 digits)
`MM` | Month
`DD` | Day
`HH` | Hour
`II` | Minute
`SS` | Second

If the date format contains blanks, it must be enclosed in apostrophes.

#### Examples:

To use ISO date format, specify

    PROCESS SQL sql-ddm << SET SQLOPTION DATEFORMAT = YYYY-MM-DD >>

To obtain date and time components in ISO format, specify

    PROCESS SQL sql-ddm << SET SQLOPTION DATEFORMAT = 'YYYY-MM-DD HH:II:SS' >>

The `DATEFORMAT` is evaluated only if data are retrieved from the database. If data are passed to the database, the conversion is done by the database system. Therefore, the format specified with `DATEFORMAT` should be a valid date format of the underlying database.

If no `DATEFORMAT` is specified for Natural fields,

- the default date format `DD-MON-YY` is used (where `MON` is a 3-letter abbreviation of the English month name) and

- the following default datetime formats are used:

  Db2 | `YYYY-MM-DD-HH.II.SS`
  MariaDB | `YYYY-MM-DD HH:II:SS`
  MySQL | `YYYY-MM-DD HH:II:SS`
  ODBC | `YYYY-MM-DD HH:II:SS`
  ORACLE | `YYYYMMDDHHIISS`
  PostgreSQL | `YYYY-MM-DD HH:II:SS`
  other | `DD-MON-YY`

#### EXIO_RESULT_SETS

This option is valid for Db2 databases only.

Db2 allows the definition of stored procedures that have output parameters specified and that return a result set at the same time. If such a stored procedure is to be called via the `CALLDBPROC` statement, then this functionality has to be enabled by setting `EXIO_RESULT_SETS` to `YES`.

#### Examples:

To enable output parameters and result sets at the same time in stored procedure calls, issue the following statement:

    PROCESS SQL sql-ddm << SET SQLOPTION EXIO_RESULT_SETS = YES >>

To disable output parameters and result sets at the same time in stored procedure calls, issue the following statement (this is the default):

    PROCESS SQL sql-ddm << SET SQLOPTION EXIO_RESULT_SETS = NO >>

Note
Only one result set can be returned. Do not use stored procedures that return more than one result set.

#### RAW_DATETIME

This option is available as of Natural Version 8.3.4. It is used to enable/disable the date/time conversion in Entire Access and Natural when date and time values are read from a database. It should only be used if date/time values are read into alphanumeric fields, but not if they are read into date/time fields.

If this option is set to `NO` (this is the default), Entire Access converts date and time values according to the given date/time format masks. If it is set to `YES`, date/time values are provided in the format which is enabled in the database.

#### Examples:

To enable date/time conversion, specify:

    PROCESS SQL sql-ddm << SET SQLOPTION RAW_DATETIME = NO >>

To disable date/time conversion, specify:

    PROCESS SQL sql-ddm << SET SQLOPTION RAW_DATETIME = YES >>

#### DYNAMIC_CURSOR

This option (available as of Natural version 9.1.4) is used to switch between a dynamic and a non-dynamic cursor type only for SQL Server databases. By default, this switch is set to YES so that only dynamic cursors are used for retrieving data. Only in situations where you experience a big loss of performance for certain SELECT statements you may try to switch the cursor to non-dynamic before that statement and back to dynamic after. It is not recommended to run the complete application with cursors switched to non-dynamic.

To enable dynamic cursors, specify:

    PROCESS SQL sql-ddm << SET SQLOPTION DYNAMIC_CURSOR = YES >>

To disable dynamic cursors, specify:

    PROCESS SQL sql-ddm << SET SQLOPTION DYNAMIC_CURSOR = NO >>

#### SQLDISCONNECT

With Entire Access, you can also specify `SQLDISCONNECT` as the statement-string. In combination with the `SQLCONNECT` statement (see below), this statement can be used to access different databases by one application within the same session, by simply connecting and disconnecting as required.

A successfully performed `SQLDISCONNECT` statement clears the information previously provided by the `SQLCONNECT` statement; that is, it disconnects your application from the currently connected SQL database determined by the DBID of the DDM used in the `PROCESS SQL` statement. If no connection is established, the `SQLDISCONNECT` statement is ignored. It will fail if a transaction is open.

Note
If Natural reports an error in the `SQLDISCONNECT` statement, the connection status does not change. If the database reports an error, the connection status is undefined.

#### SQLCONNECT option = value

With Entire Access, you can also specify `SQLCONNECT option=value` as the statement-string. This statement can be used to establish a connection to an SQL database according to the DBID specified in the DDM addressed by the `PROCESS SQL` statement. The `SQLCONNECT` statement will fail if the specified connection is already established.

Note
If the `SQLCONNECT` statement fails, the connection status does not change.

Supported options are:

- `USERID`

- `PASSWORD`

- `OS_PASSWORD`

- `OS_USERID`

- `DBMS_PARAMETER`

If several options are specified, they must be separated by a comma. The options are evaluated as described below.

The specified value can be either a character literal or a Natural variable of format A. If Natural performs an implicit reconnect, because the connection to the database was lost, the values provided by the `SQLCONNECT` statement are used.

#### USERID and PASSWORD

Specifying `USERID` and `PASSWORD` for the database logon suppresses the default logon window and the evaluation of the environment variables `SQL_DATABASE_USER` and `SQL_DATABASE_PASSWORD`.

If only `USERID` is specified, `PASSWORD` is assumed to be blank, and vice versa. If neither `USERID` nor `PASSWORD` is specified, default logon processing applies.

Note
With database systems that do not require user ID and password, a blank user ID and password can be specified to suppress the default logon processing.

#### OS_USERID and OS_PASSWORD

Specifying `OS_PASSWORD` and `OS_USERID` for the operating system logon suppresses the logon window and the evaluation of the environment variables `SQL_OS_USER` and `SQL_OS_PASSWORD`.

If only `OS_USERID` is specified, `OS_PASSWORD` is assumed to be blank, and vice versa. If neither `OS_USERID` nor `OS_PASSWORD` is specified, default logon processing applies.

Note
With operating systems that do not require user ID and password, a blank user ID and password can be specified to suppress the default logon processing.

#### DBMS_PARAMETER

Specifying `DBMS_PARAMETER` dynamically overwrites the DBMS Parameter definition in the Natural global configuration file.

#### Examples:

    PROCESS SQL sql-ddm << SQLCONNECT USERID = 'DBA', PASSWORD = 'SECRET' >>

This example connects to the database specified in the Natural global configuration file with user ID "DBA" and password "SECRET".

    DEFINE DATA LOCAL
    1 #UID  (A20)
    1 #PWD  (A20)
    END-DEFINE

    INPUT 'Please enter Oracle user ID and password' / #UID / #PWD

    PROCESS SQL sql-ddm << SQLCONNECT USERID = : #UID,
                           PASSWORD          = : #PWD,
                           DBMS_PARAMETER    = 'ORA:mydb'
                        >>

This example connects to the Oracle database "mydb" with the user ID and password taken from the `INPUT` statement.

    PROCESS SQL sql-ddm << SQLCONNECT USERID = ' ', PASSWORD = ' ',
                           DBMS_PARAMETER = 'DB2:EXAMPLE' >>

This example connects to the Db2 database "EXAMPLE" without specifying user ID and password (since these are not required by Db2, which uses the operating system user ID).

### SELECT

The `INTO` clause and scalar operators for the `SELECT` statement either are RDBMS-specific and do not conform to the standard SQL syntax definitions (the Natural common set), or impose restrictions when used with Entire Access.

Entire Access does not support the `INDICATOR` and `LINDICATOR` clauses in the `INTO` clause. Thus, Entire Access requires the following syntax for the `INTO` clause:

`INTO` |  | parameter, |
`VIEW` {view-name},

The concatenation operator ( || ) does not belong to the common set and is therefore not supported by Entire Access.

See the Natural documentation for more information.

### SELECT SINGLE

The `SELECT SINGLE` statement provides the functionality of a non-cursor `SELECT` operation (singleton `SELECT`); that is, a `SELECT` statement that retrieves a maximum of one row without using a cursor.

This statement is similar to the Natural `FIND UNIQUE` statement. However, Natural automatically checks the number of rows returned. If more than one row is selected, Natural returns an error message.

If your RDBMS does not support dynamic execution of a non-cursor `SELECT` operation, the Natural `SELECT SINGLE` statement is executed like a set-level `SELECT` statement, which results in a cursor operation. However, Natural still checks the number of returned rows and issues an error message if more than one row is selected.

### UPDATE

The Natural SQL `UPDATE` statement updates rows in a table without using a cursor.

Whereas Natural translates the DML `UPDATE` statement into a positioned `UPDATE` statement (that is, the SQL `UPDATE WHERE CURRENT OF cursor-name` statement), the Natural SQL `UPDATE` statement is a non-cursor or searched `UPDATE` statement. A searched `UPDATE` statement is a stand-alone statement unrelated to any `SELECT` statement.

## Flexible SQL

Flexible SQL allows you to use arbitrary RDBMS-specific SQL syntax extensions. Flexible SQL can be used as a replacement for any of the following syntactical SQL items:

- atom

- column reference

- scalar expression

- condition

The Natural compiler does not recognize the SQL text used in flexible SQL; it simply copies the SQL text (after substituting values for the host variables, which are Natural program variables referenced in an SQL statement) into the SQL string that it passes to the RDBMS. Syntax errors in flexible SQL text are detected at runtime when the RDBMS executes the string.

Note the following characteristics of flexible SQL:

- It is enclosed in "<<" and ">>" characters and can include arbitrary SQL text and host variables.

- Host variables must be prefixed by a colon (:).

- The SQL string can cover several statement lines; comments are permitted.

Flexible SQL can also be used between the clauses of a select expression:

    SELECT selection
      << ... >>
      INTO ...
      FROM ...
      << ... >>
      WHERE ...
      << ... >>
      GROUP BY ...
      << ... >>
      HAVING ...
      << ... >>
      ORDER BY ...
      << ... >>

### Examples:

    SELECT NAME
    FROM EMPLOYEES
    WHERE << MONTH (BIRTH) >>  =  << MONTH (CURRENT_DATE) >>

    SELECT NAME
    FROM EMPLOYEES
    WHERE << MONTH (BIRTH) = MONTH (CURRENT_DATE) >>

    SELECT NAME
    FROM EMPLOYEES
    WHERE SALARY > 50000
    << INTERSECT
       SELECT NAME
       FROM EMPLOYEES
       WHERE DEPT = 'DEPT10'
    >>

## RDBMS-Specific Requirements and Restrictions

This section discusses restrictions and special requirements for Natural and some RDBMSs used with Entire Access.

### Case-Sensitive Database Systems

In case-sensitive database systems, use lower-case characters for table and column names, as all names specified in a Natural program are automatically converted to lower-case.

Note
This restriction does not apply when you use flexible SQL.

### MySQL and MariaDB

MySQL and MariaDB do not implement updatable cursors. This means that the statements `UPDATE WHERE CURRENT OF cursor-name` and `DELETE WHERE CURRENT OF cursor-name` are not supported. However, when tables have a `PRIMARY KEY` or `UNIQUE NOT NULL` index that consists of a single column that has an integer type, Natural uses _rowid to simulate `WHERE CURRENT OF cursor-name` statements. Please refer to your MySQL or MariaDB documentation and especially the Create Table chapter for more information

## Data-Type Conversion

When a Natural program accesses data in a relational database, Entire Access converts RDBMS-specific data types to Natural data formats, and vice versa. The RDBMS data types and their corresponding Natural data formats are described in the Natural DDM Editor documentation under Data Conversion for RDBMS.

The date/time or datetime format specific to a particular database can be converted into the Natural formats D and T; see the section Date/Time Conversion below.

## Date/Time Conversion

The RDBMS-specific date/time or datetime format can be converted into the Natural formats D and T.

To use this conversion, you first have to edit the Natural DDM to change the date or time field formats from A(lphanumeric) to D(ate) or T(ime). The SQLOPTION DATEFORMAT is obsolete for fields with format D or T.

Note
Date or time fields converted to Natural D(ate)/T(ime) format must not be mixed with those converted to Natural A(lphanumeric) format.

For update commands, Natural converts the Natural Date and Time format to the database-dependent representation of DATE/TIME/DATETIME to a precision level of seconds.

For retrieval commands, Natural converts the returned database-dependent character representation to the internal Natural Date or Time format; see conversion tables below.

For Natural Date variables, the time portion is ignored and initialized to zero.

For Natural Time variables, tenth of seconds are ignored and initialized to zero.

Note
For retrieval commands, the date component of Natural Time is not ignored and is initialized to 0000-01-02 (YYYY-MM-DD) if the RDBMS's time format does not contain a date component.

### Conversion Tables

#### Db2

RDBMS Formats | Natural Date | Natural Time
--- | --- | ---
DATE TIME | `YYYY-MM-DD` | `HH.II.SS`

#### MariaDB

RDBMS Formats | Natural Date | Natural Time
--- | --- | ---
DATE TIME | `YYYY-MM-DD` | `HH:II:SS`

#### MySQL

RDBMS Formats | Natural Date | Natural Time
--- | --- | ---
DATE TIME | `YYYY-MM-DD` | `HH:II:SS`

#### ODBC

RDBMS Formats | Natural Date | Natural Time
--- | --- | ---
DATE TIME | `YYYY-MM-DD` | `HH:II:SS`

#### Oracle

RDBMS Formats | Natural Date | Natural Time
--- | --- | ---
DATE (Oracle session parameter NLS_DATE_FORMAT is set to YYYYMMDDHH24MISS) | `YYYYMMDD000000` (Oracle time component is set to null for update commands and ignored for retrieval commands.) | `YYYYMMDDHHIISS` *

* When comparing two time values, remember that the date components may have different values.

#### PostgreSQL

RDBMS Formats | Natural Date | Natural Time
--- | --- | ---
DATE TIME | `YYYY-MM-DD` | `HH:II:SS`

## Obtaining Diagnostic Information

If the database returns an error while being accessed, you can call the non-Natural program `CMOSQERR` to obtain diagnostic information about the error, using the following syntax:

    CALL 'CMOSQERR' parm1 parm2

The parameters are:

Parameter | Format/Length | Contents
--- | --- | ---
parm1 | I4 | The number of the error returned by the database.
parm2 | A255 | The text of the error returned by the database.

### On this page

- Generating Natural DDMs
- Setting Natural Profile Parameters
- Natural DML Statements
- Natural SQL Statements
- Flexible SQL
- RDBMS-Specific Requirements and Restrictions
- Data-Type Conversion
- Date/Time Conversion
- Obtaining Diagnostic Information