Not sure how I forgot the exception for #1 Exception in thread "main" org.jooq.exception.DataAccessException: SQL [select "public"."st_intersects"(cast(? as any), cast(? as any)) as "magic"]; ERROR: syntax error at or near "any" Position: 44 at org.jooq.impl.Util.translate(Util.java:649) at org.jooq.impl.DefaultExecuteContext.sqlException(DefaultExecuteContext.java:360) at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:262) at org.jooq.impl.AbstractResultQuery.fetch(AbstractResultQuery.java:274) at org.jooq.impl.AbstractResultQuery.fetch(AbstractResultQuery.java:350) at org.jooq.impl.AbstractDelegatingSelect.fetch(AbstractDelegatingSelect.java:184) at dao.Main.main(Main.java:80) Caused by: org.postgresql.util.PSQLException: ERROR: syntax error at or near "any" Position: 44 at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2101) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1834) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255) at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:510) at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:386) at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:379) at org.jooq.impl.DataSourcePreparedStatement.execute(DataSourcePreparedStatement.java:86) at org.jooq.impl.AbstractResultQuery.execute(AbstractResultQuery.java:196) at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:258) ... 4 more Java Result: 1
On Fri, Feb 1, 2013 at 12:04 PM, Peter Cooner <[email protected]> wrote: > Hi all, > > I am doing a refactor of our database application and am considering using > jOOQ. > > Previous setup: > - Postgres + PostGIS > - JDBC + PostGIS-JDBC > - Apache's DBUtil and DBCP > - 90% PreparedStatements > - Use of Point / PGgeometryLW > - Use of PGhstore > > I've done a review of jOOQ already, and am fairly pleased with what I see, > - Support of Postgres + PostGIS > - Support new dynamic query options > - Cleaner, more obvious DB API > - Support for functions (PostGIS has a lot) > - Support for PGobject (and thus PGhstore, PGgeometry, and PGgeometryLW) > - Support to limit what is imported > > A few questions I have: > > 1. I was trying to use a PostGIS routine st_intersects(geom1, geom2) -> > boolean, but jOOQ tries to cast the inputs: > 270 [main] DEBUG org.jooq.tools.LoggerListener - -> with bind values > : select "public"."st_intersects"(cast('POINT(0 0)' as any), > cast('LINESTRING(2 0,0 2)' as any)) as "magic" > > From the Java side, I'm passing in PGobjects and no cast should be needed. > > Code: > Connection c = DriverManager.getConnection(URL, "postgres", ""); > > Point point = new Point(0, 0); > //point.setSrid(4326); > PGgeometry pgpt = new PGgeometry(point); > > LineString line = new LineString("LINESTRING ( 2 0, 0 2 )"); > //line.setSrid(4326); > PGgeometry pgln = new PGgeometry(line); > > > Factory f = new Factory(c, SQLDialect.POSTGRES); > SelectSelectStep s = f.select(Routines.stIntersects2(pgpt, > pgln).as("magic")); > List<?> fetch = s.fetch("magic"); > > > 2. WIth this refactoring of the Java, I'm trying to figure out how I > could implement table partitioning - suggestions? > > Table partitioning in Postgres is implemented via table inheritance. > Partitioning tables in Postgres can make working with the data more > complicated and slower though. To avoid some of the slowdown I plan to use > jOOQ's ability to dynamically build queries, assigning inserts, updates, > and deletes to the correct table(s) avoiding using triggers and other > Postgres magic functions - I'm wondering if you have any suggestions on > this... > > > > -- > You received this message because you are subscribed to the Google Groups > "jOOQ User Group" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > For more options, visit https://groups.google.com/groups/opt_out. > > > -- Pete "Yates went on to say that using MS formats left a fresh, minty feeing in user's mouths while every time an open [format] is used a kitten dies." -- maggard (on slashdot) perl -lne '(1x$_) !~ /^1?$|^(11+?)\1+$/ && print "$_ is prime"' -- You received this message because you are subscribed to the Google Groups "jOOQ User Group" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. For more options, visit https://groups.google.com/groups/opt_out.
