Not supported by dialect : Type class org.postgis.PGgeometry is not supported in dialect SQL99

[Andreas Ahlenstorf]

Hi!

When I'm trying to insert geo data into PostGIS, JOOQ 3.5.4 throws a SQLDialectNotSupportedException: 

Not supported by dialect : Type class org.postgis.PGgeometry is not supported in dialect SQL99.

The relevant binding:

public class PositionBinding implements Binding<Object, Position> {

    private final Converter<Object, Position> converter = new PositionConverter();

    @Override

    public Converter<Object, Position> converter() {

        return converter;

    }

    @Override

    public void sql(BindingSQLContext<Position> ctx) throws SQLException {

        ctx.render().visit(DSL.val(ctx.convert(converter).value()));

    }

    @Override

    public void register(BindingRegisterContext<Position> ctx) throws SQLException {

        ctx.statement().registerOutParameter(ctx.index(), Types.JAVA_OBJECT);

    }

    @Override

    public void set(BindingSetStatementContext<Position> ctx) throws SQLException {

        ctx.statement().setObject(ctx.index(), ctx.convert(converter).value(), null);

    }

    @Override

    public void set(BindingSetSQLOutputContext<Position> bindingSetSQLOutputContext) throws SQLException {

        throw new SQLFeatureNotSupportedException();

    }

    @Override

    public void get(BindingGetResultSetContext<Position> ctx) throws SQLException {

        ctx.convert(converter).value(ctx.resultSet().getObject(ctx.index()));

    }

    @Override

    public void get(BindingGetStatementContext<Position> ctx) throws SQLException {

        ctx.convert(converter).value(ctx.statement().getObject(ctx.index()));

    }

    @Override

    public void get(BindingGetSQLInputContext<Position> bindingGetSQLInputContext) throws SQLException {

        throw new SQLFeatureNotSupportedException();

    }

}

The converter just returns a PGgeometry which is a PGobject and registered with PostgreSQL's JDBC driver.

The field definition generated by JOOQ looks as follows:

createField("position", org.jooq.impl.DefaultDataType.getDefaultDataType("USER-DEFINED"), this, "", new PositionBinding());

Reading works.

I've read through old discussions and GitHub issues but wasn't able to solve the issue myself. The official documentation (http://www.jooq.org/doc/latest/manual/code-generation/custom-data-type-bindings/) wasn't very helpful either. I would be very happy if someone could point me in the right direction.

Regards,

Andreas

[Lukas Eder]

Hello,

Thanks for your enquiry.

Could you please provide the DDL of the table that contains this position column? It will be helpful in trying to reproduce this issue. Also, the source code of PositionConverter and Position (if you wrote that yourself) would be needed.

Best Regards,

Lukas

--
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 jooq-user+...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Andreas Ahlenstorf]

On 29 May 2015 at 18:16, Lukas Eder <lukas...@gmail.com> wrote:

> Could you please provide the DDL of the table that contains this position column? It will be helpful in trying to reproduce this issue. Also, the source code of PositionConverter and Position (if you wrote that yourself) would be needed.

