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 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 :
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 :
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;
}
}
Inorder to use this class from inside an RML document a few things need to be done first :
<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 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 :
<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>
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:
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.