Ilya,
Thank you for that. My INSERT is now working.
Many thanks,
Regards,
Stacy.
"Sterin, Ilya" wrote:
>
> No, the variable can't contain the whole conversion string, since it will be
> bound as a value. Rather do this...
>
> $dbh->do("INSERT INTO $fault_db VALUES (?,?,?,?,?,to_date(?,'DD-MON-YYYY
> HH24:MI'),to_date(?,'DD-MON-YYYY HH24:MI'),?,?,?,?,?) ",
> undef,
> $fault_no,
> $reported_by,
> $project_no,
> undef,
> $date_occurred,
> $date_reported,
> $time_lost,
> $one_line_summary,
> $issue,
> 'Y',
> undef,
> 'NOT YET ALLOCATED'
> );
>
> Ilya
>
> > -----Original Message-----
> > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On
> > Behalf Of Stacy Mader
> > Sent: Friday, November 02, 2001 9:31 PM
> > To: Jeff Zucker
> > Cc: dbi-users
> > Subject: Re: Quoting with placeholders
> >
> >
> > Thanks for that Jeff.
> >
> > Now that I have:
> >
> > $dbh->do("INSERT INTO $fault_db VALUES (?,?,?,?,?,to_date(?,'DD-MON-YYYY
> HH24:MI'),to_date(?,'DD-MON-YYYY HH24:MI'),?,?,?,?,?) ",
> > undef,
> > $fault_no,
> > $reported_by,
> > $project_no,
> > undef,
> > $date_occurred,
> > $date_reported,
> > $time_lost,
> > $one_line_summary,
> > $issue,
> > 'Y',
> > undef,
> > 'NOT YET ALLOCATED'
> > );
> >
> >
> > I get the error:
> >
> > DBD::Oracle::db do failed: ORA-01858: a non-numeric character was found
> > where a numeric was expected (DBD: oexec error) at ff_report.cgi line
> > 450.
> >
> > From my Oracle books, the error code states:
> >
> > "The input data to be converted using a date format model was incorrect;
> > the formal model expected a number but found a non-numeric character."
> >
> > My variables for date_occurred and date_reported are:
> >
> > $date_occurred = "to_date(\'$odate_str $otime_str\',\'DD-MON-YYYY
> > HH24:MI\')";
> > $date_reported = "to_date(\'$rdate_str $rtime_str\',\'DD-MON-YYYY
> > HH24:MI\')";
> >
> > Does this seem right?
> >
> >
> > Stacy.
> >
> >
> >
> >
> > Jeff Zucker wrote:
> > >
> > > Stacy Mader wrote:
> > > >
> > > > $allocated_to = $dbh->quote('NULL');
> > >
> > > That's wrong for two reasons: don't use quote() on something that
> > > already has quotes around it unless you want the literal quotes in the
> > > string; and if you mean an actual SQL NULL, it should not be quoted by
> > > either method.
> > >
> > > > Can the $dbh->do quote my values automatically?
> > >
> > > Yes, with placeholders:
> > >
> > > $dbh->do(
> > > " INSERT INTO $fault_db VALUES (?,?,?,?,?,?,?,?) ", undef,
> > > $reported_by, $project, undef, $one_line_summary, $issue,
> > > 'Y', undef, 'NOT YET ALLOCATED'
> > > );
> > >
> > > Note the use of the first undef which is a stand-in for \%attr which you
> > > don't need. The following undefs (with no quotes around them) are for
> > > SQL NULLs.
> > >
> > > --
> > > Jeff
