If I change the line to
SelectSelectStep s = f.select(Routines.stIntersects3(cast(pgpt,
String.class), cast(pgln, String.class)).as("magic"));
Then the cast as any goes away and the SQL looks like:
280 [main] DEBUG org.jooq.tools.LoggerListener - -> with bind values
: select "public"."st_intersects"('POINT(0 0)', 'LINESTRING(0 0,0 2)') as
"magic"
On Fri, Feb 1, 2013 at 3:24 PM, Peter Cooner <[email protected]> wrote:
> Example project for #1 - This assumes you're talking to Postgres 9.1 and
> PostGIS 2, There is an SQL file to create the DB and such.....
>
> https://github.com/petriborg/jooq-gis-test
>
> Maybe the PostGIS function st_intersects() isn't being generated correctly?
>
> http://postgis.refractions.net/docs/ST_Intersects.html
>
> It expects to geom objects, but of course jOOQ doesn't know about those -
> so it tries to use just plain Java Object... But then tries to cast them on
> the postgres side which just confuses me. :-)
>
>
>
>
>
> On Fri, Feb 1, 2013 at 1:01 PM, Peter Cooner <[email protected]> wrote:
>
>> 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"'
>>
>
>
>
> --
> 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"'
>
--
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.
