Java Stored Procedures

I am sure you are familiar with Stored Procedures. Almost every major database vendor supports it. They are program units that are precompiled and stored inside the database. Since the program is inside the database, it is tightly coupled with the database SQL language constructs. This and the precompiled nature of these programs, make the database access faster. So, even if you are writing external programs that access the database, you will benefit from coding and combining the database operations in stored procedures. This saves a lot of back and forth and thus network traffic. Since version 7, Oracle has support for Stored Procedures. These programs are coded a in native language called PL/SQL.

Since Oracle 8i, Oracle supports coding and running similar procedures (program units) written in Java. Like PL/SQL procedures, these programs are precompiled and stored inside the database and are known as Java Stored Procedures. The JAVASPs(1) are stored as classes (in blob fields) and when invoked they are run inside a JVM that runs within Oracle database. Prior to Oracle 10g, they needed to be wrapped inside a PL/SQL procedure or package. Since Oracle 10g, you can actually invoke Java classes directly from SQL (just the same way you call a PL/SQL function in a SQL).

JavaSPs are different from regular java programs in that they actually run inside a VM within the database. There are a few restrictions while coding a JAVASP.

Building and Deploying Java SP

To write and build the Java stored procedures, you can use your standard Java development environment. I use eclipse to develop and Ant to build it

build_LATAXSP.xml has steps to compile java classes and build jar file.

Deploying Java SP

At this point the jar file is ready to be deployed to Oracle. Typically we pass this step onto the DBA who will then load the jar into Oracle Database instance. But during development, developer can load these themselves using the loadjava utility. This is typically available on the machine where the database is running. (Remember, it is run by DBAs?). In our case we have the Oracle databases running on Unix boxes, so we have loadjava utility available there. I upload the file to Unix and run loadjava. While uploading make sure it’s in binary mode.

Below screenshot shows a sample run of loadjava command on Unix.

In this example, I loaded all the classes in a jar file, to the database. As shown there were 24 classes and 2 resources loaded and there were no errors. If the command failed to load the Java classes, you will see an error message here. The first time around, all the classes files are loaded. Next successive load of the same jar file, will load only classes that have been modified since the last load.

Verifying the load

To view the objects in Oracle, following SQLs can be used:

select * from all_objects where object_type = 'JAVA CLASS' and owner = <owner>;

or

select * from user_objects where object_type = 'JAVA CLASS';

To see a little more detail about the Java stored procs, use

SELECT * FROM user_java_classes; -- this lists java procs for the user.

Earlier I posted about displaying contents of the resources files (text files) loaded above.

Notes:
(1) Oracle actually refers to Java Stored Procedure as JSP. To avoid confusion with Java Server Pages, I prefer JavaSP.

[gallery include="5064,5065"]

Quick Tip: Java Stored Procedures in Oracle - Reading Content

This post is about how to get the contents of a resource file that is loaded into Oracle as part of Java Stored Procedures package.

While working on enhancing some Java procedures, I added a simple Java logger class, fashioned after Log4J. To be able to dynamically configure the logging properties (log levels, threshold), I added in a properties file, bundled into the Jar file itself¹. After loading the properties file into the Database (using LoadJava command; loaded as a Java Resource), I wanted a way to "see" what's inside the file. That's when I posted a question on StackOverflow, but eventually googled and found the answer. See my question and my own answer here. And here is the script, I came up with:

SET SERVEROUTPUT ON
EXEC DBMS_JAVA.SET_OUTPUT (1000000);
DECLARE
   bText CLOB; 
   len PLS_INTEGER;
   offset PLS_INTEGER;
   text VARCHAR2(2000);
BEGIN
   DBMS_LOB.CreateTemporary(bText, FALSE );
   DBMS_JAVA.Export_Resource('LAJavaSP.properties', bText);
   len := 1000; -- length of the lob to read.
   offset := 1;
   DBMS_LOB.Read(bText, len, offset, text); 
   DBMS_OUTPUT.Put_Line(text);
