[EMPIREDB-153] DBDatabaseDriverPostgreSQL.createReverseFunction throws syntax error exception

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Affects Version/s: empire-db-2.3.0, empire-db-2.4.1
• Fix Version/s: empire-db-2.4.1
• Component/s: Core
• Environment: os: ubuntu 10.04 desktop; jdk: oracle jdk 1.6.0_26; postgresql version: 1.9.4; jdbc-driver version: 9.1-901.jdbc4
• Assignee: Francis De Brabandere
• Reporter: Daniel Qian
• Created: 05/Aug/12 02:41
• Updated: 08/Aug/12 09:19
• Resolved: 08/Aug/12 09:19

Description

After calling the DBDatabaseDriverPostgreSQL.createReverseFunction method I got this error:

ERROR: Unable to create reverse function! at org.apache.empire.db.postgresql.DBDatabaseDriverPostgreSQL.(DBDatabaseDriverPostgreSQL.java:275) on 2012-08-05 10:07:04,026
org.postgresql.util.PSQLException: ERROR: syntax error at or near "\"

I check the source code and found that the driver execute the script contained in CREATE_REVERSE_FUNCTION and the script is just wrong.

The script in the source code is:

CREATE OR REPLACE FUNCTION reverse(TEXT) RETURNS TEXT AS '
DECLARE
original ALIAS FOR $1;
reversed TEXT := \'\';
onechar VARCHAR;
mypos INTEGER;
BEGIN
SELECT LENGTH(original) INTO mypos;
LOOP
EXIT WHEN mypos < 1;
SELECT substring(original FROM mypos FOR 1) INTO onechar;
reversed := reversed || onechar;
mypos := mypos -1;
END LOOP;
RETURN reversed;
END
' LANGUAGE plpgsql IMMUTABLE RETURNS NULL ON NULL INPUT

According to the PostgreSQL's documentation the script should be:
(
Documentation url:
http://www.postgresql.org/docs/9.1/static/plpgsql-structure.html
http://www.postgresql.org/docs/8.4/static/plpgsql-structure.html
)

CREATE OR REPLACE FUNCTION reverse(TEXT) RETURNS TEXT AS $$
DECLARE
original ALIAS FOR $1;
reversed TEXT := '';
onechar VARCHAR;
mypos INTEGER;
BEGIN
SELECT LENGTH(original) INTO mypos;
LOOP
EXIT WHEN mypos < 1;
SELECT substring(original FROM mypos FOR 1) INTO onechar;
reversed := reversed || onechar;
mypos := mypos -1;
END LOOP;
RETURN reversed;
END
$$ LANGUAGE plpgsql IMMUTABLE RETURNS NULL ON NULL INPUT;

The differences between the scipts is that the second one enclose the function body by ( $$ ) not single quote ( ' )