Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Discussion" <derby-user@db.apache.org>
Received-SPF: pass (nike.apache.org: local policy)
Mime-Version: 1.0
Message-Id: <p04320401c2e761e71c7a@[130.209.48.140]>
In-Reply-To: <46BC2F0B.2060209@sun.com>
References: <46BC2F0B.2060209@sun.com>
Date: Tue, 14 Aug 2007 15:38:01 +0100
To: derby-user@db.apache.org, Thomas Nielsen <Thomas.Nielsen@Sun.COM>
From: David Leader <d.leader@bio.gla.ac.uk>
Subject: MysSQL to Derby (was Accessing embedded database in JAR file)
Cc: btrichardson@gmail.com
Content-Type: text/plain; charset="us-ascii" ; format="flowed"

I wrote:

>
>>Despite a few problems in porting the stuff from MySQL, I must say 
>>that overall I'm very pleased with the result. My thanks to the 
>>Derby team.

And Thomas replied:

>Thanks for letting the community know :)
>
>If you don't mind - what were your top three pains when moving from 
>MySQL to Derby?

Glad to, if it will be of any interest, although the problems were 
probably a result of our* own inexperience and naivity.

1. We used DdlUtils to do the migration, did the conversion form 
MySQL to xml ok, but then encountered errors on conversion to Derby. 
These threw us initially because they related to primary keys not 
being unique, but eventually we realized that this was because 
MediumInt is only supported in MySQL and the conversion changed it to 
a Small integer. I guess we should have inspected the xml schema more 
carefully. I had consulted the page:

http://db.apache.org/ddlutils/databases/mysql.html

And my one suggestion here is that the conversion table on that page 
should include the recasting of mediumint - presumably an oversight.

2. The other problems arose at runtime after apparent successful 
conversion, and also arose from the non-standard nature of MySQL. 
There were three aspects of the java code of my app that Derby took 
exception to. One was a JDBC ResultSet method (first()) I had used in 
a hack to check for empty tables. It turned out Derby didn't like 
this and made me use some JDBC2 method for scrolling the table. It 
also wouldn't allow a finally{} clause for closing a connection that 
it already handled itself (easy enough to remove) and wouldn't allow 
a faulty SQL query that MySQL had ignored (typo of GROUP instead of 
ORDER - for the correction of which I thank it).

So the moral is perhaps that Derby is to MySQL as Java is to Perl - 
it won't tolerate the sloppy habits you had become accustomed to. :-) 
(Apologies to the Perl community)

3. Finally, my enthusiasm to produce a neatly packaged product made 
me (and my app) waste time with the zipped database. Perhaps there 
could be a health warning about this somewhere.

David

* 'Our' because a summer student was doing this as part of a Masters 
project, but I needed to help him debug it.


-- 
_______________________________________________________________
  David P.Leader, Biochemistry and Molecular Biology
  Davidson Building, University of Glasgow, Glasgow G12 8QQ, UK
  Phone: +44 41 330-5905  http://doolittle.ibls.gla.ac.uk/leader
_______________________________________________________________