webMethods adapter dynamicSQL or customSQL

,
1

What product/components do you use and which version/fix level are you on?

webMethods 10.11

Is your question related to the free trial, or to a production (customer) instance?

Production

What are you trying to achieve? Please describe it in detail.

I have a table let’s say VEHICLES with columns ID, DATE_START, STATUS,
and want to select from that table using just one adapter but for two different scenarios.

  1. I wanna select using SELECT * FROM VEHICLE WHERE STATUS = 'some condition"
  2. I wanna select using SELECT * FROM VEHICLE WHERE STATUS = 'some condition" and DATE_START > ‘other condition’

Is there a way to acomplish this without using two separate adapters (one for each query) ?

Do you get any error messages? Please provide a full error message screenshot and log file.

Have you installed all the latest fixes for the products and systems you are using?

2

With a Custom SQL adapter, no, but with a Dynamic SQL adapter, yes. With a Dynamic SQL adapter, you can pass as much or as little of the SQL statement in as input to the adapter service. You could even pass the entire SQL statement itself if so desired. A couple of things to be aware of:

  1. A Dynamic SQL adapter will perform worse than a Custom SQL adapter because of the different Java objects that are used behind the scenes (Statement vs. PreparedStatement). The difference in performance is usually negligible.

  2. A Dynamic SQL adapter can open the door to SQL injection attacks so you have to be extra careful with validating and managing your pipeline variables that are used to build the SQL statement.

In your particular case, because you only have two different scenarios, I would likely stick with two separate adapter services that use either the Custom SQL or Select SQL template. It avoids the two issues I mentioned above and I think it makes your services a little more readable.

Hope this helps,
Percio

2 Likes
3

You can also use the custom SQL adapter and craft the statement in such a way that it always does what you want.

E.g. you can pass NULL if no date check is needed and then write (:date is null) or (mydate > :date).

4

True but that assumes that you want to include the date check in the WHERE clause every time the date variable is not null, which may or may not be true. You also have to map the date variable multiple times in the input of the service, which is not necessarily a big deal if you’re only doing this for one or two variables, but it can get annoying with multiple.

Percio

5

We avoid this by writing a small SQL script as the SQL statement of the adapter. In the script, we declare a SQL variable and assign the passed value to it. After that, we can use the variable multiple times. At the service call level, we only map it once.

I don’t like dynamic SQL because of theoretical possibility of SQL injection. I don’t want to even think about that.

1 Like
6

This topic was automatically closed 180 days after the last reply. New replies are no longer allowed.