PDF Data Entry - Part III

Apologies for the brief hiatus ... I have meeting my nemesis, our local Dentist. I have a somewhat irrational or maybe not for some of you, fear of that 'chair'. Im sitting here drinking lunch and I dont mean a beer it's due to the ache in my lower jaw after having a not so wise wisdom tooth removed - more here if you are so inclined, Im not and will not be for some time.


In the last article we had got as far as building the form and adding a button - those of you that want the dynamic insertion will be pleased to hear that I have logged enhancment requests to get that functionality into a release. Now we need to work on a server side object to accept the data, parse it and then insert it into a table. I chose a servlet but a jsp will work just as well, if not better if you are in some business flow, I assume you may even be able to build a page in APEX to accept the data - Im still a relative beginner with APEX so I'll bow to someone elses input on that subject.


The XFDF XML format needs to be handled on the server:

<?xml version="1.0" encoding="UTF-8"?>

<xfdf xmlns="http://ns.adobe.com/xfdf/" xml:space="preserve">
<fields>
 <field name="DEPT_ID">
  <value>50</value>
 </field>
 <field name="EMAIL">
  <value>tim.dexter@oracle.com</value>
 </field>
 <field name="FIRST_NAME">
  <value>Tim</value>
 </field>
 <field name="HIRE_DATE">
  <value>01jan2003</value>
 </field>
 <field name="JOB_ID">
  <value>ST_MAN</value>
 </field>
 <field name="LAST_NAME">
  <value>Dexter</value>
 </field>
 <field name="MANAGER_ID">
  <value>123</value>
 </field>
 <field name="PHONE_NUMBER">
  <value>343434343</value>
 </field>
 <field name="SALARY">
  <value>44444</value>
 </field>
 </fields>


Its a simple name-value pair format. We could use a SAX or DOM parser but why bother, Publisher has a parser specifically for XFDF. Again its not documented but it is public and we'll get it documented. For now you need the following:

        ServletInputStream in = request.getInputStream();
        FPXMLParser fpXMLParser = new FPXMLParser();
        try {
            fpXMLParser.parseXML(in);
        } catch (Exception e) {
            e.printStackTrace();
        }
        Hashtable nameValuePairs = fpXMLParser.getSimpleMergingData();

The first line is standard servlet stuff to get the incoming data stream. Then we invoke the XFDF parser and initialize it with the input stream in the try-catch block. There are not too many methods to the parser but the most useful is the getSimpleMerging Data(), this drops the name/value pairs into a Hashtable object for you. Now as you might have realized we're working in memory with this parser, if you have a huge amount of data, and I mean 100megs to handle, think about using the SAXParser instead.

Now we have this object we can use it to populate a table in the data base, we'll need to enumerate through the hashtable and pull the fields and then the values to construct a sql insert statement. In this code you are not going to see any validation of the code - you will obviously need it. You could either put it into the PDF form, it supports javascript and there is ahost of web references on how to write javascript for PDF or you could of course code server side validation. The issue then becomes that if they have made a mistake you need to take their values and represent the form to the user to get it corrected - publisher can do that of course but its up to you.

        Hashtable nameValuePairs = fpXMLParser.getSimpleMergingData();
        StringBuffer sqlStmtBuffer = null;
        StringBuffer sqlColsBuffer = null;
        StringBuffer sqlValsBuffer = null;
        Enumeration fieldNames = nameValuePairs.keys();
        while(fieldNames.hasMoreElements())
        {
          String fieldName = (String)fieldNames.nextElement();
          if("btn_submit".equalsIgnoreCase(fieldName))
          {
            continue;
          }
          if(sqlStmtBuffer == null)
          {
            sqlStmtBuffer = new StringBuffer();
            sqlStmtBuffer.append("insert into employees(");
            sqlColsBuffer = new StringBuffer();
            sqlValsBuffer = new StringBuffer();
            sqlValsBuffer.append(" values(");
          }
          else
          {
            sqlColsBuffer.append(",");
            sqlValsBuffer.append(",");
          }
          sqlColsBuffer.append(fieldName);
          String value = (String)((Hashtable)nameValuePairs
.get(fieldName)).get(Constants.ATTR_V);
          sqlValsBuffer.append("'");
          sqlValsBuffer.append(value);
          sqlValsBuffer.append("'");
        }
        sqlStmtBuffer.append(sqlColsBuffer.append(")").toString());
        sqlStmtBuffer.append(sqlValsBuffer.append(")").toString());

We take the hastable and assign it to an Enumeration object so we can step through the values. We create string buffers to hold the sql statement (sqlStmtBuffer), the column names (sqlColsBuffer) and the values (sqlValsBuffer).
We are not interested in the button value so we check for the button field, 'btn_submit' and if found, we step over it and continue. The string buffers are built up while looping through the name/value pairs. We need some code to get the value:

String value = (String)((Hashtable)nameValuePairs.get(fieldName))
.get(Constants.ATTR_V);


This grabs the value from the hashtable object so it can then be inserted into the string buffer.


At the end of the loop we then append the sqlColsBuffer and the sqlValsBuffer to the sqlStmtBuffer and we get the following:

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

Now those of you that have taken a look at the EMPLOYEES table will have noticed that there is an EMPLOYEE_ID primary key on the table, so we would need to generate an employee number from the appropriate sequence.


Not to tease you but we'll pull it together in the next article ...

Comments:

Genau dieses Thema werde ich in meinem eigenen Blog auch bald betrachten. Vielen Dank für die Inspiration!

Posted by Zahnarzt Kevelaer on February 02, 2010 at 11:11 AM MST #

Great post. How long have you been running this blog for? I need to improve mine a bit! Clyde Marrello

Posted by Clyde Marrello on February 28, 2010 at 09:35 AM MST #

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