MODULE – 3

SQL : ADVANCE QUERIES

DATABASE APPLICATION
DEVELOPMENT
SQL : ADVANCE QUERIES
Constraints as Assertions
 General constraints: constraints that do not fit in
the basic SQL categories
 Mechanism: CREATE ASSERTION
 components include: a constraint name, followed by
CHECK, followed by a condition
Assertions: An Example
 “The salary of an employee must not be greater
than the salary of the manager of the department
that the employee works for’’
CREATE ASSERTION SALARY_CONSTRAINT
CHECK (NOT EXISTS (SELECT *
FROM EMPLOYEE E, EMPLOYEE M, DEPARTMENT D
WHERE E.SALARY > M.SALARY AND
E.DNO=D.NUMBER AND D.MGRSSN=M.SSN))
Using General Assertions
 Specify a query that violates the condition; include
inside a NOT EXISTS clause
 Query result must be empty
 if the query result is not empty, the assertion has been
violated
SQL Triggers
 Objective: to monitor a database and take action
when a condition occurs
 Triggers are expressed in a syntax similar to
assertions and include the following:
 event (e.g., an update operation)
 condition
 action (to be taken when the condition is satisfied)
SQL Triggers: An Example
 A trigger to compare an employee’s salary to his/her
supervisor during insert or update operations:

CREATE TRIGGER INFORM_SUPERVISOR

