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.