END;
/

This printed the following:

logLevel=ERROR
dbLogLevel=ERROR

For now, that's all I have in the properties file. Hoping to add more later.

---

1 Adding the properties file into the jar file makes it easy to read it using getSystemResourceAsStream. Otherwise, we will have to worry about Directory objects, Permissions etc.

(Project) Withdrawal Symptoms

By cartoonsv | View this Toon at ToonDoo | Create your own Toon

I must admit. I think, I am going through withdrawal symptoms. No, not that kind. I am not an alcoholic, and I don't do drugs. I do drink coffee, but definitely no withdrawal from there! This is a different kind of withdrawal. I think, I just OD'd on work and now I am feeling the project withdrawal symptoms. I did a regular day's work today, it feels like I am not doing any real work. I get out of the building, while there is some light, it feels like I am leaving too early!

I just finished a project of refactoring one of our websites for performance. It took a lot of long days and nights, incessant reading, writing, thinking. Lot of headaches, anxieties, uncertainties. At times, it looked impossible, insurmountable and the team members expressed concerns, doubts. Even the week we were getting ready, there were issues, bugs, failed tests, network issues. After all, we are in the tax (peak) season already, is this a good time to deploy such a big change? Is it going to happen? Is it worth it? It was like writing/reading a mystery novel at the same time. What is the end? I am writing it, but I am also hoping to read it ahead. Couldn't stand the suspense no more.

The number of tasks grew and grew, as I picked up slack for team members that went on vacation. Finally, it's over!! poof! gone! We installed the system Thursday night. No issues!!!!! What? No way! I was looking for issues. But none. It was a good thing, lot of planning and work went into it, yet it felt like a very bland ending of the mystery novel. Phew! No issues! It feels good. But, why does it feel like nothing happened? The rest of the team members haven't said anything yet. Did the implementation really happen? I look at the beautiful log file created by Log4J smiling at me. The changes actually helped us catch an issue, we otherwise wouldn't. Yes, we did it!!

Now, I am catching up with the rest of life. I was here, wasn't I? Calls to be made, photos to share, trips to make, people to catch up with. In the past few months, I would come home late from work, eat and jump right back on the computer, pouring over the source code, popping in and out of forums, go round and round the world wide web, eyes glued to pages of the books and the mountain of pages I printed. Now, it feels like I don't have anything to do. But, it's a nice withdrawal! No hangovers, no issues! Work, life balance? My wife thinks, I am back to life!!! Good to be back!

Software State of affairs - Maintaining OO

Last week, a friend at work was analysing code in Powerbuilder and question came up about function Overloading and we branched into Object Orientation in general. That question got me thinking. Though Object oriented programming has been around for a while, the concepts of OO are not completely understood or followed.

I posted about OO concepts here earlier. To do that I bought and read several books, sifted through the web a lot. There were so much confusion and conflicting ideas. Most of those books use obvious objects like animals, people etc in explaining OO concepts that what we learn in those is not enough to translate in to real life OO programming. I mentioned about some of these problems in my earlier post. More and more languages are adding OO concepts, yet people who are used to traditional (procedural) way of programming, struggle to adapt to it or even completely reject it. Often times, Syntax comes in the way of understanding. For e.g., Java borrowed so much from C, C++, that it's often considered to be just enhanced C, thus OO nature of it is completely ignored. And even if these developers switch to OO eventually, the objects they come up with are just repackaged C code, thus perpetuating the problem!

The application I currently support was written in Powerbuilder using a framework called EAF. The software also has business framework that is built around this, definitely a good extensible architecture. Yet, the software has degenerated over the years. Part of the problem is lack of understanding of the framework and/or OO concepts in general. I remember, when I started here, this framework was considered to be the main reason for all evil in the software! There was not enough documentation about proper framework usage, thus it was easy to blame it!

