Dynamic and Embedded SQL for db practices.pptx

Dynamic SQL
• Dynamic SQL is a programming technique that
enables you to build SQL statements dynamically
at runtime.
• SQL statement may be unknown at compilation.

EXAMPLE 1(Dynamic statement –
inline)
CREATE OR REPLACE PROCEDURE
DROP_TABLE2(P_TABLE VARCHAR2)
IS
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE '||P_TABLE;
END;
/

EXAMPLE 1(Dynamic statement
– inline)
begin
drop_table2('t5');
end;
/

Example 2((Dynamic statement –
variable)
CREATE PROCEDURE DROP_TABLE3(P_TABLE VARCHAR2)
IS
NAME VARCHAR(100);
BEGIN
NAME := 'DROP TABLE '||P_TABLE;
EXECUTE IMMEDIATE NAME;
END;
/

Example 2((Dynamic
statement – variable)
begin
drop_table2('t5’);
end;
/

What is Dynamic SQL?
• Dynamic SQL is a programming methodology
for generating and running statements at
run-time.
• It is mainly used to write the general-purpose
and flexible programs where the SQL
statements will be created and executed at
run-time based on the requirement.

What is Embedded SQL?
• As a result, database applications are usually developed by
combining capabilities of a high-level programming language
with SQL.
• The simplest approach is to embed SQL statements directly
into the source code file(s) that will be used to create an
application. This technique is referred to as embedded SQL
programming.
• sqlca.h – header file to be included.

Advantages Embedded SQL
• Helps to access databases from anywhere.
• Allows integrating authentication service for large
scale applications.
• Provides extra security to database transactions.
• Avoids logical errors while performing
transactions on our database(maintaining data
integrity, accuracy, and reliability)
• Makes it easy to integrate the frontend and the
backend of our application.

Embedded SQL
• High-level programming language compilers cannot
interpret, SQL statements.
• Hence source code files containing embedded SQL
statements must be preprocessed before compiling.
• Thus each SQL statement coded in a high-level programming
language source code file must be prefixed with the keywords
EXEC SQL and terminated with either a semicolon or the
keywords END_EXEC.

Embedded SQL
• Likewise, the Database Manager cannot work directly with
high-level programming language variables.
• Instead, it must use special variables known as host
variables to move data between an application and a
database.
• Two types of Host variables:-
1. Input Host Variables – Transfer data to database
2. Output Host Variables – receives data from database

Embedded SQL
• Host variables are ordinary programming language variables.
• To be set apart, they must be defined within a special section
known as a declare section.
EXEC SQL BEGIN DECLARE SECTION
char EmployeeID[7];
double Salary;
EXEC SQL END
DECLARE SECTION
• Each host variable must be
assigned a unique
name
even though declared in different
declaration section.

Embedded SQL
main() {
EXEC SQL BEGIN DECLARE SECTION;
int OrderID, CustID;
char SalesPerson[10], Status[6];
EXEC SQL END DECLARE SECTION;
printf ("Enter order number: ");
scanf ("%d", &OrderID);
EXEC SQL SELECT CustID, SalesPerson, Status FROM
Orders WHERE OrderID = :OrderID INTO :CustID,
:SalesPerson, :Status;
printf ("Customer number: %d n", CustID);
printf ("Salesperson: %s n",
SalesPerson); printf ("Status: %s n",
Status);
}

Embedded SQL
Connecting to Database using embedded sql
EXEC SQL CONNECT :userid IDENTIFIED BY :passwd;
EXEC SQL CREATE TABLE Test (a int);
EXEC SQL INSERT INTO Test VALUES (1);
EXEC SQL SELECT MAX (a) INTO :value from R;
printf (“Max value=%dn”,value);

SQLJ(SQL for Java)
• SQLJ – Standard for embedding SQL statements within
Java code.
• SQLJ provides a set of syntax and rules to facilitate
the interaction between Java code and SQL
• SQLJ is a programming language extension that
allows SQL (Structured Query Language)
statements to be embedded within Java programs.
• It provides a way to integrate SQL queries directly
into Java code, making it easier for developers to
work with databases and retrieve or manipulate
data.
• An SQLJ translator converts SQL statements into Java
• These are executed through the JDBC interface
• Certain classes have to be imported E.g., java.sql

SQLJ
• SQLJ precompiles SQL code in a Java program.
• Provides greater compile-time checking of SQL statements.
• Reduces the amount of code needed to execute SQL
from within Java.

SQLJ v/s JDBC
// SQLJ
int n;
#sql { INSERT INTO emp VALUES (:n)};
#sql is a directive indicating that
an SQL statement follows.
// JDBC int n;
Statement stmt =
conn.prepareStat
ement
(“INSERT INTO
emp VALUES
(?)”);
//"?" is a
placeholder for the
parameter.

SQLJ
import java.sql.*; //standard JDBC
package, which allows you to
select, insert, update, and delete
data in SQL tables
Imports Needed
class X {
void myJavaMethod() {
try {
#sql{update EMP set SAL = SAL + 100
where SAL < 1500};
}
catch (SQLException e) {…} SQL statement placed
in braces
can throw
SQLExcepti
on
}

SQLJ
Loading the JDBC Driver
SQLJ requires that the JDBC driver class is loaded. This can
be
performed in the same way as for JDBC
JDBC Driver is a software component that enables java
application to interact with the database.
try
{
Class.forName("oracle.jdbc.driver.OracleDriver");
} // dynamically load the driver's class file into
memory, which automatically registers it. To establish
a connection to an Oracle database.
catch (ClassNotFoundException e)
{

SQLJ
Specifying a Connection Context
• All SQLJ statements execute in a “connection context”
• Plays similar role as a Connection object does in JDBC.
• Establishes the database we are connecting to, the
user name, and the password.
try
{
Class.forName("oracle.jdbc.driver.OracleDriver");
DefaultContext.setDefaultContext(new DefaultContext(
"jdbc:oracle:thin:@HOSTID:1521:ORCL",
"theUser", "thePassword") );
//HOSTID is a placeholder for the actual hostname
or IP address of the database server, and 1521
is the default port for Oracle databases. ORCL
is the Oracle Service Name or SID.

SQLJ
Passing Host Variables into a SQLJ
Statement
• Prefix the java variable name with a colon (:)
#sql {delete from EMP where SAL >= :amt};

SQLJ
Dealing with Query Result Sets
• SQLJ can be used to execute queries that return a result set .
• Toprocess the result set, define an “iterator” type
that specifies the data type of each column
• Iterator -> Object used for looping.
#sql iterator MyIter(String ENAME, String JOB);
class MyClass {
MyIter iter;
#sql iter = { select ENAME, JOB from EMP };
while(iter.next()) //Traverse through
collection of records
{
String ename = iter.ENAME();
String job = iter.JOB();
}

When to Which?
How do applications connect to a database?
• App ↔ DBMS: Embedded SQL
• App ↔ Driver ↔ DBMS: JDBC/ODBC or SQLJ
What mechanisms exist to retrieve/modify data?
• Static Queries: Embedded SQL, SQLJ
• Dynamic Queries: JDBC/ODBC, Dynamic SQL

Dynamic and Embedded SQL for db practices.pptx

More Related Content

Similar to Dynamic and Embedded SQL for db practices.pptx

More from angelinjeba6

Recently uploaded

Dynamic and Embedded SQL for db practices.pptx