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
13 23437 Atli 5,058
Expert 4TB
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?
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 5,058
Expert 4TB
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.
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 5,058
Expert 4TB @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);
-
}
-
}
Thanks you very much Atli.
I solved my problem.
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 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 5,058
Expert 4TB
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.
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.
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.
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?
Sign in to post your reply or Sign up for a free account.
Similar topics
by: toedipper |
last post by:
Hello,
I am designing a table of vehicle types, nothing special, just a list of
unique vehicle types such as truck, lorry, bike, motor bike, plane, tractor
etc etc
For the table design I am...
|
by: Vicki |
last post by:
Using VS2005 beta 2, I have an xsd provided by a vendor that I want to make
into a strongly typed data set. In VS 2003 the MSDataSetGenerator tool gives
a similar error, which I was told was to be...
|
by: dotyet |
last post by:
I have been given the daunting task of sql query tuning. I am looking
for ways to get started with that. I am on DB2 UDB 8.2 (8.1 with Fixpak
8) on Windows. One point which I could think about...
|
by: Benny Raymond |
last post by:
I have a dataset setup with a main table that has some information in it
along with an element that is a nested table so that I can store rows of
history information for that one row in the main...
|
by: Wei Shi |
last post by:
Hi, does anyone know how to get the schema information
of a table. More specifically, I would like to know
1. which field(s) are primary keys?
2. the data type of each field of a table?
3. If...
|
by: N. Graves |
last post by:
Thanks for taking time to read my questions.
I'm looking for a way in Access that I could run a query that would
return table schema information. Specifically I need to get the
column name,...
|
by: Derek |
last post by:
I am creating an intranet using Visual Web Developer Express Edition.
Everything has been working OK until yesterday when I started getting 62
messages all beginning "Could not find schema...
|
by: JPS |
last post by:
Does anyone know how to query a table and get the schema
informationback as well as the values in the fields. Here is what I
have done so far. I cannot get both.
using (SqlConnection conF =...
|
by: =?Utf-8?B?Sm9iIExvdA==?= |
last post by:
how can persist schema for a sql server table into an xml file from .net
application?
thanks
|
by: klenwell |
last post by:
I'm in the process of refactoring the php code base I've amassed over
the last few years into an object-oriented framework. I'm about to
start in on the authentication/login extension and I've...
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
| |