When new developers came in, they wrote and rewrote functions and objects that completely ignored underlying framework thus code is bloated. Further, supporting business has been utmost priority, so such technical issues were completely ignored. I feel, every maintenance project should include the maintenance of the software itself - in addressing technical issues and retuning it to keep it running good, like we do with our cars. After all, business will eventually be affected, if we didn't take care of such technical issues in time! The very first problem I solved in this software was a technical issue - there was a memory leak (due to improper handling of Object pooling) in the software that kept crashing the server during peak usage in the prior years. Business of collecting taxes, was indeed affected during those crashes. And I cannot imagine how much time, effort and resources were wasted during those times.

Another problem I see in software maintenance, is the tendency to solve code smells and code bloats by adding more resources - more servers, more disk space, more memory. When a car runs sluggish, we can't solve it by just adding more engines or more tires, can we? Why is it OK with software? Prior solution to the memory leak problem I mentioned above, was to add more servers and a script to automate the Server Restarts! Once the memory leak was fixed, no more frequent crashes!!!

To curb some of the issues in the application, I recommended and started on a project to re-factor some of the code in the application earlier this year. Main goal was to improve performance in our web application, but I wanted to clean up and reintroduce some of the framework methodologies.  Though there was some push back initially, other developers started buying into the idea. Yet, I see the reluctance to change existing ways; after all the software works during normal (low?) usage and business is OK (if not happy), why change those? When I first introduced log4J (logging libraries for Java) for logging in our JSP pages, it took the team at least couple of months to buy into the idea, in spite of me showing stats to support the change.

Our application architecture uses EA Server (EAS) as the middle tier. EAS supports Java and Powerbuilder objects naturally.While Powerbuilder is great for developing applications quickly, Java was used for any multi-threaded activities such as caching and logging etc. We even have Java Stored procedures running inside Oracle database! I recently introduced tools like Eclipse, Ant etc. to streamline Java development activities. With such exposure and addition of new Java packages prompted a team member to say to the effect "we are becoming a Java shop whether we like it or not". I've tried to explain in vain that it was all there before and why we even need them!!

Part of the problem is also lack of career planning and/or training. I remember the days when companies invested in training employees to keep up with the industry trends. Now, we seem to be spending more in buying those extra servers to support more memory leaks :)

Gotcha: JSP includes

This is related to my earlier post. When we fixed the issue in Servlet Filter in the JSP Web Application we were troubleshooting it went past the first page. We landed on a blank page this time!! We were testing in a mirror site that I had created. According to the developer, the original site work and the new mirror site was having this issue.

To cut the long story short, this was caused by a change developer added to a JSP file to invalidate session. This file was included in the main JSP page that was erroring out. After some digging, I found out that the original JSP wasn't compiled in a while (See below). Bingo! I made it to recompile, and the original site also failed at the same place. This proved the invalidate() added to be culprit.

JSPs are just short hand notation for Java Servlet. It lets the developer/page designer to code the page using HTML like tags. The first time the user accesses the JSP file, the Servlet container automatically generates and recompiles a Java Servlet for the JSP file. After the first time, this step of generate/compile is skipped. In development we could make the Server to recompile each time the JSP file is changed. This works fine. The problem in our case was that an included file was changed and since the main JSP file itself did not change, the Server didn't bother to regenerate the Servlet class.

The lesson learned is not to believe what you see or not see on a JSP page. Always make sure your JSP is compiled up-to-date. To do this, you could use the compiler that comes with the Server (Servlet container) software. Most Servers (for e.g., Apache Tomcat, IBM Websphere, Sybase EA Server) come with a command line script called jspc. Another approach is to clear the your Server's work directory. See here for a discussion of this.

Gotcha: Java Concatenation

The Problem

