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.
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.
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.
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.
@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.
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.
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).
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.
