1. We have moved from vBulletin to XenForo and you are viewing the site in the middle of the move. Though the functional aspect of everything is working fine, we are still working on other changes including the new design on Xenforo.
    Dismiss Notice

IS there any MySQL Default tables like DUAL in ORACLE.

Discussion in 'MySQL' started by amit_27, May 20, 2009.

  1. amit_27

    amit_27 New Member

    Joined:
    May 19, 2009
    Messages:
    20
    Likes Received:
    0
    Trophy Points:
    0
    How do I get the Parameter information of table?
    I have table like

    mysql> desc account;
    Code:
    +----------+--------------+------+-----+---------+-------+
    | Field    | Type         | Null | Key | Default | Extra |
    +----------+--------------+------+-----+---------+-------+
    | acct_num | int(11)      | NO   | PRI | NULL    |       |
    | amount   | decimal(2,0) | YES  |     | NULL    |       |
    +----------+--------------+------+-----+---------+-------+
    
    I want all the information like colname, datatype, collength, precision, scale, Nullable. I tried using the
    Code:
    MYSQL_FIELD *fields;
    unsigned int num_fields; 
    
    pResult = mysql_store_result(MySQLDB);
    num_fields= mysql_num_fields(pResult);
    fields = mysql_fetch_fields(pResult);    
    for( i = 0; i < num_fields; i++)
    {
      printf("Field %u is %s\n", i, fields[i].name);
    }
    
    It is giving me output like :
    Field
    Type
    NULL
    KEY
    Default

    row = mysql_fetch_row(pResult);
    it will give me output as :
    acct_num

    Which I wanted to get.

    But other information not gettting like datatype, precision, scale, nullable.

    If I fire a query select * from account;It will gives me What I want in fields.
    But it is not good if the there are 100 table which has millions of records and I want information of each table.
    Is ther any other way to get only the reuqired information?
     
    Last edited by a moderator: Jun 12, 2009
  2. amit_27

    amit_27 New Member

    Joined:
    May 19, 2009
    Messages:
    20
    Likes Received:
    0
    Trophy Points:
    0
    How do I get the native data type like we get in ORACLE using

    OCIAttrGet(colHandle, OCI_DTYPE_PARAM, (dvoid *) &dataType, 0,
    OCI_ATTR_DATA_TYPE, m_errorHandle);

    It will return &datatype value for:
    VARCHAR2 - 1
    NUMBER - 2
    Date - 12

    How do we achieve this in MYSQL 5.1?
    I saw in manual but not found any pointer. There is no any point described for native data types value.
     
  3. amit_27

    amit_27 New Member

    Joined:
    May 19, 2009
    Messages:
    20
    Likes Received:
    0
    Trophy Points:
    0
    Hi,

    I have created table -== account(acct_num INT, amount INT);
    I have created view using

    CREATE VIEW v AS SELECT acct_num AS value FROM account;

    when I execute following :
    mysql> select table_name from information_schema.views;
    mysql> select table_name from information_schema.tables;
    +------------+
    | table_name |
    +------------+
    | v |
    +------------+

    If 'v' is only the view then why it is present in information_schema.tables instead only in information_schema.views?

    My problem is when I fore query to get table_name it will return me 'v' also from information_schema.tables which I don't want.
     

Share This Page