Recently one of my co-workers had an issue with a website written in JSP. Every time he went to a page, a Servlet Filter was failing with an error that the Java class "StringBuilder" was not found. I've added this Servlet Filter in the application recently and this worked fine on other machines and in our Test Servers, but failed on this one machine. Also, we use Java 1.4 and there is no way we could be using Stringbuilder which was only introduced in Java 1.5.  (Java 1.4 has only StringBuffer. See here for a discussion on the two).

(I know we are using older technology - we use Sybase EA Server 5.5, which forces us to stick with Java 1.4, but go with me on this.)

Java Strings and Java Compiler

I set to find our why it was complaining about something we don't use. I looked at the class file generated. It had "StringBuilder". Then it hit me. We use a lot of String Concatenation in our code and we use String concatenation using plus sign (+). This seemingly innocuous usage caused it and the error has to do with Compiler Optimization.

Java Strings are immutable, meaning they cannot be changed at run time. Each time we concatenate Strings, we are essentially creating newer and newer Strings.

So, str1 + str2 + str3 is essentially equivalent to

res1 = str1 + str2

res2 = res1 + str3

But Compiler may take a different approach to optimize concatenation using StringBuffer or StringBuilder (See here). So the above, concatenation can be rewritten (by compiler) as,

StringBuilder(str1).append(str2).append(str3);

So this is what happened in our case. Some concatenation got turned into StringBuilder by the Compiler and hence the error. Wasn't that supposed to read StringBuffer (because it's supposed to be Java 1.4)? Well that was because of another mistake. When he set up Eclipse our friend used a newer version than the one I recommended which defaulted to Java 1.6 compiler which caused the optimizer to produce > 1.5 code!! Hence the error on StringBuilder. But the error showed up in the first place because of the Compiler Optimization! If the compiler used the String.Concatenate in the first place, we would not have seen this error!!.

And Compiler may sometimes choose to do just that, use String.Concatenate as shown in preciseJava.com. Also, such Compiler optimization may not be always good. See this interesting blog about various possibilities of concatenations.

Oh yeah, We overrode the workspace settings in Eclipse to use 1.4 compiler for the project and all is well now.

Reference Links

1. http://chaoticjava.com/posts/stringbuilder-vs-string/


2. http://javamoods.blogspot.com/2010/02/optimization-dont-do-it-compiler-will.html


3. http://www.google.com/url?sa=t&rct=j&q=&esrc=s&source=web&cd=5&ved=0CF4QFjAE&url=http%3A%2F%2Fciteseerx.ist.psu.edu%2Fviewdoc%2Fdownload%3Fdoi%3D10.1.1.122.5641%26rep%3Drep1%26type%3Dpdf&ei=LIK6UMCwBMWUjAK4_4HIBw&usg=AFQjCNFo6trLwU4wlxgrTNFBa1tGb1p9Cg&sig2=qHQIg8S0qI-CXdJ6EOaa8w


4. http://www.precisejava.com/javaperf/j2se/StringAndStringBuffer.htm


5. http://code-thrill.blogspot.com/2012/08/stringbuilder-optimizations-demystified.html

Java: What's up with "Impl" in Class Names?

What to name it?

This is a constant question we are faced with in daily life. We want to come up with perfect names for our babies. We want to name our animals (pets) and they seem to be happy to have a name! And it seems to be a big thing! Otherwise we wouldn't have naming ceremonies like these. We try to name (rename) our buildings (otherwise what would  you call Empire State Building?), streets, cities and countries. (Myanmar or Mumbai is yesterday's story. Mexico wants to rename themselves as Mexico! See here.) And naming is not an easy thing to do. A world recognized music composer in India, Illayaraja, called his symphonic music album "How to Name it" for lack of a better name.

What's that got to do with this?

Java developers (or any developer for that matter) are merely imitating the real world and they too have to face such challenges while naming their programs, classes and objects. We don't want to name (can't) two entities same name. That would cause confusion (Name Collision). To avoid this, we use packages, name spaces, domain etc.

