PHP TOP

odbc_columns

(PHP 4, PHP 5, PHP 7, PHP 8)

odbc_columns — 指定したテーブルにあるカラム名のリストを取得する

説明

odbc_columns(
    resource $odbc,
    ?string $catalog = null,
    ?string $schema = null,
    ?string $table = null,
    ?string $column = null
): resource|false

指定したテーブルにあるカラム名のリストを取得します。

パラメータ

odbc: ODBC 接続 ID。詳細は odbc_connect() を参照ください。
catalog: カタログ(ODBC 2 の用語では '修飾子')。
schema: スキーマ (ODBC 2 の用語では '所有者')。このパラメータには以下の検索パターンを使用できます。 % はゼロ個以上の文字にマッチし、 _ はひとつの文字にマッチします。
table: テーブル名。このパラメータには以下の検索パターンを使用できます。 % はゼロ個以上の文字にマッチし、 _ はひとつの文字にマッチします。
column: カラム名。このパラメータには以下の検索パターンを使用できます。 % はゼロ個以上の文字にマッチし、 _ はひとつの文字にマッチします。

戻り値

ODBC 結果 ID を返します。失敗した場合に false を返します。

結果セットは以下のカラムを有しています。

TABLE_CAT
TABLE_SCHEM
TABLE_NAME
COLUMN_NAME
DATA_TYPE
TYPE_NAME
COLUMN_SIZE
BUFFER_LENGTH
DECIMAL_DIGITS
NUM_PREC_RADIX
NULLABLE
REMARKS
COLUMN_DEF
SQL_DATA_TYPE
SQL_DATETIME_SUB
CHAR_OCTET_LENGTH
ORDINAL_POSITION
IS_NULLABLE

ドライバは追加のカラムを返すことが出来ます。

結果セットは TABLE_CAT, TABLE_SCHEM, TABLE_NAME, ORDINAL_POSITION でソートされます。

変更履歴

バージョン	説明
8.0.0	`schema`, `table`, `column` は、nullable になりました。

例

例1 テーブルのカラム一覧を表示する

<?php
$conn = odbc_connect($dsn, $user, $pass);
$columns = odbc_columns($conn, 'TutorialDB', 'dbo', 'test', '%');
while (($row = odbc_fetch_array($columns))) {
    print_r($row);
    break; // further rows omitted for brevity
}
?>

上の例の出力は、たとえば以下のようになります。

Array
(
    [TABLE_CAT] => TutorialDB
    [TABLE_SCHEM] => dbo
    [TABLE_NAME] => TEST
    [COLUMN_NAME] => id
    [DATA_TYPE] => 4
    [TYPE_NAME] => int
    [COLUMN_SIZE] => 10
    [BUFFER_LENGTH] => 4
    [DECIMAL_DIGITS] => 0
    [NUM_PREC_RADIX] => 10
    [NULLABLE] => 0
    [REMARKS] =>
    [COLUMN_DEF] =>
    [SQL_DATA_TYPE] => 4
    [SQL_DATETIME_SUB] =>
    [CHAR_OCTET_LENGTH] =>
    [ORDINAL_POSITION] => 1
    [IS_NULLABLE] => NO
)

参考

odbc_columnprivileges() - 指定したテーブルに関するカラムおよび付随する権限のリストを取得する
odbc_procedurecolumns() - プロシージャへのパラメータに関する情報を取得する

Found A Problem?

Learn How To Improve This Page • Submit a Pull Request • Report a Bug

＋add a note

User Contributed Notes 13 notes

down

Thomas ¶

15 years ago

[MS SQL Server 2005/2008, PHP 5]

Imagine you would need to access the column names of a specific table, for instance to display them as table headers for fields with missing information. While browsing the documentation I was kind of lost how to use odbc_columns() without the usage of odbc_result_all() which outputs EVERYTHING in a single HTML table.

Here is a way to stuff all output into an array and then access only one or more fields of the odbc_columns() output:

<?php
include('connect.inc'); // <== Put all your database connection parameters in here. (DSN, PWD, USR, mssql_connect, etc.; returns $connection)

