Database Application Development - CS Department

Database Application Development - CS Department

Database Application Development Outline SQL in application code Embedded SQL Cursors Dynamic SQL JDBC SQLJ Stored procedures This chapter How to use DBMS from applications Done!

SQL in Application Code Program 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). Sailors :c_minrating = 6 SELECT S.sname, S.age FROM Sailors S WHERE S.rating > :c_minrating sid 12 24 36 45

sname dustin brandon emily trevor rating 9 6 7 6 age 19 27 32 21 SQL Integration Approaches Two main integration approaches: Embed SQL in the host language (Embedded SQL, SQLJ) Create special API to call SQL commands (JDBC) Embedded SQL Language Constructs

1) Connecting to a database: EXEC SQL CONNECT 2) Declaring host variables: Must include a statement to connect to the right database. EXEC SQL BEGIN DECLARE SECTION EXEC SQL END DECLARE SECTION 3) Statements: EXEC SQL Statement; Embedded SQL 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. Without SQL

Computer program EXEC SQL SELECT FROM WHERE Computer program Preprocessor 1 Preprocessor API CALL Native API Native API 1 DBMS Using Host Variables in SQL We assume C in our discussion. Minor differences in different host languages SQL statements can refer to variables in host

program Such host variables must be declared in the DECLARE SECTION of SQL, and they are prefixed by a colon (:) in SQL statements Embedded SQL: VARIABLES Host variables declared in host program EXEC SQL BEGIN DECLARE SECTION char c_sname[20]; /* CHARACTER(20) long c_sid; /* INTEGER short c_rating; /* SMALLINT float c_age; /* REAL EXEC SQL END DECLARE SECTION EXEC SQL In the INSERT INTO Sailors program

VALUES (:c_sname, :c_sid, :c_rating, :c_age); Host variable prefixed by : Embedded SQL: Error Variables Two special variables for reporting errors: SQLCODE (older) A negative value to indicate a particular error condition The appropriate C type is long SQLSTATE (SQL-92 standard) Predefined codes for common errors Appropriate C type is char[6] (a character string of five letters long with a null character at the end to terminate the string) One of these two variables must be declared. We assume SQLSTATE Impedance Mismatch SQL relations are 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. Database How big ? Data structure Computation Computer program *STL (Standard Template Library) is a generic C++ library that provides many basic algorithms and data structures of computer science) Cursors Can declare a cursor on a relation or query statement (which generates a relation). Can open a cursor, and repeatedly fetch a tuple then move the cursor, until all tuples have been retrieved. Can use an ORDER BY clause in the query to

control the order in which tuples are returned. Can also modify/delete tuple pointed to by a cursor. Cursor that gets names of sailors whove reserved a red boat, in alphabetical order Cursor name 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 A cursor SQL Result Jessica Jessica Ashley Ashley Michael Michael Matthew Matthew

Cursor that gets names of sailors whove 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 Fields in ORDER BY clause must also appear in SELECT clause Sorted by name Ashley Ashley Jessica Jessica SQL Result Jessica Jessica ORDER BY sname

Ashley Ashley Matthew Matthew Michael Michael Michael Michael Matthew Matthew Relations We will use these table definitions in this module Sailors(sid: integer, sname: string, rating: integer, age: real) Boats(bid: integer, bname: string, color: string) Reserves(sid: integer, bid: integer, day: date) Make reservations Embedding SQL in C

Print names and ages of sailors of a certain rating level, sorted by names. Embedding SQL in C char SQLSTATE[6]; /* error variable EXEC SQL BEGIN DECLARE SECTION char c_sname[20]; short c_minrating; EXEC SQL END DECLARE SECTION c_minrating = random(); float c_age; /* initialize c_minrating EXEC SQL DECLARE sinfo CURSOR FOR /* declare cursor SELECT S.sname, S.age FROM Sailors S WHERE S.rating > :c_minrating /* retrieve good sailors ORDER BY S.sname; EXEC SQL OPEN sinfo; /* open cursor

do { EXEC SQL FETCH sinfo INTO :c_sname, :c_age; /*fetch cursor printf(%s is %d years old\n, c_sname, c_age); } while (SQLSTATE != 02000); /* no data - no more rows EXEC SQL CLOSE sinfo; /* close cursor Update/Delete Commands Modify the rating value of the row currently pointed to by cursor sinfo UPDATE Sailors S SET S.rating = S.rating + 1 WHERE CURRENT of sinfo; Delete the row currently pointed to by cursor sinfo DELETE Sailors S FROM CURRENT of sinfo; +1

Protecting Against Concurrent Updates EXEC SQL DECLARE sinfoINSENSITIVE INSENSITIVE CURSOR FOR SELECT S.sname /* Retrieve sailor who reserves red boats 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 This application Copy Other applications Using Cursor Private copy The cursor operates over a private copy of the answer rows, i.e., insensitive to concurrent updates

Scrolling Cursors EXEC SQL DECLARE sinfo SCROLL 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 SCROLL The result tuples can be fetch in flexible orders FETCH NEXT/PRIOR: gets the next or previous tuple FETCH FIRST/LAST: gets the first or last tuple FETCH RELATIVE 3 (-3): gets the row 3 rows beyond (prior to) cursor FETCH ABSOLUTE 3 (-3): gets the row 3 rows from the beginning (end) of the result table ABSOLUTE 1 is synonym for FIRST ABSOLUTE -1 is synonym for LAST Read-Only Cursor 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 FOR READ ONLY FOR READ ONLY Any attempt to update or delete through the cursor will cause an error Dynamic SQL SQL query strings are not always known at compile time (e.g., spreadsheet, graphical DBMS frontend). Such application must accept commands from the user; and based on what the user needs, generate appropriate SQL statements. The SQL statements are constructed on-the-fly Example: char c_sqlstring[ ]= {DELETE FROM Sailors WHERE raiting>5}; EXEC SQL PREPARE readytogo FROM :c_sqlstring; EXEC SQL EXECUTE readytogo; Inform SQL system to Instruct SQL system to take the string as query execute the query

Limitation of Embedded SQL Even the source code can be compiled to work with different DBMSs, the final executable works only with one specific DBMS. DBMS-independent only at the source code level 1 EXEC SQL SELECT FROM WHERE 1) DBMS-specific preprocessor transform the Embedded SQL statements into function calls in the host language 2) This translation varies across

DBMSs (API calls vary among different DBMSs) Database specific Database specific Preprocessor 1 Preprocessor 2 API CALL Database specific 2 Native API API Native DBMS

DBMS Database API: Alternative to Embedding ODBC = Open DataBase Connectivity JDBC = Java DataBase Connectivity JDBC is a collection of Java classes and interface that enables database access The classes and interfaces are part of the java.sql package JDBC contains methods for connecting to a remote data source, executing SQL statements, receiving SQL results transaction management, and exception handling Java Application JDBC API

JDBC Driver DBMS java.sql Advantage of API Approach Applications using ODBC or JDBC are DBMS-independent at the source code level and at the level of the executable Call-level API for database access API Approach Embedded SQL Computer program Java Java Application Application

java.sql EXEC SQL SELECT FROM WHERE JDBC API Industry Standard Database specific hidden in lower level JDBC call All in Java JDBC Driver Database specific

Oracle Oracle Native API Preprocessor Preprocessor Computer program Introducing an extra level of indirection: A DBMS-specific driver traps the calls and translates them into DBMS-specific code No preprocessor. Same executable works on different DBMSs without recompiling (need proper drivers) Database specific call API CALL Database

specific Native API Native API DBMS Driver Manager Drivers are registered with a driver manager Drivers are loaded dynamically on demand The application can access several different DBMSs simultaneously Java Application JDBC API

JDBC Driver Manager JDBC Driver 1 JDBC Driver 2 SQL Server Oracle JDBC: Architecture Four architectural components: Application (initiates and terminates connections, submits SQL statements) Driver manager (loads JDBC driver and passes function calls) Driver (connects to data source, transmits requests and returns/ translates results and error codes) Data source (processes SQL

statements) Java Application JDBC API JDBC Driver Manager JDBC Driver 1 JDBC Driver 2 SQL Server SQL Server Oracle Oracle JDBC: Type 1 Driver Bridge: Translates JDBC function calls into

function calls of another non-native API such as ODBC. Extra The application can use JDBC calls to Layer access an ODBC compliant data source. Advantage: no new drivers needed Disadvantage: The additional layer affects performance Client requires the ODBC installation Not good for Web applications Client Java Application JDBC API Type 1 Driver ODBC Driver

DBMS JDBC call ODBC call JDBC: Type 2 Driver Direct translation to native API via non-Java driver: Convert JDBC calls into database-specific C/C++ API calls Drivers typically provided by the database vendor Advantage: Better performance than Type 1 Client Java Application JDBC API NonJava

Type 2 Driver Native API Disadvantage: Native API must be installed in client Not good for Web applications DBMS Call native API directly instead of going through ODBC JDBC: Type 3 Driver Client Move this to another

server Java Application JDBC API Type 2 Driver Native API DBMS JDBC: Type 3 Driver 3-Tier Approach: The driver uses standard network sockets to send commands over the network to a middleware application server The middleware server translates the JDBC requests into databasespecific calls Advantage: Needs only small JDBC driver at each client

Disadvantage: Need to maintain another server Client Java Application JDBC API Type 3 Driver Small JDBC driver JDBC request Middleware Application Server Type 1 Driver Type 2 Driver Type 4 Driver DBMS

Databasespecific call JDBC: Type 4 Driver Direct translation to the Native API via Java Driver: The Java-based driver uses java networking libraries to communicate directly with the database server (i.e., java sockets) The driver translates JDBC calls into native API of the database system Advantage: Pure Java Implementation is all Java Performance is good Disadvantage: Need a different driver for each database (compared to Type 3 Driver)

Client Java Application JDBC API JDBC call Type 4 Driver DBMS Native API call via socket connec -tion JDBC Classes and Interfaces Steps to submit a database query: 1. Load the JDBC driver 2. Connect to the data source

3. Execute SQL statements Java Application JDBC API 3 JDBC Driver Manager JDBC Driver 1 1 JDBC Driver 2 2 SQL Server Oracle JDBC Driver Management

DriverManager class: Maintains a list of currently loaded drivers Has methods to enable dynamic addition and deletion of drivers Two ways of loading a JDBC driver: Java Application JDBC API JDBC Driver Manager JDBC Driver 1 JDBC Driver 2 DBMS1

DBMS2 1. In the Java code: Class.forName(oracle/jdbc.driver.Oracledriver); /* This method loads an instance of the driver class 2. Enter at command line when starting the Java application: -Djdbc.drivers=oracle/jdbc.driver Connections in JDBC We interact with a data source through sessions. A session is started through creation of a Connection object Connections are specified through a URL that uses the jdbc protocol - jdbc:: Each connection identifies a logical session with a data source Differe nt driv Port Host ers have s lightly differe String url=jdbc:oracle:www.bookstore.com:3083; nt URL

format - chec Connection con; k the try{ docum entatio n con = DriverManager.getConnection(url,userId,password); Example: Discuss later } catch(SQLException excpt) { } ACID Properties A transaction is a collection of actions with the following ACID properties: Atomicity: A transactions changes to the state are atomic either all happen or non happen. Consistency: A transaction is a correct transformation of the state.

Isolation: Even though transaction execute concurrently, it appears to each transaction, T, that other executed either before or after T, but not both. Durability: Once a transaction completes successfully, its changes to the state survive failures (transactions effects are durable). 35 Higher-Level Protected Actions (Transactions) Since unprotected actions can be undone, they can be included in a higher-level operation (i.e., transaction), which as a whole has the ACID properties. Transaction (protected) Begin Work unproted action unproted action unproted action unproted action Commit Work

36 Connection Class Interface (1) void setTransactionIsolation(int level) Sets isolation level for the current connection public int getTransactionIsolation() Multiple Get isolation level of the current connection transactions update the same data item More concurrency Four isolation levels Degree 0 - unrepeatable reads, dirty reads, lost updates Degree 1 - unrepeatable reads, dirty reads

Degree 2 - unrepeatable reads Degree 3 - true isolation As long as applications know what they are doing, better performance can be achieved without causing anomalies Reading updates made by transaction that has not finished Computing aggregate function while other applications update the data Example: Cursor stability applications do not repeat read operations anyway !

Connection Class Interface (1) void setTransactionIsolation(int level) Sets isolation level for the current connection public int getTransactionIsolation() Get isolation level of the current connection void setReadOnly(boolean b) Specifies whether transactions are read-only public boolean getReadOnly() Tests if transaction mode is read-only void 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 getAutoCommit() Test if autocommit is set Connection Class Interface (2) public boolean isClosed() Checks whether connection is still open.

connectionname.close() Close the connection connectionname Executing SQL Statements Three different ways of executing SQL statements: 1. Statement (both static and dynamic SQL statements) 2. PreparedStatement (semi-static SQL statements) 3. CallableStatment (stored procedures) PreparedStatement class: Used to create precompiled, parameterized SQL statements SQL structure is fixed Values of parameters are determined at run-time PreparedStatement Object Four parameters Place holders String sql=INSERT INTO Sailors VALUES(?,?,?,?); PreparedStatment Connection name

pstmt=con.prepareStatement(sql); pstmt.clearParameters(); Good style to always clear pstmt.setInt(1,sid); pstmt.setString(2,sname); Setting parameter values sid, sname, rating, age are java pstmt.setInt(3, rating); variables pstmt.setFloat(4,age); int numRows = pstmt.executeUpdate(); Use executeUpdate() when no rows Number of rows modified Use executeUpdate() when no rows are returned ResultSet PreparedStatement.executeUpdate only returns the number of affected records (last example) PreparedStatement.executeQuery returns data, encapsulated in a ResultSet object ResultSet is similar to a cursor

Allows us to read one row at a time Initially, the ResultSet is positioned before the first row Use next() to read the next row next() returns false if there are no more rows ResultSet Example PreparedStatement object ResultSet rs=pstmt.executeQuery(sql); // rs is now a cursor While (rs.next()) { Use while loop to process one tuple // process the data each iteration until end of result set } Common ResultSet Methods (1) POSITIONING THE CURSOR next() Move to next row

previous() Moves back one row absolute(int num) Moves to the row with the specified number relative(int num) Moves forward or backward (if negative) first() Moves to the first row Last() Moves to the last row Common ResultSet Methods (2) RETRIEVE VALUES FROM COLUMNS

by NAME or INDEX getString(string columnName): Retrieves the value of designated column in current row getInt(int columnIndex) Retrieves the value of designated column in current row getFloat (string columnName) Retrieves the value of designated column in current row Matching Java and SQL Data Types SQL Type BIT CHAR VARCHAR

DOUBLE FLOAT INTEGER REAL DATE TIME TIMESTAMP Java class Boolean String String Double Double Integer Double java.sql.Date java.sql.Time java.sql.TimeStamp ResultSet get method getBoolean() getString() getString() getDouble()

getDouble() getInt() getFloat() getDate() getTime() getTimestamp() SQL Data Types BIT CHAR(n) A boolean value A character string of fixed length n VARCHAR(n) A variable-length character string with a maximum length n DOUBLE A double-precision floating point value FLOAT(p)

A floating point value with a precision value p INTEGER A 32-bit signed integer value REAL DATE TIME TIMESTAMP A high precision numeric value A day/month/year value A time of day (hour, minutes, second) value A day/month/year/hour/minute/second value Statement Object Another Way to Execute an SQL Statement Three different ways of executing SQL statements: 1. Statement (both static and dynamic SQL statements) 2. PreparedStatement (semi-static SQL statements) 3. CallableStatment (stored procedures) Statement object

String ResultSet stmt = con.createStatement(); // create an empty statement query = "SELECT name, rating FROM Sailors"; rs = stmt.executeQuery(query); Note: The query can be dynamically created Review: Throwable Class Throwable object: can have an associated message that provides more detail about the particular error or exception GetMessage() GetMessage() that is being thrown Throwable class: is the superclass of all errors and exceptions in the Java language getMessage(): returns the error message string of the

throwable object Object Throwable Exception Error Exception/Warning Class Hierarchy GetMessage() returns the error message string of the throwable object GetSQLState() returns an SQLState identifier according to SQL 99 GetWarning() retrieves SQL warning if they exist Object Throwable Most of the methods in java.sql can throw

an exception of type SQLException if an error occurs. Exception SQLException SQLWarning Java.SQL JDBC: Exceptions SQLException has the following methods: public String getMessage() is inherited from the Throwable class public String getSQLState() returns an SQLState identifier according to SQL 99 public int getErrorCode() retrieves a vendor-specific error code public SQLException getNextException() gets the next exception chained to this SQLException object Catch the Exception Contains code that might throw the

exception The class name of the exception we want to handle, e.g., SQLException try { body-code } catch ( exception-classname variable-name) { handler-code } Contains the code to execute if the exception occurs The variable that will hold the exception object JDBC: Warnings SQLWarning is a subclass of SQLException. Warnings are not as severe. They are not thrown and their existence has to be explicitly

tested. getWarnings() retrieves SQL warning if they exist getNextWarning() retrieves the warning chained to this SQLwarning object Warning & Eception Example try { stmt=con.createStatement(); // create an empty statement object warning=con.getWarnings(); // retrieve warning if it exists while(warning != null) { // handle SQLWarnings warning = warning.getNextWarning(); // get next warning chained to the warning object } con.clearWarnings(); stmt.executeUpdate(queryString); warning = con.getWarnings(); } //end try catch( SQLException SQLe) { // catch the SQLException

object // handle the exception } Another Example Connection con = // connect DriverManager.getConnection(url, login", pass"); Statement stmt = con.createStatement(); // create and execute a query String query = "SELECT name, rating FROM Sailors"; rs works ResultSet rs = stmt.executeQuery(query); like a cursor try { while (rs.next()){ // loop through result tuples String s = rs.getString(name"); // get the attribute values by name Int n = rs.getInt(rating");

System.out.println(s + " " + n); // print name and rating } } catch(SQLException ex) { // handle exceptions System.out.println(ex.getMessage () + ex.getSQLState () + ex.getErrorCode ()); } Executing SQL Statements Three different ways of executing SQL statements: 1. Statement (both static and dynamic SQL statements) 2. PreparedStatement (semi-static SQL statements) 3. CallableStatment (stored procedures) Stored Procedures What is a stored procedure ? Program executed through a single SQL statement Executed in the process space of the server Server Computer 1 Application

DB Server DBMS Application part 1 Remote procedure call Queries & cursors Computer 2 Computer 2 Remote procedure call Stored Procedure Computer 2 Client

Embedded SQL Computer 1 Computer 1 Client/Server Stored procedure Application part 2 Queries & cursors DB Server Stored Procedures: Advantages 1. Can encapsulate application logic while staying close to the data Less inter-process communication Computer 1 2. Avoid tuple-at-a-time return of records through cursors

Less network communication Computer 3 3. Reuse of application logic by different users Another application Application part 1 Remote 2 procedure Call (RPC) RPC Application part 2 1 3 Stored

procedure Stored procedures can be called from JDBC using CallableStatement object Computer 2 Queries & cursors DB Server SQL/PSM: Persistent Stored Modules A standard for coding stored procedures, and storing them in the database itself A mixture of conventional statement (if, while, etc.) and SQL Declare a stored procedure: CREATE PROCEDURE () procedure code; Declare a stored function: CREATE FUNCTION ()

RETURNS sqlDataType function code; Parameters in SQL/PSM The parameters must be valid SQL types Unlike the usual name-type pairs in conventional languages, PSM uses mode-nametype triples Three different modes: IN parameters are arguments to the stored procedure OUT parameters are returned from the stored procedure INOUT parameters combine the properties of IN and OUT parameters Main SQL/PSM Constructs Local variables (DECLARE ) RETURN values for FUNCTION (Unlike C, etc., it does not terminate function execution) Assign variables with SET = Branches and loops: IF (condition) THEN statements; ELSEIF (condition) statements; ELSE statements; END IF;

LOOP statements; END LOOP Queries can be parts of expressions Can use cursors naturally without EXEC SQL SQL/PSM Function Example CREATE FUNCTION rateSailor (IN sailorId INTEGER) RETURNS INTEGER DECLARE rating INTEGER // two local variables DECLARE numRes INTEGER BEGIN BEGIN END for groups of statements SET numRes = (SELECT COUNT(*) FROM Reserves R WHERE R.sid = sailorId); IF (numRes > 10) THEN rating =1; ELSE rating = 0; SQL can be END IF; part of an RETURN rating; expression

END; SQL/PSM: Procedure Examples CREATE PROCEDURE ShowNumReservations ( IN sailorid INTEGER, OUT numres INTEGER ) SET numres = (SELECT COUNT(*) FROM Reserves R WHERE R.sid = sailorid) CREATE PROCEDURE IncreaseRating( IN sailor_sid INTEGER, IN increase INTEGER ) UPDATE Sailors SET rating = rating + increase WHERE sid = sailor_sid SQL/PSM: Returning Result Set statement is used to return a result set The result set is returned to JDBC CallableStatement object that calls the procedure CREATE PROCEDURE PROCEDURE Goodsailors( Goodsailors( CREATE

IN goodrating goodrating INTEGER) INTEGER) IN Maximum Maximum number of number of is 1 result sets result sets is 1 DYNAMIC RESULT RESULT SETS SETS 1 1 DYNAMIC BEGIN BEGIN DECLARE Cur1 Cur1 CURSOR CURSOR WITH WITH RETURN RETURN FOR

FOR DECLARE Make sure to SELECT sid, sid, sname, sname, rating rating SELECT Make sure to open cursor in FROM Sailors S S FROM Sailors open cursor in order to return Result set will WHERE S.rating S.rating > > goodrating;

goodrating; Result order to return WHERE set will the result set be returned the result set be returned OPEN Cur1 Cur1 OPEN END END Receiving Multiple Result Sets The stored procedure is executed with a JDBC CallableStatement execute() method (next slide) Import java.sql.CallableStatement;

Stored procedure 11 Preperation for calling the stored procedure Goodsailors CallableStatement CStmt = con.prepareCall({Call Goodsailors(?)); CStmt.setInt(goodrating, 8); // Set parameter using its name 22 Processing Multiple Result Sets All the result sets that were opened are returned to the CallableStatement object CStmt Calling getResultSet() returns the current result set

Boolean hadResults = CStmt.execute(); while (hadResults) { ResultSet rs = 33 CStmt.getResultSet(); // process result set hadResults = CStmt.getmoreResults(); Calling getMoreResultsets() to move to the next result set } Execute() vs. executeQuery() Boolean execute() Executes the SQL statement which may be any kind of SQL statement (e.g., can be an update) ResultSet executeQuery() Executes the SQL statement and returns the ResultSet object generated by the

query JDBC: A query object CallableStatement CallableStatement cstmt= cstmt= con.prepareCall({call con.prepareCall({call ShowSailors}); ShowSailors}); ResultSet ResultSet rs rs = = cstmt.executeQuery(); cstmt.executeQuery(); while while (rs.next()) (rs.next()) { {

// // process process result result set set } } Stored Procedures in Java Stored procedure do not have to be written in SQL The following stored procedure in Java is dynamically executed by the database server whenever it is called by the client CREATE PROCEDURE TopSailors( The language in IN num INTEGER) which the routine is written LANGUAGE Java EXTERNAL NAME file:///c:/storedProcs/rank.jar Specifies the program that runs when this procedure is called Calling Stored Procedures from embedded

SQL 1 EXEC SQL BEGIN DECLARE SECTION Int sid; Int rating; EXEC SQL END DECLARE SECTION 2 // set sid and rating to some values // now increase the rating of this sailor 3 EXEC CALL IncreaseRating(:sid, :rating); Variables in host language JDBC Summary Load the JDBC driver Create a database Connection object using the DriverManager Create a Statement (PreparedStatement or

CallableStatement) object that contains the SQL statement Execute the SQL Statement object, and receive the result in a ResultSet object Step through the rows in ResultSet object and process the data in the host language SQLJ - SQL_Java Complements JDBC with a (semi-)static query model SQLJ - All arguments always bound to the same variable: #sql sailors = { SELECT name, rating INTO :name, :rating // name is bound FROM Sailors WHERE sid = :sid; } // to :name Compare to JDBC: sid=rs.getInt(1); // get value of first attribute, i.e., sid if (sid==1) { sname1=rs.getString(2); } // name can be assigned to else { sname2=rs.getString(2); } // different variable Compiler can perform syntax checks, strong type checks, consistency of the query with the schema

SQLJ Precompiler SQLJ applications are pre-processed through an SQLJ translation program Replaces embedded SQLJ code with calls to an SQLJ Java library Usually, the SQLJ Java library makes calls to a JDBC driver (standard interface) The modified program code can then be compiled by any Java compiler SQLJ (part of the SQL standard) versus embedded SQL (vendor-specific) SQLJ is more portable. Using SQLJ Every SQLJ statement has the special prefix #sql We retrieve the results of SQL queries with iterator objects (basically a cursor) Usage of an iterator goes through five steps: 1) Declare the Iterator Class Example: #sql iterator Sailors (Int sid, String name, Int rating); 2) Instantiate an iterator object from the new iterator class Example: Sailors sailors; 3) Initialize the iterator using an SQL statement Example: #sql sailors = {SELECT FROM WHERE }

4) Iteratively, read the rows from the iterator object Example: while (sailors.next()) { // process row } 5) Close the iterator object Example: sailors.close(); SQLJ Example Named iterator allows Int sid; String name; Int rating; retrieval of columns by name // (1) declare the iterator class #sql iterator Sailors(Int sid, String name, Int rating); Sailors sailors; // (2) intantiate an iterator object Assume application sets rating #sql sailors = { SELECT sid, sname INTO :sid, :name FROM Sailors WHERE rating = :rating }; // (3) initialize iterator while (sailors.next()) {

// (4) retrieve rows from iterator object System.out.println(sailors.sid + + sailors.name)); } sailors.close(); // (5) close the iterator object Two Types of SQLJ Iterators Named iterator #sql iterator Sailors(Int sid, String name, Int rating); Example in last slide Need to specify both the variable type and the name of each column of the iterator This allows retrieval of columns by name. Positional iterator Need to specify only the variable type of the iterator, and then FETCH .. INTO construct: #sql iterator Sailors(Int, String, Int); 1 2 Sailors sailors; #sql sailors = { SELECT FROM WHERE };

1 2 while (true) { #sql {FETCH :sailors INTO :sid, :name} ; // fetch next sailor if (sailors.endFetch()) { break; } // exit loop if end of iterator // process the sailor } Calling Stored Procedure from JDBC: A query object CallableStatement cstmt= con.prepareCall({call ShowSailors}); ResultSet rs = cstmt.executeQuery(); while (rs.next()) { // process result set } Note: con is the database

JDBC & SQLJ SQLJ: #sql iterator SailorInfo(); SailorInfo sailorinfo; A query object #sql sailorinfo={CALL Call stored ShowSailors}; procedure procedure while (sailorinfo.next()) { } SUMARY - SQL in Application Code Pure JAVA Need preprocessor

Java program with JDBC SQL SQL Statement Statement Java program SQLJ SQLJ Iterator executeQuery executeQuery CallableStatement CallableStatement Embedded Embedded SQL SQL

#SQL Call #SQL Call SQL/PSM SQL/PSM ResultSet ResultSet C program ResultSet ResultSet JDBC Cursor EXEC SQL PreparedStatement

PreparedStatement Need preprocessor Vendor Vendor specific specific DBMS Catalog and Schema In MySQL, DATABASE and SCHEMA are synonyms. It is a folder for tables, views, constraints, triggers, and procedures) CREAT SCHEMA TheDatabase; USE TheDatabase CREAT TABLE FirstTable (); Some other vendors treat the terms DATABASE and SCHEMA differently Some vendors also have a term CATALOG (used to group schemas). MySQL does not support this concept

MySQL System Catalog System catalog is a system schema called INFORMATION_SCHEMA It contains tables that can be queried for metadata about the tables and other things in any schema (i.e., database) in MySQL SELECT TABLE_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = TheDatabase; JDBC Catalog and Schema In general, catalog contains information about tables, views, indexes, stored procedures, triggers, and constraints According to JDBC, a database may have a set of catalog and each catalog may have a set of schemas (i.e., concepts for grouping tables). Parameters getTables(catalog, schema, tableNames, columnNames)

Returns table names for all tables matching tableNames and all columns matching columnNames Ex: getTables(null,null,null,null) gets information for all tables getColumns(catalog, Returns table column names for all tables matching schema, tableNames and all columns matching columnNames tableNames, columnNames) Ex: getColumns(null,null,tableName,null) gets all attributes of tableName Examining Database Metadata DatabaseMetaData object gives information about the database system such as table names and tables columns. The database DatabaseMetaData md = con.getMetaData(); // print information about the driver: System.out.println(

Name: + md.getDriverName() + version: + md.getDriverVersion()); Some DatabaseMetaData Methods 134 methods in JDBC 2.0 getCatalogs(): retrieves catalog names available in this database getIndexInfo(): retrieves a description of the indexes and statistics for the given table getTables(): retrieves a description of the tables available in the given catalog GetColumns(): retrieves a description of table columns available in the specified catalog getPrimaryKeys(): retrieves a description of the given tables primary key columns. Print names of tables and their columns DatabaseMetaData md=con.getMetaData(); ResultSet trs=md.getTables(null,null,null,null); // get all tables String tableName; While(trs.next()) { // for each table, do tableName = trs.getString(TABLE_NAME); // get TABLE_NAME field

System.out.println(Table: + tableName); ResultSet crs = md.getColumns(null,null,tableName,null); // get all attributes of tableName while (crs.next()) { trs TABLE_NAME crs COLUMN_NAME+ , System.out.println(crs.getString(COLUMN_NAME) Table1 sid ); Table2 sname Print Sailors } the columns rating Table3

of each table age } Table4 Table5 Summary Embedded SQL allows execution of parametrized static queries within a host language Dynamic SQL allows execution of completely adhoc queries within a host language Cursor mechanism allows retrieval of one record at a time and bridges impedance mismatch between host language and SQL APIs such as JDBC introduce a layer of abstraction between application and DBMS Summary (Contd.) SQLJ: Static model, queries checked at compile-time. Stored procedures execute application logic directly at the server SQL/PSM standard for writing stored

procedures

Recently Viewed Presentations

  • VIG with adults with learning disabilities and their support ...

    VIG with adults with learning disabilities and their support ...

    There are several examples across the VIG and VERP literature of successful use of these interventions with adults with ID or autism (e.g. Forsyth & Sked, 2011; Forster, 2015), and a youtube video from Forster (2016) demonstrating the approach in...
  • Issue Y2K The Great War for Talent!

    Issue Y2K The Great War for Talent!

    Tom Peters' Re-Imagine! Business Excellence in a Disruptive Age Manama/ Kingdom of Bahrain/ 14April2004 "In Tom's world, it's always better to try a swan dive and deliver
  • Army Audit Readiness Test Results: SBR: Internal Controls E&C ...

    Army Audit Readiness Test Results: SBR: Internal Controls E&C ...

    DAAS MILS Transaction Edits and Routing. 2 Garbled Transactions refer to transactions with fields that are shifted or unreadable (DLM 4000.25-4, Section C4.3.2.4.1) ** Based on review of the FY2016 DAAS SOC 1 Report by the AWCF Audit Readiness Team,...
  • Nov. 26, 2012

    Nov. 26, 2012

    For though its effects were more immediately felt by them, yet its influence appeared to be injurious to the commerce and prosperity of Great Britain.We shall decline the ungrateful task of describing the irksome variety of artifices practised by many...
  • What is XML? - ICJIA

    What is XML? - ICJIA

    XML accomplishes this by using Markup Language to identify structures within a document. Structured information contains both content (words) and an indication of what role the content plays. For example, the object "vehicle" contains many elements such as description, color,...
  • Proposals for Change in Medical Negligence Cases - Pre Action ...

    Proposals for Change in Medical Negligence Cases - Pre Action ...

    Liability as Employer. Liability as Occupier. Negligence. wrongdoing and fault. duty of care. breach of duty of care. injury caused by breach of duty of care. Duty of Care. Common Law Duty of Care. Statutory Duty of Care. Common Law...
  • IDSC Home of HEDA

    IDSC Home of HEDA

    Staff Time Sheets. This module is an easy web application which assists all staff in capturing their time spent on a particular HEMIS programme over a period of time.
  • Managing Millennials Will Coz 1 This era of

    Managing Millennials Will Coz 1 This era of

    7. Advance the careers of employees, to make a real difference in the community, to rescue struggling employees, to instill a sense of pride and self-worth in others. They aren't just remembered for nuts and bolts achievements but for helping...