How to Calculate Date Values in SQL Data for HTML5and CSS3 Programming
How to Implement a Database in MySQL for HTML5 and CSS3 Programming
How to Run a Script with phpMyAdmin in SQL for HTML5and CSS3 Programming

How to Use Embedded SQL

The most common method of mixing SQL with procedural languages is called embedded SQL. Wondering how embedded SQL works? Take one look at the name and you have the basics down: Drop SQL statements into the middle of a procedural program, wherever you need them.

Of course, as you may expect, an SQL statement that suddenly appears in the middle of a C program can present a challenge for a compiler that isn’t expecting it. For that reason, programs containing embedded SQL are usually passed through a preprocessor before being compiled or interpreted. The EXEC SQL directive warns the preprocessor of the imminent appearance of SQL code.

As an example of embedded SQL, look at a program written in Oracle’s Pro*C version of the C language. The program, which accesses a company’s EMPLOYEE table, prompts the user for an employee name and then displays that employee’s salary and commission. It then prompts the user for new salary and commission data — and updates the employee table with it:

EXEC SQL BEGIN DECLARE SECTION;
  VARCHAR uid[20];
  VARCHAR pwd[20];
  VARCHAR ename[10];
  FLOAT salary, comm;
  SHORT salary_ind, comm_ind;
EXEC SQL END DECLARE SECTION;
main()
{
  int sret;   /* scanf return code */
  /* Log in */
  strcpy(uid.arr,"FRED"); /* copy the user name */
  uid.len=strlen(uid.arr);
  strcpy(pwd.arr,"TOWER"); /* copy the password */
  pwd.len=strlen(pwd.arr);
  EXEC SQL WHENEVER SQLERROR STOP;
  EXEC SQL WHENEVER NOT FOUND STOP;
  EXEC SQL CONNECT :uid;
  printf("Connected to user: percents \n",uid.arr);
  printf("Enter employee name to update: ");
  scanf("percents",ename.arr);
  ename.len=strlen(ename.arr);
  EXEC SQL SELECT SALARY,COMM INTO :salary,:comm
     FROM EMPLOY
     WHERE ENAME=:ename;
  printf("Employee: percents salary: percent6.2f comm:
    percent6.2f \n",
    ename.arr, salary, comm);
  printf("Enter new salary: ");
  sret=scanf("percentf",&salary);
  salary_ind = 0;
  if (sret == EOF !! sret == 0) /* set indicator */
   salary_ind =-1; /* Set indicator for NULL */
  printf(“Enter new commission: “);
  sret=scanf("percentf",&comm);
  comm_ind = 0; /* set indicator */
  if (sret == EOF !! sret == 0)
   comm_ind=-1;  /* Set indicator for NULL */
  EXEC SQL UPDATE EMPLOY
     SET SALARY=:salary:salary_ind
     SET COMM=:comm:comm_ind
     WHERE ENAME=:ename;
  printf("Employee percents updated. \n",ename.arr);
  EXEC SQL COMMIT WORK;
  exit(0);
}

You don’t have to be an expert in C to understand the essence of what this program is doing (and how it intends to do it). Here’s a rundown of the order in which the statements execute:

  1. SQL declares host variables.

  2. C code controls the user login procedure.

  3. SQL sets up error handling and connects to the database.

  4. C code solicits an employee name from the user and places it in a variable.

  5. An SQL SELECT statement retrieves the data for the named employee’s salary and commission, and the statement stores the data in the host variables :salary and :comm.

  6. C then takes over again and displays the employee’s name, salary, and commission and then solicits new values for salary and commission. It also checks to see whether an entry has been made, and if one has not, it sets an indicator.

  7. SQL updates the database with the new values.

  8. C then displays an Operation complete message.

  9. SQL commits the transaction, and C finally exits the program.

You can mix the commands of two languages like this because of the preprocessor. The preprocessor separates the SQL statements from the host language commands, placing the SQL statements in a separate external routine. Each SQL statement is replaced with a host-language CALL of the corresponding external routine. The language compiler can now do its job.

The way the SQL part is passed to the database depends on the implementation. You, as the application developer, don’t have to worry about any of this. The preprocessor takes care of it. You should be concerned about a few things, however, that do not appear in interactive SQL — things such as host variables and incompatible data types.

Host variables

Some information must be passed between the host language program and the SQL segments. You pass this data with host variables. In order for SQL to recognize the host variables, you must declare them before you use them. Declarations are included in a declaration segment that precedes the program segment. The declaration segment is announced by the following directive:

EXEC SQL BEGIN DECLARE SECTION ;

The end of the declaration segment is signaled by this line:

EXEC SQL END DECLARE SECTION ;

Every SQL statement must be preceded by an EXEC SQL directive. The end of an SQL segment may or may not be signaled by a terminator directive. In COBOL, the terminator directive is “END-EXEC”, and in C, it’s a semicolon.

Convert data types

Depending on the compatibility of the data types supported by the host language and those supported by SQL, you may have to use CAST to convert certain types. You can use host variables that have been declared in the DECLARE SECTION. Remember to prefix host variable names with a colon (:) when you use them in SQL statements, as in the following example:

INSERT INTO FOODS
 (FOODNAME, CALORIES, PROTEIN, FAT, CARBOHYDRATE)
 VALUES
 (:foodname, :calories, :protein, :fat, :carbo) ;
  • Add a Comment
  • Print
  • Share
blog comments powered by Disqus
How to Use SQL with Microsoft Access
How to Create Views to Retrieve and Manipulate SQL Data
How to Constrain Values in SQL Server 2005
How to Use UPDATE, DELETE, and INSERT SQL Statements
MySQL Queries
Advertisement

Inside Dummies.com