Maintaining / Auto-generating IBatis SQL Maps?

Question

Hello, I just started a new job and inherited the project from hell. Hell = {2 years over schedule, overly complex, uses both oracle and sql server}

There are 100+ stored procedures in the Oracle server and each one has a IBatis SQL Map. Some share the same result map. The DBA likes to change stores procs on a daily basis and not tell me.

Question: Are there any tools out there that can examine all the IBatis SQL Maps in the solution. Ideally it would verify:

1. Store Procedure exists
2. Store Procedure parameters match the ones in the parameter map
3. Store Procedure result [column names] match the ones in the result map
4. Store Procedure result is not missing anything specified on the result map
5. The object property titles in the result map match the ones listed on the result map

Background: I normally use just SQL Server and SubSonic 2.2 as an ORM. This way I just execute a command and my DAL is magically auto-generated, this way if a column that I need is missing I get a nice easy to understand compile time error and not a confusing run time error. Is there a similar tool I could use here?

thanks for your help!

Answer 1

There is a tool called Ibator but I don't think it was intended for what you're describing. My approach would be to create tests which exercise the iBatis code. That way, when a test fails you know something is wrong. Something else you can do is use metadata from Oracle to test for the existence of the procedures, etc. These checks could be additional tests.

Answer 2

It's been a while since I've touched Oracle, but if I recall correctly, the output of stored procedures (from a select, for example) is not declared anywhere. Therefore, it would need to be reverse engineerable by a tool. LinqToSql tries to do this, with partial success, for SQL Server, but it's generally a difficult and unreliable process. So, that would seem to make items 3-5 on your list near unachievable with code gen/tooling. I haven't delved too deeply into SubSonic 2.2's support for stored procedures, but I assume it would struggle with items 3-5 as well. In contrast, single output fields declared as part of the stored procedure are fairly straight-forward to deal with.

Items 1 & 2 are much more achievable, but I'm not familiar with the tooling available around iBatis, sorry. I don't think iBatis or Oracle really have much to do with your problem though.

Your best bet may be convincing your DBA to be more helpful and/or aggressively running diffs to detect and manually cope with stored procedure changes.