[postgis-users] problem using JDBC PreparedStatement

[Markus Schaber]

Hi, Mark,

Just van den Broecke wrote:

> Another example to sort of prove my point is that UPDATE fails with the
> same error reason:
> UPDATE spatialone SET point=?, modificationdate=? WHERE id = 52
> expands to
> UPDATE spatialone SET point=GeomFromText('POINT (4.92 52.35)', 4326),
> modificationdate=1160482168135 WHERE id = 52

So you use prepstat.setString(1,"GeomFromText('POINT (4.92 52.35)'") or
prepstat.setObjet(1,"GeomFromText('POINT (4.92 52.35)'")?

Both of them will see that it's a java.lang.String object you put into
the statement, and will send it as TEXT to the server, that's why the
server does not recognize this as a Geometry.

There's a second problem: The string will be sent literally, so the
GeomFromText() function will not be executed on the server side, but be
part of the String.

You have two possibilities.

A) Sending EWKT/EWKB

Prepare the statement like "UPDATE spatialone SET point=?::geometry ..."
and then send either "SRID=4326;POINT(4.92 52.35)" or
"0101000020E6100000AE47E17A14AE1340CDCCCCCCCC2C4A40" via setString().

This is compatible with using SetObject() on a LWGeometry, that means
you can use the same prepared statement.

B) Preparing the function call

Prepare "UPDATE spatialone SET point=GeomFromText(?,?) ..." and then
setString(1,"POINT(4.92 52.35)") and setInt(2,4326). When all your
geometries have the same SRID, you can also prepare "UPDATE spatialone
SET point=GeomFromText(?,4326) ..." which will save you the setInt() call.

This has the advantage of being compatible with the way OpenGIS advises
in their standard documents, so it should work with other OGC compliant
databases.

Both ways should work through a plain JDBC driver without the
DriverWrapperLW wrappers, the Wrapper's sole purpose is to make
PGgeometryLW work.

> (These strings come from the debugger, like java.sql.PreparedStatement
> member field values).

Some pgjdb driver versions had a problem with properly quoting the
parametrized queries in debug output. If you want to see what really
happened, you'll have to use the server logs, but they seem to be rather
useless for prepared statements before version 8.2 which is currently in
beta.

> Like said when using a real PGgeometryLW object
> i.s.o. a String the same code works. best,


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

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