Thursday, July 31, 2008

Using JDBC from PeopleCode

PSST0101 wrote a very good example of using JDBC from PeopleCode in a post titled Writing to Access Databases. Even though this example is Microsoft Access specific, you will notice it uses standard JDBC code, and, therefore, could be used to connect to any database that has a JDBC driver. Just make sure you put your target database JDBC driver in your classpath. On a standard app server, that is either $PS_HOME/class or %PS_HOME%\class.

7 comments:

newbie said...

Jim,
Thanks for your post. I am trying to connect to Oracle data base using JDBC from peoplecode and I am getting the following error message.

No suitable driver found.

I downloaded the jdbc class12.zip and made a .jar and placed it in $PS_HOME/CLASS

But when I wrote the same logic in a java program it is working as expected. Can you let me know as how to check if the driver is registering in peoplecode or not.

Thanks in advance.

Jim Marion said...

If Class.forName throws a ClassNotFoundException, then you have a class path problem.

Is log4j*.jar in your $PS_HOME/class directory or $PS_HOME/appserv/classes directory? Your implementation might be using $PS_HOME/appserv/classes.

Did you restart your app server after placing the jar file in the class dir? The startup process adds jars to the classpath at startup.

newbie said...

Jim,
Thanks for your immediate response. I am not getting Class.Forname error. I am getting the following error

Java Exception: java.sql.SQLException: No suitable driver: during call of java.sql.DriverManager.getConnection.

Yes log4j*.jar is in my $PS_HOME/class directory. We did bounce the process scheduler server as we will he running the app engine through process scheduler.

Thanks.

Jim Marion said...

@newbie, can you post your class.forName call and your JDBC url? Please don't post your server name, database name, password, etc. Replace these with generic text.

class.forName is supposed to register the driver and return a reference to the Class object. You can call the getName() method on the returned object and print that to your AE log file just to make sure class.forName is finding the class.

You say it was working as a Java program, but not from PeopleCode, so I assume your code is OK. Another consideration is your JVM version. Does your Java program use the same JVM as your process scheduler server? If not, you may want to recompile your Java app and run it using the same JVM as your process scheduler.

What version of PeopleTools are you using? Different PeopleTools versions use different JVM versions. If your version of PeopleTools uses Java 1.4 or higher, you might consider ojdbc14.jar.

I'm going to write up a quick test using ojdbc14.jar and see what happens. I'll post the results.

newbie said...

I wrote the following for Class.forName
&class = GetJavaClass("java.lang.Class");
&class.forName("oracle.jdbc.driver.OracleDriver");

The above code did not work so I did some research and used the following

Local JavaObject &jCls = GetJavaClass("java.lang.Class");
Local JavaObject &jClsLoadr = GetJavaClass("java.lang.ClassLoader");
Local JavaObject &jDocClass = &jClsLoadr.getSystemClassLoader().loadClass("oracle.jdbc.driver.OracleDriver");

Local JavaObject &jDocClass = &jCls.forName("oracle.jdbc.driver.OracleDriver", False, &jClsLoadr.getSystemClassLoader());

&driverManager = GetJavaClass("java.sql.DriverManager");

&connection = &driverManager.getConnection("jdbc:oracle:thin:@HRMS:1521:sid", "scott", "tiger");

We are on JVM 1.4 and on 8.45 Peopletools.

I will check and confirm if we have the same Java version on my process scheduler as that of my Java program.

Thanks.

Jim Marion said...

@newbie, I experienced the same class loader problem with class.forName. I don't know why. I've used class.forName before. I was also able to replicate your "No suitable driver" error. Using the driver directly, however, I was able to get this working. You can find the details in my latest post Using Oracle JDBC from PeopleCode. I hope this satisfies your requirements. Please let me know.

newbie said...

Jim,
Thanks for your solution. I can connect using JDBC to remote database. Using driver directly solved my problem.

Once again thanks a lot.