JDBC Adapter update

, ,
1

I have an update JDBC adapter service that updates say field A and Field B where field C

In the database A & B are mandatory but in the input to my service sometimes only A or B are populated. When this happens I get exception cannot update (A or B) to null

What I would hope for is that if only A has data A is updated (ignoring B) and if only B has data only B is updated (ignoring A)

Is there something I can do to get around the current behaviour?

I hope not to have to create multiple adapter update services and surrounding flow logic e.g. If A call update A, If B call update B, If A&B; update A&B; because the real table i’m talking about is 20 or so fields where this condition could occur.

(It’s Oracle 10g, JDBC 6.0.3 and IS 6.5)

P.s. I can’t beleive I haven’t come accross this before…

2

Did you try dynamic SQL…

UPDATE TABLE_NAME_HERE SET ${inputCondition} WHERE C=?

IF A Exists and B doesn’t exist THEN
inputCondition = ‘A=%A’s Value%’
ELSE IF A Doesn’t Exist and B exists THEN
inputCondition = ‘B=%B’s Value%’
ELSE IF BOTH exists THEN
inputCondition = '‘A=%A’s Value%, B=%B’s Value%’
END IF

3

You could write a stored procedure to handle the logic. Then call the stored procedure from an adapter service. There really has to logic somewhere to handle this. Either in flow to create the dynamic SQL or in a stored procedure to determine which variables have been passed. Where do you want to do the work?

Thanks,
Steve

4

Yep I’ve done 3 different scenarios to handle this,

  1. Flow logic
  2. Dynamic SQL
  3. Select the row from the table, conditionally map either from input or from the selected row based on whether the field in the input is null

(An SP was considered but it’s an app database that we don’t want to change - long story)

Dynamic SQL is OK but gets messy building the {inputCondition} variable when you have a lot of fields that may / may not be null

3 seems the easiest in terms of less actual code. One extra DB hit is not a problem in this case. I was hoping for some ability in the adapter to optionally use or ignore null inputs.

P.S. thinking it through the behaviour I’m looking for would have to be optional and by default it’s probably best the adapter works the way it does. Imagine if you had to update a name with fields First Middle Last from say

Rob The King

to

Joe (Null) King

If by default the adapter ignored the null input you would get

Joe The King

This wouldn’t make sense.