Thread: Bug report (with solution) on org.postgresql.jdbc2.EscapedFunctions

There is a bug on :

org.postgresql.jdbc2.EscapedFunctions.constantToInterval method.

Value parameter have not to contain an integer. For example : ? a prepared statment JDBC parameter

each return of constantToInterval method like :
must be replaced by :

This modification required to remove the interval keyword of all method using constantToInterval.

Sorry for my English.

Have a nice day.

Christophe JANTON

On Wed, 5 Nov 2008, Christophe Janton wrote:

> org.postgresql.jdbc2.EscapedFunctions.constantToInterval method.
>
> Value parameter have not to contain an integer. For example : ? a prepared
> statment JDBC parameter
>
> each return of constantToInterval method like :
> *return "'"+value+" day'";*
> must be replaced by :
> *return "CAST(" + value + " || ' day' as interval)";*
>
> This modification required to remove the interval keyword of all method
> using constantToInterval.
>

I'm not sure I fully understand the problem.  Can you provide a testcase
to demonstrate the failure?

Kris Jurka

I have applied your fix to CVS for 8.2 and up.

Kris Jurka

Christophe Janton wrote:
> Make a PreparedStatement like this :
>
> select *
> from TABLE t
> where
> t.col < {fn timestampadd(SQL_TSI_SECOND,?,now())}
>
> this will be convert to :
>
> select *
> from TABLE t
> where
> t.col < (interval '? second' + now())
>
> then when, in java, you will set the parameter on the PreparedStatement
> : the jdbc driver will raise an exception like wrong parameter index.
> This exception is because the parameters <?> is surround with simple
> quote and so not recognise as parameter.
>
> I suggest that the conversion have to be :
>
> select *
> from TABLE t
> where
> t.col < (CAST(? || ' second' as interval) + now())
>
> I hope this will help you.
>
> 2008/11/7 Kris Jurka <books@ejurka.com <mailto:books@ejurka.com>>
>
>
>
>     On Wed, 5 Nov 2008, Christophe Janton wrote:
>
>         org.postgresql.jdbc2.EscapedFunctions.constantToInterval method.
>
>         Value parameter have not to contain an integer. For example : ?
>         a prepared
>         statment JDBC parameter
>
>         each return of constantToInterval method like :
>         *return "'"+value+" day'";*
>         must be replaced by :
>         *return "CAST(" + value + " || ' day' as interval)";*
>
>         This modification required to remove the interval keyword of all
>         method
>         using constantToInterval.
>
>
>     I'm not sure I fully understand the problem.  Can you provide a
>     testcase to demonstrate the failure?
>
>     Kris Jurka
>
>