Professional Web Applications Themes

How to get column names using SQL? - IBM DB2

In article <a06d6e69.0307100719.2e206e69posting.google.com >, [email]minjieexcite.com[/email] says... > I am writing a C++ application that not only wants to look at data in > the database, but also the corresponding column names. Is there a SQL > statement to get column names for a certain table? I know I can use > select tabnames from syscat.tables > to get table names, so I assume there should be a similar statement > for getting the column names. > > I'm using DB2 UDB V8.1. > > Thanks in advance for any help. > SYSCAT.COLUMNS To show the columns of this view: 'db2 ...

  1. #1

    Default Re: How to get column names using SQL?

    In article <a06d6e69.0307100719.2e206e69posting.google.com >,
    [email]minjieexcite.com[/email] says...
    > I am writing a C++ application that not only wants to look at data in
    > the database, but also the corresponding column names. Is there a SQL
    > statement to get column names for a certain table? I know I can use
    > select tabnames from syscat.tables
    > to get table names, so I assume there should be a similar statement
    > for getting the column names.
    >
    > I'm using DB2 UDB V8.1.
    >
    > Thanks in advance for any help.
    >
    SYSCAT.COLUMNS

    To show the columns of this view:

    'db2 describe table syscat.columns' from the db2 command window
    Gert van der Kooij Guest

  2. #2

    Default Re: How to get column names using SQL?

    select colname from syscat.columns where tabname='ADDRESS'

    (to get the right schema, you may have to join on syscat.tables.tabname
    and syscat.tables.tabschema)

    or

    describe table adamache.address



    minjie wrote:
    > I am writing a C++ application that not only wants to look at data in
    > the database, but also the corresponding column names. Is there a SQL
    > statement to get column names for a certain table? I know I can use
    > select tabnames from syscat.tables
    > to get table names, so I assume there should be a similar statement
    > for getting the column names.
    >
    > I'm using DB2 UDB V8.1.
    >
    > Thanks in advance for any help.
    Blair Adamache Guest

  3. #3

    Default Re: How to get column names using SQL?

    minjie <minjieexcite.com> wrote:
    > I am writing a C++ application that not only wants to look at data in
    > the database, but also the corresponding column names. Is there a SQL
    > statement to get column names for a certain table? I know I can use
    > select tabnames from syscat.tables
    > to get table names, so I assume there should be a similar statement
    > for getting the column names.
    >
    > I'm using DB2 UDB V8.1.
    Not sure what you really want, so here are three choices:

    (1) Query the DB2 catalog to see which tables and columns you have:

    SELECT colname
    FROM syscat.tables
    WHERE tabschema = ... AND tabname = ...

    (substitute ... with the appropriate values)

    (2) Get the names of the columns returned by a query. That is by no means
    the same as the column names in persistent tables!

    The best is to do a DESCRIBE:

    DESCRIBE SELECT x, y, z FROM table JOIN anotherTable ON ...

    (3) Similar as in (2), you can just use explicit column names in your query:

    SELECT a AS col1, a+b AS col2, b AS col3
    FROM table

    --
    Knut Stolze
    Information Integration
    IBM Germany / University of Jena
    Knut Stolze Guest

  4. #4

    Default Re: How to get column names using SQL?

    On Thu, 10 Jul 2003, Knut Stolze wrote:
    > minjie <minjieexcite.com> wrote:
    >
    > > I am writing a C++ application that not only wants to look at data in
    > > the database, but also the corresponding column names. Is there a SQL
    > > statement to get column names for a certain table? I know I can use
    > > select tabnames from syscat.tables
    > > to get table names, so I assume there should be a similar statement
    > > for getting the column names.
    > >
    > > I'm using DB2 UDB V8.1.
    >
    > Not sure what you really want, so here are three choices:
    >
    > (1) Query the DB2 catalog to see which tables and columns you have:
    >
    > SELECT colname
    > FROM syscat.tables
    > WHERE tabschema = ... AND tabname = ...
    >
    > (substitute ... with the appropriate values)
    >
    If you're writing your application using the CLI or ODBC, then there
    are functions to handle this for you. SQLColumns() does this without
    having to write a query.

    Also, there is a CLI function SQLDescribeCol() that you can use with an
    open statement handle to get the names of the columns for the statement.
    (i.e. if you prepare a query "select * from a_table", you can use
    SQLNumResultCols() and SQLDescribeCol() to find out the columns in the
    result set).

    Check the CLI Guide and Reference for more information.


    Good luck,





    -----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
    [url]http://www.newsfeeds.com[/url] - The #1 Newsgroup Service in the World!
    -----== Over 80,000 Newsgroups - 16 Different Servers! =-----
    Ian D. Bjorhovde Guest

  5. #5

    Default Re: How to get column names using SQL?

    "minjie" <minjieexcite.com> wrote in message
    news:a06d6e69.0307100719.2e206e69posting.google.c om...
    > I am writing a C++ application that not only wants to look at data in
    > the database, but also the corresponding column names. Is there a SQL
    > statement to get column names for a certain table? I know I can use
    > select tabnames from syscat.tables
    > to get table names, so I assume there should be a similar statement
    > for getting the column names.
    >
    > I'm using DB2 UDB V8.1.
    >
    > Thanks in advance for any help.
    Others have given the SQL to get the columns names, but I would add an order
    by in the SQL query just to make sure you get them in the right order.

    The DB2 Catalog tables are described in the appendix of the SQL Reference
    Manual, Volume 1. DB2 manuals in PDF format can be downloaded for free from
    the IBM web site.


    Mark A Guest

  6. #6

    Default Re: How to get column names using SQL?

    Knut Stolze <stolzede.ibm.com> wrote:
    > SELECT colname
    > FROM syscat.tables
    Oops, should have been syscat.columns here.

    --
    Knut Stolze
    Information Integration
    IBM Germany / University of Jena
    Knut Stolze Guest

  7. #7

    Default Re: How to get column names using SQL?

    "Mark A" <maswitchboard.net> wrote in message news:<MJiPa.50$ww6.82216news.uswest.net>...
    > Others have given the SQL to get the columns names, but I would add an order
    > by in the SQL query just to make sure you get them in the right order.
    >
    > The DB2 Catalog tables are described in the appendix of the SQL Reference
    > Manual, Volume 1. DB2 manuals in PDF format can be downloaded for free from
    > the IBM web site.
    Thank you all for the replies. I have one question regarding to 'order
    by' as suggested by Mark. The app will display column names and their
    values side by side, which means the order have to be exactly the
    same. But I will have two SELECT statements, one to get the column
    names, and the other to get their values. How can I make sure the
    order will be the same for the two SELECTs? For example, the column
    names are:
    inst_num, creation_date, last_modified_date, A1, A2, B1, B2, C1, C2,
    A3, A4, D1, D2, ...
    If I use the following statement:
    select colname from syscat.columns where tabname='ops' order by
    colname
    the column names will be selected in the following order:
    A1, A2, A3, A4, B1, B2, C1, C2, creation_date, D1, D2, inst_num,
    last_modified_date ...
    But the values for those columns will still be corresponding to the
    order shown in the first case, if I use the following for the 2nd
    SELECT:
    select * from ops where inst_num = 1;
    the 1st value will be 1, instead of A1, the 2nd value will be
    2003-07-09-17.36.53.319908, instead of A2, and so on. How should I
    order these two SELECTs in this case? I assume there is no way to
    order the 2nd SELECT, which is to get a row of data? I know one way to
    resolve it is to order the column names now, when I'm still creating
    the database. But if later I need to add columns to that table, the
    column names will not be in order again. Another way is not to order
    the 1st SELECT, but will I be guaranteed that the column names will be
    selected in the order when they were created? It seems so after a few
    tests, but I'm not sure if it will always work. If I use SQLDA in the
    app for the retrieved data, I cannot order them in the code, either,
    or can I?
    Thanks,
    Minjie
    minjie Guest

  8. #8

    Default Re: How to get column names using SQL?

    minjie <minjieexcite.com> wrote:
    > Thank you all for the replies. I have one question regarding to 'order
    > by' as suggested by Mark. The app will display column names and their
    > values side by side, which means the order have to be exactly the
    > same. But I will have two SELECT statements, one to get the column
    > names, and the other to get their values. How can I make sure the
    > order will be the same for the two SELECTs? For example, the column
    > names are:
    > inst_num, creation_date, last_modified_date, A1, A2, B1, B2, C1, C2,
    > A3, A4, D1, D2, ...
    > If I use the following statement:
    > select colname from syscat.columns where tabname='ops' order by
    > colname
    > the column names will be selected in the following order:
    > A1, A2, A3, A4, B1, B2, C1, C2, creation_date, D1, D2, inst_num,
    > last_modified_date ...
    > But the values for those columns will still be corresponding to the
    > order shown in the first case, if I use the following for the 2nd
    > SELECT:
    > select * from ops where inst_num = 1;
    How about building an explicit select-list and, thus, avoiding the problem
    alltogether?

    select A1, A2, A3, A4, B1, B2, C1, C2, creation_date, D1, D2, inst_num,
    last_modified_date
    from ops where inst_num = 1;


    If you don't want to do that, change the first select statement like this:

    SELECT colname
    FROM syscat.columns
    WHERE tabname='ops' AND tabschema = ...
    ORDER BY colno

    Please note that the "colno" contains the numerical values for the column
    position. That's the same that "select *" will use.

    I also recommend that you add the schema name into your query to make sure
    you get the information only for the one table you are interested in.

    > Another way is not to order
    > the 1st SELECT, but will I be guaranteed that the column names will be
    > selected in the order when they were created? It seems so after a few
    > tests, but I'm not sure if it will always work.
    No, it's not guaranteed. The only safe way to get a consistent ordering is
    to use an ORDER BY clause. Everything else is just coincidence.
    > If I use SQLDA in the
    > app for the retrieved data, I cannot order them in the code, either,
    > or can I?
    You can access the column name information once you do the DESCRIBE (see the
    SQLNAME attribute in the SQLVAR entries). With that information, you can
    sort out which output parameter of the SELECT belongs to which column name.
    Given that, you don't need the first query at all. That should also avoid
    problems if the query contains some calculations or renamed columns or so,
    which just doesn't correspond 1:1 to the columns in the table that you
    query.

    --
    Knut Stolze
    Information Integration
    IBM Germany / University of Jena
    Knut Stolze Guest

Similar Threads

  1. Getting column data without column names
    By Jared@Itron in forum Coldfusion Database Access
    Replies: 4
    Last Post: January 18th, 09:26 PM
  2. counting column and getting column names
    By dduck1934 in forum Coldfusion Database Access
    Replies: 0
    Last Post: May 24th, 02:05 PM
  3. Finding column names
    By Roko in forum ASP.NET Data Grid Control
    Replies: 0
    Last Post: December 27th, 06:07 PM
  4. DataGrid Column Names
    By phil ashby in forum Macromedia Flash Actionscript
    Replies: 0
    Last Post: March 2nd, 02:43 PM
  5. No column names
    By Vesna Martinovic in forum IBM DB2
    Replies: 0
    Last Post: July 3rd, 07:45 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139