ZF-745: Request solution for result set metadata

Issue Type: New Feature Created: 2007-01-12T15:38:55.000+0000 Last Updated: 2009-01-09T14:10:30.000+0000 Status: Closed Fix version(s): - 1.8.0 (30/Apr/09)

Reporter: Bill Karwin (bkarwin) Assignee: Ralph Schindler (ralph) Tags: - Zend_Db

Related issues: Attachments:


Currently the Zend_Db_Adapter_Abstract interface supports fetching data values of a result set, but the metadata is not available. So there no convenient way to determine the original SQL datatype of a column; all column values are mapped to PHP scalars.

This is a feature request to add a method to Zend_Db_Adapter_Abstract, and implement in each respective subclass, to fetch a special result-set consisting of metadata of the result set.


Posted by Bill Karwin (bkarwin) on 2007-01-12T15:46:37.000+0000


PDO has a method getColumnMeta() but it is noted as "experimental" in the PDO docs. Not all PDO drivers are guaranteed to support it. I haven't checked the status of support for this method in the PDO drivers we use in ZF.

The PDO getColumnMeta() method returns an associative array for a single column (specified by the 0-indexed ordinal number of the column), with the following keys: - native_type - driver:decl_type - flags - name - len - precision - pdo_type

MySQLi stmts have a result_metadata() method, so this could support result set metadata. The result_metadata() method returns a result-set object, which is an iterable collection of fields. Each field is an object. The documentation does not specify the members of this object.

The OCI API for Oracle has individual methods for metadata. So this could support result set metadata. The methods are: - oci_field_is_null() - oci_field_name() - oci_field_precision() - oci_field_scale() - oci_field_size() - oci_field_type_raw() - oci_field_type()

Posted by Bill Karwin (bkarwin) on 2007-01-12T15:46:42.000+0000

I would propose a new alternative fetch() method for Zend_Db_Adapter, to retrieve data combined with metadata in a canonical form. The adapter is responsible for assembling the metadata into this form.

Then a Zend_Db_Table_Row would also have a new method getMetadata() which returns an associative array mapping column names to a further associative array with attributes for the respective column. Zend_Db_Table_Row would always fetch the metadata for a query from the adapter. This might cause a slight performance cost depending on the adapter's implementation, but it is likely to be insignificant compared to the cost of the query itself.

Posted by Bill Karwin (bkarwin) on 2007-03-13T15:09:26.000+0000

Reset components to just Zend_Db.

Posted by Fabrizio Balliano (balliano) on 2007-06-14T03:26:30.000+0000

I wrote some considerations about this and other things on my blog:

metadata retrieving should be untied from the zend_db_table and should work using the getcolumnmeta function, that should be released as stable (not experimental like now).

a feature that needs to be added is the table name of the field, absolutely needed when writing general code

Posted by Wil Sinclair (wil) on 2008-03-25T20:43:57.000+0000

Please categorize/fix as needed.

Posted by Wil Sinclair (wil) on 2008-04-18T13:11:58.000+0000

This doesn't appear to have been fixed in 1.5.0. Please update if this is not correct.

Posted by Wil Sinclair (wil) on 2008-12-04T12:53:17.000+0000

Reassigning as Ralph is the maintainer of Zend_Db

Posted by Ralph Schindler (ralph) on 2009-01-09T12:30:29.000+0000

How does Zend_Db_Adapter_Abstract::describeTable() not cover this use case?


Posted by Bill Karwin (bkarwin) on 2009-01-09T13:25:58.000+0000

What are the data types, string lengths, nullability, etc. of columns returned by the following query:

<pre class="highlight">
SELECT CONCAT(a.first_name, ' ', a.last_name) AS full_name, 
  GROUP_CONCAT(b.bug_id) AS bug_id_list,
  COUNT(*) AS num_bugs,
  SUM(b.status = 'OPEN')/COUNT(*) AS percent_of_open_bugs
FROM bugs b JOIN accounts a ON a.account_id = b.reported_by
GROUP BY a.account_id;

Result set metadata is about query results, not tables on disk. Queries may have expressions of different data types than any that occur in the underlying tables. If you capture that query and create a temp table based on it, you can see what I mean:

<pre class="highlight">
CREATE TEMPORARY TABLE foo   SELECT ...query above...
| Field                | Type          | Null | Key | Default | Extra |
| full_name            | varchar(41)   | YES  |     | NULL    |       | 
| bug_id_list          | longblob      | YES  |     | NULL    |       | 
| num_bugs             | bigint(21)    | NO   |     | 0       |       | 
| percent_of_open_bugs | decimal(27,4) | YES  |     | NULL    |       | 

I logged this feature request a long time ago because the PDOStatement interface had a new function getColumnMeta() that Zend_Db_Statement didn't implement.

However, that PDO statement is experimental and most (all?) PDO drivers don't implement it. Surfacing result set metadata through Zend_Db_Statement is therefore impossible, since the underlying drivers don't supply the information. So I would recommend postponing this Zend_Db feature request indefinitely, or simply close it as "won't fix."

Posted by Ralph Schindler (ralph) on 2009-01-09T14:10:29.000+0000

Marking as wont fix.

Since the votes are low, and the underlying delivery mechanism is marked as experimental, I see no other option. Lets re-evaluate when the new PDO comes around. ;)

Have you found an issue?

See the Overview section for more details.


© 2006-2016 by Zend, a Rogue Wave Company. Made with by awesome contributors.

This website is built using zend-expressive and it runs on PHP 7.