v9: Query form with dynamic query condition

Grzegorz Lag

Hello

 

I want to know if there is some easy way to build query form with dynamic query condition. I have some workaround for this, but I hope anyone has better solution

 

Let say that we have table in database (customers) with columns

• ID
• Name
• Country
• Prefered

On form we will have:

 - textbox with name (we want to query by name using like)

 - dropdown with list of countries

 - dropdown with list of towns dependant on

 - dropdown with list (yes, no, both) if thic customer is prefered

 - grid and search button

 

On load, there should be no result on grid.

If user enter part of name, this should be added to WHERE condition.

If user select country, it should be added to WHERE condition

If user select both from prefered, there should be no WHERE condition with it

 

It would be great if query condition could refresh only on search button - not when first drop down will change (becouse it has dependants of it's own).

 

I was trying with dynamic query in stored procedure, it works, but It's not perfect

 

Greg

Jerome_Pearce

In previous versions we used to build the SQL query based on selections in the form. For example, if a country was selected, we would add teh SQL "AND Country = '%Country' to a variable , and use that variable as the 'rows' property of the grid.

 

In version 9 you can no longer do this. One alternative is to have a clause such as " AND (Country LIKE  @SelectedCountry + '%' OR @SelectedCountry = '' "

 

This works OK in SQL Server (and you need to use || instead of + in Oracle), but for some reason Oracle has issues with literal values like this and may cause a table scan.

Grzegorz Lag

Jerome I know exactly how to solve it in v7, but in v9 what you wrote is only part of solution.

 

Problem is when you have dependecies on dropdowns, and BO always refresh with parameter dependant on this variable.

 

I don't want to have grid refresh until user click search button. For example if i want to do some validations before it.

 

Paul Denton

I am not sure I understand the difference between v7 and v9 here....

 

If you had a grid on a form in v7 that "was dependent" and three dropdowns with "have dependents" enabled, then the form (and grid) would do a refill whenever you selected a new value in any one of the grids.  The same occurs in v9.

 

An option would be disabling the "has dependents" setting on the dropdowns...

 

Maybe I am not fully understanding the question?

 

EDIT:

 

Following on, if I am understanding that you don't want the grid refreshing with the other fields on the form, then perhaps you could work around this by having two instances of the Business Object on the form.  Point the grid to the second instance and only update e local variables assigned to the parameters of this second instance under your button...

 

Does that sound right?

 

EDIT 2:

 

Yeah.. What Jerome said...

Jerome_Pearce

Ah, I see what you mean, sorry.

You obviously cannot stop the grid refreshing but you want to stop the query changing on the refresh. The only way I can see is to have another set of variables as the parameters, and setting these, and doing any required validations, on the button click.

Iain_Lindsay

Hi

 

I achieve this kind of thing using a Query Business Object and a WHERE clause containing different CASE statements.

 

So the query would look something like this:

 

SELECT 
 * 
FROM 
 eFolder 
WHERE 
 -- only check folder name if it is provided
 eFolderName = 
  CASE 
   WHEN ISNULL(@folderName,'') = '' 
   THEN eFolderName 
   ELSE @folderName 
  END
AND
 -- do a like against the specified subject
 ISNULL(eSubject,'') LIKE @subjectLike
AND
 -- only check creation time if we have both a from and to date
 eCreationTime 
 BETWEEN 
  CASE 
   WHEN ISNULL(@creationStart,'') = '' OR ISNULL(@creationEnd,'') = '' 
   THEN eCreationTime 
   ELSE @creationStart 
  END
 AND 
  CASE 
   WHEN ISNULL(@creationStart,'') = '' OR ISNULL(@creationEnd,'') = '' 
   THEN eCreationTime 
   ELSE @creationEnd 
  END

 

and when I assign values to the MBO parameters on the form I make sure my @subjectLike parameter value is something like this:

 

string.Format("%{0}%", MyProcessData.MySubjectLikeVariable)

The query syntax is valid for SQL Server, not sure about Oracle though.

 

Hope that helps

 

Iain

Grzegorz Lag

Jerome

 

You have right with this variables scenerio. I was doing it the same way. But, as always, e-workaround

 

About query, I was trying the same syntax as Lain, but with many coditions it's quite strange to maintenace my idea was creating stored procedure and build dynamic query string and sp_executesql. I'm not sure which solution is faster. But this one with stored procedure has one problem. With dynamic select statment in stored procedure Designer have problems with discovery columns. You have to do dummy procedure first. Create BO and then update stored procedure.

Another e-workaround:(

Kevin Manuel

Here is the technique I have found to tie a Query type of Business Object to a dynamic WHERE statement:

 

1. Create a Query type Business Object with a parameter of @WHERESTATEMENT (or whatever parameter name you like)

 

2. Create the SQL with your query and put it in a format that looks like this:

EXEC('SELECT * FROM eUser ' + @WHERESTATEMENT)  

**make sure you put the space after the last SQL command so that when it concatenates with your variable value the statement will look like 'SELECT * FROM eUser WHERE ...'

 

3. Set your remainging business object values such as variable names and the default parameter value.

    If you leave the default parameter value empty the Business Object will return all records if the Parameter is not set otherwise if you put in a default WHERE statement it will default to this select filter.

 

4. Create a LOCAL variable for your form named WHERESTATEMENT as a MEMO field.

 

5. Drag your Business Object to your form

 

6. Bind the @WHERESTATEMENT parameter to the Local variable WHERESTATEMENT  (you can't currently change the parameter at of a Business Object at run time but you can bind variables to the parameter and set the value of the variable to change the filtering)

 

7. Set the Business Object to ALWAYS REFRESH

 

8. Do your normal logic to build the where statement and set the Local variable to the value you desire to change the where condition wherever you choose (ie field refresh, visual script, etc). The variable should be set to a value such as LOCAL.WHERECLAUSE="WHERE eUserName = 'joe' ". You can also use this to set the ordering for the grid such as:

LOCAL.WHERECLAUSE = "WHERE eUserName = 'joe' order by eUserName Desc"

 

Don't forget to set the "Field is dependant on another" for the Grid if it should refresh after a field with the property "Field has dependants of its own" is upated.

 

This technique works with Version 9.0 and 9 SR1 and MS SQL server. I have not tried it with Oracle.

Jerome_Pearce

kmanuel, that is a great idea.

 

It will not work with Oracle, but there may be a similar command for that.

 

You would, of course have to manage quotes in strings yourself while building the string, as well as the need to add quotes around text and formatting dates. So there is a lot or work involved, but at least it is possible like this a la version 5/6/7.

Grzegorz Lag

Kevin

Nice trick with this EXEC, it's much faster than creating stored procedure.

Many thanks

Russ Wicks

Hi Kevin,

Thanks for the tip. The only issue I see with this solution is that the designer is not aware of the column names, and therefore variable names cannot be set and the BO columns cannot be assigned to grid columns. - Or am I missing something here?

Grzegorz Lag

Hi

 

How can I make the same on oracle? This Exec trick is nice, but works only on MS SQL.

Paul Lawrence

The trick with dynamic queries is to fool the designer into thinking that your query is valid.  The designer does not like a parameter for the WHERE clause or the ORDER BY clause.  However if you end you query with:

 

WHERE 1=@myParam

 

and then start your dynamic query with 1 AND - then add your dynamic where clause.

 

If you need to include a dynamic ORDER BY clause include this in your parameter.

 

The designer seems happy with this approach, you get the query variables in designer and can use the business object like any other.

 

I see no reason why this approach would not work in Oracle

Brian Mellert

The EXEC approach worked for the BO, but when I tried to use said BO on a form grid, there were no "columns" to map to even though the EXEC return result was identical to the original.  I couldn't even deploy.  (All I did was enclose the query in EXEC('...') ... it "tests" ok I could use elsewhere.  Bummer

Neil_Hudspeth

The Query needs to be something like:

 

EXEC('Select eFolderName, EPriority, eOriginator from eFolder '+@WhereClause)

 

And the parameter "Where ePriority >5"

 

If the Query is

 

EXEC('Select eFolderName, EPriority, eOriginator from eFolder Where '+@WhereClause)

 

and the Parameter is "ePriority >5"

 

It will test OK but you will see no columns and hence will not be able to generate a grid.  This is because the "Variable Names" Tab does not evaluate the parameter and so sees an incomplete Query and fails.  Whereas the "Test Tab" does evaluate the parameter, sees a syntactically correct Query so succeeds.

 

I believe the previous posts on this topic do explain another way around this if you don't want the Where in the parameter.

 

BTW, Don't forget to populate the Default parameter value on the Parameter tab. 

 

Brian Mellert

I hadn't red the previous post closely enough .. my bad.  Thanks for pointing out the entire clause, including the WHERE itself, had to be in the parameter to work.  Doing this and I was able to get it to work.  (I just have to remember to put in my join condition or a Cartesian product will result.)

 

:robothappy: Thanks for pointing out what I should have caught myself.

Jerome_Pearce

As we have demonstrated in a few of our free examples, what we do is build the required SQL in code to INSERT records into a simple table that contains only the Folder Id of the Query Folder and the resulting Folder Ids. The main benefits are no hacking, ,it works with ALL databases, and good use of indexes and parameterised SQL. You just build a normal Business Object by linking your query with the results from this table.

 

Also, be aware that using the above approach, there is a real danger from SQL injection, which would make any security expert refuse it outright.

Neil_Hudspeth

If you have a requirement to pass a Penetration test, you should apply Hot Fix 3 or later to Metastorm BPM Version 9 SR 3 and follow the instructions in the Release Notes.