Modifications for Oracle

Modifications to the tutorial for using an Oracle database instead of a SQL Server database.

Prerequisites

This tutorial begins where Tutorial: Reusing Existing COBOL Programs in a Java Web Services Environment left off, using the Apache Tomcat application server, and the same CobolBook and JSPBookDemo projects. Therefore, you must complete that tutorial in its entirety, using the Tomcat 7 application server, before starting this tutorial. See Tutorial: Reusing Existing COBOL Programs in a Java Web Services Environment for complete instructions.

You must also install Oracle Database 11g Express Edition.

Modifications

This section explains the modifications you need to make to the SQL Server version of the tutorial so that the application works with Oracle instead of SQL Server.

Install and Configure a JDBC Driver
Install a JDBC Driver
This tutorial uses the Oracle Database 11g Release 2 JDBC driver to connect to Oracle 11g Express Edition. Follow these instructions instead of the instructions in the Install a JDBC Driver section of the Install and Configure a JDBC Driver topic:
Copy the JDBC Driver to Tomcat
Follow the instructions provided in the Copy the JDBC Driver to Tomcat section of the Install and Configure a JDBC Driver topic, but copy the ojdbc6.jar file to the lib subdirectory of your Apache Tomcat installation.
Change the Tomcat HTTP Port
By default, Oracle uses port 8080 which is the same as Tomcat. To avoid conflict, change the Tomcat HTTP port as follows:
  1. In Project Explorer, expand Servers > Tomcat v7.0 Server at localhost-config.
  2. Double-click server.xml to open it in the editor.
  3. Change: 
    <Connector connectionTimeout="20000"
port="8080" protocol="HTTP/1.1" redirectPort="8443"/>

    To: 

    <Connector connectionTimeout="20000"
port="9000" protocol="HTTP/1.1" redirectPort="8443"/>
  4. Click File > Save to save server.xml.
Define a Connection and Load an Oracle Database
Create the Oracle Database
The tutorial uses the standard Oracle XE database that is included in the product; therefore, you do not need to create a database for this tutorial.
Start Enterprise Developer
If you have closed Enterprise Developer since completing Tutorial: Reusing Existing COBOL Programs in a Java Environment, open it again; then open the Java EE perspective and the Project Explorer.
Define an Oracle Connection
Follow the instructions provided in the Define a SQL Server Connection section of the Create and Load a SQL Server Database topic, with the following modifications:
  • Connection Profile dialog box, select the Oracle profile type.
  • On the New Driver Definition dialog box Name/Type tab, select Oracle Thin Driver (for System Version 11).
  • On the JAR List tab, follow these instructions:
    1. Click Add JAR/Zip.
    2. Browse to the local directory that contains your downloaded ojdbc6.jar file, and double-click the file to select it.

      This adds the file to the Driver file list.

  • On the Properties tab, use the following information:
    Property Value
    SID Xe
    Host Localhost
    Port number 1521
    User name Scott 1
    Password tiger 1,2
    Connection URL jdbc:oracle:thin:@localhost:1521:xe

    1 Connection credentials used for this tutorial.

    2 Password is case sensitive.

  • Follow these steps on the New Connection Profile dialog box:
    1. Click Test Connection.
    2. When the connection succeeds, clear the message box; then click Finish to save the connection and close the New Connection Profile dialog box.
Add an SQL Script
Follow the instructions provided in the Add an SQL Script section of the Create and Load a SQL Server Database topic, with the following modifications:
  • Name the new script file SetupOraBooks.sql.
  • Add the following contents: 
    DROP TABLE BOOKS;
CREATE TABLE BOOKS (
	TITLE	VARCHAR2(50) NOT NULL,
	TYPE    	VARCHAR2(20) NOT NULL,
	AUTHOR  	VARCHAR2(50) NOT NULL,
	STOCKNO 	CHAR(4) PRIMARY KEY,
	ISBN    	DECIMAL (13, 0) NOT NULL, 
	RETAIL	DECIMAL(4,2) NOT NULL,
   	ONHAND	INT NOT NULL,
    	SOLD	INT NOT NULL
   );

