Tuesday, March 17, 2009

Using Oracle JDBC from PeopleCode

PSST0101's post Writing to Access Databases demonstrates how to call standard JDBC boiler plate code from PeopleCode. I tried it with the Oracle JDBC driver, but couldn't get the driver to load. First, class.forName kept throwing java.lang.ClassNotFoundException: oracle/jdbc/OracleDriver. Since the intent of class.forName is to register the driver with the JDBC DriverManager, I thought I would see if I could register it manually:

Local JavaObject &driverManager = GetJavaClass("java.sql.DriverManager");
&driverManager.registerDriver(CreateJavaObject("oracle.jdbc.OracleDriver"));

This time the JVM found the class, but, when I executed DriverManager.getConnection, the JVM threw java.sql.SQLException: No suitable driver.

The point of boiler plate JDBC code is to abstract the persistence layer from the code tier, allowing for configurable data repositories. Since PeopleCode requires Java class names as strings, PeopleCode affords us this configurable feature without the JDBC abstraction. Therefore, we can skip the JDBC niceties in favor of direct driver usage:

Local JavaObject &driver = CreateJavaObject("oracle.jdbc.OracleDriver");
Local JavaObject &info = CreateJavaObject("java.util.Properties");

&info.put("user", "john_doe");
&info.put("password", "secret");

Local JavaObject &conn = &driver.connect("jdbc:oracle:thin:@servername:1521:SID", &info);
Local JavaObject &stmt = &conn.createStatement();
Local JavaObject &rs = &stmt.executeQuery("SELECT ROLENAME FROM PSROLEDEFN WHERE ROWNUM < 11");
Local number &rowIdx;

While &rs.next()
&rowIdx = &rowIdx + 1;
MessageBox(0, "", 0, 0, "Row " | &rowIdx | " column 1: " | &rs.getObject(1).toString());
End-While;

REM ** Close JDBC resources per Cheryl's comment below;
&rs.close();
&stmt.close();
&conn.close();

Even though I find this example thought provoking, I have not used it in production. When integrating with other applications, I first consider Integration Broker's asynchronous services. Integration Broker provides services such as queuing that aren't available with direct access. If I can't find a delivered target connector and can't create a custom target connector to suit my needs, then my next consideration is Oracle database links.

PeopleSoft's flexible architecture provides developers with a vast array of options. It is important for us as developers to know how and when to use these options.

Update (March 24, 2009)

Nicolas pointed out that users should not hard code user names and passwords. I absolutely agree. The code above is provided as an example. If you implement a solution like this, I strongly encourage you to Encrypt the password and store it somewhere else. PeopleCode has Encrypt and Decrypt functions for this purpose. You can either store the user name and password encrypted in the database or store it in a protected file off line. Furthermore, don't use a privileged user. Use a database user that only has the database access required to accomplish the coded task.

Besides a hard coded password, the code above contains a hard coded SQL string literal. I strongly discourage this practice as well. Use App Designer SQL definitions to store SQL statements. Unlike string literals, PeopleSoft change management tools can search, compare, and maintain managed objects referenced in SQL definitions.

27 comments:

Nicolas Gasparotto said...

Jim, hardcoded the password is not very... hmmm, political correct, is it ? Security hole here. Very big issue, especially when customer wants tyo cut down the access to the admin/dev by implementing DB Vault.

Jim Marion said...

I totally agree. Thanks for pointing this out. This code example is a teaching tool, a proof of concept. I wouldn't hard code SQL in PeopleCode either. Likewise, the only time I would connect back to the PeopleSoft Oracle database using something other than the PeopleSoft SQL Access Manager is to execute a stored procedure that has output parameters. With autonomous transactions, I can't see any other reason to make a second connection.

I've been asked the question of how to do this, so I thought I would try to figure it out. The only reason I can think of to try to connect to an Oracle database from PeopleSoft is if you are not using Oracle as your PeopleSoft DB and/or your Database Administrator won't create database links to an external database.

If you are a reader and want to implement a solution like this, please don't hard code the password. Encrypt it and store it somewhere else. PeopleCode has Encrypt and Decrypt functions. Furthermore, don't use a privileged user. Use a database user that only has the database access required to accomplish the coded task.

I should have mentioned these items as caveats to this post. Since not everyone reads post comments, I will update the post accordingly.

Unknown said...

Hey Jim!

One way we've been able to get around the security for using the jdbc.OracleDriver was to simply configure a Remote Database Access for our external database. Then just use PSREMOTEDBDEFN to get your username and password (already encrypted) from there, you can even string together your server name, port, SID, etc., from the same information.

This way the password is already encrypted, and the config (for the Remote Database) is contained within the PeopleSoft environment.

Cheers! And please keep posting new and exciting things!

