Thanks Rick
I also noticed that the wording and ordering of limit and offset for select
statements is way different in derby.
Postgres style: select * from table limit 3 offset 5
Derby: select * from table offset 5 rows fetch next 3 rows only
Next issue I ran into was that I have tons of insert statements that read
like this (postgres style)
insert into table (column1, column2) values ('asd', 'xyz') on conflict do
nothing;
An insert statement like this is used in a batched prepared statement.
Overall goal is to insert everything and when there is a primary key
collision, just ignore it. In postgres, any failure will cause the whole
batch to abort. Is there a derby equivalent to this? I did run across this
merge jira which may solve the problem.
https://issues.apache.org/jira/browse/DERBY-3155 but is that the only
solution?
On Wed, Nov 14, 2018 at 7:59 PM Rick Hillegas <rick.hille...@gmail.com>
wrote:
> 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
>
>
>
