Build and execute stored procedure 'on the fly'

[iksrazal]

Hi all,

I've been requested to build a String that represents a stored
procedure (SP) dynamically at runtime, and afterwards execute it. The
DB is beta mysql5. My questions are:

1) Is it mandatory that in JDBC a SP be called by a CallableStatement?
For example, a SELECT statement can be built and executed at runtime,
could this also be done for a SP?
2) Does a SP need to be installed into the DB before being invoked?

I ask because I think I my need to install the SP at runtime, after
being built, and then call it via a CallableStatement. This would
require, I believe, redirecting the SP to a SQL client shell via Java
and stdin, and afterwards, invoke the CallableStatement.

Any ideas?
iksrazal

 

[Bjorn Abelli]

1) Is it mandatory that in JDBC a SP be called by a CallableStatement?

Yes, although "some" JDBC-drivers could provide other means...

For example, a SELECT statement can be built and
executed at runtime, could this also be done for a SP?

That depends on the actual JDBC-driver, but I would say that it should work
in most cases.

But the creation of an SP is "just" a statement (CREATE PROCEDURE...), so
you should use a simple "execute(...)" on a simple "Statement" for that.

2) Does a SP need to be installed into the DB before being invoked?

By its nature an SP is an element in the database, so I can't see how it
would be possible to call it otherwise... ?

There's a reason why it's named *stored* procedures... ;-)


// Bjorn A