SlideShare a Scribd company logo
Click to add Title
Embedded SQL, Dynamic SQL
and SQLJ
e-Infochips Institute of Training Research and Academics Limited
Prepared By:-
Dharita Chokshi
Outlines
• What is SQL
• What is Embedded SQL
• Cursors
• Dynamic SQL
• SQLJ
• Summary
What is SQL?
• Structured Query Language (SQL) is a standardized language
used to manipulate database objects and the data they
contain.
• It comprised of several different statements that are used
manipulate data values.
• SQL being a nonprocedural is not a general-purpose
programming language.
UPDATE EMPLOYEE SET LASTNAME = 'Jones' WHERE
EMPID = '001'
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.
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);
Cursor
• Can declare a cursor on a query statement which generates a
relation.
• Can open a cursor, repeatedly fetch a tuple, move the cursor,
until all tuples have been retrieved.
• Control order: ORDER BY, in queries that are accessed through
a cursor
• Can also modify/delete tuple pointed to by cursor.
• Must close cursor at end.
Cursor
EXEC SQL DECLARE myCursor CURSOR FOR SELECT bid
from Reservations;
EXEC SQL OPEN myCursor;
EXEC SQL WHENEVER NOT FOUND DO break;
while (1) {
EXEC SQL FETCH myCursor INTO :num;
}
EXEC SQL CLOSE myCursor;
Dynamic SQL
• Dynamic SQL means composing and executing new (not
previously compiled) SQL statements at run-time.
• Although static SQL statements are relatively easy to
incorporate, Dynamic SQL statements are much more flexible
as they can be constructed at run time.
• Dynamic queries can be complex because the type and
number of retrieved attributes are unknown at compile time.
INSERT INTO EMPLOYEES VALUES (?, ?)
DELETE FROM DEPARTMENT WHERE DEPTID = ?
SQLJ
• SQLJ – Standard for embedding SQL in Java
• Similar to existing SQL extensions provided for C, FORTRAN,
and other programming languages.
• IBM, Oracle, and several other companies proposed SQLJ as
a standard and as a simpler and easier-to-use alternative to
JDBC.
• 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)};
// JDBC
int n;
Statement stmt = conn.prepareStatement
(“INSERT INTO emp VALUES (?)”);
stmt.setInt(1,n);
stmt.execute ();
stmt.close();
class X {
void myJavaMethod() {
try {
#sql{update EMP set SAL = SAL + 100
where SAL < 1500};
}
SQLJ
import java.sql.*;
import sqlj.runtime.*;
import sqlj.runtime.ref.*;
Imports Needed
SQL statement placed in braces
can throw SQLException
catch (SQLException e) {…}
}
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
try
{
Class.forName("oracle.jdbc.driver.OracleDriver");
}
catch (ClassNotFoundException e)
{
System.out.println("Could not load driver");
}
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") );
}
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 .
• To process the result set, define an “iterator” type that
specifies the data type of each column
#sql iterator MyIter(String ENAME, String JOB);
class MyClass {
MyIter iter;
#sql iter = { select ENAME, JOB from EMP };
while(iter.next()) {
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
Thank you
Ad

More Related Content

What's hot (20)

Strings in Java
Strings in JavaStrings in Java
Strings in Java
Abhilash Nair
 
Python SQite3 database Tutorial | SQlite Database
Python SQite3 database Tutorial | SQlite DatabasePython SQite3 database Tutorial | SQlite Database
Python SQite3 database Tutorial | SQlite Database
ElangovanTechNotesET
 
SQL - Structured query language introduction
SQL - Structured query language introductionSQL - Structured query language introduction
SQL - Structured query language introduction
Smriti Jain
 
JVM
JVMJVM
JVM
baabtra.com - No. 1 supplier of quality freshers
 
Aggregate functions
Aggregate functionsAggregate functions
Aggregate functions
sinhacp
 
Introduction to SQL
Introduction to SQLIntroduction to SQL
Introduction to SQL
Ehsan Hamzei
 
SQL JOIN
SQL JOINSQL JOIN
SQL JOIN
Ritwik Das
 
Database Triggers
Database TriggersDatabase Triggers
Database Triggers
Aliya Saldanha
 
Java: GUI
Java: GUIJava: GUI
Java: GUI
Tareq Hasan
 
Java threads
Java threadsJava threads
Java threads
Prabhakaran V M
 
Event handling
Event handlingEvent handling
Event handling
Shree M.L.Kakadiya MCA mahila college, Amreli
 
Advance oops concepts
Advance oops conceptsAdvance oops concepts
Advance oops concepts
Sangharsh agarwal
 
View of data DBMS
View of data DBMSView of data DBMS
View of data DBMS
Rahul Narang
 
STRUCTURE OF SQL QUERIES
STRUCTURE OF SQL QUERIESSTRUCTURE OF SQL QUERIES
STRUCTURE OF SQL QUERIES
VENNILAV6
 
Sql fundamentals
Sql fundamentalsSql fundamentals
Sql fundamentals
Ravinder Kamboj
 
JDBC: java DataBase connectivity
JDBC: java DataBase connectivityJDBC: java DataBase connectivity
JDBC: java DataBase connectivity
Tanmoy Barman
 
embedded-static-&dynamic
embedded-static-&dynamicembedded-static-&dynamic
embedded-static-&dynamic
Saranya Natarajan
 
All data models in dbms
All data models in dbmsAll data models in dbms
All data models in dbms
Naresh Kumar
 
Overview of UML Diagrams
Overview of UML DiagramsOverview of UML Diagrams
Overview of UML Diagrams
Manish Kumar
 
Tcpip services and applications
Tcpip services and applicationsTcpip services and applications
Tcpip services and applications
Online
 

Similar to SQL, Embedded SQL, Dynamic SQL and SQLJ (20)

Dynamic and Embedded SQL for db practices.pptx
Dynamic and Embedded SQL for db practices.pptxDynamic and Embedded SQL for db practices.pptx
Dynamic and Embedded SQL for db practices.pptx
angelinjeba6
 
Advanced SQL - Database Access from Programming Languages
Advanced SQL - Database Access  from Programming LanguagesAdvanced SQL - Database Access  from Programming Languages
Advanced SQL - Database Access from Programming Languages
S.Shayan Daneshvar
 
Chapter09
Chapter09Chapter09
Chapter09
sasa_eldoby
 
[Www.pkbulk.blogspot.com]dbms07
[Www.pkbulk.blogspot.com]dbms07[Www.pkbulk.blogspot.com]dbms07
[Www.pkbulk.blogspot.com]dbms07
AnusAhmad
 
stored.ppt
stored.pptstored.ppt
stored.ppt
YashaswiniSrinivasan1
 
Oracle Database 12c - New Features for Developers and DBAs
Oracle Database 12c - New Features for Developers and DBAsOracle Database 12c - New Features for Developers and DBAs
Oracle Database 12c - New Features for Developers and DBAs
Alex Zaballa
 
Oracle Database 12c - New Features for Developers and DBAs
Oracle Database 12c  - New Features for Developers and DBAsOracle Database 12c  - New Features for Developers and DBAs
Oracle Database 12c - New Features for Developers and DBAs
Alex Zaballa
 
Generating Code with Oracle SQL Developer Data Modeler
Generating Code with Oracle SQL Developer Data ModelerGenerating Code with Oracle SQL Developer Data Modeler
Generating Code with Oracle SQL Developer Data Modeler
Rob van den Berg
 
Jdbc
JdbcJdbc
Jdbc
Nitesh Kumar Pandey
 
Beige_Pastel_Minimalist_Thesis_Defense_Presentation_(3)[1].pptx
Beige_Pastel_Minimalist_Thesis_Defense_Presentation_(3)[1].pptxBeige_Pastel_Minimalist_Thesis_Defense_Presentation_(3)[1].pptx
Beige_Pastel_Minimalist_Thesis_Defense_Presentation_(3)[1].pptx
21981a05i0
 
plsql les06
 plsql les06 plsql les06
plsql les06
sasa_eldoby
 
SQL overview and software
SQL overview and softwareSQL overview and software
SQL overview and software
Randy Riness @ South Puget Sound Community College
 
CSE311_IAH_Slide06_SQL _Retrival_Queries.pptx
CSE311_IAH_Slide06_SQL _Retrival_Queries.pptxCSE311_IAH_Slide06_SQL _Retrival_Queries.pptx
CSE311_IAH_Slide06_SQL _Retrival_Queries.pptx
noshinnawar31
 
java database connectivity for java programming
java database connectivity for java programmingjava database connectivity for java programming
java database connectivity for java programming
rinky1234
 
introduction to SQL query language beginner.ppt
introduction to SQL query language beginner.pptintroduction to SQL query language beginner.ppt
introduction to SQL query language beginner.ppt
PatriceRochon1
 
Jdbc
JdbcJdbc
Jdbc
lathasiva
 
Rapid SQL Datasheet - The Intelligent IDE for SQL Development
Rapid SQL Datasheet - The Intelligent IDE for SQL DevelopmentRapid SQL Datasheet - The Intelligent IDE for SQL Development
Rapid SQL Datasheet - The Intelligent IDE for SQL Development
Embarcadero Technologies
 
SQL for Web APIs - Simplifying Data Access for API Consumers
SQL for Web APIs - Simplifying Data Access for API ConsumersSQL for Web APIs - Simplifying Data Access for API Consumers
SQL for Web APIs - Simplifying Data Access for API Consumers
Jerod Johnson
 
Chap3 3 12
Chap3 3 12Chap3 3 12
Chap3 3 12
Hemo Chella
 
4 jdbc step1
4 jdbc step14 jdbc step1
4 jdbc step1
myrajendra
 
Dynamic and Embedded SQL for db practices.pptx
Dynamic and Embedded SQL for db practices.pptxDynamic and Embedded SQL for db practices.pptx
Dynamic and Embedded SQL for db practices.pptx
angelinjeba6
 
Advanced SQL - Database Access from Programming Languages
Advanced SQL - Database Access  from Programming LanguagesAdvanced SQL - Database Access  from Programming Languages
Advanced SQL - Database Access from Programming Languages
S.Shayan Daneshvar
 
[Www.pkbulk.blogspot.com]dbms07
[Www.pkbulk.blogspot.com]dbms07[Www.pkbulk.blogspot.com]dbms07
[Www.pkbulk.blogspot.com]dbms07
AnusAhmad
 
Oracle Database 12c - New Features for Developers and DBAs
Oracle Database 12c - New Features for Developers and DBAsOracle Database 12c - New Features for Developers and DBAs
Oracle Database 12c - New Features for Developers and DBAs
Alex Zaballa
 
Oracle Database 12c - New Features for Developers and DBAs
Oracle Database 12c  - New Features for Developers and DBAsOracle Database 12c  - New Features for Developers and DBAs
Oracle Database 12c - New Features for Developers and DBAs
Alex Zaballa
 
Generating Code with Oracle SQL Developer Data Modeler
Generating Code with Oracle SQL Developer Data ModelerGenerating Code with Oracle SQL Developer Data Modeler
Generating Code with Oracle SQL Developer Data Modeler
Rob van den Berg
 
Beige_Pastel_Minimalist_Thesis_Defense_Presentation_(3)[1].pptx
Beige_Pastel_Minimalist_Thesis_Defense_Presentation_(3)[1].pptxBeige_Pastel_Minimalist_Thesis_Defense_Presentation_(3)[1].pptx
Beige_Pastel_Minimalist_Thesis_Defense_Presentation_(3)[1].pptx
21981a05i0
 
CSE311_IAH_Slide06_SQL _Retrival_Queries.pptx
CSE311_IAH_Slide06_SQL _Retrival_Queries.pptxCSE311_IAH_Slide06_SQL _Retrival_Queries.pptx
CSE311_IAH_Slide06_SQL _Retrival_Queries.pptx
noshinnawar31
 
java database connectivity for java programming
java database connectivity for java programmingjava database connectivity for java programming
java database connectivity for java programming
rinky1234
 
introduction to SQL query language beginner.ppt
introduction to SQL query language beginner.pptintroduction to SQL query language beginner.ppt
introduction to SQL query language beginner.ppt
PatriceRochon1
 
Rapid SQL Datasheet - The Intelligent IDE for SQL Development
Rapid SQL Datasheet - The Intelligent IDE for SQL DevelopmentRapid SQL Datasheet - The Intelligent IDE for SQL Development
Rapid SQL Datasheet - The Intelligent IDE for SQL Development
Embarcadero Technologies
 
SQL for Web APIs - Simplifying Data Access for API Consumers
SQL for Web APIs - Simplifying Data Access for API ConsumersSQL for Web APIs - Simplifying Data Access for API Consumers
SQL for Web APIs - Simplifying Data Access for API Consumers
Jerod Johnson
 
Ad

Recently uploaded (20)

Slide share PPT of NOx control technologies.pptx
Slide share PPT of  NOx control technologies.pptxSlide share PPT of  NOx control technologies.pptx
Slide share PPT of NOx control technologies.pptx
vvsasane
 
Machine Learning basics POWERPOINT PRESENETATION
Machine Learning basics POWERPOINT PRESENETATIONMachine Learning basics POWERPOINT PRESENETATION
Machine Learning basics POWERPOINT PRESENETATION
DarrinBright1
 
2.3 Genetically Modified Organisms (1).ppt
2.3 Genetically Modified Organisms (1).ppt2.3 Genetically Modified Organisms (1).ppt
2.3 Genetically Modified Organisms (1).ppt
rakshaiya16
 
Control Methods of Noise Pollutions.pptx
Control Methods of Noise Pollutions.pptxControl Methods of Noise Pollutions.pptx
Control Methods of Noise Pollutions.pptx
vvsasane
 
6th International Conference on Big Data, Machine Learning and IoT (BMLI 2025)
6th International Conference on Big Data, Machine Learning and IoT (BMLI 2025)6th International Conference on Big Data, Machine Learning and IoT (BMLI 2025)
6th International Conference on Big Data, Machine Learning and IoT (BMLI 2025)
ijflsjournal087
 
Working with USDOT UTCs: From Conception to Implementation
Working with USDOT UTCs: From Conception to ImplementationWorking with USDOT UTCs: From Conception to Implementation
Working with USDOT UTCs: From Conception to Implementation
Alabama Transportation Assistance Program
 
Jacob Murphy Australia - Excels In Optimizing Software Applications
Jacob Murphy Australia - Excels In Optimizing Software ApplicationsJacob Murphy Australia - Excels In Optimizing Software Applications
Jacob Murphy Australia - Excels In Optimizing Software Applications
Jacob Murphy Australia
 
Water Industry Process Automation & Control Monthly May 2025
Water Industry Process Automation & Control Monthly May 2025Water Industry Process Automation & Control Monthly May 2025
Water Industry Process Automation & Control Monthly May 2025
Water Industry Process Automation & Control
 
ATAL 6 Days Online FDP Scheme Document 2025-26.pdf
ATAL 6 Days Online FDP Scheme Document 2025-26.pdfATAL 6 Days Online FDP Scheme Document 2025-26.pdf
ATAL 6 Days Online FDP Scheme Document 2025-26.pdf
ssuserda39791
 
Lecture - 7 Canals of the topic of the civil engineering
Lecture - 7  Canals of the topic of the civil engineeringLecture - 7  Canals of the topic of the civil engineering
Lecture - 7 Canals of the topic of the civil engineering
MJawadkhan1
 
hypermedia_system_revisit_roy_fielding .
hypermedia_system_revisit_roy_fielding .hypermedia_system_revisit_roy_fielding .
hypermedia_system_revisit_roy_fielding .
NABLAS株式会社
 
DED KOMINFO detail engginering design gedung
DED KOMINFO detail engginering design gedungDED KOMINFO detail engginering design gedung
DED KOMINFO detail engginering design gedung
nabilarizqifadhilah1
 
sss1.pptxsss1.pptxsss1.pptxsss1.pptxsss1.pptx
sss1.pptxsss1.pptxsss1.pptxsss1.pptxsss1.pptxsss1.pptxsss1.pptxsss1.pptxsss1.pptxsss1.pptx
sss1.pptxsss1.pptxsss1.pptxsss1.pptxsss1.pptx
ajayrm685
 
Using the Artificial Neural Network to Predict the Axial Strength and Strain ...
Using the Artificial Neural Network to Predict the Axial Strength and Strain ...Using the Artificial Neural Network to Predict the Axial Strength and Strain ...
Using the Artificial Neural Network to Predict the Axial Strength and Strain ...
Journal of Soft Computing in Civil Engineering
 
Modelling of Concrete Compressive Strength Admixed with GGBFS Using Gene Expr...
Modelling of Concrete Compressive Strength Admixed with GGBFS Using Gene Expr...Modelling of Concrete Compressive Strength Admixed with GGBFS Using Gene Expr...
Modelling of Concrete Compressive Strength Admixed with GGBFS Using Gene Expr...
Journal of Soft Computing in Civil Engineering
 
Uses of drones in civil construction.pdf
Uses of drones in civil construction.pdfUses of drones in civil construction.pdf
Uses of drones in civil construction.pdf
surajsen1729
 
Slide share PPT of SOx control technologies.pptx
Slide share PPT of SOx control technologies.pptxSlide share PPT of SOx control technologies.pptx
Slide share PPT of SOx control technologies.pptx
vvsasane
 
ML_Unit_V_RDC_ASSOCIATION AND DIMENSIONALITY REDUCTION.pdf
ML_Unit_V_RDC_ASSOCIATION AND DIMENSIONALITY REDUCTION.pdfML_Unit_V_RDC_ASSOCIATION AND DIMENSIONALITY REDUCTION.pdf
ML_Unit_V_RDC_ASSOCIATION AND DIMENSIONALITY REDUCTION.pdf
rameshwarchintamani
 
Prediction of Flexural Strength of Concrete Produced by Using Pozzolanic Mate...
Prediction of Flexural Strength of Concrete Produced by Using Pozzolanic Mate...Prediction of Flexural Strength of Concrete Produced by Using Pozzolanic Mate...
Prediction of Flexural Strength of Concrete Produced by Using Pozzolanic Mate...
Journal of Soft Computing in Civil Engineering
 
Machine foundation notes for civil engineering students
Machine foundation notes for civil engineering studentsMachine foundation notes for civil engineering students
Machine foundation notes for civil engineering students
DYPCET
 
Slide share PPT of NOx control technologies.pptx
Slide share PPT of  NOx control technologies.pptxSlide share PPT of  NOx control technologies.pptx
Slide share PPT of NOx control technologies.pptx
vvsasane
 
Machine Learning basics POWERPOINT PRESENETATION
Machine Learning basics POWERPOINT PRESENETATIONMachine Learning basics POWERPOINT PRESENETATION
Machine Learning basics POWERPOINT PRESENETATION
DarrinBright1
 
2.3 Genetically Modified Organisms (1).ppt
2.3 Genetically Modified Organisms (1).ppt2.3 Genetically Modified Organisms (1).ppt
2.3 Genetically Modified Organisms (1).ppt
rakshaiya16
 
Control Methods of Noise Pollutions.pptx
Control Methods of Noise Pollutions.pptxControl Methods of Noise Pollutions.pptx
Control Methods of Noise Pollutions.pptx
vvsasane
 
6th International Conference on Big Data, Machine Learning and IoT (BMLI 2025)
6th International Conference on Big Data, Machine Learning and IoT (BMLI 2025)6th International Conference on Big Data, Machine Learning and IoT (BMLI 2025)
6th International Conference on Big Data, Machine Learning and IoT (BMLI 2025)
ijflsjournal087
 
Jacob Murphy Australia - Excels In Optimizing Software Applications
Jacob Murphy Australia - Excels In Optimizing Software ApplicationsJacob Murphy Australia - Excels In Optimizing Software Applications
Jacob Murphy Australia - Excels In Optimizing Software Applications
Jacob Murphy Australia
 
ATAL 6 Days Online FDP Scheme Document 2025-26.pdf
ATAL 6 Days Online FDP Scheme Document 2025-26.pdfATAL 6 Days Online FDP Scheme Document 2025-26.pdf
ATAL 6 Days Online FDP Scheme Document 2025-26.pdf
ssuserda39791
 
Lecture - 7 Canals of the topic of the civil engineering
Lecture - 7  Canals of the topic of the civil engineeringLecture - 7  Canals of the topic of the civil engineering
Lecture - 7 Canals of the topic of the civil engineering
MJawadkhan1
 
hypermedia_system_revisit_roy_fielding .
hypermedia_system_revisit_roy_fielding .hypermedia_system_revisit_roy_fielding .
hypermedia_system_revisit_roy_fielding .
NABLAS株式会社
 
DED KOMINFO detail engginering design gedung
DED KOMINFO detail engginering design gedungDED KOMINFO detail engginering design gedung
DED KOMINFO detail engginering design gedung
nabilarizqifadhilah1
 
sss1.pptxsss1.pptxsss1.pptxsss1.pptxsss1.pptx
sss1.pptxsss1.pptxsss1.pptxsss1.pptxsss1.pptxsss1.pptxsss1.pptxsss1.pptxsss1.pptxsss1.pptx
sss1.pptxsss1.pptxsss1.pptxsss1.pptxsss1.pptx
ajayrm685
 
Uses of drones in civil construction.pdf
Uses of drones in civil construction.pdfUses of drones in civil construction.pdf
Uses of drones in civil construction.pdf
surajsen1729
 
Slide share PPT of SOx control technologies.pptx
Slide share PPT of SOx control technologies.pptxSlide share PPT of SOx control technologies.pptx
Slide share PPT of SOx control technologies.pptx
vvsasane
 
ML_Unit_V_RDC_ASSOCIATION AND DIMENSIONALITY REDUCTION.pdf
ML_Unit_V_RDC_ASSOCIATION AND DIMENSIONALITY REDUCTION.pdfML_Unit_V_RDC_ASSOCIATION AND DIMENSIONALITY REDUCTION.pdf
ML_Unit_V_RDC_ASSOCIATION AND DIMENSIONALITY REDUCTION.pdf
rameshwarchintamani
 
Machine foundation notes for civil engineering students
Machine foundation notes for civil engineering studentsMachine foundation notes for civil engineering students
Machine foundation notes for civil engineering students
DYPCET
 
Ad

SQL, Embedded SQL, Dynamic SQL and SQLJ

  • 1. Click to add Title Embedded SQL, Dynamic SQL and SQLJ e-Infochips Institute of Training Research and Academics Limited Prepared By:- Dharita Chokshi
  • 2. Outlines • What is SQL • What is Embedded SQL • Cursors • Dynamic SQL • SQLJ • Summary
  • 3. What is SQL? • Structured Query Language (SQL) is a standardized language used to manipulate database objects and the data they contain. • It comprised of several different statements that are used manipulate data values. • SQL being a nonprocedural is not a general-purpose programming language. UPDATE EMPLOYEE SET LASTNAME = 'Jones' WHERE EMPID = '001'
  • 4. 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.
  • 5. 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.
  • 6. 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
  • 7. 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.
  • 8. 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); }
  • 9. 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);
  • 10. Cursor • Can declare a cursor on a query statement which generates a relation. • Can open a cursor, repeatedly fetch a tuple, move the cursor, until all tuples have been retrieved. • Control order: ORDER BY, in queries that are accessed through a cursor • Can also modify/delete tuple pointed to by cursor. • Must close cursor at end.
  • 11. Cursor EXEC SQL DECLARE myCursor CURSOR FOR SELECT bid from Reservations; EXEC SQL OPEN myCursor; EXEC SQL WHENEVER NOT FOUND DO break; while (1) { EXEC SQL FETCH myCursor INTO :num; } EXEC SQL CLOSE myCursor;
  • 12. Dynamic SQL • Dynamic SQL means composing and executing new (not previously compiled) SQL statements at run-time. • Although static SQL statements are relatively easy to incorporate, Dynamic SQL statements are much more flexible as they can be constructed at run time. • Dynamic queries can be complex because the type and number of retrieved attributes are unknown at compile time. INSERT INTO EMPLOYEES VALUES (?, ?) DELETE FROM DEPARTMENT WHERE DEPTID = ?
  • 13. SQLJ • SQLJ – Standard for embedding SQL in Java • Similar to existing SQL extensions provided for C, FORTRAN, and other programming languages. • IBM, Oracle, and several other companies proposed SQLJ as a standard and as a simpler and easier-to-use alternative to JDBC. • 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
  • 14. 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.
  • 15. SQLJ v/s JDBC // 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();
  • 16. class X { void myJavaMethod() { try { #sql{update EMP set SAL = SAL + 100 where SAL < 1500}; } SQLJ import java.sql.*; import sqlj.runtime.*; import sqlj.runtime.ref.*; Imports Needed SQL statement placed in braces can throw SQLException catch (SQLException e) {…} }
  • 17. 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 try { Class.forName("oracle.jdbc.driver.OracleDriver"); } catch (ClassNotFoundException e) { System.out.println("Could not load driver"); }
  • 18. 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") ); }
  • 19. SQLJ Passing Host Variables into a SQLJ Statement • Prefix the java variable name with a colon (:) #sql {delete from EMP where SAL >= :amt};
  • 20. SQLJ Dealing with Query Result Sets • SQLJ can be used to execute queries that return a result set . • To process the result set, define an “iterator” type that specifies the data type of each column #sql iterator MyIter(String ENAME, String JOB); class MyClass { MyIter iter; #sql iter = { select ENAME, JOB from EMP }; while(iter.next()) { String ename = iter.ENAME(); String job = iter.JOB(); }
  • 21. 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

Editor's Notes

  • #2: 1
  • #4: Structured Query Language (SQL) is a standardized language used to manipulate database objects and the data they contain. SQL is comprised of several different statements that are used to define, alter, and destroy database objects, as well as add, update, delete, and retrieve data values. However, SQL is nonprocedural, and therefore is not a general-purpose programming language. (SQL statements are executed by the DB2 Database Manager, not by the operating system.) As a result, database applications are usually developed by combining the decision and sequence control of a high-level programming language with the data storage, manipulation, and retrieval capabilities of SQL. Several methods are available for merging SQL with a high-level programming language, but 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.
  • #6: When the preprocessor (a special tool known as the SQL precompiler) encounters these keywords, it replaces the text that follows (until a semicolon (;) or the keywords END-EXEC is found) with a DB2 UDB-specific function call that forwards the specified SQL statement to the DB2 Database Manager for processing.
  • #7: Host variables that transfer data to a database are known as input host variables, while host variables that receive data from a database are known as output host variables. Regardless of whether a host variable is used for input or output, its attributes must be appropriate for the context in which it is used. Therefore, you must define host variables in such a way that their data types and lengths are compatible with the data types and lengths of the columns they are intended to work with
  • #8: A declare section can be coded anywhere high-level programming language variable declarations can be coded in a source code file. Although a source code file typically contains only one declare section, multiple declare sections are allowed.
  • #13: a program accepts SQL statements from the keyboard at run-time. Generally, dynamic SQL statements are well suited for applications that interact with a rapidly changing database or that allow users to define and execute ad-hoc queries.
  • #14: JDBC is an API that enables database access from Java programs
  • #15: SQLJ is passed through a precompiler Checks SQL against the database Generates Java code with JDBC calls
  • #16: JDBC needs: explicit statement handles explicit setXxx binds explicit connection
  • #18: JDBC is an API that enables database access from Java programs
  • #19: JDBC is an API that enables database access from Java programs
  • #20: JDBC is an API that enables database access from Java programs
  • #21: JDBC is an API that enables database access from Java programs
  翻译: