Product/components used and version/fix level:

Software AG Designer 10.3

Detailed explanation of the problem:

Hello SAG Forum!
Please, how can I send multiple parameters to be used in a custom SQL query?

I created a custom SQL with the following query:
SELECT *
FROM
my_tbl
WHERE
my_col in ?

It only returns the rows when I add 1 param as INPUT, the field type as String and I just type “ABC” (without the quotes).

I’d like to send something as ABC, DEF, GHI as parameter to be filled in the “IN” SQL clause.

Is it possible?

Thank you in advance

Error messages / full error message screenshot / log file:

No errors.

Question related to a free trial, or to a production (customer) instance?

Production (customer)

Try something like this

SELECT *
FROM
my_tbl
WHERE
my_col in ? and my_col2 in ? and my_col3 in ?

Define my_col , my_col2 , my_col3 under Inputfields

Hello @Sandeep_Deepala
Thank your for your suggestion!
Doing as you suggested it works perfectly.

But, the system that will call the flow service might send only 1 or more parameters to be used in the query. That’s why I’m trying to use the “IN”.

Can you share sql developer version of the complete query you wanted to execute ?

SQL Developer version?

Sorry, I’m using the Service Designer with a webMethods Adapter for JDBC.

The query that I want to execute is something like this

SELECT *
FROM [table]
WHERE [column] IN ?

The difference is that I only want to replace that “?” to, for example, (‘BB’, ‘IV’, ‘JB’).

Sometimes, it can come only (‘BB’), or (‘BB’, ‘IV’)

That’s why I’d like something dynamic

Try with Dynamic sql adapter service … build your query in flow service and pass it to dynamic adapter service.

Oh! That worked perfectly!
Thank you very much @Sandeep_Deepala

@Sandeep_Deepala , please, I have just one more doubt.

I can see that I have to insert manually all the column names when using the Dynamic SQL adapter in the Output section because this button is disabled:

Please, is there anything that I can do to enable it and make the adapter Fill In all the table columns automatically? It works with Custom SQL.

Thanks again!

The accepted solution is a HUGE security risk. I am sorry to be so blunt, but it is really a terrible idea. All sorts of SQL injection are possible with this.

If I understand things correctly the actual problem is that the number of parameters is not clear upfront. I would suggest to build the “IN” part of the query in a Flow service and hand this in to the query.

@jahntech.cj you’re right!

However, in my scenario, I don’t need to worry about the security because it won’t be published to the internet. Only our company’s ERP will have access to the flow service that will prepare the query that in the end will be something like this:

SELECT *
FROM [table]
WHERE [column] IN ( ${I_MODEL} )

The ${I_MODEL} will be replaced to ‘AB’, ‘CD’, ‘DE’, ‘FG’

Often the greatest security risk is internal. I get that internal components and people are more trusted, but in this case, there is no need to open this particular security hole with the “put any SQL you want in here.” The constrained SQL you have is better-- though I still strongly recommend you do not use * but instead define the explicit columns.

You always need to consider security. Next year things may change and you may not be around to warn of the possible exposure.

Agreed. This approach is something to avoid.

You should limit the “dynamic” part as much as possible. For example:

select a, b, c, d
from foo
where x in (${list})

Don’t use * for the columns. Be explicit.

You should be able to do CustomSQL too, passing the set string as an input. E…g (‘BB’) or (‘BB’,‘IV’).

SELECT a, b, c
FROM [table]
WHERE [column] IN ?

@reamon,
I tried with the custom SQL but the records returns only if I sent ONE of the possible values for the column, and I didn’t figure it out how to send more than ONE value to be used (I tried with Array, String…)

About the security you’re right. Since the start I’ve already set a predefined SQL and I just wanted to change part of it (and not allowing to receive a full sql).

If you don’t know if you are going to receive a single element or an array, just assume it will be an array and pass your parameters as document list to your custom query.

If you don’t know the query it self but have multiple query templates, create separate queries. You can’t decide everything on the fly any way. You must know your options like, if this comes then this query, else if that comes then this query and so on. So implement this logic in flow service. Try not to do everything in one place. Split your queries. You can use dynamic queries, only if you are using a framework, like Entity Framework or Hybernate. Don’t do it yourself. In fact don’t even use custom query if you can use the other adapters.

Think about it like this. Imagine you were successful and it works like magic, and some time in the future it needs an update, and you are not around for some reason. What do you think will happen? Nobody will understand what that query does, am I right? Having multiple different purpose queries is OK. Don’t do everything in one place.

@engin_arlak , I tried with the following but it didn’t work:

image902×755 21.6 KB

image852×188 7.95 KB

image1077×131 3.46 KB

com.wm.pkg.art.error.DetailedServiceException: [ART.117.4002] Adapter Runtime (Adapter Service): Unable to invoke adapter service .adapt:TR_MCCDcustomSQL_1 with connection MY_CONN_R1.
Data Mapping Exception for field: I_SPGR. Error: null

So, I just used the flow service to create a string containg the values that will replace the ${I_MODEL} to ‘AB’,‘BB’,‘KB’ . The rest of the query it’s hardcoded.

I tried to do with the select template adapter, but unfortunately I didn’t manage to work with the IN clause.

Yes, this is the path to take. Alas, the SelectSQL template does not support the IN clause. You’ll need to use the CustomSQL template.

This is such an important point !

And it is just as important for the person who wrote the code some time ago, as for someone completely different.

When I started with Java many years ago, there was one particular instance, where I didn’t understand my own (working!) code less than 24 hours after I had written it. This is of course more of an anecdote about my lousy Java skills back then. But the principle is the same even for the most experienced programmer.

I bet that happened to every developer at some point. Not having a clue what your code does… So relatable.

The input string must be as supported by the DB. If you’re using as you posted:

SELECT a, b, c
FROM [table]
WHERE [column] IN ?

then the string to pass in must be: (‘BB’,‘IV’)

Or for one: (‘BB’)

I would suggest you place the ( ) in the query itself.

SELECT a, b, c
FROM [table]
WHERE [column] IN (?)

Then the inputs would be ‘BB’,‘IV’ or ‘BB’

You cannot input a list var of any kind for the ? value. You need to create the comma-delimited string in a way that the DB accepts. Whatever the calling system is providing, you’ll need to form the string.

Post exactly what the adapter service inputs are and the SQL tab (don’t expose any confidential info). The community will be able to help determine what to do.

@Sandeep_Deepala is asking you to use another tool, such as SQL Developer or TOAD or any other client tool, for you to work out exactly the SQL query that will work. Then we can help put the same wiithin wM Integration Server (using Designer).

Assume some computer in the internal network breached. Every client you have including internal DBs, cache servers, etc. anything outside of your server is a client and needs to be secure.