Re: Prepared statement with function as argument: how to bind values?

Do you have to quote 'POINT(?,?)'

Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca

On Thu, Sep 22, 2011 at 5:01 AM, alexbruy <alexander(dot)bruy(at)gmail(dot)com> wrote:
> Hi all,
>
> in my app I need to execute insert query one of the arguments of which
> is a PostGIS
> function call. Here is query
>
> INSERT INTO poi(geom,latitude,longitude,description,comment)
> VALUES (ST_GeomFromText('POINT(34.567 45.5621)', 4326), 34.567,
> 45.5621, 'some description', 'user comment');
>
> The problem is that I can't bind all necessary values, I can't write
> query as
>
> INSERT INTO poi(geom,latitude,longitude,description,comment)
> VALUES (ST_GeomFromText('POINT(? ?)', 4326), ?, ?, ?, ?);
>
> and bind all values because in this case I get exception
>
> org.postgresql.util.PSQLException: The column index is out of range:
> 6, number of columns: 5.
>
> How I can bind values for function arguments? I see two possible
> solutions:
> 1. create query string in runtime, concatenate all function arguments
> and then bind all other values
> 2. write stored fuction in database which will accept all arguments
> and the internally call ST_GeomFromText
>
> In first case I can't prepare statement for multiple inserts and need
> to create query string on every
> insert operation. The secon approach looks a more flexible.
>
> Any hints on this problem? Maybe I miss something in documentation and
> it is possible to bind values
> to the functions arguments inside insert query?
>
> Thanks,
> Alex
>
> --
> Sent via pgsql-jdbc mailing list (pgsql-jdbc(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-jdbc
>