webMethods

Hi Kailash,

please refer to the JDBC Adapter Users Guide for further informations regarding the available template types.

I think, that you are looking at the wrong templates here. Most likely you will have to use an ExecuteSQL template here, but be careful while assigning the inputs to avoid SQL injection issues towards your database. Even the CustomSQL/DynamicSQL templates not error-prone related to this.

Please described verbosely, what the funtion is doing and why you think, that it should be possible to use it as a source for a "Select from" statement, where the function call would be placed after the from part, followed by the WHERE-clause describing the condition to be matched.

Please note that this is not related to the database type being used, but is a more generic point how the JDBC Adapter is designed to work.

Regards,
Holger

Hi @Holger von Thomsen,

Thanks for your response and insights on this.

Currently the SelectSQL Adapter is Selecting data from a VIEW but the performance for the View is not satisfactory.

So, the DB Team have come up with the FUNCTION which we are asked to invoke instead.

So, once the FUNCTION was created we thought we could choose the same from the list in the SelectSQL as we could with all other Tables and Views in the Schema.

P1 and P2 are Inputs passed by webMethods in the SelectSQL Adapter call

Currently 

SELECT COLUMN1, COLUMN2

FROM [Schema].[VIEW] (@P1, @P2);

New

SELECT COLUMN1, COLUMN2

FROM [Schema].[FUNCTION] (@P1, @P2);

Also, do we have List which mentions the Possible Types supported? So far I have seen Type=TABLE, Type=VIEW only... so is TYPE=FUNCTION is it possible/permissible?

Hi Kailash,

I don´t think that this will work as this is not specified in the SQL Syntax at all.
More likely you should analyse why selecting from the table or view is not performant enough for your needs.
I would be interested how the function is implemented.

Is the view a normal view or a materialized view?

Eventually you will have to add some indices to the columns identified by P1 and P2 to make the select statement faster.

Regards,
Holger

I think this boils down to the fundamental concepts of what these objects are on database end. For this situation I believe you have to use either a Dynamic\Custom\Execute SQL template to run your function.

Thanks @Holger von Thomsen and @Akshith Arremreddy for your responses to this.

Regarding the View will try to get more information from DB team.

Yes, we are going to try to use CustomSQL and DynamicSQL templates and see if it works to invoke the DB function. Shall update here how it goes.

Also, is the Execute SQL being suggested is ExecuteService template?

Hi All,

I can confirm that using CustomSQL Template we can invoke the DB Function successfully. The DB Function Invoke should also work with DynamicSQL Template also.

As for the the ExecuteSQL template I do not see such a Template but a ExecuteService Template which I understand can we used to invoke a Flow/Java Service instead

SELECT COLUMN1, COLUMN2

FROM [Schema].[FUNCTION] ('abc','xyz')

Thanks @Holger von Thomsen and @Akshith Arremreddy for your interest and answers to this topic