Thursday, September 25, 2008

Diving into Java

Well I finally wrote my first professional Java program. It was professional in the sense that it did more than "Hello World" and was for work. There was data made available on an external Internet web page. To get the data the users had to: select from 4 drop down lists, press a button, copy the data out of MS Notepad, paste the data into Excel. The kicker was to get all the data they were going to have to do this process 3,591 times. My challenge was to automate the download of all this data off the web page directly into our Oracle database. This is the story of my two week "Dive into Java".

I first started looking at automated web page testing software to see if I could script the series of clicks. It became apparent quite quickly that I would be able to handle the clicks, but not the copy and paste of the data out of MS Notepad into Excel.

I then started looking at the external web page. I "inspected" the source code using Firebug to see all the values for the 4 drop down lists and got to thinking that if I could simulate these actions in Java maybe there would be a way of capturing the web page results to a text file that could be loaded into the database. So I started looking into Java. Keep in mind that I had read Mastering Java 2 5 years ago and only written HelloWorld.java. All of the people that got formal Java and J2EE training in our department have left the company, so I had "hack" my way through this.

I discussed the issue with a colleague of mine. He gave me a small snippet of Java code which connected to a web page and wrote the results to standard output. If I could take this code, loop through the permutations of the 4 drop down lists (arrays), and write the results out to a text file, I would have the data.

I first had to get into a Java IDE, Oracle JDeveloper. I was slightly familiar with it. I had used it for the Entity Relationship diagrammer. I had to get used to how projects mapped over to directories. That was not too bad.

My next challenge was that the Java program program was unable to get out through the our firewall at work. I could only run it at home where I had a direct connect to the Internet. Abstract class URLConnection (wow I sound like a Java developer) could not get through the firewall. I got to thinking, Java applications like JDeveloper connect out through the firewall by prompted for my firewall ID and password. So I did some searching on the Internet and found Authenticator.setDefault. With this I was able to get through the firewall out to the external web page, but I had to hard code an ID and password into the Java program. That had to change, so now I had to dig into "prompting" the user for the information.

So I did yet more Internet surfing (see a pattern here) and came up with DataExchangeTest.java from the Core Java 2 Fundamentals. I now was able to prompt for my firewall ID and password and got rid of all hard coding. (Trust me it is all gone.) This got me into Java Swing/AWT (more Java alphabet soup).

Now I could run the Java program from computer at work. The program created a text file on my computer which I had to FTP to my database server, SQL*Load the file into a database staging table, and run a data parsing database procedure to populate the final destination tables. Could I write the data into the staging table in the database, taking SQL*Load out of the picture, and "kick off" the data parsing procedure from Java? Here comes JDBC. More surfing gave me JDBC basics so I could "write" the data directly into the database and populate the destination table.

My next step is to run the Java as a regularly scheduled batch job. It could get the latest released data and publish what the differences are. This is not a user requirement yet. Maybe I will take that on in my "spare time".

Wow, what a two-week long struggle with no training, but I did it. I can now put Java on my resume, not yet.

Mike

Monday, September 01, 2008

Last day of "Spring Cleaning"

Tomorrow is my last day of spring cleaning at work. Since I received no suggestions as to what to do with the old Oracle software and documentation, I am going to through it away in the junk tomorrow.

Mike