charles a Ross commented on Feb 15, 2013

Re: Using XPages to Integrate Between Notes Applications with Relational Databases

The standard developer's package MySQL install does include a database named "world" that has a table named "cities" , which could go together with this example but this article completely skips the most critical step, which is the install of the the jdbc driver. Interesting but frustrating.

You could combine this article with the "SQL Databasource" article / project posted on OpenNTF by Ulrich Krause. and get something useful. His contributation has more concrete artifacts but is also sketchy on instructions, but put the two together, and you could catch a clue.

The UK blogicle is here, and it links to the open NTF project, which has a helpful PDF included

http://www.eknori.de/2011-07-03/using-mysql-data-in-xpages/

Robert F Harwood commented on Jan 13, 2012

Re: Using XPages to Integrate Between Notes Applications with Relational Databases

checked

Carl Roman Hultay commented on Jan 21, 2011

Re: Using XPages to Integrate Between Notes Applications with Relational Databases

I would also appreciate a sample project, or an explanation of how to add the JAR file to the project, or to the server dependencies.

Pascal Vachon commented on Jul 13, 2009

Can we categorize data inside a Repeat control?

One more...

Would it be possible to categorize data in your Repeat control much like a regular categorized view?

For example, displaying every cities from the US inside the US category, every cities from Canada inside the Canada category and so on.

I looked at the Section control, which is very close to what I want, but the category is always duplicated (one category for each city instead of re-grouping them together)

I can understand why... but I just want to know if there's a way to achieve this?

Thanks again!

Jo Grant commented on Jun 18, 2009

@Pascal, leverage Notes as much as possible

Yes, the "security" in this example is completely hard coded!

This is server code running on the server. So it doesn't have to be secure in the same was as client code. For example, it would be fine to leave in the hardcoded access point, if you use the Database ACL to ensure that only valid people had access to the database at all.

For a smaller granularity of access, I would suggest you create and maintain a role in your database ACL that governs access, and the getStatement() function (or equivalent) consults the runtime to see if the current user has that role or not, and proceeds or errors accordingly.

Jo Grant commented on Jun 18, 2009

@Andrew, loading the jar file

Did you see the section starting: "the jar file containing our driver must be part of this application."

There was a security problem in the current version of Notes when I wrote that (since fixed) and you couldn't bundle the JAR file with the app like a normal JAR file you wanted to access from Javascript. You had to put it in the system directory. Try that and see if that works.

I considered posting the database, but it's complicated. You don't need just the database, but also the drivers, the setup, and the schema and data for the MySQL partition. We have to do a lot of process to post 3rd party things here and the only use in the database with out that other stuff is the Javascript code. So I tried to put as complete Javascript code in the article as possible.

Pascal Vachon commented on Jun 4, 2009

What about security?

I successfully developed a Domino application (for test purpose) that displays data from a DB2 database using XPages and JDBC based on your article and John Mackey's blog.

I was previously using NSFDB2 to achieve something similar... but as we all know, this setup is now discontinued.

However, what I liked about NSFDB2 was that the security was based on the Domino ACL even though the data was actually stored inside DB2.

In the above article, it looks like Domino is using the same username/password for all users to select/update data from the MySQL database. Would it be possible to respect the Domino ACL? I presume this would require designing your own security system to "map" the Domino users to pre-defined user in the MySQL database... while NSFDB2 did it all automatically for us.

I was just wondering is somebody achieved something similar?

But thanks for the article... it's very interesting.

Andrew Tjecklowsky commented on May 11, 2009

Which JDBC driver version did you use?

I've tried to create an XPage that uses a custom Java class and also uses either the "org.gjt.mm.mysql.Driver" or the "com.mysql.jdbc.Driver" drivers (Connector/J version 5.1). However my XPage silently 'crashes' (it doesn't respond, neither catches any exceptions) when trying to call DriverManager.getConnection(...). I've tried to use another JDBC driver for another type of RDBMS and it successfully returns data. I know I have access from the XPage server to MySQL because I can create a normalt Java agent that retrieves data.

Is it possible for you to post the sample database for this article? Thanks!