Failed to copy data from sqlserver to postgres

[viviakemik]

Hi,

I'm doing some tests to use debezium-server-jdbc and when trying to copy data from sqlserver to postgres the following error occurs:

'{"version":"1.9.3.Final","connector":"sqlserver","name":"localhost","ts_ms":1670434961413,"snapshot":"true","db":"ab_prototipo","sequence":null,"schema":"dbo","table":"MOVTO","change_lsn":null,"commit_lsn":"00000034:000001f0:0001","event_serial_no":null}', 'dbo', NULL) was aborted: **ERROR: column "databasename" of relation "cdc_localhost"** does not exist Position: 29 Call getNextException to see other errors in the batch. [statement:"INSERT INTO cdc_localhost (databaseName , tableChanges , source , schemaName , ddl ) VALUES (:databaseName, :tableChanges, :source, :schemaName, :ddl)", arguments:{positional:{}, named:{databaseName:ab_prototipo,tableChanges:[{type=CREATE, id="ab_prototipo"."dbo"."MOVTO", table={defaultCharsetName=null, primaryKeyColumnNames=[CODEMPRESA, DATAMOVTO, NROMOVIMENTO],

Is it possible to use in this case (sqlserver as source -> postgres as sink)?
Because I believe it's just the lack of quotes when using the column that has camel case (in the case of postgres)
I looked here and it would be necessary to change the code where it is used (as this is not configurable in postgres). Am I correct?

Thanks!

[ismailsimsek]

@viviakemik sql statement seems correct. the error is about missing column databasename in postgresql(destination) table cdc_localhost . this table seems like database history topic.

could you try to run it with following setting?

debezium.source.database.history=io.debezium.relational.history.MemoryDatabaseHistory

or this

debezium.source.database.history=io.debezium.relational.history.FileDatabaseHistory
debezium.source.database.history.file.filename=data/databasehistory.txt

[viviakemik]

@ismailsimsek thanks for the reply!

the cdc_localhost table is created by debezium and the databaseName column exists, but with camel case. It creates that way in postgres, but when inserting it, it doesn't find it because it looks for databasename (I believe it should be "databaseName" with quotes). Am I correct?

Thanks again

[ismailsimsek]

you are right, seems like PG is case sensitive. preparedInsertStatement method should use quote around the column names. same for preparedDeleteStatement too

[ismailsimsek]

@viviakemik could you try new release with setting debezium.sink.jdbc.identifier-quote-char=". this should quote column names and work

[viviakemik]

@ismailsimsek thanks!
I'll do that and as soon as I have the results I'll let you know

[viviakemik]

@ismailsimsek I'm trying to test the new version but the following error occurs: Connector completed: success = 'false', message = 'Error while trying to run connector class 'io.debezium.connector.sqlserver.SqlServerConnector'', error = '{}'

When I go back to the previous version, this error does not occur in the sql server as a source (I am using the same settings, only with the addition of debezium.sink.jdbc.identifier-quote-char="

Do you have any idea what might be going on?
thanks again

[viviakemik]

@ismailsimsek The test (sql server to postgres) now worked, but I had to revert the quarkus version to 2.8.2 (I don't know the exact reason, but in versions 2.13.2 and 2.14.0 the error described in my last comment occurs)