BEFORE INSERT OR UPDATE OF
SALARY, SUPERVISOR_SSN ON EMPLOYEE
FOR EACH ROW
WHEN
(NEW.SALARY> (SELECT SALARY FROM EMPLOYEE
WHERE SSN=NEW.SUPERVISOR_SSN))
INFORM_SUPERVISOR (NEW.SUPERVISOR_SSN,NEW.SSN;
Views in SQL
 A view is a “virtual” table that is derived from
other tables
 Allows for limited update operations (since the
table may not physically be stored)
 Allows full query operations
 A convenience for expressing certain operations
Specification of Views
 SQL command: CREATE VIEW
 a table (view) name
 a possible list of attribute names (for example, when
arithmetic operations are specified or when we want
the names to be different from the attributes in the base
relations)
 a query to specify the table contents
SQL Views: An Example
 Specify a different WORKS_ON table

CREATE VIEW WORKS_ON_NEW AS

SELECT FNAME, LNAME, PNAME, HOURS
FROM EMPLOYEE, PROJECT, WORKS_ON
WHERE SSN=ESSN AND PNO=PNUMBER
GROUP BY PNAME;
Using a Virtual Table
 We can specify SQL queries on a newly create
table (view):
SELECT FNAME, LNAME FROM WORKS_ON_NEW
WHERE PNAME=‘Seena’;
 When no longer needed, a view can be dropped:
DROP VIEW WORKS_ON_NEW;
DATABASE APPLICATION DEVELOPMENT
Justification for access to databases via
programming languages :

 A relational DBMS supports an interactive SqL

interface, and users can directly enter SQL commands.
 SQL is a direct query language; as such, it has
limitations.
 via programming languages :
 Complex computational processing of the data.
 Specialized user interfaces.
 Access to more than one database at a time.
 SQL in Application Code
 SQL commands can be called from within a host
language (e.g., C++ or Java) program.
 Once a connection is established, SQL commands can be
used to insert, delete, and modify data.
 SQL queries can be used to retrieve desired data. but we
need to bridge an important difference in how a database
system sees data and how an application program in a
language like Java or C sees data:
 The result of a database query is a set (or multiset) or
records, hut Java has no set or multiset data type.
 This mismatch is resolved through additional SQL
constructs that allow applications to obtain a handle on a
collection and iterate over the records one at a time.
 Embedded SQL allows us to access data using static SQL
queries in application code with Dynamic SQL, we can
create the queries at run-time.
 Cursors bridge the gap between set-valued query answers
and programming languages that do not support set-values
 Java as a popular application development language,
especially for Internet applications, has made
accessing a DBMS from Java code.
 JDBC, a programming interface that allows us to
execute SQL queries from a Java program and use
the results in the Java program.
 JDBC provides greater portability than Embedded
SQL or Dynamic SQL, and offers the ability to
connect to several DBMSs without recompiling the
code
Accessing Databases from Applications

 SQL commands can be executed from within a program in a host

language such as C or Java. The use of SQL commands within a
host language program is called Embedded SQL.
Embedded SQL
 SQL statements (i.e., not declarations) can be used wherever a
statement in the host language is allowed.
 SQL statements must be clearly marked so that a preprocessor can
deal with them before invoking the compiler for the host language.
 Any host language variables used to pass arguments into an SQL
command must be declared in SQL.
 In particular, some special host language variables must be
declared in SQL.
 two complications to bear in mind.
 First, the data types recognized by SQL may not be
recognized by the host language and vice versa. This
mismatch is typically addressed by casting data values
appropriately before passing them to or from SQL
commands.
 The second complication has to do with SQL being set-
oriented, and is addressed using cursors
 SQL statements can refer to host variables (including
special variables used to return status).
 Must include a statement to connect to the right database.
Embedded SQL
 Approach: Embedded SQL in the host language.
 A preprocessor converts the SQL statements into special
API calls.
 Then a regular compiler is used to compile the code.

 Embedded SQL is the one which combines the high

level language with the DB language like SQL. It
allows the application languages to communicate with
DB and get requested result.
 Structure of embedded SQL defines step by step process
of establishing a connection with DB and executing the
code in the DB within the high level language.
Declaring Variables and Exceptions
 Language constructs:
 Connecting to a database:
EXEC SQL CONNECT
 Declaring variables:
EXEC SQL BEGIN (END) DECLARE SECTION
 Statements:
EXEC SQL Statement;
Embedded SQL: Variables
EXEC SQL BEGIN DECLARE SECTION
char c_sname[20];
long c_sid;
short c_rating;
float c_age;
EXEC SQL END DECLARE SECTION

 Two special “error” variables:

 SQLCODE (long, is negative if an error has occurred)
 SQLSTATE (char[6], predefined codes for common errors)
 SQL statements can refer to variables defined in the
host program.
 Such host language variables must be prefixed by a
colon (:) in SQL statements and be declared between
the commands EXEC SQL BEGIN DECLARE
SECTION and SQL END DECLARE SECTION.
 The declarations are similar to how they would look
in a C program and, as usual in C. are separated by
semicolons. For example. we can declare variables c-
sname, c_sid, c_mt'ing, and cage EXEC
EXEC SQL BEGIN DECLARE SECTION
char c_sname[20];
long csid;
short crating;
float cage;
EXEC SQL END DECLARE SECTION
 We also need some way for SQL to report what went wrong if
an error condition arises when executing an SQL statement.
 The SQL-92 standard recognizes two special variables for
reporting errors, SQLCODE and SQLSTATE.
 SQLCODE is the older of the two and is defined to return
some negative value when an error condition arises, without
specifying further just what error a particular negative integer
denotes.
 SQLSTATE, introduced in the SQL-92 standard for the first
time, predefined values with several common error
conditions, thereby introducing some uniformity to how errors
are reported.
 One of these two variables must be declared.
Cursor that gets names of sailors who’ve reserved a red
boat, in alphabetical order

EXEC SQL DECLARE sinfo CURSOR FOR

SELECT S.sname
FROM Sailors S, Boats B, Reserves R
WHERE S.sid=R.sid AND R.bid=B.bid AND B.color=‘red’
ORDER BY S.sname
As a simple example, the following Embedded' SQL
statement inserts a row, whose column values me based on
the values of the host language variables contained in it,
into the Sailors relation:
EXEC SQL INSERT INTO Sailors VALUES
(:c_sname, :csid, :crating, :cage);
Observe that a semicolon terminates the command, as per
the convention for terminating statements in C.
 The SQLSTATE variable should be checked for errors and
exceptions after each Embedded SQL statement.
EXEC SQL WHENEVER [SQLERROR I NOT FOUND]
[ CONTINUE I GOTO st'mt ]
 the value of SQLSTATE should be checked after each

Embedded SQL statement is executed.

 If SQLERROR is specified and the value of SQLSTATE

indicates an exception, control is transferred to stmt, which

is presumably responsible for error and exception handling.
 Control is also transferred to stmt if NOT FOUND is

specified and the value of SQLSTATE is 02000, which

denotes NO DATA.
Retrieving Single Tuples with Embedded SQL

 In a C program, an embedded SQL statement is distinguished

from programming language statements by prefixing it with
the keywords EXEC SQL so that a preprocessor (or
precompiler) can separate embedded SQL statements from the
host language source code.
 The SQL statements within a program are terminated by a
matching END-EXEC or by a semicolon (;).
 Within an embedded SQL command, the programmer can
refer to specially declared C program variables; these are
called shared variables because they are used in both the C
program and the embedded SQL statements.
 Shared variables are prefixed by a colon (:) when they
appear in an SQL statement.
Continues..

 It also allows program variables to have the same

names as attribute names, since they are
distinguishable by the colon (:) prefix in the SQL
statement.
 Names of database schema can construct with
attributes and relations—can only be used within
the SQL commands, but shared program
variables can be used elsewhere in the C program
without the colon (:) prefix.
write C programs to process the COMPANY
database.

 We need to declare program variables to match the

types of the database attributes that the program
will process.
 The programmer can choose the names of the
program variables; they may or may not have
names that are identical to their corresponding
database attributes.
 We will use the C program variables declared in
Figure 10.1 for all our examples and show C
program segments without variable declarations.
 Shared variables are declared within a declare
section in the program, as shown in Figure 10.1
(lines 1 through 7).
 A few of the common bindings of C types to SQL
types are as follows. The SQL types INTEGER,
SMALLINT, REAL, and DOUBLE are mapped to
the C data types long, short, float, and double,
respectively.
 lines 1 and 7, which tell the precompiler to take note of the C variable
names between BEGIN DECLARE and END DECLARE because they can
be included in embedded SQL statements—as long as they are preceded by a
colon (:).
 Lines 2 through 5 are regular C program declarations.
 The C program variables declared in lines 2 through 5 correspond to the
attributes of the EMPLOYEE and DEPARTMENT tables from the
COMPANY database in Figure 5.5
 The variables declared in line 6—SQLCODE and SQLSTATE—are called
SQL communication variables; they are used to communicate errors and
exception conditions between the database system and the executing
program.
 Line 0 shows a program variable loop that will not be used in any embedded
SQL statement, so it is declared outside the SQL declare section.
 Connecting to the Database
 The SQL command for establishing a connection to a
database has the following form: CONNECT TO AS
AUTHORIZATION
 In general, since a user or program can access several
database servers, several connections can be established,
but only one connection can be active at any point in time.
 The programmer or user can use the to change from the
currently active connection to a different one by using the
following command: SET CONNECTION ; Once a
connection is no longer needed, it can be terminated by
the following command: DISCONNECT ;
Communication variables SQLCODE and SQLSTATE

 After each database command is executed, the DBMS returns a

value in SQLCODE.
 A value of 0 indicates that the statement was executed successfully
by the DBMS.
 If SQLCODE > 0 (or, more specifically, if SQLCODE = 100), this
indicates that no more data (records) are available in a query result.
 If SQLCODE < 0, this indicates some error has occurred.
 In later versions of the SQL standard, a communication variable
called SQLSTATE was added, which is a string of five characters.
 A value of ‘00000’ in SQLSTATE indicates no error or exception;
other values indicate various errors or exceptions.
 For example, ‘02000’ indicates ‘no more data’ when using
SQLSTATE. Currently, both SQLSTATE and SQLCODE are
available in the SQL standard.
Example of Embedded SQL Programming.

 Embedded SQL programming is a repeating program segment

(loop) that takes as input a Social Security number of an
employee and prints some information from the corresponding
EMPLOYEE record in the database.
 The program reads (inputs) an Ssn value and then retrieves the
EMPLOYEE tuple with that Ssn from the database via the
embedded SQL command.
 The INTO clause (line 5) specifies the program variables into
which attribute values from the database record are retrieved.
 C program variables in the INTO clause are prefixed with a
colon (:).
 The INTO clause can be used in this manner only when the
query result is a single record; if multiple records are retrieved,
an error will be generated.
 Line 7 in E1 illustrates the communication between the
database and the program through the special variable
SQLCODE.
 If the value returned by the DBMS in SQLCODE is 0, the
previous statement was executed without errors or exception
conditions.
 Line 7 checks this and assumes that if an error occurred, it was
because no EMPLOYEE tuple existed with the given Ssn;
therefore it outputs a message to that effect (line 8).
 A cursor is a variable that refers to a single tuple (row) from a
query result that retrieves a collection of tuples.
 It is used to loop over the query result, one record at a time.
The cursor is declared when the SQL query is declared
 The main disadvantage of embedded SQL is that it supports only
static SQLs.
 If we need to build up queries at run time, then we can use dynamic

sql. That means if query changes according to user input, then it

always better to use dynamic SQL.
Advantage:
 In embedded SQL, compiler knows the query in advance and pre-

compiler compiles the SQL code much before C compiles the code
for execution. Hence embedded SQLs will be faster in execution.
 But in the case of dynamic SQL, queries are created, compiled and

executed only at the run time. This makes the dynamic SQL little
complex, and time consuming.
 Cursor is a Temporary Memory or Temporary Work Station. It is
allocated by Database Server at the Time of Performing

 DML operations on Table by User. Cursors are used to store Database

Tables. There are 2 types of Cursors: Implicit Cursors, and Explicit
Cursors.

 1. 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.

 2. 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.
Cursors

 A major problem in embedding SQL statements in a host language like C is

that an impedance mismatch occurs because SQL operates on set" of records,
whereas languages like C do not cleanly support a set-of-records abstraction.
 The solution is to essentially provide a mechanism that allows us to retrieve
rows one at a time from a relation.
 This mechanism is called a cursor. we can declare a cursor on any relation or
on any SQL query
 Once a cursor is declared, we can open it (which positions the cursor just
before the first row); fetch the next row; move the cursor (to the next row, to
the row after the next n, to the first row, or to the previous row, etc., by
specifying additional parameters for the FETCH command); or close the
cursor.
 Thus, a cursor essentially allows us to retrieve the rows in a table by
positioning the cursor at a particular row and reading its contents
Main components of Cursors
Each cursor contains the followings 5 parts

 1. Declare Cursor: In this part, we declare variables and

return a set of values.
 2. Open: This is the entering part of the cursor.
 3. Fetch: Used to retrieve the data row by row from a cursor.
 4. Close: This is an exit part of the cursor and used to close a
cursor.
 5. Deallocate: In this part, we delete the cursor definition
and release all the system resources associated with the
cursor.
Dynamic SQL
 The embedded SQL queries were written as part of the host program source
code.
 Hence, anytime we want to write a different query, we must modify the program
code and go through all the steps involved (compiling, debugging, testing, and
so on).
 In some cases it is convinent to write a program that can execute different SQL
queries or updates (or other operations) dynamically at runtime.
 For Example, when a user-friendly interface generates SQL queries dynamically
for the user based on user input through a Web interface or mobile App.
 preparation of a Dynamic SQL command occurs at run-time and is run-time
overhead.
 Interactive and Embedded SQL commands can be prepared once at compile-
time and then re-executed as often as desired.
 Dynamic SQL, which is one technique for writing
this type of database program.
 Consider an application such as a spreadsheet or a
graphical front-end that needs to access data from a
DBMS.
 Such an application must accept commands from a
user and, based on what the user needs, generate
appropriate SQL statements to retrieve the
necessary data.
 PREPARE and EXECUTE, through a simple
example:
 char c_sqlstring[] = {"DELETE FROM Sailors WHERE
rating>5"};
 EXEC SQL PREPARE readytogo FROM :csqlstring; EXEC
SQL EXECUTE readytogo;
 The first statement declares the C variable c_sqlstring and
initializes its value to the string representation of an SQL
command.
 The second statement results in this string being parsed and
compiled as an SQL command, with the resulting executable
bound to the SQL variable readytogo.
 Since readytogo is an SQL variable, just like a cursor name, it is
not prefixed by a colon.

The third statement executes the command.
 Microsoft sql server:

 It is a relational dbms, which is developed by

Microsoft. sql server commonly used in ecommerce,
online transaction, data ware housing etc.
MY SQL:
 Open srce rdbms, main used in web data base, mainly

used fo ecommerce,dataware house etc.

Oracle :
 multi model dbms, oracle co-orp developed.Used of

payment, banking,running online processing,reservation

system.
 Microsoft azure sql server: built by Microsoft windows

cloud platform.
 Any operations or data to perform in cloud this wil be
use.

Mongo DB:
Open source database which stores and index the data
in the form of collection of documents .
It produces high performance and it retrives data very
faster because of indexing than conventional db’s.
 Introduction to JDBC
 It is a standarad Java Application program interface quietly
used for database independent connection b/w Java
programming language and wide range of database with the
help of java API.
 We can make a connection with any application program or
java application with the data base.
 Jdbc used to interact with various databases. It can define
platform independent b/w a relational db and java programs.
 It can develop an application in a one system and run in any
other systems because of platform independent.
We can also manipulate data from the database with the help of

different java classes and interfaces .

JDBC: Architecture
 Four architectural components:
 Application (initiates and terminates connections,with a
data source, submits SQL statements and retrieves the
results)
 Driver manager (load JDBC driver, to load JDBC drivers
and pass JDBC function calls from the application to the
correct driver)
 Driver (connects to data source, transmits requests and
returns/translates results and error codes from a form that
is specific to the data source into the JDBC standard.)
 Data source (processes SQL statements ie processes
commands from the driver and returns the results)
 Drivers in JDBC are classified into four types depending on the architectural relationship
between the application and the data source

Four types of drivers:

Bridge: Translates SQL commands into non-native API.
Example: JDBC-ODBC bridge. Code for ODBC and JDBC driver
needs to be available on each client
Direct translation to native API, non-Java driver:
 Translates SQL commands to native API of data source. Need OS-specific
binary on each client.
Network bridge:
 Send commands over the network to a middleware server that talks to the
data source. Needs only small JDBC driver at each client.
Direction translation to native API via Java driver:
 Converts JDBC calls directly to network protocol used by DBMS. Needs
DBMS-specific Java driver at each client.
 Type 1 − JDBC-ODBC Bridge Driver
 In a Type 1 driver, a JDBC bridge is used to access
ODBC drivers installed on each client machine. Using
ODBC, requires configuring on your system a Data
Source Name (DSN) that represents the target database.
 When Java first came out, this was a useful driver
because most databases only supported ODBC access
but now this type of driver is recommended only for
experimental use or when no other alternative is
available.
 The JDBC-ODBC Bridge that comes with JDK 1.2 is a
good example of this kind of driver.
JDBC-ODBC Bridge Driver
Type 2:JDBC-Native –API driver:
 In a Type 2 driver, JDBC API calls are converted into native
C/C++ API calls, which are unique to the database.
 These drivers are typically provided by the database vendors
and used in the same manner as the JDBC-ODBC Bridge.
 The vendor-specific driver must be installed on each client
machine.
 If we change the Database, we have to change the native API,
as it is specific to a database and they are mostly obsolute now,
but you may realize some speed increase with a Type 2 driver,
because it eliminates ODBC's overhead.
 The Oracle Call Interface (OCI) driver is an example of a Type
2 driver.
Type 3 − JDBC-Net pure Java
 In a Type 3 driver, a three-tier approach is used to access
databases.
 The JDBC clients use standard network sockets to
communicate with a middleware application server.
 The socket information is then translated by the
middleware application server into the call format
required by the DBMS, and forwarded to the database
server.
 This kind of driver is extremely flexible, since it requires
no code installed on the client and a single driver can
actually provide access to multiple databases.
You can think of the application server as a JDBC "proxy,"
meaning that it makes calls for the client application.

As a result, you need some knowledge of the application

server's configuration in order to effectively use this driver
type.
Type 4 − 100% Pure Java
 In a Type 4 driver, a pure Java-based driver
communicates directly with the vendor's database
through socket connection.
 This is the highest performance driver available for the
database and is usually provided by the vendor itself.
 This kind of driver is extremely flexible, you don't need
to install special software on the client or server.
 Further, these drivers can be downloaded dynamically.
Which Driver should be Used?
 If you are accessing one type of database, such as

Oracle, Sybase, or IBM, the preferred driver type is 4.

 If your Java application is accessing multiple types of

databases at the same time, type 3 is the preferred driver.

 Type 2 drivers are useful in situations, where a type 3 or

type 4 driver is not available yet for your database.

 The type 1 driver is not considered a deployment-level

driver, and is typically used for development and testing

purposes only.
JDBC Classes and Interfaces
Steps to submit a database query:
 Load the JDBC driver
 Connect to the data source
 Execute SQL statements
 Process the results returned by DBMS
 Terminate the connection
Steps to submit a database
query
JDBC Driver Management
 In JDBC, data source drivers are managed by the
Drivermanager class, which maintains a list of all
currently loaded drivers.
 The Drivermanager class has methods
registerDriver, deregisterDriver, and getDrivers to
enable dynamic addition and deletion of drivers.
 The first step in connecting to a data source is to
load the corresponding JDBC driver.
 The static method forName in the Class class
returns the Java class as specified in the argument
string and executes its static constructor.
 Loading a JDBC driver:
To register a driver is to use java’s as
Class.forName() method, to dynamically load the
driver’s class file into memory, which automatically
registers it.
 In the Java code(to register the oracle driver)
Class.forName(“oracle.jdbc.driver.OracleDriver”);
 DriverManager.registerDriver()
DriverManager.registerDriver()to register the Oracle driver as
shown below:

Establish a connection using the Connection class object

After loading the driver, establish connections as shown below as follows:
Connection con = DriverManager.getConnection(url,user,password)

•user: Username from which your SQL command prompt can be accessed.
•password: password from which the SQL command prompt can be accessed.
•con: It is a reference to the Connection interface.
•Url: Uniform Resource Locator which is created as shown below:
String url = “ jdbc:oracle:thin:@localhost:1521:xe”

Where oracle is the database used, thin is the driver used, @localhost is the IP Address
where a database is stored, 1521 is the port number and xe is the service provider. All 3
parameters above are of String type and are to be declared by the programmer before calling
the function. Use of this can be referred to form the final code.
 Java Database Connectivity with MySQL
 To connect Java application with the MySQL database, we need to follow
5 following steps.

 In this example we are using MySql as the database. So we need to know

following informations for the mysql database:
 Driver class: The driver class for the mysql database
is com.mysql.jdbc.Driver.
 Connection URL: The connection URL for the mysql database
is jdbc:mysql://localhost:3306/sonoo where jdbc is the API, mysql is the
database, localhost is the server name on which mysql is running, we may
also use IP address, 3306 is the port number and sonoo is the database
name. We may use any database, in such case, we need to replace the
sonoo with our database name.
 Username: The default username for the mysql database is root.
 Password: It is the password given by the user at the time of installing the
mysql database. In this example, we are going to use root as the password.
 Connections in JDBC
 A session with a data source is started through creation of a Connection object;
 A connection identifies a logical session with a data source; multiple connections

within the same Java program can refer to different data sources or the same data
source.
 Connections are specified through a JDBC URL, a URL that uses the jdbc

protocol.
 We interact with a data source through sessions. Each connection identifies a

logical session.
JDBC URL: jdbc:<subprotocol>:<otherParameters>
Example:
String uri = .. jdbc:oracle:www.bookstore.com:3083.. Connection connection;
try
{
Connection connection = DriverManager.getConnection(urI,userId,password);
}
catch(SQLException excpt)
{
System.out.println(excpt.getMessageO);
return;
 Connection Class Interface
 The Connection interface provide many methods for transaction management like commit(), rollback(),
setAutoCommit(), setTransactionIsolation(), etc

 public int getTransactionIsolation() throws SQLException and



void setTransactionIsolation(int level) throws SQLException

Gets/Sets isolation level for the current connection.

 public boolean getReadOnly() and

void setReadOnly(boolean b)
Specifies if transactions executed through this connection are read-only

 public boolean getAutoCommit() and

void setAutoCommit(boolean b)

If autocommit is set, then each SQL statement is considered its own transaction. Otherwise, a transaction is
committed using commit(), or aborted using rollback().

 public boolean isClosed()

Checks whether connection is still open.
Methods
3) public void setAutoCommit(boolean status): is used to set the commit status. By
default, it is true.

4) public void commit(): saves the changes made since the previous commit/rollback is
permanent.
5) public void rollback(): Drops all changes made since the previous commit/rollback.
6) public void close(): closes the connection and Releases a JDBC resources
immediately.
Connection Interface Fields
There are some common Connection interface constant fields that are present in the
Connect interface. These fields specify the isolation level of a transaction.
TRANSACTION_NONE: No transaction is supported, and it is indicated by this
constant.
TRANSACTION_READ_COMMITTED: It is a constant which shows that the dirty
reads are not allowed. However, phantom reads and non-repeatable reads can occur.
TRANSACTION_READ_UNCOMMITTED: It is a constant which shows that dirty
reads, non-repeatable reads, and phantom reads can occur.
TRANSACTION_REPEATABLE_READ: It is a constant which shows that the non-
repeatable reads and dirty reads are not allowed. However, phantom reads and can occur.
TRANSACTION_SERIALIZABLE: It is a constant which shows that the non-
repeatable reads, dirty reads as well as the phantom reads are not allowed.
 Executing SQL Statements
 In JDBC Three different ways of executing SQL statements:
 The Statement class is the base class for the other two statement

