Transcript EXEC SQL

Database Application Development
SQL In Application Code :
1. Embedded SQL
2. Database API’s
3. SQLJ
SQL in Application Code
 SQL commands can be called from within a host
language (e.g., C++ or Java) program.
 SQL statements can refer to host variables (including
special variables used to return status).
 Must include a statement to connect to the right database
 Two main integration approaches:
 Embed SQL in the host language (Embedded SQL, SQLJ)
 Create special API to call SQL commands (JDBC)
Embedded SQL
 Approach: Embed 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.
 Language constructs:
 Connecting to a database:
EXEC SQL CONNECT
 Declaring variables:
EXEC SQL BEGIN (END) DECLARE SECTION
Embedded SQL
 Language Constructs (cntd.)
 Statements:
EXEC SQL Statement;
 Variables: (Two special “error” variables, one of them
must be declared)
SQLCODE (long, is negative if an error has
occurred)
SQLSTATE (char[6], predefined codes for common
errors)
Database API’s
 Alternative to embedding
 Rather than modify compiler, add library with database
calls (API)
 Special standardized interface: Procedures/objects
 Pass SQL strings from language, presents result sets in a
language-friendly way
Database API’s
 Sun’s JDBC: Java API
 Supposedly DBMS-neutral, a “driver” traps the calls and
translates them into DBMS specific code
 Database can be across a network
 DBMS independent both at the source code and
executable level.
JDBC Architecture
JDBC architecture has four components:
 Application (initiates and terminates connections,
submits SQL statements)
 Driver manager (load JDBC driver)
 Driver (connects to data source, transmits requests and
returns/translates results and error codes)
 Data source (processes SQL statements)
JDBC Architecture
Steps to submit a database query:
 Load the JDBC driver
 Connect to the data source
 Execute SQL statements
Example Code
 Step 1: Include necessary Java packages
import java.sql.*;
 Step 2: Load the corresponding JDBC driver for the data
source you want to connect.
Class.forName("com.mysql.jdbc.Driver");
Example Code
 We interact with a data source through sessions.
 Each connection identifies a logical session.
 Connnections are specified through a JDBC URL in the
following form:
jdbc:<subprotocol>:<otherParameters>
e.g. :
String url ="jdbc:oracle://knuth.ug.bcc.bilkent.edu.tr/test"
Example Code
 Now, we want to create and execute SQL statements. In
JDBC, there are three different ways of executing statements:
Statement, PreparedStatement and Callable Statement.
 We can simply use a Statement for a query, and use ResultSet
object to handle the query result.
 First, let’s declare and create a Statement object.
Statement selectStmt;
selectStmt = con.createStatement();
Example Code
 Notice that we will handle the result by using a ResultSet
object, similar to a cursor.
ResultSet rs;
 Execute the query
