So I’m trying to setup value lists for my project.
I’m in the ValueLists create screen and pick the table I want from the list - the resulting table definition is incorrect.
The table in question is called k_r_item_exception_code, owned by dmass38 - the database server I’m using connects as dmass38 as well.
There is another table in the database called the same thing owned by a different user - there is a public synonym for that object as well.
So, instead of Servoy picking up the table definition for dmass38.k_r_item_exception_code it is picking up the public synonym that points to the dmass.k_r_item_exception.
Why would Servoy pick up a public synonym before a locally owned object !?!?!?!?
I dropped the public synonym but can’t get Servoy to pickup the correct table definition - I’ve tried refreshing the view in Servoy, restarting Servoy but it still picks up the wrong definition.
Help!!
Ok , I think I’ve tracked down the issue.
I’ll first re-clarify how things are setup…
KELMAN36 owns a table called K_R_ITEM_EXCEPTION.
DMASS38 owns a table called K_R_ITEM_EXCEPTION.
The structure of these two tables is different.
KELMAN36 has granted SELECT and REFERENCES on K_R_ITEM_EXCEPTION_CODE to PUBLIC
DMASS38 has granted SELECT on K_R_ITEM_EXCEPTION to a role called DMASS38_READONLY.
ANDERSON is an Oracle user that has been granted the DMASS38_READONLY role and can, therefore, select from the DMASS38.K_R_ITEM_EXCEPTION_CODE table.
ANDERSON has a local synonym K_R_ITEM_EXCEPTION that points to DMASS38.K_R_ITEM_EXCEPTION table.
I have a database server in Servoy that connects as ANDERSON.
When I see the list of available objects it sees something called K_R_ITEM_EXCEPTION_CODE
When I ‘open’ the table to reveal its structure it is the structure for the KELMAN36.K_R_ITEM_EXCEPTION_CODE instead of DMASS38.K_R_ITEM_EXCEPTION_CODE
The only way to fix this is to remove ALL PUBLIC grants on the KELMAN36.K_R_ITEM_EXCEPTION_CODE table.
When I do this and ‘open’ the table to reveal its structure in Servoy the table finally displays the DMASS38.K_R_ITEM_EXCEPTION_CODE structure.
Even if I grant REFERENCES to PUBLIC on the KELMAN36.K_R_ITEM_EXCEPTION_CODE table and ‘open’ the table in Servoy it sees the KELMAN36 version of the structure and NOT the DMASS38 structure.
Smells like a bug to me…
Please submit an issue about this in our CRM system. Also link it to this topic.
Mark,
What happens if you use fill in the schema/catalog fields in the server definitions?
Rob
If I fill-in any of those fields the connection to the Oracle database cannot be made (i.e. there’s an error). I’m supposing that those fields mean nothing to the Oracle JDBC driver.
Are you using capital letters to fill in the schema name? Oracle is a bit sensitive about the case…
Paul
If I put lowercase in ‘Schema’ I get an error ‘ORA-01435: user does not exist’.
If I put uppercase in the ‘Schema’ I get no error but the list of database objects is empty when I refresh the Database Server.
If I put lowercase or uppercase in the ‘Catalog’ I got an error ‘ORA-00922: missing or invalid option’
Ok. This will be a long story ![:)]( "Smile")
. A partial fix will be available in Servoy 4.0.2 (didn’t reach 4.0.1).
Currently Servoy ignores SYNONYMS/ALIASES as not all databases have them and they can make data broadcasting difficult (if you have the same table accessible by two different names - one through a synonym).
SYNONYMS/ALIASES could be supported in the future (probably with the developer being responsable for not using both alias and real name) - so please add this as a feature request to the Support System if you would like this to be implemented.
This being said, let’s talk about the exact scenario you described. You connected to the Oracle database using the ANDERSON user, but with schema set to , not ANDERSON. In this case Servoy gets access to all tables of all users/schemas on the server that are accessible to user ANDERSON. It would receive two K_R_ITEM_EXCEPTION tables, one from KELMAN36 schema and one from DMASS38 schema (it ignores the alias). It would have to choose one of them - and it will always use the one from the last schema alphabetically ordered - so KELMAN36.
The FIX I did now is - in case of such name conflicts - Servoy will give priority to tables/views of the user used for the connection. Of course, in your case this will change nothing, because both tables are not of the connected user.
So I have two solutions for your problem - to use before an eventual SYNONYMS/ALIASES feature request is implemented:
SOLUTION 1:
- create a view K_R_ITEM_EXCEPTION owned by ANDERSON that is SELECT * FROM DMASS38.K_R_ITEM_EXCEPTION (careful, you will have to grant SELECT directly to ANDERSON, not through the role - otherwise Oracle will not allow you to create the view)
- wait for 4.0.2 and K_R_ITEM_EXCEPTION view should be used by Servoy because of the fix above. Current version may or may not choose the view for use, depending on alphabetical ordering of schemas…
SOLUTION 2:
- create a view K_R_ITEM_EXCEPTION owned by ANDERSON that is SELECT * FROM DMASS38.K_R_ITEM_EXCEPTION (same as in solution 1)
- in the server editor specify use of schema ANDERSON - in this case you will only have tables/views owned by ANDERSON shown - so your view will be used as it is the only one with that name. Don’t know if this works for you, as you might want to work with tables from other schemas as well that are accessible to the ANDERSON user.
Another solution that just came to me would be creating a different server in Servoy in order to access this table - duplicate server and specify schema DMASS38. This way you can use tables/views available to ANDERSON that belong to DMASS38. No need to create that extra view in solutions 1 and 2 posted above or wait for a new version of Servoy.
The problem manifests itself even if the Oracle user I’m logging in as OWNS the tables !!
Example…
DMASS38 owns a table called K_R_ITEM_EXCEPTION_CODE
KELMAN36 owns a table called K_R_ITEM_EXCEPTION_CODE
There is a public SELECT grant on KELMAN36.K_R_ITEM_EXCEPTION_CODE
My database connection in Servoy Developer connects as DMASS38
When I open the K_R_ITEM_EXCEPTION_CODE table it shows me the table definition for KELMAN36 and NOT the one that DMASS38 owns !!!
The theory about ‘not all databases have synonyms’ doesn’t even apply here because in this case DMASS38 can’t even get to his OWN table.
This is REALLY nasty. Like I said in earlier posts the only way to ‘fix’ this is to remove the public grants OF ANY KIND from the KELMAN36.K_R_ITEM_EXCEPTION_CODE table. Once that is done then DMASS38 can see his OWN K_R_ITEM_EXCEPTION_CODE table.
I can’t use a view either because Servoy won’t let me create a form for it because the VIEW doesn’t have a PRIMARY KEY.
Yes, if you have more then one table with the same name accessible, before the fix I was talking about it would choose one of them based on the alphabet. I tried creating the view as I said and creating a form for it - and it worked for me. It shows the pk column correctly. Doesn’t it show the correct columns for the view in the table editor (the same as in the table) in your case?
Did you try the last solution I proposed? The one in my second post? Duplicate the server connection & use in the new one schema DMASS38 for user ANDERSON. It should work. And you don’t need to create another view.
We have a working solution for this - I think its a combination of what you were saying earlier.
We setup our database connection as ANDERSON and set the schema to DMASS38 - then it picks up the correct list ! And, in the example table I referred to earlier (K_R_ITEM_EXCEPTION_CODE) it picks up the CORRECT table definition.
I think our problem is solved… for now ![:mrgreen:]( "Mr. Green")