databaseManager.addTableFilterParam() Broken ???

Classic Servoy
1

We are trying to implement databaseManager.addTableFilterParam() in our solution.
However we does, the function seems to NOT work. Calling it at solution startup, or anytime later, what we get is ‘true’ as result, but with absolutely no filter on table data.

When setting filters whith addFoundSetFilterParam, we can check the function obviously because it works, and using -dbstacktrace to monitor how it works.

On the contrary, databaseManager.addTableFilterParam() lets no information in the trace in addition. Please check it for 2.2 final!

Servoy dev and client 2.2 RC7; java 1.5.

On the second hand, will a filtered table be filtered when accessed by a relation? (Do you patch relations to a table when it is filtered?)

Best regards,

2

Hi Oliver, works fine for me!

but you have to keep in mind, that it only works for new created foundsets
How does you code look like?

3

HJK:
but you have to keep in mind, that it only works for new created foundsets
How does you code look like?

Hmm, may be you are speaking of addFoundSetFilterParam() which also works very fine for us. Our issue is with addTableFilterParam().

Here’s an example which refuse to do anything:

[...]
var sqlServerName = forms.mMission.controller.getServerName();
var aBilling 		= databaseManager.addTableFilterParam(sqlServerName, 'abilling', 'f0008', '=', null);
var aCurrencies 	= databaseManager.addTableFilterParam(sqlServerName, 'acurrencies', 'f0008', '=', null);
var aWritings 		= databaseManager.addTableFilterParam(sqlServerName, 'awritings', 'f0008', '=', null);
var cListelements	= databaseManager.addTableFilterParam(sqlServerName, 'clistelements', 'f0008', '=', null);
var iElements 		= databaseManager.addTableFilterParam(sqlServerName, 'ielements', 'f0008', '=', null);
var iHeaders 		= databaseManager.addTableFilterParam(sqlServerName, 'iheaders', 'f0008', '=', null);
var pElements 		= databaseManager.addTableFilterParam(sqlServerName, 'pelements', 'f0008', '=', null);
var pHeaders 		= databaseManager.addTableFilterParam(sqlServerName, 'pheaders', 'f0008', '=', null);
[...]

When called, this method render true for each of the variables, meaning the filter must be on, but no filtering occurs in the solution, nor does the filters appears in the trace when displaying records.
(here, we do not want to display any record from any table which contains anything in column ‘f0008’)

4

We are talking about the same!

I do not know about the value: null

try a sample code like this on a form and attach it to for example onLoad:

var sqlServerName = forms.mMission.controller.getServerName();
var aBilling = databaseManager.addTableFilterParam(sqlServerName, ‘abilling’, ‘f0008’, ‘=’, 23);

(for example you know that 23 excists. btw: you must be going from design to browse-mode to activate the filter than)

You will see that it works!

5

Thank you for your advice Harjo, but for us it is broken!

We use ‘Version R2 2.2rc8-build 326 / Java version 1.5.0_03-b07 (Windows XP SP2/SP1)’ now and got the same results as when using rc7. This on several systems!

What is your configuration, because we have several hours of tests and addTableFilterParam() is definitely not working at all for us!

Does anybody else use this function with success? If yes with which configuration?

(btw, We got the same results on upgraded systems and on systems with a fresh install (all options))

6

Did you use it in the solution startup method?

7

Yes, we do. And for testing purposes, we try to call the method also by hand, but to no avail…
Another point, not mentionned before is that we use modules in all solutions.

8

Our tests work fine (even with null values), could you sent a small sample solution demonstrating the problem?

9

You were all right, addTableFilterParam() is working fine.
Actually we got an error, due to this call:

[...]
var sqlServerName = forms.mMission.controller.getServerName();
[...]

Which effectively loads the form with a foundset before we set the filter… :idea:

