updating multiple rows.

[Nitin]

Hi All,

Need help. I am creating a web page, which fetches data from an oracle
database and then gives user an option to change the value from a
selection list only for one column and then update the oracle database
accordingly. I am able to fetch data from the oracle database without
any problem. However, I have no clue as to how to proceed with
updation of the database once user has done the modification. Further
more, my script is not able to run sub update, when i press update
button it reruns the whole script from the top and does to tries to
run the sub part. It does not return any error message as well.

I would really appreciate a speedy reply.

#!/sbcimp/run/pd/perl/prod/bin/perl -w
use CGI qw/:all/;
use CGI::Carp qw/fatalsToBrowser/;
use DBI;
$wobj = new CGI;
$path_info = $wobj->path_info;
$script_name = $wobj->script_name;
&update if $path_info=~/update/;
print $wobj->header();
print $wobj->start_html();
print $wobj->startform(-action=>"$script_name/update",
-name=>'update');
DBI->trace(1);
print $wobj->hr;
$dbh = DBI->connect('DBI:Oracle:foo', 'scott', 'tiger') || die
"Database connection not possible: $DBI::errstr";
$alter = "ALTER SESSION SET TIME_ZONE='Asia/Singapore'";
$sql = "SELECT WORK_NUMBER, WORK_TYPE, WORK_DESCRIPTION,
WORK_REQUESTOR, TO_CHAR(WORK_STARTDATETIME, 'Day DD-MM-YYYY HH24:MI
TZR'), TO_CHAR(WORK_ENDDATETIME, 'Day DD-MM-YYYY HH24:MI TZR'),
TO_CHAR(SNG_STARTDATETIME, 'Day DD-MM-YYYY HH24:MI'),
TO_CHAR(SNG_ENDDATETIME, 'Day DD-MM-YYYY HH24:MI'), WORK_STATUS FROM
WORK WHERE WORK_STATUS = 'Unapproved' ORDER BY SNG_STARTDATETIME";
$sth = $dbh->prepare ($alter) || die '$DBI::errstr';
$sth->execute() || die '$DBI::errstr';
$sth = $dbh->prepare( $sql ) || die '$DBI::errstr';
$sth->execute() || die '$DBI::errstr';
$sth->bind_columns(undef,\$work_number,\$work_type,\$work_description,\$work_requestor,\$work_startdatetime,\$work_enddatetime,\$sng_startdatetime,\$sng_enddatetime,\$work_status)
|| die '$DBI::errstr';
print '<table>';
while ( $sth->fetch() ) {
%work_hash = ($work_number => "$work_status");
print "<tr><td align=Left bgcolor=#ffe0cc><strong>Work
Type</strong></td><td align=left>$work_type</td></tr>";
print '<tr><td align=Left
bgcolor=#ffe0cc><strong>Number</strong></td>';
print "<tr><td align=Left
bgcolor=#ffe0cc><strong>Requestor</strong></td><td
align=Left>$work_requestor</TD></TR>";
print "<tr><td align=Left bgcolor=#ffe0cc><strong>Time
Window</strong></td><td align=Left>$work_startdatetime -
$work_enddatetime</TD></TR>";
print "<TR><td align=Left bgcolor=#ffe0cc><strong>Time Window in SGT
</strong></td><td align=Left>$sng_startdatetime -
$sng_enddatetime</TD></TR>";
print "<TR><td align=Left
bgcolor=#ffe0cc><strong>Description</strong></td><td
align=Left>$work_description</TD></TR>";
print "<TR><td align=Left
bgcolor=#ffe0cc><strong>Status</strong></td><td align=Left><SELECT
NAME='status' Size=1><Option>Unapproved<Option>Approved<Option>Rejected<Option
Selected>$work_status</Select></TD></TR>";
}
print "</TABLE>";
foreach (keys %work_hash) {
print $_ => $work_hash{$_};
}
$dbh->disconnect() || die '$DBI::errstr';
print $wobj->submit(-name=>'update', -value=>'update');
print $wobj->end_html;

sub update {
DBI->trace(1);
$dbh = DBI->connect('DBI:Oracle:foo', 'scott', 'tiger') || die
"Database connection not possible: $DBI::errstr";
foreach (keys %work_hash) {
$sqlupdate = "UPDATE WORK SET WORK_STATUS='$work_hash{$_}' WHERE
WORK_NUMBER=$_";
$sth = $dbh->prepare ($sqlupdate) || die 'DBI::errstr';
$sth->execute() || die 'DBI::errstr';
}
$dbh->disconnect() || die '$DBI::errstr';
}

Thanks

Nitin

[Nitin Thakur]

No replies!! Is my question too complicated or is it not clear?????

Thanks

nitin

nitin....@ubs.com (Nitin) wrote in message news:<ff21db84.04092...@posting.google.com>...