How can I retrieve SQL field names of a temp table using Perl?

Question

Below is the code I'm using to run the query, parse the result set, and parse the rows (respectively)

$exec_ret = $DBS->SQLExecSQL($STMT);

while ($DBS->SQLFetch() == *PLibdata::RET_OK)
{
      $rowfetch = $DBS->{Row}->GetCharValue($colname[$i]);
}

Can I grab the column/field name of a temp table using similar syntax? $colname[$i] is predefined at the top to hold the column/field names. This is hard-coded right now, but I would rather automate it by pushing values into $colname inside of a loop that runs before the rows are parsed.

Answer 1

Try running SHOW TABLE yourtable and treating it as if it were a SELECT.

Answer 2

What module are you using for database access? I don't recognize the method names.

If you're using DBI, you can get the column names from the statement handle after executing it:

my $sth = $dbh->prepare($STMT);
$sth->execute;
my $columns = $sth->{NAME_uc};

while (my $row = $sth->fetch) {
  for my $i (0 .. $#$row) {
    print "$columns->[$i]: $row->[$i]\n";
  }
  print "\n";
}

There are 3 versions of the column names: NAME gives the column names as the database returns them, NAME_lc converts them to all lower case, and NAME_uc converts them to all upper case. If you care about database independence, I suggest you avoid NAME and use one of the other two.