thanos1983 has asked for the wisdom of the Perl Monks concerning the following question:

Dear Monks,

Thank you for your time and effort reading and relpying to my question.

I am sure that this question it is not a challenge for most of you but it seems that I can not find an answer to such a simple question.

I want to create a Database, table, and insert some data on MySQL database. The problem comes that I also want to check if all of these elements exist before I send the request to create them.

The only solution that I have found so far is to connect and disconnect from the DB after the completion of one process. This not only makes my code slower but also I am sending multiple request.

I am sure that it can be done with one request from beginning. I tried to find a command to use such as use "db" same as MySQL syntax, but so far not successfully.

The reason that I am connecting and disconnecting is that the script will complain that it "can not find the database" alternatively.

Sample of my code is provided under:

#!/usr/bin/perl

use strict;
use warnings;
use DBD::mysql;

$|=1; #flush every time the program

my $db = 'xxxx';
my $port = '3306'; # default
my $host = 'localhost'; # or external
my $pass = 'xxxx';
my $user = 'xxxx';
my $table = 'xxxx';
my $checkExist;

sub mysql {

    my $dbh = DBI->connect("dbi:mysql::".$host.":".$port."", 
               "".$user."",
               "".$pass."", 
               { 'PrintError' => 1, 'RaiseError' => 1 } 
    ) or die "Could not connect to ".$host.": ". $DBI::errstr ."\n";

    my $databases = $dbh->do("SHOW DATABASES LIKE '".$db."'") or die "
+Error: " .dbh->errstr. "\n";

    if ($databases eq 1) {
    printf "Database: ".$db." exists not creating: ".$db."\n";
    }
    else {
    printf "Database: ".$db." does not exist creating: ".$db."\n";

    $checkExist = $dbh->prepare("CREATE DATABASE IF NOT EXISTS `".$db.
+"`") or die "Could not create the: ".$db." error: ". $dbh->errstr ."\
+n";

    if (!$checkExist->execute()) {
        die "Error: ". $checkExist->errstr ."\n";
    }

    $checkExist->finish();

    $dbh->disconnect();

    } # End of else

    $dbh = DBI->connect("dbi:mysql:".$db.":".$host.":".$port."", 
            "".$user."",
            "".$pass."", 
            { 'PrintError' => 1, 'RaiseError' => 1 } 
    ) or die "Could not connect to ".$host.": ". $DBI::errstr ."\n";

    my $tables = $dbh->do("SHOW TABLES FROM `".$db."` WHERE Tables_in_
+".$db." LIKE '".$table."'") or die "Error: " .dbh->errstr. "\n";

    if ($tables eq 1) {
    printf "Table: ".$table." exists not creating: ".$table."\n";
    }

    else {
    printf "Table: ".$table." does not exist creating: ".$table."\n";

    $checkExist = $dbh->prepare("CREATE TABLE IF NOT EXISTS `".$table.
+"` (
                    `id` int(11) NOT NULL AUTO_INCREMENT,
                    `UnixTime` int(11) NOT NULL,
                    `losses` int(11) NOT NULL,
                    PRIMARY KEY (`id`)
                    ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREM
+ENT=1 ;");

    if (!$checkExist->execute()) {
        die "Error: ". $checkExist->errstr ."\n";
    }

    $checkExist->finish();

    $dbh->disconnect();

    } # End of else

    my $range = 50;
    my $minimum = 100;

    my $random_number = int(rand($range)) + $minimum;
    my $time = time();

    my $losses = $time . ' ' . $random_number;

    $dbh = DBI->connect("dbi:mysql:".$db.":".$host.":".$port."", 
            "".$user."",
            "".$pass."", 
            { 'PrintError' => 1, 'RaiseError' => 1 } 
    ) or die "Could not connect to ".$host.": ". $DBI::errstr ."\n";

    $checkExist = $dbh->prepare("INSERT IGNORE INTO `".$table."` (`Uni
+xTime`, `losses`) VALUES ('".$time."','".$random_number."') ");

    if (!$checkExist->execute()) {
    die "Error: ". $checkExist->errstr ."\n";
    }

    $checkExist->finish();

    $dbh->disconnect();

    return $losses;

} # End of mysql sub

my $output = &mysql();

print "Added:" .$output. "\n";

[download]

Seeking for Perl wisdom...on the process...not there...yet!

Replies are listed 'Best First'.
Re: MySQL avoid multiple connect request
by poj (Abbot) on May 23, 2014 at 10:20 UTC
    USE database should work with MySQL ; 
    #!/usr/bin/perl
use strict;
use warnings;
use DBI;

my $db    = 'xxxx';
my $table = 'xxxx';

my $dbh = dbh(); # connect

# create database
$dbh->do('CREATE DATABASE IF NOT EXISTS '.$db)
      or die "Could not create database $db : ". $dbh->errstr;

# create table
$dbh->do("USE $db");  
$dbh->do("CREATE TABLE IF NOT EXISTS $table (
            id        int(11) NOT NULL AUTO_INCREMENT,
            UnixTime  int(11) NOT NULL,
            losses    int(11) NOT NULL,
            PRIMARY KEY (id)
          ) ENGINE=InnoDB DEFAULT CHARSET=latin1 
            AUTO_INCREMENT=1");

# insert data  
my $range   = 50;
my $minimum = 100;
my $random_number = int(rand($range)) + $minimum;
my $time = time();
$dbh->do("INSERT IGNORE INTO $table (UnixTime,losses)
          VALUES (?,?)",
          undef,
          $time,$random_number);
print "added $time $random_number to $db table $table\n";

sub dbh {
  my $dsn = "DBI:mysql:database=;host=localhost";
  my $dbh = DBI->connect($dsn, 'user', 'pwd',
            {RaiseError => 1, PrintError => 1}) 
            or die (Error connecting " $DBI::errstr");
}

    [download]
    poj
[reply]
[d/l]
[select]

      To poj:

      Exactly what I need, so simple yet I could not find it online. To be honest I did not think even testing for my self, I thought that since I can not find it, it is not possible.

      Again thanks for your time and effort assisting me to my problem.

      Seeking for Perl wisdom...on the process...not there...yet!
[reply]

        I thought that since I can not find it, it is not possible.
        That's the mentality you want to avoid! Simply make a backup(easy as copying the table) of both database and code, then try it.
        The worst thing that can happen is that you have to restore the data(unless you're so dense as to do it on a production server etc). 

        ~Thomas~ 
        
"Excuse me for butting in, but I'm interrupt-driven..."
[reply]

Back to Seekers of Perl Wisdom