With the Play Framework 2.1
I have the following SQL defined in my evolution:
CREATE OR REPLACE FUNCTION idx(myArray anyarray, myElement anyelement) RETURNS int AS $$
SELECT i FROM (
SELECT generate_series(array_lower(myArray,1),array_upper(myArray,1))
) g(i)
WHERE myArray[i] = anyElement
LIMIT 1; $$ LANGUAGE sql IMMUTABLE;
When I execute the evolution, I get the following error:
We got the following error: ERROR: unterminated dollar-quoted string at or near
"$$ SELECT i FROM ( SELECT generate_series(array_lower(myArray,1),
array_upper(myArray,1)) ) g(i) WHERE myArray[i] = anyElement LIMIT 1" Position:
87 [ERROR:0, SQLSTATE:42601], while trying to run this SQL script:
I'm using the PostgreSQL driver version 9.1-901.jdbc4.
I looked at the postgres query logs and found out that Play is trying to do the following:
LOG: execute <unnamed>: insert into play_evolutions values($1, $2, $3, $4, $5, $6, $7)
PST DETAIL: parameters: $1 = '1',
$2 = 'c834d463ebd9916b0a3388040300a0926514faef',
$3 = '2013-03-05 00:00:00',
$4 = '-- THE EVOLUTION UP STATEMENTS GO HERE',
$5 = '-- THE EVOLUTION DOWN STATEMENTS GO HERE',
$6 = 'applying_up',
$7 = ''
So, for some reason Play is trying to insert SQL into a text column without properly escaping. Has anyone else found a work around for this? Do you think this is a JDBC problem and not a Play problem? Also, has anyone got Liquibase working with Play 2.1?
Also, just changing the $$ to ' does not work either. In that case we get a different error, but we still can't execute the evolution.
Edit: I've added added an example from a brand new play project. Please download at: http://elijah.zupancic.name/files/play_evolution_problem.tar.gz
To get the example working you will need to create a new database as shown in the first comment on the evolution 1.sql. Then you will need to configure your conf/application.conf to connect to postgres on the correct port and with the correct user.
I've just did an experiment where I try to insert the create function sql totally outside of the play framework. The example is here: http://elijah.zupancic.name/files/PgCreateFunction.tar.gz<
It turns out that it is very reproducible.
EDIT: It turns out that I can't reproduce it in Java.