JDBC driver choking on semicolons in quoted strings
---------------------------------------------------
Key: JRUBY-5588
URL: http://jira.codehaus.org/browse/JRUBY-5588
Project: JRuby
Issue Type: Bug
Affects Versions: JRuby 1.6RC3
Reporter: James Lavin
Assignee: Thomas E Enebo
The following migration worked with Postgres previously but now generates an
error:
def self.up
execute <<-SQL
CREATE OR REPLACE FUNCTION pc_chartoint(chartoconvert character varying)
RETURNS integer AS
$BODY$
SELECT CASE WHEN trim($1) SIMILAR TO '[0-9]+'
THEN CAST(trim($1) AS integer)
ELSE NULL END;
$BODY$
LANGUAGE 'sql' IMMUTABLE STRICT;
SQL
end
ActiveRecord::JDBCError: ERROR: unterminated dollar-quoted string at or near
"$_$
SELECT CASE WHEN trim($1) SIMILAR TO '[0-9]+'
THEN CAST(trim($1) AS integer)
ELSE NULL END"
Position: 210: --
-- Name: pc_chartoint(character varying); Type: FUNCTION; Schema: public;
Owner: -
I've tried putting everything on a single line and using single- and
double-quotation marks instead of $BODY$. Always the same error.
I believe the JDBC driver is choking on the semicolon at the end of "ELSE NULL
END;"
Here are some other threads that seem to have encountered the same problem.
Many users report that their code runs fine when sent to Postgres via psql
(etc.) but not via a JDBC driver:
http://jira.codehaus.org/browse/MSQL-36
http://stackoverflow.com/questions/3499483/error-unterminated-quoted-string-at-or-near
http://permalink.gmane.org/gmane.comp.db.postgresql.jdbc/16657
http://www.mail-archive.com/[email protected]/msg15898.html
Thanks in advance for your time looking into this!
--James
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://jira.codehaus.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
---------------------------------------------------------------------
To unsubscribe from this list, please visit:
http://xircles.codehaus.org/manage_email
