http://www.perlmonks.org?node_id=809698

vinoth.ree has asked for the wisdom of the Perl Monks concerning the following question:

Hi Monks

Below is my code to execute any select Query and fetch data from database and return the fetched value. It will return only the values, Is it possible to take the column name? So that I can hold the column name and the corresponding value in a hash.

sub Select_Qry{ ($class,$dbaseh,$query)=@_; my($sth, $execu); if(!defined($query)){ $sth->finish(); $dbaseh->disconnect(); } if($dbaseh->ping){ $sth=$dbaseh->prepare("$query") or warn $file->log( + level => 'info', message => localtime() .": Can't prepare SQL state +ment:[$query] $DBI::errstr\n"); #Prepare the select query $execu =$sth->execute() or warn $file->log( level = +> 'info', message => localtime() .": Can't execute SQL statement:[$q +uery] $DBI::errstr\n"); #Execute the se +lect query if(!defined($execu)){ $file->log( level => 'info', message => local +time() .": Can't execute SQL statement:[$query] $DBI::errstr\n"); } else{ $file->log( level => 'info', message => local +time() .":SUCCESS:[$query]\n"); } while ( @arra = $sth->fetchrow_array ) { push (@Data_Array,@arra); } $file->log( level => 'info', message => localtime() +.": Fetched Data [@Data_Array]\n"); } else{ $file->log( level => 'info', message => loca +ltime() .": Reconnecting with Database by Ping function\n"); $dbaseh=&connection(); $file->log( level => 'info', message => loca +ltime() .": DataBase Get Connected Successfully.\n" ); $sth=$dbaseh->prepare("$query") or warn $file +->log( level => 'info', message => localtime() .": Can't prepare SQL + statement: $DBI::errstr[$query]\n"); #pre +pare the select query $execu=$sth->execute() or warn $file->log( le +vel => 'info', message => localtime() .": Can't execute SQL statemen +t: $DBI::errstr[$query]\n"); + #execute the select query if(!defined($execu)){ $file->log( level => 'info', message = +> localtime() .": Can't execute SQL statement:[$query] $DBI::errstr\ +n"); } else{ $file->log( level => 'info', message = +> localtime() .":SUCCESS:[$query]\n"); } while ( @arra = $sth->fetchrow_array ) + { push (@Data_Array,@arra); } $file->log( level => 'info', message = +> localtime() .": Fetched Data [@Data_Array]\n"); } $sth->finish(); $dbaseh->disconnect(); + #Disconnect the databa +se. return @Data_Array; }

Replies are listed 'Best First'.
Re: DBI : Get Column name and Value in hash
by GrandFather (Saint) on Nov 27, 2009 at 08:10 UTC

    That code can't possibly work! Consider:

    my ($sth, $execu); if (!defined ($query)) { $sth->finish ();

    $sth is declared, but not initialised. Then if $query is undefined $sth->finish () is called, which must fail with a "Can't call method "finish" on an undefined value at C:\Users\Peter\Delme~~\PerlScratch\noname.pl line 10. " error.

    Working a little closer to your issue: passing a query as a string in this fashion without passing parameters for the execute () prevent you from using placeholders which probably means your code is susceptible to insertion attacks.

    Which leads to: the code building the query knows which columns are used for the fetched data and has to deal with the returned rows so the problem isn't with the code you've shown - it's with the calling code.

    BTW, always use strictures (use strict; use warnings; - see The strictures, according to Seuss).


    True laziness is hard work

      Thanks for your information

Re: DBI : Get Column name and Value in hash
by stefbv (Curate) on Nov 27, 2009 at 08:03 UTC
    Yes, you can use 'fetchrow_hashref' instead of 'fetchrow_array' with some small changes in the code. Check the docs for DBI.
Re: DBI : Get Column name and Value in hash
by Tux (Canon) on Nov 27, 2009 at 17:18 UTC

    fetchrow_hashref () is slower than using bind_columns (), with which you can achieve the same:

    my %rec; my $sth = $dbh->prepare ("select * from foo"); $sth->execute; $sth->bind_columns (\@rec{@{$sth->{NAME_lc}}}); while ($sth->fetch) { print "foo.c_foo = $rec{c_foo}\n"; }

    Some proof with a table with 8 fields and and 62684 records:

    cmpthese (5, { hashref => sub { my $sth = $dbh->prepare ("select * from ll_go"); $sth->execute; 1 while my $h = $sth->fetchrow_hashref; }, bindcol => sub { my %rec; my $sth = $dbh->prepare ("select * from ll_go"); $sth->execute; $sth->bind_columns (\@rec{@{$sth->{NAME_lc}}}); 1 while $sth->fetch; }, }) => perl bench.pl Rate hashref bindcol hashref 1.31/s -- -76% bindcol 5.49/s 320% --

    Enjoy, Have FUN! H.Merijn
Re: DBI : Get Column name and Value in hash
by Anonymous Monk on Nov 27, 2009 at 07:58 UTC
    Yes, it is possible. What trouble are you having accomplishing this task?

      Oh Sorry! fetchrow_hashref function is there in DBI module to do this job.