A few things:
a. Put some print STDERR statements in to make sure your
variables contain what you expect them to contain, and
to make sure execution is proceeding where it should.
print STDERR "--some helpful debugging info here--\n";
Also for debugging, if you aren't sure how DBI and/or
DBD::Oracle are treating your DBI statements, wrap the
prepare and execute in "trace" statements - read the
DBI perldocs on "trace" by doing
perldoc DBI
at a command prompt.
b. Your logic for checking to see if a user id already exists
is flawed - you are doing a 2 step process:
1. SELECT to see if user id already exists
2. if it doesn't exist, INSERT new one
Between 1 and 2, someone else could have inserted the
same user id that you are trying to insert in 2.
You should do away with step 1(checking to see if the user
id already exists). Instead, make sure the user id is a
unique key field in the database(which will not allow
duplicates to be inserted), so that if you try to insert
a user id that already exists, the database will kick out
an error - your job is to properly detect that "duplicate
key" error *nicely* so that you can give a proper error
message. One way to do that is to use "eval" which will
allow you to trap errors in DBI statments - I believe
the duplicate key error will occur when you try to
"execute" the sql. Here is some pseudo-code off the top
of my head for a way I've done this in the past:
my $sql = qq{
INSERT into my_user_table (user_id)
VALUES (?)
};
$dbh->{RaiseError} = 1;
eval {
my $sth = $dbh->prepare($sql);
$sth->execute($user_id);
};
if ($@) {
if ($@ =~ /"unique part of dup key msg"/) {
### Error caused by a "duplicate key" condition ###
### Do whatever it is you want to do now for
### the duplicate key error - print a message,
### display an error, whatever.
}
else {
### There was a DBI error, but it was not
### caused by a duplicate key condition.
}
}
NOTE: This code is completely untested and just off the
top of my head, but hopefully it's close.
The use of "eval" and RaiseError is all described very
nicely in the fine DBI perldoc.
HTH.
--
Hardy Merrill
Senior Software Engineer
Red Hat, Inc.
Nguyen, David M [[EMAIL PROTECTED]] wrote:
> I am writting a script using DBI::Oracle to insert user account into
> database, before inserting I need to verify to make sure userid is not
> already existed. I have script written, ran it but nothing seems happen.
> Can someone look into my script and provide me some correction?
>
>
> Thanks,
> David
>
>
> ****************Script goes below********************
>
> #!/usr/local/bin/perl -w
>
>
> use CGI qw(param);
>
> use DBI;
>
>
>
>
>
> ##############################
>
> # Setup Configurable Variables
>
> ##############################
>
> $date = `date`;
>
>
>
> $username = param("username");
>
> $username =~ s/%27|'|"|;|#|$//g;
>
> chomp ($username);
>
>
>
> $email = param("email");
>
> $email =~ s/%27|'|"|;|#|$//g;
>
> chomp ($email);
>
>
>
> $phone = param("phone");
>
> $phone =~ s/%27|(|)|'|"|;|#|$//g;
>
> chomp ($phone);
>
>
>
> $department = param("department");
>
> $department =~ s/%27|'|"|;|#|$//g;
>
> chomp ($department);
>
>
>
> $ems = param("ems");
>
> $ems = `echo $ems | tr "[a-z]" "[A-Z]"`;
>
> chomp ($ems);
>
>
>
> $sgx = param("sgx");
>
> $sgx = `echo $sgx | tr "[a-z]" "[A-Z]"`;
>
> chomp ($sgx);
>
>
>
> $psx = param("psx");
>
> $psx = `echo $psx | tr "[a-z]" "[A-Z]"`;
>
> chomp ($psx);
>
>
>
> $nfs = param("nfs");
>
> $nfs = `echo $nfs | tr "[a-z]" "[A-Z]"`;
>
> chomp ($nfs);
>
>
>
> $gsx = param("gsx");
>
> $gsx = `echo $gsx | tr "[a-z]" "[A-Z]"`;
>
> chomp ($gsx);
>
>
>
> $other = param("other");
>
> $other = `echo $other | tr "[a-z]" "[A-Z]"`;
>
> chomp ($other);
>
>
>
> $location = param("location");
>
> $location =~ s/%27|'|"|;|#|$//g;
>
> chomp ($location);
>
>
>
> $reason = param("reason");
>
> $reason =~ s/%27|'|"|;|#|$//g;
>
> chomp ($reason);
>
>
>
> $manager = param("manager");
>
> #$manager = `echo $manager | tr "[a-z]" "[A-Z]"`;
>
> chomp ($manager);
>
>
>
> $userid = param("userid");
>
> chomp ($userid);
>
>
>
> $keyword = param("keyword");
>
> $keyword =~ s/%27|'|"|;|#|$//g;
>
> chomp ($keyword);
>
>
>
> $comments = param("comments");
>
> $comments =~ s/%27|'|"|;|#|$//g;
>
> chomp ($comments);
>
>
>
> ################################
>
> # Output the HTML content type
>
> ################################
>
> print "Content-type: text/html\n\n";
>
>
>
>
>
> ###############
>
> # SQL
> ###############
>
>
>
> $SQLCHECK="select userid from useraccount where userid = '$userid'";
>
>
>
> $SQL = "insert into
> useraccount(username,email,phone,department,ems,sgx,psx,nfs,
> gsx,other,location,reason,manager,userid,keyword,comments)
>
> values
>
> ('$username','$email','$phone','$department','$ems','$sgx','$psx','$nfs','$g
> sx',
> '$other','$location','$reason','$manager','$userid','$keyword','$comments')"
> ;
>
>
> $dbh = DBI->connect( "serverdbmachine","user","password","Oracle" );
>
>
>
> $cursor = $dbh->prepare( "$SQLCHECK" );
>
>
>
> cursor->execute();
>
> @result = $cursor->fetchrow();
>
>
>
> if (@result eq "$userid") {
>
> print "User $userid already exists\n";
>
> } else {
>
> $cursor = $dbh->prepare( "$SQL" );
>
> $cursor->execute() || die $cursor->errstr;
>
> print "Your request has been sent. THANK YOU\n";
>
> $cursor = $dbh->commit();
>
>
>
> }
>
>
>
>
> $cursor->finish;
>
> $dbh->disconnect;