Each "object" tends to have 2 forms in Java world (or .Net, C++ etc) - the class and it's interface! An interface is a class's "public profile" - much like public profile we have in linked-in, what we want other classes to know about this class.

We could say the interface is not unlike a business card or a cover letter we send with our resume when we apply for a job. Cover letter contains exact amount of information that may be of interest to the other party while the resume may have lot more inner details. Now they both are about you, but not the same.

So, what's up with "Impl"?

So, if you are a developer, what would you call the interface and implementation for an object that models a real car?

[caption id="" align="alignright" width="356"] OO interface names[/caption]

This is not as simple as it seems. There are at least 2 camps. One camp, the Microsoft based camp calls the (implementation) classes by just the name and prefixes their interfaces with "I". To them the Implementation is the real object and interface is it's business card. The other camp (seemingly started by IBM) uses the plain name for interfaces and the (implementation) classes are named with a suffix "Impl" for Implementation. Here the interface represents the abstracted real word object and the Implementation class merely has extra (inner) details that doesn't concern outsiders (other classes). The Java world mostly follows the "Impl" convention.

So there you have it, when you see Java classes named with "Impl" at the end, you know why. End of story, right? No, not really. Object Oriented pundits and purists don't agree. Here is a nice post where the author diisagrees with "Impl" usage. And here is another perspective to it. And it's an ongoing debate. Don't believe me? See here, here, here.

So which way to go?

Naming your class can be a personal choice, but most of us write programs that will be shared with others in the community. That's why conventions and best practices exist. If you are working on a project, set up and follow standards appropriate to your shop.

In some cases, you may not be able to change the "I" or "Impl" in names. Many programs may have some type of interface (protocol) like CORBA, COM, Web Services. In these cases, they generate code (Stubs, Skeletons etc) that you have to based your own code on. Document these and stick to those conventions to avoid any surprises.

Personally, I am OK with using Impl suffix. I work with EAServer which runs both Powerbuilder and Java objects. These objects "talk" to each other using CORBA IIOP. Both Powerbuilder and EAServer tools generate CORBA IDL files which in turn are used to generate stubs and skeletons in Java (or C++). Developers then write Implementation classes for the interfaces generated. The class files generated and the impl classes may end up in the same Classpath and to avoid Name Collision, we have to name implementation classes differently and the convention is to use Impl suffix. This is a convention I am sticking with in my current project.

Reference Links

A note about my "cheesy" cartoon there. While searching for something relevant I found Toondoo where you can make your own cartoon. You can start with a free account. I also use openclipart for some of the pictures in my other posts.

http://www.toondoo.com

http://openclipart.org

http://isagoksu.com/2009/development/java/naming-the-java-implementation-classes/

http://docs.oracle.com/cd/E13183_01/en/alui/devdoc/docs6x/aluidevguide/ref_idk_deploymentimplkeys.html

http://leshazlewood.com/2009/03/03/java-class-naming-conventions/

http://www.gnu.org/software/libc/manual/html_node/Interface-Naming.html

http://osherove.com/blog/2006/6/14/interface-naming-anything-but-javas-standard-please.html

EA Server

I've been posting about Powerbuilder a lot lately. The PB application, I work with currently is actually an n-tier application written in PB, Java and JSP. The application is built around a middle tier from Sybase called Enterprise Application (EA) Server. It's also called Jaguar (which is actually a part of the EA Server). A quick search for EA Server in google brings up Electronics Arts page, a gaming site. Search on Jaguar brings up all kinds of other things. So, there is not a lot of help on EA Server, though Sybase's own site has a wealth of information on the product. In my current project, we rely on EA Server for all our business rules and multi-tier application support. I am learning about the product in an effort to improve our architecture and thus our application performance. I will my share my experiences here, so it can benefit others trying to learn or use the product.

