Getting metadata through DBI

[blakekr]

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"
$sth->execute();
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?

 

[steve1]

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

 

[steve1]

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.

Steve