To Chapter 4.12

Embedded SQL

Written by T. Nikovits and A. B. Novak

Why:

there are some problems which can not be expressed in SQL (eg. loops)

riport generation

graphical interface

Solution:

-embedded SQL

- 4GL developer tools

(Oracle Developer)

EXEC <SQL statements> (END-EXEC)

Declarations: variables for both languages (for the SQL and for the host languagae)

EXEC SQL BEGIN DECLARE SECTION;

…..

EXEC SQL END DECLARE SECTION;

Host: C

C statement
SQL statement
C statement
C statement
SQL statement
…

File extensions: .ps (Oracle) .sc. (Ingres)

Embedded program  SQL Precompiler  SQL C functions

Ingres: esqlc.

esqlc program.scprogram.c

Oracle: proc.

proc program.scprogram.c

The result is a C program  CompileRun

You have to attached the libraries. These can be found in the DBMS, they are installed already with the DBMS

Environment

Client / Server
Procedures
SQL statement / SQL statement
execution
C variables / Answer
accept the answer
Other procedures for evaluation of answer
  • The SQL statement are sent to the server
  • Answer:
  • error message (depending of the success of a delete, update)
  • query result (Select…)

Syntax

Sql statements should be recognized, the precompiler compiles only these ones.

General syntax:

EXEC SQL <SQL statement > ;

Example:

EXEC SQL

CREATE TABLE ITEM

(COD NUMBER(5), INAME VARCHAR2(15)) ;

EXEC SQL

DELETE FROM ITEM

WHERE INAME IN

('TABLE', 'MONITOR', 'PENCIL') ;

EXEC SQL

INSERT INTO ITEM(COD, CNAME)

VALUES (10, 'Keyboard') ;

Anywhere can be an SQL statement or a C statement.

Variables

In SQL - statements instead of using database items we can use C variables. They could be simple variables, structures, or pointers.

EXEC SQL DELETE FROM ITEM

WHERE CNAME = <C variable> ;

EXEC SQL INSERT INTO ITEM(COD, CNAME)

VALUES <C variable> ;

Variables must be declared in the declare session:

EXEC SQL BEGIN DECLARE SECTION ;

variable declaration by C syntax

EXEC SQL END DECLARE SECTION ;

Example:

EXEC SQL BEGIN DECLARE SECTION ;

long newcod;

char cname[16];

EXEC SQL END DECLARE SECTION ;

C variables should be written AFTER : . In this way they can be distinguished from the database items (sharing same name, eg. Attribute names)

Example:

EXEC SQL INSERT INTO ITEM(cod)

VALUES :newcod ;

EXEC SQL DELETE FROM ITEM

WHERE cname = :cname ;

What would happen if we forgot to put : there?

Question:

What is the result of the following program?

EXEC SQL

SELECT DNAME, DEPTNO, MANAGER, BUDGET, ADDRESS, PHONE

INTO :dname, :deptno, :manager, :budget, :address, :phone

FROM DEPT

WHERE DNAME= "research";

Using structures

TABLE ITEM :

codinteger

cnamechar(15)

colorchar(15)

weightfloat

struct {

longcod;

charcname[16];

charcolor[16];

doubleweight;

} item_rec;

Referring to a strucuture in SQL:

EXEC SQL INSERT INTO ITEM

VALUES (:item_rec) ;

Or:

EXEC SQL INSERT INTO ITEM

VALUES (:item_rec.cod, :item_rec.cname,
:item_rec.color, :item_rec.weight) ;

Connect to database

Syntax:

EXEC SQL CONNECT : user_name

IDENTIFIED BY : password ;

Disconnect syntax:

EXEC SQL DISCONNECT ;

Remark:

  • Oracle: COMMIT RELEASE

ROLLBACK RELEASE

  • Ingres: multisession (SESSION)

Cursors

Cursors are useful to get back one row from the result of a SELECT statement.

SELECT CURSOR pointing to the actual tuple

Creating a cursor:

EXEC SQL DECLARE cname CURSOR

FOR SELECT ... ;

This reflects changes.

EXEC SQL DECLARE knev INSENSITIVE CURSOR

FOR SELECT ... ;

Cursor can be defined only with SELECT grant

Examples:

1.

EXEC SQL