Jim Marion said...

@k_malmos, Thanks for the great idea!

Just Cheryl said...

I found that I had to close the connection, statement and the query within the PeopleCode program or threads were left on the database.

Found a good link with examples:
http://blog.shinetech.com/?p=66

Jim Marion said...

@Cheryl, you are SO right! I can't believe I forgot the close() statements. I added them and credited you. Thank you!

Asgar said...

Hello.,

I am trying to establish remote database connection through peopletools 8.49 to SQL Server 2005.

But poeplebooks says that to setup JDBC drivers of SQL Server 2000 on the path to establish the connection.

It makes confusion,
See.. we are using poepletools 8.49that supports SQL Server 2005.

If we do with the drivers of SQL Server 2000, it is connecting. But it gives error in data sql select statements.

Jim Marion said...

@Mohamed, this blog post discusses how to connect to databases from PeopleCode without using PeopleTools delivered connection mechanisms. If you are having trouble setting up your PeopleSoft system, then I suggest you open a support case with support.oracle.com.

PeopleSoft itself uses native database libraries to connect. It does not use JDBC. If you are using JDBC, however, then, yes, you need to have your JDBC jar file in your class path. You can read more about setting up your app server's Java class path in PeopleBooks here.

Just Cheryl said...

Here are the 2 steps necessary (found this by search JDBC in PeopleBooks) and our Infrastructure person figured out the files required:

Application Server Setup (and maybe your Batch servers if running code in AE):
Summary: Copy both jar files to the classes directory. Update the CLASSPATH line in psappsrv.cfg

1) Copy Oracle 10g Files (JDK 1.4) to Folder /appserv/classes

o ojdbc14.jar - JDBC driver classes
o orai18n.jar - contains many important character set and globalization support files

2) Update CLASSPATH on update file in "psappsrv.cfg" file for the Instance


[PSTOOLS]
;=========================================================================
; General settings for PSTOOLS
;=========================================================================
; RDBA Oracle JDBC driver
;Add to CLASSPATH=%ORACLE_HOME%\jdbc\lib\ojdbc14.jar
Add to CLASSPATH=%PS_HOME%/appserv/classes/ojdbc14.jar
Add to CLASSPATH=%PS_HOME%/appserv/classes/orai18n.jar


This was for PeopleTools 8.49.15

Anonymous said...

wow! so far this has all been super helpful. I am wondering if it is possible to called stored procedures from the javaobject within peoplecode? if it is possible, can you point me to any examples?

Jim Marion said...

@magster, I don't have a sample worked up, but instead of &conn.createStatement(), you would use &conn.prepareCall("CALL MY.STORED_PROC(?, ?, ?)"). This will return a CallableStatement For each parameter, call setString. For each output parameter, call registerOutParameter. I'm not sure but registerOutParameter might give you the "more than one overload matches" error. If it does, then you may have to resort to reflection.

If the stored proc is really a function or can be wrapped in a function, and if the function/proc doesn't modify data, then you can use it in a select list or where clause of an SQL statement (SELECT MY_FUNC() FROM PS_INSTALLATION).

Unknown said...

Jim,

Once again thanks for keeping this blog going. I refer to it now more than PeopleBooks...

I too am trying to use JDBC via peoplecode. I am able to connect, get a resultset via getobject(), etc.
My issue is that randomly the java references will justy hang indefintely and eventually timeout.

Specifically it hangs on the second line below:

Local JavaObject &driver = CreateJavaObject("com.microsoft.sqlserver.jdbc.SQLServerDriver");
Local JavaObject &info = CreateJavaObject("java.util.Properties");

A refresh and cache clear of the DEV app server cures this issue. I am just concerned about migrating this up to environments in our prod path. What happens if we have load balancing, for example via two app servers?

I am connecting to a SQL server database and using the properties to set the url and password.

&info.put("user", &userid);
&info.put("password", &password);

Local JavaObject &conn = &driver.connect("jdbc:sqlserver:" | &url, &info);

Local JavaObject &stmt = &conn.createStatement();




Thanks for any additional information.


BJ




Jim Marion said...

@Brian, that is interesting. I haven't run into that before. Creating an instance of java.util.Properties should not cause your app server to hang. Are you sure that it isn't related to the JDBC connection? I can see network issues, etc, causing a hang.

In regards to load balancing, that won't be an issue. PeopleCode runs start to finish on the same app server. The app server is stateless. PeopleSoft does not serialize JavaObject variables. This means that once you create a Java variable, it only lasts as long as the currently executing PeopleCode. Once the event ends, the object is destroyed. This is true even if you use a function like a modal function that pauses PeopleCode execution. Java variables are not restored across requests, so load balancing will have no impact.