classes.
 It allows us to query the data source with any static or
dynamically generated SQL query.
 Statement (both static and dynamic SQL statements)
 PreparedStatement
 CallableStatment

The PreparedStatement class is dynamically generates

precompiled SQL statements that can be used several times;
 PreparedStatement :Precompiled, parametrized SQL statements:

 Structure is fixed
 Values of parameters are determined at run-time
The Connection interface is a factory of Statement,
PreparedStatement, and DatabaseMetaData, i.e., an object of
Connection can be used to get the object of Statement and
DatabaseMetaData
 Commonly used methods of Connection interface:
 1) public Statement createStatement(): creates a
statement object that can be used to execute SQL
queries.
 2) public Statement createStatement(int
resultSetType,int
resultSetConcurrency): Creates a Statement
object that will generate ResultSet objects with the
given type and concurrency.
Consider the sample code using a PreparedStatment object

 The SQL query specifies the query string, but uses '‘?'
for the values of the parameters, which are set later
using methods setString, setFloat, and setlnt. The '‘?'
placeholders can be used anywhere in SQL statements
where they can be replaced with a value.
 Examples of places where they can appear include the
WHERE clause (e.g., 'WHERE author=?'), or in SQL
UPDATE and INSERT statements, as in Figure 6.3.
 The method setString is one way to set a parameter
