Solved

JDBC_23 - Can't coerce '2022-04-25 09:46:50' of type 'STRING' to column 'last_update_date(DATETIME)'

  • 25 April 2022
  • 7 replies
  • 162 views
F
Rank

my configuration:
Use Multi-Row Operation  is close
and 
Max Cache Size Per Batch (Entries) is “-1”

origin:
{
    "table": "tableName",
    "op_type": "I",
    "op_ts": "2022-04-25 09:01:56.000000",
    "current_ts": "2022-04-25T09:02:01.014000",
    "pos": "00000000020000939589",
    "after": {
       “ROW_ID”:”123456”,
        "LAST_UPD": "2022-04-25 01:01:54"
    }
}
Destinations is jdbc producer

I can not update the LAST_UPD into mysql column “last_update_date” 
please give me some advice

icon

Best answer by Bikram 26 April 2022, 04:57

View original

7 replies

F
Rank

change the column type of  ”last_update_date“  to varchar in mysql ， the date can update

Bikram
Rank
Userlevel 5
Badge +1

please change the column name LAST_UPD to last_update_date and i believe it will solve your issue.

F
Rank

by the way I have set a mapping
Field to Column Mapping:
{
        "paramValue": ?,
        "dataType": "USE_COLUMN_TYPE",
        "columnName": "last_update_date",
        "field": "/after/LAST_UPD"
    }

Bikram
Rank
Userlevel 5
Badge +1

ok , i see . I hope the data type for the column are same in both source and destination. if it’s  not then it will fail.

In this case if the last_updated_date column is string type or data time type then you should send same data type to destination JDBC producer.

F
Rank

how to change the “STRING” type  to “DATETIME” ?   
I need to change “LAST_UPD” ‘s  type to ”DATETIME“
and send it to mysql as “DATETIME’

use JavaScript Evaluator？

Bikram
Rank
Userlevel 5
Badge +1

Can you please refer below sample code and try it and let me know if it helps. 

 

https://docs.streamsets.com/portal/datacollector/latest/help/datacollector/UserGuide/Expression_Language/Functions.html#concept_qkr_trf_sw

${time:extractDateFromString('2017-05-01 20:15:30.915','yyyy-MM-dd HH:mm:ss.SSS')}
F
Rank

it work!  thank you very much

Reply


Terms & Conditions