Inserting geometries using a JDBC PreparedStatement

[Yeroc]

All...

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.

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(?,4267), ?)";
PreparedStatement insertStmt = conn.prepareStatement(psqlInsertLine);

String geometry = getGeometry(attr1, attr2); // get a geometry based
on the attributes...

insertStmt.setString(1, id);
insertStmt.setString(2, attr1);
insertStmt.setString(3, geometry);
insertStmt.setString(4, attr2);

insertStmt.executeUpdate();

==========8<==========

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 ?!?

Does anyone how to get this working?  What am I doing wrong?

Thanks,
Corey

_______________________________________________
postgis-users mailing list
postgis-users@...
http://postgis.refractions.net/mailman/listinfo/postgis-users

[Guido Lemoine]

Corey,

Apparently, you do no longer need the single quote around the LINESTRING.
I reproduced your problem and found that dropping the single quotes
solves the
problem. I am using 8.0.3/1.0. Curious.

GL

Corey Puffalt wrote:

> All...
>
> 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.
>
> 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(?,4267), ?)";
> PreparedStatement insertStmt = conn.prepareStatement(psqlInsertLine);
>
> String geometry = getGeometry(attr1, attr2); // get a geometry based
> on the attributes...
>
> insertStmt.setString(1, id);
> insertStmt.setString(2, attr1);
> insertStmt.setString(3, geometry);
> insertStmt.setString(4, attr2);
>
> insertStmt.executeUpdate();
>
> ==========8<==========
>
> 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 ?!?
>
> Does anyone how to get this working?  What am I doing wrong?
>
> Thanks,
> Corey
>
>------------------------------------------------------------------------
>
>_______________________________________________
>postgis-users mailing list
>postgis-users@...
>http://postgis.refractions.net/mailman/listinfo/postgis-users
>  
>

_______________________________________________
postgis-users mailing list
postgis-users@...
http://postgis.refractions.net/mailman/listinfo/postgis-users

[Markus Schaber-7]

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

[Yeroc]

Guido,

Thanks a lot!  That's the one thing I didn't try and when you think about it, should have been the most obvious. :/  The new behavior actually makes more sense than before since calling setString() should automatically put in the appropriate quotes.  The single quotes I was putting into the string were probably being escaped.

Thanks again,
Corey

Guido Lemoine wrote:

Corey,

Apparently, you do no longer need the single quote around the LINESTRING.
I reproduced your problem and found that dropping the single quotes
solves the
problem. I am using 8.0.3/1.0. Curious.

GL

[Yeroc]

Markus,

I tried it with both the jdbc driver I had been using previously as well as the new one bundled with v8.1.4.  There was no difference in behavior between the two drivers.  The difference appears to be in the backend.  

The code is identical.  I took my existing code and just re-ran it against the new database.  As you say, now that I think about it, it's a wonder that it ever worked.  There must have been a bug (or feature? ;) in the backend that caused it to work before.

Thanks for your reply.
Corey

[Guido Lemoine]

So, did you remove the single ' quotes? I had to revise the code that used
an old JDBC driver (requiring the ' quote) to the new 8.x driver where the
use of the quote causes an error. Obviously a JDBC driver problem then.
On the "back-end" you still need the quote, i.e. when using psql. It's nice
that the new driver takes care of the quotes, but not really consistent. I
would expect the SQL statements in my Java code to be more or less the
same as in psql.

GL

Yeroc wrote:

>Markus,
>
>I tried it with both the jdbc driver I had been using previously as well as
>the new one bundled with v8.1.4.  There was no difference in behavior
>between the two drivers.  The difference appears to be in the backend.  
>
>The code is identical.  I took my existing code and just re-ran it against
>the new database.  As you say, now that I think about it, it's a wonder that
>it ever worked.  There must have been a bug (or feature? ;) in the backend
>that caused it to work before.
>
>Thanks for your reply.
>Corey
>
>
>  
>

_______________________________________________
postgis-users mailing list
postgis-users@...
http://postgis.refractions.net/mailman/listinfo/postgis-users

[Yeroc]

Guido,

Yes I removed the single (') quotes.  JDBC drivers are supposed to handle all the quoting for you.  This is a good thing in general since you don't have to worry about the idiosyncracies of different databases.  Using PreparedStatements and a properly-written JDBC driver will also protect you from SQL injection since the driver takes care escaping/quoting things properly.

Corey

Guido Lemoine wrote:

So, did you remove the single ' quotes? I had to revise the code that used
an old JDBC driver (requiring the ' quote) to the new 8.x driver where the
use of the quote causes an error. Obviously a JDBC driver problem then.
On the "back-end" you still need the quote, i.e. when using psql. It's nice
that the new driver takes care of the quotes, but not really consistent. I
would expect the SQL statements in my Java code to be more or less the
same as in psql.