We want to add an extra where clause to a datasource query using the Pre-Query field in the datasource definition. We therefore created an action that uses API_DATASOURCE.setfilterwhereclause.

In the debug monitor we can see that the filter where clause is set, but it has no effect on the following query. Only when we do 'execute query' for a second time, the filter where clause is applied.

Is the Pre-Query event not the right moment to set a filter where clause?

asked 23 Nov '12, 01:29

Peter%20W's gravatar image

Peter W
accept rate: 0%

Hi Peter, we are looking into it. We'll get back to you shortly.

(23 Nov '12, 01:33) Yalim K. Gerger ♦♦

Could you tell us what your use case is? In most cases adding a bind variable to the query is enough. What made you choose this API instead of adding a bind variable to the query?

(23 Nov '12, 01:35) Yalim K. Gerger ♦♦

Yalim, we want to be able to filter a datasource using a variable where clause, with multiple varying columns, that depends on the (external web) user that is querying the datasource. If we would use bindvars for this purpose, we would have to create bindvars for all columns of the datasource, which is not a preferable situation.

(23 Nov '12, 01:53) Peter W

Hi Peter,

OK. Now I understand. We are adding a new API called setQuery in version 1.2 which is exactly for this use case. Using this API you will be able to change the entire SQL of your datasoruce, based on the information provided by the user. So you could update your where clause easily using this API and keep the rest of the query the same. (Obviously, column names and their order should never change.)

setFilterWhereClause API is not ideal for your use case. If you use setFilterAPI, the original query will be executed and all the rows will be retrieved to the datasource. But only rows that pass through the filter will show up on the screen. There are rare use cases where this is desired behavior. But in general, this API should be used as a last resort.

As a side note: There seems to be a bug in Pre-Query which prevents the filter being applied. If you use Post-Query trigger it should all work.

So my suggestion is: Continue using setFilterWhereClause in the Post-Query trigger for now. Replace the whole thing with the setQuery API after you install version 1.2. Or if you can just wait a couple of weeks until 1.2 is out.

Does this make sense?


answered 23 Nov '12, 02:06

Yalim%20K.%20Gerger's gravatar image

Yalim K. Gerger ♦♦
accept rate: 20%

edited 23 Nov '12, 02:07

Hi Peter,

Since version 1.2, you can use api_datasource.setDefaultQuery API to achieve your request.

Regards, Ibrahim

(21 Dec '12, 10:36) Ibrahim Sand... ♦♦

Hi Ibrahim,

Setting api_datasource.setDefaultQuery in pre-query trigger does not work in 1.2.1. It has the same problem as described here. You can set it, but it is not picked up by the query. Requerying the datasource a second time picks up the set defaultQuery.. I had sent an email to Yalim about this, he forwarded it to Samet and he was going to look into it.

Best, Michiel

(21 Dec '12, 11:56) Michiel A

Hi Michiel,

Since version 1.3.0, this problem is fixed.


(08 Apr '13, 09:57) Ibrahim Sand... ♦♦

I don't know the case you are trying to solve, but it seems that a function returning rows using "pipe row" can solve your problem. Inside this function you can use dynamic sql and ref cursors. I have built many generic oracle reports using this technique.


answered 24 Nov '12, 12:02

Vlisidis%20Akis's gravatar image

Vlisidis Akis
accept rate: 5%

Yes, this method would work, too,

(24 Nov '12, 12:05) Yalim K. Gerger ♦♦
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported



Asked: 23 Nov '12, 01:29

Seen: 1,260 times

Last updated: 08 Apr '13, 09:57

© Copyright Gerger 2017