DBI column_info question

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

I'm trying to use the DBI::column_info method to get details about the columns in a mySQL table -- which mostly works but I confused by ORDINAL_POSITION: which the docs define as "The column sequence number (starting with 1)." I was expecting (1,2,3) from my 3 column test table but I get (1,1,1). Am I missing something?

 
    my $sth1 = $dbh->prepare("SELECT * FROM $table");
        $sth1->execute() or die $DBI::errstr;
        my $col_names = $sth1->{NAME};
    
    foreach my $column ( @{$col_names}) {
        my $sth = $dbh->column_info(undef, undef, $table, $column);
    
        foreach my $col_info ($sth->fetchrow_hashref) {
            print " $col_info->{'ORDINAL_POSITION'}  $col_info->{'COLU
+MN_NAME'} \n";
                }
      }
[download]

results ..

 1 id 
 1 name 
 1 start
[download]

Comment on DBI column_info question Select or Download Code

Replies are listed 'Best First'.
Re: DBI column_info question by runrig (Abbot) on Mar 20, 2015 at 19:39 UTC
It's a bug in DBD::mysql. If you got column info on the whole table, it would correctly return the position of each column. But since you are only getting info on one column at a time, the count starts with '1' each time. In the code, the ordinal position is a simple incremented variable for each column returned.	[reply]
Re^2: DBI column_info question by FryingFinn (Beadle) on Mar 26, 2015 at 16:54 UTC
thanks for pointing me in right direction. changed my code to... `my $sth = $dbh->column_info(undef, undef, 'table1', "%"); my $col_info = $sth->fetchall_hashref('ORDINAL_POSITION');` [download] which gives exactly what I wanted. "thx" x 100	[reply] [d/l]
Re: DBI column_info question by ww (Archbishop) on Mar 20, 2015 at 19:03 UTC
What do you get if you print the all components of `$col_info->{...}`? Spirit of the Monastery	[reply] [d/l] [select]