value. It is good style to always use clearParameters ( )
before setting parameter values in order to remove any
old data.
 There are different ways of submitting the query
string to the data source.
 we used the executeUpdate command, which is
used if we know that the SQL statement does not
return any records.
 The executeUpdate method returns an integer
indicating the number of rows the SQL statement
modified; it returns 0 for successful execution
without modifying any rows.
Executing SQL Statements
(Contd.)
String sql=“INSERT INTO Sailors VALUES(?,?,?,?)”;
PreparedStatment pstmt=con.prepareStatement(sql);
pstmt.clearParameters();
pstmt.setInt(1,sid);
pstmt.setString(2,sname);
pstmt.setInt(3, rating);
pstmt.setFloat(4,age);

// we know that no rows are returned, thus we use

executeUpdate()
int numRows = pstmt.executeUpdate();
ResultSets
 The statement executeQuery returns a, ResultSet object, which is
similar to a cursor.
 ResultSet cursors in JDBC 2.0 are very powerful; they allow
forward and reverse scrolling and in-place editing and insertions.
 PreparedStatement.executeUpdate only returns the number of
affected records.
 PreparedStatement.executeQuery returns data, summarized in a
ResultSet object (a cursor).
 most basic form, the ResultSet object allows us to read one row of
the output of the query at a time.
 Initially, the ResultSet is positioned before the first row, and we
