The problem with stored procedures is that they have parameters and
parameters are inflexible. You can use SQL to return result sets but what
happens when you try to send large amount of data or complex data?
The answer is to switch from an ODBC interface to a web-services interface,
which has no problem with hierarchically-structured data exchange (XML or
JSON). ODBC is also too hard to secure.
Notwithstanding the foregoing, the use of stored procedures has provided a
means of retaining business logic on database servers, which is where
business logic belongs. The migration of business logic off database
servers has been a huge travesty, and a blot on the IT industry as a whole.
The idea of moving business logic off database servers dovetails with the
problems that people have expressed regarding OO languages. OO has a place,
but the use of OO for business objects was a bad use case generally, and
its overuse has become a quagmire in many organizations.
Returning to the idea of deploying web-services as an alternative to stored
procedures, the impact on mainstream language environments that run on
servers (i.e. aspx.Net, PHP, Python, Ruby, Java) would be huge. There's
essentially no need for middle-ware language environments to be involved at
all. They just add latency, bottlenecks, potential failure points,
potential security risks. They would be utterly unjustifiable.
On the client side, browsers would likely produce and consume JSON. On the
IBM i side, RPG programs would likely produce and consume JSON.
midrange.com
