we have installed Rollbase in a private server, made a copy of our existing OpenEdge database (which has about 800 tables and a lot of data), imported the Rollbase schema (-infile create_oe.sql) and all the other things that were mentioned in chapter 18.
Now when I try to create a new object "New Object (with tab) from an External Metadata" then the Rollbase server let me wait a while, then comes with the page where I should select an existing table. Except: there are no tables, the combobox is empty!
The SQL explorer in Eclipse (or what's the product name nowadays) can retrieve schema and data when the tablenames are prepended with "pub.". So perhaps we need to set some configuration in Rollbase that specifies that it must look in schema "pub". Is there such a setting?
Any other clues why Rollbase may not be able to find the schema?
thanks, Jurjen.
I guess it has something to do with SQL Schema. All ABL tables are exposed in the PUB schema. The Rollbase tables are in the ROLLBASE schema. It seems that Rollbase looks in the ROLLBASE schema and not in the PUB schema.
Now if I knew enough SQL statements, I might try to define that PUB is a synonym of ROLLBASE. Or perhaps try some statement that exposes all (or parts of) PUB in the ROLLBASE schema.
Unfortunately I do not know enough SQL. Help?
some progression... I found that after statement:
create synonym rollbase.customer for pub.customer;
in the Eclipse SQL-editor, the Rollbase can actually find the Customer table and create a Rollbase object for it.
Note: nothing has implicitly created a schema named "rollbase".
Note also that "rollbase" is just a username, and this is the user that imported the "create_oe.sql" tables, and user "rollbase" is the username specified in the connect-params where Rollbase connects to this database.
I think that had I used a different username, then maybe I would not need to create synonyms for every existing table?
Wild guess: is Rollbase actually authorized to see what's in PUB?
Thanks, yes I think Rollbase (the user with username "rollbase") is authorized. Because when I created the user I have entered the " grant DBA to 'rollbase'; " command.
Also, the SQL-editor in Eclipse is also logged in as user "rollbase" and it can see everything in PUB, but only when the tablename is qualified with "pub." like "select * from pub.customer".
It seems that user "rollbase" by default only looks in schema "rollbase" when the tablename is not qualified with a a schema-name.
That is normal behaviour for SQL clients. By default, each user gets a schema with the same name as their user name. When they connect, they are placed into that schema. When you use a table name in a SQL statement, we look up the name in the current schema when a schema name is not specified. Furthermore, when you create a table, it goes into your schema by default and you are the owner.
There is a set schema statement that allows you to switch to another schema. There is also an option to set the schema to something other than default when you connect.
As for your original problem, I don't think it is good practice to put the Rollbase tables into an existing database. IMHO, better to keep them separate.
Thanks Gus.
You said: "There is also an option to set the schema to something other than default when you connect."
I can't find that option in docs or samples, and now I am out of guesses.
The key thing is in your SQL, you execute this statement:
SET SCHEMA 'pub'
(This is very useful in DataDirect Cloud with the OpenEdge On-Premise Driver: You can set the "Initialization String" to SET SCHEMA 'pub'. Once you do that, then SELECT * for Customer will work.)