Getting the length of a column with Doctrine DBAL

Question

I'm using the Doctrine DBAL project to get the length of the column in the table below (in this case the integer has a length of 8). I don't use the ORM and I don't have the schema defined in YML or anything.

CREATE TABLE IF NOT EXISTS `mytable` (
  `id` int(8) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

While looking for an answer I noticed that depending on the parameter type there seem to be a couple of properties that give some useful information; length, precision and scale. The following code lists some column properties but not the length of 8 that I'm looking for. It's quite surprising to me that it's so hard to find this common column property.

$sm = $connection->getSchemaManager(); // $connection is assumed to exist (for e.g. via $this->container->get('database_connection') in Symfony)
foreach ($sm->listTableColumns('mytable') as $column) {
    print_r($column->toArray());
}

Output:
Array
(
    [name] => id
    [type] => Doctrine\DBAL\Types\IntegerType Object
        (
        )

    [default] => 
    [notnull] => 1
    [length] => 
    [precision] => 10
    [scale] => 0
    [fixed] => 
    [unsigned] => 
    [autoincrement] => 1
    [columnDefinition] => 
    [comment] => 
)

user163588 user163588 · Accepted Answer · 2013-05-17T08:34:54

After some Doctrine DBAL digging I found that this is not possible using the library. Schema managers simply discard this information after retrieval. Unfortunately there is no way to access the required functions within the schema manager classes to provide generic functionality for this because they are protected and of course I don't want to make changes to the library. I ended up executing the following SQL for MySQL (which is probably not portable between platforms but that's fine for now).

SELECT COLUMN_NAME AS Field, COLUMN_TYPE AS Type FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = 'mydatabase' AND TABLE_NAME = 'mytable';

Getting the length of a column with Doctrine DBAL

1 Answers