Passing Array to Stored Procedure with PHP

KungFuPanda · #1 (**permalink**) 07-29-09, 19:32

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?

KungFuPanda · #2 (**permalink**) 07-30-09, 07:22

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($stmt, 1, "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.

KungFuPanda · #3 (**permalink**) 07-30-09, 11:14

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

KungFuPanda · #4 (**permalink**) 07-31-09, 09:36

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech