You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@cayenne.apache.org by "open.pumpkin" <op...@gmail.com> on 2009/09/07 11:07:43 UTC

Easily Import/Export tables

Hello,

Is it possible to export tables using a dedicated cayenne function ?

I would like to export one or more tables (and data), specified by the user
of my application, to import them in another environment (for example from
development to production).

The ideal thing would be a SQL script generated by cayenne, independant of
databases and cayenne (I could use it with Mysql query browser, for
example).

Else, using serialization of cayenne objects ?
Or another possibility ?

The interest is to not manually write SELECT and UPDATE statements.

Thanx.
O.P.

Re: Easily Import/Export tables

Posted by "open.pumpkin" <op...@gmail.com>.
Missing. Sorry.
Here the headers of the stack trace :

Apache Ant version 1.7.1 compiled on November 10 2008
Buildfile: build.xml
Detected Java version: 1.5 in: /usr/lib/jvm/java-1.5.0-sun-1.5.0.18/jre
Detected OS: Linux
parsing buildfile /myPath/build.xml with URI = file:/myPath/build.xml
Project base dir set to: /myPath
[antlib:org.apache.tools.ant] Could not load definitions from resource
org/apache/tools/ant/antlib.xml. It could not be found.
Build sequence for target(s) `exportTask' is [exportTask]
Complete build sequence is [exportTask, ]

exportTask:
[cdataport] log4j:WARN No appenders could be found for logger
(org.apache.cayenne.conf.DefaultConfiguration).
[cdataport] log4j:WARN Please initialize the log4j system properly.
[cdataport] Porting from 'Dev_WSN_STORE_Node' to 'Local_WSN_STORE_Node'.
[cdataport] Number of entities: 4
[cdataport] Deleting WSN_STORE_REG

BUILD FAILED
(...)



2009/9/8 Andrus Adamchik <an...@objectstyle.org>

>
> On Sep 8, 2009, at 3:24 AM, open.pumpkin wrote:
>
>  Line #20 is : srcNode="Dev_WKN_Node" destNode="Local_WKN_Node" />
>> Connections to databases are correct (if I stop one DB, I receive a
>> Connection refused exception). Node names are correct. But I'm not
>> familiar
>> with ant.
>> Can you enlighten me?
>>
>
> Try running ant with -v flag: "ant -v". It should print the stack trace.
>
>
>  Now some general questions :
>> When using the ant method to export DB data, ID's are kept identical in
>> different DB's ? Or, if they change, the foreign keys are automatically
>> mapped with new ID's ? If not, then this method does not interest me.
>>
>
> The ids are preserved.
>
> Andrus
>
>

Re: Easily Import/Export tables

Posted by Andrus Adamchik <an...@objectstyle.org>.
Looks like this bug to me:

https://issues.apache.org/jira/browse/CAY-1170

It is fixed in 3.0M6. Which version of Cayenne do you have?

Andrus


On Sep 8, 2009, at 10:27 AM, open.pumpkin wrote:

> The trace :
> /.../build.xml:20: Error porting data: null
>    at  
> org.apache.cayenne.tools.DataPortTask.execute(DataPortTask.java:108)
>    at  
> org.apache.tools.ant.UnknownElement.execute(UnknownElement.java:288)
>    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
>    at
> sun 
> .reflect 
> .NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
>    at
> sun 
> .reflect 
> .DelegatingMethodAccessorImpl 
> .invoke(DelegatingMethodAccessorImpl.java:25)
>    at java.lang.reflect.Method.invoke(Method.java:585)
>    at
> org 
> .apache.tools.ant.dispatch.DispatchUtils.execute(DispatchUtils.java: 
> 106)
>    at org.apache.tools.ant.Task.perform(Task.java:348)
>    at org.apache.tools.ant.Target.execute(Target.java:357)
>    at org.apache.tools.ant.Target.performTasks(Target.java:385)
>    at org.apache.tools.ant.Project.executeSortedTargets(Project.java: 
> 1337)
>    at org.apache.tools.ant.Project.executeTarget(Project.java:1306)
>    at
> org 
> .apache 
> .tools 
> .ant.helper.DefaultExecutor.executeTargets(DefaultExecutor.java:41)
>    at org.apache.tools.ant.Project.executeTargets(Project.java:1189)
>    at org.apache.tools.ant.Main.runBuild(Main.java:758)
>    at org.apache.tools.ant.Main.startAnt(Main.java:217)
>    at org.apache.tools.ant.launch.Launcher.run(Launcher.java:257)
>    at org.apache.tools.ant.launch.Launcher.main(Launcher.java:104)
> Caused by: java.lang.NullPointerException
>    at org.apache.cayenne.dba.TypesHandler.<init>(TypesHandler.java:74)
>    at  
> org.apache.cayenne.dba.TypesHandler.getHandler(TypesHandler.java:58)
>    at org.apache.cayenne.dba.JdbcAdapter.<init>(JdbcAdapter.java:100)
>    at
> org.apache.cayenne.dba.mysql.MySQLAdapter.<init>(MySQLAdapter.java:75)
>    at
> org 
> .apache 
> .cayenne.dba.mysql.MySQLSniffer.createAdapter(MySQLSniffer.java:71)
>    at
> org 
> .apache 
> .cayenne 
> .dba.DbAdapterFactoryChain.createAdapter(DbAdapterFactoryChain.java: 
> 58)
>    at  
> org.apache.cayenne.dba.AutoAdapter.loadAdapter(AutoAdapter.java:173)
>    at org.apache.cayenne.dba.AutoAdapter.getAdapter(AutoAdapter.java: 
> 155)
>    at
> org.apache.cayenne.dba.AutoAdapter.getExtendedTypes(AutoAdapter.java: 
> 263)
>    at  
> org.apache.cayenne.access.DataNode.performQueries(DataNode.java:243)
>    at org.apache.cayenne.access.DataPort.processDelete(DataPort.java: 
> 151)
>    at org.apache.cayenne.access.DataPort.execute(DataPort.java:110)
>    at  
> org.apache.cayenne.tools.DataPortTask.execute(DataPortTask.java:104)
>    ... 17 more
> --- Nested Exception ---
> java.lang.NullPointerException
>    at org.apache.cayenne.dba.TypesHandler.<init>(TypesHandler.java:74)
>    at  
> org.apache.cayenne.dba.TypesHandler.getHandler(TypesHandler.java:58)
>    at org.apache.cayenne.dba.JdbcAdapter.<init>(JdbcAdapter.java:100)
>    at
> org.apache.cayenne.dba.mysql.MySQLAdapter.<init>(MySQLAdapter.java:75)
>    at
> org 
> .apache 
> .cayenne.dba.mysql.MySQLSniffer.createAdapter(MySQLSniffer.java:71)
>    at
> org 
> .apache 
> .cayenne 
> .dba.DbAdapterFactoryChain.createAdapter(DbAdapterFactoryChain.java: 
> 58)
>    at  
> org.apache.cayenne.dba.AutoAdapter.loadAdapter(AutoAdapter.java:173)
>    at org.apache.cayenne.dba.AutoAdapter.getAdapter(AutoAdapter.java: 
> 155)
>    at
> org.apache.cayenne.dba.AutoAdapter.getExtendedTypes(AutoAdapter.java: 
> 263)
>    at  
> org.apache.cayenne.access.DataNode.performQueries(DataNode.java:243)
>    at org.apache.cayenne.access.DataPort.processDelete(DataPort.java: 
> 151)
>    at org.apache.cayenne.access.DataPort.execute(DataPort.java:110)
>    at  
> org.apache.cayenne.tools.DataPortTask.execute(DataPortTask.java:104)
>    at  
> org.apache.tools.ant.UnknownElement.execute(UnknownElement.java:288)
>    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
>    at
> sun 
> .reflect 
> .NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
>    at
> sun 
> .reflect 
> .DelegatingMethodAccessorImpl 
> .invoke(DelegatingMethodAccessorImpl.java:25)
>    at java.lang.reflect.Method.invoke(Method.java:585)
>    at
> org 
> .apache.tools.ant.dispatch.DispatchUtils.execute(DispatchUtils.java: 
> 106)
>    at org.apache.tools.ant.Task.perform(Task.java:348)
>    at org.apache.tools.ant.Target.execute(Target.java:357)
>    at org.apache.tools.ant.Target.performTasks(Target.java:385)
>    at org.apache.tools.ant.Project.executeSortedTargets(Project.java: 
> 1337)
>    at org.apache.tools.ant.Project.executeTarget(Project.java:1306)
>    at
> org 
> .apache 
> .tools 
> .ant.helper.DefaultExecutor.executeTargets(DefaultExecutor.java:41)
>    at org.apache.tools.ant.Project.executeTargets(Project.java:1189)
>    at org.apache.tools.ant.Main.runBuild(Main.java:758)
>    at org.apache.tools.ant.Main.startAnt(Main.java:217)
>    at org.apache.tools.ant.launch.Launcher.run(Launcher.java:257)
>    at org.apache.tools.ant.launch.Launcher.main(Launcher.java:104)
>
>
> 2009/9/8 Andrus Adamchik <an...@objectstyle.org>
>
>>
>> On Sep 8, 2009, at 3:24 AM, open.pumpkin wrote:
>>
>> Line #20 is : srcNode="Dev_WKN_Node" destNode="Local_WKN_Node" />
>>> Connections to databases are correct (if I stop one DB, I receive a
>>> Connection refused exception). Node names are correct. But I'm not
>>> familiar
>>> with ant.
>>> Can you enlighten me?
>>>
>>
>> Try running ant with -v flag: "ant -v". It should print the stack  
>> trace.
>>
>>
>> Now some general questions :
>>> When using the ant method to export DB data, ID's are kept  
>>> identical in
>>> different DB's ? Or, if they change, the foreign keys are  
>>> automatically
>>> mapped with new ID's ? If not, then this method does not interest  
>>> me.
>>>
>>
>> The ids are preserved.
>>
>> Andrus
>>
>>

Re: Easily Import/Export tables

Posted by "open.pumpkin" <op...@gmail.com>.
The trace :
/.../build.xml:20: Error porting data: null
    at org.apache.cayenne.tools.DataPortTask.execute(DataPortTask.java:108)
    at org.apache.tools.ant.UnknownElement.execute(UnknownElement.java:288)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
    at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
    at java.lang.reflect.Method.invoke(Method.java:585)
    at
org.apache.tools.ant.dispatch.DispatchUtils.execute(DispatchUtils.java:106)
    at org.apache.tools.ant.Task.perform(Task.java:348)
    at org.apache.tools.ant.Target.execute(Target.java:357)
    at org.apache.tools.ant.Target.performTasks(Target.java:385)
    at org.apache.tools.ant.Project.executeSortedTargets(Project.java:1337)
    at org.apache.tools.ant.Project.executeTarget(Project.java:1306)
    at
org.apache.tools.ant.helper.DefaultExecutor.executeTargets(DefaultExecutor.java:41)
    at org.apache.tools.ant.Project.executeTargets(Project.java:1189)
    at org.apache.tools.ant.Main.runBuild(Main.java:758)
    at org.apache.tools.ant.Main.startAnt(Main.java:217)
    at org.apache.tools.ant.launch.Launcher.run(Launcher.java:257)
    at org.apache.tools.ant.launch.Launcher.main(Launcher.java:104)
Caused by: java.lang.NullPointerException
    at org.apache.cayenne.dba.TypesHandler.<init>(TypesHandler.java:74)
    at org.apache.cayenne.dba.TypesHandler.getHandler(TypesHandler.java:58)
    at org.apache.cayenne.dba.JdbcAdapter.<init>(JdbcAdapter.java:100)
    at
org.apache.cayenne.dba.mysql.MySQLAdapter.<init>(MySQLAdapter.java:75)
    at
org.apache.cayenne.dba.mysql.MySQLSniffer.createAdapter(MySQLSniffer.java:71)
    at
org.apache.cayenne.dba.DbAdapterFactoryChain.createAdapter(DbAdapterFactoryChain.java:58)
    at org.apache.cayenne.dba.AutoAdapter.loadAdapter(AutoAdapter.java:173)
    at org.apache.cayenne.dba.AutoAdapter.getAdapter(AutoAdapter.java:155)
    at
org.apache.cayenne.dba.AutoAdapter.getExtendedTypes(AutoAdapter.java:263)
    at org.apache.cayenne.access.DataNode.performQueries(DataNode.java:243)
    at org.apache.cayenne.access.DataPort.processDelete(DataPort.java:151)
    at org.apache.cayenne.access.DataPort.execute(DataPort.java:110)
    at org.apache.cayenne.tools.DataPortTask.execute(DataPortTask.java:104)
    ... 17 more
--- Nested Exception ---
java.lang.NullPointerException
    at org.apache.cayenne.dba.TypesHandler.<init>(TypesHandler.java:74)
    at org.apache.cayenne.dba.TypesHandler.getHandler(TypesHandler.java:58)
    at org.apache.cayenne.dba.JdbcAdapter.<init>(JdbcAdapter.java:100)
    at
org.apache.cayenne.dba.mysql.MySQLAdapter.<init>(MySQLAdapter.java:75)
    at
org.apache.cayenne.dba.mysql.MySQLSniffer.createAdapter(MySQLSniffer.java:71)
    at
org.apache.cayenne.dba.DbAdapterFactoryChain.createAdapter(DbAdapterFactoryChain.java:58)
    at org.apache.cayenne.dba.AutoAdapter.loadAdapter(AutoAdapter.java:173)
    at org.apache.cayenne.dba.AutoAdapter.getAdapter(AutoAdapter.java:155)
    at
org.apache.cayenne.dba.AutoAdapter.getExtendedTypes(AutoAdapter.java:263)
    at org.apache.cayenne.access.DataNode.performQueries(DataNode.java:243)
    at org.apache.cayenne.access.DataPort.processDelete(DataPort.java:151)
    at org.apache.cayenne.access.DataPort.execute(DataPort.java:110)
    at org.apache.cayenne.tools.DataPortTask.execute(DataPortTask.java:104)
    at org.apache.tools.ant.UnknownElement.execute(UnknownElement.java:288)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
    at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
    at java.lang.reflect.Method.invoke(Method.java:585)
    at
org.apache.tools.ant.dispatch.DispatchUtils.execute(DispatchUtils.java:106)
    at org.apache.tools.ant.Task.perform(Task.java:348)
    at org.apache.tools.ant.Target.execute(Target.java:357)
    at org.apache.tools.ant.Target.performTasks(Target.java:385)
    at org.apache.tools.ant.Project.executeSortedTargets(Project.java:1337)
    at org.apache.tools.ant.Project.executeTarget(Project.java:1306)
    at
org.apache.tools.ant.helper.DefaultExecutor.executeTargets(DefaultExecutor.java:41)
    at org.apache.tools.ant.Project.executeTargets(Project.java:1189)
    at org.apache.tools.ant.Main.runBuild(Main.java:758)
    at org.apache.tools.ant.Main.startAnt(Main.java:217)
    at org.apache.tools.ant.launch.Launcher.run(Launcher.java:257)
    at org.apache.tools.ant.launch.Launcher.main(Launcher.java:104)


2009/9/8 Andrus Adamchik <an...@objectstyle.org>

>
> On Sep 8, 2009, at 3:24 AM, open.pumpkin wrote:
>
>  Line #20 is : srcNode="Dev_WKN_Node" destNode="Local_WKN_Node" />
>> Connections to databases are correct (if I stop one DB, I receive a
>> Connection refused exception). Node names are correct. But I'm not
>> familiar
>> with ant.
>> Can you enlighten me?
>>
>
> Try running ant with -v flag: "ant -v". It should print the stack trace.
>
>
>  Now some general questions :
>> When using the ant method to export DB data, ID's are kept identical in
>> different DB's ? Or, if they change, the foreign keys are automatically
>> mapped with new ID's ? If not, then this method does not interest me.
>>
>
> The ids are preserved.
>
> Andrus
>
>

Re: Easily Import/Export tables

Posted by Andrus Adamchik <an...@objectstyle.org>.
On Sep 8, 2009, at 3:24 AM, open.pumpkin wrote:

> Line #20 is : srcNode="Dev_WKN_Node" destNode="Local_WKN_Node" />
> Connections to databases are correct (if I stop one DB, I receive a
> Connection refused exception). Node names are correct. But I'm not  
> familiar
> with ant.
> Can you enlighten me?

Try running ant with -v flag: "ant -v". It should print the stack trace.


> Now some general questions :
> When using the ant method to export DB data, ID's are kept identical  
> in
> different DB's ? Or, if they change, the foreign keys are  
> automatically
> mapped with new ID's ? If not, then this method does not interest me.

The ids are preserved.

Andrus

Re: Easily Import/Export tables

Posted by "open.pumpkin" <op...@gmail.com>.
Thanks for reply.

I'm considering the ant method but I have a problem and some questions.

Here the error I receive during execution of my ant script :
Buildfile: build.xml

exportTask:
[cdataport] log4j:WARN No appenders could be found for logger
(org.apache.cayenne.conf.DefaultConfiguration).
[cdataport] log4j:WARN Please initialize the log4j system properly.
[cdataport] Porting from 'Dev_WKN_Node' to 'Local_WKN_Node'.

BUILD FAILED
/path/toMy/build.xml:20: Error porting data: null

Here my build.xml :
<project name="name of project" default="exportTask" basedir=".">

    <path id="classpath">
        <fileset dir="/home/myUser">
            <include name="cayenne-tools-3.0.jar" />
            <include name="cayenne-server-3.0.jar" />
            <include name="vpp-2.2.1.jar" />
        </fileset>
        <fileset
dir="/home/myUser/servers/jboss-portal-2.7.2/server/default/lib">
            <include name="**/*.jar" />
        </fileset>
    </path>

    <taskdef name="cdataport"
classname="org.apache.cayenne.tools.DataPortTask">
        <classpath refid="classpath" />
    </taskdef>

    <target name="exportTask">
        <cdataport
projectFile="/home/myUser/workspace/gestion-portlet/src/main/webapp/WEB-INF/cayenne/cayenne.xml"
            srcNode="Dev_WKN_Node" destNode="Local_WKN_Node" />
    </target>

</project>

Line #20 is : srcNode="Dev_WKN_Node" destNode="Local_WKN_Node" />
Connections to databases are correct (if I stop one DB, I receive a
Connection refused exception). Node names are correct. But I'm not familiar
with ant.
Can you enlighten me?

Now some general questions :
When using the ant method to export DB data, ID's are kept identical in
different DB's ? Or, if they change, the foreign keys are automatically
mapped with new ID's ? If not, then this method does not interest me.

Thanks.
O.P.

2009/9/7 Andrus Adamchik <an...@objectstyle.org>

> Hi there,
>
> Cayenne may not have built in data migration tools that would fit every
> scenario, although there's been some attempts to write them [1]. At the same
> time Cayenne is a good *platform* for writing such tools based on either
> objects (de)serialization, or direct migration [1], and less so for
> SQL-based migrations (although that's also possible).
>
> Note on the XML approach... Current XML (de)serialization API [2] is too
> raw for my liking, but coincidentally last week I started writing a tool
> based on XStream [3] to serialize designated object subgraphs (subsets of a
> large DB defined based on some rules), with the goal of loading that data to
> another DB or back to the same DB (i.e. cloning). The code turned out to be
> pretty simple and powerful. It can handle databases of practically any size,
> and define subgraph scope in terms of mapped relationships. Not yet sure if
> that becomes open source though.
>
> Finally if your export/import rules are something simple (e.g. "transfer
> the entire DB"), I'd strongly suggest to investigate dump/load tools
> provided by your database. All of the major DB's have them. This may save
> you from reinventing the wheel.
>
> Cheers,
> Andrus
>
> [1] http://cayenne.apache.org/doc/cdataport.html
> [2]
> http://cayenne.apache.org/doc/api/org/apache/cayenne/xml/package-summary.html
> [3] http://xstream.codehaus.org/
>
>
> On Sep 7, 2009, at 5:07 AM, open.pumpkin wrote:
>
>  Hello,
>>
>> Is it possible to export tables using a dedicated cayenne function ?
>>
>> I would like to export one or more tables (and data), specified by the
>> user
>> of my application, to import them in another environment (for example from
>> development to production).
>>
>> The ideal thing would be a SQL script generated by cayenne, independant of
>> databases and cayenne (I could use it with Mysql query browser, for
>> example).
>>
>> Else, using serialization of cayenne objects ?
>> Or another possibility ?
>>
>> The interest is to not manually write SELECT and UPDATE statements.
>>
>> Thanx.
>> O.P.
>>
>
>

Re: Easily Import/Export tables

Posted by Andrus Adamchik <an...@objectstyle.org>.
Hi there,

Cayenne may not have built in data migration tools that would fit  
every scenario, although there's been some attempts to write them [1].  
At the same time Cayenne is a good *platform* for writing such tools  
based on either objects (de)serialization, or direct migration [1],  
and less so for SQL-based migrations (although that's also possible).

Note on the XML approach... Current XML (de)serialization API [2] is  
too raw for my liking, but coincidentally last week I started writing  
a tool based on XStream [3] to serialize designated object subgraphs  
(subsets of a large DB defined based on some rules), with the goal of  
loading that data to another DB or back to the same DB (i.e. cloning).  
The code turned out to be pretty simple and powerful. It can handle  
databases of practically any size, and define subgraph scope in terms  
of mapped relationships. Not yet sure if that becomes open source  
though.

Finally if your export/import rules are something simple (e.g.  
"transfer the entire DB"), I'd strongly suggest to investigate dump/ 
load tools provided by your database. All of the major DB's have them.  
This may save you from reinventing the wheel.

Cheers,
Andrus

[1] http://cayenne.apache.org/doc/cdataport.html
[2] http://cayenne.apache.org/doc/api/org/apache/cayenne/xml/package-summary.html
[3] http://xstream.codehaus.org/

On Sep 7, 2009, at 5:07 AM, open.pumpkin wrote:

> Hello,
>
> Is it possible to export tables using a dedicated cayenne function ?
>
> I would like to export one or more tables (and data), specified by  
> the user
> of my application, to import them in another environment (for  
> example from
> development to production).
>
> The ideal thing would be a SQL script generated by cayenne,  
> independant of
> databases and cayenne (I could use it with Mysql query browser, for
> example).
>
> Else, using serialization of cayenne objects ?
> Or another possibility ?
>
> The interest is to not manually write SELECT and UPDATE statements.
>
> Thanx.
> O.P.

Re: Easily Import/Export tables

Posted by "open.pumpkin" <op...@gmail.com>.
> Which version of Cayenne do you have?
I use 2.0.4.
I'll try to test with 3.0.

> it's trivial to write ant tasks to export and import data
I agree.
I'm writing a java class to generate dynamic dumps (sql script) of my
tables.
It's not so longer I thought.

Thanks for help !


2009/9/8 Mike Kienenberger <mk...@gmail.com>

> As an alternative:
>
> I find it far easier to use DBUnit to export and import data in a
> database-independent format.   If you use Eclipse, you can install the
> QuantumDB plugin and export/import directly from eclipse (for small
> tables).
>
> Otherwise, it's trivial to write ant tasks to export and import data.
>
>    <target name="export" description="Copies all global table data to
> XML files" depends="">
>                <dbunit
>                        driver="oracle.jdbc.driver.OracleDriver"
>                        classpath="${Oracle.lib}"
>                        url="${jdbc.url}"
>                        schema="schema"
>                        userid="user"
>                        password="password">
>
>                <export dest="./data/dbunit/schema/TABLE1.xml"
> format="flat">
>                        <query name="SCHEMA.TABLE1" sql="SELECT * FROM
> SCHEMA.TABLE1
> ORDER BY ID"/>
>                </export>
>                <export dest="./data/dbunit/schema/TABLE2.xml"
> format="flat">
>                        <query name="SCHEMA.TABLE2" sql="SELECT * FROM
> SCHEMA.TABLE2
> ORDER BY ID"/>
>                </export>
>                </dbunit>
>    </target>
>
>
>    <target name="import" description="Recreates global tables in
> Oracle from XML files" depends="">
>                <dbunit
>                        driver="oracle.jdbc.driver.OracleDriver"
>                        classpath="${Oracle.lib}"
>                        url="${jdbc.url}"
>                        schema="schema"
>                        userid="user"
>                        password="password">
>            <operation type="CLEAN_INSERT"
> src="./data/dbunit/schema/TABLE1.xml" "/>
>            <operation type="CLEAN_INSERT"
> src="./data/dbunit/schema/TABLE2.xml" "/>
>                </dbunit>
>    </target>
>
> On Mon, Sep 7, 2009 at 5:07 AM, open.pumpkin<op...@gmail.com>
> wrote:
> > Hello,
> >
> > Is it possible to export tables using a dedicated cayenne function ?
> >
> > I would like to export one or more tables (and data), specified by the
> user
> > of my application, to import them in another environment (for example
> from
> > development to production).
> >
> > The ideal thing would be a SQL script generated by cayenne, independant
> of
> > databases and cayenne (I could use it with Mysql query browser, for
> > example).
> >
> > Else, using serialization of cayenne objects ?
> > Or another possibility ?
> >
> > The interest is to not manually write SELECT and UPDATE statements.
> >
> > Thanx.
> > O.P.
> >
>

Re: Easily Import/Export tables

Posted by Mike Kienenberger <mk...@gmail.com>.
As an alternative:

I find it far easier to use DBUnit to export and import data in a
database-independent format.   If you use Eclipse, you can install the
QuantumDB plugin and export/import directly from eclipse (for small
tables).

Otherwise, it's trivial to write ant tasks to export and import data.

    <target name="export" description="Copies all global table data to
XML files" depends="">
		<dbunit
			driver="oracle.jdbc.driver.OracleDriver"
			classpath="${Oracle.lib}"
			url="${jdbc.url}"
			schema="schema"
			userid="user"
			password="password">

    		<export dest="./data/dbunit/schema/TABLE1.xml" format="flat">
    			<query name="SCHEMA.TABLE1" sql="SELECT * FROM SCHEMA.TABLE1
ORDER BY ID"/>
    		</export>						
    		<export dest="./data/dbunit/schema/TABLE2.xml" format="flat">
    			<query name="SCHEMA.TABLE2" sql="SELECT * FROM SCHEMA.TABLE2
ORDER BY ID"/>
    		</export>
		</dbunit>
    </target>


    <target name="import" description="Recreates global tables in
Oracle from XML files" depends="">
		<dbunit
			driver="oracle.jdbc.driver.OracleDriver"
			classpath="${Oracle.lib}"
			url="${jdbc.url}"
			schema="schema"
			userid="user"
			password="password">
            <operation type="CLEAN_INSERT"
src="./data/dbunit/schema/TABLE1.xml" "/>
            <operation type="CLEAN_INSERT"
src="./data/dbunit/schema/TABLE2.xml" "/>
		</dbunit>
    </target>

On Mon, Sep 7, 2009 at 5:07 AM, open.pumpkin<op...@gmail.com> wrote:
> Hello,
>
> Is it possible to export tables using a dedicated cayenne function ?
>
> I would like to export one or more tables (and data), specified by the user
> of my application, to import them in another environment (for example from
> development to production).
>
> The ideal thing would be a SQL script generated by cayenne, independant of
> databases and cayenne (I could use it with Mysql query browser, for
> example).
>
> Else, using serialization of cayenne objects ?
> Or another possibility ?
>
> The interest is to not manually write SELECT and UPDATE statements.
>
> Thanx.
> O.P.
>