Mailing-List: contact commits-help@empire-db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: empire-db-dev@empire-db.apache.org
Date: Sun, 5 Aug 2012 02:42:02 +0000 (UTC)
From: "Daniel Qian (JIRA)" <empire-db-dev@incubator.apache.org>
To: empire-db-commits@incubator.apache.org
Message-ID: <1976050305.14423.1344134522544.JavaMail.jiratomcat@issues-vm>
Subject: [jira] [Created] (EMPIREDB-153)
 DBDatabaseDriverPostgreSQL.createReverseFunction throws syntax error
 exception
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit

Daniel Qian created EMPIREDB-153:
------------------------------------

             Summary: 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


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