How can I determine the column type with PDO?

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 solu开发者_StackOverflow中文版tion.

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

Take a look at this method: PDOStatement->getColumnMeta

This is how I did 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

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

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.

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>

If you're working with Postgres:

select
    CHARACTER_MAXIMUM_LENGTH,
    COLUMN_NAME,
    IS_NULLABLE,
    COLUMN_DEFAULT,
    NUMERIC_PRECISION,
    NUMERIC_SCALE,
    UDT_NAME 
from
    INFORMATION_SCHEMA.COLUMNS 
where
    TABLE_NAME='table_name'