Best unofficial Apache Server developers community
Username
Forgot password?
Sign in with Twitter account
Sign in with Facebook account

PreparedStatement is not reading all my parameters for PostGIS Geography

0

66 views

I have the following JDBC code. Note that I am attempting to use PostGIS geography:

PreparedStatement stmt = db.prepareStatement("INSERT INTO " +
                    "source_imagery (image_path, boundary, image_time)" +
                    " VALUES (?, ST_GeographyFromText('POLYGON((" +
                    "? ?, ? ?, ? ?, ? ?))'), ?)");

            stmt.setString(1, file.getAbsolutePath());
            stmt.setDouble(2, bounds.getY());
            stmt.setDouble(3, bounds.getX());
            ...

I am getting the following exception on the last line of code:

org.postgresql.util.PSQLException: The column index is out of range: 3, number of columns: 2.

I understand that it thinks I only have 2 parameters there, but you can see that I intended there to be 10. I'm not sure why it is not reading any of the parameters within the POLYGON. I know that this SQL statement works if I use it directly in the database, but I'm not sure what I have to change to make it work in my Java code. Any ideas?

asked June 21, 2011 5:18 pm CDT
posted via StackOverflow

1 Answers

1
Best answer
 

Your problem is that this:

'POLYGON((? ?, ? ?, ? ?, ? ?))'

is an SQL string literal that just happens to contain eight question marks. Since that is an SQL string literal, none of the question marks inside it are considered to be placeholders. That leaves you with two placeholders: the one at the very beginning of the VALUES list and the one at the very end.

You'll have to build your polygon some other way. There might be a better way than ST_GeographyFromText but, alas, I don't know what it is and I don't have PostGIS set up anywhere. If necessary, you can build the POLYGON string by hand with standard string wrangling and then use a placeholder for it:

VALUES (?, ST_GeographyFromText(?), ?)

The placeholder inside ST_GeographyFromText will be seen as a placeholder as it isn't inside a string literal and you could user stmt.setString to give it a value.

answered June 25, 2011 8:20 am CDT

Your answer

Join with account you already have


Sign in with Twitter account
Sign in with Facebook account
Sign in with Google Friend Connect

Preview
Similar questions
Engineyard using postgis
February 4, 2011
Rails with PostGIS
March 14, 2011
Get PostGIS version
January 28, 2011
Reading log file
April 18, 2011