comment on

Hello skef,

Welcome to the community. Well I do not see something extremely wrong with your code. So I have a few minor suggestions and possible problem solutions.

I was looking online and maybe this is the reason/solution to your problem AutoInactiveDestroy

Taken from the documentation:

Type: boolean, inherited

The "InactiveDestroy" attribute, described above, needs to be explicitly set in the child process after a fork(). This is a problem if the code that performs the fork() is not under your control, perhaps in a third-party module. Use AutoInactiveDestroy to get around this situation.

If set true, the DESTROY method will check the process id of the handle and, if different from the current process id, it will set the InactiveDestroy attribute.

This is the example it's designed to deal with:

my $dbh = DBI->connect(...);
    some_code_that_forks(); # Perhaps without your knowledge
    # Child process dies, destroying the inherited dbh
    $dbh->do(...); # Breaks because parent $dbh is now broken
[download]

Caution: The AutoInactiveDestroy attribute was added in DBI 1.614.

So in case you want to add this feature you need to verify/update your version.

Second Objervation: From Programming with the DBI By Alligator Descartes & Tim Bunce:

What happens if you don't explicitly disconnect? Since DBI handles are references to Perl objects, Perl's own garbage collector will move in and sweep up any object trash you leave lying around. It does that by calling the object's DESTROY method when there are no longer any references to the object held by your script, or when Perl is exiting.

Third Objervation: Personally I do not like Manual Error Checking so I do it automatically by setting $h->{PrintError} = 1; and $h->{RaiseError} = 1;. You can read on the same reference about Automatic Versus Manual Error Checking.

Taken all this in consideration a sample script written in MySQL but with minor modifications should work also for SQL.

I have added functions to check if the DB or the TABLE exist else create them.

I am also using conf.ini file just in case I want to connect to multiple devices so I can use a for loop. Personally I found it extremely useful in case you do not like it just import your data straight.

conf.ini

[MySQL]
user=username
pass=password
host=hostname
port=3306
db=database_name
table=table_name
[download]

MySQL.pl Script

#!/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 IF NOT EXISTS `".$config
+{'MySQL.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";
    }

    } # End of else

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

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

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

    $checkExist = $dbh->prepare("INSERT IGNORE INTO `".$config{'MySQL.
+table'}."` (`UnixTime`, `losses`) VALUES ('".$time."','".$random_numb
+er."') ");

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

    my $statement = "SELECT * FROM `".$config{'MySQL.table'}."` WHERE 
+1";

    my $hash_ref = $dbh->selectall_hashref($statement, 'id');

=arrayref
    # get array of id and name pairs:
    my $ary_ref = $dbh->selectcol_arrayref($statement, { Columns=>[1,3
+] });
    my %hash = @$ary_ref; # build hash from key-value pairs so $hash{$
+id} => name

    print Dumper(\%hash);
=cut
    
=count rows
    $checkExist = $dbh->prepare("SELECT COUNT(*) FROM `".$config{'MySQ
+L.table'}."`");
    
    if (!$checkExist->execute()) {
    die "Error: ". $checkExist->errstr ."\n";
    }

    my ($crows) = $dbh->selectrow_array($checkExist);
    print $crows . "\n";
=cut

    $checkExist->finish();

    $dbh->disconnect()
    or warn "Error disconnecting: $DBI::errstr\n";

    return $hash_ref;

} # End of mysql sub

my $output_ref = mysql();

print Dumper($output_ref);

__END__

First Round:

Database: Speed does not exist creating: Speed
Table: Data does not exist creating: Data
$VAR1 = {
          '1' => {
                   'UnixTime' => '1413048302',
                   'losses' => '102',
                   'id' => '1'
                 }
        };

Second Round:

$VAR1 = {
          '1' => {
                   'UnixTime' => '1413048302',
                   'losses' => '102',
                   'id' => '1'
                 },
          '2' => {
                   'losses' => '135',
                   'UnixTime' => '1413048306',
                   'id' => '2'
                 }
        };
[download]

Hope this helps and solves your problem.

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

In reply to Re: DBI DESTROY problem by thanos1983
in thread DBI DESTROY problem by skef

Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!

Titles consisting of a single word are discouraged, and in most cases are disallowed outright.

Read Where should I post X? if you're not absolutely sure you're posting in the right place.

Please read these before you post! —

Posts may use any of the Perl Monks Approved HTML tags:

a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr

You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)

	For:		Use:
	&		`&`
	<		`<`
	>		`>`
	[		`[`
	]		`]`

Link using PerlMonks shortcuts! What shortcuts can I use for linking?

See Writeup Formatting Tips and other pages linked from there for more info.