How can I determine the column type with PDO?

Question

Hello folks.

I need a way to determine the type of a database column (varchar/numeric/date/...) when reading from the DB with PDO.

When fetching values from the DB, PDO produces only string values, regardless of the actual type of the table column.

Is there any non driver specific way to get this information? I know that there are SQL statements that retrieve the types for any given table but i'd prefer a more generic solution.

EDIT: PDOStatement::getColumnMeta() is of no use to me, because it's not supported by the PDO driver I use at the moment (Oracle).

Answer 1

Take a look at this method: PDOStatement->getColumnMeta

Answer 2

It's marked as "experimental", but the PDOStatement->getColumnMeta method looks like it will do what you want.

Answer 3

This is how I done it in my WraPDO class:

$tomet = $sth->getColumnMeta($column_index);
$tomet['type'] = $this->_translateNativeType($tomet['native_type']);

private function _translateNativeType($orig) {
    $trans = array(
        'VAR_STRING' => 'string',
        'STRING' => 'string',
        'BLOB' => 'blob',
        'LONGLONG' => 'int',
        'LONG' => 'int',
        'SHORT' => 'int',
        'DATETIME' => 'datetime',
        'DATE' => 'date',
        'DOUBLE' => 'real',
        'TIMESTAMP' => 'timestamp'
    );
    return $trans[$orig];
}

$sth: PDOStatement->getColumnMeta

Answer 4

I wrote a function a while ago which extracted table column information. I ended up doing something like this:

SHOW COLUMNS FROM <table> WHERE Field = ?

For a typical primary key, that produces this:

+-------+---------+------+-----+---------+----------------+
| Field | Type    | Null | Key | Default | Extra          |
+-------+---------+------+-----+---------+----------------+
| id    | int(11) | NO   | PRI | NULL    | auto_increment |
+-------+---------+------+-----+---------+----------------+

I then parsed the output into a usable array. However, that was pre-PHP 5.1.0. Now you can probably use PDOStatement->getColumnMeta.

Answer 5

If you're working with Oracle:

select COLUMN_NAME,
       DATA_TYPE,
       DATA_LENGTH,
       DATA_PRECISION,
       DATA_SCALE
from user_tab_cols
where table_name = '<Table Name>'
order by column_id

but it isn't portable

Many SQL flavours also have

DESCRIBE <Table Name>