Two years ago (2009), a colleague of mine and I presented at RMOUG on using Oracle’s BPEL with Microsoft’s SQL Server to create Web services. Since we were using SQL Server instead of Oracle, we had to create xsd files to define our data. This took quite a bit of time, though not nearly as much time as dragging arrows to connect each of the items in the xsd file. In the end, we had a working deployment that realistically took about six months of duration and maybe 300 hours of work. Most of the problems were due to lack of available documentation at the time, and a difficult time getting Oracle’s BPEL product to play nice with SQL Server. The same service using an Oracle database could be done quite fast. Once you get the hang of it, we are talking minutes. Of course testing takes quite a bit more time.

Well, fast forward to the present. I was in a meeting and the topic of using Web services in Application Express was bantered about. After a few minutes, most of the team was looking at me. I am the one who experiments with products, and they were correct in assuming that I had the solution. Actually, there is a weather example available at Oracle. I was able to demo this example in a few minutes, and show how easy Web services are to integrate into APEX. The conversation then moved on to discussing whether the organization should use our purchased SOA suite to build a Web service library, but that is an entirely different story.

I found myself unable to stop thinking about the difficulty experienced using SQL Server and BPEL. I really couldn’t stop thinking about it. So, I did something about it. Several years ago, I had written a paper on Web services using AXIS. This had been several years ago and AXIS was now using a completely rewritten code base. What’s a guy to do? After downloading the Axis2 1.5.1 Release, I extracted and deployed the included war file to the Tomcat server running on my laptop. There is a samples directory, and one of the easiest ways to get started is the pojoguide example. Basically, there are four files that make up this sample. There is the service definition, src/META-INF/services.xml. In this file, you name the web service that is defined in the service directory.
In the data directory is a bean, src/pojo/data/Weather.java, which simply defines the output. For simple Web services in AXIS2, it seams easy to just create a class, and either return this class, or an array of the defined class as output. Believe me, this method is really easy to use! Okay, the rpcclient is more complicated. In my case, I replace this with a client directory, and created a Test.java file. I use this to test my Web service. After all, it is really just a Java class. In the end, I simply use a browser to test my Web service, displaying the results in my browser. The big deal is the data in the service directory. src/pojo/service/WeatherService.java contains the actual Web service. The class is the Web service, and all methods can be services that you call. Easy cheesy! For examples, I have just copied the pojoguide directory and simply renamed it, then updated the four classes described above. Of course, you have to update the build.xml file to point to the correct files and directories, but that isn’t too bad. Use ant to build the Web service to generate deployable file and test the service itself.

So back to my original point. After downloading the jtds SQL Server driver from SourceForge, I copied the jar file to the lib directory under axis2/WEB-INF and restarted Tomcat. I also had to add this to my build path in ant to get an error free compile. Now I could connect to my database. 15 Minutes later I had created my first AXIS2 Web service in years. Another ten minutes, and I was able to integrate this into APEX. Two great products working together. Technology is awesome!