have to retrieve the first row with an explicit call to the next 0
method.
 The next method returns false if there are no more rows in the
query answer, and true otherwise.
ResultSets (Contd.)
ResultSet rs = stmt.executeQuery(sqlquery);
// rs is now a cursor
//first call to rs.next ( ) moves to the first record
/ / rs.next( )moves to the next row
String sqlQuery;
ResultSet rs = stmt.executeQuery(sqlQuery)
While (rs.next())
{
// process the data
} Fig: Using Result Set Obj
A ResultSet is a very powerful cursor
 previous ( ) moves back one row.
• absolute (int num) moves to the row with the specified number.
• relative (int num) moves forward or backward (if num is negative) relative to the
current position.
relative (-1) has the same effect as previous.
• first 0 moves to the first row, and last 0 moves to the last row.
 Matching Java and SQL Data Types

SQL Type Java class ResultSet get method

BIT Boolean getBoolean()
CHAR String getString()
VARCHAR String getString()
DOUBLE Double getDouble()
FLOAT Double getDouble()
INTEGER Integer getInt()
REAL Double getFloat()
DATE java.sql.Date getDate()
TIME java.sql.Time getTime()
TIMESTAMP java.sql.TimeStamp getTimestamp()
 JDBC provides special data types and specifies
their relationship to corresponding SQL data types.
 Figure 6.5 shows the accessor methods in a
