In an ADABAS application with a modular structure, the SQL statements are typically not distributed over the entire application but are concentrated in a single access layer. This access layer has a procedural interface with the rest of the application at which the typical operations for application objects are made available.
For example, insert, update, delete, and select operations are realized on the application object CUSTOMER where the rest of the application does not need to know the number of tables that represent this application object and which integrity checks are performed when modifying customer information.
In client-server configurations, there is an interaction between client and server when executing any SQL statement in the access layer.
The number of these interactions can be drastically reduced when the SQL access layer is no longer run in the client but in the server. ADABAS provides a language for this purpose which allows an SQL access layer to be formulated on the server side.
This has three important advantages:
- The number of interactions between client and server is reduced by several factors. Client-server communication is only required for each operation on the object layer, no longer for each SQL statement. This enhances the performance of client-server configurations considerably.
- The second advantage has to do with software engineering. The SQL access layer contains the procedurally formulated integrity and business rules. The concentration of these rules on the server side and their elimination from applications means that these rules can be updated centrally so that they become immediately effective in all applications. In this way, these integrity and decision rules also become part of the database catalog.
- An SQL access layer relocated to the server side also allows for creating client-specific database functionality and is thus an important customizing tool.
All suppliers of SQL~DBMS are moving technically in the direction of supporting DB procedures and triggers. But with respect to the individual offerings a very close look must be taken at the procedural completeness of the DB procedure language (goal: full programming language) and of the testability of new DB procedures (goal: test environment independent of the DB kernel, preferably 4GL level). In ADABAS, as an additional feature, the code of a DB procedure may run optionally on the client or on the server. This simplifies the development and testability decisively, and characterizes the DB procedure basically as centralized application code.
With the exception of the data type LONG, all data types supported by ADABAS can be used in DB procedures for input/output parameters. It is also possible to pass the name of a cursor (i.e., of a result table) as input and output parameters. This allows a result to be selected in the form of a table within a DB procedure and it allows the result rows to be retrieved by a sequence of FETCH statements outside the DB procedure.
The author or owner of a DB procedure can authorize other ADABAS users to employ this procedure (EXECUTE privilege). This leads to an implicit privilege extension of the other users; i.e., they can execute operations on database objects with a DB procedure for which they are not privileged. In this way, it can be ensured that the manipulation of certain database objects is only possible with DB procedures and no longer with the SQL language.
Triggers are special DB procedures. They possess the same procedural power but are not called explicitly; they run implicitly as a consequence of an INSERT, UPDATE or DELETE operation in a table. This provides a general user-exit mechanism which allows the user to formulate further arbitrary actions on a database in addition to the normal statement semantics. Among other things, triggers permit the formulation of complex integrity rules, the checking of complicated access protection conditions, and the execution of implicit database modifications.
In ADABAS, triggers are generally performed after the execution of the DML statement. Within the trigger, access is given to the before and after image of the table row by qualifying OLD or NEW for the corresponding column. This is an essential prerequisite for the creation of consistency or integrity rules which concern modifications of such a column.
In case of mass INSERT, UPDATE, or DELETE statements, one SQL statement modifies more than one table row. When doing so, it must be decided whether a trigger has to be activated for each table row concerned or only once for the SQL statement. ADABAS supports both options; statement-oriented triggers are formulated by a specific trigger locking.
Apart from a development environment and the testability of DB procedures and triggers, the SQL extensions described in the following in the form of temporary tables and subtransactions are required for their practical application.