Now, here is something interesting... The JVM is loaded into memory when the app server first loads the JVM. It doesn't get destroyed until you restart the app server. What this means is that you can use singletons, statics, etc, to maintain Java state across session requests. Of course, this is only successful if you have a single app server. Once you throw a load balancer in there, you have to work stateless because you can't ensure a request will return to the same app server. Nevertheless, it can be useful for caching, etc.

Shail P said...

@Brian Heinbaugh

Hello Brian Thanks for posting your experience with SQLServer Remote DB connection.

I just wanted to point out one issue in your code, you statement Local JavaObject &driver = CreateJavaObject("com.microsoft.sqlserver.jdbc.SQLServerDriver");
has everything in place except for the driver class path, which should be
"com.microsoft.jdbc.sqlserver.SQLServerDriver".

Hope this helps.

Thanks,
Shail P

James Sherman said...

I came across your article because I wish to develop a bolt-on to psoft, but don't want to write it in pcode and give away my source code... therefore, I am considering writing it in java.

What are your thoughts?

Jim Marion said...

@Tricia, you can write back-end logic in Java and deploy it to an app server. You can then invoke the Java methods using PeoplewCode's GetJavaClass and CreateJavaObject. If the "bolt on" will be displayed through the PeopleSoft UI, then you will still need to create pages and components. Of course, you could create a separate Java web app and relate it to PeopleSoft through a Portal Registry structure. It all depends on how tightly you want to integrate.

If you plan to deploy this solution to other organizations, keep in mind that Java is easily decompiled into source code. I use Java with PeopleCode quite a bit, not for code obfuscation reasons. Acquiring Java source code is harder than PeopleCode, but certainly possible.

Unknown said...

Hi Jim,
I have one issue with calling java class from peoplecode. I read about it and placed the file at the location PS_HOME\Appserve\Classes\ and still not able to call it.

We have bounced the server cleared the cache but still it is not working.

I am able to execute the file from command prompt but not from peoplecode so suggest me what to do on this

Jim Marion said...

@Pranay, I believe it depends on the operating system whether to look in class or classes folder. You can tell by looking at how many jar files are in the folder. Otherwise, yes, that is the right place. When adding or updating jar files, you don't need to clear cache, just restart the app server. The reason for this is because the Java classpath is set at app server startup, and then jar files are loaded into memory as needed. Likewise, once a library is loaded into memory, if you replace the jar file, Java won't re-read the file. That is why you have to restart the app server.

Unknown said...

Hi Jim,

I have one Problem going on I am now able to call my java class file and the problem is my java file uses POI-3.15.jar file but our people tools is having POI-3.10.jar so my java code is giving exception
Java Exception: java.lang.NoSuchMethodError: org.apache.poi.ss.usermodel.Workbook.iterator()Ljava/ut il/Iterator;:

as the old version is not having this function.

I have tried replacing the Jar with new version but then by N-vision delivered peoplesoft program is going to no success due to that.

So kindly suggest me what can be done in this scenarios.

Thanks,
Pranay

Jim Marion said...

@Pranay, the good news is that PeopleSoft now delivers POI. The bad news is that it is a different version than you are using. My recommendation is to update your custom code to use the later version of POI. I don't think you want two different versions in your classpath.

Unknown said...

Hi Jim,

There are some functionality which is not present in the older version of JAR file which we need to use in our code of Excel merger so that's why we are stuck with this. So is there any see through for this as this is blocking our deliverables on time.

Kindly suggest.

Thanks,
Pranay

Unknown said...

Hi Jim,

Is There any way we can use executable JAR file so that we will not have to replace the later version of JAR files.


Thanks,
Pranay

Jim Marion said...

@Pranay, you could replace the delivered POI, but that is risky. I'm not sure if custom class loaders will let you use a different version of a JAR, but that might be an option as well. This is a Java issue, so I suggest searching for Java solutions that allow you to load multiple versions of a JAR into the same JVM. Let me know what you find.

Unknown said...

Hi Jim,

I was able to call my executable JAR using Shell script file and execute it, now I don't need to replace any file or don't need any specific location thanks for the help.

Thanks,
Pranay

Jim Marion said...

@Pranay, that would work. Clever trick.

Revathy G.Vallaban said...

I'm tyring to connect other DB using JDBC and I would like to know if the session opened is closed properly. How can I check it from backend and make sure its closed. Could you please clarify.

Jim Marion said...

@Revathy, great question, and possibly not answerable. If used from the process scheduler, the JVM goes out of scope at the end of the program. With the app server, each app server process goes back into a pool, so it would be hard to know which process is still running and has an open JDBC connection. But then again, if it would be cleaned up with pure Java, then your PeopleCode/JVM interaction would work exactly the same.