wM JDBC Adapter and PreparedStatements

, ,
1

Two questions regarding wM JDBC adapter:

  1. I want to use a adapter service that internally will use PreparedStatement. Does CustomSQL or DynamicSQL internally use PreparedStatements? Is there something in configuration that I have to specify apart from the placeholder (? in the query) and specifying the input/output parameters?

  2. I created a simple DynamicSQL query with 5 input parameters. Query is something like “select * from sample where id in (?, ?, ?, ?, ?)”.
    Have bound the input param’s to names like input1, input2 etc. The actual values of these are retrieved from calling another sevice in a previous step. When I look at the pipleline to bind the design-time variables, the source id’s are in an Array. But the destination (i.e. ServiceIn.DynamicSql_ServiceName.input1, input2 etc.) input variables are not in an array. Is there any way, I can get the placeholders to show up as arrays so I can loop through the source and map each source input into the destination input? Else I have to branch on $iteration inside the loop, check if $iteration == 1, then map first variable of input array to input1 and so on. Not a very elegant solution.

Responses would be much appreciated.

Thanks.

2

Based on your question2 is your source loop/fields (arrays) always comes with couple of iterations or just 1 iteration of data??If it is just with one iteration then you can map your source loop/fields directly to dynamic sql inputs and specify index[0] in the map/properties instead of branch on %$iteration% == 1…

HTH,
RMG

3

Thanks RMG.

The source is an array of 5 id’s
example, sourceId = {10, 20, 30, 40, 50}

Unless, I am misunderstanding your comments, I will have 5 iterations of the source array.

The destination by default from DynamicSQL Adapter service is an object with 5 properties:
FetchSummaryFromDB
- input1
- input2
- input3
- input4
- input5

It would have been nice if the inputs for the Adapter Service show up as arrays as well (instead of distinct sub-elements or properties) so
source->destination mapping could have been done in one map step within a loop.

Do you know whether it internally uses PreparedStatements?

Thanks.

4

“example, sourceId = {10, 20, 30, 40, 50}
Unless, I am misunderstanding your comments, I will have 5 iterations of the source array”
–If this is the case when you know it will come with 5 iterations then when you map to dynamicAS inputs then specify in the map properties sourceid[0] to input1,[1] to input2 …[5] to input5…this can be done in one map step…so that you can avoid branching step…you agree?? Pardon me if this gets confuses you…

HTH,
RMG

5

Yes, I agree that I can avoid using branch statement and map all 5 elements of the source to dynamicAS inputs. I am new to webMethods so I don’t know exactly how to specify sourceid[0] in the pipleline mapping (I am used to drawing lines in the pipeline GUI w/out specifying array indicies). But your comments do point me in the right direction. Thanks for your help.

6

No problem,Hope you got the indices thing specified now in the map step…thanks.

HTH
RMG

7

Yes, Thanks RMG. It was in the Indicies Property of the Map Properties. Thanks for helping with this.