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"
