tag:blogger.com,1999:blog-34020393.post5157280998139562451..comments2024-03-27T10:59:38.307-07:00Comments on Jim's PeopleSoft Journal: Using Oracle JDBC from PeopleCodeJim Marionhttp://www.blogger.com/profile/12995110203807924786noreply@blogger.comBlogger27125tag:blogger.com,1999:blog-34020393.post-6648762263121933772020-03-10T14:17:11.927-07:002020-03-10T14:17:11.927-07:00@Revathy, great question, and possibly not answera...@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.Jim Marionhttps://www.blogger.com/profile/12995110203807924786noreply@blogger.comtag:blogger.com,1999:blog-34020393.post-21843370015044564932020-02-18T06:19:08.402-08:002020-02-18T06:19:08.402-08:00I'm tyring to connect other DB using JDBC and ...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.<br />Revathy G.Vallabanhttps://www.blogger.com/profile/06639978091715014233noreply@blogger.comtag:blogger.com,1999:blog-34020393.post-86528200406494002282016-10-07T06:50:49.382-07:002016-10-07T06:50:49.382-07:00@Pranay, that would work. Clever trick.@Pranay, that would work. Clever trick.Jim Marionhttps://www.blogger.com/profile/12995110203807924786noreply@blogger.comtag:blogger.com,1999:blog-34020393.post-76403308648352433792016-10-07T05:06:41.573-07:002016-10-07T05:06:41.573-07:00Hi Jim,
I was able to call my executable JAR usin...Hi Jim,<br /><br />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.<br /><br />Thanks,<br />PranayAnonymoushttps://www.blogger.com/profile/18053301167520441048noreply@blogger.comtag:blogger.com,1999:blog-34020393.post-86630869702478243342016-10-05T06:55:17.581-07:002016-10-05T06:55:17.581-07:00@Pranay, you could replace the delivered POI, but ...@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.Jim Marionhttps://www.blogger.com/profile/12995110203807924786noreply@blogger.comtag:blogger.com,1999:blog-34020393.post-57524299220864677502016-10-04T22:09:22.166-07:002016-10-04T22:09:22.166-07:00Hi Jim,
Is There any way we can use executable JA...Hi Jim,<br /><br />Is There any way we can use executable JAR file so that we will not have to replace the later version of JAR files.<br /><br /><br />Thanks,<br />PranayAnonymoushttps://www.blogger.com/profile/18053301167520441048noreply@blogger.comtag:blogger.com,1999:blog-34020393.post-89826296917294062262016-10-04T21:51:39.510-07:002016-10-04T21:51:39.510-07:00Hi Jim,
There are some functionality which is not...Hi Jim,<br /><br />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.<br /><br />Kindly suggest.<br /><br />Thanks,<br />PranayAnonymoushttps://www.blogger.com/profile/18053301167520441048noreply@blogger.comtag:blogger.com,1999:blog-34020393.post-15109850462626319702016-10-04T07:05:18.076-07:002016-10-04T07:05:18.076-07:00@Pranay, the good news is that PeopleSoft now deli...@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.Jim Marionhttps://www.blogger.com/profile/12995110203807924786noreply@blogger.comtag:blogger.com,1999:blog-34020393.post-72614828276685150722016-10-04T05:42:31.599-07:002016-10-04T05:42:31.599-07:00Hi Jim,
I have one Problem going on I am now able...Hi Jim,<br /><br />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 <br />Java Exception: java.lang.NoSuchMethodError: org.apache.poi.ss.usermodel.Workbook.iterator()Ljava/ut il/Iterator;:<br /><br />as the old version is not having this function.<br /><br />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.<br /><br />So kindly suggest me what can be done in this scenarios.<br /><br />Thanks,<br />PranayAnonymoushttps://www.blogger.com/profile/18053301167520441048noreply@blogger.comtag:blogger.com,1999:blog-34020393.post-50513416284615981562016-09-27T11:30:56.805-07:002016-09-27T11:30:56.805-07:00@Pranay, I believe it depends on the operating sys...@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.Jim Marionhttps://www.blogger.com/profile/12995110203807924786noreply@blogger.comtag:blogger.com,1999:blog-34020393.post-3664975103285084102016-09-27T05:38:59.578-07:002016-09-27T05:38:59.578-07:00Hi Jim,
I have one issue with calling java class f...Hi Jim,<br />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.<br /><br />We have bounced the server cleared the cache but still it is not working.<br /><br />I am able to execute the file from command prompt but not from peoplecode so suggest me what to do on thisAnonymoushttps://www.blogger.com/profile/18053301167520441048noreply@blogger.comtag:blogger.com,1999:blog-34020393.post-89344827110785846292015-06-22T09:19:01.361-07:002015-06-22T09:19:01.361-07:00@Tricia, you can write back-end logic in Java and ...@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.<br /><br />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.Jim Marionhttps://www.blogger.com/profile/12995110203807924786noreply@blogger.comtag:blogger.com,1999:blog-34020393.post-12424235609378491702015-06-20T11:42:54.016-07:002015-06-20T11:42:54.016-07:00I came across your article because I wish to devel...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.<br /><br />What are your thoughts?James Shermanhttps://www.blogger.com/profile/09315555990708586680noreply@blogger.comtag:blogger.com,1999:blog-34020393.post-60276501404914200962014-02-08T19:41:30.229-08:002014-02-08T19:41:30.229-08:00@Brian Heinbaugh
Hello Brian Thanks for posting ...@Brian Heinbaugh <br /><br />Hello Brian Thanks for posting your experience with SQLServer Remote DB connection.<br /><br />I just wanted to point out one issue in your code, you statement Local JavaObject &driver = CreateJavaObject("com.microsoft.sqlserver.jdbc.SQLServerDriver");<br />has everything in place except for the driver class path, which should be <br />"com.microsoft.jdbc.sqlserver.SQLServerDriver".<br /><br />Hope this helps.<br /><br />Thanks,<br />Shail PShail Phttps://www.blogger.com/profile/03086732890519086649noreply@blogger.comtag:blogger.com,1999:blog-34020393.post-18628849953113732013-05-09T12:19:17.576-07:002013-05-09T12:19:17.576-07:00@Brian, that is interesting. I haven't run int...@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.<br /><br />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.<br /><br />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.Jim Marionhttps://www.blogger.com/profile/12995110203807924786noreply@blogger.comtag:blogger.com,1999:blog-34020393.post-41606213543679179112013-05-09T10:54:17.322-07:002013-05-09T10:54:17.322-07:00Jim,
Once again thanks for keeping this blog goin...Jim,<br /><br />Once again thanks for keeping this blog going. I refer to it now more than PeopleBooks...<br /><br />I too am trying to use JDBC via peoplecode. I am able to connect, get a resultset via getobject(), etc. <br />My issue is that randomly the java references will justy hang indefintely and eventually timeout. <br /><br />Specifically it hangs on the second line below:<br /><br />Local JavaObject &driver = CreateJavaObject("com.microsoft.sqlserver.jdbc.SQLServerDriver");<br />Local JavaObject &info = CreateJavaObject("java.util.Properties");<br /><br />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?<br /><br />I am connecting to a SQL server database and using the properties to set the url and password.<br /><br />&info.put("user", &userid);<br />&info.put("password", &password);<br /><br />Local JavaObject &conn = &driver.connect("jdbc:sqlserver:" | &url, &info);<br /><br />Local JavaObject &stmt = &conn.createStatement();<br /><br /><br /><br /><br />Thanks for any additional information.<br /><br /><br />BJ<br /><br /><br /><br /><br />Anonymoushttps://www.blogger.com/profile/00509945284063684979noreply@blogger.comtag:blogger.com,1999:blog-34020393.post-34843427924025581412010-06-09T16:24:03.357-07:002010-06-09T16:24:03.357-07:00@magster, I don't have a sample worked up, but...@magster, I don't have a sample worked up, but instead of &conn.createStatement(), you would use &conn.<a href="http://java.sun.com/j2se/1.4.2/docs/api/java/sql/Connection.html#prepareCall(java.lang.String)" rel="nofollow">prepareCall</a>("CALL MY.STORED_PROC(?, ?, ?)"). This will return a <a href="http://java.sun.com/j2se/1.4.2/docs/api/java/sql/CallableStatement.html" rel="nofollow">CallableStatement</a> For each parameter, call <a href="http://java.sun.com/j2se/1.4.2/docs/api/java/sql/CallableStatement.html#setString(java.lang.String,%20java.lang.String)" rel="nofollow">setString</a>. For each output parameter, call <a href="http://java.sun.com/j2se/1.4.2/docs/api/java/sql/CallableStatement.html#registerOutParameter(int,%20int)" rel="nofollow">registerOutParameter</a>. 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 <a href="http://jjmpsj.blogspot.com/search/label/Java%20Reflection" rel="nofollow">reflection</a>.<br /><br />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).Jim Marionhttps://www.blogger.com/profile/12995110203807924786noreply@blogger.comtag:blogger.com,1999:blog-34020393.post-22652372785114564832010-06-09T16:06:19.062-07:002010-06-09T16:06:19.062-07:00wow! so far this has all been super helpful. I a...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?Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-34020393.post-59110902216762184642010-04-22T13:43:27.495-07:002010-04-22T13:43:27.495-07:00Here are the 2 steps necessary (found this by sear...Here are the 2 steps necessary (found this by search JDBC in PeopleBooks) and our Infrastructure person figured out the files required:<br /><br />Application Server Setup (and maybe your Batch servers if running code in AE):<br />Summary: Copy both jar files to the classes directory. Update the CLASSPATH line in psappsrv.cfg<br /><br />1) Copy Oracle 10g Files (JDK 1.4) to Folder /appserv/classes<br /><br />o ojdbc14.jar - JDBC driver classes<br />o orai18n.jar - contains many important character set and globalization support files<br /><br />2) Update CLASSPATH on update file in "psappsrv.cfg" file for the Instance<br /><br /><br />[PSTOOLS]<br />;=========================================================================<br />; General settings for PSTOOLS<br />;=========================================================================<br />; RDBA Oracle JDBC driver<br />;Add to CLASSPATH=%ORACLE_HOME%\jdbc\lib\ojdbc14.jar<br />Add to CLASSPATH=%PS_HOME%/appserv/classes/ojdbc14.jar<br />Add to CLASSPATH=%PS_HOME%/appserv/classes/orai18n.jar<br /><br /><br />This was for PeopleTools 8.49.15Just Cherylhttps://www.blogger.com/profile/04301818586567329681noreply@blogger.comtag:blogger.com,1999:blog-34020393.post-10976557937151580482010-04-19T14:23:13.895-07:002010-04-19T14:23:13.895-07:00@Mohamed, this blog post discusses how to connect ...@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.<br /><br />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 <a href="http://download.oracle.com/docs/cd/E13292_01/pt849pbr0/eng/psbooks/tpcr/htm/tpcr20.htm#d0e51629" rel="nofollow">here</a>.Jim Marionhttps://www.blogger.com/profile/12995110203807924786noreply@blogger.comtag:blogger.com,1999:blog-34020393.post-57179554767949520972010-04-19T09:32:13.122-07:002010-04-19T09:32:13.122-07:00Hello.,
I am trying to establish remote database ...Hello.,<br /><br />I am trying to establish remote database connection through peopletools 8.49 to SQL Server 2005.<br /><br />But poeplebooks says that to setup JDBC drivers of SQL Server 2000 on the path to establish the connection.<br /><br />It makes confusion,<br />See.. we are using poepletools 8.49that supports SQL Server 2005.<br /><br />If we do with the drivers of SQL Server 2000, it is connecting. But it gives error in data sql select statements.Asgarhttps://www.blogger.com/profile/00239144480731820538noreply@blogger.comtag:blogger.com,1999:blog-34020393.post-15541563414045294152010-04-14T14:17:54.995-07:002010-04-14T14:17:54.995-07:00@Cheryl, you are SO right! I can't believe I f...@Cheryl, you are SO right! I can't believe I forgot the close() statements. I added them and credited you. Thank you!Jim Marionhttps://www.blogger.com/profile/12995110203807924786noreply@blogger.comtag:blogger.com,1999:blog-34020393.post-70700894043471906282010-04-14T13:54:43.574-07:002010-04-14T13:54:43.574-07:00I found that I had to close the connection, statem...I found that I had to close the connection, statement and the query within the PeopleCode program or threads were left on the database.<br /><br />Found a good link with examples:<br />http://blog.shinetech.com/?p=66Just Cherylhttps://www.blogger.com/profile/04301818586567329681noreply@blogger.comtag:blogger.com,1999:blog-34020393.post-42882888212737359992009-06-04T08:31:45.374-07:002009-06-04T08:31:45.374-07:00@k_malmos, Thanks for the great idea!@k_malmos, Thanks for the great idea!Jim Marionhttps://www.blogger.com/profile/12995110203807924786noreply@blogger.comtag:blogger.com,1999:blog-34020393.post-56807699203822883332009-06-04T05:34:49.050-07:002009-06-04T05:34:49.050-07:00Hey Jim!
One way we've been able to get aroun...Hey Jim!<br /><br />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.<br /><br />This way the password is already encrypted, and the config (for the Remote Database) is contained within the PeopleSoft environment.<br /><br />Cheers! And please keep posting new and exciting things!Unknownhttps://www.blogger.com/profile/06529680053762307453noreply@blogger.com