This document describes how to use Natural with SQL databases via Entire Access. For information about installation and configuration, see Natural and Entire Access in the Database Management System Interfaces documentation and the separate Entire Access documentation.
This document covers the following topics:
Note:
On principle, the features and examples contained in the document
Accessing Data in an Adabas
Database also apply to the SQL databases supported by
Natural. Differences, if any, are described in the documents for the individual
database access statements (see the Statements
documentation) in paragraphs named Database-Specific
Considerations or in the documents for the individual Natural
parameters (see the Parameter
Reference). In addition, Natural offers a specific set of
statements to access SQL databases.
Entire Access is an application programming interface (API) that supports Natural SQL statements and most Natural DML statements (see the Statements documentation).
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.
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 |
OFF |
Natural does not issue an This is the default. |
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:
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
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.
Note:
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.
Note:
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.
Notes:
END TRANSACTION
statement within a database loop;
place it outside the loop or after the outermost loop of nested loops.
END TRANSACTION
statement cannot be used to store
transaction (ET) data when used with Entire Access.
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.
Note:
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 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 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. On this platform, only the extended set is supported by Natural.
The common set can be handled by each SQL-eligible database system supported by Natural. It basically corresponds to the standard SQL syntax definitions. For a detailed description of the common set of Natural SQL statements, see Common Set and Extended Set (in the Statements documentation).
This section describes considerations and restrictions when using the common set of Natural SQL statements with Entire Access.
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.
Note:
It is not possible to run database loops using the PROCESS
SQL
statement.
Natural 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:
DBPROCESS
(for
Sybase only)
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
|
Microsoft SQL Server | YYYYMMDD HH:II:SS
|
other | DD-MON-YY
|
This option is valid for Sybase and Microsoft SQL Server databases only.
This option is used to influence the allocation of SQL statements to
Sybase and Microsoft SQL Server DBPROCESS
es.
DBPROCESS
es are used by Entire Access to emulate database cursors,
which are not provided by the Sybase and Microsoft SQL Server DBlib
interface.
Two values are possible:
MULTIPLE
|
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.
|
SINGLE
|
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 SELECT ions
from non-shared temporary tables.
|
Notes:
DBPROCESS
option only applies to the Sybase
and Microsoft SQL Server 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 valid for Sybase and Microsoft SQL Server databases only.
With Sybase and Microsoft SQL Server, 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 and Microsoft SQL Server 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.
Supported options are:
Notes:
SQLCONNECT
statement fails, the connection
status does not change.
SQLCONNECT
statement are used.
The options are evaluated as described below.
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 assignment 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, ... |
Note:
The concatenation operator (||) does not belong to the common set
and is therefore not supported by Entire Access.
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
DELETE
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.
The following topics are covered:
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.
The following topics are covered below:
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.
- Case-Sensitivity
SYBASE and Microsoft SQL Server are case-sensitive, and Natural passes parameters in lowercase. Thus, if your SYBASE and Microsoft SQL Server tables or fields are defined in uppercase or mixed case, you must use database
SYNONYM
s or Natural flexible SQL.- Positioned UPDATE and DELETE Statements
To support positioned
UPDATE
andDELETE
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.- Querying Rows
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 anotherDBPROCESS
) until the lock is released by anEND TRANSACTION
orBACKOUT 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 noFOR BROWSE
clause.Natural automatically appends the
FOR BROWSE
clause if the query is referenced by a positionedUPDATE
orDELETE
statement.- Transaction/Non-Transaction Mode
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 andDELETE
s to be issued; thus, commands that run in non-transaction mode, for example,CREATE TABLE
, cannot be issued.- Stored Procedures
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 Editors documentation under Data Conversion for RDBMS (in the section DDM Editors.
The date/time or datetime format specific to a particular database can be converted into the Natural formats D and T; see 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 may
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. 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.
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.
RDBMS Formats | Natural Date | Natural Time |
---|---|---|
DATE |
YYYYMMDD
|
|
TIME |
00HHIISS
|
RDBMS Formats | Natural Date | Natural Time |
---|---|---|
DATE |
YYYY-MM-DD
|
|
TIME |
HH.II.SS |
RDBMS Formats | Natural Date | Natural Time |
---|---|---|
DATETIME , year to day
|
YYYY-MM-DD
|
|
DATETIME , year to second (other formats are not
supported)
|
YYYY-MM-DD-HH:II:SS*
|
RDBMS Formats | Natural Date | Natural Time |
---|---|---|
DATE |
YYYY-MM-DD
|
|
TIME |
HH:II:SS
|
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 *
|
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.
RDBMS Formats | Natural Date | Natural Time |
---|---|---|
DATETIME |
YYYYMMDD
|
YYYYMMDD HH:II:SS *
|
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 | Description |
---|---|---|
parm1
|
I4 | The number of the error returned by the database. |
parm2
|
A70 | The text of the error returned by the database. |