Thread: Prepared statement with function as argument: how to bind values?
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
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.bruy@gmail.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@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-jdbc >
> Do you have to quote 'POINT(?,?)' Since that's an argument to ST_GeomFrom*Text*, I imagine he does. Or do you mean is he quoting the parameter value with SQL apostrophe quotes, which he shouldn't?. In any case, that looks like a weird API--if there's a different function you can call, Alex, that will take an actual point object, that would probably make your life easier. If this is all you have to work with, the problem is that you can't parameterize a text literal. I think you would need to pass the full String 'POINT(34.567 45.5621)' as a jdbc parameter value. However, I don't quite understand the error you're getting. I would expect complains about extra parameters, not about the number of columns (which looks legit). --- Maciek Sakrejda | System Architect | Truviso 1065 E. Hillsdale Blvd., Suite 215 Foster City, CA 94404 (650) 242-3500 Main www.truviso.com
Hi, On 23 сен, 16:25, p...@fastcrypt.com (Dave Cramer) wrote: > Do you have to quote 'POINT(?,?)' Yes, because this is argument of ST_GeomFromText function. On 23 сен, 19:28, msakre...@truviso.com (Maciek Sakrejda) wrote: > Since that's an argument to ST_GeomFrom*Text*, I imagine he does. Or > do you mean is he quoting the parameter value with SQL apostrophe > quotes, which he shouldn't?. In any case, that looks like a weird > API--if there's a different function you can call, Alex, that will > take an actual point object, that would probably make your life > easier. Unfortunately there is no another function that can take actual point. But it is possible to create stored procedure that will take all necessary arguments. > If this is all you have to work with, the problem is that you can't > parameterize a text literal. I think you would need to pass the full > String 'POINT(34.567 45.5621)' as a jdbc parameter value. However, I > don't quite understand the error you're getting. I would expect > complains about extra parameters, not about the number of columns > (which looks legit). Thanks, I'll try. Regarding error message, I find it a bit confusing. 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. Thanks, Alex
> 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? --- Maciek Sakrejda | System Architect | Truviso 1065 E. Hillsdale Blvd., Suite 215 Foster City, CA 94404 (650) 242-3500 Main www.truviso.com
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
> 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);
> ...
Well, I'm still not sure what's going on. That doesn't seem like it
would correspond to the error message. For what it's worth, what I was
suggesting is
String sql = "INSERT INTO
poi(geom,latitude,longitude,description,comment) VALUES
(ST_GeomFromText('POINT(? ?)', 4326), ?, ?, ?, ?)";
stmt = conn.prepareStatement(sql);
stmt.setString(1, "POINT(" + lon + "," + lat + ")");
stmt.setFloat(2, lon);
stmt.setFloat(3, lat);
...
---
Maciek Sakrejda | System Architect | Truviso
1065 E. Hillsdale Blvd., Suite 215
Foster City, CA 94404
(650) 242-3500 Main
www.truviso.com
Sivakumar wrote
> I think it's too late to respond for this issue, but i hope it will be
> useful for someone else. I also faced the same issue of binding, how i
> solved is
>
>
> String sql = "INSERT INTO
> poi(geom,latitude,longitude,description,comment) VALUES
> (
*
> ST_GeomFromText(?, 4326)
*
> , ?, ?, ?, ?)";
> stmt = conn.prepareStatement(sql);
*
> stmt.setString(1, "POINT(" +lon + " " + lat + ")");
*
> stmt.setFloat(2, lon);
> stmt.setFloat(3, lat);
> stmt.setTimestamp(4, descr);
> stmt.setString(5, comment);
>
> Thanks,
> Sivakumar
Doesn't seem that much an improvement over dynamic SQL.
INSERT INTO ...
SELECT func_outer(func_inner(val1, val2), 4326), val3
FROM (VALUES (?,?,?)) src (val1,val2,val3);
David J.
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Prepared-statement-with-function-as-argument-how-to-bind-values-tp4833351p5797356.html
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.
David Johnston wrote
>
> Sivakumar wrote
>> I think it's too late to respond for this issue, but i hope it will be
>> useful for someone else. I also faced the same issue of binding, how i
>> solved is
>>
>>
>> String sql = "INSERT INTO
>> poi(geom,latitude,longitude,description,comment) VALUES
>> (
*
>> ST_GeomFromText(?, 4326)
*
>> , ?, ?, ?, ?)";
>> stmt = conn.prepareStatement(sql);
*
>> stmt.setString(1, "POINT(" +lon + " " + lat + ")");
*
>> stmt.setFloat(2, lon);
>> stmt.setFloat(3, lat);
>> stmt.setTimestamp(4, descr);
>> stmt.setString(5, comment);
>>
>> Thanks,
>> Sivakumar
> Doesn't seem that much an improvement over dynamic SQL.
>
> INSERT INTO ...
> SELECT func_outer(func_inner(val1, val2), 4326), val3
> FROM (VALUES (?,?,?)) src (val1,val2,val3);
>
> David J.
I take the editorializing back...
Reading again since the call is expecting a text for the geom then
concatenating together a string seems like a necessary requirement of the
solution.
That said the use of select and values together is something that can be
useful.
David J.
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Prepared-statement-with-function-as-argument-how-to-bind-values-tp4833351p5797359.html
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.
I think it's too late to respond for this issue, but i hope it will be useful for someone else. I also faced the same issue of binding, how i solved is String sql = "INSERT INTO poi(geom,latitude,longitude,description,comment) VALUES (ST_GeomFromText(?, 4326), ?, ?, ?, ?)"; stmt = conn.prepareStatement(sql); stmt.setString(1, "POINT(" +lon + " " + lat + ")"); stmt.setFloat(2, lon); stmt.setFloat(3, lat); stmt.setTimestamp(4, descr); stmt.setString(5, comment); Thanks, Sivakumar
View this message in context: Re: Prepared statement with function as argument: how to bind values?
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.
View this message in context: Re: Prepared statement with function as argument: how to bind values?
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.