View Issue Details
| ID | Project | Category | View Status | Date Submitted | Last Update |
|---|---|---|---|---|---|
| 0005832 | SymmetricDS | Bug | public | 2023-05-16 14:22 | 2023-09-13 17:25 |
| Reporter | sebaies | Assigned To | |||
| Priority | urgent | ||||
| Status | closed | Resolution | open | ||
| Product Version | 3.14.7 | ||||
| Summary | 0005832: insert error geometry field from SQLServer to PostgreSQL | ||||
| Description | doing a first load with "reload-node" I get an error on a table that contains a field of type geometry, the source db is of type sql server (provider net.sourceforge.jtds.jdbc.Driver) while the destination db is PostgreSQL (provider org.postgresql.Driver). The error I get is the following: " DefaultDatabaseWriter - Failed to process insert event in batch aimag-dbw-test-23 on channel 'reload'. Failed sql was: insert into "public"."civici" ("id", "id_via", "codecivico", "id_tipo_accesso", "note", "id_zona", "origine", "idtronco", "geometry", "rotation") values (491408,1661,'1',null,'import dati maggio',null,'181962',null,ST_GEOMFROMTEXT('787f0000010cd23ed81f316c2441dc60968b47ed5241'),null) on conflict do nothing Failed raw sql was: insert into "public"."civici" ("id", "id_via", "codecivico", "id_tipo_accesso", "note", "id_zona", "origine", "idtronco", "geometry", "rotation") values (?,?,?,?,?,?,?,?,ST_GEOMFROMTEXT(?),?) on conflict do nothing Failed sql parameters: [491408, 1661, '1', null, 'import dati maggio', null, '181962', null, '787f0000010cd23ed81f316c2441dc60968b47ed5241', null] Failed sql parameters types: [BIGINT, INTEGER, VARCHAR, INTEGER, VARCHAR, INTEGER, VARCHAR, VARCHAR, LONGVARCHAR, BIGINT] Failed sql state and code: XX000 (0) Failed row data was: "491408","1661","1",,"import dati maggio",,"181962",,,,,"787f0000010cd23ed81f316c2441dc60968b47ed5241", org.jumpmind.db.sql.SqlException: ERROR: parse error - invalid geometry Hint: "78" <-- parse error at position 2 within geometry at org.jumpmind.db.sql.AbstractSqlTemplate.translate(AbstractSqlTemplate.java:308) at org.jumpmind.db.sql.AbstractSqlTemplate.translate(AbstractSqlTemplate.java:297) at org.jumpmind.db.sql.JdbcSqlTransaction.addRow(JdbcSqlTransaction.java:443) at org.jumpmind.symmetric.io.data.writer.DefaultDatabaseWriter.execute(DefaultDatabaseWriter.java:1076) at org.jumpmind.symmetric.io.data.writer.DefaultDatabaseWriter.insert(DefaultDatabaseWriter.java:257) at org.jumpmind.symmetric.io.data.writer.AbstractDatabaseWriter.write(AbstractDatabaseWriter.java:185) at org.jumpmind.symmetric.io.data.writer.NestedDataWriter.write(NestedDataWriter.java:62) at org.jumpmind.symmetric.model.ProcessInfoDataWriter.write(ProcessInfoDataWriter.java:84) at org.jumpmind.symmetric.io.data.writer.NestedDataWriter.write(NestedDataWriter.java:62) at org.jumpmind.symmetric.io.data.writer.TransformWriter.write(TransformWriter.java:186) at org.jumpmind.symmetric.io.data.DataProcessor.forEachDataInTable(DataProcessor.java:194) at org.jumpmind.symmetric.io.data.DataProcessor.forEachTableInBatch(DataProcessor.java:160) at org.jumpmind.symmetric.io.data.DataProcessor.process(DataProcessor.java:108) at org.jumpmind.symmetric.service.impl.DataLoaderService$LoadIntoDatabaseOnArrivalListener$2.call(DataLoaderService.java:1069) at org.jumpmind.symmetric.service.impl.DataLoaderService$LoadIntoDatabaseOnArrivalListener$2.call(DataLoaderService.java:1045) at java.util.concurrent.FutureTask.run(FutureTask.java:266) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) at java.lang.Thread.run(Thread.java:748) Caused by: org.postgresql.util.PSQLException: ERROR: parse error - invalid geometry Hint: "78" <-- parse error at position 2 within geometry at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2676) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2366) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:356) at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:496) at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:413) at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:190) at org.postgresql.jdbc.PgPreparedStatement.executeUpdate(PgPreparedStatement.java:152) at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:136) at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:136) at org.jumpmind.db.sql.JdbcSqlTransaction.executePreparedUpdate(JdbcSqlTransaction.java:462) at org.jumpmind.db.sql.JdbcSqlTransaction.addRow(JdbcSqlTransaction.java:440) ... 16 more " analyzing the query it's trying to execute the problem is that it tries to make ST_GEOMFROMTEXT not of the WKT (STAsText on sql server) but of the bytes. What should be done in these cases? Thank you | ||||
| Steps To Reproduce | create a table in both dbs with a geometry column and try to force reload-node to copy data from source to target | ||||
| Tags | No tags attached. | ||||