EA Server is an Application Server like Oracle Weblogic, IBM Websphere, JBoss etc. It is a Java container like all others mentioned. EA Server is a full blown Java server, so it supports Servlet, JSP, EJB and JMS. Apart from that, it has a speciality of being able to run PB objects as well. With support for CORBA (IIOP) and COM (on windows machines) components, it enables PB developers to develop application modules in PB, Java, C++ that can talk to each other through CORBA. For e.g., we have a site built using JSP pages that "talk" to PB objects. Recently, I developed a wrapper in Java for PB classes, so our Coldfusion applications can use PB objects as well. With this setup, we are able to implement an n-tier application in various languages.

While researching the issues and figuring out new methods to improve our Server performance, I realized how powerful the server is. I currently work with EAS 5.5. The latest version is 6.4. In 6.x, Sybase rearchitected the product completely to be more Java centric. To be able to do that, they included a Jetty Java container and wrapped each PB object as a J2EE object, so everything runs as Java objects. I haven't tried this version yet, but it sounds interesting. Unfortunately, from what I heard, Sybase (SAP) is thinking of getting rid of this completely in a few years and re-architect PB support into SAP's Netweaver server. So, really the knowledge about EA Server may disappear in a few years. (This is exactly what happened to Forte! Is it my luck or what?).

Irrespective of the fate of the EA Server, I believe the techniques learned in this exercise will be useful with other servers as well. I am already, trying our web app in Apache Tomcat which can then connect to our PB objects running in EA Server. I will share these experiences here as well.  So, if you are interested in EA Server or writing n-tier in PB or even how to make PB talk to Java components usingCORBA, please check back in.

Java & I - My Java Development experience

Over the years I worked with various versions of Java through 1.6. But, Java was never my main bread & butter. Irrespective of the environment there was always some flavor of Java used in the development environment.

Java Swing, Applet etc
I started working with Java 1.0, many years ago. All those books and web sites with applet code with flashy animations made it look really interesting. (Real applet experience was totally different though!). Since then, I've worked in Java on and off. For a few years, I worked in a Java swing building Java applet (2MB jar!!) against a Forte Server. This included working with Visual Cafe, Visigenic CORBA, IDL files etc. Then I had chance to upgrade the application Java 1.2.

J2EE?!
Later, I got a chance to work on a J2EE application against Oracle. This ran in Apache and JBOSS. Java bean, EJB, Servlets, JSP and the whole nine-yards of typical Java developer's field. Then I got contracts to work on PB and VB. I kept in touch with Java though.

Java as a reporting tool
Couple of years ago, I got back into Java when I worked on a Java reporting package based around BIRT Java API for working with Excel files. (I think it's owned by Actuate now). The Java program was part of the batch environment on UNIX. We used it to generate Excel reports (with multiple worksheets) to be sent to users via e-mail. This was a challenging project.

Java Stored Procedures
Recently I got a change to work on yet another Java flavor. As mentioned elsewhere, where  I work currently, the environment is mainly in Powerbuilder and Oracle. Every now and then we find some surprises (in the last 18 months I've learned to expect there). There are a lot of Java packages/programs built around the PB application. These run on EA Server's Java container and use CORBA layer to talk to the PB application. The one I worked recently was slightly different. It was not the usual JDBC application, but a Java Stored procedure(s) that runs inside Oracle. I ended up in cleaning up the code, setting up a development environment around Eclipse and Ant and setting standards for other developers.

Java JSP, Coldfusion
We also have a lot of JSP pages that use CORBA to talk to our PB application through IIOP/CORBA. Recently we started building Coldfusion pages. While trying to help the developers to interface with our PB application, I realized that Coldfusion has been completely rewritten in Java. Coldfusion server was running on a Linux box. To get this to work with our Powerbuilder (EA) server, we needed a Visigenic product. (I eventually wrote a Java class that interfaces with the CORBA thus isolating Coldfusion from CORBA thus saving money for the company).

So many places, so many flavors. I've been having fun with Java. I hope to share some of these experiences here. In the coming posts, I will go into more detail about these Java experiences, hoping it would benefit some newcomers.