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

2006-03-23 Thread 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)

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

2006-03-22 Thread Jim Dodgen 

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

Thanks

Jim