Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl: the Markov chain saw
 
PerlMonks  

Retrieving column names from SQL with DBI

by Krillian (Acolyte)
on Sep 24, 2020 at 17:34 UTC ( #11122167=perlquestion: print w/replies, xml ) Need Help??

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

Hi everyone, I have the following script:
my ($query) = our $connect->prepare("SELECT number, name FROM TableNam +e"); $query->execute(); my $data = $query->fetchall_arrayref(); $query->finish; foreach $data ( @$data) { my %row_data; # get a fresh hash for the row data my ($variable1, $variable2) = @$data; $row_data{number} = "$variable1"; $row_data{name} = "$variable2"; } push(@loop_data, \%row_data); }
Not the most efficient way of doing this but achieves what I want. I would like to be able to do a similar thing, but for an unknown number of columns in the SQL Table. So:
my ($query) = our $connect->prepare("SELECT * FROM TableName"); $query->execute(); my $data = $query->fetchall_arrayref(); $query->finish; foreach $data ( @$data) { my %row_data; # get a fresh hash for the row data foreach my $data_item (@$data) { $row_data{NAMEOFSQLCOLUMN} = "$data_item"; # How do I get the name + of the SQL column that the data_item belong to? } } push(@loop_data, \%row_data); }
Any help would be very much appreciated!

Replies are listed 'Best First'.
Re: Retrieving column names from SQL with DBI
by roho (Chancellor) on Sep 25, 2020 at 04:43 UTC
    Use "$sth{NAME}" to retrieve the list of field names in the query:
    $sth->execute(); my @fields = @{$sth->{NAME}};

    "It's not how hard you work, it's how much you get done."

Re: Retrieving column names from SQL with DBI
by NetWallah (Canon) on Sep 24, 2020 at 18:02 UTC
    Here is what I use.
    Note : Global $dbh has the db handle.
    sub Fetch_w_callback{ # Main DBI retrieval mechanism---- my ($sql, $callback) = @_; my $state = $_[2] ||= {}; # ** This THIRD param is CALL-by-ref ** $debug{SQL} = $sql; (my $sth= $state->{STH} = $dbh->prepare($sql))->execute(); $state->{ROW} = $state->{QUIT} = 0; while (my $row = $sth->fetchrow_hashref()){ $debug{ROW} = $state->{ROW}++; #last if ($callback->($row,$state) || 0) < 0; # Return negative +to quit $callback->($row,$state); last if $state->{QUIT}; # Call-ee asked us to stop. } $sth->finish; }
    Calling example:
    my $sql= "SELECT blah blah...."; Fetch_w_callback( "COPY ($sql) TO STDOUT CSV HEADER", # Let pg do c +sv work sub { my ($row,$state) = @_; print $row,"\n"; ##$fieldnames ||= $state->{STH}->{NAME} || [keys %$row]; ##if ($state->{ROW} ==1 ){ ## print $state->{HEADER} = ## join(",",@$fieldnames, "SOURCE=\@$ENV{HOSTNAM +E}" ## . " on " . scalar(localtime(time))) + ."\n"; # Header row ##} ## ##print join(",", map{defined $_?$_:""}@$row{@$fieldnames}) + ."\n"; return 0; }, $state );

                    "Imaginary friends are a sign of a mental disorder if they cause distress, including antisocial behavior. Religion frequently meets that description"

Re: Retrieving column names from SQL with DBI
by hippo (Chancellor) on Sep 25, 2020 at 08:35 UTC

    If you use a hashref as the slice arg to fetchall_arrayref you will receive an AoH rather than an AoA. Then it becomes trivial:

    my $query = $connect->prepare ("SELECT * FROM TableName"); $query->execute (); my $data = $query->fetchall_arrayref ({}); for my $row (@$data) { for my $key (keys %$row) { print "Column name is $key, column value is $row->{$key}\n"; } }

    🦛

      Note that fetching hashrefs is (much) slower than fetching arrayrefs. Most often this is marginal compared to the time spent in getting the data from the database, but in many cases, you can bring the speed back by using bind_columns:

      my $sth = $dbh->prepare ("select * from foo"); $sth->execute; my (@fields, %r) = @{$sth->{NAME_lc}}; $sth->bind_columns (\@r{@fields}); while ($sth->fetch) { # Do something with the record. Fields are stored in %r printf "%-16s: %s\n", $_, $r{$_} // "--undef--" for @fields; }

      Enjoy, Have FUN! H.Merijn
      Dear all, Thank you very much for all your suggestions. Went with fetchall_arrayref for now and does exactly what I need. Will try out the other solutions later as well. Again, thank you!
Re: Retrieving column names from SQL with DBI
by jcb (Parson) on Sep 28, 2020 at 22:43 UTC

    From another perspective, if you need to look up what columns are in a table, you might find the DBI $dbh->table_info method helpful. After gathering a list of available columns, you can build a query with known columns in a known order, or use bind_columns to bind hash values with the column names as keys; the DBI documentation has an example of this in the description for bind_columns.

Re: Retrieving column names from SQL with DBI
by LanX (Cardinal) on Sep 24, 2020 at 23:36 UTC
    > I would like to be able to do a similar thing, but for an unknown number of columns in the SQL Table.

    Maybe I'm misunderstanding the question, but this looks like a typical application for fetchrow_hashref or fetchall_hashref .

     $hash_ref = $sth->fetchrow_hashref;

    An alternative to fetchrow_arrayref . Fetches the next row of data and returns it as a reference to a hash containing field name and field value pairs. Null fields are returned as undef values in the hash.

    $hash_ref = $sth->fetchall_hashref($key_field);

    The fetchall_hashref method can be used to fetch all the data to be returned from a prepared and executed statement handle. It returns a reference to a hash containing a key for each distinct value of the $key_field column that was fetched. For each key the corresponding value is a reference to a hash containing all the selected columns and their values, as returned by fetchrow_hashref().

    Cheers Rolf
    (addicted to the Perl Programming Language :)
    Wikisyntax for the Monastery

Re: Retrieving column names from SQL with DBI
by perlfan (Vicar) on Sep 28, 2020 at 01:02 UTC
    Most DBs provide introspection via SQL, for example MySQL/MariaBD's SHOW command. I know in this particular cast, this represents maybe an additional and unnecessary call. But in the future it's good to know this is exposed via SQL and therefore available to you via DBI just like any other SQL.

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://11122167]
Approved by Perlbotics
Front-paged by Corion
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others studying the Monastery: (7)
As of 2021-02-25 14:22 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found

    Notices?