[jira] [Assigned] (EMPIREDB-153) DBDatabaseDriverPostgreSQL.createReverseFunction throws syntax error exception

["Francis De Brabandere (JIRA)" <em...@incubator.apache.org>]

     [ https://issues.apache.org/jira/browse/EMPIREDB-153?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Francis De Brabandere reassigned EMPIREDB-153:
----------------------------------------------

    Assignee: Francis De Brabandere
    
> DBDatabaseDriverPostgreSQL.createReverseFunction throws syntax error exception
> ------------------------------------------------------------------------------
>
>                 Key: EMPIREDB-153
>                 URL: https://issues.apache.org/jira/browse/EMPIREDB-153
>             Project: Empire-DB
>          Issue Type: Bug
>          Components: Core
>    Affects Versions: empire-db-2.3.0, empire-db-2.4.1
>         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
>            Reporter: Daniel Qian
>            Assignee: Francis De Brabandere
>
> 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 ( ' )

--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira