I don’t have a lot of time to write, because I have to work in the morning, but I just wanted to quick get it down that the Cubs game thing with my high school classmates was really fun. I was really nervous that it might be ackward, but it was actually really fun. I’ll have to write a bit more maybe after work tomorrow. I’m tired and dreading work in the morning.
Vacation during Memorial Day week was good. By about Tuesday, I was ready to come home, but the rest of the time that I spent with the family was still fun. It always amazes me how well Disney does with their stuff. It’s also very clear that Disney is largely a vacation company who uses intellectual property for their destinations. I almost felt a little hippocritical for going to the Most Copyrighted Place on Earth, but just a little. I had a good time, anyway.
Work’s been crazy since I got back. We had a new project that was due in six weeks for testing the Monday after I got back. By that Thursday, it was due in four weeks. I’ve been working like crazy since then to get as much done as fast as possible. As a result, all I really have to write about is work.
Interestingly enough, even though this is a web project and we’re using Cold Fusion, I’ve actually been turned into a Java programmer for this project a couple times now.
The first task was that eventually, our web system is going to have to generate barcodes. Now, I have a JavaScript library to do this using single pixel black and white images and tables in the browser. However, to me that seems a little dangerous for a system that could need to work globally. I have also run across Barbecue, a Java barcoding library, in my barcode research in the past. Since Barbecue can render and write barcodes out as files, this seemed safer than the option of requiring fonts be installed or using JavaScript to draw the barcode in table cells in the browser.
For this, I write a class that encapsulated the specific functionality that we needed, plus provided access to some methods that are within Barbecue, as well as some methods to provide access to Barbecue’s Barcode object without having to worry about instantiating a Java object in the Cold Fusion code. This way, all of the instantiation is taken care of in a Cold Fusion file that can be included and has function calls to create the barcode as a JPEG or provide the Java object without dealing with the cfobject tag.
The second task is to take an Excel file uploaded from the web that has a variable number of columns for its layout from instance to instance and get it into a database table which has a constant number of columns. The format of the Excel file can be specified, but each upload will use a different format due to the varying number of columns. Here we go.
First, I found the JExcel toolkit which provides the ability to read and manipulate Excel files entirely in Java. There are other toolkits out there, but some rely on ODBC drivers, Windows COM objects, and who knows what else. JExcel is a free software all-in-one solution.
I’ve written another Java class that takes an Excel file and validates it. If there are errors, it spits out a color coded version of the file. The validation is more flexible than I will let others know, because it doesn’t really care what order the columns are in as long as they are all present. Since I will be giving a formatted version of the file to work from, I’m going to tell people to stick to the format and discourage them from playing with the file. Hopefully there will be fewer questions why one keeps getting back their file this way. Though with the color coding, it should be obvious if you ask me.
If the file is good, it needs to be converted to something that we can use. Variable column numbers won’t be able to be imported into a database table. Next, there is a function to create a destinations file. This pulls out a subset of the columns in the table, assigns them a segment id and outputs them to a tab-delimited file. Next, we create a details file. This file has the same segment id as the other file, plus one column name and that column’s value. Hence, if there are six variable columns in the input, there are six detail records generated that tie back to the destination using the segment id.
Now, we have these two files, information to tie everything together. It’s time to get it to the database. Due to a rights issue, the web server’s connection to the database is not allowed access back to the web server. While this could be fixed, to me is says that if something were to go wrong with a server in the future or we have an upgrade, it could be overlooked and the application will fail, so no bulk load SQL constructs. However, the agent running on the database does have rights to the web server, which I can hope will stay consistent from upgrade to upgrade. The fact that it works without intervening now is more promising than changing things, anyway.
So the database agent will have to do this for me as a job. I have written a stored procedure to create a job for the import, which runs a local data package on the server to insert the rows from the two files into the two tables, and tie them back together using that segment id with a more meaningful link. The segement id is then removed to stop developers from making poor queries. Since the Java class is unaware of what’s in the database, the tie would require passing a second parameter in every query to not mess up an order. Better to err on the side of caution.
From here, the orders are scheduled to be inserted into the database. If the job succeeds, it is removed from the jobs listing, if it fails it stays. I can query the job’s status and provide reports of jobs in this category that failed in the morning for followup if necessary. In practice, I’ve been able to get 10,000 rows after transformation (2,000 before transformation) into the database in a few seconds. The longest part for the user is to upload the file to the server from the web. The Java part with validation and transformation really moves quite fast, from what I can tell.
To recap the process:
Maybe this seems simple when it’s in a nice list, but it has taken a great deal of my time to get where I am at now. This is not a completed project yet, either. I still need to add some extra validations, and other things, but it is getting closer to feature complete each day. My biggest hang up right now is that a lot of the process for things moving through our new system is not clearly defined.
Maybe the conglomeration of technoligies for this solution seems odd. I’ve tried to use technologies that have the least amount of dependence on each other and smallest number of network rights issues. I’ve also tried to make sure that each step does not have to know about what comes before it or after it, successive steps should only need to know that the previous step succeeded.
Unfortunately, this is only a sub-project of a larger project, on which the clock is ticking. I’ve been working pretty long hours this week to get this to such a state. Most likely the next thing that I’ll be tackling will be a pick-and-pack system to use with this data or an interface to our shipping system. Luckily, I know a lot about the shipping system and the interfacing that we’ll be doing will be rather elementary.