I presented a 3 Day Introduction to XPages Course this week for one of my clients, and they challenged me to have XPages connect to a DB2 environment they had set up. I must be honest I was a touch nervous as this would be my first time attempting this period.
I told the client that we would spend the last day (T0day) focusing on this particular challenge, and then also show them how the Application Layout Custom Control works.
We started around 7:00am this morning, and by 8:15am, we had a View Panel with 1 column displaying data from a DB2 table. Here’s the best part…..we got it right on our FIRST TRY.
Now, I have to make mention and take my hat off to my client for providing me with all the DB2 resources I needed to make the JDBC Data Source work, which included:
1. JDBC Jar Files which were placed in the Domino Directory under the folder “jvm\lib\ext”
2. The URL to the DB2 Database that we planned on using
3. The Class name of the JDBC DB2 Driver
Using this Video from Niklas Heidloff as a reference, as well as perusing the JDBC NSF that ships with the 853 XPages Extension Library, we were able to configure a .jdbc file in our Database’s Package Explorer, create a View Panel in our XPage, map it to a JDBCQuery Data Source, and BOOEM!!!
This was just too easy. I’ll submit a video tutorial on how to achieve this as soon as I’m done with my other tutorials. My backlog is starting to build up. However, for those who are anxious, use the video link I added in this post, and reference the notes below:
NOTES FOR DB2 INTEGRATION:
Using Niklas’s Video as a reference, do the following:
1. in your .jdbc file that you need to create in the Database’s Package Explorer under the “WEB-INF/jdbc” folder, have your XML look something along the lines of:
2. You will need to copy the 2 JDBC Jar Files that come with the DB2 Install into the “jvm\lib\ext” folder in the Domino Program Directory.
3. Restart the HTTP Server (Restart Task HTTP)
You should be able to come right by following the rest of the video.
Enjoy, and please feel free to provide feedback.
John Jardin
No related posts.
Great job, John ! Thanks for sharing.
No No, thanks for your video
Helped a lot.
I wonder what the value of DB2 connection is for most customers? I rather see the current problems with nsf resolved as discussed here: http://www.takingnotespodcast.com/blogs/takingnotes.nsf/dx/TakingNotesEpisode142.htm
I’ve also been pleasantly surprised with how smoothly the JDBC access tools work. I have an XPages app that connects to a Microsoft SQL Server and the data sources and @Jdbc* functions have made working with it so much easier (and likely more efficient) than the previous roll-your-own methods. My personal favorite pet feature so far is how well the data sources work with the Extension Library’s Dynamic View Panel control.
During the course today my client attempted editing DB2 data via a View Panel and the Modal Dialog found in the Extension Library. While there’s some understanding needed even on my side with regards to the Object Data Source, he was still able to implement a working example.
I look forward to working with more scenarios.
Good stuff John. Got this on my radar as well!
What would be really interesting is to work with DB/2′s PureXML. Bind a XPage to the XML from a DB/2 column.
Agreed, and noted.
How hard would it be to do this with SQL?
Not difficult according to Theory. I haven’t done this myself yet, but if you look at my post, you’ll need to store the Jar Drivers for SQL in the Dom Directory, and in the .jdbc file, you’ll need to provide the Class Name and URL path to the database.
Hi,
Good job, John.
I have created a jdbcQuery datasource in the view panel and I got the result too. How can I fill the new xPage with data from jdbcQuery when I click on any column of the view? I tried new jdbcQuery datasource on the new xPage but and put a select statement with SQL Parameter, unfortunately I am not able to map the field on the new xpage, can you look into this.
Regards,
Hi Mohammed. To be honest I am still rather new to the JDBC Classes, but I can tell you that using a JDBCRowSet Data Source instead of a JDBCQuery will get you closer to the result you are looking for.
If you download or have the 8.5.3 Extension Library from OpenNTF.org, you will see the Zip File contains a JDBC .nsf, which contains many examples of using JDBC in XPages. There is very little documentation on this right now, but the good news is I have already created Part 1 of a 2 part video series which will answer your questions. Part 1 will most likely be released next week, and part 2 within the next 2-3 weeks. This Video tutorial will cover everything I know and have done with JDBC and XPages integrating with DB2.
I hope this helps for now.