SQL filter and search in tables with relationship.

User 40fe9b3632

20-12-2010 10:31:36

Instant JChem 5.3.8, Windows, MySQL server.


In our database I have introduced row level sequrity, because I want to exclude information related to projects the users are not working on. I have made a user_project table and a projects table, where I define which project or projects a specific compound ID (EPTnumber) is related to.


The SQL filter looks like this: EPTnumber IN (SELECT EPTnumber FROM PROJECTS WHERE Project_name IN (SELECT Project FROM USER_PROJECT WHERE USER_PROJECT.user_name = '__IJC_USERNAME__'))



I has been added in all tables in "Extra Attributes". It works fine, but for one problem: I have made a relationship between my Structures table and various data tables. I have also made a form view, where one can see the relational data. It works in Browse mode, but it is only possible to make a query in the fields related to the Structures table. Queries in any of the fields referring to the related (data) tables returns an error message: “A org.springframework.dao.DataIntegrityViolationException exception has occurred. However, the system should continue working without further problems. Click Show Detals for the strack trace”


What is wrong? The individual tables are searchable in Grid view.



 

ChemAxon fa971619eb

20-12-2010 16:35:55

We'll look into this. We'll need to set up a similar test system so it will take a little while.


In the meantime, could you look in the log file and see if there is more information about the error reported there? If so then please send/attach it to this topic (or send privately if you prefer)


Thanks


Tim

ChemAxon 60ee1f1328

21-12-2010 12:42:56

Hi There,


We set up a test scenario and found that the problem was down to unqualified SQL, in particular on the tables that the filter is being applied to.


i.e. you should need to apply your SQL filter like below - fully qualified. In this test case we used the demo data provided in IJC as parent and child tables but of course you should edit accordingly for your own table names.


Cheers.


WOMBAT.EPTnumber IN 

(SELECT p.EPTnumber

 FROM PROJECTS p WHERE p.Project_name

 IN (SELECT up.Project

     FROM USER_PROJECT up

     WHERE up.user_name = '__IJC_USERNAME__'))



WOMBAT_ACT_LIST.EPTnumber IN

(SELECT p.EPTnumber

 FROM PROJECTS p WHERE p.Project_name

 IN (SELECT up.Project

     FROM USER_PROJECT up

     WHERE up.user_name = '__IJC_USERNAME__'))