JDBC and OCCI

1. JDBC and OCCI

2. JDBC • Java API to allow java program to interface with database • Platform independence • Database independence

3. JDBC applications • Import the JDBC class (java.sql.*) • Load the JDBC drivers • Connect to the database • Interact with the database using JDBC • Disconnect with the database

4. Load the JDBC drivers • Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();

5. Connect to the database • Connection Conn = DriverManager.getConnection(url,username,password); • url is of the form: • Jdbc:oracle:drivertype:@database • Example: "jdbc:oracle:thin:@erg.csci.unt.edu:1521:ERG"

6. The Connection Object • Statement • prepareStatement • CallableStatement

7. Using Statement Object • Statement Stmt = Conn.createStatement(); ResultSet RS = Stmt.executeQuery("SELECT * from user_tables");

8. The ResultSet Class • The resultset class provides access to a table of data generated by executing a query. • Methods: • Next • Close • getString(int columnIndex)

9. ResultSet Metadata • ResultSetMetData rsetmd = rs.getMetaData() • Method: • getColumnCount • getColumnDisplaySize(int column) • getColumnTypeName(int column)

10. Close • RS.close(); • Stmt.close(); • Conn.close();

11. OCCI • Oracle C++ call interface • Steps are similar to JDBC

12. Create an environment • An OCCI driver manager maps to an OCI environment handle. • env = Environment::createEnvironment (Environment::DEFAULT); • Establish a connection • conn = env->createConnection (user, passwd, db);

13. create and bind a SQL statement Statement • *stmt = conn->createStatement ("SELECT * FROM user_tables"); • execute the statement • stmt->execute ();

14. get the result of the statement • ResultSet *rs = stmt->getResultSet ();

15. The ResultSet Class • The resultset class provides access to a table of data generated by executing a query. • Methods: • Next • Close • getString(int columnIndex)

16. Terminate the connection • env->terminateConnection (conn); Environment::terminateEnvironment (env);