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

Here's one for oracle

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);
}


On Tue, 27 Jun 2006 10:34:27 -0500, JupiterHost.Net wrote
brian.barto@xxxxxxxxxxxxxxxxxxx wrote:
Hi all. My goal is to get a list of all field names and data types for
those fields in any given table in a mysql database. From reading the
DBI documentation, the only way I've been able to do this is by
preparing and executing a query against a table first. Then I would use

Instead of trying to hack together the data bases on an assumed
behavior and pseodo query, do a query that actually actually asks
for what you want.

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

$sth->{NAME} and $sth->{TYPE} to get the field names and their
respective data types. it looks like this:

$sth = $dbh->prepare("SELECT * FROM $table") or die "Can't prepare
statement!\n";
$sth->execute or die "Can not execute statement!\n";
@types = @{$sth->{TYPE}};
@cols = @{$sth->{NAME}};

PS: use strict and warnings or you're going to have headaches when
reusing $sth @types, @cols, etc...


Matthew Dougherty
mdougher@xxxxxxxx
973-325-8556
AIM: mattsei
SKYPE: matthewdougherty_nj

.

Relevant Pages

  • Re: Possible to get field names and types in a table without executing a query?
    ... those fields in any given table in a mysql database. ... DBI documentation, the only way I've been able to do this is by ... Instead of trying to hack together the data bases on an assumed behavior and pseodo query, do a query that actually actually asks for what you want. ...
    (perl.dbi.users)
  • Re: function does not return
    ... Some servers will put slashes on returning data, ... The exception to this is if you called addslashes() before you inserted the data in the MySQL database. ... The ifstatement probably made sense years ago, before the query was assigned the line before. ...
    (alt.php)
  • Re: How do mysql_fetch_assoc/mysql_query work?
    ... When I submit a query using PHP I ... at least the data I want) in an array. ... I submit a query to a MySQL database it goes away, ... What language are you using? ...
    (comp.lang.php)
  • Re: How do mysql_fetch_assoc/mysql_query work?
    ... Jerry Stuckle wrote: ... When I submit a query using PHP I ... Maybe you don't want a 2D array? ... I submit a query to a MySQL database it goes away, ...
    (comp.lang.php)
  • Getting this week from database?
    ... Dear Folks and Friends, ... I screwed up somewhere and had this code working before, but now, ... I have a simple MySql database which has ... the query returns no data at all. ...
    (alt.php)