Code:
#!/usr/bin/perl
use DBI;
use DBD::mysql;
use DBI qw(:sql_types);
$hostname = "localhost";
$database = "c2";
$username = "user";
$password = "password";
$dbh = DBI->connect("dbi:mysql:dbname=$database;host=$hostname", "$username", "$password", {
RaiseError => 1,
AutoCommit => 0
}
) || die "Database connection not made: $DBI::errstr";
$sth = $dbh->prepare("SELECT server_id FROM server WHERE server_name =?");
$ins = $dbh->prepare("INSERT INTO server_details (server_details_id, server_id, apt_source, cleaner_conf, crons, date, df, dmidecode, ethtool, fstab, group, hosts_allow, hosts_deny, hosts, ifconfig, j2_serverconf, last, lsmod, lspci, mdstat, mount_nfs, netstat, network, proc_scsi, pw, raidtab, rpm_list, sudoers, sysctl, tr114, uname, date_inserted) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
$dir = "/usr/local/c2/serverfiles";
opendir(sf, $dir) or die "can't open $dir: $!";
while (defined($serv_dir = readdir(sf))) {
@data_load = "";
next if $serv_dir =~ /^\.\.?$/; # skip . and ..
$sth->execute($serv_dir) or die $sth->errstr;
$sid = $sth->fetchrow_array;
#logic to make new server entries if sid = null
print "$serv_dir has a server id of: $sid\n";
@xml = glob("$dir/$serv_dir/*.xml");
@data_load[0] = "\t";
@data_load[1] = "$sid";
$inc = 2;
foreach $xml_file (@xml) {
#print "$xml_file";
open(XMLFILE, "$xml_file" ) or die "can't open $xml_file: $!";
while(<XMLFILE>) {
@data_load[$inc] .= $_;
}
$inc++;
}
#foreach $x (@data_load) {
# print "$x\n";
#}
$ins->bind_param(1,$data_load[0],SQL_VARCHAR); # server_details_id, primary key
$ins->bind_param(2,$data_load[1],SQL_INTEGER); # server_id foreign key
$ins->bind_param(3,$data_load[2],SQL_TEXT); # Line 49, the problem.
$ins->bind_param(4,$data_load[3],SQL_TEXT); # cleaner_conf
$ins->bind_param(5,$data_load[4],SQL_TEXT); # crons
$ins->bind_param(6,$data_load[5],SQL_TEXT); # date
$ins->bind_param(7,$data_load[6],SQL_TEXT); # df
$ins->bind_param(8,$data_load[7],SQL_TEXT); # dmidecode
$ins->bind_param(9,$data_load[8],SQL_TEXT); # ethtool
$ins->bind_param(10,$data_load[9],SQL_TEXT); # fstab
$ins->bind_param(11,$data_load[10],SQL_TEXT); # group
$ins->bind_param(12,$data_load[11],SQL_TEXT); # hosts_allow
$ins->bind_param(13,$data_load[12],SQL_TEXT); # hosts_deny
$ins->bind_param(14,$data_load[13],SQL_TEXT); # hosts
$ins->bind_param(15,$data_load[14],SQL_TEXT); # ifconfig
$ins->bind_param(16,$data_load[15],SQL_TEXT); # j2_serverconf
$ins->bind_param(17,$data_load[16],SQL_TEXT); # last
$ins->bind_param(18,$data_load[17],SQL_TEXT); # lsmod
$ins->bind_param(19,$data_load[18],SQL_TEXT); # lspci
$ins->bind_param(20,$data_load[19],SQL_TEXT); # mdstat
$ins->bind_param(21,$data_load[20],SQL_TEXT); # mount_nfs
$ins->bind_param(22,$data_load[21],SQL_TEXT); # netstat
$ins->bind_param(23,$data_load[22],SQL_TEXT); # network
$ins->bind_param(24,$data_load[23],SQL_TEXT); # proc_scsi
$ins->bind_param(25,$data_load[24],SQL_TEXT); # pw
$ins->bind_param(26,$data_load[25],SQL_TEXT); # raidtab
$ins->bind_param(27,$data_load[26],SQL_TEXT); # rpm_list
$ins->bind_param(28,$data_load[27],SQL_TEXT); # sudoers
$ins->bind_param(29,$data_load[28],SQL_TEXT); # sysctl
$ins->bind_param(30,$data_load[29],SQL_TEXT); # tr114
$ins->bind_param(31,$data_load[30],SQL_TEXT); # uname
$ins->bind_param(32,$data_load[31],SQL_DATETIME); # timestamp for record creation
$ins->execute() or warn $ins->errstr();
}
closedir(sf);