Accessing Databases from Applications and
Advanced
Objective
▪ The objective of Unit 3 is to understand the accessing of database
from various applications.
▪ This unit explains the fundamentals of embedding SQL queries in high
level languages.
▪ Implementation of cursors in PL/SQL
Accessing Databases from Applications
▪ Embedded SQL
▪ The SQL commands embedded in any programming language is called as Embedded SQL.
▪ The language which the SQL is embedded is called as host language. The languages
such as C, C++, Java, Python.
▪ The SQL statements are embedded with the host language whenever required.
▪ The compiler of the host language is invoked for SQL pre-processing.
▪ The special variable called host variable is used in the code to alternate between
application and database.
▪ The variables of host language must be declared in SQL.
▪ The host variables are classified into two types
o Input Host variables (move data to database system)
o Output Host variables (acquire data from database system
Declaring Variables and Exceptions
▪ The variables defined in the host program are referred by SQL
statements with the prefixed colon (:)
▪ SYNTAX
EXEC SQL BEGIN DECLARE
SECTION
... EXEC SQL END DECLARE SECTION
The variable declarations look similar to the programming
language. Each variable must be assigned a distinct Empname
Embedding SQL Statements
▪ The SQL statement must be embedded within the host language.
▪ Each SQL statement is preceded with Exec SQL statement.
▪ For example, the following Embedded SQL statement inserts a row,
whose column values are based on the above host language
variables.
EXEC SQL
INSERT INTO EMP VALUES (: emp Emp name,: empid,: salary,: age);
▪ After each statement the SQLSTATE should be checked for errors and
exceptions.
EXEC SQL WHENEVER [SQLERROR I NOT FOUND] [ CONTINUE I GOTO
statement]
Embedding SQL Statements
#include<stdio.h>
main() {
EXEC SQL BEGIN DECLARE SECTION;
int Empid, Age;
char Empname [10], Status[6];
EXEC SQL END DECLARE SECTION;
printf ("Enter the employee number: ");
scanf ("%d", &Empid);
EXEC SQL SELECT Age, Empname, Status FROM
Employee WHERE Empid = :Empid INTO :Age,
:Empname, :Status;
printf ("Employee Age number: %d \n", Age);
printf ("Empname: %s \n", Empname);
printf (“Status: %s \n", Status);
}
Cursors
▪ Cursor is a Temporary Memory.
▪ It is Allocated by Database Server at the Time of
Performing DML(Data Manipulation Language) operations on the
Table by the User. Cursors are used to store Database Tables.
• There are 2 types of Cursors: Implicit Cursors, and Explicit Cursors.
Implicit Cursors: Implicit Cursors are also known as Default Cursors
of SQL SERVER. These Cursors are allocated by SQL SERVER when
the user performs DML operations.
Explicit Cursors: Explicit Cursors are Created by Users whenever the
user requires them. Explicit Cursors are used for Fetching data from
Table in Row-By-Row Manner.
Cursor
▪ How To Create Explicit Cursor?
Declare Cursor Object
▪ Syntax:
DECLARE cursor_name CURSOR FOR SELECT * FROM
table_name
DECLARE s1 CURSOR FOR SELECT * FROM studDetails
2. Open Cursor Connection
OPEN s1
Cursor
Fetch Data from the Cursor There is a total of 6 methods to access data from the
cursor. They are as follows:
[Link] is used to fetch only the first row from the cursor table.
[Link] is used to fetch only the last row from the cursor table.
[Link] is used to fetch data in a forward direction from the cursor table.
[Link] is used to fetch data in a backward direction from the cursor table.
[Link] n is used to fetch the exact nth row from the cursor table.
[Link] n is used to fetch the data in an incremental way as well as a decremental
way.
Syntax:
FETCH NEXT/FIRST/LAST/PRIOR/ABSOLUTE n/RELATIVE n FROM
cursor_name
Cursors
▪ Cursors are defined as pointers to access each record in the database.
▪ This method is used to overcome the problem of mismatch between
programming languages and SQL.
▪ Cursor can be declared on any existing database relation.
▪ There are two types
▪ Read-only and Forward-only
▪ The read only cursors will not update the data.
▪ Forward only cursors can be moved from first row to the last row.
▪ a)Steps for using cursors
(i)Declare the cursor for the SQL statement
(ii)Open the cursor
(iii)Fetch the rows
(iv)Close the cursor
Basic Cursor Definition and Usage
▪ Cursors are used to fetch single rows from the table.
▪ Open cursor is used with select statement
▪ Cursor cannot be used with insert, delete and update statement.
