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

So now I need one for every database?

Despite the prior thread entry refering to

select * from table where 0 = 1

as a hack in the 'bad' sense, I suggest that this is a hack in the 'good' sense.

Suppose you have a real query:

select a.foo, b.bar.c.baz
from
a, b, c
where .....

The 0= 1 method works for that too. Contrast that with parsing the
from clause and the where clause to create a tabel catalog query. Yuk.



On 6/27/06, JupiterHost.Net <mlists@xxxxxxxxxxxxxxx> wrote:


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 :)



--
Matthew O. Persico
.

Relevant Pages

  • Re: Oh Boss . . . Where are you?
    ... in your case it is "Hack". ... I guess Mr. Lundahl ... did not have a "Specific Performance" clause in his contract as he ...
    (rec.audio.tubes)
  • Re: Struct Layout in C# to Delphi.Net
    ... What the hack is that? ... And how i go about Delphi? ... * add "System.Runtime.InteropServices" the the uses clause ... TMyClass = class....... ...
    (microsoft.public.dotnet.languages.csharp)