Resources
- Full support
- Partial support
Resources
This is an old revision of the document!
This example shows the creation of a basic stored procedure and accessing it using ADOdb
This procedure uses the IBM DB2 SAMPLE database. It performs the following functions:
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
The procedure can be created by simply creating a PHP string using the above code, then executing it
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');
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.
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 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);
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.
If the stored procedure was one that returned a recordset, that can be accessed via the result of the execution, in the normal way.