SQL Step-by-Step (24) Embedding SQL

Embed SQL

In order to better understand Embedded SQL, this section uses a specific example to illustrate. Embedding SQL allows a program to connect to a database and include SQL code into a program so that the database can be used, manipulated, and processed in a program. The following is a routine with embedded SQL written in C that will print a report, which must precompile the SQL statement before normal compilation. Embedding SQL is not the same for different systems, so the following programs are slightly modified in different systems, especially variable declarations and process records. When embedding SQL, consider the network, database management system, operating system is very important.

The following is a detailed code:

#include <stdio.h>

/* The following section is a declaration of host variables that will be used in the program

EXEC SQL BEGIN DECLARE section;

int Buyerid;

Char firstname[100], lastname[100], item[100];

EXEC SQL end DECLARE section;

/* The following include the SQLCA variable, which can be used for error checking * *

EXEC SQL INCLUDE SQLCA;

Main () {

/* The following connection database * *

EXEC SQL CONNECT Userid/password;

/* The following is a connection to the database and check for errors generated t/if (Sqlca.sqlcode) {

printf (Printer, "Error Connecting to Database server.\n");

Exit ();

}

printf ("Connected to Database server.\n");

/* Declare a "Cursor" below. It will be used when the query results are more than one line.

EXEC SQL DECLARE itemcursor CURSOR for

SELECT ITEM, Buyerid

From Antiques

Order BY ITEM;

EXEC SQL OPEN itemcursor;

/* You can also add some other error check here, here is omitted the * *

/* When this cursor has no data, Sqlcode will be generated to allow us to exit the loop. Notice here that, for simplicity's sake, we exit any Sqlcode when the program encounters an error. */

EXEC SQL FETCH itemcursor into:item,: Buyerid;

while (!sqlca.sqlcode) {

EXEC SQL UPDATE Antiques

SET Price = Price + 5

WHERE item =: Item and Buyerid =: Buyerid;

EXEC SQL SELECT Ownerfirstname, Ownerlastname

Into:firstname,: LastName

From Antiqueowners

WHERE Buyerid =: buyerid;

printf ("%25s%25s%25s", FirstName, LastName, Item);

EXEC SQL FETCH itemcursor into:item,: Buyerid;

}

/* Close cursor, submit changes and exit the program. */

EXEC SQL Close datacursor;

EXEC SQL COMMIT release;

Exit ();

}