$outval = odbc_columns($connection, "your DB name", "%", "your table name", "%");

$pages = array();
while (odbc_fetch_into($outval, $pages)) {
        echo $pages[3] . "<br />\n"; // presents all fields of the array $pages in a new line until the array pointer reaches the end of array data
    }
?>

Now your array $pages will have the following contents:
([x] is the array index displayed here for better understanding)

[0] TABLE_CAT <== your DB name
[1] TABLE_SCHEM <== dbo, your table scheme
[2] TABLE_NAME <== your table name
[3] COLUMN_NAME <== your column names (selected all with "%" in odbc_columns() )
[4] DATA_TYPE <== -8
[5] TYPE_NAME <== nchar (corresponds to -8, 11 f.i. is datetime and so on)
[6] COLUMN_SIZE <== num. val.
[7] BUFFER_LENGTH <== num. val.
[8] DECIMAL_DIGITS <== num. val. or NULL
[9] NUM_PREC_RADIX <== num. val. or NULL
[10] NULLABLE <== num. val.
[11] REMARKS <== num. val. or NULL
[12] COLUMN_DEF <== num. val. or NULL
[13] SQL_DATA_TYPE <== num. val.
[14] SQL_DATETIME_SUB <== num. val. or NULL
[15] CHAR_OCTET_LENGTH <== num. val. or NULL
[16] ORDINAL_POSITION <== num. val.
[17] IS_NULLABLE <== YES/NO
[18] SS_DATA_TYPE <== num. val.

Now you can access each field recursivly by its key and output only the DESIRED fields instead of having ALL output from odbc_result_all().
Please note that the array key starts at zero (0) instead of one (1), so echo $pages[3] selects COLUMN_NAME from the above list.

I hope this helps...

Cheers
Thomas

down

ke3wh at comcast dot net ¶

20 years ago

The complete script to get into from a DSN MS Access DB Table and display it is below.

function Error_Handler( $msg, $cnx ) {
    echo "$msg \n";
    odbc_close( $cnx);
    exit();
    }

$cnx = odbc_connect( 'DSN_NAME' , '', '' );//connect to MSAccess
    if (!$cnx) {
      Error_handler( "Error in odbc_connect" , $cnx );
    }  

    $res400= odbc_columns($cnx,"DSN_NAME","","TABLE");
    echo odbc_result_all($res400);

down

php dot lpatrick at spamgourmet dot com ¶

16 years ago

Since I was just looking for table descriptions of an MS Access file I didn't know the table structure of, I wrote this (where $inputfile is the Access file name):

<?php
$conn = odbc_connect("DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=".$inputfile;, "", "");

$tabs = odbc_tables($conn);
$tables = array();
while (odbc_fetch_row($tabs)){
    if (odbc_result($tabs,"TABLE_TYPE")=="TABLE") {
        $table_name = odbc_result($tabs,"TABLE_NAME");
        $tables["{$table_name}"] = array();
        $cols = odbc_exec($conn,'select * from `'.$table_name.'` where 1=2');  // we don't want content
      $ncols = odbc_num_fields($cols);
        for ($n=1; $n<=$ncols; $n++) {
            $field_name = odbc_field_name($cols, $n);
            $tables["{$table_name}"]["{$field_name}"]['len'] = odbc_field_len($cols, $n);
            $tables["{$table_name}"]["{$field_name}"]['type'] = odbc_field_type($cols, $n);
        }
    }
}
odbc_close ($conn);
print_r($tables);
?>

down

eion at robbmob dot com ¶

12 years ago

Sometimes the array keys of the results of odbc_columns() can be in lower case, instead of upper case (eg 'column_name' instead of 'COLUMN_NAME').  This can vary depending on which ODBC driver you are connecting to.

down

jeremie dot legrand at komori-chambon dot fr ¶

16 years ago

Here is the way to use odbc_columns() with "Attunity Connect", an ODBC connector to VMS :

$db = "my_database";
$table = "my_table";
$con = odbc_connect($db, "user", "password");
$result = odbc_columns($con, $db, "", $table, "%");
while (odbc_fetch_row($resu)) {
    echo odbc_result_all($resu);
}

