#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2011
    Posts
    42
    Rep Power
    4

    Lightbulb Creating a hash from an array.


    Happy new year to you all good people.!!

    Following is my attempt. I want to get row counts of 2 or more DB tables and assign them in a hash as described below.

    Code:
    %DB = (
              TABLE1 => "<Rowcount>",
              TABLE2 => "<Rowcount>",
            );
    Following is what I have done so far.
    Code:
    use strict;
    my $DB_STRING = $ENV{'MY_DB_STRING'};
    my @TABLES = ("VERSION_INFO","TABLE_INFO");
    my $QUERY;
    my @cnt;
    foreach my $TAB (@TABLES) {
    $QUERY = "select count(*) from $TAB"; 
    my @cnt= `sqlplus -s $DB_STRING <<START;
    SET NEWPAGE 0;
    SET SPACE 0;
    SET PAGESIZE 0;
    SET linesize 10000;
    SET ECHO OFF;
    SET FEEDBACK OFF;
    SET HEADING OFF;
    $QUERY
    quit;
    START`;
    
    foreach (@cnt) {
    print "$_\n";
     }
    }
    Now I want to store the values retreived from the above script to be kept in a hash as mention on top. Gentleman this is the first time Im playing with perl hashes and I would like to grasp the idea by doing something useful and there Im stucked.

    Many thanks in advance good people.

    /Bindo
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    4
    Rep Power
    0
    Anybody think about this problem. Please share here. I don't more understand but I am very interneted it.
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2011
    Posts
    42
    Rep Power
    4
    Oh great. I thought Im the only one.

    Originally Posted by takehousely
    Anybody think about this problem. Please share here. I don't more understand but I am very interneted it.
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Apr 2009
    Posts
    1,968
    Rep Power
    1225
    To me it looks like you're jumping through too many hoops to query the db.

    Instead of shelling out to sqlplus, you should be using Perl's DBI module along with the required DBD::Oracle driver module.

    DBI
    DBD::Oracle

    This is untested, so it may need a slight tweak, but is the direction I'd take.
    Code:
    use strict;
    use warnings;
    use DBI;
    use DBD::Oracle;
    
    my $dsn   = '....';  # your dsn connection string;
    my $user  = 'username';
    my $pass  = 'password';
    my @table = qw(VERSION_INFO TABLE_INFO);
    my %DB;
    
    my $dbh = DBI->connect($dsn, $user, $pass, {RaiseError => 1})
            or die "DB connection failed - $DBI::errstr";
    
    $DB{$table[0]} = $dbh->do("select count(*) from $table[0]");
    $DB{$table[1]} = $dbh->do("select count(*) from $table[1]");
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Apr 2009
    Posts
    1,968
    Rep Power
    1225
    I forgot that the 'do' method is used for non select statements, so we need to handle the prepare/execute/fetch ourselves.

    Code:
    my $sth = $dbh->prepare("select count(*) from $table[0]");
    $sth->execute;
    
    my ($cnt) = $sth->fetchrow_array;
    $DB{$table[0]} = $cnt;
    I'll leave the loop construction that handles both tables to the reader.

    Comments on this post

    • OmegaZero agrees : Also, you can use $dbh->selectrow_array('SELECT...') (or its friends) to merge the prepare/execute/fetch into one call
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2011
    Posts
    42
    Rep Power
    4
    Thank you very much Mr Fish. I still work for the same company and they never seem to allow us to get the cspan modules. Further I beleive that the newbies can learn more with the manual approaches. Please help out sir.


    Originally Posted by FishMonger
    I forgot that the 'do' method is used for non select statements, so we need to handle the prepare/execute/fetch ourselves.

    Code:
    my $sth = $dbh->prepare("select count(*) from $table[0]");
    $sth->execute;
    
    my ($cnt) = $sth->fetchrow_array;
    $DB{$table[0]} = $cnt;
    I'll leave the loop construction that handles both tables to the reader.
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Apr 2009
    Posts
    1,968
    Rep Power
    1225
    I still work for the same company and they never seem to allow us to get the cspan modules.
    If that truly is the case, then they've thrown away the vast majority of Perl's functionality and at that point it doesn't make any sense to continue to program in Perl.

    The more likely problem is that you haven't presented the advantages of using cpan modules in a manor that they would accept.

    I have not worked with sqlplus or for that matter Oracle databases, but I can't imagine any valid need/reason to use those set statements just to get a row count.

    What does your sqlplus statement return/assign to @cnt?

    If it returns more than a single value/integer, which I can't see why it would, then you need to alter the statement.

    See if this does what you need.
    Code:
    use warnings;
    use strict;
    
    my $DB_STRING = $ENV{'MY_DB_STRING'};
    my @TABLES = ("VERSION_INFO","TABLE_INFO");
    my ($cnt, %DB);
    
    foreach my $TAB (@TABLES) {
        my $QUERY = "select count(*) from $TAB"; 
        my $cnt = `sqlplus -s $DB_STRING $QUERY quit`;
        
        $DB{$TAB} = $cnt;
    }
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2011
    Posts
    42
    Rep Power
    4
    Mr Fish Thank you very much sir. In my honest opinion you are just extraordainary. To my eye this is the answer to my query. let me try this on Monday when Im at work(I dont have a db set up in my home notebook).

    To answer your question about the count yes it just take the number of rows and assign it to the array. In my original post I have included only 2 tables (version_info, table_info) this could be more.

    one more question Mr Fish. I was thinking that we will be needing an incrrementer ($i++) when populating the hash with mysql output?

    Thanks again, You have helped me so many times.

    /Bindo
  16. #9
  17. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Apr 2009
    Posts
    1,968
    Rep Power
    1225
    I was thinking that we will be needing an incrrementer ($i++) when populating the hash with mysql output?
    change:
    Code:
    $DB{$TAB} = $cnt;
    to:
    Code:
    $DB{$TAB} += $cnt;
  18. #10
  19. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2011
    Posts
    42
    Rep Power
    4
    Mr Fish, It worked Sir. Thank you very much. However there is small doubt that I need to calrify which is when I try to print the keys and the values I see some dupicates getting returned.

    Code:
    use strict;
    my $DB_STRING = $ENV{'DB_STRING'};
    my @TABLES = ("VERSION_INFO","TABLE_INFO");
    my $QUERY;
    my %DB;
    
    foreach my $TAB (@TABLES) {
    $QUERY = "select count(*) from $TAB"; 
    my $cnt= `sqlplus -s $DB_STRING <<START;
    $QUERY
    quit;
    START`;
     
    $DB{$TAB} = $cnt;
    foreach my $key ( keys %DB )
    {
    print "Table --> $key, 	value --> $DB{$key}\n";
    }
    }
    Following it the out it produces.
    mmdba2@sbi-126:/x02/MMD/mmdba2>./mmm1.pl
    Code:
    Table --> VERSION_INFO, 	value --> 	 1
    Table --> VERSION_INFO, 	value --> 	 1
    Table --> TABLE_INFO, 	value --> 	58
    Please note that the VERSION_INFO table has been printed twice. Please advice Sir. IS this the right way to iterate through a hash?

    /Bindo
  20. #11
  21. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Apr 2009
    Posts
    1,968
    Rep Power
    1225
    Don't print the hash data inside the loop that builds the hash and please use proper indentation.

    Also, there's no need to use a here doc for such a simple short statement and a couple of those vars aren't really needed.

    One guideline I try to follow in cases like this is if it adds verbosity, it must also add clarity. You here doc and extra vars only add verbosity.

    Additionally, the choice of var names is important. They should provide some info or describe what the var holds and in the vast majority of cases should not be in all uppercase.

    This is how I'd write that code snippet.
    Code:
    use strict;
    use warnings;
    
    my $db_login = $ENV{'DB_STRING'};
    my @tables   = ('VERSION_INFO', 'TABLE_INFO');
    my %DB;
    
    foreach my $table (@tables) {
        $DB{$table} = `sqlplus -s $db_login select count(*) from $table quit`;
    }
    
    foreach my $table ( keys %DB )
    {
        print "Table --> $table, value --> $DB{$table}\n";
    }

IMN logo majestic logo threadwatch logo seochat tools logo