Database principles-Embedded SQL

Embedded SQL is a method of writing SQL statements directly into the source code of the C language, Cobol,fortran, Ada, and other programming languages. This approach enables applications to have the ability to access data and process data.

The following is an embedded SQL basic processing process:

Communication between Embedded SQL statements and the main language

SQL passes state information to the main language, is the main language can control the SQL program flow-sql Communication Area

Main language provides variables to SQL-main variable

The SQL execution results are given to the main language processing-main variables, cursors

SQL Communication Area

After the SQL statement executes, the system will feed back to the application some information, including the various data describing the system's current working state and running environment, which will be sent to the SQL Communication area Sqlca. The application extracts these state information from the SQLCA and decides which statements to execute next.
SQLCA is a data structure that is defined in an application with the exec SQL INCLUDE Sqlca.

SQLCA has a variable sqlcode that stores the return code after each execution of the SQL statement.

The application should test the value of Sqlcode after each execution of an SQL statement to understand the SQL statement execution and handle it accordingly.

If Sqlcode equals the predefined constant success, the SQL statement succeeds, otherwise it represents an error code.

For example, after deleting a DELETE statement, the following different information is available in SQLCA depending on the execution situation:
• Violation of data protection rules, action rejection
• Rows that do not meet the criteria are not deleted
• The number of deleted rows (sqlcode=success) has been successfully deleted
• Unconditional deletion of warning messages
• Execution error due to various reasons

Main variable

In ESQL statements, you can use program variables from the host language to enter or output data.

The main language program variable used in the SQL statement is referred to as the host variable (host Variable), or the primary variable. It is mainly used for data communication between embedded SQL and host language, and the main variables can be divided into input variables and output variables according to different functions. The primary variable that is responsible for entering the parameter value for the SQL operation is the input main variable, the primary variable responsible for accepting the return value of the SQL operation is the output main variable, and if the return value is NULL, the master variable is pail because the host language generally cannot handle null values.

Cursor

The main language is record-oriented, and a set of primary variables can hold only one record at a time, and using only the primary variable does not fully satisfy the requirements of the SQL statement to output data to the application. Use cursors to coordinate these two different processing methods. During database development, when the data you retrieve is just a record, the transaction statement code you write tends to use the Select INSERT statement, which requires a cursor to read one record at a time from a result set.

Where cursors are not required:

• Query result is a single record

This type of statement does not require a cursor, just use the INTO clause to specify the main variable that holds the query result

• Non-current form of adding and deleting statements

The main variable can be used in the SET clause and the WHERE clause of the update, and the SET clause can also use the indicator variable

Instance:

[ Example 2] Check student information according to student number. Suppose you have assigned the student's number to the main variable Givensno.  EXECSELECT  sno,sname,ssex,sage,sdeptinto  : Hsno,: Hname,: hsex,:hage,:hdeptfrom  StudentWHERE Sno=: Givensno;

 [    =- 1  Span style= "color: #000000;"  exec  SQL update   Student  set  sage=  :raise:sageid  where  sdept=  ' cs< Span style= "color: #ff0000;" > '   

SQL statements that use cursors

SQL statements that must use cursors

• SELECT statement with multiple records for query results
• UPDATE statement in current form
• Delete statement in current form

The purpose of the current form of the UPDATE statement and the DELETE statement is a collection-oriented operation that operates all records at once. If you want to modify or delete only one of these records, use a SELECT statement with a cursor to find all the records that satisfy the criteria, and further identify the records to be modified or deleted, using the current form of the UPDATE statement and DELETE statement to modify or delete.

Note: When a SELECT statement in a cursor definition has a union or an ORDER BY clause, the SELECT statement is equivalent to defining a non-updatable view, and cannot use the current form of the UPDATE statement and the DELETE statement.

Database principles-Embedded SQL