Database Application Development

Database Application Development. Chapter 6 Sections 6.1 – 6.3.5. Overview. Concepts covered in this lecture: SQL in application code Embedded SQL Cursors Dynamic SQL JDBC SQLJ Stored procedures. SQL in Application Code.

Share Presentation

Embed Code

Link

Download Presentation

jdbc architecture
sql string
short c rating
executing sql
boolean
exec sql begin

kane

kane + Follow

Download Presentation

Database Application Development

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

Presentation Transcript

Database Application Development Chapter 6 Sections 6.1 – 6.3.5
Overview Concepts covered in this lecture: • SQL in application code • Embedded SQL • Cursors • Dynamic SQL • JDBC • SQLJ • Stored procedures
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)
SQL in Application Code (Cont'd.) Impedance mismatch: • SQL relations are (multi-) sets of records, with no a priori bound on the number of records. No such data structure exist traditionally in procedural programming languages such as C++. (Though now: STL) • SQL supports a mechanism called a cursor to handle this. • A cursor is an additional construct used to bridge the gap caused by the impedance mismatch.
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 issues with variables: (i) type correspondence (solved by casting/explicit correspondence), and (ii) impedance mismatch (solved by the cursor mechanism). • 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)
Embedded SQL: Statement • 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 • Statements:EXEC SQL Statement;
Embedded SQL: Statement (Cont’d) • Example: • Insert a row with values from variables of the host language: EXEC SQL INSERT INTO Sailors VALUES (:c_sname, :c_sid, :c_rating, :c_age); • Special command for checking for errors after each statement: EXEC SQL WHENEVER [SQLERROR|NOT FOUND] [CONTINUE|GOTO smt]
Cursors: Intuition • Can declare a cursor on a relation or query statement (which generates a relation). • Can open a declared cursor, and repeatedly fetch a tuple, then move the cursor, until all tuples have been retrieved. • Can use a special clause, called ORDER BY, in queries that are accessed through a cursor, to control the order in which tuples are returned. • Fields in ORDER BY clause must also appear in SELECT clause. • The ORDER BY clause, which orders answer tuples, is only allowed in the context of a cursor. • Can finally close an open cursor. • Can also use a cursor to modify/delete single tuples. However, INSERT, DELETE, and UPDATE usually don’t require cursors.
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 • sinfo is the cursor. • Note that it is illegal to replace S.sname by, say, S.sid in the ORDER BY clause! (Why?) • Can we add S.sid to the SELECT clause and replace S.sname by S.sid in the ORDER BY clause?
Cursors: Syntax • General syntax for declaring cursors: DECLARE cursorname [INSENSITIVE] [SCROLL] CURSOR [WITH HOLD] FOR query [ORDER BY order-item-list] [FOR READ ONLY | FOR UPDATE] • INSENSITIVE: private copy of cursor SCROLL: allows more than basic FETCH options WITH HOLD: not closed when transaction commits • Variant of UPDATE command for cursors: UPDATE table SET expression WHERE CURRENT OF cursorname
Embedding SQL in C: An Example char SQLSTATE[6]; EXEC SQL BEGIN DECLARE SECTION char c_sname[20]; short c_minrating; float c_age; EXEC SQL END DECLARE SECTION c_minrating = random(); EXEC SQL DECLARE sinfo CURSOR FOR SELECT S.sname, S.age FROM Sailors S WHERE S.rating > :c_minrating ORDER BY S.sname; do < EXEC SQL FETCH sinfo INTO :c_sname, :c_age; printf(“%s is %d years old\n”, c_sname, c_age); >while (SQLSTATE != ‘02000’); EXEC SQL CLOSE sinfo;
Dynamic SQL • Embedded SQL statements are known at compile time. • SQL query strings are not always known at compile time (e.g., spreadsheet, graphical DBMS frontend): Allow construction of SQL statements on-the-fly! • Two main commands: PREPARE sql_cmd FROM sql_string EXECUTE sql_cmd • Example: char c_sqlstring[]= 5”>; EXEC SQL PREPARE readytogo FROM :c_sqlstring; EXEC SQL EXECUTE readytogo;
Database APIs: 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 • 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
JDBC: Architecture • Four architectural 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 (Cont'd.) 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 via 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. Direct 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.
JDBC Classes and Interfaces Steps to submit a database query: • Load the JDBC driver • Connect to the data source • Execute SQL statements
JDBC Driver Management • All drivers are managed by the DriverManager class • Loading a JDBC driver (3 ways): • In the Java code:Class.forName(“oracle/jdbc.driver.Oracledriver”); Returns driver class object given its complete name. • When starting the Java application (at command line):-Djdbc.drivers=oracle/jdbc.driver • Can explicitly instantiate a driver.
Connections in JDBC We interact with a data source through a session which is started with the creation of a connection object. Each connection identifies a logical session et is started through a JDBC URL. • JDBC URL (i.e. a URL using the JDBC protocol): jdbc:: Example: Establish a connection to an Oracle DB: String url=“jdbc:oracle:www.bookstore.com:3083”; Connection con; try < con = DriverManager.getConnection(url,usedId,password); >catch SQLException excpt
Connection Class Interface • public int getTransactionIsolation() andvoid setTransactionIsolation(int level)Sets isolation level for the current connection. • public Boolean getReadOnly() andvoid setReadOnly(boolean b)Specifies whether transactions in this connection are read-only • public boolean getAutoCommit() andvoid 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.
Executing SQL Statements • Three different ways of executing SQL statements (i.e. 3 classes of SQL stmt objects): • Statement (allows to query DBs with both static and dynamic SQL statements) • PreparedStatement (semi-static SQL statements) • CallableStatement (stored procedures) • PreparedStatement class:dynamically generates precompiled, parameterized SQL statements: • Structure is fixed • Values of parameters are determined at run-time
Executing SQL Statements (Cont'd.) String sql=“INSERT INTO Sailors VALUES(. )”; PreparedStatment pstmt=con.prepareStatement(sql); pstmt.clearParameters(); pstmt.setInt(1,sid); // Assume sid, sname, … pstmt.setString(2,sname); // are Java vars containing pstmt.setInt(3, rating); // values to be inserted pstmt.setFloat(4,age); // we know that no rows are returned, thus // we use executeUpdate() int numRows = pstmt.executeUpdate();
Result Sets • PreparedStatement.executeUpdate only returns the number of affected records • PreparedStatement.executeQuery returns data, encapsulated in a ResultSet object (a cursor) ResultSet rs=pstmt.executeQuery(sql); // rs is now a cursor; next() retrieves the next row While (rs.next()) < // process the data >
Result Sets (Cont'd.) A ResultSet is a very powerful cursor: • previous(): moves one row back • absolute(int num): moves to the row with the specified number • relative (int num): moves forward or backward • first() and last()
Matching Java and SQL Data Types
JDBC: Exceptions and Warnings • Most of java.sql can throw an SQLException if an error occurs • SQLWarning is a subclass of SQLException; not as severe (they are not thrown and their existence has to be explicitly tested) • Methods in SQLException: getMessage(), getSQLState(), getErrorCode(), getNextException()
Warning and Exceptions (Cont'd.) try < stmt=con.createStatement(); warning=con.getWarnings(); while(warning != null) < // handle SQLWarnings; warning = warning.getNextWarning(): >con.clearWarnings(); stmt.executeUpdate(queryString); warning = con.getWarnings(); … > //end try catch( SQLException SQLe) < // handle the exception >

Related
More by User

Database Application Development

Database Application Development. Overview. Concepts: SQL in application code Embedded SQL Cursors Dynamic SQL Stored procedures. SQL in Application Code. SQL commands can be called from within a host language (e.g., C++ or Java ) program.

382 views • 21 slides

Advanced Database Application Development

Performance Tuning Performance Benchmarks Standardization E-Commerce Legacy Systems. Advanced Database Application Development. Adjusting various parameters and design choices to improve system performance for a specific application. Tuning is best done by identifying bottlenecks, and

608 views • 47 slides

Review Database Application Development

Review Database Application Development. ER-diagram. Forms. Access Database Development. Reports. Queries. ER-diagram. Entity types Strong entity type Weak entity type Relationships Cardinality constraints Participation constraints Attributes atomic attributes

275 views • 17 slides

Database Application Development

Database Application Development. Chpt 6. Xin Zhang. Embedded SQL. 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).

196 views • 10 slides

DAT602 Database Application Development

DAT602 Database Application Development . Lecture 10 Manipulate Data by Using JDBC. Database Application Development - Lecture 10. In this lecture, we’ll discuss about how to manipulate data by using JDBC in Java application. - insert - update - delete.

285 views • 20 slides

DAT602 Database Application Development

DAT602 Database Application Development . Lecture 6 JAVA Swing. Database Application Development - Lecture 6. Library of JAVA. In JAVA’s library, different packages contain classes with different functions. java.lang java.io java.util javax.swing …

392 views • 30 slides

Database application development

Database application development. INF08104: Database Systems Brian Davison , 2013/14. Agenda. Objects and relations Embedded SQL operations Interoperability. The role of databases. A specialist component in a ranger system Support for multiple concurrent users

283 views • 15 slides

Database Application Development

Database Application Development. Chapter 6. Overview. Concepts covered in this lecture: SQL in application code Embedded SQL Cursors Dynamic SQL JDBC SQLJ ODBC Stored procedures. Justification for access to databases via programming languages :.

656 views • 56 slides

The Database Application Development Process

The Database Application Development Process. Outline. Information Engineering Client Server (2 tier) vs. Web Application (3 tier) software systems Functional Decomposition (analysis technique) Planning Matrix (analysis technique) Enterprise Data Model vs. Project (system level) data model

334 views • 19 slides

Database Application Development

Database Application Development. Chapter 6. Outline. How do applications connect to a database? App  DBMS: Embedded SQL App  Driver  DBMS: JDBC/ODBC or SQLJ App Server Driver DBMS: JDBC/ODBC Note: architecture generally transparent to app.

461 views • 21 slides

Database Application Development

Database Application Development. R&G Chapter 6 Lecture 16. Administrivia. Exams should be returned by next week Grades might be on-line by tomorrow Homework 3 should be available next week Due date will be adjusted accordingly. Review. Last time: How DBMSs are used in Web Applications

507 views • 43 slides

Database Application Development

Database Application Development. Chapter 6. Overview. Concepts covered in this lecture: SQL in application code Embedded SQL Cursors Dynamic SQL JDBC SQLJ Stored procedures. SQL in Application Code.

577 views • 40 slides

Chapter 8: database application development

Modern Database Management. Chapter 8: database application development. Define terms Explain three components of client/server systems: presentation, processing, and storage Distinguish between two-tier and three-tier architectures

808 views • 41 slides

Database Application Development

Database Application Development. CS348 Introduction to Database Management Systems Fall 2014. Communicating with a Database Sever. How have you interacted with a database server? Web interface Management console that comes with the DBMS Your custom written program (java/C++)

536 views • 31 slides

DATABASE APPLICATION DEVELOPMENT SAK 3408

DATABASE APPLICATION DEVELOPMENT SAK 3408. The Web and DBMS. The Web as Database Application Platform. Providing users with an interface to one or more database. Requirements for Web-DBMS Integration. The ability to access valuable information in a secure manner.

243 views • 10 slides

DAT602 Database Application Development

DAT602 Database Application Development. Lecture 17&18 B/S model database application. Database Application Development - Lecture 17&18. Here we still use E-Library as our example . We are going to use Netbeans as our IDE. Database Application Development - Lecture 17&18.

304 views • 18 slides

Database Application Development

Database Application Development. Overview. Concepts: SQL in application code Embedded SQL Cursors Dynamic SQL JDBC SQLJ Stored procedures. SQL in Application Code. SQL commands can be called from within a host language (e.g., C++ or Java ) program.

588 views • 45 slides

DAT602 Database Application Development

DAT602 Database Application Development. Lecture 12 C/S Model Database Application. Database Application Development - Lecture 12. In this lecture, you will see how to create a simple Client / Server model database application.

369 views • 26 slides

Database Application Development

273 views • 26 slides

BICS546 Client/Server Database Application Development

BICS546 Client/Server Database Application Development. Evolution of Multi-User DBMS Architectures. Teleprocessing: One computer with a single CPU and a number of dumb terminals. File-Server:

295 views • 28 slides

Database Application Development

Database Application Development. Chapter 6 PSM (Stored Procedures). Stored Procedures. What is a stored procedure: SQL allows you to define procedures and functions and store in the DB server Program executed through single SQL statement Executed in process space by the database server

274 views • 24 slides