I work a data transformation product that has a GUI interface that maps one type of object to another. So it has a bunch of elements on the left that maps to elements on the right. These can be XML, Java, or database. With database the product is built on top of Hibernate, so it creates the definitions based on importing the metadata and runs the queries using Hibernate. In the product, you call a DatabaseSelect() function which is associated with the root element of a collection of table elements and that function provides the parameters for the where clause, something like this (imagine this is part of a GUI):
Person [call DatabaseSelect(where LastName > "K")]
Id
LastName
FirstName
...
We want to add stored procedure support as well. My understanding of stored procedures is you can't know what they will return though the normal JDBC meta data, so we will require the client specify the result set elements manually. I was thinking of implementing the user of a stored procedure to populate a "table" in a similar way that a select is used, someting like this:
StoredProcResults [call DatabaseStoredProcedure(value 1, value 2, value 3)]
ResultValue1
ResultValue2
ResultValue3
...
I'm hoping that doing it this way will handle 90% (or more) of the access to (legacy) stored procedures out there in a convenient manner.
The other level of support we are considering is the use of a stored procedure as a function that can be invoked as one of the arguments to the select statement as specified by the value of the Where class in the DatabaseSelect() function (above). I think Hibernate should support this usage directly (where the stored procedure (function really) returns a scalar value.
The case this will not handle are the cases where a stored procedure is used as part of a select statement, that is where it must get involved with access to other tables through a join.
My questions:
1) Is this level of support useful and will it cover most required access to stored procedures.
2) Is there a better way to handle this?
TIA