SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Member
    Join Date
    Mar 2011
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Smile Mysql view using workbench in Linux

    Hello!
    I'm new with mysql in a linux environment.So i need to create a view in linux using mysql workbench.Once i create a view using workbench,is says there is a DDL syntax error.
    Code MySQL:
    DROP VIEW IF EXISTS `transport`.`ol_account`;
    CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW  `transport`.`ol_account` AS (select `account_n1`.`JobN0` AS `intJEntryNo`,`account_n1`.`JobDate` AS `dtDate`,`account_n1`.`mainAccID` AS `mainAccID`,`account_n1`.`subAccID` AS `subAccID`,`account_n1`.`description` AS `description`,`account_n1`.`crAmo` AS `debit`,`account_n1`.`crDeb` AS `credit` from `account_n1`);
    This is the code i used.But in Windows format using navicat it works superbly.is it wrong?
    Can anyone please help me to come out from this mess.
    Thanks in advance.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    try it without the parentheses around the SELECT statement

    if that doesn't fix it, you may just have to tell us the actual error message
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    p.s. why are you defining the view in a different database?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Member
    Join Date
    Mar 2011
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Smile

    Thanks r937.
    I tried it.But It Says
    "Apply Changes to Object
    The object's DDL statement contains syntax errors.
    Are you sure you want to apply the DDL statement unchanged".This is the statement i used to create the view.
    Code MySQL:
    DROP VIEW IF EXISTS `transport`.`new_view`;
     
    CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW  `transport`.`new_view` AS  SELECT `account_n1`.`JobN0` AS `intJEntryNo`,`account_n1`.`JobDate` AS `dtDate`,`account_n1`.`mainAccID` AS `mainAccID`,`account_n1`.`subAccID` AS `subAccID`,`account_n1`.`description` AS `description`,`account_n1`.`crAmo` AS `debit`,`account_n1`.`crDeb` AS `credit` FROM `account_n1`;
    new_view is the name of the view.
    Transport is the Database Name.
    account_n1 is the Table Name.This code works superbly in windows platform.
    so can anyone help me to figure this out?.
    Thanks in Advance.

  5. #5
    SitePoint Addict
    Join Date
    Apr 2009
    Posts
    358
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Are you using the same version of workbench on both linux and windows?
    Doug G
    =====
    "If you ain't the lead dog, the view is always the same - Anon

  6. #6
    SitePoint Member
    Join Date
    Mar 2011
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Smile

    no.i'm using the workbench in linux & navicat & mysql query browser in windows.So once i try to execute the above query in workbench it gives me that error.
    I tried the code from the internet to create a new view using mysql workbench.And i followed as it say.But it doesnt work.Please help me to figure it out.
    Anyway there is another question.
    I have a database containing 5 views.Once i get a backup from the mysql administrator and restore using the same tool.After executing it Those 5 views are not created.But database tables are created by that name.Idont know how to solve this.Please help me to sort this. Thanks in Advance.I think it is a bug from mysql.

  7. #7
    SitePoint Addict
    Join Date
    Apr 2009
    Posts
    358
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Oh, I was just taking a guess that the linux version of mysql workbench might be older than the windows one. I haven't worked with views in mysql and I don't have much help. You might try doing the same sql from the command line mysql from the linux server and see if any more information shows up to help you identify the problem.
    Doug G
    =====
    "If you ain't the lead dog, the view is always the same - Anon


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •