Using External Objects form Withtin the IDRS

While the IDRS' RML has an extremly powerful native dialect for extracting data and presenting it, there are several things the RML can't do.  This was done intentianally to ensure the seperation of presentation logic and business logic.  What is the diference between business logic and presentation logic?  An example of presentatino logic is using the <ifchange> tag to break dataset into groups.  Business logic would be if there were certain conditions that needed to be spaecified inorder to display a group of data or the format that it is presented in.  While RML doesn't nativly support any way of directly aplying business logic to a report, it does allow for a report to call methods form external objects.

This document is going to build a small report simmilar to the one in the IDRS beginner's tutorial.  This tutorial will center on the use of objects withtin the IDRS and will assume a certain level of profivcentcy in basic RML syntax.  In this example we will build a simple invoice page that will return an invoice based on the user who is currently logged in.

The final invoice will look like this :



Marc's Invoice

Marc Boorshtein
123 I was Up All Night Ln
Debugville, NY 02125

Customer ID:123
 
 
Item # Description Cost
123 Redhat Linux 6.2 $35.00
678 Tux Inflatable Chair $15.00
Total
$50.00

Thank you for buying at my store


Before we can build the RML report, we must first define these things :

The data we are going to use will be in this given table :

CREATE TABLE invoice (
  custid int,
  firstname varchar(10),
  lastname varchar(10),
  street varchar(30),
  city varchar(20),
  state varchar(2),
  zip varcahr(5),
  item int,
  itemdesc varchar(20),
  itemcost float
);
 
custid firstname lastname street city state zip item itemdesc itemcost
123 Marc Boorshtein 123 I was Up All Night Ln Debugville NY 02125 123 Redhat Linux 6.2 35.0
123 Marc Boorshtein 123 I was Up All Night Ln Debugville NY 02125 678 Tux Inflatable Chair 15.0
325 Laura Loomis 127 I was Up All Night Ln Boston MA 01701 458 Mandrake Linux 7.1 35.0

While this table is not an ideal relational model, we'll use it anyway.

Now that we have a datamodel, lets decide what we're going to do inside of external objects.  While this exercise will not demonstrate how to apply business logic inside of external methods, it will be used to show all of the features of external methods withtin the IDRS.  Our object will have methods to perform these actions :

This is what the java class will look like :

import java.sql.*; //classes for connecting to a database
import IdrsDb;  //Used to retrieve data from an IDRS result set

public class LoadTotal {
  private String totalFieldName;

  public LoadTotal(String totalFieldName) {
    this.totalFieldName = totalFieldName;  //This holds onto the what we're going to call the field that will hold the total cost
 }

  public Resultset getTotalSet(Integer id, java.sql.Connection con) throws Exception {
    String sql = "SELECT sum(itemcost) as" + this.totalFieldName + " FROM invoice WHERE custid = " + id.toString() + ";"; //creates a SQL statement that we can then use to get the total
    Statement stmt = con.createStatement();
     return stmt.executeQuery();
  }

  public String getTotal(String caption, IdrsDb db) throws Exception {
    String totalReturn;
    //returns the total amount stored
    String total = db.getFieldData(this.totalFieldName,"number, currency");
    totalReturn = "<td>" + caption + "</td><td>" + total + "</td>";
    return totalReturn;
  }

}



This simple class is straight forward.  The constructor loads what we will call the field that holds the total amount due.  getTotalSet() is passed the userid of the currently logged in user and a connection to the database and returns the resultset for the given sql statement.  getTotal() is given the caption for total on the final report and the total cost in currency format.

Inorder to use this class from inside an RML document a few things need to be done first :

  1. Make sure that the IdrsDb class is in the CLASSPATH
  2. compile the above code, "LoadTotal.java"
  3. Add the resulting LoadTotal.class to the CLASSPATH
Once these steps are done, we can begin to build the RML report :


<HEAD>
  <title>This is a test using objects in the IDRS</title>
  <object id="obj">
    <class>LoadTotal</class>
    <constructor>
      <varType>String</varType>
    </constructor>
    <method>
      <name>getTotalSet</name>
      <varType>UserID</vartype>
      <varType>Connection</vartype>
    </method>
    <method>
      <name>getTotal</name>
      <varType>String</varType>
      <varType>DataSet</varType>
    </method>
  </object>

  <db id="header">
    <dbdriver>postgresql.Driver</dbdriver>
    <username>web</username>
    <dbname>jdbc:postgresql:invoices</dbname>
    <SQL>
      <SRC>
        SELECT custid, firstname,lastname,street,city,state, zip FROM invoice WHERE custid = ?;
      </SRC>
      <varType>UserID</varType>
    </SQL>
  </db>

  <db id="details">
    <usedb>header</usedb>
    <SQL>
      <SRC>
        SELECT item, itemdesc, itemcost FROM invoice WHERE custid = ?;
      </SRC>
      <vartype>UserID</vartype>
    </SQL>
  </db>

  <db id="total">
    <usedb>header</usedb>
    <usemethod objid="obj">getTotalSet</usemethod>
  </db>

  <varlist id="list">
    <vartype>String</vartype>
    <dbresult>total</dbresult>
  </varlist>
</HEAD>



    The <HEAD> for our rml report looks wrather complicated, but it really isn't.  It's larger then would really be nessasary for this task, but it shows all of the things that can be done with the new features of the IDRS.  Before you continue with this tutorial, please read the RML Specification.

    The first block of RML is an <object> block.  The <object> tag is similar to the <db> tag in that it's only atribute is an ID.  This ID is used to refrence the object throughout the RML report.  The <class> tag tells the IDRS what class to use when loading this object.  The name we use is LoadTotal, the same name of the class we wrote earlier.  If this class were part of a larger package, we would have had to include the package names as well.  For example, if our class had been part of the com.IDRS.Samples package, we would have had to use com.IDRS.Samples.LoadTotal as the classname.

    Next comes the <constructor> tag.  This tag tells the IDRS what values should be passed to the constructor when the object is created.  This is when any information that would be imporant to the entire object should be entered.  In this case we are going to pass a string that will be used in the SQL statement that we will use to retrieve the total from the invoice database.  This information will be provided by the user when they fill in the form that will be used to call this report.  If there were no variables that needed to be passed to the constructor of the object, then the <constructor> tags would have nothing between them, but still need to be on seperate lines.

    Next comes the method declarations.  Our first method, getTotalSet, is used to retrieve the total amount in the invoice from the database.  We pass this method two arguments, the UserID and a Connection.  The UserID is used to identify who we're going to retrieve the data for and the connection is used to get the information from the database.  One of the main features of the IDRS is database connection pooling, so even if you are using an external object to build the resultset, you can still have the benefit of a prebuilt database connection.  The return of this method MUST be a resulltset, this is because it will be used within a <db>.   The next method declaration is getTotal.  This method takes a string and a dataset.  The dataset is unspecififed because it will be defined later.  This allows for the method of an object to be re-used in different situations.  Again, there is no return type specified.  This method will be used inside the <body> and MUST return a string.

    The next block in the rml report is a <db> tag.  This is a standard <db> tag, but it is uses a UserID as the <vartype> for an argument.  This makes the report user centric, so that  a user can only see his/her information when logged in.  The next <db> tag has the exact same situation.

    The next <db> tag is somewhat different from the previous two.  Instead of a <sql> or <storedproc> tag, there's a <usemethod> tag.  This tag tells the IDRS that an external method is going to be used to retrieve the results for this <db> tag.  The method getTotalSet will be called from the obj object.  There are no <vartype> tags.  They don't need to be used becuase they were already used when declaring the method.

    The last part of the header is a <varlist> tag.  <Varlist> tags are used to tell the IDRS what values are going to be used when a method is called within the <body> of an rml report.  Between these tags are the <vartype> tags that we're use to, and also the <dbresult> tag.  This tag tells the IDRS to pass a given <db>'s dataset to the mthod we are going to call.  In this case we are telling the IDRS to use the total <db>.

    Now that we have built the <HEAD> of our report, lets build the <body> of the report :


<BODY>
  <field>header.firstname</field>'s Invoice<p>

  <field>header.firstname</field>
  <field>header.lastname</field><br>
  <field>header.street</field><br>
  <field>header.city</field>, <field>header.state</field> <field>header.zip</field><p>

  Customer ID : <field>header.custid</field><p>
  <table border="1">
    <tr>
      <td><b>Item #</b></td>
      <td><b>Description</b></td>
      <td><b>Cost</b></td>
    </tr>
    <repeat id="details" color1="navy" color2="white">
      <tr bgcolor="<field><backcolor></field>">
        <td> <font color="<field><forecolor></field>"><field>details.item</field></font></td>
        <td> <font color="<field><forecolor></field>"><field>details.itemdesc</field></font></td>
        <td> <font color="<field><forecolor></field>"><field format="number, currency">details.itemcost</field></font></td>
      </tr>
    </repeat>
    <tr>
      <td></td><usemethod objid="obj" varlist="list">getTotal</usemethod></tr>
  </table>
  <p> Thank you for buying at my store
