CODEWITHSUNDEEP
phpdatabaseoracle-databasestored-proceduresoracle11g
WAQAS SHABIR
WAQAS SHABIR

Reputation: 23

PHP and oracle Stored procedure

i am having a bit of trouble working out how to use PHP and call an oracle stored procedure.

Running the following command directly on SQL developer gives me the results I need:

 variable O_username varchar2;
 EXECUTE LOGINADMIN('John','John', 'Attractions', :O_username);

  print O_username;

However on PHP I am having trouble getting the results I require. This is my PHP code.

include 'connection.php';


 $username = ($_POST['uname']);
    $password = ($_POST['pass']);
    $role = ($_POST['admin_role']);
$query = "EXECUTE LOGINADMIN(:bind1,:bind2, :bind3, :bind4);
";
oci_bind_by_name($stmt, ":bind1", $username);
oci_bind_by_name($stmt, ":bind2", $password);
oci_bind_by_name($stmt, ":bind3", $role);
oci_bind_by_name($stmt, ":bind4", $result, 50);

$stmt = oci_parse($conn, $query);
oci_execute($stmt);
while ($row=oci_fetch_row($stmt))

$count = oci_num_rows($stmt);
ECHO $stmt;
ECHO $result;

echo OCI_RESULT ($stmt);
if ($count > 0 && $role == 'Attractions' ) {
        echo ("<SCRIPT LANGUAGE='JavaScript'>
        window.alert('Logged Succesfully!')
        window.location.href='attractionshome.php'
        </SCRIPT>");
           $_SESSION['attractionlogin'] = $username;
        exit();

It looks like I am having some trouble getting the input out so $result can see there is a variable for this.

thank you,

Upvotes: 2

Views: 2108

Answers (3)

benja420
benja420

Reputation: 1

1 create store procedure 
create or replace PROCEDURE mysp (myrc out sys_refcursor) as
begin
     open myrc for select driver_id,login from drivers ;
     myrc:=myrc;

END mysp;

2 file php

    include 'config_conection.php';
        $conexion = oci_connect($user, $pass, $db);
        if(!$conexion){
            $e = oci_error();
            echo "error";
        }

    $driver_id=1826;


    $curs = oci_new_cursor($conexion);
    $stid = oci_parse($conexion, "begin mysp(:myrc); end;");
    oci_bind_by_name($stid, ":myrc", $curs, -1, OCI_B_CURSOR);
    oci_execute($stid);

    oci_execute($curs);  // Ejecutar el REF CURSOR como un ide de sentencia normal
    while (($row = oci_fetch_array($curs, OCI_ASSOC+OCI_RETURN_NULLS)) != false) {
        echo $row['DRIVER_ID'] . "<br />\n". $row['LOGIN'] . "<br />\n";
    }

    oci_free_statement($stid);
    oci_free_statement($curs);
    oci_close($conn)

Upvotes: 0

Louis N
Louis N

Reputation: 16

You first need to ensure that your stored procedure is correct, and it returns the expected result:

create or replace PROCEDURE loginadmin
(
v_username    IN STAFF.USERNAME%TYPE,
v_password    IN STAFF.PASSWORD%TYPE,
v_admin_role  IN ADMIN.ADMIN_ROLE%TYPE,
O_Username    OUT STAFF.USERNAME%TYPE
)
AS
  BEGIN
  SELECT STAFF.USERNAME INTO O_Username
  FROM STAFF 
  INNER JOIN ADMIN
  ON STAFF.NINO = ADMIN.NINO
  WHERE STAFF.USERNAME = v_username AND STAFF.PASSWORD = v_password and ADMIN.ADMIN_ROLE = v_admin_role;
END;
 /

Then execute it to test its result! 

variable O_Username varchar2;
execute loginadmin ('testUsername', 'testPassword', 'testRole', :O_Username);
print O_Username;

Then in your php code, make sure you include the length as @tim have indicated. (in my example is 40). Then try to execute your stored procedure with a begin!

include 'connection.php';
$username = $_POST['uname'];
$password = $_POST['pass'];
$role = $_POST['admin_role'];
$query = 'begin loginadmin (:bind1,:bind2, :bind3, :bind4); end;';

$stmt = oci_parse($conn, $query);
oci_bind_by_name($stmt, ':bind1', $username);
oci_bind_by_name($stmt, ':bind2', $password);
oci_bind_by_name($stmt, ':bind3', $role);
oci_bind_by_name($stmt, ':bind4', $result, 40);
oci_execute($stmt);

Then have your code logic to check if the result matches the username and the role is Master for example and you know the rest!

if ($result == $username && $role == 'Master' ) {
        echo ("<SCRIPT LANGUAGE='JavaScript'>
        window.alert('Logged Succesfully!')
        window.location.href='master.php'
        </SCRIPT>");
           $_SESSION['attractionlogin'] = $username;
        exit();
    }

Upvotes: 0

timclutton
timclutton

Reputation: 13004

You must bind variables before calling oci_execute:

$stmt = oci_parse($conn, $query);
oci_bind_by_name($stmt, ":bind1", $username);
oci_bind_by_name($stmt, ":bind2", $password);
oci_bind_by_name($stmt, ":bind3", $role);
oci_bind_by_name($stmt, ":bind4", $result, $maxlength, SQLT_CHR);
oci_execute($stmt);

See Example #11 Binding parameters for a PL/SQL stored procedure in the manual.

Also note that your OUT variable call (:bind4) is incorrect; the fourth parameter should be maxlength. In the code above I've included it but you must set it based on the size of data to be returned! From the manual:

You must specify maxlength when using an OUT bind so that PHP allocates enough memory to hold the returned value.

Upvotes: 1

Related Questions