[solved] A clean way to get at database table/column metadata?

[fredp]

Hi,

I'm looking for a clean (db independent) way of retrieving a database table's metadata, specifically a table's column names and associated datatypes. The adodb lite meta module isn't included in v1.4.1, so calls to the $db->MetaColumns() method are out.  I searched the forums and found some people have tried manually parsing the output of db-specific "SHOW... " statements.  That doesn't seem like a good long term solution.  So, hearing about any other alternatives would be greatly appreciated!

If necessary, I'll create a feature request, but thought I'd check here first.  ;)

Thanks!
Fred

P.S. As a side note: why do I want this?
My client maintains a lot of tabular data in multiple excel spreadsheets. They're currently using a custom app, that I wrote years ago, to generate a directory of static html pages from each spreadsheet file, based on a WYSIWYG template. It works, but they're getting overwhelmed with all the static pages. It's time to move them to a cms and make things simpler for them. The current plan is to provide them with 1) a simple way to import a spreadsheet into a db table and 2) a generic module/plugin that will query a table of their choice and display the data in a table with appropriate headings and pagination.  To do the latter, I need to retrieve a table's column names, for use in headings, and datatypes, for proper formatting.

[Nullig]

You could use a UDT, where you pass the table name and execute a mysql query, like:

  "SHOW COLUMNS FROM `tablename`"

and return the array.

Nullig

[fredp]

You could use a UDT, where you pass the table name and execute a mysql query, like:
   "SHOW COLUMNS FROM `tablename`"
...

I appreciate your comment. But, as I noted in my original post, I'm looking for a long term database server independent (not necessarily mysql) solution. I know about the mysql specific "SHOW COLUMNS" hack, er, I mean "solution".    It is clever and fine as a short term workaround, but it's essentially turning a missing functionality problem in the db API layer into a future maintenance and portability problem for the app. layer. I'd like to be able to reuse the plugin (or UDT) in future projects with different database servers without modification.

Access to table (or result-set) metadata is pretty fundamental to any Dynamic SQL API. It is required for apps/tools that don't have a priori knowledge of table metadata. I realize not everyone needs this, but when you need it... you need it.    

If there isn't a clean way to do what I want now, no problem.  I can submit a Feature Request and plead my case there.  Or, I can start a thread on "Feature Ideas", if people would like to kick around the idea first.

As a sub-question: does anyone know if 2.0 is planning on providing table/result-set metadata access via the 2.0 db API?  I looked at the SVN 2.0 tree and it looks like things are changing in the db area, from 1.4.1, but I'm not sure how far along this is and what is ultimately planned API-wise for 2.0.
 
Thanks again!
Fred

[alby]

As a sub-question: does anyone know if 2.0 is planning on providing table/result-set metadata access via the 2.0 db API?

In this moment 2.0 has full adodb and not adodb_lite

Alby

[fredp]

After reading the "ADOdb Lite" docs, I found an answer to my question in two *_ResultSet methods: FetchField() and FetchCount(). Here's an excerpt from the doc explaining these methods:

Supported Result Set Functions ...
...
FetchField($column_number)
   Returns an object containing the name, type and max_length of the associated field.
   If the max_length cannot be determined reliably, it will be set to -1.
   The column numbers are zero-based. See example 2.

FieldCount( )
   Returns the number of fields (columns) in the record set.
...

With these, it's easy to fetch the column meta data for a query's ResultSet!  The alternative, that I had previously found, the MetaColumns() method (missing in cmsms), only works on tables.  :(  

Fred P.

P.S.  For anyone interested, I found these pages on the "ADOdb Lite" website  helpful in answering my question:
   Support/Unsupported ADOdb Functions: http://adodblite.sourceforge.net/functions.php
   Optional Modules: http://adodblite.sourceforge.net/modules.php