MetaColumns() for a MS SQL view?

Question

I am trying to use ADODB to work with a MS SQL database containing views. MetaColumns() works well with tables, but returns an empty array when I use a view name as the parameter. Further research shows that $metaColumnsSQL uses sys.tables object for resolving column names, so it doesn't appear to be intended for views. Is there a way to obtain column names for a view object?

Answer 1

ADOdb cannot provide a metaColumns() object for a view because it's basis is the interrogation of the schema for the objects associated with a single table.

You can emulate metaColumns() with a view by using the fetchField() method as follows, using NorthWind:

$SQL = " CREATE VIEW vEmpTerritories AS
    SELECT employees.employeeId, EmployeeTerritories.TerritoryId
      FROM employees, EmployeeTerritories 
     WHERE EmployeeTerritories.employeeId = employees.employeeId";


$db->execute($SQL);
$SQL = "SELECT * FROM vEmpTerritories"; 

$f = $db->execute($SQL);

$recordSet = $db->Execute($SQL);
$cols = $recordSet->fieldCount();
for($i=0;$i<$cols;$i++){
    $fld = $recordSet->FetchField($i);
    print_r($fld);

}

This would return an array of ADOfieldObjects with basic information about each column:

ADOFieldObject Object
(
    [name] => employeeId
    [max_length] => 
    [type] => int
    [column_source] => employeeId
)
ADOFieldObject Object
(
    [name] => TerritoryId
    [max_length] => 20
    [type] => nvarchar
    [column_source] => TerritoryId
)

Unfortunately, the data returned from fetchfield() is not as detailed as from metaColumns but it may be sufficient for your needs.