rs = selectStmt.executeQuery("SELECT * FROM
employee");
Example Code
 Now, you can read values from each tuple and do something
with them
while(rs.next())
{
System.out.print(“Emp no:” + rs.getInt(1) +"
");
System.out.print(“Emp name:”+
rs.getString(2)”+ " ");
…
}
Example Code
 For a PreparedStatement, the structure is fixed but values of
parameters are determined at the run time.
 First, determine your query structure
String sql_string= "INSERT INTO employee VALUES
(?,?,?,?,?,?,?,?,?)“
 Create the prepared statement
PreparedStatement pstmt = con.prepareStatement
(sql_string);
Example Code
 Now, we instantiate the parameters with values
pstmt.clearParameters();
pstmt.setInt(1,100);
pstmt.setString(2, “john”);
pstmt.setString(3, “manager”);
 Submit the query to the data source.
pstmt.executeUpdate();
SQL J
 Complements JDBC with a (semi-)static query model.
 Compiler can perform syntax checks, strong type checks,
consistency of the query with the schema.
 SQLJ is a part of the SQL standard whereas embedded SQL
is vendor-specific.
Internet Applications
URI
 URI (Uniform Resource Identifier): Uniform
naming schema to identify resources on the Internet.
e.g : http://www.cs.wisc.edu/~dbbook/index.html
 URI has three parts:
 Naming schema (http)
 Name of the host computer (www.cs.wisc.edu)
 Name of the resource (~dbbook/index.html)
 URLs are a subset of URIs
HTTP (HyperText Transfer Protocol)
 Client (web browser) sends HTTP request to server
 Server receives request and replies
 Client receives reply; makes new requests
HTTP (HyperText Transfer Protocol)
Note that, HTTP is stateless:
 No “sessions”
 Every message is completely self-contained
 No previous interaction is “remembered” by the protocol
Web Data Formats
 HTML:
 The presentation language for the Internet.
 HTML is a markup language. Commands are tags
 XML: A self-describing, hierarchal data model
 DTD: Standardizing schemas for Xml
Web Data Formats
XML – The Extensible Markup Language
 Language: A way of communicating information
 Markup: Notes or meta-data that describe your data or
language
 Extensible: Limitless ability to define new languages or
data sets
Web Data Formats
 The point is that you can include your data and a description
of what the data represents. This is useful for defining your
own language or protocol
 e.g. Chemical Markup Language
<molecule>
<weight>234.5</weight>
<Spectra>…</Spectra>
<Figures>…</Figures>
</molecule>
Components of Data-Intensive Systems
Three separate types of functionality:
 Data management
 Application logic
 Presentation
The system architecture determines whether these three
components reside on a single system (“tier) or are
distributed across several tiers.
The Three-Tier Architecture
The Three Layers
 Presentation tier
 Primary interface to the user
 Needs to adapt to different display devices (PC, PDA, cell
phone, voice access?)
 Middle tier
 Implements business logic (implements complex actions,
maintains state between different steps of a workflow)
 Accesses different data management systems
 Data management tier
 One or more standard database management systems
The Three-Tier Architecture
A course enrollment system:
 Database System: Student info, course info, instructor
info, course availability, pre-requisites, etc.
 Application Server: Logic to add a course, drop a course,
create a new course, etc.
 Client Program Log in different users (students, staff,
faculty), display forms and human-readable output
Presentation Tier
 HTML Forms: How to pass data to the middle tier
 JavaScript: Simple functionality at the presentation tier
 Style sheets: Separating data from formatting
Presentation Tier - HTML Forms
 Common way to communicate data from client to middle
tier. General format of a form:
< FORM ACTION = “page.jsp” METHOD= “GET” NAME
= “LoginForm” >
…
</FORM>
 Components of an HTML FORM tag:
 ACTION: Specifies URI that handles the content
 METHOD: Specifies HTTP GET or POST method
 NAME: Name of the form; can be used in client-side scripts to
refer to the form.
Presentation Tier - HTML Forms
 Inside HTML forms, there may be the INPUT tag. It has
the following attributes:
 TYPE: text (text input field), password (text input field where
input is displayed as stars), reset (resets all input fields)
 NAME: symbolic name, used to identify field value at the
middle tier.
 VALUE: default value
Presentation Tier - HTML Forms
 Example : A form that has two text input fields, one submit
and one reset button.
<form method="POST" action="Welcome.jsp">
<input type="text" name="userid">
<input type="password" name="password">
<input type="submit" value="Login“ name="submit">
<input type=“reset” value=“Clear”>
</form>
Presentation Tier - HTML Forms
Passing Arguments
Two methods: GET and POST. Form contents go into the
submitted URI.
Structure:
action?name1=value1&name2=value2&name3=value3
Action: Name of the URI specified in the form. Note that the
page named action needs to be a program, script, or page that will
process the user input
(name,value)-pairs: These come from INPUT fields in the
form; empty fields have empty values (“name=“)
e.g. Welcome.jsp?userid=john&password=johnpw
Presentation Tier - JavaScript
 Goal: Add functionality to the presentation tier. Sample
applications:
 Detect browser type and load browser-specific page
 Form validation: Validate form input fields
 Browser control: Open new windows, close existing windows
(example: pop-up ads)
 Usually embedded directly inside the HTML with the
<SCRIPT>… </SCRIPT> tag.
Middle Tier
 Encodes business logic
 Connects to database system(s)
 Accepts form input from the presentation tier
 Generates output for the presentation tier.
Middle Tier
Technologies:
 CGI: Protocol for passing arguments to programs
running at the middle tier
 Application servers: Runtime environment at the middle
tier
 Servlets: Java programs at the middle tier
 JavaServerPages: Java scripts at the middle tier
 PHP: PHP scripts at the middle tier.
Middle Tier - PHP
 PHP is a widely-used general-purpose scripting language that
is especially suited for Web development and can be
embedded into HTML.
<html>
<head>
<title>PHP Test</title>
</head>
<body>
<?php
echo '<p> Hello World</p>';
?>
</body>
</html>
Middle Tier - PHP
<html>
<head><title> EXAMPLE PAGE</title></head>
<body>
Hello World!!! This is my first PHP script as a CS352
student.... <br> <br>
Here is the list of all employees in our company
database: <br> <br>
<?php .....
Middle Tier - PHP
<?php
// Connect to server where your DB is, login and password to the
DBMS (Here it is MySQL)
$connection =
mysql_connect('139.179.21.74','company_user','123');
// Give an error if you can not connect
if(!connection)
die("Could not connect");
....
Middle Tier - PHP
// Choose the database among those databases to use. As a
//company_user, we are allowed to use the company database.
mysql_select_db('company');
Middle Tier - PHP
// Prepare a query string, just like we have done in Java
$query = "SELECT * FROM Employee";
// Send & execute the query, again this is similar to saying:
// "ResultSet rs =stmt.executeQuery($query)" in our java
//example
$result = mysql_query($query);
// This counts the number of tuples in the result
$result_no = mysql_num_rows($result);
Middle Tier - PHP
// if some results are found, write them
if ($result_no >0)
{
for($i = 1; $i<=$result_no; $i++)
{
// Fetch the contents of current record into the variables $employee_no,
etc.
list($employee_no, $name…) = mysql_fetch_row($result);
// This is to write the output to HTML so that it is viewed by the browser
echo "NO: $employee_no NAME: $name SKILL: $skill …“;
echo "<br>";
}
}
Middle Tier - PHP
// If no results are found
else
echo “no employee in the company database”
// Close the php tag
?>
//Close the body and html tags
</body>
</html>
Maintaining State in HTTP
HTTP is stateless.
Advantages
 Easy to use: don’t need anything
 Great for static-information applications
 Requires no extra memory space
Disadvantages
 No record of previous requests means
 No shopping baskets or user logins
 No custom or dynamic content
 Security is more difficult to implement
Maintaining State in HTTP
 Server-side state: Information is stored in a database,
or in the application layer’s local memory
 Client-side state: Information is stored on the client’s
computer in the form of a cookie
 Hidden state: Information is hidden within
dynamically created web pages
 The material discussed in this document is summarized from the
slides of the textbook (Chapter 7)“Database Management Systems” by
Ramakrishnan and Gehrke, 3rd Edition.
The material discussed in this document is
summarized from the slides of the textbook
(Chapter 7)“Database Management
Systems” by Ramakrishnan and Gehrke,
3rd Edition.