How to get Table schema information ?

[amit2781]

How can we get the table schema information in MYSQL 5.1.34?

Like we use in ORACLE to get it as:

OCIDescribeAny() // //get the describe handle for the table to be described.

OCIAttrGet() // to get parameter handle

OCIAttrGet() ////get the number of columns in the table

[Atli]

Hi.

Well, there is always the INFORMATION_SCHEMA database.
You can search for every detail about your databases there.

Then there is the SHOW command and various other Utility Statements.

Is that what you are looking for?

[amit2781]

Thanks. I got the information from INFORMATION_SCHEMA COLUMNS.

While retrieving the data from the result of query I faced following problem.

mysql> select select COLUMN_NAME,  DATA_TYPE, IS_NULLABLE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION from INFORMATION_SCHEMA.COLUMNS where table_name='account');

In coding I run the query using mysql_real_query() whose result is stored in

pResult = mysql_store_result(pMySQLDB);
fields = mysql_fetch_field(pResult);
my_ulonglong noOfRows =  mysql_num_rows(pResult);
row = mysql_fetch_row(pResult);

it will not return any rows.

But in fields I get select COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION that I don't want.
I want the value like "amit, "MY_VAR_STRING", '10', '0'.

I want to store these data into another variable however not getting it.


Can you please tell me how do I fetch the data what I want from the query output or I am doing anything wrong?

[Atli]

I think you are confusing the old MySQL (mysql) extension with the new Improved MySQL (mysqli) extension.

The function mysqli_real_query belongs to the latter.

Note the "i" following the "mysql" in the function name.
All the functions belonging to the Improved MySQL extension also have the trailing "i" in the function name.

This is how these function should be used:

<?php
// Connect to MySQL using the procedural style mysqli functions.
$dbLink = mysqli_connect("localhost", "user", "passwd", "dbName");
if(mysqli_connect_errno())
{
    printf("MySQL Connection failed! %s", mysqli_connect_error());
}
 
// Create a query to execute.
$sql = "SELECT
            COLUMN_NAME,
            DATA_TYPE, IS_NULLABLE,
            CHARACTER_MAXIMUM_LENGTH,
            NUMERIC_PRECISION
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE
            table_name='tableName'
        AND table_schema='dbName'";
 
// Execute the query
if(mysqli_real_query($dbLink, $sql))
{
    // Fetch the results
    $result = mysqli_store_result($dbLink);
 
    // Print the number of rows returned
    printf("Query succeeded! (%d rows returned)<br />", mysqli_num_rows($result));
 
    // Print the results
    $rowNo = 0;
    while($row = mysqli_fetch_assoc($result)) {
        echo '<br />Row #', (++$rowNo) ,'<br />';
        foreach($row as $_colName => $_colValue) {
            echo " - {$_colName} = {$_colValue}<br />";
        }
    }
 
    // Free the result object
    mysqli_free_result($result);
}
else {
    printf("Query execution failed! %s", mysqli_error($dbLink));
}
 
// Close the connection
mysqli_close($dbLink);
?>

Note that I added the 'table_schema' column to the query. I used it to filter the query based on the database name, as well as the table name.
If you don't want that, just remove it.

[amit2781]

I am using API syntax from MySQL 5.1reference mannual which is recommended version.
which does not use 'i' as 'mysqli'.

I am writing application in C++.

[Atli]

@amit2781
That's the sort of thing we need to know straight away, or we will have to waste time guessing.

Going through the C API in the manual, I'm guessing this is how you use that function:
(Note, I don't really know C/C++, so the syntax may be a bit off)

// Execute query
result_success = mysql_real_query(&connection, &query_string, query_length);
if(!result_success) {
    // Error. Query failed
}
else {
    // Get the result
    result_resource = mysql_store_result(&connection);
    if(!result_resource) {
        // Error. Failed to fetch resource.
    }
    else {
        // Get field definitions
        num_fields = mysql_num_fields(result_resource);
        fields = mysql_fetch_fields(result_resource)
 
        // Get row count
        row_count = mysql_num_rows(result_resource);
 
        // Fetch rows
        while((row = mysql_fetch_row(result_resource))) {
            // Do whatever needs to happen to the rows...
        }
 
        // Free the result
        mysql_free_result(result_resource);
    }
}

[amit2781]

Thanks you very much Atli.

I solved my problem.

[amit2781]

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?

[Atli]

Perhaps this is what your are looking for?
21.10.3.19. mysql_fetch_fields()

[amit2781]

I think it is right that What I require will get it from -
enum enum_field_types Ex.
for TINY - 0, SMALLINT - 1 and so on.

As this is enum types so will consider the 0, 1, 2 ... so on.
But need to see in more details.

Thanks.

[Atli]

How about:

MYSQL_FIELD *field;
while((field = mysql_fetch_field(result)))
{
    printf("field name %s\n", field->type);
}

Wouldn't that print the type of the field?
The MYSQL_FIELD::type is a enum_field_types enum.

[amit2781]

Yes it will give for the fields and not for the rows.
like
mysql> select column_name, column_type from information_schema.columns where table_name='account' and table_schema='amit';
---------------------------------------------
| column_name | column_type |
---------------------------------------------|
| acct_num | int(11) |
| amount | decimal(2,0) |
---------------------------------------------

It will give me
MYSQL_TYPE_VAR_STRING for column_name where I required like
MYSQL_TYPE_LONG for "acct_num" .

If we will see in ORACLE it is giving the values for "acct_num" as 2 which indicates INT data type native value.

[amit2781]

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 fire query to get table_name it will return me 'v' also from information_schema.tables which I don't want.

[amit2781]

I have uninstalled MySQL 5.1.34 and tried to reinstalled but it is giving me error as :
The security settings could not be applied to the database because the connection has failed with the following error.

Error Nr. 1045
Access denied for user 'root'@'localhost' (using password: YES)

If a personal firewall is running on your machine, please make sure you have opened the TCP port 3306 for connections. Otherwise no client application can connect to the server. After you have opened the port please press [Retry] to apply the security settings.

If you are re-installing after you just uninstalled the MySQL server please note that the data directory was not removed automatically. Therefore the old password from your last installation is still needed to connect to the server. In this case please select skip now and re-run the Configuration Wizard from the start menu.

I tried from start menu also but the same thing happens.

Is there anything wrong while installation?