Overview of JDBC,Pro*C and Oracle connectivity on Omega

1. CSE 5330 – Database Systems Overview of JDBC,Pro*C and Oracle connectivity on Omega

2. Options for doing the project • Windows application using Java • JDBC for database connectivity • Java AWT / Swing etc. for the GUI • Menu driven program using Pro*C • If you know C, but not Java

3. Java Database Connectivity (JDBC) • JDBC provides a standardized library for accessing relational databases • Connecting to the database • Initiating queries • Creating stored (parameterized) queries • Data structure for the query result • JDBC is not embedded SQL • JDBC is located in java.sql package

4. JDBC driver Java application JDBC API JDBC driver manager JDBC driver API Oracle JDBC driver (classes12.zip) Client Oracle database Server (omega)

5. JDBC driver (continued) • JDBC driver for Oracle needs to be on the client machine • Available on omega at • /opt/oracle/product/9.2.0.4.0/jdbc/lib/classes12.zip • The JDBC driver (classes12.zip) needs to be included in the runtime classpath

6. Six steps in using JDBC • Load the driver • Establish a connection • Create a statement object • Execute a query • Process the results • Close the connection

7. JDBC usage steps - details • Step 1 : Load the driver try { Class.forName ("oracle.jdbc.driver.OracleDriver"); } catch(Exception e) { System.out.println("JDBC ORACLE driver failed to load"); }

8. JDBC usage steps - details • Step 2 : Establish a connection • try • { • con = DriverManager.getConnection("jdbc:oracle:thin:scott/tiger@omega.uta.edu:1521:CSE1"); • } • catch(Exception e) • { • System.out.println("Failed to connect to the database"); • }

9. JDBC usage steps - details • Step 3 : Create a statement • Statement statement = con.createStatement(); • Step 4 : Execute a query String query = “SELECT FNAME, SALARY FROM EMPLOYEE”; ResultSet rs = Statement.executeQuery(query); • (For SELECT) String query = “UPDATE EMPLOYEE SET SALARY = 50000 WHERE FNAME = “John”; Statement.executeUpdate(query); • (For UPDATE, INSERT, DELETE)

10. JDBC usage steps - details • Step 5 : Process the result While (rs.next()) { System.out.println(rs.getString(1) + “ “ + rs.getInt(2)); } • First column has index 1, not 0 • ResultSet provides various getXXX methods that take a column index, and return the data

11. JDBC usage steps - details • Step 6 : Close the connection • con.close(); • As opening a connection is expensive, postpone this step if additional database operations are expected

12. Online resources • JDBC tutorial • http://java.sun.com/docs/books/tutorial/jdbc/ • JDBC API reference • http://java.sun.com/j2se/1.4.2/docs/api/java/sql/package-summary.html

13. Pro*C • Embedded SQL • Allows you to embed SQL in C code Pre-compiler C compiler Pro*C program C code Executable Embedded SQL statements SQL statements replaced By library calls SQLLIB Oracle runtime library

14. Pro*C syntax • SQL statements can be placed anywhere between a C block • All SQL statements need to start with EXEC SQL and be terminated by a semicolon { int a; /* ... */ EXEC SQL SELECT salary INTO :a FROM Employee WHERE SSN=876543210; /* ... */ printf("The salary is %d\n", a); /* ... */ } • Be sure to include sqlca.h

15. Connecting to the database • Legal way char *username = "SCOTT"; char *password = "TIGER"; … EXEC SQL CONNECT :username IDENTIFIED BY :password; • Illegal way • EXEC SQL CONNECT SCOTT IDENTIFIED BY TIGER; • EXEC SQL CONNECT 'SCOTT' IDENTIFIED BY 'TIGER';

16. Dynamic sql • Used to dynamically create entire SQL statements • char *s = "INSERT INTO emp VALUES(1234, 'jon', 3)"; • EXEC SQL PREPARE q FROM :s; • EXEC SQL EXECUTE q; • Alternately, • char *s = "INSERT INTO emp VALUES(1234, 'jon', 3)"; • EXEC SQL EXECUTE IMMEDIATE :s;

17. Host variables • Key to the communication between the host program and the database • They should be declared in a Declare section • EXEC SQL BEGIN DECLARE SECTION; • /* Declare all host variables inside this section: • */ char *uid = "scott/tiger"; • ... • EXEC SQL END DECLARE SECTION;

18. Transactions • Sequence of SQL statements that are treated as a single unit of work • Transaction ends when you issue • EXEC SQL COMMIT;

19. Cursors • Used for fetching multiple rows of data • First, declare it. • EXEC SQL DECLARE emp_cursor CURSOR FOR • SELECT ename, empno, sal • FROM emp • WHERE deptno = :dept_number; • Then, open it. • EXEC SQL OPEN emp_cursor; • Fetch the records. • for (;;) • { • EXEC SQL FETCH emp_cursor INTO :emp_name1, :salary1; • }

20. Cursors (continued) • Finally, close it. • EXEC SQL CLOSE emp_cursor; • Error handling • Every executable SQL statement returns a status code to the SQLCA variable sqlcode • A zero status code means that Oracle executed the statement without detecting an error or exception • A positive status code means that Oracle executed the statement but detected an exception • A negative status code means that Oracle did not execute the SQL statement because of an error.

21. Online reference • Pro*C/C++ Precompiler Programmer's Guide • http://omega.uta.edu/oracle/appdev.920/a97269/pc_01int.htm

22. Oracle connectivity on Omega • To connect with Oracle the SSH tool may be used • It is available for download from http://www.uta.edu/oit/downloads.html Once downloaded , omega can be accessed as follows:-

23. Oracle connectivity on Omega Once you submit this box, you will be asked to enter your password as follows :-

24. Oracle connectivity on Omega • Incase you reside at an off-campus location, you will not be able to directly access http://omega.uta.edu using SSH • You will need to download and connect using a VPN client and then connect through SSH to omega. • The VPN client can be downloaded from http://www.uta.edu/oit/clientservices/network/vpn/pc/vpn_pc.html