[postgis-users] Inserting geometries using a JDBC PreparedStatement

[Markus Schaber]

Hi, Corey,

Corey Puffalt wrote:

> Has anyone been able to insert geometries using a standard JDBC 
> PreparedStatement successfully with Postgis 1.1.3 & Postgresql 8.1.4?
>  I had this working with postgis 0.8 & Postgresql 7.4.1 but I'm now 
> upgrading to the latest versions.

I suggest that you also updated the pgjdbc driver, and do not use the
PostGIS jdbc extension, right?

> String psqlInsertLine = "INSERT INTO gis_table (id, attr1, geometry, 
> attr2) " + "VALUES (?, ?, GeometryFromText(?,4267), ?)"; 
> PreparedStatement insertStmt = conn.prepareStatement(psqlInsertLine);
>  geometry is a string. For example: "'LINESTRING(-113.7568111 
> 57.1167611 ,-113.7568111 57.1167611 ,-113.8803611 57.1146194 
> ,-113.8803611 57.1146194 )'"
> 
> This works under 0.8 but with 1.1.3 I'm getting the following error:
> 
> org.postgresql.util.PSQLException: ERROR: Invalid OGC WKT (does not 
> start with P,L,M or G)
> 
> As you can see my WKT does, in fact, start with an L ?!?

No, it does start with an '.

As you use setString, the driver includes the ' inside the string using
proper quoting, and sends it to the backend.

I wonder why and how it worked with the old backend, btw. Is it really
the same code? Maybe you used string concatenation instead of setString
and prepared statements? It's also possible that it's a weird side
effect of using the V2 protocol with an old jdbc driver...


HTH,
Markus
-- 
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf.     | Software Development GIS

Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org