| Online Eiffel Documentation

EiffelStudio

# 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 toprecompile 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 abstractexpressions. 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 tobind 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.