- skip to content

# Database Abstraction Layer

### User Tools

- Login

### Site Tools

- Recent changes
- Media Manager
- Sitemap

### Sidebar

##### Resources

- Reference Manual
- User Guide
- The Data Dictionary
- ADOdb-xmlschema
- ADOdb Active Record
- Performance Monitoring
- Date/Time Library
- Session Management Library
- Unsupported Addons
- Coding Standards

##### Supported Databases

- Access
- ADO
- CSV
- Firebird
- Frontbase
- IBM DB2
- Informix
- Interbase
- LDAP
- Microsoft SQL Server
- MySQL
- Netezza
- ODBC
- ODBTP
- Oracle
- PDO
- Postgresql
- SAP ADS
- SAP ASE
- SAP Maxdb
- SAP Sqlanywhere
- Sqlite
- Sybase
- Plain Text
- Visual Foxpro

##### Other

- Feature Comparison Matrix
- Connection Matrix
- Include File Guide

v5:userguide:stored_procedure

# Stored Procedure Example

## Description

This example shows the creation of a basic stored procedure and accessing it using ADOdb

## The Procedure

This procedure uses the IBM DB2 SAMPLE database. It performs the following functions:

1. Accepts 2 input parameters, a 6 character keyword and 20 character name
2. Finds the highest existing account number in the table
3. Adds 10 to that number
4. Creates a new record, using the new account number and the input values
5. Makes the new account number available to the PHP procedure calling it

### Procedure Code

    CREATE OR REPLACE PROCEDURE PROC_SAMPLE2(IN PARAMETER1 VARCHAR(6),
                                             IN PARAMETER2 VARCHAR(20),
                                             OUT PARAMETER3 INT)
    BEGIN
    DECLARE v_PARAM1 VARCHAR(6);
    DECLARE v_PARAM2 VARCHAR(20);
    DECLARE v_INT INTEGER;
    SET v_PARAM1=PARAMETER1;
    SET v_PARAM2=PARAMETER2;
    SELECT MAX(ACTNO) INTO v_INT FROM act;
    INSERT INTO act (ACTNO,ACTKWD,ACTDESC) SELECT v_INT+10,v_PARAM1,v_PARAM2 FROM SYSIBM.SYSDUMMY1;
    SET PARAMETER3=v_INT+10;
    END

### Creation

The procedure can be created by simply creating a PHP string using the above code, then executing it using `$db→execute($string)`.

## Accessing The Procedure

### Step 1: Preparation

The first step is to access the procedure using the prepareSp() method. This method access the procedure and returns a handle, that we will use later. If the procedure name provided is not valid, then the method returns false.

    $procedure = $db->prepareSp('PROC_SAMPLE2');
    if (!$procedure)
        die ('Invalid or inaccessible stored procedure name');

### Step 2: Setting Parameters

Next, the parameters must be set. Stored Procedure Parameters may be either Input,Output or Input-Output parameters. ADOdb provides access to input-output parameters via the outParameter method. The parameter names must match the names of the input/output parameters defined in the stored procedure.

#### Input Parameters

Because both the parameter names and values are passed by reference, they must be initialized as strings before passing to the methods

    /*
     * Correct
     */
    $parameter1Name = 'PARAMETER1';
    $parameter1value = 'TESTVAL';

    $parameter2Name = 'PARAMETER2';
    $parameter2value = 'ACCOUNT NAME';

    $ok = $db->inParameter($procedure,$parameter1Name,$parameter1Value);
    $ok = $db->inParameter($procedure,$parameter2Name,$parameter2Value);

    /*
     * Incorrect
     */
    $ok = $db->inParameter($procedure,'PARAMETER1','TESTVAL');
    $ok = $db->inParameter($procedure,'PARAMETER2','ACCOUNT NAME');

#### Output Parameters

Output and Input-Output Parameters are passed by reference, the variables used to hold them must be created and initialized before the stored procedure is executed.

    $parameter3Name  = 'PARAMETER3';
    $parameter3Value = 0;

    $ok = $db->outParameter($procedure,$parameter3Name,$parameter3Value);

### Executing The Stored Procedure

The stored procedure is executed by passing the handle of the procedure to the execute statement.

    $result = $db->execute($procedure);

    print "parameter3 IS NOW $parameter3Value";

The values of any output parameters are set by the execute statement, and may be accessed without further action.

### Retrieving Recordsets

If the stored procedure was one that returned a recordset, that can be accessed via the result of the execution, in the normal way.

v5/userguide/stored_procedure.txt · Last modified: 2016/01/12 17:31 by mnewnham

### Page Tools

- Show pagesource
- Old revisions
- Backlinks
- Rename Page
- Back to top