How do I access SQL from XPages

Question

What is the process to access data from a SQL data source and have it fill in a list box control so that the user may select one of the values?

I have been given the name of the database and server, the login ID and password.

Code samples would really be appreciated as I have never done any SQL coding.

I should have stated that the client is using UP1 and not ExtLib — RoyRumaner, Feb 01 '12 at 19:11

score 8 · Answer 1 · answered Jan 26 '12 at 22:26

The latest Extension Library on OpenNTF ( extlib.openntf.org ) has a whole bunch of Relational Database extensions.

You'll need to get the JDBC drivers for whatever SQL server your going to be accessing and then take a look at the ExtLib demo application on how to create the JDBC connector from your application. Once the connector is in place you can then just the new controls in ExtLib to easily create a view pane etc.

You will also need more then the SQL server, username and password, you'll need to find out the different tables that you'll be accessing so that you can reference them from your Xpages application.

score 3 · Accepted Answer · answered Feb 01 '12 at 18:50

3

I've created a video showing JDBC access from XPages: http://www.youtube.com/watch?v=p6oRCsTsVqc

answered Feb 01 '12 at 18:50

Niklas Heidloff

952
6
13

David Leedy · Answer 3 · 2012-02-01T18:05:14.650

0

Keep in mind that you need the actual ext. library for this. The upgrade pack does not contain the JDBC stuff.

Edit: Keep in mind that if you don't need "LIVE" data access, and the information you want is fairly static you could always just use a lotusscript agent to pull the data down into Notes Documents. Run that once a day or whatever. No fancy XPages stuff needed. That's fairly common coding and practices with examples available.

Then just have the list box pull from the documents you brought down.

edited Feb 01 '12 at 18:05

answered Jan 27 '12 at 01:48

David Leedy

3,583
18
38

Are you saying that if we installed UP1, then we have to install the JDBC stuff from OpenNTF separately? What issues will this cause on future updates like UP2 and beyond? – RoyRumaner Jan 27 '12 at 05:24
No. You install Upgrade Pack if you want IBM support. you installed Extension Library if you want updates faster. Currently Upgrade Pack does NOT contain the JDBC stuff. you don't want to mix and match. it's either or. So if you want JDBC then install ext. library instead of Upgrade Pack – David Leedy Feb 01 '12 at 15:36
I was afraid you were going to say that. I just got the client to agree to upgrade to UP1 on all their servers. Now I have to tell them to go back to the ExtLib? They are not going to go for that. – RoyRumaner Feb 01 '12 at 17:05
There was a big discussion at a BOF about Lotusphere about this. I personally don't get it. If you want support its upgrade pack 1 with fewer features because it's not all fleshed out / test. If you want all the bells and whistles ext. library. It does NOT make sense to mix upgrade pack with ext. lib pieces as now you're running unsupported software. So why mess with UP1 in the first place? – David Leedy Feb 01 '12 at 18:00
At the company I am currently engaged at, the IT Departments will not put unsupported software on their systems - to much of a risk. They have no problem with it if it is supported, but I still needed to jump through all sorts of hoops just to get them to think about adding it. Now, what you are saying is that because they installed UP1 and not ExtLib, we do not have drag and drop capabilities to connect to SQL from an XPage and have to do it the old fashioned way. – RoyRumaner Feb 01 '12 at 19:08
Roy - what I'm really saying is that the JDBC pieces is NOT supported by IBM at this time. That's why it's still in Ext. library. So if you're first statement hold - your company won't install it anyway. – David Leedy Feb 03 '12 at 13:02

score 0 · Answer 4 · answered Jan 27 '12 at 02:12

0

Wait for the book that will e released soon about the extlib. I know Jeremy hodge wrote the chapter so you might be able to get some info from him.

answered Jan 27 '12 at 02:12

Mark hughes

153
8

I would wait for the book, but the client has a different timeline. They want it in 2-3 weeks, not 2-3 months. – RoyRumaner Jan 27 '12 at 05:25

score 0 · Answer 5 · edited May 23 '17 at 12:14

0

From an answer I gave earlier: you might want to check out the blog post announcing the JDBC support . It has an excellent video explanation and a link to a slide deck.

Also, take a look at Xpages101 lesson 61. It's paid-for content, but well worth it if you're serious about Xpages development.

edited May 23 '17 at 12:14

Community

1
1

answered Jan 27 '12 at 07:06

Thimo Jansen

485
2
15

score 0 · Answer 6 · answered Jan 27 '12 at 08:11

If you want to combine Upgrade Pack 1 (UP1) with the Extension Library JDBC parts, then make sure to use the Extension Library that matches exactly the UP1 version. This is version 853-20111215 of the Extension Library. Then you can use the update site method to only deploy the experimental parts of the Extension Library (com.ibm.xsp.extlibx.feature_8.5.3.20111215-0914.jar).

For newer releases of Extension Library things might (will) have changed so that UP1 and Extension Library can not work together.

When UP2 is released, you need to remove the Extension Library package and deploy UP2. At that point in time UP2 might contain the JDBC support.

score 0 · Answer 7 · answered Jan 28 '12 at 15:21

Roy, As the previous posters put the ext library stuff will make it a little more "Drag and Drop", but you can use regular JDBC connection to get the data you want, Its pretty simple, but a lot more code than using Domino as a backend. You might want to look at this John Mackey blog post about doing a very similar thing...http://www.jmackey.net/groupwareinc/johnblog/johnblog.nsf/d6plinks/GROC-7G9GT4

How do I access SQL from XPages

7 Answers7

Linked