0
amit_27's Avatar, Join Date: May 2009
Go4Expert Member
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 shabbir; 12Jun2009 at 15:44.. Reason: Code blocks
0
amit_27's Avatar, Join Date: May 2009
Go4Expert Member
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.
0
amit_27's Avatar, Join Date: May 2009
Go4Expert Member
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.