INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Multiple schemas in Mimer - how to use them?

Multiple schemas in Mimer - how to use them?

(OP)
I find the way schemas are implemented in Mimer extremely confusing, as well as non-SQL92 compliant. Perhaps, someone could help me understand how to use them in Mimer.

From all docs that I found it follows that schema is tightly associated with an ident (USER or PROGRAM). One does not set the current schema other than by logging in with the appropriate ident. SET SCHEMA is not supported in Mimer (hence the non-compliance with SQL92). So, it does not seem to be possible to reference tables from different schemas in one SELECT statement.

The ability to create USER ident WITHOUT SCHEMA only adds to the confusion. If I log in as such a user, what schema will my tables belong to?

If anyone figured this out, I will appreciate sharing his/her wisdom with me.

Alex.

RE: Multiple schemas in Mimer - how to use them?

To reference a table in another schema, just use


select * from schema_name.table_name


If you don't specify a schema name, a schema having the same name as the current authorization ident will be used.

In (the now obsolete) SQL 92 the SET SCHEMA STATEMENT was needed for compliance with FULL or INTERMEDIATE SQL. It was not needed to be compliant with entry level SQL. In the current emerging standard, SQL 2003, SET SCHEMA is not a core feature.

An ident created without a schema can not create any objects.

RE: Multiple schemas in Mimer - how to use them?

(OP)
Thank you for the quick response. Perhaps I should have been more explicit in what I am trying to accomplish.

We are working on a Java-based report generator that can use different RDBMS for querying. One of the things that we have to do is to get the list of all schemas available in the given RDBMS. JDBC provides a database-independent mechanism for this in its DatabaseMetaData getCatalogs() and getSchemas() methods. In SQL Server and Sybase, getCatalogs() returns a list of databases ("schemas" in the standard terminology); in McKoi getSchemas() does the same. But in Mimer, getCatalogs() returns nothing (catalogs are not supported in Mimer), and the list of schemas returned from getSchemas() contains only the schema associated with the currently logged in ident. In other words, there is no way to get the full list of all schemas available in Mimer - and this means that Mimer will have to be handled diffrently by the report generator logic.

Unless, of course, you can show me that there is a way to do it.

Thanks again.

Alex.

RE: Multiple schemas in Mimer - how to use them?

Hello.
The DatabaseMetaData.getSchemas method call will return a result set with those schemas which contain resources (such as a table or a procedure) that are available to the current user. There may be objects in the database which the current user has not been granted access to.

For example, try creating an additional user, and let that user create a procedure which the first user is granted execute rights on. For example (if you like paste this to BSQL):

CREATE IDENT USR1 AS USER IDENTIFIED BY 'USR1';
DISCONNECT;
CONNECT;
USR1
USR1
CREATE PROCEDURE PROC () BEGIN END;
GRANT EXECUTE ON PROCEDURE PROC TO PUBLIC;

and then try calling DatabaseMEtaData.getSchemas using an ident other than USR1.

I believe this behavior is perfectly in line with the SQL-99 standard, which the JDBC specification is supposed to match.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close