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_SCH EMA 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_SCH EMA COLUMNS.

While retrieving the data from the result of query I faced following problem.
[code=mysql]
mysql> select select COLUMN_NAME, DATA_TYPE, IS_NULLABLE, CHARACTER_MAXIM UM_LENGTH, NUMERIC_PRECISI ON from INFORMATION_SCH EMA.COLUMNS where table_name='acc ount');[/code]

In coding I run the query using mysql_real_quer y() whose result is stored in
[code=php]
pResult = mysql_store_res ult(pMySQLDB);
fields = mysql_fetch_fie ld(pResult);
my_ulonglong noOfRows = mysql_num_rows( pResult);
row = mysql_fetch_row (pResult);[/code]

it will not return any rows.

But in fields I get select COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIM UM_LENGTH, NUMERIC_PRECISI ON 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_que ry 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:
[code=php]<?php
// Connect to MySQL using the procedural style mysqli functions.
$dbLink = mysqli_connect( "localhost" , "user", "passwd", "dbName");
if(mysqli_conne ct_errno())
{
printf("MySQL Connection failed! %s", mysqli_connect_ error());
}

// Create a query to execute.
$sql = "SELECT
COLUMN_NAME,
DATA_TYPE, IS_NULLABLE,
CHARACTER_MAXIM UM_LENGTH,
NUMERIC_PRECISI ON
FROM INFORMATION_SCH EMA.COLUMNS
WHERE
table_name='tab leName'
AND table_schema='d bName'";

// Execute the query
if(mysqli_real_ query($dbLink, $sql))
{
// Fetch the results
$result = mysqli_store_re sult($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_as soc($result)) {
echo '<br />Row #', (++$rowNo) ,'<br />';
foreach($row as $_colName => $_colValue) {
echo " - {$_colName} = {$_colValue}<br />";
}
}

// Free the result object
mysqli_free_res ult($result);
}
else {
printf("Query execution failed! %s", mysqli_error($d bLink));
}

// Close the connection
mysqli_close($d bLink);
?>[/code]
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]

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)
[code=c]// Execute query
result_success = mysql_real_quer y(&connection, &query_strin g, query_length);
if(!result_succ ess) {
// Error. Query failed
}
else {
// Get the result
result_resource = mysql_store_res ult(&connection );
if(!result_reso urce) {
// Error. Failed to fetch resource.
}
else {
// Get field definitions
num_fields = mysql_num_field s(result_resour ce);
fields = mysql_fetch_fie lds(result_reso urce)

// Get row count
row_count = mysql_num_rows( result_resource );

// Fetch rows
while((row = mysql_fetch_row (result_resourc e))) {
// Do whatever needs to happen to the rows...
}

// Free the result
mysql_free_resu lt(result_resou rce);
}
}[/code]

[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(colH andle, OCI_DTYPE_PARAM , (dvoid *) &dataType, 0,
OCI_ATTR_DATA_T YPE, 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_fie lds()

[amit2781]

I think it is right that What I require will get it from -
enum enum_field_type s 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:
[code=c]MYSQL_FIELD *field;
while((field = mysql_fetch_fie ld(result)))
{
printf("field name %s\n", field->type);
}[/code]
Wouldn't that print the type of the field?
The MYSQL_FIELD::ty pe is a enum_field_type s enum.

[amit2781]

Yes it will give for the fields and not for the rows.
like
mysql> select column_name, column_type from information_sch ema.columns where table_name='acc ount' and table_schema='a mit';
---------------------------------------------
| 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_nu m 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_sch ema.views;
mysql> select table_name from information_sch ema.tables;
+------------+
| table_name |
+------------+
| v |
+------------+

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

My problem is when I fire query to get table_name it will return me 'v' also from information_sch ema.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'@'localho st' (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?