</BODY>



    Save this document as "object.rml".

     The <body> of our document is fairly simple.  First the header information is printed and the table is begun.  A <repeat> tag prints our table of items.

    After the <repeat> tag their is a <usemethod> tag that is used to extract the total amount for our customer's purchase.  First we tell the IDRS what object the method is part of.  Then we tell the IDRS what <varlist> to use and what method to use.  That's it, the method will execute and the resulting string will be printed to the final report.

    Now that we have a report, we must deploy the report so that it can be accessed by the IDRS.  There are several pices of information that we must give the IDRS.  If you need to findout the particulars of what the diferent pices of information are, please read the beginner's tutorial.  This is the deployment information that we are going to use:
 
DocID :  2
DocName :  object_test
DoocSrc object.rml
DocGroups :  1,4,
DocVarSrc : (stored in objsmpl.var)
Enter Total Field Alias : <input type="text" name="obj_alias"><br>
Enter the caption for the Total Field : <input type="text" name="list_totalname"><br>
<input type="submit">
isFile : false
DocParams : obj_alias:header_UserID:details_UserID:total_UserID:total_Connection:list_totalname:list_Dataset:
DocConns :  jdbc:postgresql:invoices,

    It is beyond the scope of this document to explain what all of these parameters are.  If you need to learn about this, please read the beginner's tutorial.  The only piece we will discuss is DocParams.

    If you look at our DocVarSrc field, you will see that there are only two fields for input.  They follow the IDRS naming convention and are straight forward.  If you look at the DocParams field you will see that it contins both of these input fields, but also several other fields.  These fields are special commands that tell the IDRS that special values are going to be used.  Lets look at all of these parameters and their relationship to the report we built.

    obj_alias: This tells the IDRS to use the value of the obj_alias input as the FIRST value to be passed to the obj object.  Why do I say this is the first value to be passed if it is the only one?  The IDRS processes input through a sequential process. The first value corresponding to an object is processed using the first <vartype> tag.  If there is a conflict (for instance you try  to assign "dfg" to a <vartype>int</vartype>) or there is mismatch in the number of values and <vartype> tags (lets say there are 2 values and 4 <vartype> tags), then an error will occurr.

    header_UserID:  This tells the IDRS that the id of the currently logged in user will be passed to the header <db> in the order it is given.  Here it is the only parameter that is being passed.

    details_UserID:  Same as header_UserID,. except now the user id is being passed to the details <db>.

    total_UserID:  Same as header_UserID,. except now the user id is being passed to the total <db>.

    total_Connection: This tells the IDRS that the database connection being requested by the total <db> is going to be passed to the method that will be called.  This allows an external method to take advantage of the IDRS' database connection pooling capabilities.

    list_totalname:  This tells the IDRS to pass the value of total_listname, one of the input boxes we defined in our DocVarSrc field to the list <varlist>.

    list_Dataset:  This tells the IDRS that a dataset is going to be passed to the method being called.  The Dataset is defined in the list <varlist> of our report, we mearly have to tell the IDRS that there will be a dataset here.

    Now that we have all of the information we need to deploy this document, lets deploy this report to the IDRS using the InsertToIDRS tool:



java InsertToIDRS -insgvfpc jdbc:postgresql:idrs webadmin pass123 2 object_test object.rml 1,4, objsmple.var  false obj_alias:header_UserID:details_UserID:total_UserID:total_Connection:list_totalname:list_Dataset: jdbc:postgresql:invoices,

    The final step to deploying our system is to tell the IDRS to pool connections to our invoices database.  This is done by changing the <numbds> tag in the IDRS' web.xml file by increasing one to this number.  Then add another set of db-initalization paramaters to the web.xml file.  To learn more information about setting up the IDRS' web.xml file, see the initialzation params part of the IDRS documentation.

    Now that the configuration file is configured, restart the servlet runner so that it will update the connection pools and the report is ready to use!  The final step is to add a user named 'marc' with a userid of 231 and in either group 1 or 4.

    Please send any feedback to mboorshtein@yahoo.com.