Embedded SQL
Problems that must be solved by embedded SQL:
How to compile an SQL-embedded host language program into executable code
How to transmit data and information between the host language and DBMS
The query result of a database is generally a set of tuples. These tuples must be assigned to the variables in the host language program one by one for processing, during which conversion problems exist.
The Data Types of the two are sometimes not exactly the same or equivalent, and the necessary data type conversion problems need to be solved.
General Form of Embedded SQL
For the host database language SQL, DBMS processing method:
Pre-compile
Modify and expand the main language to allow you to process SQL statements.
To distinguish between the SQL language and the main language:
All SQL statements must be prefixed with exec SQL;
The ending mark of SQL varies with the main language. PL/1 and C end with a semicolon (;), and end with end-exec in COBOL
Example: exec SQL drop table student;
Communication between embedded SQL and the main language
The communication between database work units and source work units mainly includes:
Transmits the execution status information of SQL statements to the main language so that the main language can control the program process accordingly, mainly using the SQL Communication zone (sqlca ).
The main language provides parameters for SQL statements, which are mainly implemented using primary variables.
The SQL statement query results are further processed in the main language, mainly using the primary variables and cursors.
SQL Communication zone
Sqlca is a global variable defined by the system. It is defined in the application in the following format:
Exec SQL include sqlca;
Sqlca has a variable sqlcode: It stores the code returned after each SQL statement is executed. It is mainly used to Report SQL Execution to the application.
Primary variable (1)
Main variables: the main language program variables used in SQL statements, which can be divided into: Input main variables and output main variables.
The input primary variables are assigned values by the application and referenced by SQL statements.
The output primary variables are assigned values by SQL statements and return to the application.
The primary variable cannot directly accept null values. It can be followed by an indicator variable (integer variable) to indicate whether the primary variable is null.
Primary variable (2)
All primary variables, except those defined by the system, must be described. The starting and ending rows are:
Exec SQL begin declare section;
Exec SQL end declare section;
The description is different from the database object name. A colon (:) is added before the primary variable name and the indicator variable in the SQL statement. Can be referenced directly outside of SQL statements.