ResultSet object for the most common SQL
datatypes.
SQLJ
Whereas JDBC provides an API, SQLJ consists of a language extension.
Thus programs containing SQLJ must be run through a preprocessor (the
SQLJ translator) before they can be compiled.
SQLJ v/s JDBC
 SQLJ source programs are smaller than equivalent JDBC programs

because SQLJ provides a shorter syntax.

 SQLJ uses database connections to type-check static SQL code. JDBC,

being a completely dynamic API, does not do any type checking until run-
time.

 SQLJ programs allow direct embedding of Java bind expressions within

SQL statements. JDBC requires separate get and/or set call statements for
each bind variable and specifies the binding by position number.

 SQLJ provides strong typing of query outputs and return parameters and
allows type-checking on calls. JDBC passes values to and from SQL
without compile-time type checking.

 SQLJ provides simplified rules for calling SQL stored procedures and
functions.
 Unlike JDBC, having semi-static SQL queries allows the compiler to
perform SQL syntax checks, strong type checks of the compatibility
of the host variables with the respective SQL attributes, and
consistency of the query with the database schema-tables, attributes,
views, and stored procedures--all at compilation time.
 For example, in both SQLJ and Embedded SQL, variables in the host
language always are bound statically to the same arguments, whereas in
JDBC, we need separate statements to bind each variable to an argument
and to retrieve the result.
 For example, the following SQLJ statement binds host language
