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

You may try this:

INSERT INTO poi(geom,latitude,longitude,description,comment)
VALUES (ST_GeomFromText('POINT(' || ? || ' ' || ? || ')', 4326), ?, ?,
?, ?);

then you have 6 parameters to bind.
Andras

> 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
>