DBIプログラム１
戻る
<![CDATA[
# obtain_table.pl
use strict;
use DBI;
my ( %t, $n, @fld, @rec, @list);
while(<DATA>){
@fld = split;
if (/^TABLE/){
$t{table} = $fld[1];
} elsif (/^Field/) {
@list = @fld[1..$#fld];
}
}
$t{dsn} = "DBI:mysql:host=localhost;database=cookbook";
$t{dbh} = DBI->connect($t{dsn}, "cbuser", "cbpass") or die "Cannot connect to server\n";
$t{dbh}->do("SET NAMES sjis");
if(!$t{dbh}){
print "SQL read ERROR!\n";
exit;
}
$t{sth} = $t{dbh}->prepare("SELECT * FROM $t{table}");
$t{sth}->execute;
while (@rec = $t{sth}->fetchrow_array) {
$t{list}{$rec[1]}++;
}
$t{sth}->finish;
$t{dbh}->disconnect;
# 出力
$t{file} = $t{table} . '_order.txt';
open(OUT,">../txt/$t{file}");
print OUT "Filename=$t{file}\n";
for $n (sort keys %{ $t{list} } ) {
print OUT $t{table},'==>';
print OUT $n,"\n";
}
close(OUT);
print "The output file is ../txt/$t{file}.\n";
__DATA__
TABLE main_type1
#TABLE main_maker1
#TABLE main_name1
Field id name memo
--------------------------------------------------------------
# SQLファイルを生成
# make_table.pl
use strict;
my (%t,$n,@fld,@list);
while(<DATA>){
@fld = split;
if (/^TABLE/){
$t{table} = $fld[1];
} elsif (/^Field/) {
@list = @fld[1..$#fld];
}
}
$t{inputfile} = $t{table} . '_order.txt';
# Reading input data
open(IN,"../txt/$t{inputfile}") or die "Can't open the file $t{inputfile}.\n";
while(<IN>){
if (/^$t{table}/) {
chop;
@fld = split(/==>/);
$t{list}{$fld[1]}++;
}
}
close(IN);
@list = sort keys %{ $t{list} };
$t{outputfile} = $t{table} . '.sql';
open(OUT,">../sql/$t{outputfile}");
print OUT 'DROP TABLE IF EXISTS ';
print OUT $t{table},';',"\n";
print OUT 'CREATE TABLE ';
print OUT $t{table},"\n";
print OUT '(',"\n";
print OUT ' id INT AUTO_INCREMENT,',"\n";
print OUT ' name CHAR(50),',"\n";
print OUT ' memo CHAR(200),',"\n";
print OUT ' PRIMARY KEY (id)',"\n";
print OUT ');',"\n\n";
for $n ( 0 .. $#list ) {
print OUT 'INSERT INTO ';
print OUT $t{table};
print OUT ' (name,memo) VALUES("';
print OUT $list[$n],'","X");';
print OUT "\n";
}
print "The output file is ../txt/$t{outputfile}.\n";
__DATA__
TABLE main_type1
#TABLE main_maker1
#TABLE main_name1
Field id name memo__DATA__
--------------------------------------------------------------
# obtain_table2.pl
use strict;
use DBI;
my ( %t, $n, @fld, @rec, @list);
while(<DATA>){
@fld = split;
if (/^TABLE/){
$t{table} = $fld[1];
} elsif (/^Field/) {
@list = @fld[1..$#fld];
}
}
$t{dsn} = "DBI:mysql:host=localhost;database=cookbook";
$t{dbh} = DBI->connect($t{dsn}, "cbuser", "cbpass") or die "Cannot connect to server\n";
$t{dbh}->do("SET NAMES sjis");
if(!$t{dbh}){
print "SQL read ERROR!\n";
exit;
}
$t{sth} = $t{dbh}->prepare("SELECT * FROM $t{table}");
$t{sth}->execute;
while (@rec = $t{sth}->fetchrow_array) {
$t{list}{$rec[1]}++;
}
$t{sth}->finish;
$t{dbh}->disconnect;
# 出力
$t{file} = $t{table} . '_order2.txt';
open(OUT,">../txt/$t{file}");
print OUT "Filename=$t{file}\n";
for $n (sort keys %{ $t{list} } ) {
print OUT $t{table},'==>';
print OUT $n,"\n";
}
close(OUT);
print "The output file is ../txt/$t{file}.\n";
__DATA__
TABLE main_type1
#TABLE main_maker1
#TABLE main_name1
Field id name memo
--------------------------------------------------------------
# obtain_type1.pl
use strict;
use DBI;
my ( %t, $n, @fld, @rec, @list,@list1,@list2);
while(<DATA>){
@fld = split;
if (/^TABLE/){
$t{table} = $fld[1];
} elsif (/^Field/) {
@list = @fld[1..$#fld];
}
}
$t{dsn} = "DBI:mysql:host=localhost;database=cookbook";
$t{dbh} = DBI->connect($t{dsn}, "cbuser", "cbpass") or die "Cannot connect to server\n";
$t{dbh}->do("SET NAMES sjis");
if(!$t{dbh}){
print "SQL read ERROR!\n";
exit;
}
$t{sth} = $t{dbh}->prepare("SELECT * FROM $t{table}");
$t{sth}->execute;
while (@rec = $t{sth}->fetchrow_array) {
push(@list2,$rec[0]);
push(@list1,$rec[1]);
}
$t{sth}->finish;
$t{dbh}->disconnect;
# 出力
$t{file} = $t{table} . '_list.txt';
open(OUT,">../txt/$t{file}");
print OUT "Filename=$t{file}\n";
for $n ( 0 .. $#list1 ) {
print OUT $t{table},'==>';
print OUT $list2[$n],'==>';
print OUT $list1[$n],"\n";
}
close(OUT);
print "The output file is ../txt/$t{file}.\n";
__DATA__
#TABLE main_name1
TABLE main_type1
Field id name memo
]]>
戻る