DdlUtils
  1. DdlUtils
  2. DDLUTILS-95

Can't extract data from Derby db when table included in a schema

    Details

    • Type: Bug Bug
    • Status: Open
    • Priority: Major Major
    • Resolution: Unresolved
    • Affects Version/s: None
    • Fix Version/s: 1.2
    • Component/s: Ant Tasks
    • Labels:
      None
    • Environment:
      Derby database or any database with schema support and the userid not matching the schema name

      Description

      I have a derby Database named myDatabase.
      The database contains a schema rentabike.
      the schema contains a table named bikes.

      when I use the DatabaseToDdlTask to try
      and unload the schema and data, schema="rentabike" catalog=""
      userid="user" password="pass", an error occurs.

      The schema is extracted successfully however the data is not.

      Here is a snippet of the stack trace:
      org.apache.ddlutils.DynaSqlException : Error while performing a query
      at org.apache.ddlutils.platform.PlatformImplBase.query(PlatformImplBase.
      java:793)
      at org.apache.ddlutils.task.WriteDataToFileCommand.execute(WriteDataToFi
      leCommand.java :82)
      at org.apache.ddlutils.task.DatabaseTaskBase.executeCommands(DatabaseTas
      kBase.java:198)
      at org.apache.ddlutils.task.DatabaseTaskBase.execute(DatabaseTaskBase.ja
      va:222)
      at org.apache.tools.ant.UnknownElement.execute (UnknownElement.java:275)
      at org.apache.tools.ant.Task.perform(Task.java:364)
      at org.apache.tools.ant.Target.execute(Target.java:341)
      at org.apache.tools.ant.Target.performTasks(Target.java :369)
      at org.apache.tools.ant.Project.executeSortedTargets(Project.java:1216)
      at org.apache.tools.ant.Project.executeTarget(Project.java:1185)
      at org.apache.tools.ant.helper.DefaultExecutor.executeTargets (DefaultExe
      cutor.java:40)
      at org.apache.tools.ant.Project.executeTargets(Project.java:1068)
      at org.apache.tools.ant.Main.runBuild(Main.java:668)
      at org.apache.tools.ant.Main.startAnt(Main.java :187)
      at org.apache.tools.ant.launch.Launcher.run(Launcher.java:246)
      at org.apache.tools.ant.launch.Launcher.main(Launcher.java:67)
      Caused by: org.apache.derby.client.am.SqlException: Schema 'user' does not exist
      at org.apache.derby.client.am.Statement.completeSqlca(Unknown Source)
      at org.apache.derby.client.net.NetStatementReply.parsePrepareError(Unkno
      wn Source)
      at org.apache.derby.client.net.NetStatementReply.parsePRPSQLSTTreply (Unk
      nown Source)
      at org.apache.derby.client.net.NetStatementReply.readPrepareDescribeOutp
      ut(Unknown Source)
      at org.apache.derby.client.net.StatementReply.readPrepareDescribeOutput(
      Unknown Source)
      at org.apache.derby.client.net.NetStatement.readPrepareDescribeOutput_(U
      nknown Source)
      at org.apache.derby.client.am.Statement.readPrepareDescribeOutput(Unknow
      n Source)
      at org.apache.derby.client.am.Statement.flowExecute (Unknown Source)
      at org.apache.derby.client.am.Statement.executeQueryX(Unknown Source)
      at org.apache.derby.client.am.Statement.executeQuery(Unknown Source)
      at org.apache.commons.dbcp.DelegatingStatement.executeQuery (DelegatingSt
      atement.java:205)
      at org.apache.ddlutils.platform.PlatformImplBase.query(PlatformImplBase.
      java:787)

      I think it is occuring because the generated sql from the WritDataToFileCommand.execute

      does not take into account the schema name. Note userid is added in front of table names by default.

      writer.write(platform.query(model, "select * from "+tables[0].getName(), tables));

      The correct sql is select * from schema.table. or select * from rentabike.bikes

        Issue Links

          Activity

          Hide
          Martin Voigt added a comment -

          I had a similar problem and did some investigation:

          • environment for this to occur: Derby and Oracle databases
          • the database schema is written to flat file _without_ the qualifying schema of the different tables
          • the data is written to flat file _without_ the qualifying schema of the different tables
          • the sql statements constructed in _SqlBuilder.getInsertSql_ or _WriteDataToFileCommand.execute_ do not make use of the qualified tablenames that are used in Derby or Oracle ==> on unload they won't find the table on load they will load the wrong table

          examples: a table that should be accessed by sql

          select * from TEST.MYTABLE

          results in

          ===schema===

          <database name="schema_from_ant_task">
          <table name="MYTABLE">
          <column name="MYTABLE_ID" primaryKey="false" required="true" type="INTEGER" size="10" autoIncrement="false"/>
          . . .

          ===data===

          <?xml version='1.0' encoding='UTF-8'?>
          <data>
          <MYTABLE MYTABLE_ID="1"/>
          . . .

          Any suggestion where to store the schema TEST ???

          Show
          Martin Voigt added a comment - I had a similar problem and did some investigation: environment for this to occur: Derby and Oracle databases the database schema is written to flat file _ without _ the qualifying schema of the different tables the data is written to flat file _ without _ the qualifying schema of the different tables the sql statements constructed in _ SqlBuilder.getInsertSql _ or _ WriteDataToFileCommand.execute _ do not make use of the qualified tablenames that are used in Derby or Oracle ==> on unload they won't find the table on load they will load the wrong table examples: a table that should be accessed by sql select * from TEST.MYTABLE results in ===schema=== <database name="schema_from_ant_task"> <table name="MYTABLE"> <column name="MYTABLE_ID" primaryKey="false" required="true" type="INTEGER" size="10" autoIncrement="false"/> . . . ===data=== <?xml version='1.0' encoding='UTF-8'?> <data> <MYTABLE MYTABLE_ID="1"/> . . . Any suggestion where to store the schema TEST ???
          Hide
          Jason VanGundy added a comment -

          I'm also facing difficulties using this util as my username does not match the schema name. Code just needs to change to craft the table name as <schema>.<tablename> rather than just <tablename> so that it doesn't assume username and schema name are the same.

          Show
          Jason VanGundy added a comment - I'm also facing difficulties using this util as my username does not match the schema name. Code just needs to change to craft the table name as <schema>.<tablename> rather than just <tablename> so that it doesn't assume username and schema name are the same.

            People

            • Assignee:
              Thomas Dudziak
              Reporter:
              jim vester
            • Votes:
              2 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

              • Created:
                Updated:

                Development