I did a pretty serious investigation into this a few months ago. The bottom line is that it is not really feasible to do code generation for stored procedures. The main issues are these:
1. The database metadata does not have enough information to fully understand what will be required for input and what the expected outputs will be for stored procedures. In addition, since stored procedures are programs the outputs may differ with different inputs (i.e. sometimes no result set, sometimes 1 or more result sets). Finally, the metadata methods for stored procedures are all optional - so some drivers don't implement them at all. 2. Because of #1, it would be necessary to execute the SP with a variety of inputs to understand the outputs. The XML configuration for describing the different input scenarios would be onerous. And even worse - executing the SP solely for code generation purposes would likely have unintended side effects in the database. With that in mind my opinion is that stored procedures, in general, are not an area that would not significantly benefit from code generation. If your particular situation is that you are using SP's to simply wrap existing SQL, then I think an XSLT could be a good way to go - either that or create a custom implementation of the SqlMapGenerator interface to meet your particular needs. Jeff Butler On Nov 29, 2007 10:17 AM, Raymond McDermott <[EMAIL PROTECTED]> wrote: > Our mainframe standards require us to produce SQL in the form of SQL > stored procedures. > > We use Db2 v7.2/zOS now (v8.1 next year). > > The template for the stored procedure is well understood and I would just > need to plug in the parameters and the sql. > > We like Abator for producing the original SQL but now face the problem of > dropping out of it once we have created the stored procedures. This is bad > for changes / refactoring and we would like to retain the flexibility and > productivity that ABATOR offers. > > My first thought was to apply some XSLT to the output of ABATOR but then I > wondered if it might be smarter to provide a change to the generator so that > the output is simply correct. > > Any ideas on what would be the best way for me to attack this? > > Thanks > > Ray >
