This is one of those cases where placeholders would save you a lot of grief.
The value inserted into your SQL for $host is not a valid column name. Even
if you quote it, something like "Joe's_PC" would kill it all over again.
Run 'perldoc DBI' to read the fine manual. The sections on placeholders,
bind_param(), and execute() would be particularly interesting in this case.
See my suggested code below.
--
Mac :})
** I normally forward private database questions to the DBI mail lists. **
Give a hobbit a fish and he'll eat fish for a day.
Give a hobbit a ring and he'll eat fish for an age.
----- Original Message -----
From: "Xiaoxia Dong" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Thursday, March 15, 2001 6:53 AM
Subject: [Fwd: [Fwd: how to check to a database to see if i need update or
insert]]
> > > I am new to perl DBI. I am trying to do something that like machine
> > > uptime, i read all those in from a set of
> > > file. Everytime, if i found machine done, i need to insert a new
record,
> > > otherwise, i just need to update
> > > the existing record in the oracle database. How can i do it?
> > >
> > > Suppose i know i just need to update, i have the following sql
> > > statement:
> > >
> > > $sql = "UPDATE uptime SET up_time=$uphours
> > > WHERE hostname = $host and
> > > startdate between
> > > (TO_DATE('$yy:$month:$sday:$shour:$sminute:00',
> > > 'YYYY:MM:DD:HH24:MI:SS') and
> > > TO_DATE('$yy:$month:$sday:$shour:$sminute:59',
> > > 'YYYY:MM:DD:HH24:MI:SS')
> > > )";
$dbh -> {RaiseError} = 1;
my $fmt = 'YYYY:MM:DD:HH24:MI:SS';
my $ymdhm = "$yy:$month:$sday:$shour:$sminute";
my $sth = $dbh -> prepare(
"UPDATE uptime SET up_time = ?
WHERE hostname = ? and
startdate between (TO_DATE( ?,'$fmt') and TO_DATE(
?,'$fmt')" );
$sth -> execute( $uphours, $host, "$ymdhm:00", "$ymdhm:59" );
> > > when i try to execute this statement, it gave following messages:
> > > DBD::Oracle::st execute failed: ORA-00904: invalid column name (DBD
> > > ERROR: OCIStmtExecute) at upora.pl line 135.
> > > UPDATE uptime SET up_time=212.633333333333
> > > WHERE hostname = twister and
> > > startdate between
> > > TO_DATE('2001:3:8:5:34:00',
> > > 'YYYY:MM:DD:HH24:MI:SS') and
> > > TO_DATE('2001:3:8:5:34:59',
> > > 'YYYY:MM:DD:HH24:MI:SS')
> > >
> > > ORA-00904: invalid column name (DBD ERROR: OCIStmtExecute)after get
into
> > > uptimeOracle
> > > how can i correct this one?
