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

------_=_NextPart_001_01C699F7.A9E61529
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

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
$sth->{NAME} and $sth->{TYPE} to get the field names and their
respective data types. it looks like this:
=20
$sth =3D $dbh->prepare("SELECT * FROM $table") or die "Can't prepare
statement!\n";
$sth->execute or die "Can not execute statement!\n";
@types =3D @{$sth->{TYPE}};
@cols =3D @{$sth->{NAME}};
=20
This seams like wasted overhead since I don't use the results of the
query. Also, since my query is "SELECT * FROM $table", which dumps all
data in the table, you may understand my concern for wasted overhead
when dealing with tables of a significant size.
=20
Is there a way to get table names and data types without executing a
query? If not, is there a query I can use that will... say... only
return the first row (without WHERE clauses), to minimize the overhead?
=20
Thanks,
Brian

------_=_NextPart_001_01C699F7.A9E61529--

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

brian.barto@spectrum-health.org 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
> $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}};
>
> This seams like wasted overhead since I don't use the results of the
> query. Also, since my query is "SELECT * FROM $table", which dumps all
> data in the table, you may understand my concern for wasted overhead
> when dealing with tables of a significant size.
>
> Is there a way to get table names and data types without executing a
> query? If not, is there a query I can use that will... say... only
> return the first row (without WHERE clauses), to minimize the overhead?

I'm not sure what your objection to WHERE clauses is... You could do
something like SELECT * FROM $table WHERE 0=1.

DBI also has table_info() and column_info() methods which might be useful to
you.

Ronald

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

I would try to use SELECT * FROM TABLE WHERE 0=1. That has no result
rows, but it should deliver the column information. By the way, most SQL
databases have a way to query some special tables or view to find
information about a table. Consult the database manual for that.

Alexander

On 27.06.2006 16:40, brian.barto@spectrum-health.org 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
>$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}};
>
>This seams like wasted overhead since I don't use the results of the
>query. Also, since my query is "SELECT * FROM $table", which dumps all
>data in the table, you may understand my concern for wasted overhead
>when dealing with tables of a significant size.
>
>Is there a way to get table names and data types without executing a
>query? If not, is there a query I can use that will... say... only
>return the first row (without WHERE clauses), to minimize the overhead?
>
>Thanks,
>Brian
>
>
>


--
Alexander Foken
mailto:alexander@foken.de http://www.foken.de/alexander/

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

=20
> brian.barto@spectrum-health.org wrote:
> >=20
> > Hi all. My goal is to get a list of all field names and=20
> data types for=20
> > those fields in any given table in a mysql database. From=20
> reading the=20
> > DBI documentation, the only way I've been able to do this is by=20
> > preparing and executing a query against a table first. Then I would=20
> > use $sth->{NAME} and $sth->{TYPE} to get the field names and their=20
> > respective data types. it looks like this:
> >=20
> > $sth =3D $dbh->prepare("SELECT * FROM $table") or die "Can't prepare =

> > statement!\n"; $sth->execute or die "Can not execute statement!\n";=20
> > @types =3D @{$sth->{TYPE}}; @cols =3D @{$sth->{NAME}};
> >=20
> > This seams like wasted overhead since I don't use the=20
> results of the=20
> > query. Also, since my query is "SELECT * FROM $table",=20
> which dumps all=20
> > data in the table, you may understand my concern for wasted=20
> overhead=20
> > when dealing with tables of a significant size.
> >=20
> > Is there a way to get table names and data types without=20
> executing a=20
> > query? If not, is there a query I can use that will... say... only=20
> > return the first row (without WHERE clauses), to minimize=20
> the overhead?
>=20
> I'm not sure what your objection to WHERE clauses is... You=20
> could do something like SELECT * FROM $table WHERE 0=3D1.

Tried it and it works just fine. I guess I specified no where clause
because I thought a value of a known field would have to be greater,
lesser, or equal to something and I wouldn't be able to depend on the
value of the fields, let alone even knowing their names. Didn't know you
could do THAT, though.

So what does "WHERE 0=3D1" actually mean? Just curious. :)

- Brian

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

brian.barto@spectrum-health.org wrote:
>
> So what does "WHERE 0=1" actually mean? Just curious. :)

It means select all rows where zero is equal to one. So it always returns
zero rows. :)