DECLARE DEPT-EMP CURSOR FOR

SELECT dname, surname, first_name

FROM DEPT d, EMP e

WHERE emp.deptno = emp.deptno

ORDER BY deptno, surname, first_name;

2. AVG salaries for each dept:

EXEC SQL

DECLARE dept-avg-sal CURSOR FOR

SELECT dnaem, AVG(sal)

FROM DEPT d, EMP e

WHERE d.deptno = e.deptno.

GROUP BY deptno

ORDER BY dname;

Using the cursor first we have to open it::

EXEC SQL OPEN cursor_name ;

(at runtime it will point to the tuple used latest by the program, delete, update, insert)

Read and go to next:

EXEC SQL FETCH cursor_name

INTO <C variables> ;

The tuple, to that the cursor points will be read into C variables. TYPE CONVERSION!!!

Closing cursor:

EXEC SQL CLOSE curso_name ;

Remark:

cursor_name is NOT a C-variable, that is why : is not needed.

Updating data using cursors

The type of update must be declraed in the cursor definition:

EXEC SQL DECLARE cursor_name CURSOR FOR SELECT ....

FOR UPDATE [OF column1, column2, ...] ;

Remark:

Only for ONE table used by select. No distinct, no group by, no having, no union!!

Syntax:

EXEC SQL UPDATE <table_name>

SET column = <statement>, ...

WHERE CURRENT OF <cursor_name> ;

Deleting rows:

EXEC SQL DELETE FROM <table_name>

WHERE CURRENT OF <cursor_name> ;

Error handling

For each SQL statement the system feeds back to the client program whether the statement execution was successful or not. That means, that the server program set the values of certain variables of the client program.

The program is responsible for defining these variables:

EXEC SQL INCLUDE SQLCA;

This is a structure type C variables, Named: SQLCA.

Main elements:

sqlca.sqlcode: long sqlcode;

error code for last SQL statement:

= 0 succesfully executed

< 0 error

> 0 exceptional case, like no rows in the answer = 100, or end table)

sqlca.sqlerrmstructure type: lenght of error message and first 70 characters

NULL values handling - indicator variables

INTERBASE SQL TUTORIAL:The following program declares a cursor, opens the cursor, and then loops through the cursor’s active set, fetching and printing values. The program closes the cursor when all processing is finished or an error occurs.

#include <stdio.h>

EXEC SQL

BEGIN DECLARE SECTION;

char deptname[26];

char lname[16];

char fname[11];

EXEC SQL

END DECLARE SECTION;

main ()

{

EXEC SQL

WHENEVER SQLERROR GO TO abend;

EXEC SQL

DECLARE DEPT_EMP CURSOR FOR

SELECT DEPARTMENT, LAST_NAME, FIRST_NAME

FROM DEPARTMENT D, EMPLOYEE E

WHERE D.DEPT_NO = E.DEPT_NO"

ORDER BY DEPARTMENT, LAST_NAME, FIRST_NAME;

EXEC SQL

OPEN DEPT_EMP;

EXEC SQL

FETCH DEPT_EMP

INTO :deptname, :lname, :fname;

while (!SQLCODE)

{

printf("%s %s works in the %s department.\n",fname,

lname, deptname);

EXEC SQL

FETCH DEPT_EMP

INTO :deptname, :lname, :fname;

}

EXEC SQL

CLOSE DEPT_EMP;

exit();

abend:

if (SQLCODE)

{

isc_print_sqlerror();

EXEC SQL

ROLLBACK;

EXEC SQL

CLOSE_DEPT_EMP;

EXEC SQL

DISCONNECT ALL;

exit(1)

}

else

{

EXEC SQL

COMMIT;

EXEC SQL

DISCONNECT ALL;

exit()

}

}

Questions to review:

1. Explain each unit briefly.

2. Insert a variable named as number_of tuples, type INTEGER, whose value is equal to the number of rows printed in the program.

3. List the variables in the host language (C)

4. How many cursors are defined in this program.

5. Give the names of the cursors.

6. What will be printed out if Xian Xi’s name, which is the last in the table, will be changed to Xian Xu. Why? Could you alter this result?

7. Is the transaction committed? Explain.

8. Mr. Cody has been granted for selection on all tables. Could he run that program?