Обсуждение: JDBC Connection must be restarted after executing SQL statement that calls a PL/PGSQL function that won't compile
JDBC Connection must be restarted after executing SQL statement that calls a PL/PGSQL function that won't compile
От
pgsql-bugs@postgresql.org
Дата:
Jason Southern (southern@heymax.com) reports a bug with a severity of 2 The lower the number the more severe it is. Short Description JDBC Connection must be restarted after executing SQL statement that calls a PL/PGSQL function that won't compile Long Description ============================================================================ POSTGRESQL BUG REPORT TEMPLATE ============================================================================ Your name : Jason Southern Your email address : southern@heymax.com System Configuration ---------------------- Architecture (example: Intel Pentium) : 600MHz Intel Pentium III, 256MB RAM Operating System (example: Linux 2.0.26 ELF) : Linux 2.2.16 RedHat 6.2 PostgreSQL version (example: PostgreSQL-6.3) : PostgreSQL-7.0.2 Compiler used (example: gcc 2.7.2) : gcc 2.96 JDBC Driver Version: 7.0.x (jdbc7.0-1.2.jar) JVM: Sun JVM 1.3 Please enter a FULL description of your problem: ------------------------------------------------- JDBC Connection object fails to execute SQL after executing a statement that calls a PL/PGSQL function that won't compile. Please describe a way to repeat the problem. Please try to provide a concise reproducible example, if at all possible: ----------------------------------------------------------------------- 1. Create a PL/PGSQL function that has a syntax error that prevents it from being compiled: DROP FUNCTION BROKEN(VARCHAR); CREATE FUNCTION BROKEN(VARCHAR) RETURNS NUMERIC AS 'DECLARE v_string VARCHAR; v_first_char CHAR(1); v_ascii_val NUMERIC; BEGIN v_ascii_val >= 65 AND v_ascii_val <= 90 THEN /* String begins with an uppercase letter A-Z */ RETURN 1; ELSE IF v_ascii_val >= 97 AND v_ascii_val <= 122 THEN /* String begins with an lowercase letter a-z */ RETURN 1;;; ELSE RETURN 0; END IF; END IF; END;' LANGUAGE 'plpgsql'; 2. Verify function won't compile by executing the following statement in psql: SELECT broken('test') AS word The following message appears: NOTICE: plpgsql: ERROR during compile of broken near line 6 ERROR: parse error at or near ">" 3. Execute the same statement using Statement.executeQuery() SELECT broken('test') AS word The following exception is thrown when this SQL statement is executed: Exception in thread "main" java.sql.SQLException: ERROR: parse error at or near ">" at org.postgresql.Connection.ExecSQL(Connection.java:393) at org.postgresql.jdbc2.Statement.execute(Statement.java:273) at org.postgresql.jdbc2.Statement.executeQuery(Statement.java:54) at TestPostgres.main(TestPostgres.java:303) <-- the name of my test class If this SQLException is caught, any attempt to invoke a properly formed SQL statement using Statement.executeQuery() results in the following exception being thrown: Exception in thread "main" No results were returned by the query. at org.postgresql.jdbc2.Statement.executeQuery(Statement.java:58) at TestPostgres.main(TestPostgres.java:311) <-- the name of my test class I expected a ResultSet object being returned, even if the statement produces no results. If one closes the current connection object, reconnects, and creates a new statement object, Statement.executeQuery() does not throw exception. Sample Code No file was uploaded with this report