Embedded SQL in DBMS

Embedded SQL, Dynamic SQL, and SQLJ

Retrieving Single Tuples with Embedded SQL In this section, we give an overview of how SQL statements can be embedded in a general-purpose programming language such as C, ADA, COBOL, or PASCAL. The programming language is called the host language. Embedded SQL in DBMS

Most SQL statements—including data or constraint definitions, queries, updates, or view definitions—can be embedded in a host language program. An embedded SQL statement is distinguished from programming language statements by prefixing it with the keywords EXEC SQL. so that a preprocessor (or precompiler) can separate embedded SQL statements from the host language code. The SQL statements can be terminated by a semicolon (;) or a matching END-EXEC. Embedded SQL in DBMS

To illustrate the concepts of embedded SQL, we will use C as the host programming language. * Within an embedded SQL command, we may refer to specially declared program variables. These are called shared variables because they are used in

Connecting to the Database. The SQL command for establishing a connection to a database has the following form:

CONNECT TO <server name> AS <connection name>

AUTHORIZATION <user account name and password> ;

In general, since a user or program can access several database servers, several connections can be established, but only one connection can be active at any point in time. The programmer or user can use the <connection name> to change from the currently active connection to a different one by using the following command: Embedded SQL in DBMS

SET CONNECTION <connection name> ;

Once a connection is no longer needed, it can be terminated by the following command:

DISCONNECT <connection name> ;

In the examples in this chapter, we assume that the appropriate connection has already been established to the COMPANY database, and that it is the currently active connection.

Communicating between the Program and the DBMS Using SQLCODE and SQLSTATE. The two special communication variables that are used by the DBMS to communicate exception or error conditions to the program are SQLCODE and SQLSTATE. The SQLCODE variable. is an integer variable. After each database command is executed, the DBMS returns a value in 1 SQLCODE. A value of 0 indicates that the statement was executed successfully by the DBMS. If SQLCODE > 0 (or, more specifically, if SQLCODE = 100), this indicates that no more data (records) are available in a query result.

If SQLCODE < 0, this indicates some error has occurred. In some systems—for example, in the Oracle RDBMS-SQLCODE is a field in a record structure called SQLCA (SQL communication area), so it is referenced as SQLCA.SQLCODE. In this case, the definition of SQLCA must be included in the C program by including the following line: Embedded SQL in DBMS

EXEC SQL include SQLCA ;

In later versions of the SQL standard, a communication variable called SQLSTATE was added, which is a string of five characters. A value of ‘00000’in SQLSTATE indicates no error or exception; other values indicate various errors or exceptions. For example, ‘02000’indicates ‘no more data’ when using SQLSTATE. Currently, both SQLSTATE and SQLCODE are available in the SQL standard. Many of the error and exception codes returned in SQLSTATE are supposed to be standardized for all SQL,

vendors and platforms, whereas the codes returned in SQLCODE arer ized but are defined by the DBMS vendor. Hence, it is generally beth SOL STATE because this makes error handling in the application programi nendent of a particular DBMS. As an exercise, the reader should rewin examples given later in this chapter using SQLSTATE instead of SQLCODE

Example of Embedded SQL Programming. Our first example to illusto embedded SQL programming is a repeating program segment (loop) that reas Social Security Number of an employee and prints some information from the responding EMPLOYEE record in the database. The C program code is shown program segment El . The program reads (inputs) an Ssn value onto then retrieves the EMPLOYEE tuple with that Ssn from the database via the embea ded SOL command.

The INTO CLAUSE (line 5) specifies the program variables into which attribute values from the database are retrieved. C program variables in the INTO clause are prefixed with a colon (:), as we discussed earlier. Line 7 in El illustrates the communication between the database and the program through the special variable SQLCODE. Embedded SQL in DBMS

If the value returned by the DBMS in SQLCODE is 0, the previous statement was executed without errors or exception conditions. Line 7 checks this and assumes that if an error occurred, it was because no EMPLOYEE tuple existed with the given Ssn; therefore it outputs a message to that effect (line 8). Embedded SQL in DBMS

In El a single tuple is selected by the embedded SQL query; that is why we are able to assign its attribute values directly to C program variables in the INTO clause in line 5. In general, an SQL query can retrieve many tuples. In that case, the C program will

Program segment E1, a C program segment with embedded SQL.

//Program Segment El:

0) loop = 1 ;

1) while (loop) {

2) prompt(“Enter a Social Security Number: “, ssn) ;

3) EXEC SQL

4) select Fname, Minit, Lname, Address, Salary

5) into : fname, :minit, :lname, address, :salary

6) from EMPLOYEE where Ssn = :ssn ;

7) if (SQLCODE == 0) printf(fname, minit, lname, address, salary)

8) else printf(“Social Security Number does not exist: “, ssn) i

9)prompt (“More Social Security Numbers (enter 1 for Yes, 0 for No):

10)}

typically go through a the retrieved tuples and process them one at a time. A cursor is used to allow tuple -at-a-time processing by the host language program.we describe cusors next. Embedded SQL in DBMS