INSERT INTO BOOKS VALUES(
	'OLIVER TWIST',
	'CHARLES DICKENS',
	'CLASSIC',
	'1111',
	9780140620467,
	5.00,
	10,
	30
);

INSERT INTO BOOKS VALUES(
	'A GAME OF THRONES',
	'GEORGE R. R. MARTIN',
	'FANTASY',
	'1112',
	7428545,
	3.86,
	17,
	75
);

INSERT INTO BOOKS VALUES(
	'A CLASH OF KINGS',
	'GEORGE R. R. MARTIN',
	'FANTASY',
	'1113',
	7447833,
	6.49,
	17,
	75
);

INSERT INTO BOOKS VALUES(
	'THE DAY OF THE JACKAL',
	'FREDERICK FORSYTH',
	'ADVENTURE',
	'1114',
	99552710,
	2.00,
	26,
	75
);

INSERT INTO BOOKS VALUES(
	'HARRY POTTER AND THE PHILOSOPHER''S STONE',
	'J. K. ROWLING',
	'ADVENTURE',
	'1116',
	747558191,
	5.24,
	48,
	100
);

INSERT INTO BOOKS VALUES(
	'HARRY POTTER AND THE CHAMBER OF SECRETS',
	'J. K. ROWLING',
	'ADVENTURE',
	'1117',
	747562180,
	5.24,
	44,
	100
);

INSERT INTO BOOKS VALUES(
	'HARRY POTTER AND THE PRISONER OF AZKABAN',
	'J. K. ROWLING',
	'ADVENTURE',
	'1118',
	747573760,
	5.24,
	49,
	100
);

INSERT INTO BOOKS VALUES(
	'HARRY POTTER AND THE GOBLET OF FIRE',
	'J. K. ROWLING',
	'ADVENTURE',
	'1119',
	747582386,
	6.74,
	44,
	100
);

INSERT INTO BOOKS VALUES(
	'HARRY POTTER AND THE ORDER OF THE PHOENIX',
	'J. K. ROWLING',
	'ADVENTURE',
	'1120',
	747591261,
	6.74,
	37,
	100
);

INSERT INTO BOOKS VALUES(
	'HARRY POTTER AND THE HALF-BLOOD PRINCE',
	'J. K. ROWLING',
	'ADVENTURE',
	'1121',
	747598460,
	6.74,
	41,
	100
);

INSERT INTO BOOKS VALUES(
	'HARRY POTTER AND THE DEATHLY HALLOWS',
	'J. K. ROWLING',
	'ADVENTURE',
	'1122',
	1408810298,
	6.74,
	40,
	100
);

INSERT INTO BOOKS VALUES(
	'THE FELLOWSHIP OF THE RING',
	'J. R. R. TOLKIEN',
	'FANTASY',
	'1123',
	7123825,
	4.95,
	23,
	100
);

INSERT INTO BOOKS VALUES(
	'THE TWO TOWERS',
	'J. R. R. TOLKIEN',
	'FANTASY',
	'1124',
	261102362,
	7.99,
	28,
	100
);

INSERT INTO BOOKS VALUES(
	'LORD OF THE FLIES',
	'WILLIAM GOLDING',
	'SCARY',
	'2222',
	9780571191475,
	4.50,
	30,
	100
);

INSERT INTO BOOKS VALUES(
	'CATCH 22',
	'JOSEPH HELLER',
	'WAR',
	'3333',
	9780099477310,
	6.50,
	50,
	200
);

INSERT INTO BOOKS VALUES(
	'THE HITCHHIKER''S GUIDE TO THE GALAXY',
	'DOUGLAS ADAMS',
	'COMEDY',
	'4444',
	9780345453747,
	6.73,
	100,
	360
);

