Declaring host variables and using them:
EXEC SQL BEGIN DECLARE SECTION;
char cid[5],cname[14],city[21];
float discount;
EXEC SQL END DECLARE SECTION;
...
...
scanf("%s",cid);
EXEC SQL select cname
into :cname
from customers
where cid = :cid;
...
...
scanf("%s%S%S%f",cid,cname,city,&discount);
EXEC SQL insert into customers
values (:cid,:cname,:city,:discount);
Indicator Variables: integer variables which are used to indicate if a null value is fetched from the database or stored into the database.
EXEC SQL BEGIN DECLARE SECTION; float cust_discount; char cid[5]; short int cd_ind; EXEC SQL END DECLARE SECTION;
To check if the value fetched from database is Null or not:
EXEC SQL select discnt
into :cust_discount:cd_ind
from customers
where cid = :cust_id;
if (cd_ind == -1)
printf("Customer discount is Null\n");
else if (cd_ind == 0)
printf("Customer discount is not Null\n");
To store a null value into the database:
cd_ind = -1;
EXEC SQL update customers
set discnt = :cust_discount:cd_ind
where cid = :cust_id;
SQL Communications Area (sqlca):
EXEC SQL INCLUDE sqlca;
Use of sqlca.sqlcode variable to check for error:
void insert_customer()
{
printf("Type in Customer Id: "); scanf("%s",cid);
printf("Type in Customer Name: "); scanf("%s",cname);
printf("Type in City: "); scanf("%s",city);
printf("Type in Discount: "); scanf("%f",&discount);
EXEC SQL SET TRANSACTION READ WRITE;
EXEC SQL INSERT INTO customers
VALUES(:cid,:cname,:city,:discount);
if (sqlca.sqlcode < 0) {
printf("\n\nCUSTOMER (%s) ALREADY PRESENT\n",cname);
EXEC SQL ROLLBACK WORK;
return;
}
EXEC SQL COMMIT;
}
Selecting more than one row using cursors:
void print_customers()
{
EXEC SQL DECLARE c2 CURSOR FOR
SELECT cid, cname, city, discnt
FROM customers;
EXEC SQL SET TRANSACTION READ ONLY;
EXEC SQL OPEN c2;
EXEC SQL FETCH c2 INTO :cid,:cname,:city,:discount;
while (sqlca.sqlcode == 0) {
cid[strlen(cid)] = '\0';
cname[strlen(cname)] = '\0';
city[strlen(city)] = '\0';
printf("%6s%15s%15s\t%f\n",cid,cname,city,discount);
EXEC SQL FETCH c2 INTO :cid,:cname,:city,:discount;
}
EXEC SQL CLOSE c2; EXEC SQL COMMIT;
}
Delete and Update using cursors:
Delete all customers from the customers table who live in "Duluth" and have made no orders:
Without cursor:
EXEC SQL delete from customers c
where c.city = 'Duluth' and
not exists (select *
from orders o
where c.cid = o.cid);
With Cursor:
EXEC SQL declare del_cust cursor for
select cid
from customers c
where c.city = 'Duluth' and
not exists (select *
from orders o
where c.cid = o.cid)
for update of cid;
...
...
EXEC SQL open del_cust;
EXEC SQL fetch del_cust into :cust_id;
while (sqlca.sqlcode <> 100) {
EXEC SQL delete from customers
where current of del_cust;
EXEC SQL fetch del_cust into :cust_id;
}
Recursive Query:
SQL> create table employees (
eid integer,
mgrid integer);
SQL> select *
from employees;
EID MGRID
---------- ----------
2 1
3 1
4 2
5 2
6 4
7 6
Given a employee, print all the employees who work under that employee at all levels.
EXEC SQL BEGIN DECLARE SECTION;
int eid, a;
EXEC SQL END DECLARE SECTION;
EXEC SQL INCLUDE sqlca;
main()
{ int newrowadded;
exec sql create table answer(
a integer not null, primary key (a));
/* Cursor for employees at next level (Initial answers) */
exec sql declare c1 cursor for
select eid from employees where mgrid = :eid;
/* answer(X) if employees(X,Y) and answer(Y) */
exec sql declare c2 cursor for
select eid from employees,answer where mgrid = a;
/* Cursor to print the answers */
exec sql declare c3 cursor for select a from answer;
Recursive Query Continued:
Get initial answers using Cursor c1:
printf("Type in employee id:");
scanf("%d",&eid);
exec sql open c1;
exec sql fetch c1 into :a;
while (sqlca.sqlcode == 0) {
exec sql insert into answer values (:a);
exec sql fetch c1 into :a;
}
exec sql close c1;
exec sql commit work;
Recursive Query Continued:
Repeatedly process Cursor c2:
do {
newrowadded = FALSE;
exec sql open c2;
exec sql fetch c2 into :a;
while (sqlca.sqlcode == 0) {
exec sql insert into answer values (:a);
if (sqlca.sqlcode == 0)
newrowadded = TRUE;
exec sql fetch c2 into :a;
}
exec sql close c2;
} while (newrowadded);
exec sql commit work;
Recursive Query Continued:
Print results from answer table:
printf("Answer is\n");
exec sql open c3;
exec sql fetch c3 into :a;
while (sqlca.sqlcode == 0) {
printf("%d\n",a);
exec sql fetch c3 into :a;
}
exec sql close c3;
exec sql commit work;
exec sql drop table answer;
exec sql commit work;
}/*end of main*/