/**
* @see TableInfo::initColumns()
*/
protected function initColumns()
{
include_once 'creole/metadata/ColumnInfo.php';
include_once 'creole/drivers/odbc/ODBCTypes.php';
ODBCTypes::loadTypeMap($this->conn);
$result = @odbc_columns($this->dblink, $this->dbname, '', $this->name);
if (!$result) {
throw new SQLException('Could not get column names', $this->conn->nativeError());
}
while (odbc_fetch_row($result)) {
$name = odbc_result($result, 'COLUMN_NAME');
$type = odbc_result($result, 'TYPE_NAME');
$length = odbc_result($result, 'LENGTH');
$is_nullable = odbc_result($result, 'NULLABLE');
$default = '';
$precision = odbc_result($result, 'PRECISION');
$this->columns[$name] = new ColumnInfo($this, $name, ODBCTypes::getType($type), $type, $length, $precision, $is_nullable, $default);
}
@odbc_free_result($result);
$this->colsLoaded = true;
}
function &MetaColumns($table)
{
global $ADODB_FETCH_MODE;
$false = false;
if ($this->uCaseTables) {
$table = strtoupper($table);
}
$schema = '';
$this->_findschema($table, $schema);
$savem = $ADODB_FETCH_MODE;
$ADODB_FETCH_MODE = ADODB_FETCH_NUM;
/*if (false) { // after testing, confirmed that the following does not work becoz of a bug
$qid2 = odbc_tables($this->_connectionID);
$rs = new ADORecordSet_odbc($qid2);
$ADODB_FETCH_MODE = $savem;
if (!$rs) return false;
$rs->_has_stupid_odbc_fetch_api_change = $this->_has_stupid_odbc_fetch_api_change;
$rs->_fetch();
while (!$rs->EOF) {
if ($table == strtoupper($rs->fields[2])) {
$q = $rs->fields[0];
$o = $rs->fields[1];
break;
}
$rs->MoveNext();
}
$rs->Close();
$qid = odbc_columns($this->_connectionID,$q,$o,strtoupper($table),'%');
} */
switch ($this->databaseType) {
case 'access':
case 'vfp':
$qid = odbc_columns($this->_connectionID);
#,'%','',strtoupper($table),'%');
break;
case 'db2':
$colname = "%";
$qid = odbc_columns($this->_connectionID, "", $schema, $table, $colname);
break;
default:
$qid = @odbc_columns($this->_connectionID, '%', '%', strtoupper($table), '%');
if (empty($qid)) {
$qid = odbc_columns($this->_connectionID);
}
break;
}
if (empty($qid)) {
return $false;
}
$rs =& new ADORecordSet_odbc($qid);
$ADODB_FETCH_MODE = $savem;
if (!$rs) {
return $false;
}
$rs->_has_stupid_odbc_fetch_api_change = $this->_has_stupid_odbc_fetch_api_change;
$rs->_fetch();
$retarr = array();
/*
$rs->fields indices
0 TABLE_QUALIFIER
1 TABLE_SCHEM
2 TABLE_NAME
3 COLUMN_NAME
4 DATA_TYPE
5 TYPE_NAME
6 PRECISION
7 LENGTH
8 SCALE
9 RADIX
10 NULLABLE
11 REMARKS
*/
while (!$rs->EOF) {
// adodb_pr($rs->fields);
if (strtoupper(trim($rs->fields[2])) == $table && (!$schema || strtoupper($rs->fields[1]) == $schema)) {
$fld = new ADOFieldObject();
$fld->name = $rs->fields[3];
$fld->type = $this->ODBCTypes($rs->fields[4]);
// ref: http://msdn.microsoft.com/library/default.asp?url=/archive/en-us/dnaraccgen/html/msdn_odk.asp
// access uses precision to store length for char/varchar
if ($fld->type == 'C' or $fld->type == 'X') {
if ($this->databaseType == 'access') {
$fld->max_length = $rs->fields[6];
} else {
if ($rs->fields[4] <= -95) {
// UNICODE
$fld->max_length = $rs->fields[7] / 2;
} else {
$fld->max_length = $rs->fields[7];
}
}
} else {
$fld->max_length = $rs->fields[7];
}
$fld->not_null = !empty($rs->fields[10]);
$fld->scale = $rs->fields[8];
$retarr[strtoupper($fld->name)] = $fld;
} else {
if (sizeof($retarr) > 0) {
break;
}
}
$rs->MoveNext();
}
$rs->Close();
//-- crashes 4.03pl1 -- why?
if (empty($retarr)) {
$retarr = false;
}
return $retarr;
}
/**
* ODBC::getFieldNames()
*
* Return the field names of the table
*
* @param string $table: the table where we should fetch the field names from
* @return array
* @access public
* @author Teye Heimans
*/
function getFieldNames($table)
{
$sql = odbc_columns($this->_conn);
$result = array();
$num = odbc_num_fields($sql);
for ($i = 1; $i <= $num; $i++) {
$result[$i - 1] = odbc_field_name($sql, $i);
}
$num = odbc_num_rows($sql);
echo "Aantal rows: {$num}<br />\n";
for ($i = 0; $i <= $num; $i++) {
echo odbc_result($sql, 4) . "<br >\n";
}
return $result;
}
<table border=\"1\">
<tr bgcolor="yellow">
<th>Table Name</th><th>Description</th><th>Columns</th>
</tr>
<tr>
<td><?php
echo $name = odbc_result($tableexec, "TABLE_NAME");
?>
</td>
<td><?php
echo odbc_result($tableexec, "REMARKS");
?>
</td>
<?php
// number of columns. Need to look them up first.
$colexec = odbc_columns($conn, "", "", $name);
?>
<td><?php
echo odbc_num_fields($colexec);
?>
</td>
</tr>
<tr>
<td align="right" colspan="3">
<table border="0" cellpadding="10" cellspacing="0">
<tr>
<td>
<table border="1">
<tr bgcolor="yellow">
<th>Column Name</th><th>Description</th>
<th>Data Type</th><th>Size</th>
function get_cols(&$conn, $table)
{
global $dbname;
$cols = array();
$keys = array();
// $table = 'dbo.'.$table;
$r = odbc_primarykeys($conn, $dnname, '%', $table);
while ($row = odbc_fetch_array($r)) {
$keys[] = $row['COLUMN_NAME'];
}
$r = odbc_columns($conn, $dbname, '%', $table, '%');
while ($row = odbc_fetch_array($r)) {
// print_r($row);
$c = array();
$c['name'] = htmlentities($row['COLUMN_NAME']);
$c['type'] = $row['TYPE_NAME'];
$c['special'] = '';
if (isset($row['COLUMN_SIZE'])) {
$c['special'] = $row['COLUMN_SIZE'];
}
if (isset($row['LENGTH'])) {
$c['special'] = $row['LENGTH'];
}
$c['nn'] = $row['NULLABLE'] == 0;
$c['default'] = str_replace("'", '', $row['COLUMN_DEF']);
$c['pk'] = in_array($row['COLUMN_NAME'], $keys);
$cols[] = $c;
}
return $cols;
}
function &MetaColumns($table)
{
$table = strtoupper($table);
/* // for some reason, cannot view only 1 table with odbc_columns -- bug?
$qid = odbc_tables($this->_connectionID);
$rs = new ADORecordSet_odbc($qid);
if (!$rs) return false;
while (!$rs->EOF) {
if ($table == strtoupper($rs->fields[2])) {
$q = $rs->fields[0];
$o = $rs->fields[1];
break;
}
$rs->MoveNext();
}
$rs->Close();
$qid = odbc_columns($this->_connectionID,$q,$o,strtoupper($table),'%');
*/
$qid = odbc_columns($this->_connectionID);
$rs = new ADORecordSet_odbc($qid);
if (!$rs) {
return false;
}
$retarr = array();
while (!$rs->EOF) {
if (strtoupper($rs->fields[2]) == $table) {
$fld = new ADOFieldObject();
$fld->name = $rs->fields[3];
$fld->type = $this->ODBCTypes($rs->fields[4]);
$fld->max_length = $rs->fields[7];
$retarr[strtoupper($fld->name)] = $fld;
} else {
if (sizeof($retarr) > 0) {
break;
}
}
$rs->MoveNext();
}
$rs->Close();
//-- crashes 4.03pl1 -- why?
return $retarr;
}
getTables(){$res=odbc_tables($this->connection);$tables=array();while($row=odbc_fetch_array($res)){if($row['TABLE_TYPE']==='TABLE'||$row['TABLE_TYPE']==='VIEW'){$tables[]=array('name'=>$row['TABLE_NAME'],'view'=>$row['TABLE_TYPE']==='VIEW');}}odbc_free_result($res);return$tables;}function
getColumns($table){$res=odbc_columns($this->connection);$columns=array();while($row=odbc_fetch_array($res)){if($row['TABLE_NAME']===$table){$columns[]=array('name'=>$row['COLUMN_NAME'],'table'=>$table,'nativetype'=>$row['TYPE_NAME'],'size'=>$row['COLUMN_SIZE'],'nullable'=>(bool)$row['NULLABLE'],'default'=>$row['COLUMN_DEF']);}}odbc_free_result($res);return$columns;}function
/**
* list all fields in a table in the current database
*
* @param string $table name of table that should be used in method
*
* @return mixed array of field names on success, a MDB2 error on failure
* @access public
*/
function listTableFields($table)
{
$db =& $this->getDBInstance();
if (PEAR::isError($db)) {
return $db;
}
$rs = odbc_columns($db->connection, "%", "%", $table);
$columns = array();
while ($data = odbc_fetch_array($rs)) {
$columns[] = $data[COLUMN_NAME];
}
/*
throw new Exception();
$table = $db->quoteIdentifier($table, true);
$columns = $db->queryCol("SELECT c.name
FROM syscolumns c
LEFT JOIN sysobjects o ON c.id = o.id
WHERE o.name = '$table'");
if (PEAR::isError($columns)) {
return $columns;
}
*/
if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE) {
$columns = array_map($db->options['field_case'] == CASE_LOWER ? 'strtolower' : 'strtoupper', $columns);
}
return $columns;
}
/**
* Return all the fields of a table.
*
* It also populate the metadata array attribute will all the informations about the
* table fields. Type, Key, Extra, Null
* TABLE_QUALIFIER
TABLE_SCHEM
TABLE_NAME
COLUMN_NAME
DATA_TYPE
TYPE_NAME
PRECISION
LENGTH
SCALE
RADIX
NULLABLE
REMARKS
*
* @param string $table Name of the Table
* @return array $field All the fields name
*/
function getTableField($table = "")
{
if (is_array($table)) {
$atable = $table;
} elseif (strlen($table) > 0) {
$atable = $table;
} else {
$atable = $this->table;
}
if (is_array($atable)) {
reset($atable);
$numfields = 0;
while (list($key, $table) = each($atable)) {
//$table_def = mysql_query("SHOW FIELDS FROM $table", );
$table_def = odbc_columns($this->dbCon->id, $this->dbCon->getDatabase(), "%", $table);
for ($i = 0; $i < odbc_num_rows($table_def); $i++) {
$row_table_def = odbc_fetch_array($table_def);
$field[$numfields] = $row_table_def["COLUMN_NAME"];
$fieldname = $row_table_def["COLUMN_NAME"];
$this->metadata[$table][$fieldname]["Type"] = $row_table_def["TYPE_NAME"];
$this->metadata[$table][$fieldname]["Null"] = $row_table_def["NULLABLE"];
$this->metadata[$table][$fieldname]["Key"] = $row_table_def["SCALE"];
//not sure of that one.
$this->metadata[$table][$fieldname]["Extra"] = $row_table_def["REMARKS"];
$numfields++;
}
}
} else {
$table_def = odbc_columns($this->dbCon->id, $this->dbCon->getDatabase(), "%", $atable);
for ($i = 0; $i < odbc_num_rows($table_def); $i++) {
$row_table_def = odbc_fetch_array($table_def);
$field[$i] = $row_table_def["COLUMN_NAME"];
$fieldname = $row_table_def["COLUMN_NAME"];
$this->metadata[$table][$fieldname]["Type"] = $row_table_def["TYPE_NAME"];
$this->metadata[$table][$fieldname]["Null"] = $row_table_def["NULLABLE"];
$this->metadata[$table][$fieldname]["Key"] = $row_table_def["SCALE"];
//not sure of that one.
$this->metadata[$table][$fieldname]["Extra"] = $row_table_def["REMARKS"];
}
}
reset($field);
return $field;
}
<?php include 'config.inc'; $conn = odbc_connect($dsn, $user, $pass); var_dump($result = odbc_columns($conn, '', '', '', '')); var_dump(odbc_fetch_row($result)); var_dump($result = odbc_columns($conn, NULL, NULL, NULL, NULL)); var_dump(odbc_fetch_row($result)); var_dump($result = odbc_columns($conn, 'FOO', 'FOO', 'FOO', 'FOO')); var_dump(odbc_fetch_row($result));
function getColumnDescr($table)
{
switch ($this->_dbType) {
case 'mysql':
$tmp = $this->execute("describe {$table}");
break;
case 'odbc':
$tmp = odbc_columns($this->db);
break;
}
return false;
}
/** Returns the columns of a specified table-name. */
function getColumns($tablename){
if (!$this->conn){
throw new Exception("No connection to a database.");
}
if (!trim($tablename)){
throw new Exception("Not a valid table-name.");
}
if ($this->getType() == "mysql"){
$f_gc = $this->query("SHOW FULL COLUMNS FROM " . $tablename) or die($this->query_error());
while($d_gc = $this->query_fetch_assoc($f_gc)){
$value = "";
if ($d_gc['Null'] == "YES"){
$notnull = "no";
}else{
$notnull = "yes";
}
if ($d_gc['Key'] == "PRI"){
$primarykey = "yes";
}else{
$primarykey = "no";
}
if ($d_gc['Extra'] == "auto_increment"){
$autoincr = "yes";
}else{
$autoincr = "no";
}
if ($d_gc['Type'] == "tinytext"){
$maxlength = 255;
}else{
$maxlength = "";
}
$columns[$d_gc['Field']] = array(
"name" => $d_gc['Field'],
"notnull" => $notnull,
"type" => $d_gc['Type'],
"maxlength" => $maxlength,
"default" => $d_gc['Default'],
"primarykey" => $primarykey,
"value" => $value,
"input_type" => "mysql",
"autoincr" => $autoincr,
"comment" => $d_gc["Comment"]
);
}
}elseif($this->getType() == "pgsql"){
$f_gc = $this->query("
SELECT
pg_attribute.attname AS fieldname,
pg_attribute.atttypmod AS maxlength,
pg_attribute.attnotnull AS notnull,
pg_type.typname AS fieldtype,
pg_attribute.atthasdef,
pg_class.oid
FROM
pg_attribute,
pg_class,
pg_type
WHERE
pg_class.oid = pg_attribute.attrelid AND
pg_class.relname = '$tablename' AND
pg_attribute.attnum > 0 AND
pg_type.oid = pg_attribute.atttypid
");
while($d_gc = $this->query_fetch_assoc($f_gc)){
if ($d_gc[atthasdef] == "t"){
//The column has a default value, which we have to look up.
$f_gdv = $this->query("SELECT * FROM pg_attrdef WHERE adrelid = '$d_gc[oid]'");
$d_gdv = $this->query_fetch_assoc($f_gdv);
$default = substr($d_gdv[adsrc], 1, -1);
}else{
$default = "";
}
if ($d_gc[notnull] == "t"){
$notnull = "yes";
}else{
$notnull = "no";
}
if (preg_match("/^int[0-9]$/", $d_gc[fieldtype])){
$type = "int";
}else{
$type = $d_gc[fieldtype];
}
if ($d_gc[maxlength] == -1){
$maxlength = "";
}else{
$maxlength = $d_gc[maxlength] - 4;
}
$primarykey = "no";
$columns[] = array(
"name" => $d_gc['fieldname'],
"notnull" => $notnull,
"type" => $type,
"maxlength" => $maxlength,
"default" => $default,
"primarykey" => $primarykey,
"input_type" => "pgsql"
);
}
}elseif($this->getType() == "sqlite"){
$f_gc = $this->query("PRAGMA table_info(" . $tablename . ")") or die($this->query_error());
while($d_gc = sqlite_fetch_array($f_gc)){
if (!$d_gc['notnull']){
$notnull = "no";
}else{
$notnull = "yes";
}
if ($d_gc['pk'] == "1"){
$primarykey = "yes";
}else{
$primarykey = "no";
}
$columns[$d_gc['name']] = array(
"name" => $d_gc['name'],
"notnull" => $notnull,
"type" => $d_gc['type'],
"maxlength" => $maxlength,
"default" => $d_gc['dflt_value'],
"primarykey" => $primarykey,
"input_type" => "sqlite"
);
}
}elseif($this->getType() == "sqlite3"){
$f_gc = $this->query("PRAGMA table_info(" . $tablename . ")") or die($this->query_error());
while($d_gc = $this->query_fetch_assoc($f_gc)){
if (!$d_gc['notnull']){
$notnull = "no";
}else{
$notnull = "yes";
}
if ($d_gc['pk'] == "1"){
$primarykey = "yes";
}else{
$primarykey = "no";
}
$columns[$d_gc['name']] = array(
"name" => $d_gc['name'],
"notnull" => $notnull,
"type" => $d_gc[type],
"maxlength" => $maxlength,
"default" => $d_gc['dflt_value'],
"primarykey" => $primarykey,
"input_type" => "sqlite"
);
}
}elseif($this->getType() == "access"){
$f_gc = odbc_columns($this->conn);
while($d_gc = odbc_fetch_array($f_gc)){
if ($d_gc[TABLE_NAME] == $tablename){
if ($d_gc[IS_NULLABLE] == "YES"){
$notnull = "no";
}else{
$notnull = "yes";
}
$columns[$d_gc[COLUMN_NAME]] = array(
"name" => $d_gc[COLUMN_NAME],
"type" => $d_gc[TYPE_NAME],
"maxlength" => $d_gc[COLUMN_SIZE],
"notnull" => $notnull,
"input_type" => "access"
);
}
}
}else{
throw new Exception("Not a valid type: " . $this->getType());
}
//So that all types seems the same to the program.
if (!$columns){
return array();
}
foreach($columns AS $key => $value){
/** NOTE: Fix bug when decimal- and enum-columns hadnt their maxlength set. */
$type = $columns[$key]["type"];
if (preg_match("/^decimal\(([0-9]+),([0-9]+)\)$/", $type, $match)){
//this is a decimal-field.
$columns[$key]["type"] = "decimal";
$columns[$key]["maxlength"] = $match[1] . "," . $match[2];
}elseif(preg_match("/^enum\((.+)\)$/", $type, $match)){
//this is a enum-field.
$columns[$key]["type"] = "enum";
$columns[$key]["maxlength"] = $match[1];
}elseif(preg_match("/([a-zA-Z]+)\(([0-9]+)\)/", $type, $match)){
$columns[$key]["type"] = $match[1];
$columns[$key]["maxlength"] = $match[2];
}
/** NOTE: Standardlize the column-types. */
$columns[$key]["type"] = strtolower(trim($columns[$key]["type"]));
if ($columns[$key]["type"] == "integer"){
$columns[$key]["type"] = "int";
}elseif($value["type"] == "counter"){
$columns[$key]["type"] = "counter";
$columns[$key]["primarykey"] = "yes";
$columns[$key]["autoincr"] = "yes";
}
/** NOTE: Fix bug with quotes on default values (when saved they would have double quotes). */
if ($columns[$key]["default"]){
if (substr($columns[$key]["default"], 0, 1) == "'" && substr($columns[$key]["default"], -1, 1) == "'"){
$columns[$key]["default"] = substr($columns[$key]["default"], 1, -1);
}
}
}
return $columns;
}
echo odbc_errormsg();
exit(1);
}
// columnprivileges
$rh = odbc_columnprivileges($r, '', 'test', 'my_table', '%');
//var_dump($rh);
echo "resource? " . is_resource($rh) . "\n";
if ($rh == NULL) {
echo odbc_errormsg();
exit(1);
}
while ($rr = odbc_fetch_array($rh)) {
var_dump($rr);
}
// columns
$rh = odbc_columns($r, '', 'test', 'my_table', '%');
//var_dump($rh);
echo "resource? " . is_resource($rh) . "\n";
if ($rh == NULL) {
echo odbc_errormsg();
exit(1);
}
while ($rr = odbc_fetch_array($rh)) {
// we use ODBC 3, so we differ in column type # for date columns
// this is not a bug
if ($rr['TYPE_NAME'] == 'datetime') {
$rr['DATA_TYPE'] = '(hack)';
$rr['SQL_DATA_TYPE'] = '(hack)';
}
var_dump($rr);
}
function RetrieveODBCData($action)
{
$availableActions = array('-delete', '-edit', '-find', '-findall', '-new', '-sqlquery');
if (!in_array(strtolower($action), $availableActions)) {
// first off, toss out any requests for actions NOT supported under ODBC
return new FX_Error("The action requested ({$action}) is not supported under ODBC via FX.php.");
}
$odbc_res = odbc_connect($this->database, $this->DBUser, $this->DBPassword);
// although username and password are optional for this function, FX.php expects them to be set
if ($odbc_res == false) {
return new FX_Error('Unable to connect to ODBC data source.');
}
switch ($action) {
case '-delete':
case '-edit':
case '-find':
case '-findall':
case '-new':
$this->dataQuery = $this->BuildSQLQuery($action);
if (FX::isError($this->dataQuery)) {
return $this->dataQuery;
}
case '-sqlquery':
// note that there is no preceding break, as we don't want to build a query
$odbc_result = odbc_exec($odbc_res, $this->dataQuery);
if (!$odbc_result) {
$tempErrorText = odbc_errormsg($odbc_res);
odbc_close($odbc_res);
return new FX_Error("Unsuccessful query: {$this->dataQuery} ({$tempErrorText})");
}
$this->foundCount = odbc_num_rows($odbc_result);
$fieldCount = odbc_num_fields($odbc_result);
if ($theResult < 0) {
$tempErrorText = odbc_errormsg($odbc_res);
odbc_close($odbc_res);
return new FX_Error("Unable to access field count for current ODBC query. ({$tempErrorText})");
}
$odbc_columns = odbc_columns($odbc_res);
if (!$odbc_columns) {
$tempErrorText = odbc_errormsg($odbc_res);
odbc_close($odbc_res);
return new FX_Error("Unable to retrieve column data via ODBC. ({$tempErrorText})");
}
while (odbc_fetch_row($odbc_columns)) {
$fieldNumber = odbc_result($odbc_columns, 'ORDINAL_POSITION');
$this->fieldInfo[$fieldNumber]['name'] = odbc_result($odbc_columns, 'COLUMN_NAME');
$this->fieldInfo[$fieldNumber]['type'] = odbc_result($odbc_columns, 'TYPE_NAME');
$this->fieldInfo[$fieldNumber]['emptyok'] = odbc_result($odbc_columns, 'IS_NULLABLE');
$this->fieldInfo[$fieldNumber]['maxrepeat'] = 1;
$this->fieldInfo[$fieldNumber]['extra'] = 'COLUMN_SIZE:' . odbc_result($odbc_columns, 'COLUMN_SIZE') . '|BUFFER_LENGTH:' . odbc_result($odbc_columns, 'BUFFER_LENGTH') . '|NUM_PREC_RADIX:' . odbc_result($odbc_columns, 'NUM_PREC_RADIX');
}
while (odbc_fetch_row($odbc_result)) {
$tempRow = array();
for ($i = 1; $i <= $fieldCount; ++$i) {
$theResult = odbc_result($odbc_result, $i);
if (!$this->useInnerArray) {
$tempRow[$this->fieldInfo[$i]['name']] = $theResult;
} else {
$tempRow[$this->fieldInfo[$i]['name']] = array($theResult);
}
if ($this->fieldInfo[$i]['name'] == $this->primaryKeyField) {
$currentKey = $theResult;
}
}
if ($this->genericKeys || $this->primaryKeyField == '') {
$this->currentData[] = $tempRow;
} else {
$this->currentData[$currentKey] = $tempRow;
}
}
break;
default:
return new FX_Error("The action requested ({$action}) is not supported by FileMaker under ODBC via FX.php.");
break;
}
$this->fxError = 0;
return true;
}
function GetColumnInfo($TableName)
{
switch ($this->dbc->Dialect) {
case "TSQL":
$qualifier = $this->dbc->dbDefault;
$schema = "%";
break;
case "Access":
$qualifier = $this->dbc->dsn;
$schema = "";
break;
default:
return null;
}
echo "<p>GetColumnInfo: " . $qualifier . "." . $schema . "." . $TableName . "</p>";
$rsMain = odbc_columns($this->dbc->dbMain, $qualifier, $schema, $TableName);
//odbc_result_all($rsMain);
if (!$rsMain) {
return null;
}
$arColumns = array();
while ($this->FetchAssoc($rsMain, $row)) {
if ($row["TABLE_NAME"] != $TableName) {
continue;
}
$colinfo = new dbColumn();
//echo "<p>GetColumnInfo: ".$row["COLUMN_NAME"].':'.$row["TYPE_NAME"]."</p>";
$colinfo->ColName = $row["COLUMN_NAME"];
$colinfo->ColType = $row["TYPE_NAME"];
if (array_key_exists("PRECISION", $row)) {
$colinfo->ColLength = $row["PRECISION"];
} else {
if (array_key_exists("COLUMN_SIZE", $row)) {
$colinfo->ColLength = $row["COLUMN_SIZE"];
}
}
$colinfo->Nullable = $row["NULLABLE"] == "YES";
$colinfo->IsPKey = false;
$colinfo->Writeable = $row["TYPE_NAME"] != 'int identity';
array_push($arColumns, $colinfo);
}
$this->FreeResult($rsMain);
//$rsMain=odbc_columnprivileges($this->dbc->dbMain, $qualifier, $schema, $TableName,"%");
//odbc_result_all($rsMain);
//$this->FreeResult($rsMain);
$rsMain = odbc_primarykeys($this->dbc->dbMain, $qualifier, $schema, $TableName);
if ($rsMain) {
while ($this->FetchAssoc($rsMain, $row)) {
$colname = $row["COLUMN_NAME"];
//echo "<p>GetColumnInfo pk: ".$colname."</p>";
for ($i = 0; $i < count($arColumns); $i++) {
if ($arColumns[$i]->ColName == $colname) {
$arColumns[$i]->IsPKey = true;
break;
}
}
}
$this->FreeResult($rsMain);
}
return $arColumns;
}
/**
* Returns metadata for all columns in a table.
* @param string
* @return array
*/
public function getColumns($table)
{
$result = odbc_columns($this->connection);
$res = array();
while ($row = odbc_fetch_array($result)) {
if ($row['TABLE_NAME'] === $table) {
$res[] = array('name' => $row['COLUMN_NAME'], 'table' => $table, 'nativetype' => $row['TYPE_NAME'], 'size' => $row['COLUMN_SIZE'], 'nullable' => (bool) $row['NULLABLE'], 'default' => $row['COLUMN_DEF']);
}
}
odbc_free_result($result);
return $res;
}
/**
* Get the structure of a field into an array
*
* @param string $table_name name of table that should be used in method
* @param string $field_name name of field that should be used in method
* @return mixed data array on success, a MDB2 error on failure
* @access public
*/
function getTableFieldDefinition($table_name, $field_name)
{
$db =& $this->getDBInstance();
if (PEAR::isError($db)) {
return $db;
}
$result = $db->loadModule('Datatype', null, true);
if (PEAR::isError($result)) {
return $result;
}
list($schema, $table) = $this->splitTableSchema($table_name);
$table = $db->quoteIdentifier($table, true);
$fldname = $db->quoteIdentifier($field_name, true);
$res = odbc_columns($db->connection, "%", "%", $table);
$column = array();
while ($data = odbc_fetch_array($res)) {
if (strcasecmp($field_name, $data['COLUMN_NAME'])) {
$column['table_name'] = $data['TABLE_NAME'];
$column['name'] = $data['COLUMN_NAME'];
$column['type'] = $data['TYPE_NAME'];
if ($data['IS_NULLABLE'] == "YES") {
$column['is_nullable'] = 1;
} else {
$column['is_nullable'] = 0;
}
$column['column_default'] = $data['COLUMN_DEF'];
$column['length'] = $data['COLUMNZ_SIZE'];
$column['numeric_precision'] = $data['NUM_PREC_RADIX'];
$column['numeric_scale'] = $data['DECIMAL_DIGITS'];
$column['collation_name'] = NULL;
}
}
/*
$query = "SELECT t.table_name,
c.column_name 'name',
c.data_type 'type',
CASE c.is_nullable WHEN 'YES' THEN 1 ELSE 0 END AS 'is_nullable',
c.column_default,
c.character_maximum_length 'length',
c.numeric_precision,
c.numeric_scale,
c.character_set_name,
c.collation_name
FROM INFORMATION_SCHEMA.TABLES t,
INFORMATION_SCHEMA.COLUMNS c
WHERE t.table_name = c.table_name
AND t.table_name = '$table'
AND c.column_name = '$fldname'";
if (!empty($schema)) {
$query .= " AND t.table_schema = '" .$db->quoteIdentifier($schema, true) ."'";
}
$query .= ' ORDER BY t.table_name';
$column = $db->queryRow($query, null, MDB2_FETCHMODE_ASSOC);
if (PEAR::isError($column)) {
return $column;
}
*/
if (empty($column)) {
return $db->raiseError(MDB2_ERROR_NOT_FOUND, null, null, 'it was not specified an existing table column', __FUNCTION__);
}
if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE) {
if ($db->options['field_case'] == CASE_LOWER) {
$column['name'] = strtolower($column['name']);
} else {
$column['name'] = strtoupper($column['name']);
}
} else {
$column = array_change_key_case($column, $db->options['field_case']);
}
$mapped_datatype = $db->datatype->mapNativeDatatype($column);
if (PEAR::isError($mapped_datatype)) {
return $mapped_datatype;
}
list($types, $length, $unsigned, $fixed) = $mapped_datatype;
$notnull = true;
if ($column['is_nullable']) {
$notnull = false;
}
$default = false;
if (array_key_exists('column_default', $column)) {
$default = $column['column_default'];
if (is_null($default) && $notnull) {
$default = '';
} elseif (strlen($default) > 4 && substr($default, 0, 1) == '(' && substr($default, -1, 1) == ')') {
//mssql wraps the default value in parentheses: "((1234))", "(NULL)"
$default = trim($default, '()');
if ($default == 'NULL') {
$default = null;
}
}
}
$definition[0] = array('notnull' => $notnull, 'nativetype' => preg_replace('/^([a-z]+)[^a-z].*/i', '\\1', $column['type']));
if (!is_null($length)) {
$definition[0]['length'] = $length;
}
if (!is_null($unsigned)) {
$definition[0]['unsigned'] = $unsigned;
}
if (!is_null($fixed)) {
$definition[0]['fixed'] = $fixed;
}
if ($default !== false) {
$definition[0]['default'] = $default;
}
foreach ($types as $key => $type) {
$definition[$key] = $definition[0];
if ($type == 'clob' || $type == 'blob') {
unset($definition[$key]['default']);
}
$definition[$key]['type'] = $type;
$definition[$key]['mdb2type'] = $type;
}
return $definition;
}
