parameter issues in DBD:Oracle

[email protected] (Bruce Johnson)
Newsgroups perl.dbi.users
Message-ID <[email protected]>
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
lmpx.com only provides a reader for public news (NNTP) servers. It is not affiliated with the servers or forums shown here and is not responsible for the content of articles, which is written by their respective authors.