1

I am using Sybase Power Designer to create a database from a physical data model (Sybase creates an SQL file) . When i import the SQL file with phpMyAdmin I have the following error:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'if exists(select 1 from sys.sysforeignkey where role='FK_ARTWORK_CREATES_ARTIST'' at line 7 .

Any ideas? Could this error appear due to errors on the physical model or there is another problem?

This is the code :

if exists(select 1 from sys.sysforeignkey where role='FK_ARTWORK_HAS_BUY') then
    alter table artwork
       delete foreign key FK_ARTWORK_HAS_BUY
end if;
AD7six
  • 63,116
  • 12
  • 91
  • 123
user3759619
  • 11
  • 1
  • 3
  • Which versions of mySQL and PowerDesigner are you using? – pascal Jun 24 '14 at 15:27
  • It looks like your Physical Data Model is not using the MySQL DBMS in PowerDesigner. For PowerDesigner, this syntax `select 1 from sys.sysforeignkey` only appears in SQL Anywhere (and Sybase IQ) DBMS. – pascal Jun 26 '14 at 07:28
  • it was 16.5(power designer) , when i tried the latest on my university's lab i didn't face the same problem. – user3759619 Aug 19 '14 at 22:55
  • Maybe the default DBMS definition when you used PowerDesigner at your university labs, was MySQL... Getting back to my initial question, are you sure that Physical Model (in PowerDesigner) targets MySQL? You can use `Database > Change DBMS` to verify it. – pascal Aug 29 '14 at 15:06

2 Answers2

1

The error you are getting is from MySQL. Regardless of the tool used to generate SQL, the database seems to be MySQL (or something is terribly wrong with your systems if they are confused and think they are MySQL).

The MySQL if statement (documented here) has to be inside a stored program. That means that this code only compiles inside a stored procedure, user defined function, or trigger. It doesn't "just work" on its own.

In addition, MySQL doesn't have sys tables. It uses information_schema tables. My strongest suggestion is to use tools appropriate for your actual database. If you are using a tool to generate Sybase, then use Sybase as the destination database. If you are using MySQL, then use a tool to generate MySQL code. Or, better yet, learn how to write the commands yourself.

Finally, if you intend to use Sybase, then connect to the correct database and your problem should be fixed.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • sorry!!i wasn't that clear! i re-edited the question!! thank you though!! PS: It's mandatory use sybase power designer. – user3759619 Jun 20 '14 at 10:38
  • 2
    @user3759619 then it's mandatory to review the sql it generates and make sure it's valid for the db you're going to feed it to. There is probably an option to say what sql flavour the generated output should be compatible with. – AD7six Jun 20 '14 at 10:42
  • @user3759619 . . . If you have to use PowerDesigner, then use Sybase. – Gordon Linoff Jun 20 '14 at 10:49
0

As I can't post here, and sqlfiddle.com temporary unavailable, I decided to post code with correct syntax for Sybase on the PasteBin

Hamlet Hakobyan
  • 32,965
  • 6
  • 52
  • 68