#34 Export to DB button does not work with HSQLDB

v1.0 (example)
open
nobody
5
2014-07-13
2010-03-04
Anonymous
No

Create Table or Alter Table in the ER Diagram Window and then click the "Export to DB" button; it fails and errors out:

java.sql.SQLException: Invalid argument in JDBC call: table: null
at org.hsqldb.jdbc.Util.sqlException(Util.java:365)
at org.hsqldb.jdbc.Util.sqlException(Util.java:103)
at org.hsqldb.jdbc.Util.sqlException(Util.java:140)
at org.hsqldb.jdbc.Util.nullArgument(Util.java:169)
at org.hsqldb.jdbc.JDBCDatabaseMetaData.getImportedKeys(JDBCDatabaseMetaData.java:3956)
at org.insightech.er.editor.model.dbexport.db.PreTableExportManager.dropForeignKeys(PreTableExportManager.java:185)
at org.insightech.er.editor.model.dbexport.db.PreTableExportManager.run(PreTableExportManager.java:91)
at org.insightech.er.editor.view.dialog.dbexport.ExportDBSettingDialog.perfomeOK(ExportDBSettingDialog.java:119)
at org.insightech.er.common.dialog.AbstractDialog.buttonPressed(AbstractDialog.java:215)
at org.eclipse.jface.dialogs.Dialog$2.widgetSelected(Dialog.java:624)
at org.eclipse.swt.widgets.TypedListener.handleEvent(TypedListener.java:228)
at org.eclipse.swt.widgets.EventTable.sendEvent(EventTable.java:84)
at org.eclipse.swt.widgets.Widget.sendEvent(Widget.java:1176)
at org.eclipse.swt.widgets.Display.runDeferredEvents(Display.java:3493)
at org.eclipse.swt.widgets.Display.readAndDispatch(Display.java:3112)
at org.eclipse.jface.window.Window.runEventLoop(Window.java:825)
at org.eclipse.jface.window.Window.open(Window.java:801)
at org.insightech.er.editor.view.action.dbexport.ExportToDBAction.execute(ExportToDBAction.java:52)
at org.insightech.er.editor.view.action.AbstractBaseAction.runWithEvent(AbstractBaseAction.java:67)
at org.eclipse.ui.actions.RetargetAction.runWithEvent(RetargetAction.java:230)
at org.eclipse.jface.action.ActionContributionItem.handleWidgetSelection(ActionContributionItem.java:584)
at org.eclipse.jface.action.ActionContributionItem.access$2(ActionContributionItem.java:501)
at org.eclipse.jface.action.ActionContributionItem$6.handleEvent(ActionContributionItem.java:452)
at org.eclipse.swt.widgets.EventTable.sendEvent(EventTable.java:84)
at org.eclipse.swt.widgets.Widget.sendEvent(Widget.java:1176)
at org.eclipse.swt.widgets.Display.runDeferredEvents(Display.java:3493)
at org.eclipse.swt.widgets.Display.readAndDispatch(Display.java:3112)
at org.eclipse.ui.internal.Workbench.runEventLoop(Workbench.java:2405)
at org.eclipse.ui.internal.Workbench.runUI(Workbench.java:2369)
at org.eclipse.ui.internal.Workbench.access$4(Workbench.java:2221)
at org.eclipse.ui.internal.Workbench$5.run(Workbench.java:500)
at org.eclipse.core.databinding.observable.Realm.runWithDefault(Realm.java:332)
at org.eclipse.ui.internal.Workbench.createAndRunWorkbench(Workbench.java:493)
at org.eclipse.ui.PlatformUI.createAndRunWorkbench(PlatformUI.java:149)
at org.eclipse.ui.internal.ide.application.IDEApplication.start(IDEApplication.java:113)
at org.eclipse.equinox.internal.app.EclipseAppHandle.run(EclipseAppHandle.java:194)
at org.eclipse.core.runtime.internal.adaptor.EclipseAppLauncher.runApplication(EclipseAppLauncher.java:110)
at org.eclipse.core.runtime.internal.adaptor.EclipseAppLauncher.start(EclipseAppLauncher.java:79)
at org.eclipse.core.runtime.adaptor.EclipseStarter.run(EclipseStarter.java:368)
at org.eclipse.core.runtime.adaptor.EclipseStarter.run(EclipseStarter.java:179)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.eclipse.equinox.launcher.Main.invokeFramework(Main.java:559)
at org.eclipse.equinox.launcher.Main.basicRun(Main.java:514)
at org.eclipse.equinox.launcher.Main.run(Main.java:1311)
at org.eclipse.equinox.launcher.Main.main(Main.java:1287)
Caused by: org.hsqldb.HsqlException: Invalid argument in JDBC call: table: null
at org.hsqldb.error.Error.error(Error.java:77)
... 46 more

