EMBEDDED SQL

1
EMBEDDED SQL

• Pepper

2
WHICH LANGUAGES

• C
• Visual C
• Java
• Many, many more
• What is needed from the language
• Must know how to connect to oracle db
• Must know how to format commands to oracle

3
How Java Knows

• Libraries with Oracle and array knowledge
• import java.sql.
• import java.util.Enumeration
• Environment variables
• setenv CLASSPATH ".ORACLE_HOME/jdbc/lib/classe
  s12.zipORACLE_HOME/jdbc/lib/nls_charset12.zip
• setenv LD_LIBRARY_PATH "ORACLE_HOME/lib
• ORACLE_HOME/jdbc/lib"

4
Setting up Java to run on Panther

• This is initial setup that has nothing to do with
  Oracle
• Path with
• /opt/IBMJava2-141/bin
• /opt/IBMJava2-141/jre/bin
• /usr/X11R6/bin

5
Environment just plain oracle

• Unix Environment variables
• For Oracle to find its programs
• setenv ORACLE_BASE /usr/users/db/oracle
• setenv ORACLE_HOME ORACLE_BASE/OraHome1
• For Oracle to know which set of data
• setenv ORACLE_SID adelphi

6
Environment just plain oracle

• Path must include
• For Oracle
• ORACLE_HOME/binORACLE_HOME

7
Viewing env variables

• env
• env grep PATH
• See it in .cshrc and .mycshrc

8
Connecting to the Database

• final String tnspre "jdbcoracleoci8_at_"
  String user "pepperk" //edit this
  String pass "123" //edit this try
  System.err.println("Registering driver...")
  DriverManager.registerDriver
• (new oracle.jdbc.driver.OracleDriver())
  System.err.println("Attempting connection...")
  Connection x DriverManager.getConnection(t
  nspre, user, pass)

9
Catching the error if connection fails

• catch (SQLException se)
  System.err.println("Caught an SQLException!")
  se.printStackTrace() System.err.println(
  "Available drivers") Enumeration e
  DriverManager.getDrivers() while
  (e.hasMoreElements())
  System.err.println(e.nextElement())

10
Sending to Oracle

• System.err.println("Creating statement...")
• Statement stmt x.createStatement()
• stmt.execute ("DROP TABLE jdbctest")
• // note no semi colon
• stmt.execute("CREATE TABLE jdbctest (text
  varchar2(127))")
• stmt.execute("INSERT INTO jdbctest VALUES ('This
  is a test')")

11
Getting back from oracle

• Sent
• ResultSet result stmt.executeQuery("SELECT
  FROM jdbctest")
• // ResultSet's pointer initializes before the
  first row
• result.next()
• System.out.println(result.getString("text"))
• //release the result variable space
• result.close()
• // release the sql statement variable space
• x.close()

12
Compiling

• javac test.java
• gtgt creates classes
• java ltthe class namegt

13
Samples

• Simple
• Asking for user and password