variables title, price, and author to the return values of the cursor books.
#sql books = {
SELECT title, price INTO :title, :price
FROM Books WHERE author = :author
};
SQLJ code fragment that selects records from the Books table that match a given author.
String title; Float price; String atithor;
#sql iterator Books (String title, Float price);
Books books;
/ / the application sets the author
/ / execute the query and open the cursor
#sql books = {
SELECT title, price INTO :titIe, :price
FROM Books WHERE author = :author
};
/ / retrieve results
while (books.next()) {
System.out.println(books.titleO + ", " + books.price());
}
books.close();
The corresponding JDBC code fragment looks as follows (assuming we also declared price, name,
and author:
PrcparcdStatcment stmt = connection.prepareStatement(
" SELECT title, price FROM Books WHERE author = ?");
/ / set the parameter in the query ancl execute it
stmt.setString(1, author);
ResultSet 1'8 = stmt.executeQuery();
/ / retrieve the results
while (rs.next()) {
System.out.println(rs.getString(l) + ", " + rs.getFloat(2));
}

 All SQLJ statements have the special prefix #sql. In SQLJ, we retrieve the
results of SQL queries with iterator objects, which are basically cursors.
 An iterator is an instance of an iterator class. Usage of an iterator in SQLJ goes

through five steps:

• Declare the Iterator Class: In the preceding code, this happened through
the statement
#sql iterator Books (String title, Float price);
This statement creates a new Java class that we can use to instantiate objects.
• Instantiate an Iterator Object from the New Iterator Class:
We instantiated our iterator in the statement Books books;.
• Initialize the Iterator Using a SQL Statement:
In our example, this happens through the statement #sql books ;;;;;; ....
• Iteratively, Read the Rows From the Iterator Object:
This step is very similar to reading rows through a ResultSet object in JDBC
• Close the Iterator Object.
SQLJ clauses
 SQLJ statements start with “#sql”

 SQLJ statements terminate with “;”

 SQLJ host variables start with “:”

 SQL text is enclosed in curly braces “{..}”

// SQLJ
int n;
#sql { INSERT INTO emp VALUES (:n)};

// JDBC
int n;
Statement stmt = conn.prepareStatement (“INSERT INTO emp VALUES
(?)”);
stmt.setInt(1,n);
stmt.execute ();
stmt.close();
SQLJ Code
Int sid; String name; Int rating;
// named iterator
#sql iterator Sailors(Int sid, String name, Int rating);
Sailors sailors;
// assume that the application sets rating
#sailors = {
SELECT sid, sname INTO :sid, :name
FROM Sailors WHERE rating = :rating
};
// retrieve results
while (sailors.next()) {
System.out.println(sailors.sid + “ “ + sailors.sname));
}
sailors.close();
SQLJ Iterators
Two types of iterators (“cursors”):
 Named iterator
 Need both variable type and name, and then allows retrieval of columns by
