> Not sure why you are doing "eval"... plus I've rarely had to bind with > the "type" for Oracle...
I just pulled this method from a web site. As I understand it, this is done to facilitate the error checking. I asumed this was a best method for this operation. http://informatics.umdnj.edu/bioinformatics/courses/5004/Notes/DBI%20Examples.htm Craig ---------------------------------------- > Subject: RE: datatype error > Date: Fri, 27 Jul 2007 08:41:07 -0400 > From: [EMAIL PROTECTED] > To: [EMAIL PROTECTED]; [email protected] > > -----Original Message----- > From: Craig Metzer [mailto:[EMAIL PROTECTED] > Sent: Thursday, July 26, 2007 6:36 PM > To: [email protected] > Subject: datatype error > > I'm getting an error trying to run an insert action due to the DATE or > DATETIME datatype. I couldn't find anything on it, and was wondering if > anyone could point me in the right direction -- Thanks > > I'm trying to run an insert ... the date value is giving me errors. > Here's the code: > > > my $sql = qq{ INSERT INTO perfdata VALUES ( ?, ?, ?, ?, ?, ? ) }; > my $sth = $dbh->prepare( $sql ); > for ( @record ) { > eval { > $sth->bind_param( 1, @$_->[0], SQL_VARCHAR ); > $sth->bind_param( 2, @$_->[1], SQL_VARCHAR ); > $sth->bind_param( 3, @$_->[2], SQL_DATETIME ); > $sth->bind_param( 4, @$_->[3], SQL_INTEGER ); > $sth->bind_param( 5, @$_->[4], SQL_CHAR ); > $sth->bind_param( 6, @$_->[5], SQL_VARCHAR ); > $sth->execute(); > $dbh->commit(); > }; > if ( $@ ) { > warn "Database error: $DBI::errstr\n"; > $dbh->rollback(); #just die if rollback is failing > } > } > $sth->finish(); > > where record is: > > push @record, [$src_swt, $dst_swt, otime(), $latency, $reachability, > $path]; > > sub otime { > my $m = (split /\s/, gmtime)[1]; > my ($seconds, $minutes, $hours, $day_of_month, $month, > $year, $wday, $yday, $n) = gmtime; > if ($hours > 12) { > $hours -= 12; > $n = 'PM'; > } else { > $n = 'AM'; > } > return sprintf("%04d-%02d-%02d %02d:%02d:%02d", > $year+1900, $month, $day_of_month, $hours, $minutes, $seconds) . " > $n"; > } > > results in: > SQL type 9 for ':p3' is not fully supported, bound as SQL_VARCHAR > instead at ./atmping.pl line 124. > DBD::Oracle::st execute failed: ORA-01861: literal does not match format > string > ... > > I also tried 24 hr time format resulting in the same error: > ... > > > Not sure why you are doing "eval"... plus I've rarely had to bind with > the "type" for Oracle... > > But to the date issue... it has been my experience that you either set > the date format for the session or add a "to_date" function to the sql > then bind the "string" data (date) and let Oracle do the work... at > least that's easier for me... > > Example: > > ... > $sth->prepare(q{select to_date(?,'Mon-yyyy') as First_of_Month from > dual}) or die"..."; > $sth->execute('Jul-2007') or die "..."; > ($bom) = $sth->fetch; > ... > Or... > > $sth->prepare(q{select add_months(to_date(?,'Mon-yyyy'),1) - 1 as > lastDay from dual}) or die"..."; > $sth->execute() or die "..."; > Foreach my $date ('Jan-2007', 'Feb-2007', ...) { > $sth->bind_param($date); > my ($eom) = $sth->fetch; > } > > Not tested... > > Hope this helps... > > jwm _________________________________________________________________ Missed the show? Watch videos of the Live Earth Concert on MSN. http://liveearth.msn.com
