db-ddlutils-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Martin Voigt (JIRA)" <j...@apache.org>
Subject [jira] Commented: (DDLUTILS-95) Can't extract data from Derby db when table included in a schema
Date Fri, 26 May 2006 13:46:30 GMT
    [ 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


Mime
View raw message