Re: parameter issues in DBD:Oracle

You cannot pack a comma separated list into a single ?, because they
will be wrapped together in one set of quotes.
Use multiple ? in your 'in()' clause, and send each item individually
into execute().

allan

On Fri, Mar 26, 2010 at 2:01 PM, Bruce Johnson
<[email protected]> wrote:
> I'm trying the following:
>
> $sqcollision = "select reserved_id, reserver, purpose, to_char(starttime,
> 'HH:MI'), to_char(stoptime, 'HH:MI') from reservedroomtest where roomid in
> (?) and ((to_date(?, 'MM/DD/YYYY HH24:MM') <= starttime and to_date(?,
> 'MM/DD/YYYY HH24:MM') >= starttime) or (to_date(?, 'MM/DD/YYYY HH24:MM') >=
> starttime and to_date(?, 'MM/DD/YYYY HH24:MM') < stoptime))";
>
> $csr2= $lda->prepare($sqcollision);
>
> $csr2->execute($ridlist, $rstart, $rstop, $rstart, $rstart);
>                        if ( $ora_errstr ) {
>                                print "params sent $ridlist; $rstart; $rstop;
> $rstart; $rstart <p> $ora_errstr\n";
>                                exit;
>                        }
>
> Which results in punting with the following error:
>
> params sent 105, 106, 110; 03/23/2010 19:00; 03/23/2010 20:00; 03/23/2010
> 19:00; 03/23/2010 19:00
>
> ORA-01722: invalid number (DBD ERROR: error possibly near <*> indicator at
> char 135 in 'select reserved_id, reserver, purpose, to_char(starttime,
> 'HH:MI'), to_char(stoptime, 'HH:MI') from reservedroomtest where roomid in
> (:<*>p1) and ((to_date(:p2, 'MM/DD/YYYY HH24:MM') <= starttime and
> to_date(:p3, 'MM/DD/YYYY HH24:MM') >= starttime) or (to_date(:p4,
> 'MM/DD/YYYY HH24:MM') >= starttime and to_date(:p5, 'MM/DD/YYYY HH24:MM') <
> stoptime))')
>
> Does the DBD enclose all parameters in ''s?
>
> Am I actually trying to execute:
>
> select reserved_id, reserver, purpose, to_char(starttime, 'HH:MI'),
> to_char(stoptime, 'HH:MI') from reservedroomtest where roomid in ('105, 106,
> 110') and ((to_date('03/23/2010 19:00', 'MM/DD/YYYY HH24:MM') <= starttime
> and to_date('03/23/2010 20:00', 'MM/DD/YYYY HH24:MM') >= starttime) or
> (to_date('03/23/2010 19:00', 'MM/DD/YYYY HH24:MM') >= starttime and
> to_date('03/23/2010 19:00', 'MM/DD/YYYY HH24:MM') < stoptime))
>
> --
> Bruce Johnson
> University of Arizona
> College of Pharmacy
> Information Technology Group
>
> Institutions do not have opinions, merely customs
>
>
>



-- 
"The truth is an offense, but not a sin"