Re: Question about PDO stored procedure
| From: | Dan Scott | Date: | Sat, 19 Nov 2005 13:36:47 +0000 |
| Subject: | Re: Question about PDO stored procedure | ||
| References: | 1 | Groups: | php.pecl.dev |
| Request: | Send a blank email to pecl-dev+get-3237@lists.php.net to get a copy of this message | ||
Finally got the time to test this out.
Using PHP-5.1.0RC6 on Linux, with DB2 V8 FP10 on Linux. PHP is
compiled --with-pdo-odbc=ibm-db2. I still need to reproduce this on a
Windows system using the precompiled version of PDO / PDO_ODBC grabbed
from pecl4win.php.net. If that works, then it sounds like there may be
a problem with your ODBC driver.
Here's what I did:
1. Create a stored procedure that accepts an IN, INOUT, and OUT
parameter (source below).
2. Bind the parameters as described in the PDOStatement::bindParam
documentation:
try {
$conn = new PDO("odbc:$database", $user, $password);
$sql = 'CALL match_animal(?, ?, ?)';
$stmt = $conn->prepare($sql);
$name = "Peaches";
$second_name = "Rickety Ride";
$stmt->bindParam(1, $name);
$stmt->bindParam(2, $second_name,
PDO::PARAM_STR|PDO::PARAM_INPUT_OUTPUT, 128);
$stmt->bindParam(3, $weight, PDO::PARAM_STR, 128);
print "Values of bound parameters _before_ CALL:\n";
print " 1: {$name} 2: {$second_name} 3: {$weight}\n\n";
if ($stmt->execute()) {
print "Values of bound parameters _after_ CALL:\n";
print " 1: {$name} 2: {$second_name} 3: {$weight}\n\n";
print "Results:\n";
while ($row = $stmt->fetch()) {
print " {$row[0]}, {$row[1]}, {$row[2]}\n";
}
}
}
3. Output is as expected:
Values of bound parameters _before_ CALL:
1: Peaches 2: Rickety Ride 3:
Values of bound parameters _after_ CALL:
1: Peaches 2: TRUE 3: 1.23000000000000E+001
Results:
Peaches , ,
Pook , ,
Rickety Ride , ,
Smarty , ,
Sweater , ,
(Source for stored procedure):
CREATE PROCEDURE match_animal(IN first_name VARCHAR(128), INOUT
second_name VARCHAR(128), OUT animal_weight DOUBLE)
DYNAMIC RESULT SETS 1
MODIFIES SQL DATA
LANGUAGE SQL
NO EXTERNAL ACTION
BEGIN
DECLARE match_name INT DEFAULT 0;
DECLARE c1 CURSOR FOR
SELECT COUNT(*) FROM animals
WHERE name IN (second_name);
DECLARE c2 CURSOR FOR
SELECT SUM(weight) FROM animals
WHERE name in (first_name, second_name);
DECLARE c3 CURSOR WITH RETURN FOR
SELECT name FROM animals
WHERE name BETWEEN first_name AND second_name
ORDER BY name;
OPEN c1;
FETCH c1 INTO match_name;
IF (match_name > 0)
THEN SET second_name = 'TRUE';
END IF;
CLOSE c1;
OPEN c2;
FETCH c2 INTO animal_weight;
CLOSE c2;
OPEN c3;
END
Dan
On 11/14/05, Nell Gawor <nell_gawor@hotmail.com> wrote:
> Hi,
>
> I'm not exactly sure which PDO driver I am using. I download it and the ODBC support from:
> http://pecl4win.php.net/ext.php/php_pdo.dll and I grabbed the
> one that said php-5.0.5 (5_0).
>
> I am connecting to a DB2 database running on OS/400 (iSeries) using ODBC and the iSeries Access
> ODBC driver.
>
> Thanks,
> Nell
> *
> List: pecl-dev
> <http://marc.theaimsgroup.com/?l=pecl-dev&r=1&w=2>
> Subject: Re: [PECL-DEV] Question about PDO stored procedure
> <http://marc.theaimsgroup.com/?t=113198946200003&r=1&w=2>
> From: Dan Scott <denials () gmail ! com>
> <http://marc.theaimsgroup.com/?a=109814068800002&r=1&w=2>
> Date: 2005-11-14 18:42:26
> <http://marc.theaimsgroup.com/?l=pecl-dev&r=1&b=200511&w=2>
> Message-ID: fbb0d11d0511141042jd1778cbhc9498c13aab3522b () mail ! gmail ! com
> <http://marc.theaimsgroup.com/?i=fbb0d11d0511141042jd1778cbhc9498c13aab3522b%20%28%29%20mail%20%21%20gmail%20%21%20com>
> [Download message RAW
> <http://marc.theaimsgroup.com/?l=pecl-dev&m=113199389905067&q=rawÇ€¤ò
> éÓ»u¤àŠ>]*
>
> Hi Neil - -can you tell us which PDO driver? And which database? and
> which version you're using?
>
> Dan
>
> On 11/14/05, Nell Gawor <nell_gawor@hotmail.com> wrote:
> > Hi,
> >
> > I hope this is the right list to post this too. I am trying to use PDO
> > w/PHP 5.0.5 on Windows. I am calling a stored procedure that has one
> > input parameter and one output parameter.
> > The problem is that if I don't set the value of the return_value to some
> > string before binding the parameter, I get:
> >
> > [Mon Nov 14 11:46:17 2005] [notice] Parent: child process exited with
> > status 3221225477 -- Restarting.
> >
> > So what I end up having to do is:
> >
> > $stmt = $dbh->prepare("CALL TESTPHP.PROCTEST(?,?)");
> > $cust_key = 'A000000001';
> > $stmt->bindParam(1, $cust_key, PDO::PARAM_STR);
> >
> > $return_value = 'none';
> > $stmt->bindParam(2, $return_value, PDO::PARAM_STR, 256);
> > $stmt->execute();
> >
> > And this works. But why do I need to do this?
> >
> > Thanks,
> > Nell
> >
> > --
> > PECL development discussion Mailing List (http://pecl.php.net/)
> > To unsubscribe, visit: http://www.php.net/unsub.php
> >
> >
>
> --
> PECL development discussion Mailing List (http://pecl.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
> --
> PECL development discussion Mailing List (http://pecl.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>