jdbc producer insert to oracle db error jdbc_23

asked 2019-11-25 16:22:41 -0600

weicui gravatar image

Hi,

I created a simple pipeline: read from csv file and write to oracle database.

The destination table structure is:

CREATE TABLE "NETWORK_DEVICE_DIM" (
"TAC_CD" VARCHAR2(16 CHAR) NOT NULL ENABLE, "MKT_NM" VARCHAR2(4000 CHAR), "MNFCT_NM" VARCHAR2(4000 CHAR), "FREQ_BND" VARCHAR2(4000 CHAR), "RADIO_INTF" VARCHAR2(64 CHAR), "BRND_NM" VARCHAR2(256 CHAR), "MDL_NM" VARCHAR2(512 CHAR), "OS_NM" VARCHAR2(128 CHAR), "NFC" VARCHAR2(256 CHAR), "BLTH" VARCHAR2(256 CHAR), "WLAN" VARCHAR2(256 CHAR), "DVC_TYP" VARCHAR2(256 CHAR), "SOURCE" VARCHAR2(64 CHAR), "LAST_UPDATE_TS" TIMESTAMP (6) )

I created the jdbc producer and the mapping.

the mapping: [ { "paramValue": "", "dataType": "STRING", "columnName": "TAC_CD", "field": "/TAC" }, { "paramValue": "", "dataType": "STRING", "columnName": "MKT_NM", "field": "/'Marketing Name'" }, { "paramValue": "", "dataType": "STRING", "columnName": "MNFCT_NM", "field": "/'Manufacturer (or) Applicant'" }, { "paramValue": "", "dataType": "STRING", "columnName": "FREQ_BND", "field": "/Bands" }, { "paramValue": "", "dataType": "STRING", "columnName": "RADIO_INTF", "field": "/'Radio Interface'" }, { "paramValue": "", "dataType": "STRING", "columnName": "BRND_NM", "field": "/'Brand Name'" }, { "paramValue": "", "dataType": "STRING", "columnName": "MDL_NM", "field": "/'Model Name'" }, { "paramValue": "", "dataType": "STRING", "columnName": "OS_NM", "field": "/'Operating System'" }, { "paramValue": "", "dataType": "STRING", "columnName": "NFC", "field": "/NFC" }, { "paramValue": "", "dataType": "STRING", "columnName": "BLTH", "field": "/Bluetooth" }, { "paramValue": "", "dataType": "STRING", "columnName": "WLAN", "field": "/WLAN" }, { "paramValue": "", "dataType": "STRING", "columnName": "DVC_TYP", "field": "/'Device Type'" }, { "paramValue": "", "dataType": "DATETIME", "columnName": "LAST_UPDATE_TS", "defaultValue": "systimestamp" } ]

the data set after reading the file, before sending to jdbc producer:

0 TAC : STRING 00100100 1 'Marketing Name' : STRING G410 2 'Manufacturer (or) Applicant' : STRING Mitsubishi 3 Bands : STRING GSM 1800,GSM 900 4 '5G Bands' : STRING Not Known 5 LPWAN : STRING Not Known 6 'Radio Interface' : STRING NONE 7 'Brand Name' : STRING Not Known 8 'Model Name' : STRING G410 9 'Operating System' : STRING Not Known 10 NFC : STRING Not Known 11 Bluetooth : STRING Not Known 12 WLAN : STRING Not Known 13 'Device Type' : STRING Handheld 14 'Removable UICC' : STRING Not Known 15 'Removable EUICC' : STRING Not Known 16 'NonRemovable UICC' : STRING Not Known 17 'NonRemovable EUICC' : STRING Not Known 18 Simslot : STRING Not Known 19 Imeiquantitysupport : STRING Not Known

The error message: com.streamsets.pipeline.api.base.OnRecordErrorException: JDBC_23 - Can't coerce 'Not Known' of type 'STRING' to column 'BLTH' at com.streamsets.pipeline.lib.jdbc.JdbcBaseRecordWriter.setParamsToStatement(JdbcBaseRecordWriter.java:638)

I don't understand why it complains the BLTH column, which is not the first one or last one with 'Not Known' value.

After I deleted the other mappings fields and only kept the TAC_CD for testing purpose, the stack trace is:

JDBC_23 - Can't coerce 'Not Known' of type 'STRING' to column 'WLAN' com.streamsets.pipeline.api.base.OnRecordErrorException: JDBC_23 - Can't coerce 'Not Known' of type 'STRING' to column 'WLAN' at com.streamsets.pipeline.lib.jdbc.JdbcBaseRecordWriter.setParamsToStatement(JdbcBaseRecordWriter.java:638) at com.streamsets.pipeline.lib.jdbc.JdbcGenericRecordWriter.setParameters(JdbcGenericRecordWriter.java:215) at com.streamsets.pipeline.lib.jdbc.JdbcGenericRecordWriter.write(JdbcGenericRecordWriter.java:145) at com.streamsets.pipeline.lib.jdbc.JdbcGenericRecordWriter.writeBatch(JdbcGenericRecordWriter.java:91) at com.streamsets.pipeline.lib.jdbc.JdbcUtil.write(JdbcUtil.java:897)

why is it complaing the "WLAN", which is not even in the mapping?

Can someone help me on this? I appreciated.

Thanks,

Wei

edit retag flag offensive close merge delete