Posts Tagged ‘sql’

18 Sep 2012

TUTORIAL: Integrating XPages with DB2 – Part 2

Hi everyone. So, just 2 things firstly:

1. This is an extended Post to David Leedy’s NotesIn9 Posting of “Intro to XPages and Relational Data – Part 2“.

2. I do apologize that this took a while to publish. This is one of the more challenging years to find some free time to contribute to the Domino and XPages community. Having said that, I have quite a few Video Tutorials planned before this year ends, so stay tuned :)


Now that that’s out the way…..this post is Part 2 to the “Integrating XPages with DB2 – Part 1“. In this Video I show you how to Create/Read/Update/Delete DB2 Data using XPages. More importantly, I show you how to empower the User to perform all these functions via a User Interface. It’s usually easy to write this logic in the back-end, but a little more involved when handing that power over to the User.


The good news is that it’s fairly straightforward. I show you some quick functions that will get you up and running in no time. As I explained in the Video though, there are much more advanced ways of integrating to DB2. This will involve some knowledge of DB2 itself. The keyword is…..”Stored Procedures“. This is where the power lies. A Stored Procedure is very much like a Sub Routine in LotusScript or a Function in Java or JavaScript, but you code in SQL Language. You can pass the Stored Procedure parameters, have all your logic neatly set out in a central place in DB2 and return values from the Stored Procedure which will allow you do things like create Child Documents, etc.

Later on in the near future, I will think up a straightforward Tutorial which will show you how this all works.

I hope you enjoy this Video. I really enjoyed making it. It was one of my smoothest recordings where everything went right the first time.

I want to thank David Leedy as always for adding this to his NotesIn9 Series. You da man :)




IMPORTANT NOTE: If you are not able to view this Video in HD, please Click Here to open this video up in a different Player.

29 Feb 2012

TUTORIAL: Integrating XPages with DB2 – Part 1

Hi everyone. I’m happy to announce a Video Tutorial where I explain how to integrate one’s XPages Application with DB2 directly, and not through back-end Forms and Views.

The trick here is to have a bit of JDBC understanding, as well as a basic understanding of Relational Databases and SQL Query.

To work with this Video Tutorial, you’ll need to have the following in place:

1. An installed version of DB2. This can be DB2 Express as well, which is free – Click here to download.

2. An installed version of Domino Designer 8.5.3. I tested everything on my Domino Server, but there’s no reason why this shouldn’t work using your local HTTP session for Domino Designer – Click here to download.

3. An UpdateSite to install OSGI Plugins on Domino Server. NOTE: The UpdateSite database gets used for Domino Servers, and will not work for your local environment. To test everything using your Local HTTP, you’ll need to install the OSGI Plugins manually. You do this by extracting the contents of the Plugins Zip File to the Notes Data Directory, under “domino/workspace/applications/eclipse” folder.


Click here to view the Tutorial on how to setup the UpdateSite database and deploy OSGI Plugins on your Domino Server.


4. With reference to point number 3, Install the XPages Extension Library and JDBC Driver Plugins.

Download XPages Extension Library

Download JDBC Driver


5. Once you’ve deployed your plugins to your Domino Server, you can type in the following Commands to check if the Plugins were installed properly:

XPages Extension Libary:

tell http osgi ss

JDBC DB2 Plugin:

tell http osgi ss extlib.driver.db2

The Domino Server should return information relating to the plugins you installed.


6. Here’s a copy of the Code that needs to be added to the db2.jdbc file that I speak about in the Video



7. Here’s a copy of the Code that I use in the Video to add/remove/update and delete data in DB2

var mydata = {
LAST_NAME: “Jardin”

@JdbcInsert(“db2″,  “JJARDIN.USERPROFILES”, mydata)
var mydata = {
LAST_NAME: “Smith”

@JdbcUpdate(“db2″, “JJARDIN.USERPROFILES”, mydata, “ID=?”,1)


I trust you will enjoy this Video. A Big thanks to David Leedy for making the video part of his NotesIn9 Series, and congrats once again on his recent milestone of 100 000 Views.

Also, another Big Thanks to the Domino and XPagees community, for always been available to assist wherever help is needed.