Hello again bigup401,
I found some time and I put together a small example that replicates your script and also the test case.
#!/usr/bin/perl
use DBI;
use strict;
use warnings;
use Data::Dumper;
use Config::Simple;
#package LSPDB;
$|=1; #flush every time the program
my $path = 'conf.ini';
my %config = ();
my $checkExist = "";
sub mysql {
Config::Simple->import_from("".$path."", \%config)
or die Config::Simple->error();
my $dbh = DBI->connect("dbi:mysql::".$config{'MySQL.host'}.":".$co
+nfig{'MySQL.port'}."",
"".$config{'MySQL.user'}."",
"".$config{'MySQL.pass'}."",
{ 'PrintError' => 1, 'RaiseError' => 1 , 'AutoInactiveD
+estroy' => 1 }
) or die "Could not connect to ". $config{'MySQL.host'} .": ". $DB
+I::errstr ."\n";
my $databases = $dbh->do("SHOW DATABASES LIKE '".$config{'MySQL.db
+'}."'")
or die "Error: " .dbh->errstr. "\n";
if ($databases eq 1) {
printf "Database: ". $config{'MySQL.db'} ." exists not creating: "
+. $config{'MySQL.db'} ."\n";
}
else {
printf "Database: ". $config{'MySQL.db'} ." does not exist creatin
+g: ". $config{'MySQL.db'} ."\n";
$checkExist = $dbh->do("CREATE DATABASE IF NOT EXISTS `".$config{'
+MySQL.db'}."`")
or die "Could not create the: ".$config{'MySQL.db'}." error: "
+. $dbh->errstr ."\n";
} # End of else
$dbh->do("USE ".$config{'MySQL.db'}."")
or die "Error: " .dbh->errstr. "\n";
my $tables = $dbh->do("SHOW TABLES FROM `".$config{'MySQL.db'}."`
WHERE Tables_in_".$config{'MySQL.db'}." LIKE
'".$config{'MySQL.table'}."'")
or die "Error: ".dbh->errstr. "\n";
if ($tables eq 1) {
printf "Table: ".$config{'MySQL.table'}." exists not creating: ".$
+config{'MySQL.table'}."\n";
}
else {
printf "Table: ".$config{'MySQL.table'}." does not exist creating:
+ ".$config{'MySQL.table'}."\n";
$checkExist = $dbh->prepare("CREATE TABLE ".$config{'MySQL.table'}
+." (
`ID` INT NOT NULL AUTO_INCREMENT,
`lastname` VARCHAR(25) CHARACTER SET utf8 NOT NULL
+ UNIQUE,
PRIMARY KEY(`ID`)
);");
if (!$checkExist->execute()) {
die "Error: ". $checkExist->errstr ."\n";
}
} # End of else
$checkExist = $dbh->prepare("INSERT IGNORE INTO `".$config{'MySQL.
+table'}.
"` (`lastname`) VALUES ('John'), ('Doe'), ('Jones')
+, ('Smith')");
if (!$checkExist->execute()) {
die "Error: ". $checkExist->errstr ."\n";
}
my $statement = "SELECT * FROM `".$config{'MySQL.table'}."` WHERE
+1";
my $hash_ref = $dbh->selectall_hashref($statement, 'lastname');
$checkExist->finish();
$dbh->disconnect()
or warn "Error disconnecting: $DBI::errstr\n";
return $hash_ref;
} # End of mysql sub
my $output_ref = mysql();
my @lastnames = keys %$output_ref;
print Dumper $output_ref, \@lastnames;
__DATA__
$ perl mysql.pl
Database: PerlMonks exists not creating: PerlMonks
Table: Data exists not creating: Data
$VAR1 = {
'Doe' => {
'ID' => 2,
'lastname' => 'Doe'
},
'John' => {
'ID' => 1,
'lastname' => 'John'
},
'Smith' => {
'lastname' => 'Smith',
'ID' => 4
},
'Jones' => {
'ID' => 3,
'lastname' => 'Jones'
}
};
$VAR2 = [
'Doe',
'John',
'Smith',
'Jones'
];
The conf.ini file:
[MySQL]
user=user
pass=password
host=localhost
port=3306
db=PerlMonks
table=Data
On your next question it would be easier for us to provide your minimal working example for replicating your problem.
Hope this helps.
Seeking for Perl wisdom...on the process of learning...not there...yet!
|