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

On 6/27/06, brian.barto@xxxxxxxxxxxxxxxxxxx <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
$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

Relevant Pages

  • Re: reserved error -1524 access 2007
    ... OT and cod are the same type ... If OT is a query, ... difficult to trace what's going on without details of the field types. ... It might be that JET is getting the data types mixed up. ...
    (microsoft.public.access.queries)
  • Possible to get field names and types in a table without executing a query?
    ... preparing and executing a query against a table first. ... respective data types. ... This seams like wasted overhead since I don't use the results of the ... return the first row, ...
    (perl.dbi.users)
  • Re: Query Too Complex
    ... what data types are in use at each level? ... > the grouping levels, however this is by no means ideal. ... > Do you know of anything I can do to make this report run with the number ... >> how to run the query. ...
    (microsoft.public.access.reports)
  • Re: Selecting records using criteria as arguments for a mathematical operation
    ... Show us your query and the data types of the fields involved. ... error message seems to indicate you're doing something that violates ... In the Totals row of the query builder, have you considered changing the Sales column from GroupBy to Sum? ...
    (comp.databases.ms-access)
  • Re: Two questions: Access and SQL Server/VBA
    ... I have code that loops through the field names on a query and returns ... How is the easiest way to get the data types from the SQL Server ... end database. ... correct data types lengths). ...
    (comp.databases.ms-access)