down

LyleE at LocalMotion dot com ¶

23 years ago

Took me a while to figure out this command, thought I would save some other people the time.  We couldn't quite figure out what the qualifier was.  For MSSQL 7.0, it is the Database that you are connecing to.  For the pubs database it would look something like this.



$rs = odbc_columns($DBConnection, "Pubs", "%", "jobs");



That would be for showing the jobs table.

down

tom at jargonsoft dot com ¶

5 years ago

The docs on this page are among the worst in php. I know docs are traditionally terrible for open source code, but this is NOT acceptable:
Qualifer: the qualifer. REALLY??
How about: ComplexFieldWithManyRules: the ComplexFieldWithManyRules. 
Give me a break.
How about actually defining the meaning and how to use it?
Along with several EXAMPLES!!
You are giving software developers a bad name with junk like this.

down

Artur ¶

17 years ago

This is the only way I could actually get field names using odbc_columns. Hope it will be usefull for someone.

$result = odbc_columns($odbc,$dbhost,"dbo", "KIR_ViolationDetail");

while (odbc_fetch_row($result))
{
  echo odbc_result($result,"COLUMN_NAME");
}

down

laundro at gmail dot com ¶

20 years ago

Getting all column names from Excel with ODBC:
$cols = odbc_columns($connection, $filename, NULL, $sheet);

where:
$connection is the result of your odbc_connect;
$filename is the filename of the Excel file;
$sheet is the name of the Excel worksheet.

This is useful when you want to query an Excel file without having to name ranges beforehand. With the results obtained from the above command, you can populate an array and use its contents (ie the column names) for further querying.

down

netaminas at hotamil dot com ¶

20 years ago

Connect with IBM Client Access 32-bit ODBC driver

To access table information in DB2 iSeries (AS/400) via ODBC Driver i've tried with this code and worked!!!

$conn_ODBC = odbc_connect("DSN", "USER", "PASSW") or die;
$tabela = "table name in IBM iSeries";
$libname = "library name in IBM iSeries"

$res400 = odbc_columns($conn_ODBC, "DSN", $libname, $tabela, "%")  or die("<p><font color=#FF0000>Erro Na Leitura da Tabela ".$tabela." do AS/400: ".odbc_errormsg());

echo odbc_result_all($res400);

Netaminas.com
PORTUGAL

down

Anonymous ¶

21 years ago

ODBC & MS ACCESS :

odbc_columns($conn,"DSN_NAME","","TABLE_NAME");

down

Sergio Sartori ¶

21 years ago

Using this function on a MS SQL Server 2000 database connection with the syntax:

$res = odbc_columns($connId, $dbName, "%", $tableName, "%");

I actually get a result set with THESE columns name:

TABLE_CAT, TABLE_SCHEM, TABLE_NAME, COLUMN_NAME, DATA_TYPE, TYPE_NAME, COLUMN_SIZE, BUFFER_LENGTH, DECIMAL_DIGITS, NUM_PREC_RADIX, NULLABLE, REMARKS

in the order displayed.

down

josh at engledental dot com ¶

22 years ago

(PHP4.2.1, Win2k, MSSQL 2K)

The result id returned from this function does not appear to behave exactly the same as a result id returned from a query.  

I get all sorts of errors when I try to use subsequent odbc functions on the result id, such as:

odbc_fetch_into - This function only seems to work if I don't specify a row number or if the row number = 0.

odbc_fetch_row - This function will not return a row from the result set.  Under some scenarios, I am able to use it in a while loop, but its seems buggy.  Hence, my odbc_num_of_rows function (hack to replace the non-functional odbc_num_rows function) also doesn't work.

I recommend dumping the results into an array immediately and freeing the result id created by this function.

ps - If anyone has any further insight or workarounds, I would appreciate them.

＋add a note

Atelier ”慶”

WEBクリエイター歴２０年、最先端技術と安心をお届けします。

PHPのお勉強！

odbc_columns

説明

パラメータ

戻り値

変更履歴

例

参考

Found A Problem?

User Contributed Notes 13 notes