Ask Your Question
1

JDBC producer inserts OK but how does it update a record

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

rleyba gravatar image

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

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

2 Answers

Sort by ยป oldest newest most voted
1

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

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 -0600 )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 -0600 )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 -0600 )edit

Streamsets JDBC Prodcuer, any way to define primary keys? For every micro match it's running queries on target db to check table,for column validation and to fetch primary keys. if somehow we can hard code primary keys or if we can set to fetch db/table/column details once when pipeline starts.

Tarun Kumar gravatar imageTarun Kumar ( 2020-02-13 00:26:45 -0600 )edit
0

answered 2020-02-13 00:26:49 -0600

Tarun Kumar gravatar image

Streamsets JDBC Prodcuer, any way to define primary keys? For every micro match it's running queries on target db to check table,for column validation and to fetch primary keys. if somehow we can hard code primary keys or if we can set to fetch db/table/column details once when pipeline starts.

edit flag offensive delete link more

Comments

1

Please ask this as a new/separate question.

iamontheinet gravatar imageiamontheinet ( 2020-02-13 15:25:32 -0600 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

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

Seen: 619 times

Last updated: Feb 13