Tableinfo for Oracle

Hi Tomas, You will find attached a version of tableinfo() for oci8 which works similarly to the mySQL implementation. You will notice a few differences though: 1. the returned array is different. It has new keys like 'nullable', 'default', 'format' which will prove very useful for the datagrid 'edit mode' stuff. I would like to see them implemented for mySQL too. I got rid of the 'flags' key because this does not apply to Oracle. 2. If there are, in the database, fields with the same name, some returned data will possibly be incorrect. This is because of the lack of PHP Oracle functions for getting the column's associated table (sort of mysql_tablename function). I don't think there is anything I can do about it. This function should be added to PHP as soon as possible. I never give the same name to my fields and always prefix them with some letters but I know some people who don't do that. It will be a problem for them. They should not call the tableinfo method from a DB_Result object. Instead, they can pass it a string with the table name they want metadata for. Maybe we should return a PEAR ERROR if there are more than one returned row for a column_name. This way, users will be informed that there are problems with their DB. I am waiting for your comments. Bertrand Mansion Mamasam

function tableInfo($result, $mode = null) { $count = 0; $res = array(); /* * depending on $mode, metadata returns the following values: * * - mode is false (default): * $res[]: * [0]["table"] table name * [0]["name"] field name * [0]["type"] field type * [0]["len"] field length * [0]["nullable"] field can be null (boolean) * [0]["format"] field precision if NUMBER * [0]["default"] field default value * * - mode is DB_TABLEINFO_ORDER * $res[]: * ["num_fields"] number of fields * [0]["table"] table name * [0]["name"] field name * [0]["type"] field type * [0]["len"] field length * [0]["nullable"] field can be null (boolean) * [0]["format"] field precision if NUMBER * [0]["default"] field default value * ['order'][field name] index of field named "field name" * The last one is used, if you have a field name, but no index. * Test: if (isset($result['order']['myfield'])) { ... * * - mode is DB_TABLEINFO_ORDERTABLE * the same as above. but additionally * ["ordertable"][table name][field name] index of field * named "field name" * * this is, because if you have fields from different * tables with the same field name * they override each * other with DB_TABLEINFO_ORDER * * you can combine DB_TABLEINFO_ORDER and * DB_TABLEINFO_ORDERTABLE with DB_TABLEINFO_ORDER | * DB_TABLEINFO_ORDERTABLE * or with DB_TABLEINFO_FULL */ // if $result is a string, we collect info for a table only if (is_string($result)) { $q_fields = "select column_name, data_type, data_length, data_precision, nullable, data_default from user_tab_columns where table_name='$result' order by column_id"; if (!$stmt = OCIParse($this->connection, $q_fields)) { return $this->oci8RaiseError(); } if (!OCIExecute($stmt, OCI_DEFAULT)) { return $this->oci8RaiseError($stmt); } while (OCIFetch($stmt)) { $res[$count]['table'] = $result; $res[$count]['name'] = @OCIResult($stmt, 1); $res[$count]['type'] = @OCIResult($stmt, 2); $res[$count]['len'] = @OCIResult($stmt, 3); $res[$count]['format'] = @OCIResult($stmt, 4); $res[$count]['nullable'] = (@OCIResult($stmt, 5) == 'Y') ? true : false; $res[$count]['default'] = @OCIResult($stmt, 6); if ($mode & DB_TABLEINFO_ORDER) { $res['order'][$res[$count]['name']] = $count; } if ($mode & DB_TABLEINFO_ORDERTABLE) { $res['ordertable'][$res[$count]['table']][$res[$count]['name']] = $count; } $count++; } $res['num_fields'] = $count; @OCIFreeStatement($stmt); } else { // else we want information about a resultset if ($result === $this->last_stmt) { $count = @OCINumCols($result); for ($i=0; $i<$count; $i++) { $res[$i]['name'] = @OCIColumnName($result, $i+1); $res[$i]['type'] = @OCIColumnType($result, $i+1); $res[$i]['len'] = @OCIColumnSize($result, $i+1); $q_fields = "select table_name, data_precision, nullable, data_default from user_tab_columns where column_name='".$res[$i]['name']."'"; if (!$stmt = OCIParse($this->connection, $q_fields)) { return $this->oci8RaiseError(); } if (!OCIExecute($stmt, OCI_DEFAULT)) { return $this->oci8RaiseError($stmt); } OCIFetch($stmt); $res[$i]['table'] = OCIResult($stmt, 1); $res[$i]['format'] = OCIResult($stmt, 2); $res[$i]['nullable'] = (OCIResult($stmt, 3) == 'Y') ? true : false; $res[$i]['default'] = OCIResult($stmt, 4); OCIFreeStatement($stmt); if ($mode & DB_TABLEINFO_ORDER) { $res['order'][$res[$i]['name']] = $i; } if ($mode & DB_TABLEINFO_ORDERTABLE) { $res['ordertable'][$res[$i]['table']][$res[$i]['name']] = $i; } } $res['num_fields'] = $count; return $res; } else { return $this->raiseError(DB_ERROR_NOT_CAPABLE); } } }