Ronald

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

On 27.06.2006 17:03, brian.barto@spectrum-health.org wrote:

>Tried it and it works just fine. I guess I specified no where clause
>because I thought a value of a known field would have to be greater,
>lesser, or equal to something and I wouldn't be able to depend on the
>value of the fields, let alone even knowing their names. Didn't know you
>could do THAT, though.
>
>So what does "WHERE 0=1" actually mean? Just curious. :)
>
>- Brian
>
>
>
Exactly that. "Dear SQL Engine, please give me all rows where zero is
one." You could also have used "WHERE 'dog' = 'cat'" or "WHERE
'red'='blue'". It's just a twisted way of saying "give me no rows".
SQL's SELECT bases on something called "set theory"
(http://en.wikipedia.org/wiki/Set_theory), which means that you have to
find a way to separate all the rows in your table / view into wanted
(the result set) and unwanted ones. Asking for "WHERE 0=1" just makes
sure that your result set is empty.

Alexander


--
Alexander Foken
mailto:alexander@foken.de http://www.foken.de/alexander/

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

brian.barto@spectrum-health.org 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...

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@spectrum-health.org 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@inch.com
973-325-8556
AIM: mattsei
SKYPE: matthewdougherty_nj

Re: Possible to get field names and types in a table without executinga 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 :)

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

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

------=_Part_33012_5855663.1151437559236
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

On 6/27/06, Matthew Persico wrote:
>
> So now I need one for every database?


Yes - and there a DBMS that make you do that the hard way, with raw access
to the system catalog.

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.



It is much more easily portable.


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.
>



--
Jonathan Leffler #include
Guardian of DBD::Informix - v2005.02 - http://dbi.perl.org
"I don't suffer from insanity - I enjoy every minute of it."

------=_Part_33012_5855663.1151437559236--

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

------=_Part_31969_5897949.1151437791906
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

On 6/27/06, brian.barto@spectrum-health.org
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
> $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}};
>
> This seams like wasted overhead since I don't use the results of the
> query. Also, since my query is "SELECT * FROM $table", which dumps all
> data in the table, you may understand my concern for wasted overhead
> when dealing with tables of a significant size.
>
> Is there a way to get table names and data types without executing a
> query? If not, is there a query I can use that will... say... only
> return the first row (without WHERE clauses), to minimize the overhead?
>
> Thanks,
> Brian
>
>

iirc, you can use $dbh->table_info() or tables() or type_info() functions to
pull that information without having to do a hack where you select 0 rows
just to poll for column names.

Right?

-Jordan

------=_Part_31969_5897949.1151437791906--

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

On 6/27/06, Jordan Sissel wrote:

>
> iirc, you can use $dbh->table_info() or tables() or type_info() functions to
> pull that information without having to do a hack where you select 0 rows
> just to poll for column names.
>
> Right?

Yes. Unless you want the information for a particular query you are
executing. Then use the hack method.

But I've jsut done some tests and discovered that $sth->{TYPE} returns
numbers which then have to be applied to $dbh->type_info(). And that
function will return multiple entries for many types, which may not be
useful...

For accuracy on single tables, use the functions. For queries, use the hack.


>
> -Jordan
>
>


--
Matthew O. Persico

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

> It is much more easily portable.
>
>
> 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.

How can you gaurantee all DB engines will return the column names with
no value on an empty query?

For instance I'd expect fetchall_hashref() to return {} on a query with
no results...

It'd be better to use the DB engine's built in tools, perhaps
abstracting the engine specific guts into a method that does what it
needs for the handle's engine and they all return the same hash...

*If* your app needs support every DB that is...

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

Matthew Persico wrote:

> So now I need one for every database?

For every database you need to support yes, not all engines will return
the exact same data with a query if no results and that also not in the
same format.

But each database is 00% gauranteed to support what it documents it
supports and return it in the format it has documented it returns it in :)

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

JupiterHost.Net [mailto:mlists@jupiterhost.net] wrote:
>
> > It is much more easily portable.
> >
> >
> > 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.
>
> How can you gaurantee all DB engines will return the column names with
> no value on an empty query?
>
> For instance I'd expect fetchall_hashref() to return {} on a query with
> no results...

It doesn't matter what fetchall_hashref() returns. You get the list of
columns from $sth->{NAME}.

Ronald