Hendra,
Here is the section of one of my programs that uses the count(*) and the
update or insert logic. It may help:
my $query=0;
my $strSQL2="select count(*) as query from biz_orgs where
borg_id='".$data[$i][1]."'";
my $sth2=$dbh2->prepare($strSQL2) or error("ERROR - Could not form SQL
query: $DBI::errstr");
$sth2->execute() or error("ERROR - Could not execute SQL query:
$DBI::errstr");
while($data2=$sth2->fetchrow_hashref){
$query=$data2->{QUERY};
}
if($query>0){
$strSQL2="update biz_orgs set
borg_name='".$data[$i][0]."',borg_parent_id='".$data[$i][2]."',";
$strSQL2.="borg_tree='".$data[$i][3]."',borg_ut=sysdate,
active_flag='Y' where borg_id='".$data[$i][1]."'";
error("Updating values $values");
$sth2=$dbh2->prepare($strSQL2) or error("ERROR - Could not form SQL
query: $DBI::errstr");
$sth2->execute() or error("ERROR - Values not updated: $DBI::errstr");
}
else{
$strSQL2="insert into biz_orgs
(borg_name,borg_id,borg_parent_id,borg_tree,borg_ct,borg_ut, active_flag)
";
$strSQL2.="values ($values,sysdate,sysdate, 'Y')";
error("Inserting values $values");
$sth2=$dbh2->prepare($strSQL2) or error("ERROR - Could not form SQL
query: $DBI::errstr");
$sth2->execute() or error("ERROR - Values not inserted:
$DBI::errstr");
}
This is for an Oracle DB.
Patty
Hendra Kusnandar
<hendrakusnandar@
yahoo.com> To
[EMAIL PROTECTED]
04/02/2004 01:52 cc
AM [EMAIL PROTECTED],
[EMAIL PROTECTED]
Subject
handle dupicate data entry (was
"insert into two table and check
table perl DBI")
hai patty, i don't understand your opinion but thank's
for your response.
to correct my script where i put the select count(*)
on my scripts. I have tried every way
possible and I can think of and I cannot get it to
work at all.
may be you can correct my scripts, sorry i'am still
beginner with perl.
for mike thank's for your response. I want handle
duplicate data if input source are same and will show
message on shell
"sorry, same data cannot insert again" and data will
not insert into database.
i have search with google to correct the scripts, but
i can found the solution...may be everyone have same
case with me to solve the problem.
the scripts are :
#!/usr/bin/perl
use DBI;
sub insert_user {
my ($nim, $nama, $password) = @_;
my ($insert_sql,$sth);
$database = "practice_perl";
$dbuser = "hendra";
$dbpasswd = "hendra";
$dbhost = "localhost";
$dsn = "DBI:mysql:database=$database;host=$dbhost";
$dbh = DBI->connect($dsn,$dbuser,$dbpasswd) || die
$DBI::errstr;
$insert_sql = qq {insert into user
(nik,name,password) values ('$nik', '$name',
'$password')};
$sth = $dbh->prepare($insert_sql) || die "prepare:
$insert_sql: $DBI::errstr";
$sth->execute || die "Unable to execute query:
$dbh->errstr\n";
$sth->finish;
$dbh->disconnect;
}#end insert_user
sub insert_log {
my ($input) = @_;
my ($insert_sql_log);
$database = "practice_perl";
$dbuser = "hendra";
$dbpasswd = "hendra";
$dbhost = "localhost";
$dsn = "DBI:mysql:database=$database;host=$dbhost";
$dbh = DBI->connect($dsn,$dbuser,$dbpasswd) || die
$DBI::errstr;
$insert_sql_log = "insert into log values
('',".$dbh->quote($input).")";
$dbh->do($insert_sql_log) || die ($dbh->errstr);
return $dbh->{'mysql_insertid'};
$dbh->disconnect;
}#end insert_log
print "INPUT : ";
while ($input = <>) {
#format insert : nik,name,password
if($input =~/^(\d{8}),(.*),(\w{6})$/i){
insert_user($1,$2,$3);
}else{
insert_log($input);
print "Wrong Format";
}#end if
print "\nINPUT : ";
}#end while
Hendra Kusnandar
__________________________________
Do you Yahoo!?
Yahoo! Small Business $15K Web Design Giveaway
http://promotions.yahoo.com/design_giveaway/
