Wednesday, January 27, 2010

SQL: describe a select statement instead of table

QA asked me to put max lengths in input fields that would map to values that would eventually be inserted into the Oracle database. The problem was that these values were not going into a single table, but about 10 different tables, which were defined by a complex select statement. I didnt want to describe each individual table to find out the max lengths on the column names

Take the complex SQL statement and create a view with it:

Then describe the view, and drop the view when you are done with it.

Friday, January 22, 2010

IBatis: Iterate over multiple lists at the same time

I needed to verify if each record passed several rules, where each rule consisted of several flags and their values. The problem was complexity: I had 7 flags(which had 3 possible values) to check, and 1 to 5 rules per status. This could lead to some ugly and hard to test SQL.

Another problem was that you cant iterate over multiple lists at the same time (using the same index) in ibatis.

The solution was to use Ibatis's Iterate directive. I would iterate over lists of maps, where each map represented a rule, and each bucket of the map represented a flag and its expected value.

Those iBastis code ended up looking something like this:

and the parameter class had the following code:

This worked pretty well to abstract the flags and rules away from the iBatis sql and keep them all in the rules class.