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.

Expand|Select|Wrap|Line Numbers

 
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

Expand|Select|Wrap|Line Numbers

 
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:

Expand|Select|Wrap|Line Numbers

 <?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

Quote:

Originally Posted by amit2781

I am writing application in C++.

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)

Expand|Select|Wrap|Line Numbers

 // 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:

Expand|Select|Wrap|Line Numbers

 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?

How to get Table schema information ?

re: How to get Table schema information ?

re: How to get Table schema information ?

re: How to get Table schema information ?

re: How to get Table schema information ?

re: How to get Table schema information ?

re: How to get Table schema information ?

re: How to get Table schema information ?

re: How to get Table schema information ?

re: How to get Table schema information ?

re: How to get Table schema information ?

re: How to get Table schema information ?

re: How to get Table schema information ?

re: How to get Table schema information ?

Similar MySQL Database bytes

/bytes/development

/bytes/it

/bytes/about