dynamic where with bind variables

Question

Hi Team,

Is there a way that you can set the where clause during runtime with a bind variable. It seems I can only use setfilterwhereclause with static values

I was hoping to do something like

api_datasource.setfilterwhereclause('Notes', 'customer_id = :customer_id);
api_dataspurce.setbindvar('Notes.customer_id',v_contact_id);
api_datasource.executequery('Notes');

Alas that does not work. I don't want to use static values as it will just flood the SGA with dumb statements etc.

I guess the work around is to use a more elaborate where clause defined in the datasource.

Answer 1

1

Hi Raymond,

To achieve what you want, you can use api_datasource.setDefaultQuery API to set the entire SQL of your Datasource.

We are working on a new API that will allow you to do what you have in mind and then some. It will be available in a 1.8.X version that you will receive in the next couple of months as a pro support customer.

Kind Regards,
Yalim

link

answered 04 Aug '14, 09:13

Yalim Gerger ♦♦
1.8k●5
accept rate: 15%

Thanks Yalim. That will do for now. Looking forward to new features.

(04 Aug '14, 09:57) Raymond

Answer 2

Hi Yalim,

I guess the new API is the dynamic where clauses functionality in version 1.9 :)

I have studied the tutorial http://theformspider.com/learningcenter/dynamic-where-clauses/ but have problems in using it. Can you explain this a little bit more?

I have done this:

1) Create a datasource
2) Added 10 bind variables on page "Bind variables"
3) Create "New Simple Criterions" for each bindvariable. So i have 10 statements like COLUMN_NAME = :bindVariable.
4) Create a control block to enter all 10 search criteria and a button to execute the search.
5) Create a procedure to do the search. It captures the 10 search criteria and stores it in local variables.

And now i have to add the logic to this procedure to make it work... Can you give me an example how to implement api_datasource.setWhereClause with all declarations of the variables?

Kind regards, Jan Willem Vermeer

Answer 3

0	Thanks for your advice Yalim. A perfect example was in the end of this tutorial: http://theformspider.com/learningcenter/search-panel-wizard/ I have my form completely working now. KR JW link answered 30 Sep '15, 07:12 Jan Willem V... 123●1●4●34 accept rate: 0%

dynamic where with bind variables

Follow this question

Related questions