Stories
Monday 11th, August 2008
Wednesday 16th, July 2008
Monday 7th, July 2008
XPages Example: Calling Java Methods directly from server side JavaScript - performing an SQL query Part 207/07/2008 Domino 8.5 - XPages
This is Part 2 of this example. Click here to read Part 1.
How to Build It
I'll go over the steps in detail here. The steps are actually very few. I found that the SQL.jar file is already included in the project, so you do not need to import any jar files into the Eclipse project. You can download the example Domino database by clicking here: XPagesJSToJava.nsf
Database Setup
Install DB2 if you haven't already. The sample table that we will be using is Department. This is a screen shot of the table and the data.:
**note: I used the Administrator id when generating the sample tables so the schema is Administrator. This might be different for you. You will need to use the correct schema name in your query.
You will need to copy the following db2 jar files from the DB2 installation directory "SQLLIB\java\db2jcc.jar" and "SQLLIB\java\dbjcc_license_cu.jar" to your "Domino\xsp\shared\lib" directory. There might be another way to reference the jar files by editing the Notes.ini, but this works too.
Lotus Notes Designer
Create your new Lotus Notes db XPagesJSToJava.nsf in DDE.
Now switch to the Java perspective by selecting: Window->Open Perspective->Other. You will be presented with the below list of choices. Select Java and click OK.
Expand your project on the Left hand Project navigator. Select the WebContent/WEB-INF Folder:
- right click and select New->Folder. Name the folder source, click Finish.
- right click on the source folder and select New->Package. Name the Package javaMethods
click Finish.
- right click on the javaMethods package and select New->File. Enter SQLQuery.java for the name. Click Finish.
Here's a screen shot of how it should look:
Now double click on the SQLQuery.java and paste in the following code:
package javaMethods;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class SQLQuery{
public static String executeQuery(String connDB,
String connUserName, String connPwd, String query){
String theResult="";
ResultSet resultSet=null;
try {
// load the DB2 Driver
Class.forName("com.ibm.db2.jcc.DB2Driver");
// establish a connection to DB2
Connection db2Conn =
DriverManager.getConnection(connDB,connUserName,connPwd);
Statement st = db2Conn.createStatement();
// execute the query
resultSet = st.executeQuery(query);
while (resultSet.next()) {
//theResult will be the 1st column separated using ";"
theResult=theResult+resultSet.getString(1)+";";
}
}
catch(Exception e){
System.out.println(e);
}
return theResult;
}
} |
We need to add the source folder to the java build path:
- select the project and then select
Project->Properties from the menu (or right click)
- select the Java Build Path and click on Add Folder button. Select the new source folder. Click OK.
Your screen should look like this:
Save this and switch back to the Domino Designer perspective by selecting: Window->Open Perspective->Other. Select Domino Designer.
We will now create the server side JavaScript library for our 2 functions. Expand Code and then select Script Libraries and click on New Server JavaScript Library. Enter
SQLFunctions for the name. Paste in the following code. You need to change the userName and pwd variable according to your DB2 installation. Most likely the connString will be ok if this is on your local machine.
var connString="jdbc:db2://localhost:50000/sample";
var userName="db2admin";
var pwd="password";
function getDeptNumbers(){
var query="SELECT DEPTNO FROM ADMINISTRATOR.DEPARTMENT";
depts=javaMethods.SQLQuery.executeQuery(connString,userName,pwd,query);
return @Explode(depts,";");
}
function getDepartment(deptNo){
print("dept="+deptNo);
var query="SELECT DEPTNAME FROM ADMINISTRATOR.DEPARTMENT WHERE DEPTNO='"+deptNo+"'";
dept=javaMethods.SQLQuery.executeQuery(connString,userName,pwd,query);
return dept;
} |
Create the Employee form. Here is what the form fields look like:
Create the Employee XPage. If you need a refresher in creating the XPage fields, look at one of my earlier examples posted on this site.
Add a Combo Box control bound to the DepartNo field. Click on the Values tab and click on Add Formula Item. For the formula add the JavaScript function getDeptNumbers(). Here's a screen shot:
I added additional functionality that will look up the DepartmentName for any selected DepartNo. Drag a new Button to the table. Change the label to Lookup. On the Event tab for the button, select Script Editor and enter the below remark:
Even though the remark does nothing, it causes the page to make a round trip to the server and will compute any computed text. Another option might have been to add that code to the onChange() event of the Combo Box. (probably a better choice in retrospect)
Add a Computed Field control to the table. Enter the following formula that will look up the Department Name by calling the getDepartment() function:
Next we will create a view for the EmployeesView XPage. Here's the layout:
Create a new XPage named EmployeesView and drag a View Control to the page.
- Make the first column a link.
- Select the View and on the Data tab, select Employee for the default XPage to open.
- Select All Properties and expand data. Enter "detailrow" as the var. This will allow us to reference the column values so we can perform the lookup..
Next:
- append a new column in the view by right clicking on the Depart No. column and select Append Column.
- click on the title for the new column and change the label to Department
- click on the column itself so we can enter a formula
Click on the All Properties tab for the new column.
- expand data
- click on the value property
- click on the diamond and select Computed Value
- enter the following JavaScript formula
Last Steps:
- Add the JS library to the XPages. On each XPage, select the Page properties, select the Resources tab and click Add Script Library. Select the SQLFunctions library
- Drag a new Button Control to the page that will create new Employees
- Circle back to the Employee form and create the Save and Cancel buttons that will navigate back to the EmployeeView page.
That's it, your done! Test it out.
Technorati: Domino 8.5 XPages (0)
XPages Example: Calling Java Methods directly from server side JavaScript - performing an SQL query Part 107/07/2008 Domino 8.5 - XPages
I have been reading many of the discussions this week concerning JavaScript as the language for XPages. I can understand many of the concerns and questions as to why JavaScript? So, I decided to demonstrate one of the advantages of using JavaScript as the server side language for XPages. In the below example I demonstrate how easy it is, and how powerful it is, to call Java methods directly from JavaScript. You can download the example Domino database by clicking here: XPagesJSToJava.nsf
JavaScript can call Java methods directly and pass variables to and from those methods. There are some "type" differences between Java and JavaScript that you need to be aware of. I believe the charts on this site outline the variable conversions well:
Javascript to Java Conversions. In the below example I pass string values directly from JavaScript to and from Java, which is straightforward and without any conversion issues.
Since Domino 8.5 is Eclipse based, you can switch to the Java perspective and add Java code to your project. The nsf database is the project. (If you haven't done this yet, it is interesting to switch perspectives and look around at the structure and the code.) By switching to the Java perspective, you open up your code to the importing or referencing of Java libraries, or the creation of your own classes.
What makes calling Java methods directly so powerful in XPages is that nearly every property in an XPage can be computed, and wherever these's a computation you can use JavaScript for the value or formula. For example, this will allow you to call Java for: populating data sources, labels, view column labels, view data columns, computed text, visibility formulas, any place you see a diamond (which is everywhere)...as well as all server side events. So, wherever you can call JavaScript you can also basically call Java methods!
Example Overview
In this example I am calling a Java method that will return data from a DB2 database. I pass in the query and I receive a concatenated string as the result. If you want to follow the example, and you do not have DB2, you can download DB2 9C for free from here: DB2 Expess-c 9.5. After the install you will see an option to create the Sample database, select this and you will have pre-populated tables to work with.
**Just a note that this is just for demo purposes and performance was not a key consideration. Hopefully access to relational databases will be native in a future release.
This is a screen shot of the Employee XPage. The Combo Box Control's values are populated with a JavaScript call to "getDeptNumbers()". This function calls the Java method "javaMethods.SQLQuery.executeQuery()" which is the Packagename.Class.Method()
of the method we want to call. The last line of the JavaScript function I use an "@Explode" in order to convert the string to an Array for the Combo Box values. There's very minimum amount of code in this example so it will be easy to implement.
** update 7/9/08 - I changed the location of the SQLQuery.java file in Part 2 of this article and it is different than in the Eclipse screen shot
Here is a screen shot of the Employee View where I perform a dynamic SQL lookup on the last column. Nathan Freeman posted an example of performing a @DbLookup on a view column. It's a great example, and a great change in how we as Domino developers can utilize views in XPages. View columns can contain any JavaScript formula in XPages, so they can be dynamic.
As the view is rendering it is dynamically performing SQL lookups for the Department Name by passing the ColumnValue "Depart. No." to the JavaScript function "getDepartment(deptNo)". This function makes a call to executeQuery passing in the "where" clause so we only retrieve the single Department Name for each Dept. No. in a row. Now....I Know what you are thinking....performance obviously would be a consideration in really using this technique....but it's really cool example nonetheless!
How to build it
Click here to continue with Part 2:
How to build it.....
Technorati: Domino 8.5 XPages (6) Sunday 29th, June 2008
XPages Example: Building a Custom Control - How to build a Custom Multi-FileUpload Control that you can drop on any XPage, and it stores all attachments in an external repository db.06/29/2008 05:17 AM Domino 8.5 - XPages
Below is an example of how to build a multi file upload Custom Control that uses a central attachment repository db for all the attachments. The application has very little code and utilizes the Repeat control and Panel control to generate an unlimited amount of uploads per XPage. (Actually, I am sure there is a limit but I have not hit it yet.) The step by step instructions are below. You can also download the example database(s) by clicking here: XPageCustomControl.zip
Overview:
XPage Custom Controls allow you to build your own controls for reuse. They show up in the control palette and can be dragged and dropped on any page just like any of the other controls. The concept is similar to a subform in Domino.
I was demoing XPages to a group of colleagues the other day. I was showing an example of the repeat control, which allows you to repeat a group of other controls based on a formula or value. The neat thing about the Repeat control is it can dynamically creates controls at runtime. One of the the guys asked about using the control to generate unlimited file upload controls for storage in central repository database. I thought... wow, that is a great use case for an example (thanks Mark). It was very quick to build too.
Let's start out with a look at the completed custom control:
After dropping it on an XPage, here is a screen shot of the control in use. You can see that 3 files have been uploaded already and the user clicked the "Add New Upload" button a 4th time and is presented with a blank upload control plus a description field:
The "Add New Upload" button creates a new document in the FileUploadDB database with a reference to the current document's doc ID. The page is then reloaded and the repeat control creates a new blank upload control bound to the new document. A simple JavaScript save() uploads the attachment.
You can drop this control anywhere on your page(s). The only additional item that is needed on your page is a field calculating the document's docUNID (2nd to last screen shot below).
Here are the steps to build the control:
In your database, under Database Navigator select "Custom Controls" then click "New Custom Control". Provide a name for the control.
Drag and drop a Button control on the page and for the label enter "Add New Upload". Select the Event tab and provide the following formula for the onclick() event. This will create a new attachment document in the repository and reload the page:
*note: for this example the attachment repository is named "FileUploadDB.nsf" in the Domino root directory
Next drag a Repeat control to the canvas. Select JavaScript for the Iteration and enter the following for the formula. This formula looks up the associated attachments and generates an array of docIDs based on the document collection:
Enter the following for the Repeat control options. The "docid" variable will contain the docUNID for each document:
*note: I do limit the amount of uploads to 30. I think that's enough...
Next drop a Panel control into the Repeat control. Set the Datasource as follows:
Set the Document Id to be computed. The Document ID will be the "docid" variable from the Repeat control:
There is one property we do have to set that is not obvious if you are starting out with XPages and Panels. That is to: "ignoreRequestParams". The reason for this is we are setting the Document ID and default Action for the Panel ourselves and do not want the Panel to pick up these parameters from the request. Here is a screen shot of how to set that parameter:
Drop a "Table control" to the Panel, 1 row by 2 columns. Now we need to add our controls to the Table. Add an "Edit Box", "File Upload" and "File Download". Here's a screen shot of the bindings for the controls:
Description Edit Box Control:
FileUpload Control and FileDownload Control both have the same bindings:
For the FileUpload control we want to hide the control after one file has been uploaded. We only want one attachment per repository document. Here is a screen shot of the Visibility formula and where you would set that:
Set the opposite logic for the FileDownload control, that is return false if there are 0 attachments.
Place a Button control in the second column labeled "Upload". Set the same visibilty formula as the FileUpload control. It has a very simple formula which saves all data sources. You could substitute a simple action here if you prefer:
On the form where you will use the new MultiUpload control, I use the universalID for the link between the document and the attachment. I placed a hidden Edit Box control on the page with the following Default value formula:
Lastly, here is the Attachment form in the FileuploadDB.nsf repository. I have a view sorted by ParentUNID named "vAttachmentLkUp":
That's it! It really is very minimal the amount of work involved. It took less than an hour for me to do. I am sure the interface could use a little bit more work, but I think it makes for a great example.
The one issue I had seemed to be a bug where I could not place the FileUpload control as the first control in the Panel. I ended up putting the Description first and that seemed to fix the issue...very odd issue. I posted the problem to the 8.5 Beta forum.
Download it and try it out. Hopefully seeing these techniques solving a real business need will start you thinking about other uses for this technology. Let me know if you have any questions....
John
Technorati: Domino 8.5 XPages (4) Sunday 22nd, June 2008
Monday 16th, June 2008
XPages Example part 2: Generating dynamic editable fields for a document collection06/16/2008 03:50 AM Domino 8.5 - XPages
I built on the previous XPage example, the Customer Orders application, to demonstrate how to generate dynamic fields from a document collection. There is a sample database for you to download as well as a powerpoint presentation that explains the steps involved.
Over the years I have developed different techniques to handle dynamic fields in Domino applications. I'm sure many of you have also tackled this issue in various ways. In XPages there are built in controls that you can utilize to address this issue.
In the previous example I demonstrated how XPages allows you to bind your controls on the page to multiple datasources. In this example I take it up a notch by showing how you can repeat a panel control that is bound to a document multiple times, thereby binding edit controls to a collection of Notes documents. XPages maintains the binding so that all the documents are updated from a single button with a single simple action "Save Datasources".
Here's the use case: When a Customer Order is created there could be multiple items the customer is ordering. We should give the user the ability to add new items dynamically by clicking a button. The button will create a new response document and then reload the page. The repeat control will generate the following editable fields for all the response documents: Item Number, Price, and Quantity.
Here is a screen shot:
Here are the downloads:
- CustomerOrders.nsf
- step-by-step powerpoint
Download the application and try it out. Contact me if you have any questions via posting or email. My contact information is in the powerpoint.
John Technorati: Domino 8.5 XPages (2) Monday 9th, June 2008
XPages Example: updating data from multiple forms from a single xPage06/09/2008 Domino 8.5 - XPages
I put together an example of using XPages to update data from multiple forms on a single XPage. There is a sample database for you to download as well as a presentation that walks you through the steps involved.
XPages separates the presentation layer from the data layer. This is obviously very different to the one to one relationship in Domino. XPages also allows the developer to bind to multiple datasources from a single page. This feature can really provide a different approach to developing Domino applications.
I came up with the following simple use case: We have an application where the user takes product orders that are shipped to customers. Sometimes the user needs to update the customer information while taking the order. We can provide the Customer fields right on the Order form and bind them via a Panel control to the Customer form. A single button with a simple action saves all the bound datasources for the page and therefore updates both forms for us. Here is a screen shot:
Here are the downloads:
- CustomerOrders.nsf
- step-by-step powerpoint
Download the application and try it out. Contact me if you have any questions via posting or email. My email address is in the powerpoint (created with Symphony however!).
John Technorati: Domino 8.5 XPages (5) Sunday 1st, June 2008