Thread: JDBC escaped scalar functions fail when used in function definition
While testing postgresql-9.4.1208.jar with our system, code is now failing when it attempts to use {fn now()} in the body of a PostgreSQL CREATE FUNCTION statement. This worked fine on 1207 and all previous versions of the driver.
In other words, the code below succeeds on 1207 but fails on 1208 (passing in server, port, database, username, and password as parameters):
In other words, the code below succeeds on 1207 but fails on 1208 (passing in server, port, database, username, and password as parameters):
Bug? Intentional change in behavior? Thanks, Adamimport java.sql.*; public class PGTest { public static void main(String[] args) throws SQLException { try (Connection conn = DriverManager.getConnection("jdbc:postgresql://" + args[0] + ":" + args[1] + "/" + args[2], args[3], args[4])) { try (PreparedStatement stmt = conn.prepareStatement( "CREATE FUNCTION public.fn_now() RETURNS VOID AS $$\n" + " BEGIN\n" + " SELECT {fn now()};" + " END;\n" + "$$ LANGUAGE plpgsql;" + "DROP FUNCTION public.fn_now();")) { stmt.execute(); } } } }
Re: JDBC escaped scalar functions fail when used in function definition
From
"David G. Johnston"
Date:
While testing postgresql-9.4.1208.jar with our system, code is now failing when it attempts to use {fn now()} in the body of a PostgreSQL CREATE FUNCTION statement. This worked fine on 1207 and all previous versions of the driver.
In other words, the code below succeeds on 1207 but fails on 1208 (passing in server, port, database, username, and password as parameters):Bug? Intentional change in behavior? Thanks, Adamimport java.sql.*; public class PGTest { public static void main(String[] args) throws SQLException { try (Connection conn = DriverManager.getConnection("jdbc:postgresql://" + args[0] + ":" + args[1] + "/" + args[2], args[3], args[4])) { try (PreparedStatement stmt = conn.prepareStatement( "CREATE FUNCTION public.fn_now() RETURNS VOID AS $$\n" + " BEGIN\n" + " SELECT {fn now()};" + " END;\n" + "$$ LANGUAGE plpgsql;" + "DROP FUNCTION public.fn_now();")) { stmt.execute(); } } } }
Intentional.
Not translating values embedded in string literals, dollar-quoted or otherwise, seems like it is the correct way to operate - though finding an authoritative source on the topic wasn't quick (so I gave up...).
David J.
Re: JDBC escaped scalar functions fail when used in function definition
From
"David G. Johnston"
Date:
While testing postgresql-9.4.1208.jar with our system, code is now failing when it attempts to use {fn now()} in the body of a PostgreSQL CREATE FUNCTION statement. This worked fine on 1207 and all previous versions of the driver.
In other words, the code below succeeds on 1207 but fails on 1208 (passing in server, port, database, username, and password as parameters):Bug? Intentional change in behavior? Thanks, Adamimport java.sql.*; public class PGTest { public static void main(String[] args) throws SQLException { try (Connection conn = DriverManager.getConnection("jdbc:postgresql://" + args[0] + ":" + args[1] + "/" + args[2], args[3], args[4])) { try (PreparedStatement stmt = conn.prepareStatement( "CREATE FUNCTION public.fn_now() RETURNS VOID AS $$\n" + " BEGIN\n" + " SELECT {fn now()};" + " END;\n" + "$$ LANGUAGE plpgsql;" + "DROP FUNCTION public.fn_now();")) { stmt.execute(); } } } }
Intentional.
Not translating values embedded in string literals, dollar-quoted or otherwise, seems like it is the correct way to operate - though finding an authoritative source on the topic wasn't quick (so I gave up...).
David J.