You are viewing a plain text version of this content. The canonical link for it is here.
Posted to ddlutils-user@db.apache.org by "Jeffrey W. Bullard" <je...@nist.gov> on 2010/01/14 21:10:35 UTC

problem importing to derby

I'm trying to import schema and data into derby (ddlToDatabase) from  
xml files created by databasetoDdl task executed on a mysql  
database.  I have successfully done this on simple data files  
already, but with this database the task seems to drop the  
connection.  There are 9 tables in the database.  The schema seem to  
be written okay using the writeSchemaToDatabase subtask.  Then it is  
time to read the data using the writeDataToDatabase subtask.  This  
seems to proceed okay for a while, but soon I get the following output:

[ddlToDatabase] Returning connection  
org.apache.commons.dbcp.PoolableConnection@19e02860 to data source.
[ddlToDatabase] Remaining connections: None

BUILD FAILED
/Users/bullard/DdlUtils-test/build.xml:33:  
org.apache.ddlutils.io.DataSinkException: java.sql.SQLException:  
Connection is closed.

I am including debug output from the ddlToDatabase task and the  
ddlToDatabase task from my build.xml file below.

One other symptom.  I've tried doing this a few times, and each time  
the writeDataToDatabase subtask seems to get to a different place  
before quitting.
Any suggestions are greatly appreciated.

************************************************************************ 
***********
import-target-db:
[ddlToDatabase] Read schema file /Users/bullard/DdlUtils-test/cement- 
db-schema.xml
[ddlToDatabase] About to create database using this URL:  
jdbc:derby:vcctl_cement;create=true
[ddlToDatabase] Database 'vcctl_cement' not created, connection made  
to existing database instead.
[ddlToDatabase] Created database
[ddlToDatabase] Borrowed connection  
org.apache.commons.dbcp.PoolableConnection@19e02860 from data source
[ddlToDatabase] Returning connection  
org.apache.commons.dbcp.PoolableConnection@19e02860 to data source.
[ddlToDatabase] Remaining connections: None
[ddlToDatabase] Borrowed connection  
org.apache.commons.dbcp.PoolableConnection@19e02860 from data source
[ddlToDatabase] About to execute SQL CREATE TABLE AGGREGATE_
[ddlToDatabase] (
[ddlToDatabase]     NAME VARCHAR(64) DEFAULT '' NOT NULL,
[ddlToDatabase]     GIF LONG VARCHAR FOR BIT DATA,
[ddlToDatabase]     TXT LONG VARCHAR FOR BIT DATA,
[ddlToDatabase]     INF LONG VARCHAR FOR BIT DATA,
[ddlToDatabase]     AGGREGATE_TYPE INTEGER DEFAULT 0 NOT NULL,
[ddlToDatabase]     BULK_MODULUS DOUBLE,
[ddlToDatabase]     SHEAR_MODULUS DOUBLE,
[ddlToDatabase]     SPECIFIC_GRAVITY DOUBLE DEFAULT 2.65 NOT NULL,
[ddlToDatabase]     PRIMARY KEY (NAME)
[ddlToDatabase] )
[ddlToDatabase] After execution, 0 row(s) have been changed
[ddlToDatabase] About to execute SQL INSERT INTO AGGREGATE_  
(NAME,GIF,TXT,INF,AGGREGATE_TYPE,BULK_MODULUS,SHEAR_MODULUS,SPECIFIC_GRA 
VITY) SELECT  
NAME,GIF,TXT,INF,AGGREGATE_TYPE,BULK_MODULUS,SHEAR_MODULUS,SPECIFIC_GRAV 
ITY FROM AGGREGATE
[ddlToDatabase] After execution, 27 row(s) have been changed
[ddlToDatabase] About to execute SQL DROP TABLE AGGREGATE
[ddlToDatabase] After execution, 0 row(s) have been changed
[ddlToDatabase] About to execute SQL CREATE TABLE AGGREGATE
[ddlToDatabase] (
[ddlToDatabase]     NAME VARCHAR(64) DEFAULT '' NOT NULL,
[ddlToDatabase]     GIF LONG VARCHAR FOR BIT DATA,
[ddlToDatabase]     TXT LONG VARCHAR FOR BIT DATA,
[ddlToDatabase]     INF LONG VARCHAR FOR BIT DATA,
[ddlToDatabase]     AGGREGATE_TYPE INTEGER DEFAULT 0 NOT NULL,
[ddlToDatabase]     BULK_MODULUS DOUBLE,
[ddlToDatabase]     SHEAR_MODULUS DOUBLE,
[ddlToDatabase]     SPECIFIC_GRAVITY DOUBLE DEFAULT 2.65 NOT NULL,
[ddlToDatabase]     PRIMARY KEY (NAME)
[ddlToDatabase] )
[ddlToDatabase] After execution, 0 row(s) have been changed
[ddlToDatabase] About to execute SQL INSERT INTO AGGREGATE  
(NAME,GIF,TXT,INF,AGGREGATE_TYPE,BULK_MODULUS,SHEAR_MODULUS,SPECIFIC_GRA 
VITY) SELECT  
NAME,GIF,TXT,INF,AGGREGATE_TYPE,BULK_MODULUS,SHEAR_MODULUS,SPECIFIC_GRAV 
ITY FROM AGGREGATE_
[ddlToDatabase] After execution, 27 row(s) have been changed
[ddlToDatabase] About to execute SQL DROP TABLE AGGREGATE_
[ddlToDatabase] After execution, 0 row(s) have been changed
[ddlToDatabase] Executed 25 SQL command(s) with 0 error(s)
[ddlToDatabase] Returning connection  
org.apache.commons.dbcp.PoolableConnection@19e02860 to data source.
[ddlToDatabase] Remaining connections: None
[ddlToDatabase] Written schema to database
[ddlToDatabase] Borrowed connection  
org.apache.commons.dbcp.PoolableConnection@19e02860 from data source
[ddlToDatabase] About to execute SQL: INSERT INTO aggregate (name,  
gif, txt, inf, aggregate_type, bulk_modulus, shear_modulus,  
specific_gravity) VALUES (?, ?, ?, ?, ?, ?, ?, ?)
[ddlToDatabase] Returning connection  
org.apache.commons.dbcp.PoolableConnection@19e02860 to data source.
[ddlToDatabase] Remaining connections: None

