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

On 23 сен, 22:25, msakre...@truviso.com (Maciek Sakrejda) wrote:
> > As I
> > understand, it says about number of columns because there are 5
> > columns listed in query and I try to bind value to non existing 6
> > column.
>
> As I understand it,
>
>  (1) the parameter handling code doesn't know or care you're executing
> an insert, so it shouldn't deal in "columns" here, just in parameters
>  (2) the question marks in 'POINT(?,?)' should not be treated as
> parameter markers at all: it should just be a String constant.
>
> Can you show us exactly how you're binding parameters in jdbc?

Here is code

String sql = "INSERT INTO
poi(geom,latitude,longitude,description,comment) VALUES
(ST_GeomFromText('POINT(? ?)', 4326), ?, ?, ?, ?)";
stmt = conn.prepareStatement(sql);

stmt.setFloat(1, lon);
stmt.setFloat(2, lat);
stmt.setFloat(3, lon);
stmt.setFloat(4, lat);
stmt.setTimestamp(5, descr);
stmt.setString(6, comment);

Currently I solve this problem with creating SQL in runtime from
several string pieces like this

String sql = "INSERT INTO
poi(geom,latitude,longitude,description,comment) VALUES
(ST_GeomFromText('POINT(";
sql += Float.toString(lon) + " ";
sql += Float.toString(lat) + ")', 4326),";
....

Bye,
Alex