I have been playing around with Derby for quite sometime for an application that contains an embedded web server and an embedded database! Yes, I am trying to create an app that has both components embedded.
I initially tried using Derby as a pure embedded mode but didn’t work as my application required concurrent access. So, I am running it in Networked server mode that starts within the embedded web server. So far no issues and it seems to perform quite well.
Couple of issues I ran into with respect to the SQL support in Derby are
1. Couldn’t delete a column from a table.
2. So, thought of copying everything into a temp table and creat a brand new table. However, the “create table xyz as select …” syntax doesn’t work.
3. It’s not possible to have multiple DISTINCT aggregate functions. Like, select count(distinct a),sum(distinct b) from abc”. However, in my specific case, I actually needed just one single distinct aggregate but used multiple times. Ideally, this optimization should have been built-in. But, I worked around this issue with an additional sub-select. Atleast, glad I can have multi-level nested sub-selects.
4. I wanted to convert a timestamp field and get the corresponding milliseconds out of it and so far I don’t see a way to do it in SQL. I probably select the timestamp and use java code to get the milliseconds, not sure.