Advanced SQL Features

Advanced SQL Features

Embedded SQL

AU: May-14,17, Dec.-14,16,17, Marks 8

• The programing module in which the SQL Statements are embedded is called Embedded SQL module.

• It is possible to embed SQL statements inside the programming language such as C, C++, PASCAL,Java and so on.

• It allows the application languages to communicate with DB and get requested result.

• The high level languages which supports embedding SQLS within it are also known as host language.

• An embedded SQL program must be processed by a special preprocessor prior to compilation. The preprocessor replaces embedded SQL requests with host-language declarations and procedure calls that allow runtime execution of the database accesses. Then, the resulting program is compiled by the host-language compiler. This is the main distinction between embedded SQL and JDBC or ODBC.

Example of Embedded SQL - Following program prompts the user for an order number, 

retrieves the customer number, salesperson, and status of the order, and displays the retrieved information on the screen.

int main() {

EXEC SOL INCLUDE SOLCA;

EXEC SOL BEGIN DECLARE SECTION;

int OrderID;/* Employee ID (from user) */ 

int CustID;/* Retrieved customer ID */

char SalesPerson[10] /* Retrieved salesperson name   */

char Status[6]/* Retrieved order status  */

EXEC SOL END DECLARE SECTION;

/* Set up error processing */

EXEC SOL WHENEVER SOLERROR GOTO query_error;

EXEC SOL WHENEVER NOT FOUND GOTO bad_number;

/* Prompt the user for order number */

printf ("Enter order number: ");

scanf_s("%d", &OrderID);

/* Execute the SQL query */

EXEC SOL SELECT CustID, SalesPerson, Status

FROM Orders

WHERE OrderID = :OrderID

INTO:CustID, :SalesPerson, :Status;

/* Display the results */

printf ("Customer number: %d\n", CustID);

printf ("Salesperson: %s\n", SalesPerson);

 printf ("Status: %s\n", Status);

exit():

query_error:

printf ("SQL error: %ld\n", sqlca->sqlcode);

exit();

bad_number:

printf ("Invalid order number.\n");

exit();

}

Features of Embedded SQL

(1) It is easy to use.

(2) It is ANSI/ISO standard programming language.

(3) It requires less coding

(4) The precompiler can optimize execution time by generating stored procedures for the Embedded SQL statements.

(5) It is identical over different host languages, hence writing applications using different programming languages is quite easy.

Review Questions

1. What is the need of embedded SQL?  AU: May-17, Dec.-17, Marks 2

2. What is embedded SQL? Give an example. AU: Dec.-16, Marks 5, May-14, Dec.-14, Marks 8