Change detection in MySQL.

bakuryu

There is the COLUMNS table : MySQL :: MySQL 5.0 Reference Manual :: 19.3 The INFORMATION_SCHEMA COLUMNS Table

In PHP you have some function too :

PHP: mysql_fetch_field - Manual
PHP: mysql_field_type - Manual
PHP: mysql_field_flags - Manual
PHP: mysql_field_len - Manual

__________________
Please don't click here

amit_27

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?

bakuryu

does it return any result set when you directly execute the query ?

amit_27

Thanks you very much. I solved my problem.

amit_27

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.

bakuryu

mysql_fetch_field() returns it, the maximum length of a column, so create a date field and check the value of max_length property of the returned value.

amit_27

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.

bakuryu

which version of MySQL ? It might be a bug also. Create another database and add tales and a view and check the information_schema database.

amit_27

MySQL 5.1.34 which is recommended version.

Yes I tried creating another database nad table,views still I see the entry of views when I do show tables; Snapshot of What I done.

mysql> create database try;
Query OK, 1 row affected (0.00 sec)

mysql> use try;
Database changed
mysql> show tables;
Empty set (0.00 sec)

mysql> create table tt(val INT);
Query OK, 0 rows affected (0.01 sec)

mysql> create view temp as select * from tt;
Query OK, 0 rows affected (0.02 sec)

mysql> show tables;
+---------------+
| Tables_in_try |
+---------------+
| temp |
| tt |
+---------------+
2 rows in set (0.00 sec)

mysql> select table_name, view_definition from information_schema.views;
+------------+--------------------------------------------------+
| table_name | view_definition |
+------------+--------------------------------------------------+
| temp | select `try`.`tt`.`val` AS `val` from `try`.`tt` |
+------------+--------------------------------------------------+
1 row in set (0.08 sec)

I will get temp iun both result.

amit_27

Hi,

I have created 4 tables in 'amit' database and then I deleted them. Still I able to get information about the table_schema for the table deleted.
After drop table when I fire a query for table_schema from information_schema.tables it will give me result as 'amit'. I didn't understand why it is happnes.

See the below steps done.

mysql> use amit;
mysql> show tables;
+----------------+
| Tables_in_amit |
+----------------+
| clconf |
| clmstr |
| clmt14 |
| clsync |
+----------------+
6 rows in set (0.00 sec)

mysql> select table_name from information_schema.table
+---------------------------------------+
| table_name |
+---------------------------------------+
| CHARACTER_SETS |
| COLLATIONS |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| COLUMNS |
| COLUMN_PRIVILEGES |
| ENGINES |
| EVENTS |
| FILES |
| GLOBAL_STATUS |
| GLOBAL_VARIABLES |
| KEY_COLUMN_USAGE |
| PARTITIONS |
| PLUGINS |
| PROCESSLIST |
| PROFILING |
| REFERENTIAL_CONSTRAINTS |
| ROUTINES |
| SCHEMATA |
| SCHEMA_PRIVILEGES |
| SESSION_STATUS |
| SESSION_VARIABLES |
| STATISTICS |
| TABLES |
| TABLE_CONSTRAINTS |
| TABLE_PRIVILEGES |
| TRIGGERS |
| USER_PRIVILEGES |
| VIEWS |
| account |
| clconf |
| clsync |
| clmt14 |
| clmstr |
| columns_priv |
| db |
| event |
| func |
| general_log |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| host |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| servers |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
| temp |
| tt |
+---------------------------------------+
58 rows in set (0.00 sec)

************************************************** ******************
Tables are present in information_schema.tables beforte delete
************************************************** ******************
mysql> select table_schema from information_schema.tables where table_name='CLSync';
+--------------+
| table_schema |
+--------------+
| amit |
+--------------+
1 row in set (0.01 sec)


mysql> DROP TABLE amit.CLMT14 ;
Query OK, 0 rows affected (0.02 sec)

mysql> DROP TABLE amit.CLSync ;
Query OK, 0 rows affected (0.00 sec)

mysql> DROP TABLE amit.CLMstr ;
Query OK, 0 rows affected (0.00 sec)

mysql> DROP TABLE amit.CLConf ;
Query OK, 0 rows affected (0.01 sec)

mysql> select table_schema from information_schema.tables where table_name='CLSync';
+--------------+
| table_schema |
+--------------+
| amit |
+--------------+
1 row in set (0.00 sec)


mysql> select table_schema from information_schema.tables where table_name='clsync';
Empty set (0.00 sec)

mysql> select table_schema from information_schema.tables where table_name='CLCYNC';
Empty set (0.00 sec)

************************************************** ***
I used using uppercase, lowercase letter and above u can see the difference in query.
I have created table using names as - CLSync, CLConf, CLMT14, CLMstr
************************************************** ***

mysql> select table_name from information_schema.table
+---------------------------------------+
| table_name |
+---------------------------------------+
| CHARACTER_SETS |
| COLLATIONS |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| COLUMNS |
| COLUMN_PRIVILEGES |
| ENGINES |
| EVENTS |
| FILES |
| GLOBAL_STATUS |
| GLOBAL_VARIABLES |
| KEY_COLUMN_USAGE |
| PARTITIONS |
| PLUGINS |
| PROCESSLIST |
| PROFILING |
| REFERENTIAL_CONSTRAINTS |
| ROUTINES |
| SCHEMATA |
| SCHEMA_PRIVILEGES |
| SESSION_STATUS |
| SESSION_VARIABLES |
| STATISTICS |
| TABLES |
| TABLE_CONSTRAINTS |
| TABLE_PRIVILEGES |
| TRIGGERS |
| USER_PRIVILEGES |
| VIEWS |
| account |
| columns_priv |
| db |
| event |
| func |
| general_log |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| host |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| servers |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
| temp |
| tt |
+---------------------------------------+
54 rows in set (0.00 sec)

************************************************** ******************
Tables are not present in information_schema.tables after delete
************************************************** ******************
But still responding to query for table_schema.