Sun – Week Forty
9 04 2009This week I’m going to try and finish that email script…A while ago I was assigned the task of writing a module for our booking system.
The Problem
Engineers can book hardware to escalation numbers (customer cases), however, we know that a number of engineers take advantage of this, and book systems for other, more personal, uses under the same escalation numbers.
These bookings don’t end unless:
1) The engineer or a lab team member removes the booking, or
2) The customer case is closed.
The Solution
Create something that will check the database for bookings which have been active for more than X number of days and bug the engineer via email to see if they still need the system.
What I’m doing
So, after achieving this once in Perl (*spit*) and it being little good (because we want to trigger it from the database), I gave it another bash:
This time I’m using a stored procedure (from now on referred to as an SP) which will collect a list of bookings. This will then run a Java application, passing in the booking number as an argument. The Java application will then connect to the database again and call another SP which will recover the booking details (I couldn’t pass them in as arguments as the method of calling external scripts from the database is limited to 255 characters).
This is basically where I am now, at the end of the week. Next week I’ll need to find a way to get the Java application to email the user of the system, and maybe look at a better way of passing in a list of booking numbers, as having loads of these processes running could seriously slow things down…
I will, at some point, blog about connecting to a Sybase database using Java, this should be fairly generic, I just found other documentation for doing this very poor 
Sounds complicated!
Could you not trigger your Java app from the database as a standalone job and have the app do the querying (you could even encapsulate the resultset in a stored proc) instead of the other way round?
It could retrieve all the necessary information with one query, and from here it could email everyone it needs to based on the list of dated bookings retrieved – minimises DB interaction
Rob
Yes, that would be a much better idea! Would help keep all the code a bit more together too, rather than it spilling from database to application!
Cheers Rob
No problem – interested to hear about your gripes with Sybase; sounds nasty!
I wouldn’t have suspected it too much of a problem with JDBC ?
The main problem last week was I was unsure how to find information about the database like version numbers, and access details (port number and database name for instance).
Once I found out these and managed to find the JDBC driver, it has worked pretty well! I’m not sure I like the Object the database returns, the ResultSet Object would, in my opinion, be better being scrapped and just let the database return an 2D array of Objects…But since I’ve now written my function to do something similar, it doesn’t matter too much
Ah I see! I don’t think there’s much of a case for the ResultSet object to be scrapped, but it is very convenient to work with an ORM and be able to have a List returned
Hope you get it sorted!
Well, after an email to the guy I’m doing this work for, he wants to spawn a Java app for each booking…
I’ll detail it more in this weeks entry…
[...] Not a moment too soon either! OK, so I’ve listened to what Rob said in the comments from last weeks entry and started to re-plan things, plus sent an email to the guy I was doing the work for. He came [...]
[...] Not a moment too soon either! OK, so I’ve listened to what Rob said in the comments from last weeks entry and started to re-plan things, plus sent an email to the guy I was doing the work for. He came back [...]