Skip to main content
Question

Facing issue JDBC Query

Pipeline Status: RUN_ERROR: QUERY_EXECUTOR_001 - Failed to execute query 'UPDATE user SET  created_by='HistoricalLoad', created_date="2020-04-27 15:11:25", modified_by='', modified_date="" WHERE id = '71021903' LIMIT 1': Data truncation: Incorrect datetime value: '' for column 'modified_date' at row 1

 

UPDATE user SET
created_by='${record:value('/created_by')}',
created_date="${str:isNullOrEmpty(record:value('/created_date'))?NULL: record:value('/created_date')}",
modified_by='${str:isNullOrEmpty(record:value("/modified_by"))?NULL:record:value("/modified_by")}',
modified_date="${str:isNullOrEmpty(record:value('/modified_date'))?NULL: record:value('/modified_date')}"
 WHERE id = '${record:value('/id')}' LIMIT 1

17 replies

Bikram
Rank
Userlevel 5
Badge +1

@satish 

Can you please try with the below updated query.

 

UPDATE user SET
created_by='${record:value('/created_by')}',
created_date=’${str:isNullOrEmpty(record:value('/created_date'))?NULL: record:value('/created_date')}’,
modified_by='${str:isNullOrEmpty(record:value("/modified_by"))?NULL:record:value("/modified_by")}',
modified_date=’${str:isNullOrEmpty(record:value('/modified_date'))?NULL: record:value('/modified_date')}’
 WHERE id = '${record:value('/id')}' LIMIT 1

S
Rank

@Bikram

 

  In modified_date its passing as empty value.

 

created_date=`2020-04-27 15:11:25`, modified_by='', modified_date=``

Bikram
Rank
Userlevel 5
Badge +1

@satish 

May i know if you want to pass NULL as a string in it .

S
Rank

@Bikram 

Actually Datatype is datetime Need to accept null or value.

 

Bikram
Rank
Userlevel 5
Badge +1

@satish 

We can’t pass null as a string in datetime field , it should be NULL as i mentioned before.

 

Bikram
Rank
Userlevel 5
Badge +1

 

S
Rank

@Bikram 

Is it possible to share pipeline  

Bikram
Rank
Userlevel 5
Badge +1

@satish 

 

Attached the pipeline for your reference.

1 Attachment
S
Rank

@Bikram 

 

Still i am facing issue i am attaching the sample pipeline and screenshot of table structure

3 Attachments
Bikram
Rank
Userlevel 5
Badge +1

@satish 

May I know what is the DB is being used for your use case.

S
Rank

@Bikram 

MySQL

Bikram
Rank
Userlevel 5
Badge +1

@satish 

 

The query looks fine  ,May I know the error after previewing the pipleine.

 

S
Rank

@Bikram 

Pipeline Status: RUN_ERROR: QUERY_EXECUTOR_001 - Failed to execute query 'INSERT INTO staging.check2 ( uniqueid, name,lname, age, createdon, modifiedon, amount) VALUES ('1', 'Satish', 'NULL', NULL, '2023-04-11 00:00:00', 'NULL', NULL)': Data truncation: Incorrect datetime value: 'NULL' for column 'modifiedon' at row 1

S
Rank

@Bikram Problem got solved. Thanks for your support.

Bikram
Rank
Userlevel 5
Badge +1

@satish 

May I know what was the issue.

S
Rank

 @Bikram

Query modified as below.


INSERT INTO staging.check2 ( uniqueid, name,lname, age, createdon, modifiedon, amount) VALUES ('${record:value('/uniqueid')}',
'${str:isNullOrEmpty(record:value('/name'))?'NULL':record:value('/name')}',
'${str:isNullOrEmpty(record:value('/lname'))?'NULL':record:value('/lname')}',
${str:isNullOrEmpty(record:value('/age'))?'NULL': record:value('/age')},
${str:isNullOrEmpty(record:value('/createdon')) ?"NULL": str:concat(str:concat("'", record:value("/createdon")), "'")},
${str:isNullOrEmpty(record:value('/modifiedon')) ?"NULL": str:concat(str:concat("'", record:value("/modifiedon")), "'")},
${str:isNullOrEmpty(record:value('/amount'))?'NULL': record:value('/amount')})




UPDATE staging.check2 SET uniqueid='${record:value('/uniqueid')}',
name='${str:isNullOrEmpty(record:value("/name"))?"NULL":record:value("/name")}',
age=${str:isNullOrEmpty(record:value('/age')) ?"NULL": str:concat(str:concat("'", record:value("/age")), "'")} ,
createdon=${str:isNullOrEmpty(record:value('/createdon')) ?"NULL": str:concat(str:concat("'", record:value("/createdon")), "'")},
modifiedon=${str:isNullOrEmpty(record:value('/modifiedon')) ?"NULL": str:concat(str:concat("'", record:value("/modifiedon")), "'")},
amount=${str:isNullOrEmpty(record:value('/amount')) ?"NULL": str:concat(str:concat("'", record:value("/amount")), "'")} 
WHERE uniqueid = '${record:value('/uniqueid')}' LIMIT 1

 

 

Bikram
Rank
Userlevel 5
Badge +1

@satish 

Thanks . Good to see you managed to get it resolved.

Reply


Terms & Conditions