Обсуждение: Prepared Statement is not parsed correctly

Поиск
Список
Период
Сортировка

Prepared Statement is not parsed correctly

От
"Senden Kris"
Дата:
Hi,
 
Recently we discovered a defect in the jdbc driver of PostgreSQL. It is detected in version 8.1dev-401 and also found in all later versions. The problem is that we had a faulty SQL statement in our code that was partly executed by the jdbc-driver without any warning or error log. 
The statement is
 
SELECT * FROM POSTransaction WHERE DatTransEnd = (SELECT MAX(DatTransEnd) FROM POSTransaction WHERE IdtCheckout = 9 AND FlgTraining = ?) AND IdtCheckout = 9 AND FlgTraining = ?) ORDER BY HouTransEnd DESC
 
As you can see this statement contains 1 closing bracket ('(') too much.  However while debugging the driver I saw that the problem is caused in method AbstractJdbc2Statement.parseSql(String, int, StringBuffer, boolean) throws SQLException. In that method a counter is activated which holds the number of open brackets. Each time it encounters a ( it adds one and each time it encounters a ) it substracts one. When the counter's value is less than zero, the parsings stops. In our case, this resulted that our sql statement was shortened to
 
SELECT * FROM POSTransaction WHERE DatTransEnd = (SELECT MAX(DatTransEnd) FROM POSTransaction WHERE IdtCheckout = 9 AND FlgTraining = ?) AND IdtCheckout =  9 AND FlgTraining = ?

When executing the same query in  pgAdmin III Query, I got a syntax error : ERROR:  syntax error at or near ")" at character 136.
I would expected to have the same syntax error using the prepared statement.
Can anyone confirm this problem and point me to a possible fix?
 
All the best,
 
Kris Senden

Re: Prepared Statement is not parsed correctly

От
Kris Jurka
Дата:

On Wed, 1 Feb 2006, Senden Kris wrote:

> Recently we discovered a defect in the jdbc driver of PostgreSQL. It is
> detected in version 8.1dev-401 and also found in all later versions. The
> problem is that we had a faulty SQL statement in our code that was
> partly executed by the jdbc-driver without any warning or error log.
> The statement is
>
> SELECT * FROM POSTransaction WHERE DatTransEnd = (SELECT
> MAX(DatTransEnd) FROM POSTransaction WHERE IdtCheckout = 9 AND
> FlgTraining = ?) AND IdtCheckout = 9 AND FlgTraining = ?) ORDER BY
> HouTransEnd DESC
>
> When executing the same query in  pgAdmin III Query, I got a syntax
> error : ERROR:  syntax error at or near ")" at character 136.
> I would expected to have the same syntax error using the prepared
> statement.
>

Fixed in cvs for 8.0, 8.1, and HEAD.  New 8.1 jars available here:

http://www.ejurka.com/pgsql/jars/sk/

Kris Jurka