empire-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Daniel Qian (JIRA)" <empire-db-...@incubator.apache.org>
Subject [jira] [Created] (EMPIREDB-153) DBDatabaseDriverPostgreSQL.createReverseFunction throws syntax error exception
Date Sun, 05 Aug 2012 02:42:02 GMT
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

        

Mime
View raw message