Impedance Mismatch Problem
Problem: How to connect SQL statements with conventional
programming languages
• Different models of language
• SQL query returns relation (no pgmg-language access mechanisms)
• Data structures in pgmg languages (with access mechanisms)
• Passing data between these two models is not straightforward
• Both languages have their strengths
EmbeddedSQL: 1
System Aspects of SQL
SQL statements are usually part of some larger piece of software
Embedded SQL
approach
Host language
+
Embedded SQL
Changes SQL into
something familiar
to host language
Preprocessor
Call-level interface
(CLI) approach
Host language
+
Function Calls
Host-language Compiler
Provided by DBMS
vendor
SQL library
Object-code program
EmbeddedSQL: 2
SQL/Host Language Interface
• Shared variables
– Special Declare Section
– Syntax
• A variable with a colon prefix refers to data in the result of
an SQL statement.
• A variable with no colon prefix refers to data in as usual in a
host language
• EXEC SQL
– Tells preprocessor that SQL code is coming
– Followed by other SQL statements
EmbeddedSQL: 3
Example
EmbeddedSQL: 4
JDBC
• Java Database Connectivity
• Call-Level-Interface (CLI) with Java’s OO style
EmbeddedSQL: 5
JDBC API
BASIC CLASSES:
–
Statement Class
•
•
sends SQL statements to DBMS
Useful Methods:
int executeUpdate(String sql_statement);
»
Used to execute DDL SQL statement, ex: creating, altering tables.
ResultSet executeQuery(String sql_statement);
»
–
used to execute common SQL statements, such as SELECTs
ResultSet Class
•
•
•
Manipulates rows in a table
Useful Methods:
beforeFirst();
//moves cursor to front of result set
boolean next();
//moves the cursor to the next row
The "cursor" starts just above first row, so call next() to get to the first row
EmbeddedSQL: 6
JDBC API
Establish Connection
1. Load Driver:
For example: using JDBC-ODBC Bridge driver:
Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver“);
2. Make Connection
Connection con = DriverManager.GetConnection(url, "myLogin",
"myPassword");
EmbeddedSQL: 7
JDBC API
• Create Tables
String makeTable = “create table Students” +
"(Name varchar(32), ID integer)";
Statement stmt = con.createStatement();
stmt.executeUpdate(makeTable);
• Populate Tables
Statement stmt = con.createStatement()
stmt.executeUpdate(“insert into students values ('Mark', 523)");
EmbeddedSQL: 8
JDBC API
Retrieve Data
String query = “select Name, ID from Students";
ResultSet rs = stmt.executeQuery(query);
while(rs.next())
{
String s = rs.getString("Name");
int n = rs.getInt(“ID");
}
EmbeddedSQL: 9
Linking Servers: Same idea
extended to multiple kinds of data
sources on remote servers
Linking Servers
EmbeddedSQL: 10
PHP: Same Idea as JDBC
Project 1
EmbeddedSQL: 11
Descargar

Design Objectives - Brigham Young University