Results 1 to 4 of 4
  1. #1
    Join Date
    May 2009
    Location
    Canada
    Posts
    4

    Unanswered: Passing Array to Stored Procedure with PHP

    Hi Guys,

    i created a stored Procedure for db2 9.7 which has an array input parameter type.
    Code:
    CREATE TYPE keywords VARCHAR(30) ARRAY[200];
    CREATE PROCEDURE search(IN keywordsIn keywords)
    ...
    DYNAMIC RESULT SETS 1
    ...
    DECLARE x CURSOR FOR
     SELECT * FROM UNNEST(keywordsIn) AS k(title);
    OPEN x;
    ...
    I call the Procedure within a php file as a prepared statement and tried to bind a test array
    PHP Code:
    $testArray = array("a","b","c"
    either explicitly via
    PHP Code:
    db2_bind_param 
    and
    PHP Code:
    db2_execute($stmt,array($testArray)) 
    .

    The Problem ist: PHP doesn't seem to correctly hand over the parameter to the SP as an Array. When i print the result via db2_fetch_rows() to the screen, it says:
    HTML Code:
    Array
    , just as if i would have called
    PHP Code:
    echo $testArray 
    .

    Does anyone know how to achieve passing an array to a SP via PHP? Or ist it just not possible?

  2. #2
    Join Date
    May 2009
    Location
    Canada
    Posts
    4
    It seems to be a little confusing what i want (as no one has already made an answer ) so im Posting the full test code i edited to make it clearer:

    Code:
    CREATE PROCEDURE suche (IN keywordsIn DEPP.keywords)
    	LANGUAGE SQL
    	DYNAMIC RESULT SETS 1
    	READS SQL DATA
    	NOT DETERMINISTIC
    	NO EXTERNAL ACTION
    ------------------------------------------------------------------------
    -- function body
    ------------------------------------------------------------------------
    P1: BEGIN
    
    	-- declare cursor
    	DECLARE resultCursor CURSOR WITH RETURN TO CALLER FOR
    		SELECT COUNT(*) AS titel 
    		FROM UNNEST(keywordsIn) AS k(bezeichnung); 
    	
    	OPEN resultCursor;
    	
    END P1
    that's the Procedure. Now the PHP Code:
    PHP Code:
        $dbCon db2_connect("DEPP","username","topSecret");

        
    $testArray = array("stift","bier","rose");
        
    $statement "CALL DEPP.suche(?)";

        if(!
    $dbCon){
            echo 
    "connection attempt failed";
        }
        else{
            
    $stmt db2_prepare($dbCon,$statement);
            if(!
    $stmt){
                echo 
    "prepare failed";
            }
            else{
                
    db2_bind_param($stmt1"testArray"DB2_PARAM_IN);
                if(!
    db2_execute($stmt)){
                    echo 
    "execute failed";
                }
                else{
                    while(
    db2_fetch_row($stmt)){
                        echo 
    db2_result($stmt,0)."<br>";
                    }
                }
            }
        }

        
    db2_close($dbCon); 
    Hope this does raise some answers

    Thank u in advance.

  3. #3
    Join Date
    May 2009
    Location
    Canada
    Posts
    4
    I'm going crazy with this stuff. I now figured out how to pass an array on the CLP command line to my SP:

    Code:
     db2 "CALL DEPP.suche(ARRAY['rose','bier','stift'])"
    This works perfectly. When i try to get this working via php, the prepare of this statement works, but the execute says:

    HTML Code:
    Warning: db2_execute() [function.db2-execute]: Statement Execute Failed in...
    The essential php-Code for this is:
    PHP Code:
    $dbCon db2_connect("DEPP","user","topSecret");
    $statement "CALL DEPP.suche(ARRAY['rose','stift','bier'])";
    $stmt db2_prepare($dbCon,$statement);
    db2_execute($stmt); 
    Is no one farmiliar with this? Someone must have used the array feature for DB2 UDB with php
    Last edited by KungFuPanda; 07-30-09 at 13:58.

  4. #4
    Join Date
    May 2009
    Location
    Canada
    Posts
    4
    After hours and hours of work on this i strongly believe that it is just not possible to manage this with php.
    I've read about array passing in descriptions for JDBC in the DB2 Information Center and also for achieving this with C/C++, but no PHP. So I'll give up on this and pass the parameters as a string which i will cut into array pieces within the SP.
    Last edited by KungFuPanda; 08-02-09 at 18:22.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •