For accessing database tables, the database language SQL is embedded in the SQL-PL language.
In an SQL statement, SQL-PL variables are preceded by a ':' to uniquely distinguish them from column names. In the example, 'cname', 'cfname', 'ccity', 'account', and 'cno' are global variables of the program.
For each SQL statement, a single DB call can be specified. All SQL statements for the definition and processing of database tables are permitted.
Examples: SQL statements
An SQL statement always returns a numeric code that provides information about the state of the database after processing the statement. This code can be called via the $RC variable (see chapter SQL Error Handling).
After one of the SQL statements INSERT, UPDATE, DELETE, it can also be found out via the $COUNT variable whether the statement was successful and, if so, how many rows of the statement were affected. $COUNT produces either 0 or the precise number of the inserted, updated or deleted rows.
After a SELECT the $COUNT variable returns :
~ 0 : if there is no hit, i.e. when the database does not contain any entry satisfying the qualification ($RC = 100) or when an error has occurred ($RC <> 0).
~ > 0 : if the number of hits is known, that is, if a certain sorting was demanded in the query. In this case, the response time can be fairly long.
~ NULL : if the precise number is unknown.
After a single SELECT, the $COUNT variable returns either 0 for "not found" or 1 for "found".
The SQL-PL developer must take account of the transaction concept of the database if he wants to program complex database applications.
When an SQL-PL program is called, SQL-PL implicitly opens a transaction. The SQL statement COMMIT WORK records the modifications of the current transaction in the database, terminates the transaction and opens a successor transaction. ROLLBACK WORK, by contrast, resets the database into the state at the beginning of the transaction.
SQL-PL implicitly uses the ISOLATION LEVEL 1 to synchronize transactions in multi-user operation; i.e., the updated entries are locked for other users and the entry last read cannot be modified by other users until the end of the transaction.
If a certain application requires other locks, it is possible to set explicit locks by means of the LOCK statement (see the ADABAS Reference Manual) or to assign another ISOLATION LEVEL (0 to 4) to the user by means of the component XUSER.
SQL-PL implicitly issues a COMMIT WORK before the routine waits for user input; i.e. when a form is called or in case of READ. In this way the database is prevented from rolling back a transaction itself because a lock has been set for too long. This implicit COMMIT can be suppressed for single SQL-PL routines by specifying the option AUTOCOMMIT OFF (see chapter Module Options ).
For TEST executions, no COMMIT statements are performed, not even when they come from SQL-PL routines.
The execution of the program is concluded with COMMIT WORK. If the program has to be interrupted prematurely (runtime error), it is rolled back with ROLLBACK WORK.
The following must be taken into account when accessing the database:
- SELECT statements must stand statically before FETCH statements, preferably in the same SQL-PL routine.
- If the compiler option 'SQL CHECK' has been set, the addressed tables must already exist so that the module can be successfully stored. If this option has been disabled, SQL statements are only checked for correct syntax.
- If a table definition is altered, the SQL-PL routines accessing the altered columns must be saved once again with STORE. Only in this way can the program correctly access the altered table.
A complete description of the possible SQL statements is contained in the ADABAS Reference Manual. Their return codes are described in the manual ADABAS Messages and Codes.
Syntax :
