Stored Procedures

When sending requests to the database, the request is first parsed then executed. Instead of parsing many times the same requests, i.e. with only changes in expression values, most of RDBMS enable to precompile requests. These requests can then be executed as routines and are identified by a name and a signature.

EiffelStore lets you use stored procedures with DB_PROC class to:

• Execute a stored procedure.
• Create a stored procedure.

Executing a stored procedure

To execute a stored procedure:

• Create a DB_PROC object and load the stored procedure you want to use:

procedure: DB_PROC ... create procedure.make ("UPDATE") procedure.load if procedure.exists then ... end

• Execute the procedure through a DB_SELECTION (if a result is expected) or a DB_CHANGE object (otherwise).

Note: Requests with a result (DB_SELECTION ) or without (DB_CHANGE ) are both abstract expressions. DB_PROC executes an abstract expression using an object of DB_EXPRESSION type, which corresponds to an abstract expression. DB_SELECTION and DB_CHANGE inherits from DB_EXPRESSION .

You can execute your request mostly like a basic one:

• Create your request.
• Bind request variables. Variables are stored procedure arguments.

Note: Take a look at how to bind variables to a query.

• Execute the query through the DB_PROC object.

procedure: DB_PROC expr: DB_CHANGE ... procedure.execute (expr) expr.clear_all

• Check for errors and load result if any.

Creating a stored procedure

DB_PROC also enables you to create or drop stored procedures:

• Use store to create a procedure.
• Use drop to delete one.

The following example shows how to overwrite a procedure in the database: procedure: DB_PROC ... create procedure.make ("NEW_PROCEDURE") procedure.load if procedure.exists then procedure.drop end procedure.load if not procedure.exists then procedure.set_arguments (<<"one_arg">>, <<"">>) procedure.store ("update contacts set firstname = one_arg where contactid = 1") end

See Also:
Performing a database selection.
Coupling database data and Eiffel objects.