db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Thomas J. Taylor" <thomas.tay...@itqa.miami.edu>
Subject RE: Using DdlUtils to Migrate Databases to Derby (MySQL-to-Derby)
Date Mon, 13 Mar 2006 00:39:16 GMT
Example tables are below for your reference.

Thomas

-- DDL #1: Example of MySQL Table with LONGTEXT column that is represented
as Derby CLOB column. 'col3' used to store very long text.

-- MySQL Table DDL
CREATE TABLE TableA (  
      dbid   INTEGER  NOT NULL, 
      id   VARCHAR(255)  NOT NULL, 
      col1   VARCHAR(10),
      col2   VARCHAR(50),
      col3   LONGTEXT   NOT NULL,
      PRIMARY KEY (dbid),
) CHARACTER SET utf8, COLLATE utf8_general_ci;
CREATE INDEX FormLayout_createDate ON FormLayout (createDate);

-- Derby Table DDL
CREATE TABLE TableA (	
			dbid 	BIGINT	NOT NULL, 
			id 	VARCHAR(255)	NOT NULL, 
			col1 	VARCHAR(10),
			col2 	VARCHAR(50),
			col3 	CLOB  	NOT NULL,
			PRIMARY KEY (dbid)) ;


-- DDL #2: Example of MySQL Table with LONGTEXT column that is represented
as Derby VARCHAR(32672) column. 'col1' used in SQL comparison queries.
 
-- MySQL DDL
CREATE TABLE TableB(  
      dbid   INTEGER  NOT NULL    AUTO_INCREMENT,
      id   VARCHAR(255)   NOT NULL,
      col1  LONGTEXT,
      col2   BIT,
      parentDbid  INTEGER   NOT NULL,
      PRIMARY KEY (dbid),
      FOREIGN KEY (parentDbid) REFERENCES TableA(dbid)
) CHARACTER SET utf8, COLLATE utf8_general_ci;

-- Derby DDL
CREATE TABLE TableB(	
			dbid 	BIGINT GENERATED BY DEFAULT AS IDENTITY NOT
NULL,
			id 	VARCHAR(255) 	NOT NULL,
			col1	VARCHAR(32672),
			col2 		VARCHAR(10),
			parentDbid	BIGINT 	NOT NULL,
			PRIMARY KEY (dbid),
			FOREIGN KEY (parentDbid) REFERENCES TableA (dbid)) ;


-----Original Message-----
From: Thomas Dudziak [mailto:tomdzk@gmail.com] 
Sent: Sunday, March 12, 2006 6:34 PM
To: Derby Discussion
Subject: Re: Using DdlUtils to Migrate Databases to Derby (MySQL-to-Derby)

On 3/13/06, Thomas J. Taylor <thomas.taylor@itqa.miami.edu> wrote:

> By the way, it looks like the DDLUtils failed on a MySQL LONG VARCHAR 
> that we represent as a CLOB in Derby. Typically, the column content 
> includes very long XML content.

Could you provide the SQL for the table where DdlUtils failed ? Which JDBC
driver version do you use for MySQL ?

Tom



Mime
View raw message