Preprogrammed SQL

Section 0: Module Objectives or Competencies

Course Objective or Competency	Module Objectives or Competency
The student will be able to write software programs that connect to a database and issue SQL statements to manipulate that database.	The student will be able to explain the concept of embedding SQL code in software applications.

Section 1: Overview

With preprogrammed SQL, statements are included in a program that has been written in another programming language, known as a host language, such as C, C++, Java, Visual Basic, PHP, and Python.

When using preprogrammed SOL, the results of the SQL statements cannot immediately be seen; the enveloping program processes them.

Much of SQL's popularity as a data manipulation language is due in part to its ease of use and its powerful data-retrieval capabilities.

In the real world, however, database systems are related to other systems and programs, and you still need a conventional programming language such as Visual Basic .NET, C#, or PHP to integrate database systems with other programs and systems.

Regardless of the programming tools you use, if your web application or Windows-based GUI system requires access to a database you will likely need to use SQL to manipulate the data in the database.
The approach used most commonly for establishing a connection to a database and executing SQL statements is by using one of the APIs that MySQL supports.

There are additional ways of preparing SQL statements in advance.

Stored procedures, stored functions, and triggers are discussed in other lectures.

Section 2: Applications with SQL

Applications that include SQL code are sometimes referred to variously as embedded SQL, static embedded SQL, or preprogrammed SQL.

Strictly speaking the term "embedded SQL" refers to an approach defined in the SQL:2003 standard in which a precompiler removes the SQL statements from the source code and then replaces the statements in the original file with calls to the SQL statements.

The approach is distinct because it requires the keywords EXEC SQL prior to the SQL statements being executed.
While other (perhaps older or enterprise-level) databases provide extensive support to this approach, MySQL provides only limited support because embedded SQL is only used on a limited basis when developing applications that connect to a MySQL database, the more common approach being that described in these notes.

Regardless of the name, the central idea is to place SQL statements in a program written in a host programming language.
Because SQL does not use variables and control-of-flow statements, it serves as a database sublanguage that can be added to a program written in a conventional programming language.
The SQL statements in the program are static; that is, they do not change each time the program is run.
These statements are compiled when the rest of the program is compiled.
The next several lectures provide details and examples of how this is done in PHP and MySQL, culminating with the lecture titled "PHP and MySQL Database Access".

Preprogrammed SQL works well in many situations and can be used in any application for which the data access can be determined at program design time.

For example, an order-entry program always uses the same statement to insert a new order, and an airline reservation system always uses the same statement to change the status of a seat from available to reserved.
Each of these statements would be generalized through the use of host variables; different values can be inserted in a sales order, and different seats can be reserved.
Because such statements can be hard-coded in the program, such programs have the advantage that the statements need to be parsed, validated, and optimized only once, at compile time.
This results in relatively fast code.

Another professor's discussion on embedded/preprogrammed SQL can be found here.