PHP odbc_columns函数代码示例

 /**
  * @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 {
//.........这里部分代码省略.........

 /**
  * 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;
 }

//.........这里部分代码省略.........
					
					$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;
 }