• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 296
  • Last Modified:

Simple CGI (perl) form processing script

This script inserts values from an html form into a database.  The problem is that I want it to fail if the $number value already exists in the database.  Is there a simple way of achieving this? Is this even the right area to ask this question?



#!/usr/bin/perl -w
use CGI ':standard';
use CGI::Carp qw(fatalsToBrowser);
use DBI;
$ENV{"ORACLE_HOME"} = "/home/oracle/product/9.2.0";
print "Content-type: text/html\n\n";
$dbh = DBI->connect('dbi:Oracle:', q{gavin/a32182302@(DESCRIPTION=
  (ADDRESS=(PROTOCOL=TCP)(HOST= info200.infc.ulst.ac.uk)(PORT=1521))
  (CONNECT_DATA=(SID=ORCL)))}, "") or die "Bad connect";
my $name=param('EName');
my $number=param('ENumber');
my $salary=param('SalaryInfo');
my $job=param('jobType');
print "Inserting ...Ename=$name,Enumber=$number,SalaryInfo=$salary, Job=$job\n";  
my $sql =qq{insert into emp(empno,ename,sal,job) values(?,?,?,?)};
my $sth=$dbh->prepare($sql);
$sth->execute($number,$name,$salary,$job) or die $DBI::errstr;
print "</br>Insert Complete\n";
$dbh->disconnect;
0
henrycrinkle34
Asked:
henrycrinkle34
1 Solution
 
ItatsumakiCommented:
# Sure, plenty of ways to do this.  This is the easiest
# programmatically, but does leave your database vulnerable
# to race conditions.  If you are using oracle, why not
# just enable a primary or unique key on the DB table?

#!/usr/bin/perl -w
use CGI ':standard';
use CGI::Carp qw(fatalsToBrowser);
use DBI;
$ENV{"ORACLE_HOME"} = "/home/oracle/product/9.2.0";
print "Content-type: text/html\n\n";
$dbh = DBI->connect('dbi:Oracle:', q{gavin/a32182302@(DESCRIPTION=
 (ADDRESS=(PROTOCOL=TCP)(HOST= info200.infc.ulst.ac.uk)(PORT=1521))
 (CONNECT_DATA=(SID=ORCL)))}, "") or die "Bad connect";
my $name=param('EName');
my $number=param('ENumber');
my $salary=param('SalaryInfo');
my $job=param('jobType');
print "Inserting ...Ename=$name,Enumber=$number,SalaryInfo=$salary, Job=$job\n";  
my $sql1 =qq{insert into emp(empno,ename,sal,job) values(?,?,?,?)};
my $sql2 = 'SELECT COUNT(*) FROM EMP WHERE empno = ?';
my $sth1=$dbh->prepare($sql1);
my $sth2=$dbh->prepare($sql2);

$sth2->execute($number);

if ($sth2->fetchrow_array() > 0) {
     $sth->execute($number,$name,$salary,$job) or die $DBI::errstr;
     print "</br>Insert Complete\n";
     }
else {
     print "</br>Insert Failed: duplicate record\n";
     }

$dbh->disconnect;

# Let me know if you have any problems with this.
# -tats
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now