Oracle BI Publisher Blog

Gums still aching here in stormy Colorado but Im finally bringing things together into a single servlet to receive, process and insert the data. Last time I mentioned that we needed to get an employee id from the appropriate database sequence prior to executing the 'insert' command.

The following code will retrieve the next ID from the employees sequence:

    private int getEmpID()
    {
      int empID = 0;
      try
      {
        // Try and geta connection to the db
        Connection conn = getConnection();
        // fetch the report name based on the request id
        PreparedStatement getID = conn.prepareStatement("select employees_seq.nextval from dual");
        
        // get the query result in to a result set and then assign the
        // value to a variable we can pass back to the calling method
        ResultSet idRslt = getID.executeQuery();
        idRslt.next();
        empID = idRslt.getInt(1);
        
        //Clean up 
        idRslt.close();
        getID.close();
        conn.close();
        
      }
      catch (SQLException eSQL)
      {
        System.err.println("Could not create connection");
        eSQL.printStackTrace();
      }
      catch (Exception e)
      {
       System.err.println("Exception thrown");
       e.printStackTrace(); 
      }
       
      return empID;
    } 

This assumes we have a method called getConnection() to get a connection to the database. Now we have the employee ID we can correctly construct the sql statement to insert a new employee. Now we can complete the insert statement string by adding the employee id and its value

        //Add the emp id column and value
         sqlColsBuffer.append(",EMPLOYEE_ID");
         sqlValsBuffer.append(","+Integer.toString(getEmpID()));
        
        sqlStmtBuffer.append(sqlColsBuffer.append(")").toString());
        sqlStmtBuffer.append(sqlValsBuffer.append(")").toString());

insert into employees(DEPT_ID,SALARY,EMAIL,LAST_NAME,PHONE_NUMBER,
                                   JOB_ID,HIRE_DATE,FIRST_NAME,EMPLOYEE_ID) 
values ('50','44444','tim.dexter@oracle.com','Dexter','343434343'
                                 ,'ST_MAN','01jan2003','Tim',214)

If you look closely you'll see that the hire date is in the wrong format - we need to force the user to enter it in the correct format otherwise our insert will fail. Something else you may have noticed is that DEPT_ID is not the column name in the EMPLOYEES table it should be DEPARTMENT_ID. Watch out! Either match the field names in the PDF form to the column names or do the conversion in your code to the right names. 

Now we can insert the record:

    private boolean insertRecord(String sqlStmt){
        boolean insFlg = true;
        Connection conn = getConnection();
        try
        {
        Statement stmt = conn.createStatement();
        stmt.execute(sqlStmt);
        conn.commit();
        stmt.close();
        conn.close();
        
        }
        catch (SQLException sqle){
            sqle.printStackTrace();
            insFlg = false;
        }
    return insFlg;   
    }

We pass the SQL statement in as a parameter when we call the insert method:

        if (!insertRecord(sqlStmtBuffer.toString())){
            
            out.println("Error: 45500: The employee registry could not be" +
            "updated with your information.<BR>" +
            "Please contact as administrator on x66633 for help.");
        }

I have the method returning a boolean so that I can inform the user if the insertion fails and provide a message to them to contact an administrator.
To prove it worked, here I am in the table as a new employee ...

A nice surprise the date got converted as the record was inserted ... there is a trigger on the table.

Hope you found this series useful or at least interesting, Publisher has a lot to offer to help with data entry via PDF. With some java coding you can build a system quite quickly, I built this demo in about a day. I went for simple db connections and servlets just to allow me to focus on the publisher aspects, you of course can use other methods and be a lot tighter on your server side validation.
The XFDF XML format does have a great advantage in that its a fixed schema so you could conceivably build multiple forms that feed into a single servlet on the server and then it can redirect to table handlers based on some hidden field value.
I have to thank Incheol Kang from the development team for some help along the way and we got 2 enhancement requests out of this exercise so it was useful for us. If you are interested in the source code its all in this zip file.
Happy Data Entering!