Re: Possible to get field names and types in a table without executing a query?



Matthew Dougerty wrote:

Here's one for oracle

nice :)

sub GetOracleFieldLengths
{
my ($TABLE)=@_;
my (%FIELDLENGTHS, %FIELDTYPES);
if ($TABLE=~/\.(\S+)$/)
{
$TABLE=$1;
}
my $fieldlengths=$dbh->prepare("SELECT COLUMN_NAME, DATA_TYPE, DATA_LENGTH from ALL_TAB_COLUMNS where TABLE_NAME=?");
$fieldlengths->execute(uc($TABLE));
while (my $columns=$fieldlengths->fetchrow_hashref)
{
$FIELDLENGTHS{$$columns{COLUMN_NAME}}=$$columns{DATA_LENGTH};
$FIELDTYPES{$$columns{COLUMN_NAME}}=$$columns{DATA_TYPE};
PrintDebug("DBDB $$columns{COLUMN_NAME} has value $FIELDLENGTHS{$$columns{COLUMN_NAME}} and type $FIELDTY
PES{$$columns{COLUMN_NAME}}\n");
}
return (\%FIELDLENGTHS, \%FIELDTYPES);
}

Oi, how about some "Perl Best Practices" :) Those caps are killer! Do they charge you for whitespace?

And two hashref's seem bulky to....

sub get_oracle_field_length_hashref {
my ($dbh, $table, $debug) = @_;

my $fields = {};

if($table =~ m{ \.(\S+) }xms) {
$table = $1;
}

my $sth = $dbh->prepare(
'SELECT COLUMN_NAME, DATA_TYPE, DATA_LENGTH '
. 'from ALL_TAB_COLUMNS where TABLE_NAME=?';
);
$sth->execute( uc($table) );

while( my $cols = $sth->fetchrow_hashref() ) {
$fields->{ $cols->{'COLUMN_NAME'} } = {
'length' => $cols->{'DATA_LENGTH'},
'type' => $cols->{'DATA_TYPE'},
};
print Dumper $fields->{ $cols->{'COLUMN_NAME'} } if $debug;
}

print Dumper $fields if $debug;
return $fields;
}


Isn't that much nicer to look at and easier to tell what is going on?

managin one hash is way easier than two that are supposed to have the same keys...

And trust me, in a year when you have to revisit it, you'll be reeeaaal glad its that way :)

Plus its just a few characters away from being a $dbh method:

print Dumper $dbh->get_oracle_field_length_hashref('users');

On Tue, 27 Jun 2006 10:34:27 -0500, JupiterHost.Net wrote

For instance with MySQL:

print Dumper $dbh->select_all_arrayref('SHOW TABLE STATUS FROM "db" LIKE "table"');

# same as SHOW COLUMNS FROM db.table
print Dumper $dbh->select_all_arrayref('DESCRIBE db.table');

HTH :)
.