Re: [sqlite] retreving the column names for a query (PERL)

[Scott Leighton]

On Wednesday 22 March 2006 9:43 pm, Jim Dodgen wrote:
> for a query like
>
> select * from a join b on a.x = b.z
>
> anyone know how to get all the column names of the fields that would be
> returned from the query?
>
> I am using the DBD::SQLite PERL module
>


  This script shows you how to get the column info.

   Scott



 use DBI();
  use strict;

  my $dbname = $ARGV[0] || die "usage $0  <dbname>\n";

  my $dbh=DBI->connect("DBI:SQLite2:dbname=$dbname",
                   "","",
                   {'RaiseError'=>1});

  my $sqlversion=$DBD::SQLite2::VERSION;
  my $dbiversion=$DBI::VERSION;

  print "Running DBI $dbiversion and DBD::SQLite2 $sqlversion\n";

  my @tables = $dbh->tables();

  foreach my $table (@tables) {
     print "\n\n----------------------\nTable -->$table 
\n----------------------\n";
     my $sth2 = $dbh->prepare("select * from $table limit 1") or die 
$dbh->errstr;
     $sth2->execute;

     my $fields = $sth2->{NUM_OF_FIELDS};
     print "Number of columns = $fields\n";
    
     print "Column Name                     Type  Precision  Scale  Nullable?
\n";
     print "------------------------------  ----  ---------  -----  
---------\n\n";
    
     ### Iterate through all the fields and dump the field information
     for ( my $i = 0 ; $i < $fields ; $i++ ) {
    
        my $name = $sth2->{NAME}->[$i];
    
        ### Describe the NULLABLE value
        my $nullable = ("No", "Yes", "Unknown")[ $sth2->{NULLABLE}->[$i] ];
        ### Tidy the other values, which some drivers don't provide
        my $scale = $sth2->{SCALE}->[$i];
        my $prec  = $sth2->{PRECISION}->[$i];
        my $type  = $sth2->{TYPE}->[$i];
    
        ### Display the field information
        printf "%-30s %5d      %4d   %4d   %s\n",
                $name, $type, $prec, $scale, $nullable;
      }
  }


  $dbh->disconnect();
  exit(0);

-- 
Vir prudens non contra ventum mingit 
POPFile, the OpenSource EMail Classifier http://popfile.sourceforge.net/
Linux 2.6.11.4-21.11-default x86_64
SuSE Linux 9.3 (x86-64)