Hi Alex,
Thanks for compiling this list of issues. Some comments inline...
On 11/14/18 1:22 PM, Alex O'Ree wrote:
Greetings. I'm looking for some kind of migration guide and for things
to watch out for when migration an application to derby.
Since i haven't found one yet, i decide to write down and share some
of my notes on the things I've ran into so far:
DDL - From postgres, there's lots of differences.
- Postgres 'text' becomes 'long varchar'
Sounds like LONG VARCHAR wasn't long enough for you and you needed CLOB
instead.
- Can't insert from 'text literal' into a blob without some quick code
and a function to convert it
BLOB sounds like an odd analog for TEXT. Do you mean CLOB?
- Postgres gives you the option to select the index type, derby does
not appear to. have this function. Not really sure what kind of index
it is either. btree?
All Derby indexes are btrees. They can be unique or non-unique.
JDBC clients
- limit and offset has a bit of a strange syntax. most rdbs will
access just the literal limit 10 offset 1 syntax. Derby appears to
need to wrap this in { }, so select * from table { limit 10 offset 10}
Derby supports the SQL Standard OFFSET and FETCH clauses. See
http://db.apache.org/derby/docs/10.14/ref/rrefsqljoffsetfetch.html
- from a JDBC client, don't include semicolons in your sql code.
Again, Derby supports SQL Standard syntax. The semicolons are not part
of the Standard grammar, although they are used by command line
interpreters (like Derby own ij CLI) to mark the end of statements. I
agree that rototilling your code to remove non-Standard semicolons
sounds like a drag.
For the last two, is this "normal"? I have a large code base and
refactoring it would be painful. I'm thinking it may be easier to hack
up the jdbc driver to "fix" the sql statements on the fly. Any
thoughts on this? maybe there is some kind of configuration setting to
make this easier?
The place to hack this would be in the parsing layer, below the embedded
JDBC layer. You might also want to take a look at the code for the ij
tool, which has to deal with semicolons.
Hope this helps,
-Rick
