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 <[EMAIL PROTECTED]> wrote:
Matthew Dougerty wrote:
> Here's one for oracle
nice :)
> sub GetOracleFieldLengths
> {
> my ($TABLE)[EMAIL PROTECTED];
> 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
