1

So presumably mysqldump can export to ansi compatible format so that you can import to other vendors' databases.

However trying to import to an apache derby I face all this syntax errors. Thought I try some regex and fix things but it seems that there are more than a couple.

Is there something I'm missing about mysqldump? Tried ddlutils but couldn't make progress after step 3 with these docs which seem out of date.

Do I have any other options with that?

qnoid
  • 2,346
  • 2
  • 26
  • 45

2 Answers2

1

The mysqldump option --compatible=ansi doesn't produce a SQL script that is fully ANSI compatible, it just tries to produce a result that is more ANSI compatible: "Produce output that is more compatible with other database systems or with older MySQL servers."

Unfortunately, the SQL syntax supported by each database is a bit different. SQL is a relatively weak standard, much weaker than Javascript or even C. Some databases support compatibility features like MySQL does, or even compatibility modes (disclaimer: I'm the main author of H2), but even this is not enough sometimes.

You have multiple options: you could change the generated SQL script by hand, or you could use another tool to copy the table structure / data. One such tool is the SQuirreL DB Copy Plugin (I have never actually used it, but from what I heard it works well).

Thomas Mueller
  • 48,905
  • 14
  • 116
  • 132
  • although it doesn't seem to solve the problem without any quirks it's a step in the right direction. – qnoid Sep 29 '10 at 12:13
0

If you can export your data in CSV format, or something similar, then you can use the Derby data import procedures to import your data: http://db.apache.org/derby/docs/10.7/ref/rrefimportproc.html

Bryan Pendleton
  • 16,128
  • 3
  • 32
  • 56