Tutorial: Reusing Existing SQL Programs in a Java Environment

As an extension to Tutorial: Reusing Existing COBOL Programs in a Java Web Services Environment, this tutorial shows you how to do the same thing with an SQL COBOL application using SQL Server and the Tomcat 7 application server. Additional information regarding DB2 LUW and Oracle applications is also provided.

 

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 Microsoft SQL Server 2008 or later, configured for SQL Server authentication.

After completing the prerequisite tutorial, complete this tutorial by following the instructions in topics 1 through 6 in the order listed. These topics walk you through the demonstration using a SQL Server database. If you want to use a DB2 database, start with Modifications for DB2 LUW. To use an Oracle database, start with Modifications for Oracle.

 

Install and Configure a JDBC Driver

Download and install a SQL Server JDBC driver to use in this tutorial; then make the driver available to the Apache Tomcat application server.

Install a JDBC Driver

As this tutorial uses SQL Server, you need a SQL Server-compatible JDBC driver. Download a Microsoft JDBC SQL Server Type 4 driver using this link, and install it using the Install Instructions on the download page:

Download JDBC Drivers

The name of the driver file is sqljdbc4[n].jar where n is a single digit and is appended to the filename for some versions of the driver. Be sure to take note of the location of this file after installing it.

 

Copy the JDBC Driver to Tomcat

To run your application, the Tomcat application server must find an SQL Server JDBC driver file in the Apache Tomcat installation lib subdirectory. To ensure that Tomcat finds the file there, copy the installed sqljdbc4[n].jar file to the lib subdirectory of your Apache Tomcat installation.

 

Create a SQL Server Database and Connection

Create a SQL Server database, define a database connection to the database, and load the database using a provided SQL script.

 

Create the PUBS SQL Server Database

Using SQL Server Management Studio, create a new SQL Server database, PUBS, on your local default instance of SQL Server, and configure it to use SQL Server authentication by providing a user ID and password. See your SQL Server documentation for instructions.

 

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 a SQL Server Connection

Using the Data Source Explorer in Eclipse, create a JDBC connection to the SQL Server PUBS database.

  1. From the Main Menu, select Window > Show View > Other > Data Management > Data Source Explorer.
  2. In the Data Source Explorer, right-click Database Connections; then select New from the context menu.
  3. Select the SQL Server profile type from the list; then click Next.
  4. Click New Driver Definition  .
  5. On the Name/Type tab, select the Microsoft SQL Server 2008 JDBC Driver template.
  6. In the Driver name field, type a unique name to identify the connection.
  7. On the JAR List tab, click Add JAR/Zip.
  8. Browse to the directory where the sqljdbc4.jar is installed; then double-click the file to select it.

    This places the full path and file onto the Driver files list.

  9. If the Driver files list contains an sqljdbc.jar file, select the file; then click Remove JAR/Zip.
  10. On the Properties tab complete the properties specifications as follows:

    Property

    Value

    Connection URL jdbc:sqlserver://localhost:1433;databaseName=PUBS
    Database Name PUBS
    Driver Class com.microsoft.sqlserver.jdbc.SQLServerDriver
    Password SQLServerPassword
    User ID SQLServerUserID

    Where SQLServerUserID and SQLServerPassword are the user ID and password you supplied for authentication when creating the PUBS SQL Server database.

  11. Click OK.

    This returns you to the New Connection Profile dialog box.

  12. Check Save password.
  13. Click Test Connection to verify the connection.
  14. 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

We provide an SQL script for you to run against the PUBS database. This script creates the required table and loads it with data. Because you run the script from the Project Explorer, you must first add it to the project.

  1. In the Project Explorer, expand CobolBook.
  2. Right-click src; then select New > Other.
  3. Expand General; then select File.
  4. Click Next.
  5. In the File name field, type SetupSQLBooks.sql; then click Finish.

    This opens the empty SetupSQLBooks.sql file in the file editor.

  6. Copy the following SQL script and paste it into the SetupSQLBooks.sql file:
    USE PUBS;
    GO
    
    CREATE TABLE BOOKS (
    	TITLE		VARCHAR(50) NOT NULL,
    	TYPE		VARCHAR(20) NOT NULL,
    	AUTHOR		VARCHAR(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
  7. Click File > Save to save the SQL script.

 

Execute the SQL Script

  1. In the Eclipse file editor window that contains the SetupSQLBooks.sql file, use the drop-down list at the top to set the Type to SQL Server_2008.
  2. Select the name of your SQL Server connection from the Name drop-down list.
  3. Right-click on any blank area in the body of the script; then select Execute All from the context menu.

 

Modify COBOL Source

Modify the COBOL source code in the CobolBook project, or replace the existing COBOL source with new code to access your SQL Server database.

 

Replace book.cbl with sqlbook.cbl

  1. In the Project Explorer, expand CobolBook > src > (default package).
  2. Right-click book.cbl; then select Delete from the context menu.
  3. Click OK to delete the file.
  4. Again in the Project Explorer, right-click the src entry; then select New > Other.
  5. Expand General; then select File.
  6. Click Next.
  7. In the File name field, type sqlbook.cbl; then click Finish.

    This opens the empty sqlbook.cbl file in the file editor.

  8. Copy the following COBOL SQL program and paste it into the sqlbook.cbl file:
    ********************************************************************************************************
          *
          *  Copyright (C) Micro Focus IP Development Limited 2010-2011. All rights reserved.
          *
          *  This sample code is supplied for demonstration purposes only on an "as is" basis and "is for use at
          *  your own risk".
          *
          ********************************************************************************************************
          $set sql(dbman=jdbc, behavior=mainframe) 
          $set SQL(JNDIENC=java:/comp/env) 
           program-id. BookLegacy.
    
           working-storage section.
           
               exec sql include sqlca end-exec.
    
           01 record-count pic 99.
    
           linkage section.
           01 lnk-function       pic x.
               88 read-record    value "1".
               88 add-record     value "2".
               88 delete-record  value "3".
               88 next-record    value "4".
           01 lnk-sqlstate       pic x(5).
           01 lnk-sqlmessage     pic x(80).
           
           copy "book-rec.cpy" replacing ==(prefix)== by ==lnk-b==.
    
           procedure division using by value lnk-function
                                    by reference lnk-b-details
                                    by reference lnk-sqlstate
                                    by reference lnk-sqlmessage.
           main section.
    
               exec sql connect to PUBS end-exec
               if sqlcode not >= 0
                   move sqlstate to lnk-sqlstate
                   move sqlerrmc(1:sqlerrml) to lnk-sqlmessage
                   move 'Connection error' to lnk-b-title
                   move sqlstate to lnk-b-author
                   move '00000' to lnk-sqlstate
                   move sqlerrmc(1:sqlerrml) to lnk-sqlmessage
                   goback
               end-if
               
               evaluate true
                   when read-record
                       perform do-read-record
                   when add-record
                       perform do-add-record
    
                   when delete-record
                       perform do-delete-record
                   when next-record
                       perform do-next-record
               end-evaluate
                         
               move sqlstate to lnk-sqlstate
               move sqlerrmc(1:sqlerrml) to lnk-sqlmessage
               
               exec sql disconnect end-exec
           
               exit program
               stop run
               .
    
           do-read-record section.
    
               evaluate true
                    when lnk-b-stockno <> spaces
                        exec sql 
                           select TITLE, TYPE, AUTHOR, STOCKNO, ISBN, RETAIL, ONHAND, SOLD 
                               into :lnk-b-title, :lnk-b-type, :lnk-b-author, :lnk-b-stockno, :lnk-b-isbn, :lnk-b-retail, :lnk-b-onhand, :lnk-b-sold
                           from BOOKS where STOCKNO = :lnk-b-stockno
                        end-exec
    
                    when lnk-b-title <> spaces
                        exec sql 
                           select TITLE, TYPE, AUTHOR, STOCKNO, ISBN, RETAIL, ONHAND, SOLD 
                               into :lnk-b-title, :lnk-b-type, :lnk-b-author, :lnk-b-stockno, :lnk-b-isbn, :lnk-b-retail, :lnk-b-onhand, :lnk-b-sold
                           from BOOKS where TITLE = :lnk-b-title
                        end-exec
    
                    when lnk-b-author <> spaces
                        exec sql 
                           select TITLE, TYPE, AUTHOR, STOCKNO, ISBN, RETAIL, ONHAND, SOLD 
                               into :lnk-b-title, :lnk-b-type, :lnk-b-author, :lnk-b-stockno, :lnk-b-isbn, :lnk-b-retail, :lnk-b-onhand, :lnk-b-sold
                           from BOOKS where AUTHOR = :lnk-b-author
                        end-exec
    
                    when other
          *>------------No key specified - return unsuccessful read
                         move "02000" to sqlstate
                         move "Record not found" to sqlerrmc
                         move 15 to sqlerrml
    
               end-evaluate
               
               if sqlcode not = 0
                   initialize lnk-b-details
                   *>move all '*' to lnk-b-title lnk-b-author lnk-b-type
               end-if
               .
    
           do-next-record section.
               exec sql 
                   select TITLE, TYPE, AUTHOR, STOCKNO, ISBN, RETAIL, ONHAND, SOLD 
                       into :lnk-b-title, :lnk-b-type, :lnk-b-author, :lnk-b-stockno, :lnk-b-isbn, :lnk-b-retail, :lnk-b-onhand, :lnk-b-sold
                   from BOOKS where STOCKNO > :lnk-b-stockno
               end-exec
    
               if sqlcode <> 0 AND sqlcode NOT EQUAL 1
                   initialize lnk-b-details
                   *>move all '*' to lnk-b-title lnk-b-author lnk-b-type
               end-if
               .
    
           do-add-record section.
               
               exec sql
                   insert into BOOKS (TITLE, TYPE, AUTHOR, STOCKNO, ISBN, RETAIL, ONHAND, SOLD)
                   values (:lnk-b-title, :lnk-b-type, :lnk-b-author, :lnk-b-stockno, :lnk-b-isbn, :lnk-b-retail, :lnk-b-onhand, :lnk-b-sold)
               end-exec
               if sqlcode = 0
                   exec sql commit end-exec
               end-if
               .
    
           do-delete-record section.
    
               evaluate true
                   when lnk-b-stockno <> spaces                
                       exec sql delete from BOOKS where STOCKNO = :lnk-b-stockno end-exec
    
                   when lnk-b-title <> spaces
                       exec sql delete from BOOKS where TITLE = :lnk-b-title end-exec
    
                   when lnk-b-author <> spaces
                       exec sql delete from BOOKS where AUTHOR = :lnk-b-author end-exec
    
                   when other
          *>------------No key specified - return unsuccessful read
                       move "02000" to sqlstate
                       move "Record not found" to sqlerrmc
                       move 15 to sqlerrml
               end-evaluate
               if sqlcode = 0
                   exec sql commit end-exec
               end-if
  9. Click File > Save to save the program.

 

Modify book-rec.cpy

  1. In the Project Explorer under CobolBook > src, double-click book-rec.cpy to open it in the file editor.
  2. Replace the last line of code with the following:
    03 (prefix)-sold          pic s9(5) comp-3.
  3. Click File > Save to save your changes.

 

Modify Java Source

Modify the Java source code in the JSPBookDemo project to access the SQL Server database.

 

Modify BookBean.java

  1. In the Project Explorer expland JSPBookDemo > Java Resources > src.
  2. Double-click BookBean.java to open it in the file editor.
  3. Replace the entire contents of the file with the following:
    package com.microfocus.book;
    
    import com.microfocus.cobol.program.ScaledInteger;
    
    public class BookBean
    {
      private final String _stockno;
      private final String _isbn;
      private final String _title;
      private final String _author;
      private final String _type;
      private final String _price;
      private final String _onhand;
      private final String _sold;
      private final String _stockval;
      
      BookBean(String stockno, String isbn, String title, String author,
          String type, String price, String onhand, String sold, String stockval)
      {
        this._stockno = stockno;
        this._isbn = isbn;      
        this._title = title;     
        this._author = author;    
        this._type = type;      
        this._price = price;     
        this._onhand = onhand;    
        this._sold = sold;      
        this._stockval = stockval;
      }
      
      public String getStockno()
      {
        return _stockno;
      }
      
      public String getIsbn()
      {
        return _isbn;
      }
      
      public String getTitle()
      {
        return _title;
      }
      
      public String getAuthor()
      {
        return _author;
      }
      
      public String getType()
      {
        return _type;
      }
      
      public String getPrice()
      {
        return _price;
      }
      
      public String getOnhand()
      {
        return _onhand;
      }
      
      public String getSold()
      {
        return _sold;
      }
      
      public String getStockval()
      {
        return _stockval;
      }
      
      public void toDetails(Details details)
      {
        details.setStockno(_stockno);
        details.setIsbn(Long.parseLong(_isbn));
        details.setTitle(_title);
        details.setAuthor(_author);
        details.setType(_type);
        details.setRetail(ScaledInteger.parseScaledInteger(_price));
        
        int onHandInt = Integer.parseInt(_onhand);
        if(onHandInt < 0)
          throw new RuntimeException("The number of books on hand must be 0 or positive");
        details.setOnhand(onHandInt);
        
        int soldInt = Integer.parseInt(_sold);
        if(soldInt < 0)
          throw new RuntimeException("The number of books sold must be 0 or positive");
        details.setSold(soldInt);
      }
      
      public static BookBean fromDetails(Details details)
      {
        String stockno = details.getStockno().trim();
        String isbn = "" + details.getIsbn();
        String title = details.getTitle().trim();
        String author = details.getAuthor().trim();
        String type = details.getType().trim();
        String price = details.getRetail().toString();
        String onhand = "" + details.getOnhand();
        String sold = "" + details.getSold();
        ScaledInteger stockvalInt = details.getRetail().multiply(new ScaledInteger(details.getOnhand(), 0));
        String stockval = stockvalInt.toString();
        
        return new BookBean(stockno, isbn, title, author, type, price, onhand, sold, stockval);
      }
      
      public static BookBean blankBook()
      {
        return msgBook("*************************************");
      }
      
      public static BookBean msgBook(String msg)
      {
        String stockno    = "****";                               
        String isbn       = "*************";                      
        String title      = msg;
        String author     = "*************************************";
        String type       = "****";                               
        String price      = "****";                               
        String onhand     = "****";                               
        String sold       = "****";                               
        String stockval   = "****";                               
        
        return new BookBean(stockno, isbn, title, author, type, price, onhand, sold, stockval);
      }
    }
  4. Click File > Save to save your changes.

 

Modify BookInterface.java

  • Using the same technique, replace the contents of BookInterface.java with the following:
    package com.microfocus.book;
    
    import javax.servlet.http.HttpSession;
    
    import com.microfocus.cobol.program.IObjectControl;
    import com.microfocus.cobol.runtimeservices.IRunUnit;
    import com.microfocus.cobol.runtimeservices.servlet.ServletRunUnitManager;
    
    public class BookInterface
    {
        public static final String READ_RECORD   = "1";
        public static final String ADD_RECORD    = "2";
        public static final String DELETE_RECORD = "3";
        public static final String NEXT_RECORD   = "4";
      
        public final IRunUnit runUnit;
        public final BookLegacy bookLegacy;
      
      public BookInterface(HttpSession session)
      {
        this(ServletRunUnitManager.getManager().GetSessionRunUnit(session));
      }
      
      public BookInterface(IRunUnit runUnit)
      {
        this.runUnit = runUnit;
        BookLegacy bookLegacy = (BookLegacy) runUnit.GetInstance(BookLegacy.class);
        
        if(bookLegacy == null)
        {
          bookLegacy = new BookLegacy();
          runUnit.Add(bookLegacy);
        }
        
        this.bookLegacy = bookLegacy;
      }
      
      public BookBean readBook(String stockNo) throws JavaBookException
      {
        Details details = getObject(Details.class);
        Sqlstate status = getObject(Sqlstate.class);
        Sqlmessage message = getObject(Sqlmessage.class);
        
    
        details.setStockno(stockNo);
        bookLegacy.BookLegacy(READ_RECORD, details, status, message);
        
        throwExceptionIfError(status, message);
        
        return BookBean.fromDetails(details);
      }
      
      public BookBean addBook(BookBean book) throws JavaBookException
      {
        Details details = getObject(Details.class);
        Sqlstate status = getObject(Sqlstate.class);
        Sqlmessage message = getObject(Sqlmessage.class);
        
        book.toDetails(details);
        bookLegacy.BookLegacy(ADD_RECORD, details, status, message);
        
        throwExceptionIfError(status, message);
        
        return BookBean.fromDetails(details);
      }
      
      public BookBean deleteBook(String stockNo) throws JavaBookException
      {
        Details details = getObject(Details.class);
        Sqlstate status = getObject(Sqlstate.class);
        Sqlmessage message = getObject(Sqlmessage.class);
        
        details.setStockno(stockNo);
        bookLegacy.BookLegacy(DELETE_RECORD, details, status, message);
        
        throwExceptionIfError(status, message);
        
        return BookBean.fromDetails(details);
      }
      
      public BookBean nextBook(String stockNo) throws JavaBookException
      {
        Details details = getObject(Details.class);
        Sqlstate status = getObject(Sqlstate.class);
        Sqlmessage message = getObject(Sqlmessage.class);
        
        details.setStockno(stockNo);
        bookLegacy.BookLegacy(NEXT_RECORD, details, status, message);
        
        throwExceptionIfError(status, message);
        
        return BookBean.fromDetails(details);
      }
      
      private static void throwExceptionIfError(Sqlstate status, Sqlmessage message) throws JavaBookException
      {
        String sqlstate = status.getSqlstate();
        if(!"00000".equals(sqlstate) && !"02000".equals(sqlstate) && !"21000".equals(sqlstate))
        {
            throw new JavaBookException(status.getSqlstate() + message.getSqlmessage());
        }
      }
      
      private <T extends IObjectControl> T getObject(Class<T> cls)
      {
        try
        {
          T output = cls.newInstance();
          runUnit.Add(output);
          
          return output;
        }
        catch (Throwable t)
        {
          throw new RuntimeException(t);
        }
      }
    }

 

Modify BookServlet.java

  • Using the same technique, replace the contents of BookServlet.java with the following:
    package com.microfocus.book;
    
    import java.io.PrintWriter;
    import java.io.StringWriter;
    
    import javax.servlet.RequestDispatcher;
    import javax.servlet.http.HttpServlet;
    import javax.servlet.http.HttpServletRequest;
    import javax.servlet.http.HttpServletResponse;
    import javax.servlet.http.HttpSession;
    
    import com.microfocus.cobol.runtimeservices.IRunUnit;
    import com.microfocus.cobol.runtimeservices.servlet.ServletRunUnitManager;
    
    public class BookServlet extends HttpServlet
    {
      private static final long serialVersionUID = -3563065100184185678L;
      
      public static final String STOCK_NO_ATTRIBUTE   = "stockno";
      public static final String TITLE_ATTRIBUTE      = "title";
      public static final String AUTHOR_ATTRIBUTE     = "author";
      public static final String TYPE_ATTRIBUTE     = "type";
      public static final String ISBN_ATTRIBUTE     = "isbn";
      public static final String PRICE_ATTRIBUTE      = "price";
      public static final String ONHAND_ATTRIBUTE     = "onhand";
      public static final String SOLD_ATTRIBUTE     = "sold";
      public static final String STATUS_ATTRIBUTE     = "status";
      public static final String RUN_UNIT_ID_ATTRIBUTE  = "rununitid";
      
      public static final String SUBMIT_PARAMETER     = "submit";
      public static final String READ_PARAMETER     = "Read";
      public static final String ADD_PARAMETER      = "Add";
      public static final String DELETE_PARAMETER     = "Delete";
      public static final String NEXT_PARAMETER     = "Next";
      public static final String END_PARAMETER      = "End Session";
      public static final String ERROR_VALUE        = "ERROR";
      public static final String DEFAULT_VALUE      = "DEFAULT";
      public static final String VIEW_URL         = "/BookJsp.jsp";
      
      protected void doProcessing(HttpServletRequest req, HttpServletResponse res, boolean isGet)
      {
        String subValue = req.getParameter(SUBMIT_PARAMETER);
        
        if(subValue == null)
        {
          subValue = DEFAULT_VALUE;
        }
        
        setRunUnitId(req);
        
      if(subValue.equals(READ_PARAMETER))
      {
        performRead(req, res);
      }
      else if(subValue.equals(ADD_PARAMETER))
      {
        performAdd(req, res);
      }
      else if(subValue.equals(DELETE_PARAMETER))
      {
        performDelete(req, res);
      }
      else if(subValue.equals(NEXT_PARAMETER))
      {
        performNext(req, res);
      }
      else if(subValue.equals(END_PARAMETER))
      {
        performEndSession(req, res);
      }
      else
      {
        outputBlankBook(req, res);
      }
      
        RequestDispatcher dispatcher = getServletContext().getRequestDispatcher(VIEW_URL);
        try
      {
        dispatcher.forward(req, res);
      }
      catch(Exception e)
      {
        throw new RuntimeException(e);
      }
      }
    
      @Override
      protected void doGet(HttpServletRequest req, HttpServletResponse res)
      {
        doProcessing(req, res, true);
      }
      
      @Override
      protected void doPost(HttpServletRequest req, HttpServletResponse res)
      {
        doProcessing(req, res, false);
      }
    
      private void performRead(HttpServletRequest req, HttpServletResponse res)
      {
        BookInterface bookInterface = getBookInterface(req.getSession());
        String bookId = getStockNo(req, res);
        
        if(bookId == null)
        {
          bookId = ERROR_VALUE;
        }
        
        try
        {
          BookBean book = bookInterface.readBook(bookId);
          
          outputBook(req, res, book);
        }
        catch(JavaBookException e)
        {
          outputBookException(req, res, e);
        }
        catch (Exception e)
        {
          outputException(req, res, e);
        }
      }
    
      private void performAdd(HttpServletRequest req, HttpServletResponse res)
      {
        BookInterface bookInterface = getBookInterface(req.getSession());
        
        try
        {
          BookBean book = getBook(req, res);
    
          book = bookInterface.addBook(book);
    
          outputBook(req, res, book);
        }
        catch(JavaBookException e)
        {
          outputBookException(req, res, e);
        }
        catch (Exception e)
        {
          outputException(req, res, e);
        }
      }
    
      private void performDelete(HttpServletRequest req, HttpServletResponse res)
      {
        BookInterface bookInterface = getBookInterface(req.getSession());
        String bookId = getStockNo(req, res);
        
        if(bookId == null)
        {
          bookId = ERROR_VALUE;
        }
        
        try
        {
          BookBean book = bookInterface.deleteBook(bookId);
          
          outputBook(req, res, book);
        }
        catch(JavaBookException e)
        {
          outputBookException(req, res, e);
        }
        catch (Exception e)
        {
          outputException(req, res, e);
        }
      }
    
      private void performNext(HttpServletRequest req, HttpServletResponse res)
      {
        BookInterface bookInterface = getBookInterface(req.getSession());
        String bookId = getStockNo(req, res);
        
        if(bookId == null)
        {
          bookId = ERROR_VALUE;
        }
        
        try
        {
          BookBean book = bookInterface.nextBook(bookId);
          
          outputBook(req, res, book);
        }
        catch(JavaBookException e)
        {
          outputBookException(req, res, e);
        }
        catch (Exception e)
        {
          outputException(req, res, e);
        }
      }
        
      private void performEndSession(HttpServletRequest req, HttpServletResponse res)
      {
          HttpSession session = req.getSession();
          session.invalidate();
          
          outputError(req, res, "Session invalidated");
      }
    
    
      private void outputBlankBook(HttpServletRequest req, HttpServletResponse res)
      {   
            outputBook(req, res, BookBean.blankBook());
      }
    
      private void outputBookException(HttpServletRequest req, HttpServletResponse res, JavaBookException jbe)
      {
            outputError(req, res, jbe.getMessage());
      }
    
      private void outputException(HttpServletRequest req, HttpServletResponse res, Exception e)
      {
        String msg = e.getClass().getName() + " [" + e.getMessage() + "]";
        StringWriter strWriter = new StringWriter();
        PrintWriter printWriter = new PrintWriter(strWriter);
        e.printStackTrace(printWriter);
        req.setAttribute(STATUS_ATTRIBUTE, strWriter.toString());
        outputError(req, res, msg);
      }
    
      private void outputBook(HttpServletRequest req, HttpServletResponse res, BookBean book)
      {
        if(book != null)
        {
          req.setAttribute("book", book);
        }
        else
        {
          req.setAttribute("book", BookBean.msgBook("ERROR! book is null in output book"));
        }
      }
    
      private void outputError(HttpServletRequest req, HttpServletResponse res, String msg)
      {
        outputBook(req, res, BookBean.msgBook(msg));
      }
      
      private BookBean getBook(HttpServletRequest req, HttpServletResponse res)
      {
        return new BookBean
        (
          getStockNo(req, res),
          getIsbn(req, res),
          getTitle(req, res),
          getAuthor(req, res),
          getType(req, res),
          getPrice(req, res),
          getOnhand(req, res),
          getSold(req, res),
          ""
        );
      }
      
      private String getAttribute(HttpServletRequest req, HttpServletResponse res, String attribute)
      {
        String stockNoStr;
        stockNoStr = req.getParameter(attribute);
        if(stockNoStr == null)
        {
            stockNoStr = ERROR_VALUE;
        }
        
        return stockNoStr;
      }
      
      private String getStockNo(HttpServletRequest req, HttpServletResponse res)
      {
        return getAttribute(req, res, STOCK_NO_ATTRIBUTE);
      }
      
      private String getTitle(HttpServletRequest req, HttpServletResponse res)
      {
        return getAttribute(req, res, TITLE_ATTRIBUTE);
      }
      
      private String getAuthor(HttpServletRequest req, HttpServletResponse res)
      {
        return getAttribute(req, res, AUTHOR_ATTRIBUTE);
      }
      
      private String getType(HttpServletRequest req, HttpServletResponse res)
      {
        return getAttribute(req, res, TYPE_ATTRIBUTE);
      }
      
      private String getIsbn(HttpServletRequest req, HttpServletResponse res)
      {
        return getAttribute(req, res, ISBN_ATTRIBUTE);
      }
      
      private String getPrice(HttpServletRequest req, HttpServletResponse res)
      {
        return getAttribute(req, res, PRICE_ATTRIBUTE);
      }
      
      private String getOnhand(HttpServletRequest req, HttpServletResponse res)
      {
        return getAttribute(req, res, ONHAND_ATTRIBUTE);
      }
      
      private String getSold(HttpServletRequest req, HttpServletResponse res)
      {
        return getAttribute(req, res, SOLD_ATTRIBUTE);
      }
      
      private IRunUnit getRunUnit(HttpSession session)
      {
        return ServletRunUnitManager.getManager().GetSessionRunUnit(session);
      }
    
      private void setRunUnitId(HttpServletRequest req)
      {
        HttpSession session = req.getSession();
        IRunUnit runUnit = getRunUnit(session);
        String ruid = "" +  runUnit.getRunUnitID();
        req.setAttribute(RUN_UNIT_ID_ATTRIBUTE, ruid);
      }
      
      private BookInterface getBookInterface(HttpSession session)
      {
        BookInterface output = new BookInterface(session);
        
        return output;
      }
    }
    

 

Modify JavaBookException.java

  • Using the same technique, replace the contents of JavaBookException.java with the following:
    package com.microfocus.book;
    
    import java.util.*;
    
    public class JavaBookException extends Exception
    {
    
      /**
    	 * 
    	 */
      private static final long serialVersionUID = -3882735817601888938L;
    
      private static final Map<String, String> messages;
      private static final String unknownErrorMessage = "Unknown Error: ";
    
      public final String statusCode;
    
      public JavaBookException(String statusCode)
      {
        super(messages.containsKey(statusCode) ? messages.get(statusCode)
            : unknownErrorMessage + statusCode);
        this.statusCode = statusCode;
      }
    
      static
      {
        messages = new HashMap<String, String>();
        messages.put("35", "Error: Data file not found");
        messages.put("23", "Error: Stock item not found");
        messages.put("46", "No more items left");
        messages.put("99", "Error: Item already exists");
        messages.put("01", "Error: File error");
        messages.put("B1", "Error: No key entered");
      }
    }

 

Add and Modify XML

Create a new context.xml file, and modify the existing web.xml file.

 

Add context.xml

We provide the XML code that sets the configuration of the PUBS database. You must add the XML to your project.

  1. In the Project Explorer, expand JSPBookDemo > WebContent.
  2. Right-click META-INF; then select New > Other.
  3. Expand XML; then select XML File.
  4. Click Next.
  5. In the File name field, type context.xml; then click Finish.

    This opens the empty context.xml file in the file editor.

  6. Copy the following XML code and paste it into the context.xml file:
    <?xml version="1.0" encoding="UTF-8"?>
    <Context>
    
    <!-- maxActive: Maximum number of database connections in pool. Make sure you
             configure your mysqld max_connections large enough to handle
             all of your db connections. Set to -1 for no limit.
             -->
           <!-- maxIdle: Maximum number of idle database connections to retain in pool.
             Set to -1 for no limit.  See also the DBCP documentation on this
             and the minEvictableIdleTimeMillis configuration parameter.
             -->
           <!-- maxWait: Maximum time to wait for a database connection to 
             become available in ms, in this example 10 seconds. 
             An Exception is thrown if this timeout is exceeded.  
             Set to -1 to wait indefinitely.
             -->
           <!-- username and password: username and password for the connection -->
           <!-- driverClassName: Class name for the JDBC driver -->
           <!-- url: The JDBC connection url for connecting to the database -->
    
           <Resource name="PUBS" auth="Container" type="javax.sql.DataSource"
                   maxActive="10" maxIdle="10" maxWait="10000"
                   username="SQLServerUserName" password="SQLServerPassword"
                   driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver"
                   url="jdbc:sqlserver://localhost;database=PUBS;"
           />       
    </Context>

    Replacing SQLServerUserName and SQLServerPassword respectively with the user name and password required for SQL Server authentication

  7. Click File > Save to save the file.

 

Modify web.xml

The web.xml file requires modification to accommodate the SQL Server database.

  1. In the Project Explorer expand JSPBookDemo > WebContent > WEB-INF.
  2. Double-click web.xml to open it in the file editor.
  3. Replace the entire contents of the file 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>
  4. Click File > Save to save your changes.

 

Run the JSP Application

Run the JSPBookDemo application on Tomcat 7.

  1. In the Project Explorer, right-click the JSPBookDemo project; then select Run As > Run on Server from the context menu.
  2. On the Run On Server dialog box, be sure that Tomcat v7.0 Server at localhost is selected; then click Finish.

    This starts the Web form.

  3. In the Stock Number field, type 1111; then click Read.

    The Web form shows the database information for the Oliver Twist title.

  4. Click End Session and close the Web form.

 

Modifications for DB2 LUW

Modifications to the tutorial for using a DB2 LUW database instead of a SQL Server database.

 

Prerequisites

You must install IBM DB2 LUW version 10 Express-C 32-bit.

 

Modifications

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

Install and Configure a JDBC Driver
Install a JDBC Driver
This tutorial uses the IBM DB2 LUW 10.5 Express-C JDBC driver included with the database system installer; therefore, no download or installation is required.
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 db2jcc.jar and db2jcc_license_cu.jar files to the lib subdirectory of your Apache Tomcat installation.
Create and Load a DB2 LUW Database
Create the SAMPLE DB2 LUW Database
The tutorial uses the SAMPLE database you can create using the DB2 LUW First Steps tool. See your DB2 LUW documentation for further instructions.
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 a DB2 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 DB2 for Linux, Unix, and Windows profile type.
  • On the New Driver Definition dialog box Name/Type tab, select IBM Data Server Driver for JDBC and SQLJ.
  • On the JAR List tab, follow these instructions:
    1. On the Driver file list, select the db2jcc.jar file; then click Edit JAR/Zip.
    2. Browse to the java subdirectory of your DB2 LUW installation, and double-click the db2jcc.jar file located there.

      This replaces the db2jcc.jar file on the list, and prompts you to update the other .jar file paths to use the same location. Because you are just adding a single .jar file in an alternative directory, updating the path for other files is not required.

    3. Click No.
    4. On the Driver file list, select the db2jcc_license_cisuz.jar file; then click Remove JAR/Zip.
    5. Click Add JAR/Zip.
    6. Browse to the java subdirectory of your DB2 LUW installation, and double-click the db2jcc_license_cu.jar file.

      This adds the file to the Driver file list.

  • On the Properties tab, use the following information:
    Property Value
    Connection URL jdbc:db2://localhost:50000/SAMPLE
    Database Name SAMPLE
    Driver Class com.ibm.db2.jcc.DB2Driver
    Password blank
    User ID blank
  • Follow these steps on the New Connection Profile dialog box:
    1. Check Save password.
    2. In the User name and Password fields respectively, type your DB2 user name and password credentials.
      Note: If your DB2 database does not use its own user name and password, type in your Windows authentication credentials instead.
    3. Click Test Connection.
    4. 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 SetupDB2Books.sql.
  • Add the following contents:
    DROP TABLE BOOKS;
    
    CREATE TABLE BOOKS (
    	TITLE	VARCHAR(50) NOT NULL,
    	TYPE    	VARCHAR(20) NOT NULL,
    	AUTHOR  	VARCHAR(50) NOT NULL,
    	STOCKNO CHAR(4) NOT NULL 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 yourSchema.BOOKS;
    SELECT CURRENT SCHEMA FROM SYSIBM.SYSDUMMY
  • On the second to last line of the script, replace yourSchema with the name of the schema that your SAMPLE database was created under.
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 SetupDB2Books.sql file.
  • Set the Type to DB2 UDB_V9.1.
  • Set the Name to the name of your DB2 connection.
  • In the Database field, select SAMPLE.
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 db2 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="db2" auth="Container" type="javax.sql.DataSource"
                   maxActive="10" maxIdle="10" maxWait="10000"
                   username="db2UserName" password="db2Password"
                   driverClassName="com.ibm.db2.jcc.DB2Driver"
                   url="jdbc:db2://localhost:50000/sample:retrieveMessagesFromServerOnGetMessage=true;"/>

    Replacing db2UserName and db2Password with your DB2 database credentials, or if applicable, your Windows authentication credentials.

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>db2</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 DB2 LUW. Follow the instructions in the Run the JSP Application topic.

 

Modifications for Oracle

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

 

Prerequisites

You must 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.