Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Getting metadata through DBI

Getting metadata through DBI

Getting metadata through DBI

I sent this to the DBI mailing list:

"I've just gone through the program shown in the O'Reilly DBI on page 149, or, the $dbh->table_info() query.

What that doesn't grab, but I would really like to capture, is the kind of "Type" you get when you give mySQL a DESCRIBE (column) command. In particular, I'd like to be able to capture all the alternatives coded into an ENUM or SET column. Can it be done? Any hints?"

I got this in response:

Try the 'LISTFIELDS table_name' query and execute it. That will return no result rows, but the attributes will work.

$sth = $dbh->prepare("LISTFIELDS table_name");
while ($sth->fetchrow_array()) {
  print $sth->{NAME}." - ".$sth->{TYPE};

Unfortunately, that didn't work for me -- I got no output from the fetchrow_array command.

Does it work for anyone else? Does anyone have another approach for getting detailed TYPE data through DBI? Help help help?

RE: Getting metadata through DBI

Describe is an oracle(or mysql) command.  I don't know about listfields.  You have two choices:

1. Rely on the DBI functions.

2. Have a different query for each database(and some might not have queries.  For sybase(and MS/SQL), it is sp_help table(a stored procedure).

RE: Getting metadata through DBI

That statement about mysql and sybase isn't exactly true.  Try this:

select a.name,c.name,a.length,a.prec,a.scale,a.isnullable from syscolumns a,sysobjects b,systypes c where a.id=b.id and c.xtype=a.xtype and b.name='CUSTOMERS' order by colorder

The above statement gives metadata (field name, type, length, scale, nullable indicator) from M/S SQL 2000 for a table called CUSTOMERS.  It SHOULD work for earlier versions, and sybase as well.  

If you want, you can change the condition for b.name to "b.type='U'", and add b.name to the return values. This would give you metadata for every user table in the database.


Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close