public class PositionConverter implements Converter<Object, Position> {

@Override
public Position from(Object object) {
if (object == null) {
return null;
}

Geometry geometry = ((PGgeometry) object).getGeometry();

if (!(geometry instanceof Point)) {
throw new IllegalArgumentException("Geometry is not a
org.postgis.Point.");
}

Point point = (Point)geometry;

return new Position(BigDecimal.valueOf(point.getX()),
BigDecimal.valueOf(point.getY()));
}

@Override
public Object to(Position position) {
if (position == null) {
return null;
}

Point p = new Point(position.getLatitude().doubleValue(),
position.getLongitude().doubleValue());
p.setSrid(Position.SPATIAL_REF_SYS);
return new PGgeometry(p);
}

@Override
public Class<Object> fromType() {
return Object.class;
}

@Override
public Class<Position> toType() {
return Position.class;
}
}

public class Position {

public static final int SPATIAL_REF_SYS = 4326;

private BigDecimal latitude;

private BigDecimal longitude;

// getters, setters omitted
}

create table data_point (
id serial primary key,
survey_id integer not null,
probe_id integer not null,
position geography(point,4326) null,
date_recorded timestamp with time zone not null,
date_arrived timestamp with time zone not null,
data json null
);

Regards,
Andreas

[Lukas Eder]

Thank you very much. We'll look into this as soon as possible

Regards,
Andreas

[Andreas Ahlenstorf]

Hi Lukas!

On 2 June 2015 at 08:46, Lukas Eder <lukas...@gmail.com> wrote:
> Thank you very much. We'll look into this as soon as possible

Any news on this?

Regards,
Andreas

[Lukas Eder]

Hi Andreas,

I'm sorry, I don't have any news on this issue yet - and I'll be away from E-Mail for the next three weeks. Sorry that I can't be of any help with this issue right now.

Regards,
Andreas

[Andreas Ahlenstorf]

Hi Lukas,

On 19 June 2015 at 17:40, Lukas Eder <lukas...@gmail.com> wrote:

> I'm sorry, I don't have any news on this issue yet - and I'll be away from
> E-Mail for the next three weeks. Sorry that I can't be of any help with this
> issue right now.

No problem. I'm currently working around the issue by using a geometry
constructor (ST_GeomFromEWKT(?)) and returning an EWKT string from the
converter for all write operations. Not pretty, but it does its job so
far.

Regards,
Andreas

[Lukas Eder]

Hi Andreas,

Some late feedback on this issue (which I haven't forgotten). Thanks for your patience!

I've wrestled with the PostgreSQL JDBC driver's and PostGIS's various PG* data types. They're not really very user friendly, I'm afraid, but I guess that's as good as it gets when working with PostGIS. Here's an updated version of the Binding and the Converter implementations:

package org.jooq.test.all.converters;

import java.math.BigDecimal;

import java.sql.SQLException;

import org.jooq.Converter;

import org.jooq.test.all.types.Position;

import org.postgis.Geometry;

import org.postgis.PGgeometry;

import org.postgis.Point;

/**

 * @author Lukas Eder

 * @autho Andreas Ahlenstorf

 * @see <a href=

 *      "https://groups.google.com/d/msg/jooq-user/TBQZCPTCvnk/HJYND9AQs6EJ">

 *      https://groups.google.com/d/msg/jooq-user/TBQZCPTCvnk/HJYND9AQs6EJ</a>

 */

public class PositionConverter implements Converter<Object, Position> {

    @Override

    public Position from(Object object) {

        if (object == null) {

            return null;

        }

        Geometry geometry = null;

        try {

            geometry = PGgeometry.geomFromString(object.toString());

        }

        catch (SQLException e) {

            throw new IllegalArgumentException(e);

        }

        if (!(geometry instanceof Point)) {

            throw new IllegalArgumentException("Geometry is not a org.postgis.Point.");

        }

        Point point = (Point) geometry;

        return new Position(BigDecimal.valueOf(point.getX()), BigDecimal.valueOf(point.getY()));

    }

    @Override

    public Object to(Position position) {

        if (position == null) {

            return null;

        }

        Point p = new Point(position.latitude.doubleValue(), position.longitude.doubleValue());

        p.setSrid(Position.SPATIAL_REF_SYS);

        return new PGgeometry(p);

    }

    @Override

    public Class<Object> fromType() {

        return Object.class;

    }

    @Override

    public Class<Position> toType() {

        return Position.class;

    }

}

package org.jooq.test.all.bindings;

import java.sql.SQLException;

import java.sql.SQLFeatureNotSupportedException;

import org.jooq.Binding;

import org.jooq.BindingGetResultSetContext;

import org.jooq.BindingGetSQLInputContext;

import org.jooq.BindingGetStatementContext;

import org.jooq.BindingRegisterContext;

import org.jooq.BindingSQLContext;

import org.jooq.BindingSetSQLOutputContext;

import org.jooq.BindingSetStatementContext;

import org.jooq.Converter;

import org.jooq.impl.DSL;

import org.jooq.test.all.converters.PositionConverter;

import org.jooq.test.all.types.Position;

/**

 * @author Lukas Eder

 * @autho Andreas Ahlenstorf

 * @see <a href=

 *      "https://groups.google.com/d/msg/jooq-user/TBQZCPTCvnk/HJYND9AQs6EJ">

 *      https://groups.google.com/d/msg/jooq-user/TBQZCPTCvnk/HJYND9AQs6EJ</a>

 */

public class PostgresPositionBinding implements Binding<Object, Position> {

    private final Converter<Object, Position> converter = new PositionConverter();

    @Override

    public Converter<Object, Position> converter() {

        return converter;

    }

    @Override

    public void sql(BindingSQLContext<Position> ctx) throws SQLException {

        // this change is key here. I suspect the manual was misleading...

        ctx.render().visit(DSL.sql("?::geography"));

    }

    @Override

    public void set(BindingSetStatementContext<Position> ctx) throws SQLException {

        ctx.statement().setObject(ctx.index(), ctx.convert(converter).value());

    }

    @Override

    public void get(BindingGetResultSetContext<Position> ctx) throws SQLException {

        ctx.convert(converter).value(ctx.resultSet().getObject(ctx.index()));

    }

    @Override

    public void get(BindingGetStatementContext<Position> ctx) throws SQLException {

        ctx.convert(converter).value(ctx.statement().getObject(ctx.index()));

    }

    @Override

    public void get(BindingGetSQLInputContext<Position> bindingGetSQLInputContext) throws SQLException {

        throw new SQLFeatureNotSupportedException();

    }

    @Override

    public void set(BindingSetSQLOutputContext<Position> bindingSetSQLOutputContext) throws SQLException {

        throw new SQLFeatureNotSupportedException();

    }

    @Override

    public void register(BindingRegisterContext<Position> ctx) throws SQLException {

        throw new SQLFeatureNotSupportedException();

    }

}

With the above, I could successfully run the following test:

    @Test

    public void testPostgreSQLGISGeographyTypeBinding() {

        clean(T_PG_EXTENSIONS);

        assertEquals(1,

        create().insertInto(T_PG_EXTENSIONS)

                .columns(T_PG_EXTENSIONS.ID, T_PG_EXTENSIONS.PG_POSITION)

                .values(1, new Position(BigDecimal.ZERO, BigDecimal.ONE))

                .execute());

        Position position = create().fetchOne(T_PG_EXTENSIONS).getPgPosition();

        assertEquals(0, BigDecimal.ZERO.compareTo(position.latitude));

        assertEquals(0, BigDecimal.ONE.compareTo(position.longitude));

    }

Hope this helps,
Lukas

Regards,
Andreas

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.

[m...@tthias.eu]

Hi Lukas,

I am trying to use the Binding and Converter you wrote. While it works fine for reading the postgis geography type from the database, I did not manage to use it for writing.

The following code

    final ShipmentRecord shipmentRecord =
        create.newRecord(SHIPMENT, actualNewShipment);
    shipmentRecord.reset(SHIPMENT.ID);
    shipmentRecord.store();

Results in this error:

xpatron> org.jooq.exception.SQLDialectNotSupportedException: Type class org.postgis.PGgeometry is not supported in dialect POSTGRES
xpatron> at org.jooq.impl.DefaultDataType.getDataType(DefaultDataType.java:803)
xpatron> at org.jooq.impl.DefaultDataType.getDataType(DefaultDataType.java:747)
xpatron> at org.jooq.impl.DefaultBinding.toSQLCast(DefaultBinding.java:369)
xpatron> at org.jooq.impl.DefaultBinding.sql(DefaultBinding.java:268)
xpatron> at org.jooq.impl.Val.accept(Val.java:83)
xpatron> at org.jooq.impl.DefaultRenderContext.visit0(DefaultRenderContext.java:396)
xpatron> at org.jooq.impl.AbstractContext.visit0(AbstractContext.java:394)
xpatron> at org.jooq.impl.AbstractContext.visit(AbstractContext.java:159)
xpatron> at org.jooq.impl.FieldMapForInsert.accept(FieldMapForInsert.java:83)
xpatron> at org.jooq.impl.DefaultRenderContext.visit0(DefaultRenderContext.java:396)
xpatron> at org.jooq.impl.AbstractContext.visit0(AbstractContext.java:394)
xpatron> at org.jooq.impl.AbstractContext.visit(AbstractContext.java:159)
xpatron> at org.jooq.impl.FieldMapsForInsert.accept(FieldMapsForInsert.java:90)
xpatron> at org.jooq.impl.DefaultRenderContext.visit0(DefaultRenderContext.java:396)
xpatron> at org.jooq.impl.AbstractContext.visit0(AbstractContext.java:394)
xpatron> at org.jooq.impl.AbstractContext.visit(AbstractContext.java:159)
xpatron> at org.jooq.impl.InsertQueryImpl.toSQLInsert(InsertQueryImpl.java:407)
xpatron> at org.jooq.impl.InsertQueryImpl.accept0(InsertQueryImpl.java:317)
xpatron> at org.jooq.impl.AbstractDMLQuery.accept(AbstractDMLQuery.java:185)
xpatron> at org.jooq.impl.DefaultRenderContext.visit0(DefaultRenderContext.java:396)
xpatron> at org.jooq.impl.AbstractContext.visit0(AbstractContext.java:394)
xpatron> at org.jooq.impl.AbstractContext.visit(AbstractContext.java:159)
xpatron> at org.jooq.impl.AbstractQuery.getSQL0(AbstractQuery.java:481)
xpatron> at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:307)
xpatron> at org.jooq.impl.TableRecordImpl.storeInsert0(TableRecordImpl.java:184)
xpatron> at org.jooq.impl.TableRecordImpl$1.operate(TableRecordImpl.java:155)
xpatron> at org.jooq.impl.RecordDelegate.operate(RecordDelegate.java:128)
xpatron> at org.jooq.impl.TableRecordImpl.storeInsert(TableRecordImpl.java:151)
xpatron> at org.jooq.impl.UpdatableRecordImpl.store0(UpdatableRecordImpl.java:195)
xpatron> at org.jooq.impl.UpdatableRecordImpl.access$000(UpdatableRecordImpl.java:85)
xpatron> at org.jooq.impl.UpdatableRecordImpl$1.operate(UpdatableRecordImpl.java:135)
xpatron> at org.jooq.impl.RecordDelegate.operate(RecordDelegate.java:128)
xpatron> at org.jooq.impl.UpdatableRecordImpl.store(UpdatableRecordImpl.java:131)
xpatron> at org.jooq.impl.UpdatableRecordImpl.store(UpdatableRecordImpl.java:123)
xpatron> at eu.m2machine.xpatron.transport.service.ShipmentDAO.create(ShipmentDAO.java:66)
xpatron> at eu.m2machine.xpatron.transport.service.ShipmentService.create(ShipmentService.java:52)
xpatron> at eu.m2machine.xpatron.transport.rest.ShipmentREST.doReturning(ShipmentREST.java:235)
xpatron> at eu.m2machine.xpatron.transport.rest.ShipmentREST.createReturning(ShipmentREST.java:219)
xpatron> at eu.m2machine.xpatron.transport.rest.ShipmentREST.lambda$createShipment$4(ShipmentREST.java:152)
xpatron> at io.vertx.core.impl.ContextImpl.lambda$executeBlocking$17(ContextImpl.java:296)
xpatron> at io.vertx.core.impl.OrderedExecutorFactory$OrderedExecutor.lambda$new$265(OrderedExecutorFactory.java:91)
xpatron> at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
xpatron> at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
xpatron> at java.lang.Thread.run(Thread.java:745)


The code of the converter's to() method is:

