[postgis-users] Re: JDBC PreparedStatements and inserting geometries...

[Corey Puffalt]

I discovered a solution (not sure if this is the best solution or not
-- maybe an expert could comment?) to my problem.  That was to
parameterize everything inside the single quotes (as I had already
tried) and to wrap that inside a call to text().  I stumbled across
the text() method mentioned in another email in the archive... though
I haven't yet found documentation on what it does...

==========>8==========
String psqlInsertLine =
    "INSERT INTO gis_table (id, attr1, geometry, attr2) " +
    "VALUES (?, ?, GeometryFromText(text(?),4267), ?)";
PreparedStatement insertStmt = conn.prepareStatement(psqlInsertLine);
String geometry = getGeometry(attr1, attr2); // get a geometry based
on the attributes...

insertStmt.setObject(1, id);
insertStmt.setObject(2, attr1);
insertStmt.setObject(3, "'MULTILINESTRING ((" + geometry + "))'");
insertStmt.setObject(4, attr2);

insertStmt.executeUpdate();
==========8<==========

Corey

On Fri, 25 Feb 2005 14:33:43 -0700, Corey Puffalt <cplists at gmail.com> wrote:
> All,
> 
> I'm having problems performing inserts of geometry in Java using a
> PreparedStatement.  Here's what I'm attempting to do (stripped down
> for simplicity...error handling removed etc.):
> 
> ==========>8==========
> String psqlInsertLine =
>     "INSERT INTO gis_table (id, attr1, geometry, attr2) " +
>     "VALUES (?, ?, GeometryFromText('MULTILINESTRING ((?))',4267), ?)";
> PreparedStatement insertStmt = null;
> 
> String geometry = getGeometry(attr1, attr2); // get a geometry based
> on the attributes...
> 
> insertStmt.setObject(1, id);
> insertStmt.setObject(2, attr1);
> insertStmt.setObject(3, "'MULTILINESTRING ((" + geometry + ))');
> insertStmt.setObject(4, attr2);
> 
> insertStmt.executeUpdate();
> 
> ==========8<==========
> 
> geometry is a string like so: "-114.3252583 57.0771 ,-114.3227833
> 57.0313528 ,-114.3285083 56.9643194" for example.
> 
> The particular example above results in a:
> 
> org.postgresql.util.PSQLException: The column index is out of range:
> 4, number of columns: 3.
> 
> The error occurs presumably because of the single quotes.  How can I
> get around this?  I've tried parameterizing everything between the
> single quotes but that doesn't work either...instead I get:
> 
> java.sql.SQLException: ERROR: function geometryfromtext(character
> varying, integer) does not exist
> 
> Is there anyway to use a PreparedStatement with geometries?
> 
> Thanks,
> Corey
> 
> PS: Is there a searchable archive of the mailing list somewhere?
>