Change detection in MySQL.

amit_27

what is the default escape character in MySQL?

We have in ORACLE like ' " ', in MSSQL like ' [] ' which is used while creating table.

MSSQL
create table [aa/njh] (col1 INT);

ORACLE
create table "aa/njh" (col1 INT);


If I use both( ", []) in MYSQL while create table it is nor working fine.
My aim is to mhave table name with special character on numeric.

amit_27

i got solution as :
mysql> CREATE TABLE "test" (col INT);
ERROR 1064: You have an error in your SQL syntax...
mysql> SET sql_mode='ANSI_QUOTES';
mysql> CREATE TABLE "test" (col INT);
Query OK, 0 rows affected (0.00 sec)

bakuryu

that should work.

also your 2nd query worked. and a better way to use mysql is using phpmyadmin.

__________________
Please don't click here

amit_27

Hi

I am trying to connect to MySQL database. But it is giving me error as
"ERROR 1130: Host '' is not allowed to connect to this MySQL server"

mysql>musql -h 'a.b.c.d' -u root -p
Enter password: ********

"ERROR 1130: Host 'a.b.c.d' is not allowed to connect to this MySQL server"

Also I tried to do the following :

mysql> create user 'root'@'a.b.c.d' identified by 'root';
Query OK, 0 rows affected (0.00 sec)

mysql> grant all privileges on *.* to 'root'@'a.b.c.d' with gran
t option;
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for 'root'@'a.b.c.d';
+-------------------------------------------------------------------------------
-----------------------------------------------------------------------+
| Grants for root@a.b.c.d
|
+-------------------------------------------------------------------------------
-----------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'a.b.c.d' IDENTIFIED BY
PASSWORD '*81F5E21E35407D884A6CD4A731AEBFB6AF209E1B' WITH GRANT OPTION |
+-------------------------------------------------------------------------------
-----------------------------------------------------------------------+
1 row in set (0.00 sec)

>exit

Again trying from begining

>mysql -h a.b.c.d -u root –p
Enter password: ********
ERROR 1045 (28000): Access denied for user 'root'@'ps4743.persistent.co.in' (usi
ng password: YES)


Can you please tell me if anything goes wrong?

bakuryu

where is the MySQL server ? Is it in your local machine ? or on shared hosting server ?

amit_27

MySQL is on my local machine.

I solved my problem. Thanks for answering me.

amit_27

Hi,

I am trying to fire a query in MyLSQ 5.1.34 like

mysql> select table_schema from INFORMATION_SCHEMA.TABLES where table_name='table11';

Empty set (0.08 sec)

where table11 is not present in the INFORMATION_SCHEMA.TABLES.

I am expecting the error as table not found but as it is returning the empty set.

I tried to fire a query using mysql_real_query() API. How would I get the correct result as table not found?

Or any other ways to achieve same?

bakuryu

MySQL doesn't return errors like that. if it doesn't find anything it will return an empty set. However it will throw table not found error when the table on which you are running the query does not exists, like say you deleted the INFORMATION_SCHEMA.TABLES table.

select count(*) from INFORMATION_SCHEMA.TABLES where table_name='table11'

if it returns 0 then you can conclude table not found if you are having problems to deal with empty sets.

amit_27

How do I get the Parameter information of table?
I have table like

mysql> desc account;

+----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| acct_num | int(11) | NO | PRI | NULL | |
| amount | decimal(2,0) | YES | | NULL | |
+----------+--------------+------+-----+---------+-------+

I want all the information like colname, datatype, collength, precision, scale, Nullable. I tried using the

MYSQL_FIELD *fields;
unsigned int num_fields;

pResult = mysql_store_result(MySQLDB);
num_fields= mysql_num_fields(pResult);
fields = mysql_fetch_fields(pResult);
for( i = 0; i < num_fields; i++)
{
printf("Field %u is %s\n", i, fields[i].name);
}

It is giving me output like :
Field
Type
NULL
KEY
Default

row = mysql_fetch_row(pResult);
it will give me output as :
acct_num

Which I wanted to get.

But other information not gettting like datatype, precision, scale, nullable.

If I fire a query select * from account;It will gives me What I want in fields.
But it is not good if the there are 100 table which has millions of records and I want information of each table.
Is ther any other way to get only the reuqired information?

amit_27

In short to above query -----

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