RE: [PEAR] Re: PEAR::DB and autoPrepare with dates (Oracle)

Justin- First things first: You rule. Now with that out of the way, I'll do the update manually. Thanks, Shawn -----Original Message----- From: Justin Patrin [mailto:papercrane@reversefold.com] Sent: Tuesday, February 24, 2004 3:45 PM To: pear-general@lists.php.net Subject: Re: [PEAR] Re: PEAR::DB and autoPrepare with dates (Oracle) I just checked the code and here's what I found: autoPrepare() assumes that all data passed in is a scalar, and therefore needs to be quoted. The quoteSmart() function then puts quotes around it and escapes the ' in there, causung the whole TO_DATE() call to be a string. I suggest you use a manually created SQL statement in this case as autoPrepare() doesn't have support for not-quoting. Perhaps it could be a new option... Shawn Coomey wrote: > Thanks Justin- > Yes, I sure did. The dates are copasetic. I have a feeling that it has to do > with the binding PEAR::DB does in the background in autoPrepare() and how it > relates to Oracle date formatting. Not sure though > > Shawn > > -----Original Message----- > From: Justin Patrin [mailto:papercrane@reversefold.com] > Sent: Tuesday, February 24, 2004 3:24 PM > To: pear-general@lists.php.net > Subject: Re: [PEAR] Re: PEAR::DB and autoPrepare with dates (Oracle) > > I assume you've echoes this to make sure that the strings are as > expected? A null return from date() or strtotime() could cause this. > > Shawn Coomey wrote: > > >>Yeah, the double equal was a typo as I transposed my code into the email. I >>tried quoting and not quoting the date string, and got the same result. >> >>Any suggestions? >> >>Thanks >> >>Shawn >> >>-----Original Message----- >>From: Justin Patrin [mailto:papercrane@reversefold.com] >>Sent: Tuesday, February 24, 2004 1:11 PM >>To: pear-general@lists.php.net >>Subject: [PEAR] Re: PEAR::DB and autoPrepare with dates (Oracle) >> >>Shawn Coomey wrote: >> >> >> >>>Hi folks- >>>I am trying to perform an autoPrepare() update on a date column in Oracle >> >>9i. >> >> >>>Seems to be giving me some trouble with the date formatting. Here; an >> >>example >> >> >>>of what I am trying to do: >>> >>> <?php >>> >>> $start = = "TO_DATE(" . date("m/d/Y g:i A", strtotime($start_time)) >>> . ", >>>'MM/DD/YYYY HH:MI AM')"; >>> $end = = "TO_DATE(" . date("m/d/Y g:i A", strtotime($end_time)) .. >>> ", >>>'MM/DD/YYYY HH:MI AM')"; >>> >>> $table_fields = array('event_start', 'event_end'); >>> $table_values = array($start', $end'); >>> $table_name = 'calendar_events'; >>> >>> $sth = $dbh->autoPrepare($table_name, $table_fields, DB_AUTOQUERY_UPDATE, >>>'event_id = ' . $event_id'); >>> $dbh->execute($sth, $table_values); >>> >>> ?> >>> >>>But this throws the following Oracle error: >>>[nativecode=ORA-01858: a non-numeric character was found where a numeric >> >>was >> >> >>>expected] >>> >>>How do I format the date for autoPrepare to work? >>> >>> >>>TIA- >>> >>>Shawn >> >> >>Looks like you're not quoting your date string (also you had two = signs >>when setting $start and $end): >>$start = "TO_DATE('" . date("m/d/Y g:i A", strtotime($start_time)) >>"', >> 'MM/DD/YYYY HH:MI AM')"; >> >> >>-- >>paperCrane <Justin Patrin> >> > > -- -- paperCrane <Justin Patrin> -- PEAR General Mailing List (http://pear.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php