Skip site navigation (1) Skip section navigation (2)

Bug with PreparedStatements using EXTRACT function

From: Matthias Böhm <fliegenblues(at)gmx(dot)net>
To: <pgsql-jdbc(at)postgresql(dot)org>
Subject: Bug with PreparedStatements using EXTRACT function
Date: 2012-03-16 16:45:44
Message-ID: 000301cd0394$3f325a00$bd970e00$@net (view raw or flat)
Thread:
Lists: pgsql-jdbc
Hi!

I found a bug in the postgresql jdbc driver (I'm using version 9.1 build
901). Constructing a prepared statement that uses the EXTRACT function in
the following way results in an PSQLException when trying to execute the
prepared statement:

***** CODE *****
PreparedStatement stmt2 = conn.prepareStatement(
		"SELECT EXTRACT (YEAR FROM DATE ?)");
		
java.sql.Date date = new java.sql.Date(
		new GregorianCalendar(1990, 5, 7).getTime().getTime());
		
stmt2.setDate(1, date);

// here I get an SQL exception: 
res = stmt2.executeQuery();
***** END CODE *****

The exception thrown is the following (in English the German "Syntaxfehler
bei »$1«" is "syntax error at $1"): 

Exception in thread "main" org.postgresql.util.PSQLException: FEHLER:
Syntaxfehler bei »$1«
  Position: 31
	at
org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorI
mpl.java:2102)
	at
org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.ja
va:1835)
	at
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
	at
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.j
ava:500)
	at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2St
atement.java:388)
	at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statem
ent.java:273)
	at pitas.shipdbs.storage.Bug.main(Bug.java:42)

Executing a simple unprepared statement with an equal, unprepared query
doesn't result in a syntax error:

***** CODE ****
Statement stmt = conn.createStatement();
		
ResultSet res = stmt.executeQuery(
		"SELECT EXTRACT (YEAR FROM DATE '1990-06-07 +02:00:00')");

res.next();

System.out.println("Year: " + res.getInt(1));
***** END CODE ****

A complete example that shows this bug is added at the end of the mail. 

Regards, 
Matthias Boehm


***** CODE *****

package bug;

import java.sql.*;
import java.util.*;

public class Bug {

	public static void main(String[] args) throws SQLException {
		
		// create connection...
		Connection conn = ...
		
		// ---- This does work ----
		Statement stmt = conn.createStatement();
		
		ResultSet res = stmt.executeQuery(
				"SELECT EXTRACT (YEAR FROM DATE '1990-06-07
+02:00:00')");
		
		res.next();
		
		System.out.println("Year: " + res.getInt(1));
				
		// ---- This doesn't work ----
		PreparedStatement stmt2 = conn.prepareStatement(
				"SELECT EXTRACT (YEAR FROM DATE ?)");
		
		java.sql.Date date = new java.sql.Date(
				new GregorianCalendar(1990, 5,
7).getTime().getTime());
		
		stmt2.setDate(1, date);
		
		// It doesn't work although the compiled statement with
values inserted 
		// seems to be textually equal to the statement above:
		System.out.println(stmt2.toString());
		
		// here I get an SQL exception: 
		res = stmt2.executeQuery();
		
		res.next();
		
		System.out.println("Year: " + res.getInt(1));
		
		
		conn.close();
	}

}


***** END CODE *****

Output of the program:

*****
Year: 1990
SELECT EXTRACT (YEAR FROM DATE '1990-06-07 +02:00:00')
Exception in thread "main" org.postgresql.util.PSQLException: FEHLER:
Syntaxfehler bei »$1«
  Position: 32
	at
org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorI
mpl.java:2102)
	at
org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.ja
va:1835)
	at
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
	at
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.j
ava:500)
	at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2St
atement.java:388)
	at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statem
ent.java:273)
	at Bug.main(Bug.java:41)
*****


Responses

pgsql-jdbc by date

Next:From: Dilip.SarmahDate: 2012-03-19 22:28:54
Subject: PG JDBC4 driver - what is not implemented
Previous:From: Dave CramerDate: 2012-03-14 14:46:59
Subject: Re: MoveToInsertRow on a table without a primary key

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group