I believe the problem is that the export to the database fails to generate the drop statements nor the later DDL for Create/Alter table statement.

Looks like a problem in package org.insightech.er.editor.model.dbexport.db

also:

org.insightech.er.editor.view.dialog.dbexport.ExportDBSettingDialog.java
ExportToDBManager.java
PreTableExportManager.java
HSQLDBDBManager.java
ExportToDBAction.java

Export to DDL works though.

Discussion

  • Thank you for your report.
    I fixed it at v20100307-2244.
    Please update.

     
  • Sadly, my report with the latest plugin did not work, same error as above.

     
  • To follow up my previous comment, ... I am checking the code for tablespace generation b/c HSQLDB does not have tablespaces.

     
  • Please disregard the bits about the tablespace.

    When I trace the code, the diagram contents (such as a new table or new column definition) are not passed to the DB ExportManager code so the SQL generated is 'null'.

    Also the driver class information is not propagating to the export; and no driver is found for HSQLDB. Possible that this is because of the datatools conflict.

    I am continuing to investigate.

     
  • If you use the patch below as a workaround, you can use the Export to DB button. But this only applies to the initial table creation for HSQLDB.

    I have not completed investigating why the 'drop' code is causing the Export button to fail.

    Also, I am not sure if there is check in the code to drop a table that is already populated with data.

    ### Eclipse Workspace Patch 1.0
    #P org.insightech.er
    Index: src/org/insightech/er/editor/model/dbexport/db/PreTableExportManager.java
    ===================================================================
    RCS file: /cvsroot/ermaster/org.insightech.er/src/org/insightech/er/editor/model/dbexport/db/PreTableExportManager.java,v
    retrieving revision 1.7
    diff -u -r1.7 PreTableExportManager.java
    --- src/org/insightech/er/editor/model/dbexport/db/PreTableExportManager.java 14 Mar 2010 12:00:20 -0000 1.7
    +++ src/org/insightech/er/editor/model/dbexport/db/PreTableExportManager.java 22 Mar 2010 20:20:12 -0000
    @@ -89,21 +89,25 @@
    public void run() {
    try {
    StringBuffer sb = new StringBuffer();
    - sb.append(this.dropViews());
    +/* sb.append(this.dropViews());
    sb.append(this.dropForeignKeys());
    sb.append(this.dropTables());
    sb.append(this.dropSequences());
    +
    +*/

    sb.append(this.executeDDL());

    this.ddl = sb.toString();

    +
    +
    } catch (Exception e) {
    this.exception = e;
    }
    }

    - private String dropSequences() throws SQLException {
    +/* private String dropSequences() throws SQLException {
    StringBuffer ddl = new StringBuffer();

    ResultSet sequenceSet = null;
    @@ -132,16 +136,16 @@
    }

    return ddl.toString();
    - }
    + }*/

    - private String dropSequence(String sequenceName) throws SQLException {
    +/* private String dropSequence(String sequenceName) throws SQLException {
    String sql = "DROP SEQUENCE " + this.ifExistsOption + sequenceName
    + ";";

    return sql;
    }
    -
    - private String dropViews() throws SQLException {
    +*/
    +/* private String dropViews() throws SQLException {
    StringBuffer ddl = new StringBuffer();

    ResultSet viewSet = null;
    @@ -171,13 +175,13 @@

    return ddl.toString();
    }
    -
    - private String dropView(String viewName) throws SQLException {
    +*/
    +/* private String dropView(String viewName) throws SQLException {
    String sql = "DROP VIEW " + this.ifExistsOption + viewName + ";";

    return sql;
    }
    -
    +*/
    protected String dropForeignKeys() throws SQLException {
    StringBuffer ddl = new StringBuffer();

    @@ -225,7 +229,7 @@
    return sql;
    }

    - private String dropTables() throws SQLException, InterruptedException {
    +/* private String dropTables() throws SQLException, InterruptedException {
    StringBuffer ddl = new StringBuffer();

    ResultSet tableSet = null;
    @@ -245,8 +249,8 @@
    try {
    this.checkTableExist(con, tableName);
    } catch (SQLException e) {
    - // �e�[�u����񂪎擾�ł��Ȃ��ꍇ�i���̃��[�U�̏��L���Ȃǂ̏ꍇ�j�A
    - // ���̃e�[�u���͎g�p���Ȃ��B
    + // �e�[�u����񂪎擾�ł��Ȃ��ꍇ�i���̃��[�U�̏��L���Ȃǂ̏ꍇ�j�A
    + // ���̃e�[�u���͎g�p���Ȃ��B
    continue;
    }

    @@ -262,14 +266,14 @@
    }

    return ddl.toString();
    - }
    + }*/

    - private String dropTable(String tableName) throws SQLException {
    +/* private String dropTable(String tableName) throws SQLException {
    String sql = "DROP TABLE " + this.ifExistsOption + tableName + ";";

    return sql;
    }
    -
    +*/
    private String executeDDL() throws SQLException {
    DDLCreator ddlCreator = DBManagerFactory.getDBManager(this.diagram)
    .getDDLCreator(this.diagram, true);
    @@ -287,7 +291,7 @@
    }

    /**
    - * errorSql ���擾���܂�.
    + * errorSql ���擾���܂�.
    *
    * @return errorSql
    */
    @@ -296,7 +300,7 @@
    }

    /**
    - * ddl ���擾���܂�.
    + * ddl ���擾���܂�.
    *
    * @return ddl
    */

     
  • Hi.
    I am sorry for making you suffer.
    Umm. My easy test goes well even if it is with 'drop' code.
    If there is no problem, please give me the ddl which cause error.

     
  • Is the test on HSQLDB or Oracle? a unit test or a full SIT. ?

    HSQLDB uses different syntax in the information catalog to lookup database object that is different than V$TABLES.

    I will try to get the DDL generated and post.

     
  • The following patch allows the Export to DB button to work. You can tweak the DDL SQL in the dialog as needed:

    ### Eclipse Workspace Patch 1.0
    #P org.insightech.er
    Index: src/org/insightech/er/db/impl/hsqldb/HSQLDBPreTableExportManager.java
    ===================================================================
    RCS file: /cvsroot/ermaster/org.insightech.er/src/org/insightech/er/db/impl/hsqldb/HSQLDBPreTableExportManager.java,v
    retrieving revision 1.2
    diff -u -r1.2 HSQLDBPreTableExportManager.java
    --- src/org/insightech/er/db/impl/hsqldb/HSQLDBPreTableExportManager.java 14 Aug 2009 11:13:50 -0000 1.2
    +++ src/org/insightech/er/db/impl/hsqldb/HSQLDBPreTableExportManager.java 16 Apr 2010 21:52:04 -0000
    @@ -1,7 +1,52 @@
    package org.insightech.er.db.impl.hsqldb;

    +import java.sql.ResultSet;
    +import java.sql.SQLException;
    +
    import org.insightech.er.editor.model.dbexport.db.PreTableExportManager;

    public class HSQLDBPreTableExportManager extends PreTableExportManager {

    + @Override
    + protected String dropForeignKeys() throws SQLException {
    + StringBuffer ddl = new StringBuffer();
    +
    + ResultSet tableSet = null;
    +
    + try {
    + tableSet = metaData.getTables(null, null, null,
    + new String[] { "TABLE" });
    +
    + while (tableSet.next()) {
    + String tableName = tableSet.getString("TABLE_NAME");
    + String schema = tableSet.getString("TABLE_SCHEM");
    + tableName = this.dbSetting.getTableNameWithSchema(tableName,
    + schema);
    +/*
    + * This is copied from MYSQL preTableExport -- DROP FOREIGN KEY is not supported in HSQLDB
    + *
    + String sql = "ALTER TABLE " + tableName + " DROP FOREIGN KEY;";
    +
    +*/
    +
    + String sql = "ALTER TABLE " + tableName + ";";
    +
    +
    + ddl.append(sql);
    + ddl.append("\r\n");
    + }
    +
    + } finally {
    + if (tableSet != null) {
    + tableSet.close();
    + }
    + }
    +
    + return ddl.toString();
    + }
    +
    + @Override
    + protected void prepareNewNames() {
    + }
    +
    }

     
  • Eclipse log reporting successful DDL generation:

    Time to load bundles: 11
    Starting application: 1974
    Application Started: 6871
    Apr 16, 2010 5:55:56 PM org.insightech.er.editor.model.dbexport.db.ExportToDBManager executeDDL
    INFO: DROP TABLE B_TEST_TABLE
    Apr 16, 2010 5:55:56 PM org.insightech.er.editor.model.dbexport.db.ExportToDBManager executeDDL
    INFO: DROP TABLE RTW
    Apr 16, 2010 5:55:56 PM org.insightech.er.editor.model.dbexport.db.ExportToDBManager executeDDL
    INFO: DROP TABLE TESTTABLE1
    Apr 16, 2010 5:55:56 PM org.insightech.er.editor.model.dbexport.db.ExportToDBManager executeDDL
    INFO: /* Create Tables */

    CREATE TABLE B_TEST_TABLE
    (
    COLUMN1 VARCHAR(128)
    )
    Apr 16, 2010 5:55:56 PM org.insightech.er.editor.model.dbexport.db.ExportToDBManager executeDDL
    INFO: CREATE TABLE TESTTABLE1
    (
    AVARCHAR VARCHAR(12)
    )