#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2000
    Location
    Antwerp, Belgium
    Posts
    2
    Rep Power
    0
    Hi there,

    I've done a bit of PHP coding using OCI-functions, but I've never quite been able to implement OCIFetchStatement() properly, which imo should be a lot faster than keep fetching statements with OCIFetch(), as all the operations are then done in memory instead of fetching them all the time from my Oracle server.

    Could anybody tell me how i could do the below with an OCIFetchStatement() instead of repetetive OCIFetch() functions? Thanks a million

    <?

    $query = "select bla1, bla2, bla3, bla4 from foo1, foo2 where <snip>"

    $conn = OCILogon("username", "password", "platform");
    $stmt = OCIParse($conn, $query);

    while (OCIFetch($stmt)) {

    $bla1 = OCIResult($stmt, "BLA1");
    $bla2 = OCIResult($stmt, "BLA2");
    $bla3 = OCIResult($stmt, "BLA3");
    $bla4 = OCIResult($stmt, "BLA4");

    printf("<tr><td>Date: $bla1 Time: $bla2 Name: $bla3 Address: $bla4</td></tr>n");

    }

    OCILogoff($conn);

    ?>

    Thanks :-)
  2. #2
  3. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2000
    Posts
    0
    Rep Power
    0
    Ok, this sucks, because if you have concurrent users in your site, asking for the same thing, unless the query string is the same, Oracle creates a new result set in memory each time....
    For example:
    Select sysdate from dual
    SELECT sysdate FROM dual
    select sysdate from dual

    Will produce a new result set in memory for each one of this queries....

    I strongly recommend to create a function that standarizes query strings, and define a query creation standard. If the query is the same, then, based on stats and some other things, oracle may store de result set in memory once. That's why is better to use cursors....
    Ok, after all of this blah, I give you what you want:

    function fetch ($query) {
    $cols = OCINumCols ($query);
    if ($cols) {
    $inc = 0;
    while (OCIFetchInto ($query, $arr)) {
    reset ($arr);
    for ($i = 0; $i < $cols; $i++)
    $Mat[$inc][$i] = $arr [$i];
    $inc++;
    }
    }
    return ($Mat);
    }

    $query = "select bla1, bla2, bla3, bla4 from foo1, foo2 where <snip>"
    $conn = OCILogon("username", "password", "platform");
    $stmt = OCIParse($conn, $query);
    $result_set=OCIExecute (&$stmt);


    $query_result = fetch ($result_set);

    $query_result[0][1]; // you can reference the result like a bi-dimensional array

    If my memory didn't play with me, I wrote the correct solution....it may vary a little, because I can't remember well OCI functions and their return values right now....


    Good luck!


    Conogol
    musician@seductive.com


    ------------------
    /-----------------
    |Information=power|
    -----------------/

    [This message has been edited by Conogol (edited October 05, 2000).]
  4. #3
  5. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2000
    Posts
    3
    Rep Power
    0
    Try the OCIFetchStatement. It's documented at http://www.php.net/manual/html/funct...statement.html . It has an example of pulling down both the column names returned and the data.

    ------------------
    Ron Woods/GAI
    Sr. Software Engineer
    1777 N.E. Loop 410
    Suite 600
    San Antonio, TX 78217
    Tel: 210-820-2615
    Fax: 210-832-0487
    http://www.gai-inc.com

IMN logo majestic logo threadwatch logo seochat tools logo