BUILD FAILED
/Users/bullard/DdlUtils-test/build.xml:33:  
org.apache.ddlutils.io.DataSinkException: java.sql.SQLException:  
Connection is closed.

Total time: 3 seconds
************************************************************************ 
****************

Here is the target from my build.xml:

    <target name="import-target-db" description="Creates db and loads  
data">
       <taskdef name="ddlToDatabase"
               classname="org.apache.ddlutils.task.DdlToDatabaseTask">
          <classpath refid="classpath"/>
       </taskdef>

       <ddlToDatabase schemaFile="cement-db-schema.xml"  
verbosity="debug">

          <database url="jdbc:derby:vcctl_cement"
                  driverClassName="org.apache.derby.jdbc.EmbeddedDriver"
                  username=""
                  password=""/>

          <createDatabase failonerror="false"/>
          <writeSchemaToDatabase/>
          <writeDataToDatabase datafile="cement-data.xml"/>

       </ddlToDatabase>
    </target>

Re: problem importing to derby

Posted by "Jeffrey W. Bullard" <je...@nist.gov>.
OK, that was another easy question.  My db schema (exported from  
MySQL using DdlUtils) identified LONGVARBINARY data types where they  
had been BLOBS in the MySQL database.  The BLOBS in the MySQL  
database had a max length of 65536, but when I imported into Derby  
those data types were interpreted as "LONG VARCHAR FOR BIT DATA"  
which has a max length of only 32700.  The first time derby  
encountered a field with more than 32700 for that data type, it  
presumably dropped the connection.

