[postgis-users] problem using JDBC PreparedStatement

[Just van den Broecke]

Hi Mark,

Thanks. Though I think neither of your two reasons are the cause. The 
library code that generates the PSs is used by many other apps/tables 
and always works. For INSERT it uses exactly the same order as specified 
in the table (using table metadata). Any column combination of INTs 
STRING etc works (except thus for "geometry" columns)

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

(These strings come from the debugger, like java.sql.PreparedStatement 
member field values). Like said when using a real PGgeometryLW object 
i.s.o. a String the same code works. best,

Just


Mark Cave-Ayland wrote:

> 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.
> 
> 
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
> 
>