CS 328 - Week 8 Lecture 1 - 2016-03-07

*   more on using OCI from PHP (to work with an
    Oracle DBMS)

*   NOTE that when you run:

    oci_execute($stmt, OCI_DEFAULT);

    ...OCI_DEFAULT is (amongst other things) saying
       you DON'T want auto-commits,
       you want to explicitly commit (or rollback)
       as desired;

    here's how you do that in OCI:

    oci_commit($conn);

    *   [after class]: and yes, there's an
      
        oci_rollback($conn);

	...to explicitly request a SQL 

	rollback;

	...command, also

*   Now for an *Oracle* "thing" that can be used in conjunction
    with the application tier for a LITTLE more protection
    against SQL injection:

    bind variables!

*   this includes our first use of bind variables in Oracle
    via PHP!

    ...those variables that start with a : and serve as
    a place-holder for a value to come;

    *   can be included in an Oracle statement string;
           
    *   thus, they can be in the string used in oci_parse function

    *   then, you BIND each to a name using function
            oci_bind_by_name 
        ...and then oci_execute to execute that statement
	   with those values of the bind variables

    *   benefits?
        *   can help fight SQL injection -- you can ONLY
            "fill in" that value, adding more won't look
    	     like an additional SQL command;

        *   when they are bound, they are given the
            appropriate quoting -- convenient!

        *   if you do the same statement with just different
            values for the bind variables, rebinding and
    	    re-executing can be more efficient;

            (rebinding and re-executing takes significantly
	    less time that re-setting up a new statement
	    each time)

*   let's demo use of oci_commit -- and intro Oracle bind variables --
    in a form-and-PHP to insert into dept

    *   see insert-dept.php

==========
*   CONSIDER PL/SQL stored procedures and stored functions:

    for a stored procedure such as new_dept:

    $new_dept_call = 'begin new_dept(:new_dept_name,
                                     :new_dept_loc); end;';

    for a stored function such as how_many:

    $how_many_call = 'begin :numcopies := how_many(:title); end;';

    ...NOTE you have to SET UP output values' bind variables
    a little differently (need a 4th argument, specifying its
    maximum size):

    oci_bind_by_name($how_many_stmt, ":numcopies", $numcopies, 4);

    *   we'll demo this on Wednesday;