1. We have moved from vBulletin to XenForo and you are viewing the site in the middle of the move. Though the functional aspect of everything is working fine, we are still working on other changes including the new design on Xenforo.
    Dismiss Notice

IS there any MySQL Default tables like DUAL in ORACLE.

Discussion in 'MySQL' started by amit_27, May 20, 2009.

  1. amit_27

    amit_27 New Member

    Joined:
    May 19, 2009
    Messages:
    20
    Likes Received:
    0
    Trophy Points:
    0
    IS there any default table mysql server automatically creates and manages these tables like INSERTED, DELETED table in MSSQL, DUAL table in ORACLE.

    I have used in mssql a query in trigger for insert operation
    declare trg_cursor cursor for select fname from inserted;

    In oracle a query in trigger for insert operation
    select system.clsyncseq.nextval into synchpointnumber from dual;

    like this i need default tables for mysql to fire a same query.

    I have written a trigger on table t1 for insert. When inserted a row to t1 my trigger will read the information from INSERTED, DUAL tables in case of MSSQL, ORACLE instead of table t1.
    The same thing I need to write for MySQL for INSERT, UPDATE, DELETE operation.
    Can you please direct me to achieve this?
     
  2. pradeep

    pradeep Team Leader

    Joined:
    Apr 4, 2005
    Messages:
    1,646
    Likes Received:
    86
    Trophy Points:
    0
    Occupation:
    Programmer
    Location:
    Kolkata, India
    Home Page:
    Code:
    declare trg_cursor cursor for select fname 
    
     
  3. amit_27

    amit_27 New Member

    Joined:
    May 19, 2009
    Messages:
    20
    Likes Received:
    0
    Trophy Points:
    0
    created table account :
    Code:
    +----------+---------------+------+-----+---------+-------+
    | Field    | Type          | Null | Key | Default | Extra |
    +----------+---------------+------+-----+---------+-------+
    | acct_num | int(11)       | YES  |     | NULL    |       |
    | amount   | decimal(10,2) | YES  |     | NULL    |       |
    +----------+---------------+------+-----+---------+-------+
    
    If I use (MSSQL type)
    declare trg_cursor cursor for select acct_num;

    while insert :
    mysql> INSERT INTO account (acct_num, amount) VALUES (0, 23);$
    ERROR 1054 (42S22): Unknown column 'acct_num' in 'field list'
     
  4. pradeep

    pradeep Team Leader

    Joined:
    Apr 4, 2005
    Messages:
    1,646
    Likes Received:
    86
    Trophy Points:
    0
    Occupation:
    Programmer
    Location:
    Kolkata, India
    Home Page:
    Code:
    declare trg_cursor cursor for select acct_num;
    
    You have to specify a table name here
     
  5. amit_27

    amit_27 New Member

    Joined:
    May 19, 2009
    Messages:
    20
    Likes Received:
    0
    Trophy Points:
    0
    Exactly, I need to specify the table name here. I was asking for the table name to specify in my query like DUAL, INSERTED, DELETED in ORACLE, MSSQL.

    Like INSERTED in MSSQL, DUAL table in ORACLE temporary store the updated, inserted, deleted row whilst and I can get the acct_num of only new, delete, updated row form DUAL table for my transaction.

    The same thing I want to achieve using MySQL. But I didn't see any table which
     
  6. pradeep

    pradeep Team Leader

    Joined:
    Apr 4, 2005
    Messages:
    1,646
    Likes Received:
    86
    Trophy Points:
    0
    Occupation:
    Programmer
    Location:
    Kolkata, India
    Home Page:
    Dual is available in MySQL as well

    Code:
    SELECT COUNT(*) col FROM dual WHERE 1=0
     
  7. amit_27

    amit_27 New Member

    Joined:
    May 19, 2009
    Messages:
    20
    Likes Received:
    0
    Trophy Points:
    0
    Yes, we can use the dual table but not like the Oracle. If we don't have any table reference then we can use dual in MySQL. like for arithmatic operations.
     
  8. amit_27

    amit_27 New Member

    Joined:
    May 19, 2009
    Messages:
    20
    Likes Received:
    0
    Trophy Points:
    0
    Thanks pradeep answering my question. I solved the problem.

    As MySQL support "For Each Row" mechanisam, we get the directly the output which want using "new" and "old" keyword.

    Lke new.acct_num, old.acct_num.

    Default table not required and also not available.
     
  9. amit_27

    amit_27 New Member

    Joined:
    May 19, 2009
    Messages:
    20
    Likes Received:
    0
    Trophy Points:
    0
    What is the default schema name in MySQL 5.1.34 like in ORACLE is username (system), in MSSQL is "dbo"?


    I think it is database name Isn't it?
     
  10. amit_27

    amit_27 New Member

    Joined:
    May 19, 2009
    Messages:
    20
    Likes Received:
    0
    Trophy Points:
    0
    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.
     
  11. amit_27

    amit_27 New Member

    Joined:
    May 19, 2009
    Messages:
    20
    Likes Received:
    0
    Trophy Points:
    0
    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)
     
  12. pradeep

    pradeep Team Leader

    Joined:
    Apr 4, 2005
    Messages:
    1,646
    Likes Received:
    86
    Trophy Points:
    0
    Occupation:
    Programmer
    Location:
    Kolkata, India
    Home Page:
    Default escape character is '`' (backtick)
     
  13. amit_27

    amit_27 New Member

    Joined:
    May 19, 2009
    Messages:
    20
    Likes Received:
    0
    Trophy Points:
    0
    Yes it is working fine.
     
  14. amit_27

    amit_27 New Member

    Joined:
    May 19, 2009
    Messages:
    20
    Likes Received:
    0
    Trophy Points:
    0
    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?
     
  15. pradeep

    pradeep Team Leader

    Joined:
    Apr 4, 2005
    Messages:
    1,646
    Likes Received:
    86
    Trophy Points:
    0
    Occupation:
    Programmer
    Location:
    Kolkata, India
    Home Page:
    Code:
    grant all privileges on *.* to 'root'@'%' with grant option;
     
  16. amit_27

    amit_27 New Member

    Joined:
    May 19, 2009
    Messages:
    20
    Likes Received:
    0
    Trophy Points:
    0
    I have installed MySQl using "mysql-essential-5.1.34-win32.msi". I have installed server so no need of client seperately.

    While i trying to connect in my application using "mysql_real_connect() it is giving me error like:
    ERROR 162 DB command failed (MDS_E_MYSQL_DB_CONNECT: Client does not support authentication protocol requested by server; consider upgrading MySQL client) - MySQL_Error='1251'

    I am not understood why it is telling to upgrade client.

    Can you please take the honor to get rid of this problem?
     
  17. amit_27

    amit_27 New Member

    Joined:
    May 19, 2009
    Messages:
    20
    Likes Received:
    0
    Trophy Points:
    0
    Resolved the problem.

    Thanks Pradeep for answering me.
     
  18. amit_27

    amit_27 New Member

    Joined:
    May 19, 2009
    Messages:
    20
    Likes Received:
    0
    Trophy Points:
    0
    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?
     
  19. pradeep

    pradeep Team Leader

    Joined:
    Apr 4, 2005
    Messages:
    1,646
    Likes Received:
    86
    Trophy Points:
    0
    Occupation:
    Programmer
    Location:
    Kolkata, India
    Home Page:
    "Select * from table_not_present" will give an error.
     
  20. amit_27

    amit_27 New Member

    Joined:
    May 19, 2009
    Messages:
    20
    Likes Received:
    0
    Trophy Points:
    0
    Yes it is giving the correct result.

    However I have written an application in which input is table name and need to get the database name (table_schema) from INFORMATION_SCHEMA.TABLES.

    So I can't write the query using table_not_found.

    Or DO I need to use other API instead of mysql_real_query().
    I have seen that in other implementation of ORACLE they used commands in ORACLE to fire SQL like

    1. OCIHandleAlloc
    2. //Prepare for the query execution
    ret = OCIStmtPrepare()

    3. ret = OCIAttrGet()

    4. ret = OCIStmtExecute()

    5. OCIDefineByPos()


    Like this If I need to prepare for MySQL which API I need to use.
     

Share This Page