PDF Data Entry - Part IV

 

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());
This gets us the followning statement

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


EmpDataForm4:


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!

 

Comments:

Hello. It looks like some of your organizational needs could be taken care of by looking in our direction for quicker solutions. Are you embarking on cost reduction while accommodating excessive work from your customers, without compromising your quality of work? Instead of sending your customers away to your competitors when your staffing is not adequate for your work volume, RDS is your answer to maintaining your customers. Our pricing is structured to allow your company to continue making profits from work that is outsourced to us. We are always looking for ways to help you increase your profits without compromising quality.. For more information, visit our website at http://www.risingdatasolutions.com

Posted by Antonio Sulit Jr. on March 22, 2010 at 03:35 AM MDT #

Post a Comment:
  • HTML Syntax: NOT allowed
About

Follow bipublisher on Twitter Find Us on Facebook BI Publisher Youtube ChannelDiscussion Forum

Join our BI Publisher community to get the most and keep updated with the latest news, How-to, Solutions! Share your feedback and let us hear your voice @bipublisher on Twitter, on our official Facebook page, and Youtube!

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today