1 / 58

Embedded SQL Embedded OQL (Jasmine ODQL)

Embedded SQL Embedded OQL (Jasmine ODQL). Chapter 21 + 28.8 in third edition Chapter 29.7 + Appendix E in fourth edition Article: SQLJ: It’s Javalicious! Chapter 8 in ”Principles of Database Systems with Internet and Java ™ Applications” Excerpt from ”Database Systems the complete book”

fauve
Download Presentation

Embedded SQL Embedded OQL (Jasmine ODQL)

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.

E N D

Presentation Transcript

  1. Embedded SQLEmbedded OQL (Jasmine ODQL) Chapter 21 + 28.8 in third edition Chapter 29.7 + Appendix E in fourth edition Article: SQLJ: It’s Javalicious! Chapter 8 in ”Principles of Database Systems with Internet and Java™ Applications” Excerpt from ”Database Systems the complete book” Jasmine On-line Documentation ODMG 3.0 / ODMG 2.0 (Chapter 26 in third edition / Chapter 27 in fourth edition ) IS4/2i1242/2i4042 spring 2007

  2. What is embedded SQL? • Host Language • Any programming language • Database • An SQL database (relational database) • Embedded SQL: • Helps the host language communicate with the database via SQL IS4/2i1242/2i4042 spring 2007

  3. Why embedded SQL? • Advanced logic • Database Interface/Application IS4/2i1242/2i4042 spring 2007

  4. Static vs. Dynamic • Static embedded SQL • Compiled in advance  Faster at run-time • Limited functionality • Dynamic embedded SQL • Not compiled in advance  Slower • Full functionality Everything that can be achieved with static embedded SQL, can also be achieved with dynamic embedded SQL IS4/2i1242/2i4042 spring 2007

  5. Standard embedded SQL vs. embedded SQL for Java • Standard embedded SQL • Static • Dynamic • Embedded SQL for Java • Static (i.e. SQLj) • Dynamic (i.e. JDBC) IS4/2i1242/2i4042 spring 2007

  6. Standard embedded SQL • Defined for a number of ”older” languages: • ADA • C • COBOL • FORTRAN • PASCAL • PL/1 • ISO standard IS4/2i1242/2i4042 spring 2007

  7. Standard embedded SQLBasics All SQL-commands start with EXEC SQL And are terminated with the appropriate command-terminator (t ex ;) based on the host language IS4/2i1242/2i4042 spring 2007

  8. Standard embedded SQLExample (singleton select) … EXEC SQL CONNECT myDB; EXEC SQL SELECT name, address INTO :lname, :laddress FROM myTable WHERE id=53; … Static embedded SQL IS4/2i1242/2i4042 spring 2007

  9. Standard embedded SQLExample Begin EXEC SQL DECLARE mycursor CURSOR FOR SELECT name, pet FROM Person, Pet WHERE Person.name = Pet.name; . . . EXEC SQL OPEN mycursor; . . . EXEC SQL FETCH mycursor INTO :lname, :lpet; While SQLCODE = 0 Do Begin Writeln(lname, lpet); EXEC SQL FETCH mycursor INTO :lname, :lpet; End; . . . EXEC SQL CLOSE mycursor; End. Static embedded SQL IS4/2i1242/2i4042 spring 2007

  10. Standard embedded SQLExempel VAR query : CHAR(1000); . . . query := ’DELETE FROM Employee WHERE enumber = ’0025’; EXEC SQL PREPARE statement FROM :query; EXEC SQL EXECUTE statement; query := ’DELETE FROM Employee WHERE enumber = ’0026’; EXEC SQL EXECUTE IMMEDIATE :query; tablename := ’myTable’; query := ’DELETE FROM :tablename; EXEC SQL EXECUTE IMMEDIATE :query; Dynamic embedded SQL IS4/2i1242/2i4042 spring 2007

  11. Java embedded SQLGeneral • Java program • JDBC driver • (JDBC-ODBC bridge + ODBC driver) • Database IS4/2i1242/2i4042 spring 2007

  12. Java embedded SQLArcitecture Java Application JDBC driver manager JDBC/ODBC bridge JDBC Driver (DBMS Specific) ODBC Driver DBMS IS4/2i1242/2i4042 spring 2007

  13. JDBC sequence 1. Import Packages 2. Register JDBC Driver 3. Open a Connection to the database 4. Create a Statement 5. Execute an SQL command and receive a Result Set (if there is one) 6. Process/Work with the Result Set (back to point 4) 7. Close the Result Set and the Statement 8. Close the Connection IS4/2i1242/2i4042 spring 2007

  14. 1. Import Packages //Import packages import java.sql.*; //JDBC packages //other packages import java.util.*; … java.sql package specification: http://java.sun.com/j2se/1.5/docs/api/index.html IS4/2i1242/2i4042 spring 2007

  15. 2. Register JDBC Driver //Load IBM DB2 driver Class.forName (”com.ibm.db2.jcc.DB2Driver”); //Load JDBC-ODBC driver bridge Class.forName(”sun.jdbc.odbc.JdbcOdbcDriver”); // Load Oracle driver DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver()); IS4/2i1242/2i4042 spring 2007

  16. 3. Open a Connection to the database String userID = ”dbuser”; String password = ”abc123”; String ODBCURL = ”jdbc:odbc:mydb”; String ORACLEURL = ”jdbc:oracle:mydb”; String DB2URL = ”jdbc:db2:mydb”; Connection con1 = DriverManager.getConnection (ORACLEURL, userID, password); Connection con2 = DriverManager.getConnection (ODBCURL, userID, password); Connection con3 = DriverManager.getConnection (DB2URL, userID, password); con3.setAutoCommit(true); IS4/2i1242/2i4042 spring 2007

  17. 3. Open a Connection to the database, cont. The URL (”database address”) is compised of three parts: jdbc:databasetype/ODBC:databasename/ODBC-alias ODBC-aliases can be defined in the ODBC Manager (”Data Sources (ODBC)” in the Administrative Tools in the Control Panel or by running odbcad32.exe found in system32): IS4/2i1242/2i4042 spring 2007

  18. 4. Create a Statement PreparedStatement pstmt1 = con1.prepareStatement ("SELECT hotelname, rating FROM hotel WHERE city = ?"); pstmt1.setString(1, ”Hawai”); PreparedStatement pstmt2 = con1.prepareStatement (”UPDATE event SET place = ? Price = ? WHERE activity = ?"); pstmt2.setString(1, ”beach”); pstmt2.setInt(2, 100); pstmt2.setString(3, ”swimming”); IS4/2i1242/2i4042 spring 2007

  19. 5. Execute an SQL command and receive a Result Set (if there is one) //execute the prepared statement pstmt1 and store the result in a result set ResultSet rs = pstmt1.executeQuery(); //execute the prepared statement pstmt2 pstmt2.executeUpdate(); IS4/2i1242/2i4042 spring 2007

  20. 6. Process/Work with the Result Set // Print a list of the hotels that were returned by the query System.out.println(”Hotels in Hawai:”); while (rs.next()) {System.out.println (rs.getString(”hotelname”)); } //Alternatively rs.getString(1) IS4/2i1242/2i4042 spring 2007

  21. 7. Close the Result Set the Statement 8. Close the Connection // close the result set, statements, and the connections rs.close(); pstmt1.close(); pstmt2.close(); con1.close(); con2.close(); con3.close(); IS4/2i1242/2i4042 spring 2007

  22. Data types It is often necessary to map the database data types to the Java data types/classes: • SQL integer can be mapped to Java int • SQL number, real etc can be mapped to Java real • SQL varchar, char, string etc can be mapped to Java String • SQL date, time, timestamp etc can be mapped to Java java.sql.Date, java.sql.Time, java.sql.Timestamp IS4/2i1242/2i4042 spring 2007

  23. SQLJ SQLJ uses and extends the JDBC infrastructure. It uses contexts in order to distinguish connections All SQLJ database commands start with #sql It creates profiles that contain information about the SQL commands (so that they can be handled as static commands) SQLJ Java packages: sqlj.runtime IS4/2i1242/2i4042 spring 2007

  24. SQLJ architecture IS4/2i1242/2i4042 spring 2007

  25. SQLJ sequence 1. Import Packages 2. (Define iterator) 3. Register JDBC Driver 4. Open a Connection to the database 5. Set DefaultContext / other Context 6. Execute an SQL command and receive the result (if there is any) 7. Process/Work with the result (cursor) (back to point 6) 8. Close the cursor 9. Close the Connection/Context IS4/2i1242/2i4042 spring 2007

  26. 1. Import Packages //Import packages import sqlj.runtime.*; import sqlj.runtime.ref.*; import java.sql.*; //JDBC packages import java.util.*; sqlj package specification: ftp://ftp.calweb.com/business/sqlj/SQLJ-P0.pdf http://www.ifis.mu-luebeck.de/lehre/ss99/prakt/informix-jdbc-doc/sqlj/doc/runtime/javadoc/packages.html IS4/2i1242/2i4042 spring 2007

  27. 2. (Define iterator) #sql iterator Hotel_Cursor (String hotelname, Integer rooms) ; #sql iterator String2_Cursor (String, String); IS4/2i1242/2i4042 spring 2007

  28. 3. Register JDBC Driver //Load IBM DB2 driver Class.forName (”com.ibm.db2.jcc.DB2Driver”); //Load JDBC-ODBC driver bridge Class.forName(”sun.jdbc.odbc.JdbcOdbcDriver”); // Load Oracle driver DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver()); IS4/2i1242/2i4042 spring 2007

  29. 4. Open a Connection to the database String userID = ”dbuser”; String password = ”abc123”; String ODBCURL = ”jdbc:odbc:mydb”; String ORACLEURL = ” jdbc:oracle:mydb”; String DB2URL = ”jdbc:db2:mydb”; Connection con1 = DriverManager.getConnection (ORACLEURL, userID, password); Connection con2 = DriverManager.getConnection (ODBCURL, userID, password); Connection con3 = DriverManager.getConnection (DB2URL, userID, password); con3.setAutoCommit(true); IS4/2i1242/2i4042 spring 2007

  30. 5. Set DefaultContext / other Context // Set the default context so SQLJ can automatically pick up the connection DefaultContext ctx = new DefaultContext (con3); DefaultContext.setDefaultContext (ctx); IS4/2i1242/2i4042 spring 2007

  31. 6. Execute an SQL command and receive the result (if there is any) int rooms = 500; String name = ”Grand"; String city = ”Madrid”; #sql { INSERT INTO hotel (hotelname, rooms, city) VALUES (:name, :rooms, :city) }; IS4/2i1242/2i4042 spring 2007

  32. 6. Execute an SQL command and receive the result (if there is any) String city = ”Rome”; Hotel_Cursor hotelcursor; #sql hotelcursor = {SELECT hotelname, rooms FROM hotel WHERE city = :city }; String2_Cursor hotel200cursor; #sql hotel200cursor = {SELECT hotelname, city FROM hotel WHERE rooms >= 200 }; IS4/2i1242/2i4042 spring 2007

  33. 7. Process/Work with the result (cursor) // Print a list of the hotels that were returned by the first query System.out.println(”Hotels in Rome:”); while (hotelcursor.next()) { System.out.print(hotelcursor.hotelname()); System.out.print(” ”); System.out.println(hotelcursor.rooms()); } IS4/2i1242/2i4042 spring 2007

  34. 7. Process/Work with the result (cursor) // Print a list of the hotels that were returned by the second query String s1; String s2; System.out.println(”Hotels with 200 or more rooms:”); while (true) { #sql { FETCH :hotel200cursor INTO :s1, :s2 }; if (hotel200cursor.endFetch()) break; System.out.println (s1 + "\t” + s2); } IS4/2i1242/2i4042 spring 2007

  35. 8. Close the cursor9. Close the Connection/Context // close the cursors and the connection/context hotelcursor.close(); hotel200cursor.close(); ctx.close(); //this will close the connection con3 as well ctx.close(false); //keep the connection alive con3.close(); //close only the connection IS4/2i1242/2i4042 spring 2007

  36. More information • JDBC (SUN): http://java.sun.com/products/jdbc/index.html • SQLJ: http://www.sqlj.org/ IS4/2i1242/2i4042 spring 2007

  37. OQL, ODQL & embedded OQL, ODQL • Hard to define a border • Depends on the OODBMS • compare SQL – Stored Procedure – Embedded SQL IS4/2i1242/2i4042 spring 2007

  38. Embedded OQL/ODQL • OO programming language • Java • C++ • … • OODBMS • Jasmine • Objectivity • FastObjects • … • Classes • Database specific classes, or • JDO Classes and JDO enhanced database classes • Query Language • Database specific query language, or • JDOQL IS4/2i1242/2i4042 spring 2007

  39. Embedded OQL/ODQL Architecture Java Application JDO classes Database Java Classes JDO Enhanced Database Java Classes OODBMS-specific classes OODBMS IS4/2i1242/2i4042 spring 2007

  40. OQL (example) select e.ssn, count(e.teaches) from e in Instructor where e.dept.name = "D1" and count(e.teaches) > 0 select Instructor from Instructor where Instructor.dept.name = "D1” and Instructor.teaches.count() > 0 IS4/2i1242/2i4042 spring 2007

  41. Embedded OQL (C++) #include … … %for each v in select e.ssn, count(e.teaches) from e in Instructor where e.dept.name = "D1" and count(e.teaches) > 0 do %print v; IS4/2i1242/2i4042 spring 2007

  42. Jasmine ODQL defaultCF …; … Instructor set vs, v; vs = select Instructor from Instructors where Instructor.dept.name == "D1” and Instructor.teaches.count() > 0; scan (vs, v) { v.ssn.print(); v.teaches.count().print(); }; end; IS4/2i1242/2i4042 spring 2007

  43. Embedded Jasmine ODQL i Java import jp.jasmine.japi.*; … db = new Database(); … ODQLStatement odql = db.getODQLStatement(); odql.defaultCF("myCF"); odql.execute("Instructor set vs;"); odql.execute("vs = select Instructor from Instructor where Instructor.dept.name == \"D1\" and Instructor.teaches.count() > 0;"); DBCollection instructors = (DBCollection) odql.getVar(“vs"); Enumeration instructorsEnum = instructors.elements(); IS4/2i1242/2i4042 spring 2007

  44. Embedded Jasmine ODQL i Java odql.execute("Instructor v;"); while (instructorsEnum.hasMoreElements()) { DBObject v = (DBObject) instructorsEnum.nextElement(); String ssn = (String) v.getProperty(“ssn"); System.out.println(ssn); odql.defineVar("amount", new Integer(0)); odql.setVar("v", v); odql.execute("amount = v.teaches.count();"); System.out.println(odql.getIntVar("amount")); } IS4/2i1242/2i4042 spring 2007

  45. Jasmine ODQL ODQL (Object Definition and Query Language) is an integrated objectlanguage that provides facilities for object definition, object manipulationand object query. ODQL statements can either be embedded and compiledin a host language, or interpreted using the supplied ODQL interpreter IS4/2i1242/2i4042 spring 2007

  46. Jasmine ODQL (structure) defaultCF database name Variable declaration Program code end; All commands must be terminated with ; IS4/2i1242/2i4042 spring 2007

  47. Jasmine ODQL (common constructs) if (condition) {program code}; else {program code}; while (condition) {program code}; scan (set, element variable) {program code}; IS4/2i1242/2i4042 spring 2007

  48. Jasmine ODQL (useful methods) Set methods: unique() hasElement(element) hasSameElements(set) union(set) count() … User defined methods: Can be defined for a class! For example: age() can be a method defined for the class Person and it calculates the person’s age based on the date of birth. IS4/2i1242/2i4042 spring 2007

  49. Jasmine ODQL (example) defaultCF myCF; Person set ps, p; Job set js, j; Integer totalsalary; /*All people that live in Stockholm and are over 18*/ ps = select Person from Person where Person.age() > 18 and Person.homeaddress.city == "Stockholm"; IS4/2i1242/2i4042 spring 2007

  50. Jasmine ODQL (example cont.) scan (ps, p) { totalsalary = 0; p.name.print(); p.isRich().print(); p.jobs.count().print(); js = p.jobs; scan (js, j) { totalsalary = totalsalary + j.salary; j.description.print(); j.salary.print(); }; totalsalary.print(); }; end; IS4/2i1242/2i4042 spring 2007

More Related