INSERT INTO BOOKS VALUES(
	'TO KILL A MOCKINGBIRD',
	'HARPER LEE',
	'CLASSIC',
	'5555',
	9780099466734,
	4.87,
	50,
	75
);

SELECT * FROM BOOKS;
/* create user SCOTT identified by tiger
grant dba to SCOTT */
Execute the SQL Script
Follow the instructions provided in the Execute the SQL Script section of the Create and Load a SQL Server Database topic, with the following modifications:
  • Use the SetupOraBooks.sql file.
  • Set the Type to Oracle_11.
  • Set the Name to the name of your Oracle connection.
  • In the Database field, select xe.
Modify COBOL Source
Following the instructions in the Modify COBOL Source topic, with these modifications:
Replace book.cbl with sqlbook.cbl
Follow the instructions provided in the Replace book.cbl with sqlbook.cbl of the Modify COBOL Source topic.
Note: If you have previously done this tutorial and replaced the book.cbl with the sqlbook.cbl file, skip this step and continue with Modify the EXEC SQL Statement.
Modify the EXEC SQL Statement
  1. In the Project Explorer, double-click the sqlbook.cbl entry to open the file in the editor.
  2. Change: 
    exec sql connect to PUBS end-exec

    To: 

    exec sql connect to ora end-exec
  3. Save sqlbook.cbl.
Modify book-rec.cpy
Follow the instructions provided in the Modify book-rec.cpy section of the Modify COBOL Source topic. No modifications are required.
Note: If you have previously done this tutorial and modified the book-rec.cpy, skip this step and continue with Modify Java Source.
Modify Java Source
Complete all sections of the Modify Java Source topic.
Note: If you have previously done this tutorial and modified the java source, skip this step and continue with Modify XML Source.
Add and Modify XML
Add context.xml
Following the instructions in the Add context.xml section of the Add and Modify XML topic, with this modification:
  • Copy and paste the following code into the context.xml file: 
    <Resource name="ora" auth="Container" type="javax.sql.DataSource"
               maxActive="10" maxIdle="10" maxWait="10000"
               username="SCOTT" password="tiger"
               driverClassName="oracle.jdbc.driver.OracleDriver"
               url="jdbc:oracle:thin:@localhost:1521/XE" />
Note: If you have previously done this tutorial and added the context.xml file, simply replace the contents of the file with the code shown here.
Modify web.xml
Follow the instructions in the Modify web.xml section of the Add and Modify XML topic with the following modification:
  • Replace the entire contents of web.xml with the following: 
    <?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xmlns="http://java.sun.com/xml/ns/javaee"
         xmlns:web="http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd"
         xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd"
         version="2.5">
  <display-name>JSPBookDemo</display-name>
  <servlet>
    <servlet-name>BookServlet</servlet-name>
    <servlet-class>com.microfocus.book.BookServlet</servlet-class>
  </servlet>
  <servlet-mapping>
    <servlet-name>BookServlet</servlet-name>
    <url-pattern>/view</url-pattern>
  </servlet-mapping>
  <welcome-file-list>
    <welcome-file>view</welcome-file>
  </welcome-file-list>
  <resource-ref>
      <description>DB Connection</description>
      <res-ref-name>pubs</res-ref-name>
      <res-type>javax.sql.DataSource</res-type>
      <res-auth>Container</res-auth>
  </resource-ref>
</web-app>
Run the JSP Application
No modifications are required for Oracle. Follow the instructions in the Run the JSP Application topic.
Parent topic: Tutorial: Reusing Existing SQL Programs in a Java Environment
Previous topic: Modifications for DB2 LUW
Related concepts
Tutorial: Reusing Existing COBOL Programs in a Java Web Services Environment
Install and Configure a JDBC Driver
Create a SQL Server Database and Connection
Modify COBOL Source
Modify Java Source
Add and Modify XML
Run the JSP Application