[postgis-users] problem using JDBC PreparedStatement

[Mark Cave-Ayland]

On Tue, 2006-10-10 at 00:43 +0200, Just van den Broecke wrote:

> For example the PreparedStatement
> 
> INSERT INTO spatialone VALUES (?,?,?,?,?)
> expands into
> INSERT INTO spatialone VALUES 
> (28,TheName,1160432443975,1160432443975,GeomFromText('POINT (4.92 
> 52.35)', 4326))
> 
> On execute this gives the error:
> "org.postgresql.util.PSQLException: ERROR: column "point" is of type 
> geometry but expression is of type character varying"


Hi Just,

I can see two reasons why this won't work. Firstly, you're missing a set
of single quotes around 'TheName' which will cause the PostgreSQL parser
to fail - I believe that this should be done automatically by the JDBC
driver (so it may just be a typo), but it's something that will cause
the query to fail.

Secondly, you haven't specified a column list. SQL by its very nature is
unordered and so you need to specify a column list with columns given in
the same order as the values in your VALUES clause. Otherwise as you
change the table by adding dropping columns, the default order of the
columns will change and break your application.

So you'll need to make your prepared statement look something like this:

INSERT INTO spatialone (id, name, val1, val2, the_geom) VALUES
(?,?,?,?,?)

Where of course the column names match the ones in your table.


HTH,

Mark.