    @Override                                                                    
    public Object to(Position position) {                                        
        if (position == null) {                                                  
            return null;                                                        
        }

                                                                       
                                                                                 
        Point p = new Point(position.getLatitude(), position.getLatitude());    
        p.setSrid(SPATIAL_REF_SYS);                                              
        return new PGgeometry(p);                                                
    }                                          

No method of the binding seems to be called at the call that fails.


Any idea what you cause this problem?


Kind regards,
Matthias

[Lukas Eder]

Hi Matthias

(nice E-Mail address ;-) )

I think you won't be able to bind this data type with a Converter only, as jOOQ doesn't know how to bind it to JDBC. You'll have to implement the org.jooq.Binding SPI. Did you do that? In case you did, could you please post that here?

Cheers,
Lukas

[m...@tthias.eu]

Hi Lukas,

El viernes, 6 de mayo de 2016, 15:40:32 (UTC+2), Lukas Eder escribió:

I think you won't be able to bind this data type with a Converter only, as jOOQ doesn't know how to bind it to JDBC. You'll have to implement the org.jooq.Binding SPI. Did you do that? In case you did, could you please post that here?

Thank you for your support. While preparing everthing to write this e-mail I found the problem: While I had the binding in my code, I accidently registered the converter in my codegenerator configuration. Therefore jOOQ did not know about the binding. Fixing my configuration to the following made everything working for me:

<customType>                                    
    <name>GeometryToPosition</name>              
    <type>eu.m2machine.xpatron.transport.Position</type>
    <binding>eu.m2machine.xpatron.db.converter.PositionBinding</binding>
    <!--                                        
    <converter>                                  
        eu.m2machine.xpatron.db.converter.PositionConverter
    </converter>                                
    -->                                          
</customType>

I must have been to focused on the code while trying to fix the problem.


Thank you very much again,
Matthias

[Lukas Eder]

Hi Matthias,

That certainly helps :)

Thanks for the update.

Lukas

--