Ask Your Question
1

JDBC producer inserts OK but how does it update a record

asked 2019-02-25 07:23:08 -0500

rleyba gravatar image

updated 2019-02-25 11:48:17 -0500

metadaddy gravatar image

Hi,

I am currently using the JDBC Producer stage to insert new records in my mysql databases. This works fine without issues. I now need to UPDATE some of the records with something like:

UPDATE db.table SET login_date=${(record:value("/myfield")}  WHERE prikey=${(record:value("/key")}

But there doesn't seem to be any input box where i can put in a query like the one above.

Also, I looked at the JDBC lookup stage but it seems that it is only for querying, not writing.

What stages do I need in order to DB updates like this?

thank you.

edit retag flag offensive close merge delete

1 Answer

Sort by ยป oldest newest most voted
0

answered 2019-02-25 11:48:03 -0500

metadaddy gravatar image

See the documentation on setting the CRUD operation in the record. You need to set the sdc.operation.type to 1 for INSERT or 3 for UPDATE, and ensure that the record field corresponding to the table's primary key is set; the JDBC Producer destination will automatically generate an update statement of the form you described.

edit flag offensive delete link more

Comments

Hi Pat, Thanks for this. I might have missed that section of the link where it says there is an implicit "WHERE" clause in the stage. The example given in that section was quite useful.

rleyba gravatar imagerleyba ( 2019-02-25 18:27:57 -0500 )edit

Hi Pat, a follow up question. For the non-CRUD enabled source, do we need to have a jdbc lookup to determine if the record already exists and then set the operation type? I am currently finding the existence of the record by performing jdbc lookup. Is there any way to set default operation as merge?

Anuradha gravatar imageAnuradha ( 2019-06-06 07:59:58 -0500 )edit

Hi @Anuradha - please ask this as a new question. Also, please accept and vote up this answer if it helped solve your problem.

metadaddy gravatar imagemetadaddy ( 2019-06-06 12:29:45 -0500 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2019-02-25 07:23:08 -0500

Seen: 311 times

Last updated: Feb 25