name.
 See example on previous slide.
 Positional iterator
 Need only variable type, and then uses FETCH .. INTO construct:
#sql iterator Sailors(Int, String, Int);
Sailors sailors;
#sailors = …
while (true) {
#sql {FETCH :sailors INTO :sid, :name} ;
if (sailors.endFetch()) { break; }
// process the sailor
}
Open DataBase Connectivity
 Shorten to ODBC, a standard database access method

 The goal: make it possible to access any data from any application,
regardless of which (DBMS).

 ODBC manages this by inserting a middle layer, called a database

driver , between an application and the DBMS.

 The purpose of this layer is to translate the application's data

queries into commands that the DBMS understands.

 For this to work, both the application and the DBMS must be
ODBC-compliant -- that is, the application must be capable of
issuing ODBC commands and the DBMS must be capable of
responding to them.
Stored Procedures
 What is a stored procedure:
 Program executed through a single SQL statement
 Executed in the process space of the server
 Advantages:
 Can encapsulate application logic while staying
“close” to the data
 Reuse of application logic by different users
 Avoid tuple-at-a-time return of records through cursors
Stored Procedures: Examples

CREATE PROCEDURE ShowNumReservations

SELECT S.sid, S.sname, COUNT(*)
FROM Sailors S, Reserves R
WHERE S.sid = R.sid
GROUP BY S.sid, S.sname;

Stored procedures can have parameters:

 Three different modes: IN, OUT, INOUT

CREATE PROCEDURE IncreaseRating(

IN sailor_sid INTEGER, IN increase INTEGER)
UPDATE Sailors
SET rating = rating + increase
WHERE sid = sailor_sid
Calling Stored Procedures
EXEC SQL BEGIN DECLARE SECTION
Int sid;
Int rating;
EXEC SQL END DECLARE SECTION

// now increase the rating of this sailor

EXEC CALL IncreaseRating(:sid,:rating);
JDBC:
CallableStatement cstmt= SQLJ:
con.prepareCall(“{call ShowSailors});
#sql iterator ShowSailors(…);
ResultSet rs = cstmt.executeQuery();
ShowSailors showsailors;
while (rs.next()) { #sql showsailors={CALL
… ShowSailors};
} while (showsailors.next()) {
…
}
SQL/PSM(Persistent Stored Modules)

 Most DBMSs allow users to write stored procedures in a simple, general-purpose

language (close to SQL) à SQL/PSM standard is an ISO standard like SQL.

 Allows develpers to write triggers and sp as sql scripts.

 Can run on all supported server platforms.

 In PSM, we define modules, which are collections of stored
procedures, temporary relations, and other declarations.

Declare a stored procedure:

CREATE PROCEDURE name(p1, p2, …, pn)
local variable declarations
procedure code;
Declare a function:
CREATE FUNCTION name (p1, …, pn) RETURNS sqlDataType
local variable declarations
function code;
 The function takes as input a customer identified by her cid
and a year.
 The function returns the rating of the customer, which is
defined a...'3 follows:
 Customers who have bought more than ten books during the
year are rated 'two';
 customer who have purchased in between 5 and 10 books are
rated 'one',
 otherwise the customer is rated 'zero'.
 The following SQL/PSMcode computes the rating for a given
customer and year
Main SQL/PSM Constructs

 The local variable declarations were empty, and the procedure code consisted
of an SQL query.
 CREATE PROCEDURE RateCustomer(IN custId INTEGER, IN year

INTEGER)
RETURNS INTEGER
DECLARE rating INTEGER;
DECLARE numOrders INTEGER;
SET numOrders =(SELECT COUNT(*) FROM Orders 0 WHERE O.tid =
custId);
IF (numOrders> 10) THEN rating=2;
ELSEIF (numOrders>5) THEN rating=1;
ELSE rating=O;
END IF;
RETURN rating;
 We can declare local variables using the DECLARE
statement. In our example,
 we declare two local variables: 'rating', and 'numOrders'.
 PSM/SQL functions return values via the RETURN
statement. In our example,
 we return the value of the local variable 'rating'.
 we can assign values to variables with the SET
statement. In our example,
 we assigned the return value of a query to the variable
'numOrders'.