It does not. I played around with it a little bit more and discovered that
it does work if I use the following to create the BatchBindStep.
BatchBindStep bindStep = DSL.using(configuration).batch(
DSL.using(configuration).insertInto(TABLE_NAME, TABLE.COL1, TABLE.COL2,
TABLE.GEOM, TABLE.COL4
).values((Integer) null, null, DSL.field(DSL.sql("ST_GeomFromGeoJSON(?)"
, "")), null)
);
On Thursday, March 17, 2016 at 11:12:33 AM UTC-4, Lukas Eder wrote:
>
> Does your code work if instead of writing
>
> DSL.field(DSL.sql("ST_GeomFromGeoJSON(?)", new ObjectMapper().
> writeValueAsString(data.getGeom())))
>
> You write
>
> DSL.field(DSL.sql("ST_GeomFromGeoJSON(?::text)", new ObjectMapper().
> writeValueAsString(data.getGeom())))
>
> ?
>
> 2016-03-17 0:43 GMT+01:00 <[email protected] <javascript:>>:
>
>> Hi,
>>
>> I'm trying to write a batch insert statement using jOOQ. I have two
>> versions, one that inserts a single piece of data, and one that does a
>> batch insert with a list of data. The code inserting a single item works,
>> and the code doing the batch insert doesn't. I'm trying to use the PostGIS
>> ST_GeomFromGeoJSON function to insert geojson into a Postgres database.
>>
>> create.transaction(configuration -> {
>> DSL.using(configuration).insertInto(TABLE_NAME, TABLE.COL1, TABLE.
>> COL2, TABLE.GEOM, TABLE.COL4)
>> .values(
>> data.getCol1(),
>> data.getCol2(),
>> DSL.field(DSL.sql("ST_GeomFromGeoJSON(?)", new ObjectMapper
>> ().writeValueAsString(data.getGeom()))),
>> data.getCol4())
>> .execute()
>> });
>>
>> This code creates a log line:
>>
>> 19:12:44.218 [main] DEBUG org.jooq.tools.LoggerListener - Executing
>> query : insert into "public"."table_name" ("col1", "col2",
>> "geom", "col4") values (?, cast(? as timestamp), ST_GeomFromGeoJSON(?),
>> ?::jsonb)
>> 19:12:44.219 [main] DEBUG org.jooq.tools.LoggerListener - -> with bind
>> values : insert into "public"."table_name" ("col1", "col2", "geom",
>> "col4") values (13, timestamp 'timestamp', ST_GeomFromGeoJSON(
>> '{"type":"Point","coordinates":[5.0,10.0]}'), 'random json'::jsonb)
>>
>> Trying to do this with a list of data elements I have:
>>
>> create.transaction(configuration -> {
>> BatchBindStep bindStep = DSL.using(configuration).batch(
>> DSL.using(configuration).insertInto(TABLE_NAME, TABLE.COL1, TABLE
>> .COL2, TABLE.GEOM, TABLE.COL4
>> ).values((Integer) null, null, null, null)
>> );
>> for (Data d : dataList) {
>> bindStep.bind(
>> d.getCol1(),
>> d.getCol2(),
>> DSL.field(DSL.sql("ST_GeomFromGeoJSON(?)", new ObjectMapper
>> ().writeValueAsString(data.getGeom()))),
>> d.getCol4()
>> );
>> }
>> try {
>> bindStep.execute();
>> } catch (DataAccessException ex) {
>> // Log ex, and getNextException
>> }
>> });
>>
>> This yields an error:
>>
>> 9:13:54.933 [main] ERROR t.b.p.s.Service - Error while executing SQL.
>> org.jooq.exception.DataAccessException: SQL [insert into
>> "public"."table_name" ("col1", "col2", "geom", "col4") values (?, cast(? as
>> timestamp), cast(? as varchar), cast(? as varchar)::jsonb)]; Batch entry 0
>> insert into "public"."table_name" ("col1", "col2", "geom", "col4") values
>> ('14', cast('timestamp' as timestamp),
>> cast('{"type":"Point","coordinates":[0.0,0.0]}' as varchar), cast('random
>> json' as varchar)::jsonb) was aborted. Call getNextException to see the
>> cause.
>> Caused by: java.sql.BatchUpdateException: Batch entry 0 insert into
>> "public"."table_name" ("col1", "col2", "geom", "col4") values ('14',
>> cast('timestamp' as timestamp),
>> cast('{"type":"Point","coordinates":[0.0,0.0]}' as varchar), cast('random
>> json' as varchar)::jsonb) was aborted. Call getNextException to see the
>> cause.
>> 19:13:54.935 [main] ERROR t.b.p.s.Service - Next exception:
>> org.postgresql.util.PSQLException: ERROR: column "geom" is of type
>> geometry but expression is of type character varying
>> Hint: You will need to rewrite or cast the expression.
>>
>> When I have the statement batched, jOOQ doesn't seem to be inserting the
>> SQL to use ST_GeomFromGeoJSON. I don't understand why. Help would be
>> greatly appreciated!
>>
>> --
>> 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] <javascript:>.
>> For more options, visit https://groups.google.com/d/optout.
>>
>
>
--
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/d/optout.
