Lecture 16. The use of SQL in application programming 16.1. Module language or embedded SQL?

The SQL / 89 standard defines two ways of interacting with a database from an application program written in a traditional programming language (as we already mentioned, SQL / 89 is intended to be used in conjunction with the languages ​​Kobol, Fortran, Pascal, and PL / 1, but in implementations it is usually supported and C language). The first way is that all SQL statements with which this application program can work are assembled into one module and are designed as procedures of this module. For this, SQL / 89 contains a special sub-language, the module language. When using this method of interaction with the database, the application program contains calls to the procedures of the SQL module with the transfer of actual parameters to them and obtaining response parameters.

The second way is to use the so-called embedded SQL, when using special syntax in the program in the traditional programming language SQL statements are embedded. In this case, from the point of view of the application program, the SQL statement is executed in-place. There is no explicit parameterization of SQL statements, but embedded SQL statements can use the names of the variables of the main program, and this ensures the connection between the application program and the DBMS.

Conceptually, these two methods are equivalent. Moreover, the standard establishes rules for generating an implicit SQL module in a program with embedded SQL. However, in most implementations, the SQL statements contained in the SQL module and the embedded SQL statements are handled substantially differently. The SQL module is usually compiled separately from the application program, as a result of which a set of so-called stored procedures is generated (this term is not used in the standard, but is common in commercial implementations). Those. in the case of using the SQL module, SQL statements are compiled once, and then the corresponding procedures can be called as many times as necessary from the application program.

In contrast, for SQL statements embedded in an application program, these statements are usually compiled each time they are used (more correctly, each time the statement is used when the application is run at the given time).

Of course, users are not required to be aware of this technical difference in processing two types of interaction with the DBMS. There are also systems that make a one-time compilation of embedded SQL statements and save the compiled code. But still it is better to keep this in mind.

We give some considerations for and against each of these two methods. When using the module language, the text of the application program has a smaller size, interactions with the DBMS are more localized due to the presence of explicit parameters of the procedure call. On the other hand, to understand the meaning of the behavior of an application program, simultaneous reading of two texts will be required. In addition, it seems that the syntax of the SQL module can vary significantly in different implementations. Embedded SQL provides the ability to produce more "self-contained" application programs. There is more reason to rely on the ease of transferring such a program to another DBMS environment, since the embedding standard is more or less complied with. The main disadvantage is some PL-like type of such programs, regardless of the selected primary language. And of course, you need to take into account the comments contained in the previous paragraphs.

Next, we briefly describe the module language and embedding rules in accordance with the SQL / 89 standard (again, formally, embedding rules are not part of the standard).