| SQL*Module for Ada Programmer's Guide
Part Number A58231-01
Chapter 1 introduces you to SQL*Module. This chapter answers the following questions:
You use SQL*Module to develop and manage Ada applications that access data in an Oracle database. It allows an Ada application to access relational databases without using embedded SQL, and without using a proprietary application programming interface.
SQL*Module fulfills three roles in application development:
The SQL language was designed to operate on entities in a relational database. SQL was not designed to be a general-purpose programming language, and, in fact, it is conceptually very different from 3GLs such as Ada or C. Some of these differences are:
To achieve maximum flexibility when creating large applications, you must combine SQL with host procedural programming language statements. There are several ways to do this, and these are discussed briefly in the next section.
One way to use a procedural language to access data in a relational database is to embed SQL statements directly in a program written in a host 3GL such as C or C++. After the program is coded, you use a precompiler to translate the SQL statements into calls to a runtime library that processes the SQL, and submits the SQL statements to the database.
See Figure 1-1, "Developing with the Precompilers" for details of this process.
While embedded SQL is very useful, it can have drawbacks when very large applications are being developed. There are several reasons for this:
Figure 1-1 shows how you develop applications using the Oracle precompilers.
The ANSI SQL standards committee defined the embedded SQL standard in two steps. A formalism called Module Language was defined, then the embedded SQL standard was derived from Module Language.
Using Module Language is very straightforward: place all SQL code in a separate module, and define an interface between the module containing the SQL code and the host program written in Ada. At the most concrete level, the interface simply consists of
It is also possible to develop more abstract interfaces between the host language and Module Language. One example of this is the SAMeDL (SQL Ada Module Description Language) developed at Carnegie Mellon and the Software Engineering Institute.
Figure 1-2shows how you would develop an application using SQL standard Module Language.
A Module Language compiler such as SQL*Module generates a call-level interface to procedures defined within a module, allowing them to be called like any other host language procedure. Details of the implementation of these procedures are hidden from the application.
The most immediate benefit of this approach is specialization. By separating SQL and the host language, an application developer can focus on using the host language to perform application tasks, and a database developer can focus on using SQL to perform database tasks.
The developer of the application does not need to know SQL. The procedures to be called can be treated as canned routines that return variables of well defined datatypes in the host language. Error handling becomes straightforward, since all module procedures can return a single error indicator.
Figure 1-3 shows the operation of SQL*Module when it is used to compile SQL standard modules. The module file contains a preamble, defining the host 3GL to be Ada, and two simple procedures in Module Language. When SQL*Module compiles this module, it produces two output files: a source code output file, that contains calls to the runtime library SQLLIB that do the work of accessing the database, and a specification file that declares the procedures in the source code file.
You include the specification files in the host application code that calls the module procedures, using the appropriate language-specific mechanism.
In addition to its role as a Module Language compiler, SQL*Module can also build host language interfaces to procedures that are stored in an Oracle database.
Figure 1-4 shows schematically how SQL*Module functions as an interface builder. The compiler extracts the interfaces to stored procedures, and creates an Ada output file that contains calls to the procedures. YourAda application then accesses the stored procedures in the database by calling these interface procedures.
When you create interface procedure files (output files that contain interface procedures for calling stored procedures), you can choose either early binding or late binding.
The early binding option creates a time stamp in the interface procedure for the time that the stored procedure was last compiled. If the stored procedure has been recompiled after the interface procedure was generated, a runtime error is generated when the interface procedure is called from the host application.
The late binding option calls the stored procedure through an anonymous PL/SQL block, and no time stamp is used. See "Early and Late Binding" for more information about binding.
SQL*Module supports international standards for Module Language. Refer to the Preface to this Guide for more information about supported standards. In addition, Oracle has extended the current standard in several ways. For example, datatype conversions between Oracle datatypes and Ada datatypes are defined, comments can be used in a module, and so forth. Chapter 2, "Module Language" describes the Module Language capabilities of SQL*Module in detail. A compile time option, the FIPS flagger, is available to flag use of non-standard extensions to Module Language and to SQL.
In addition to the complete Module Language standard, SQL*Module also provides a way for a host application to access PL/SQL stored procedures in an Oracle database. If a package exists in an Oracle database that contains procedures, you can use SQL*Module to build interface procedures corresponding to the PL/SQL procedures in the package. Thus the application can call the stored procedures directly.
SQL*Module generates code and specification files that can be compiled with the Ada compiler.
You can compile your stored procedures or modules to get an interface procedures file and call the modules directly from an Ada host program.