demo:
#!/usr/bin/perl -w
# Perl pragma to restrict unsafe constructs
use strict;
# use DBI model
use DBI;
# debug var results;
use Data::Dumper;
#validate entry
sub validate {
my ($v1,$v2) = @_;
my $flag = 0;
if($v1 eq $v2) {
$flag = 1;
return ($flag);
}
return ($flag);
}
#main function
sub main {
#get params
# @_
# Within a subroutine the array @_ contains the parameters passed to that subroutine.
# Inside a subroutine, @_ is the default array for the array operators push, pop, shift, and unshift.
my $currTime;
my @arrs;
# $dbh Database handle objec (AutoCommit => 1 auto commit)
my $dbh = DBI->connect('dbi:mysql:product','li','jie', { RaiseError => 1, AutoCommit => 0 });
# $sth Statement handle object
# get date from txt
my $s_file = 'user_file.txt';
my $count = 0;
my $val = '';
# open source file s_file
if(open(FO, $s_file)) {
# iterate the content of s_file
while(<FO>) {
my $line = $_;
$count++;
# abanbon line 1
next if($count == 1);
# remove the blank around head and tail
$line =~ s/\s|\s$//g;
$val = $line." ".$val;
}
# set user informations in array
@arrs = split / /, $val;
# get the current date
my ($sec,$min,$hour,$mday,$mon,$year) = localtime(time);
$year += 1900;
$mon += 1;
$currTime = $year."-".$mon."-".$mday." ".$hour.":".$min.":".$sec;
} else {
print "Open file failed!\n";
}
# insert
my $ins = $dbh->prepare("insert into user(dt_created,dt_updated,name,nick,age) values(?,?,?,?,?)");
foreach my $data ( @arrs ) {
my ($name, $nick, $age) = split /,/, $data;
#validate
my $sel = $dbh->prepare("select * from user");
$sel->execute();
my $uni = 1;
while ( my $h = $sel->fetchrow_hashref ) {
my $n = $$h{nick};
# validate whether the nick in database is existed
my $flag = validate $n, $nick;
# if the nick is exist,abandoned
if($flag) {
# set the flag
$uni = 0;
# notice
print "Duplicate nick $nick,abandoned!\n";
last;
}
}
# if the nick isn't exist in database, then insert it
if($uni) {
$ins->execute($currTime,$currTime,$name, $nick, $age);
}
}
#commit
$dbh->commit;
# select
my $query = $dbh->prepare("select * from user");
$query->execute();
# iterate the hashset
while ( my $info = $query->fetchrow_hashref ) {
# display
print Dumper $info;
}
#close resource
$dbh->disconnect;
print "\n";
}
# pass params to main function,
# @ARGV
# The array @ARGV contains the command-line arguments intended for the script.
main(@ARGV);