The fix:  I went into the schema XML file and changed all  
LONGVARBINARY identifiers to BLOB.  That seems to have worked.  I can  
examine all my tables and the data all appear to be intact.

On Jan 14, 2010, at 3:10 PM, Jeffrey W. Bullard wrote:

> I'm trying to import schema and data into derby (ddlToDatabase) from
> xml files created by databasetoDdl task executed on a mysql
> database.  I have successfully done this on simple data files
> already, but with this database the task seems to drop the
> connection.  There are 9 tables in the database.  The schema seem to
> be written okay using the writeSchemaToDatabase subtask.  Then it is
> time to read the data using the writeDataToDatabase subtask.  This
> seems to proceed okay for a while, but soon I get the following  
> output:
>
> [ddlToDatabase] Returning connection
> org.apache.commons.dbcp.PoolableConnection@19e02860 to data source.
> [ddlToDatabase] Remaining connections: None
>
> BUILD FAILED
> /Users/bullard/DdlUtils-test/build.xml:33:
> org.apache.ddlutils.io.DataSinkException: java.sql.SQLException:
> Connection is closed.
>
> I am including debug output from the ddlToDatabase task and the
> ddlToDatabase task from my build.xml file below.
>
> One other symptom.  I've tried doing this a few times, and each time
> the writeDataToDatabase subtask seems to get to a different place
> before quitting.
> Any suggestions are greatly appreciated.
>
> ********************************************************************** 
> **
> ***********
> import-target-db:
> [ddlToDatabase] Read schema file /Users/bullard/DdlUtils-test/cement-
> db-schema.xml
> [ddlToDatabase] About to create database using this URL:
> jdbc:derby:vcctl_cement;create=true
> [ddlToDatabase] Database 'vcctl_cement' not created, connection made
> to existing database instead.
> [ddlToDatabase] Created database
> [ddlToDatabase] Borrowed connection
> org.apache.commons.dbcp.PoolableConnection@19e02860 from data source
> [ddlToDatabase] Returning connection
> org.apache.commons.dbcp.PoolableConnection@19e02860 to data source.
> [ddlToDatabase] Remaining connections: None
> [ddlToDatabase] Borrowed connection
> org.apache.commons.dbcp.PoolableConnection@19e02860 from data source
> [ddlToDatabase] About to execute SQL CREATE TABLE AGGREGATE_
> [ddlToDatabase] (
> [ddlToDatabase]     NAME VARCHAR(64) DEFAULT '' NOT NULL,
> [ddlToDatabase]     GIF LONG VARCHAR FOR BIT DATA,
> [ddlToDatabase]     TXT LONG VARCHAR FOR BIT DATA,
> [ddlToDatabase]     INF LONG VARCHAR FOR BIT DATA,
> [ddlToDatabase]     AGGREGATE_TYPE INTEGER DEFAULT 0 NOT NULL,
> [ddlToDatabase]     BULK_MODULUS DOUBLE,
> [ddlToDatabase]     SHEAR_MODULUS DOUBLE,
> [ddlToDatabase]     SPECIFIC_GRAVITY DOUBLE DEFAULT 2.65 NOT NULL,
> [ddlToDatabase]     PRIMARY KEY (NAME)
> [ddlToDatabase] )
> [ddlToDatabase] After execution, 0 row(s) have been changed
> [ddlToDatabase] About to execute SQL INSERT INTO AGGREGATE_
> (NAME,GIF,TXT,INF,AGGREGATE_TYPE,BULK_MODULUS,SHEAR_MODULUS,SPECIFIC_G 
> RA
> VITY) SELECT
> NAME,GIF,TXT,INF,AGGREGATE_TYPE,BULK_MODULUS,SHEAR_MODULUS,SPECIFIC_GR 
> AV
> ITY FROM AGGREGATE
> [ddlToDatabase] After execution, 27 row(s) have been changed
> [ddlToDatabase] About to execute SQL DROP TABLE AGGREGATE
> [ddlToDatabase] After execution, 0 row(s) have been changed
> [ddlToDatabase] About to execute SQL CREATE TABLE AGGREGATE
> [ddlToDatabase] (
> [ddlToDatabase]     NAME VARCHAR(64) DEFAULT '' NOT NULL,
> [ddlToDatabase]     GIF LONG VARCHAR FOR BIT DATA,
> [ddlToDatabase]     TXT LONG VARCHAR FOR BIT DATA,
> [ddlToDatabase]     INF LONG VARCHAR FOR BIT DATA,
> [ddlToDatabase]     AGGREGATE_TYPE INTEGER DEFAULT 0 NOT NULL,
> [ddlToDatabase]     BULK_MODULUS DOUBLE,
> [ddlToDatabase]     SHEAR_MODULUS DOUBLE,
> [ddlToDatabase]     SPECIFIC_GRAVITY DOUBLE DEFAULT 2.65 NOT NULL,
> [ddlToDatabase]     PRIMARY KEY (NAME)
> [ddlToDatabase] )
> [ddlToDatabase] After execution, 0 row(s) have been changed
> [ddlToDatabase] About to execute SQL INSERT INTO AGGREGATE
> (NAME,GIF,TXT,INF,AGGREGATE_TYPE,BULK_MODULUS,SHEAR_MODULUS,SPECIFIC_G 
> RA
> VITY) SELECT
> NAME,GIF,TXT,INF,AGGREGATE_TYPE,BULK_MODULUS,SHEAR_MODULUS,SPECIFIC_GR 
> AV
> ITY FROM AGGREGATE_
> [ddlToDatabase] After execution, 27 row(s) have been changed
> [ddlToDatabase] About to execute SQL DROP TABLE AGGREGATE_
> [ddlToDatabase] After execution, 0 row(s) have been changed
> [ddlToDatabase] Executed 25 SQL command(s) with 0 error(s)
> [ddlToDatabase] Returning connection
> org.apache.commons.dbcp.PoolableConnection@19e02860 to data source.
> [ddlToDatabase] Remaining connections: None
> [ddlToDatabase] Written schema to database
> [ddlToDatabase] Borrowed connection
> org.apache.commons.dbcp.PoolableConnection@19e02860 from data source
> [ddlToDatabase] About to execute SQL: INSERT INTO aggregate (name,
> gif, txt, inf, aggregate_type, bulk_modulus, shear_modulus,
> specific_gravity) VALUES (?, ?, ?, ?, ?, ?, ?, ?)
> [ddlToDatabase] Returning connection
> org.apache.commons.dbcp.PoolableConnection@19e02860 to data source.
> [ddlToDatabase] Remaining connections: None
>
> BUILD FAILED
> /Users/bullard/DdlUtils-test/build.xml:33:
> org.apache.ddlutils.io.DataSinkException: java.sql.SQLException:
> Connection is closed.
>
> Total time: 3 seconds
> ********************************************************************** 
> **
> ****************
>
> Here is the target from my build.xml:
>
>     <target name="import-target-db" description="Creates db and loads
> data">
>        <taskdef name="ddlToDatabase"
>                classname="org.apache.ddlutils.task.DdlToDatabaseTask">
>           <classpath refid="classpath"/>
>        </taskdef>
>
>        <ddlToDatabase schemaFile="cement-db-schema.xml"
> verbosity="debug">
>
>           <database url="jdbc:derby:vcctl_cement"
>                    
> driverClassName="org.apache.derby.jdbc.EmbeddedDriver"
>                   username=""
>                   password=""/>
>
>           <createDatabase failonerror="false"/>
>           <writeSchemaToDatabase/>
>           <writeDataToDatabase datafile="cement-data.xml"/>
>
>        </ddlToDatabase>
>     </target>
>
>
>
>