So let’s say addTableFilterParam is working fine. But as we are real ‘pain in the ass’ developer, we still have an issue. We definitely want a feature to get the current ‘serverName’ as it is silly to hardcode this in an initialization procedure of a module, which by evidence can have different ‘serverName’ depending on the solution it is embedded in…

So, servoy team, would you please consider either making the call to getServerName() not to load the form with a foundset or supplying us with another function dedicated to addTableFilterParam() as for instance databaseManager.get(Table)ServerName(myTable)

And sorry for the annoyance, but this was a nasty one (for us).

10

olivier melet:
…So, servoy team, would you please consider either making the call to getServerName() not to load the form with a foundset …or supplying us with another function…

This is the chicken and the egg problem…one must be first, A form object can only return its serverName when existing, but form creation does load the default foundset.

We will think about a method like: databaseManager.getServerName(formName)

11

Jan Blok:
We will think about a method like: databaseManager.getServerName(formName)

Is this possible now, and if so, how?

Ben

12

On a form controller we now have the function getServerName. So you can do:

serverName = controller.getServerName()
13

Thanks Sandor, but Olivier mentioned this previously, and I think that was the cause of his problem:

olivier melet:
Actually we got an error, due to this call:

[...]

var sqlServerName = forms.mMission.controller.getServerName();
[…]



**Which effectively loads the form with a foundset before we set the filter...** :idea:

Unless the getServerName() function doesn’t load a foundset when called anymore? :?

14

I think that when you request the servername/tablename through a form controller, the form is loaded, so setting a tableFilter after that will not work.

But…

In general, setting tableFilters is something done in the login or onSolutionStart method and at designtime, you know exactly which tables you want to filter and in which server those tables reside. So you will not be asking a form for it’s table/server.

So, in my opinion, you do not need an extra function under the databaseManager.

Under the databaseManager, you allready have a function to request all the ServerNames used in your solution. And based on a Server, you can request all tableNames and with that you can get to all the columns of all tables. so all the info you need to set tablefilters is there.

Paul

15

And: couldn’t you ask your login form for the server?

16

In general, setting tableFilters is something done in the login or onSolutionStart method and at designtime, you know exactly which tables you want to filter and in which server those tables reside. So you will not be asking a form for it’s table/server.

So this would (probably) involve (a lot of) hard-coding in the login method.

Under the databaseManager, you allready have a function to request all the ServerNames used in your solution. And based on a Server, you can request all tableNames and with that you can get to all the columns of all tables. so all the info you need to set tablefilters is there.

I understand the idea, but it seems an awful lot of looping is involved - especially since, if you already know the form names, using something like ‘databaseManager.getServerName(formName)’ (from an array, let’s say) seems a whole lot more straightforward, at the same time bypassing the whole foundset loading issue.

It has often been said that there are many ways to do the same things with Servoy, and that’s true - this would just be one such case, but more developer-friendly in my humble opinion.

My 2 cents … :)

Ben

17

How do you “know” the formName allready?

Did you hardcode that? then you only need to change your hardcoded value to the proper table…

Please shed some light on your specific scenario for a more detailed response. Are you using multiple servers per solution? How many tables need filtering and on which data (for example, in a SaaS solution you would add an “owner” column to every table and filter all tables on the owner column)?

Paul

18

I get your point - we can either hard-code or store (in a separate table) the form or table names, either way some input is needed…

No specific scenario to speak of, more a ‘wish list’ kind of thing, I guess, especially since Jan Blok dangled that carrot in front of us… :lol:

Ben

19

I have an application which sets filters with databaseManager.addTableFilterParam(). However, later in the process I may need to either change the particular filter or delete it altogether. I thought perhaps setCreateEmptyFormFoundsets might work, but I haven’t found much info on it.

Any ideas would be helpful. I am running version 3.5.7

Thanks, Steve

20

Filters are permanent in the 3.5 branch.

Consider upgrading to 4.1, where you have all the flexibility you require to remove/update filters along the way.

Paul