Overview of JDBC and Pro*C

1. CSE 5330 – Database Systems Overview of JDBC and Pro*C

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 • Web based application using CGI-Perl • Perl DBI module for database connectivity • CGI / HTML for the GUI • Web based application using JSP, Java servlets, PHP etc. • Omega may not allow running some/all of these • Be prepared to demo on your own laptop etc.

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. Pro*C set-up on omega • All your Pro*C files should be named from lab1 to lab10 • Use ‘make.proc labx’ to compile labx.pc • This will precompile the .pc file to a C file, compile the C file and generate the executable for you • Sample Pro*C program available. Run /home/a/ax/axs3919/lab2 /home/s/sh/sharmac/cse5330/spring2006/proC/lab2

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