Abator - change to allow it to produce stored procedures instead of SQL?

[Raymond McDermott <ra...@mcdermott.be>]

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

Re: Abator - change to allow it to produce stored procedures instead of SQL?

[Raymond McDermott <ra...@mcdermott.be>]

Thanks - I think you have a point for completely generic generation, which
of course would be the best use case for a framework.  I think that a little
XSLT will do what we want, so thanks for the input.

I will post the results.

On 29/11/2007, Jeff Butler <je...@gmail.com> wrote:
>
> 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 <ra...@mcdermott.be> 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
> >
>
>

Re: Abator - change to allow it to produce stored procedures instead of SQL?

[Jeff Butler <je...@gmail.com>]

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 <ra...@mcdermott.be> 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
>