You are viewing a plain text version of this content. The canonical link for it is here.
Posted to ddlutils-dev@db.apache.org by "jim vester (JIRA)" <ji...@apache.org> on 2006/04/17 19:50:01 UTC

[jira] Created: (DDLUTILS-95) Can't extract data from Derby db when table included in a schema

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

         Key: DDLUTILS-95
         URL: http://issues.apache.org/jira/browse/DDLUTILS-95
     Project: DdlUtils
        Type: Bug

  Components: Ant Tasks  
 Environment: Derby database or any database with schema support and the userid not matching the schema name
    Reporter: jim vester
 Assigned to: Thomas Dudziak 


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

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira

[jira] Commented: (DDLUTILS-95) Can't extract data from Derby db when table included in a schema

Posted by "Martin Voigt (JIRA)" <ji...@apache.org>.
    [ http://issues.apache.org/jira/browse/DDLUTILS-95?page=comments#action_12413468 ] 

Martin Voigt commented on DDLUTILS-95:
--------------------------------------

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 ???

> Can't extract data from Derby db when table included in a schema
> ----------------------------------------------------------------
>
>          Key: DDLUTILS-95
>          URL: http://issues.apache.org/jira/browse/DDLUTILS-95
>      Project: DdlUtils
>         Type: Bug

>   Components: Ant Tasks
>  Environment: Derby database or any database with schema support and the userid not matching the schema name
>     Reporter: jim vester
>     Assignee: Thomas Dudziak

>
> 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

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira

[jira] Updated: (DDLUTILS-95) Can't extract data from Derby db when table included in a schema

Posted by "Thomas Dudziak (JIRA)" <ji...@apache.org>.
     [ http://issues.apache.org/jira/browse/DDLUTILS-95?page=all ]

Thomas Dudziak updated DDLUTILS-95:
-----------------------------------

    Fix Version: 1.1

> Can't extract data from Derby db when table included in a schema
> ----------------------------------------------------------------
>
>          Key: DDLUTILS-95
>          URL: http://issues.apache.org/jira/browse/DDLUTILS-95
>      Project: DdlUtils
>         Type: Bug

>   Components: Ant Tasks
>  Environment: Derby database or any database with schema support and the userid not matching the schema name
>     Reporter: jim vester
>     Assignee: Thomas Dudziak
>      Fix For: 1.1

>
> 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

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira