More responses inline...
On 11/15/18 8:09 AM, Alex O'Ree wrote:
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
Right. Derby uses the SQL Standard syntax. PostgreSQL uses the popular
limit/offset syntax which never made it into the Standard. The JDBC
limit/offset escape syntax is supposed to paper over this divergence of
PostgreSQL and MySQL from the Standard. But it sounds as though you had
some difficulty getting Derby's JDBC escape syntax to work for you.
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?
Right. PostgreSQL and MySQL implemented their own, idiosyncratic UPSERT
syntax. Derby uses the Standard MERGE syntax for this problem.
MERGE is a complicated statement and Derby's implementation is not
complete. See
http://db.apache.org/derby/docs/10.14/ref/rrefsqljmerge.html.
Unfortunately, Derby does not support VALUES clauses in the USING clause
of the MERGE statement. The following, admittedly awkward workarounds
may help you:
o Use a temporary table as the driving relation of the MERGE statement.
o Use a table function as the driving relation of the MERGE statement.
See http://db.apache.org/derby/docs/10.14/devguide/cdevspecialtabfuncs.html.
Here is a script which shows these workarounds in action:
connect 'jdbc:derby:memory:db;create=true';
-- using a temp table
create table t1(a varchar(10), b varchar(10));
declare global temporary table session.s1 (a varchar(10), b varchar(10)) not
logged;
autocommit off;
insert into session.s1 values ('abc', 'def');
merge into t1 t
using session.s1 s
on t.a = s.a
when not matched then insert (a, b) values (s.a, s.b)
;
commit;
select * from t1;
-- using a table function
create function twoStringPassthrough(a varchar(32672), b varchar (32672))
returns table (a varchar(32672), b varchar (32672))
language java
parameter style derby_jdbc_result_set
no sql
external name 'TwoStringArgPassthrough.passthrough';
merge into t1 t
using table(twoStringPassthrough('ghi', 'jkl')) s
on t.a = s.a
when not matched then insert (a, b) values (s.a, s.b)
;
commit;
select * from t1;
Here is the source code for the table function:
import java.sql.SQLException;
import org.apache.derby.vti.StringColumnVTI;
public class TwoStringArgPassthrough extends StringColumnVTI
{
private static final String[] COLUMN_NAMES = new String[] {"A", "B"};
private final String _A;
private final String _B;
private boolean _hasMoreRows;
private TwoStringArgPassthrough(String A, String B)
{
super(COLUMN_NAMES);
_A = A;
_B = B;
_hasMoreRows = true;
}
/** Entry point bound to the table function */
public static TwoStringArgPassthrough passthrough(String A, String B)
{
return new TwoStringArgPassthrough(A, B);
}
/** ResultSet overloads */
public boolean next()
{
try
{
return _hasMoreRows;
}
finally
{
_hasMoreRows = false;
}
}
public void close() { _hasMoreRows = false; }
/** StringColumnVTI implementation */
protected String getRawColumn(int columnNumber)
throws SQLException
{
switch (columnNumber)
{
case 1: return _A;
case 2: return _B;
default: throw new SQLException("Unsupported column number: " +
columnNumber);
}
}
}
Hope this helps,
-Rick
On Wed, Nov 14, 2018 at 7:59 PM Rick Hillegas <[email protected]
<mailto:[email protected]>> 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
