This section covers the following topics:
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.
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.
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
).
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:
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.
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.
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.
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.
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.
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.
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
.
Natural statements:
HISTOGRAM EMPLOYEES FOR AGE OBTAIN AGE
Equivalent SQL statements:
SELECT AGE, COUNT(*) FROM EMPLOYEES GROUP BY AGE ORDER BY AGE
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.
Natural statements:
READ PERSONNEL BY NAME OBTAIN NAME FIRSTNAME DATEOFBIRTH
Equivalent SQL statement:
SELECT NAME, FIRSTNAME, DATEOFBIRTH FROM PERSONNEL WHERE NAME >= ' ' ORDER BY NAME
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.
The STORE
statement adds a row to a database table. It
corresponds to the SQL INSERT
statement.
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')
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.
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.
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.
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)] |
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).
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:
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.
The INSERT
statement adds rows to a table; it corresponds
to the Natural STORE
statement.
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.
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.
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
DBPROCESS
(for Sybase only)
RAW_DATETIME
TIMEOUT
(for Sybase only)
TRANSACTION
(for Sybase only)
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.
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:
Adabas D | YYYYMMDDHHIISS |
DB2 | YYYY-MM-DD-HH.II.SS |
INFORMIX | YYYY-MM-DD HH:II:SS |
ODBC | YYYY-MM-DD HH:II:SS
|
ORACLE | YYYYMMDDHHIISS
|
SYBASE DBLIB | YYYYMMDD HH:II:SS
|
SYBASE CTLIB | YYYYMMDD HH:II:SS
|
other | DD-MON-YY |
This option is valid for Sybase databases only.
This option is used to influence the allocation of SQL statements to
Sybase DBPROCESSes. DBPROCESSes are used by Entire Access to emulate database
cursors, which are not provided by the Sybase DBlib interface. Two values are
possible: MULTIPLE
(default) and SINGLE
. The
specified value can only be changed if no database loop is active.
With DBPROCESS
set to MULTIPLE
, each
SELECT
statement uses its own secondary DBPROCESS, whereas all
other SQL statements are executed within the primary DBPROCESS. The value
MULTIPLE
therefore enables your application to execute further SQL
statements, even if a database loop is open. It also allows nested database
loops.
With DBPROCESS
set to SINGLE
, all SQL
statements use the same (that is, the primary) DBPROCESS. It is therefore not
possible to execute a new database statement while a database loop is active,
because one DBPROCESS can only execute one SQL batch at a time. Since all
statements are executed in the same (primary) DBPROCESS, however, this setting
enables SELECTions from non-shared temporary tables.
Note:
As the DBPROCESS
option only applies to the Sybase
DBlib interface, your application should use a central CALLNAT
statement to change the value (at least for SINGLE
), so that you
can easily remove these calls once Sybase client libraries are supported. Your
application should also use a central error handling that establishes the
default setting (MULTIPLE
).
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.
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 >>
This option is valid for Sybase databases only.
With Sybase, Entire Access uses a timeout technique to detect database-access deadlocks. The default timeout period is 8 seconds. With this option, you can change the duration of the timeout period (in seconds).
For example, to set the timeout period to 30 seconds, specify
PROCESS SQL sql-ddm << SET SQLOPTION TIMEOUT = 30 >>
This option is valid for Sybase databases only.
This option is used to enable or disable transaction mode. It becomes
effective after the next END TRANSACTION
or BACKOUT
TRANSACTION
statement.
If transaction mode is enabled (this is the default), Natural automatically issues all required statements to begin a transaction.
To disable transaction mode, specify:
PROCESS SQL sql-ddm << SET SQLOPTION TRANSACTION = NO >> ... END TRANSACTION
To enable transaction mode, specify:
PROCESS SQL sql-ddm << SET SQLOPTION TRANSACTION = YES >> ... END TRANSACTION
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.
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.
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.
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.
Specifying DBMS_PARAMETER
dynamically overwrites the DBMS
Parameter definition in the Natural global configuration file.
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 ADABAS D user ID and password' / #UID / #PWD PROCESS SQL sql-ddm << SQLCONNECT USERID = : #UID, PASSWORD = : #PWD, DBMS_PARAMETER = 'ADABASD:mydb' >>
This example connects to the Adabas D 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).
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.
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.
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 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 ... << ... >>
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' >>
This section discusses restrictions and special requirements for Natural and some RDBMSs used with Entire Access.
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.
To execute SQL statements against Sybase and Microsoft SQL Server, you must use one or more DBPROCESS structures. A DBPROCESS can execute SQL command batches.
A command batch is a sequence of SQL statements. Statements must be
executed in the sequence in which they are defined in the command batch. If a
statement (for example, a SELECT
statement) returns a result, you
must execute the statement first and then fetch the rows one by one. Once you
execute the next statement from the command batch, you can no longer fetch rows
from the previous query.
With Sybase and Microsoft SQL Server, an application can use more than one DBPROCESS structure; therefore, it is possible to have nested queries if you use a separate DBPROCESS for each query. Because Sybase and Microsoft SQL Server lock data for each DBPROCESS, however, an application that uses more than one DBPROCESS can deadlock itself. Natural times out in case of a deadlock.
Natural uses one DBPROCESS for each open query and another DBPROCESS
for all other SQL statements (UPDATE
, DELETE
,
INSERT
, ... ).
If a query is referenced by a positioned UPDATE
or
DELETE
statement, Natural automatically appends the FOR
BROWSE
clause to the generated SELECT
statement to allow
UPDATE
s while rows are being read.
For a positioned UPDATE
or DELETE
statement,
the SYBASE "dbqual" function is used to generate the following
search condition:
WHERE unique-index = value AND tsequal (timestamp, old-timestamp)
This search condition can be used to reselect the current row from the
query. The tsequal
function checks whether the row has been
updated by another user.
The following restrictions apply when using Natural with Sybase and Microsoft SQL Server.
Sybase and Microsoft SQL Server are case-sensitive, and Natural passes parameters in lower-case. Thus, if your Sybase and Microsoft SQL Server tables or fields are defined in upper-case or mixed case, you must use database SYNONYMs or Natural flexible SQL.
To support positioned UPDATE
and DELETE
statements, the table to be accessed must have a unique index and a timestamp
column. In addition, the timestamp column must not be included in the select
list of the query.
Sybase and Microsoft SQL Server lock pages, and locked pages are owned by DBPROCESS structures.
Pages locked by an active DBPROCESS cannot subsequently be read (by
the same or another DBPROCESS) until the lock is released by an END
TRANSACTION
or BACKOUT TRANSACTION
statement.
Therefore, if you have updated, inserted, or deleted a row in a table:
Do not start a new SELECT
(FIND
,
READ
, ...) loop against the same table.
Do not fetch additional rows from a query that references the same
table if the SELECT
statement has no FOR BROWSE
clause.
Natural automatically appends the FOR BROWSE
clause if
the query is referenced by a positioned UPDATE
or
DELETE
statement.
Sybase and Microsoft SQL Server differentiate between transaction and
non-transaction mode. In transaction mode, Natural connects to the database
allowing INSERT
s, UPDATE
s and DELETE
s to
be issued; thus, commands that run in non-transaction mode, for example,
CREATE TABLE
, cannot be issued.
It is possible to use stored procedures in Sybase and Microsoft SQL
Server using the PROCESS SQL
statement. However, the stored
procedures must not contain
commands that work only in non-transaction mode; or
return values.
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.
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.
RDBMS Formats | Natural Date | Natural Time |
---|---|---|
DATE |
|
|
RDBMS Formats | Natural Date | Natural Time |
---|---|---|
DATE |
|
|
RDBMS Formats | Natural Date | Natural Time |
---|---|---|
DATETIME, year to day |
|
|
RDBMS Formats | Natural Date | Natural Time |
---|---|---|
DATE |
|
|
RDBMS Formats | Natural Date | Natural Time |
---|---|---|
DATE |
|
YYYYMMDDHHIISS *
|
RDBMS Formats | Natural Date | Natural Time |
---|---|---|
DATETIME | YYYYMMDD
|
YYYYMMDD HH:II:SS *
|
* When comparing two time values, remember that the date components may have different values.
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 | A70 | The text of the error returned by the database. |