· Apr 25

Switch schema

The setSchema () method is not implemented in CacheConnection, so how should I switch Schema?

Product version: Caché 2018.1
Discussion (9)4
Log in or sign up to continue

Thank you very much for your reply. Maybe the problem I described is not clear enough for you to misunderstand, and if I understand it correctly, your answer is try to teach me how to switch namespace. The effect I want to achieve is to switch schema without changing existing links

for example, SQLUser.person - > Sample.person: when executing the SQL statement "select * from person" without qualifying the Schema name, switch the default 'SQLUser' schema to the specified 'Sample' schema

You can change the default schema (= what is used if no schema is specified in the query) here:

However, if you use the schema extensively in your data model to structure the model, you should always use the schema in the query. Working with fully qualified object names is simply best practice and avoids misunderstandings. It seems better not to rely on such things like a default schema because it can changed (or diff from server to server). This becomes even more important when the same object names exist in different schemas.


As you now explained what you really are looking for:   $System.SQL.Schema.SetDefault()

From Docu:

classmethod SetDefault(schema As %Library.String = "", ByRef oldval As %Library.String, Namespace As %Library.Boolean = 0) as %Library.Status

Sets the default schema used by SQL.
This configuration setting provides the ability to define a default schema name other than SQLUser, the default. When an unqualified table name is encountered in an SQL statement (and there is no #import statement specified), the default schema will be used. This setting has nothing to do with the mappings between SQL schema names and the class package name, it only specifies the default schema. Parameter:

String containing the default SQL schema name. If schema is "" or not defined, the default schema will be set to SQLUser.
Passed By Reference. Contains the previous value of the setting.
Boolean 1/0 flag. If TRUE, set the default schema for the current namespace only. The default for Namespace is FALSE


Status Code


- You must have the "USE" permission on the %Admin Manage Resource in order to change this configuration setting.
- Changing this configuration setting will affect all processes immediately.

I don't think there is a really good way to do exactly this through JDBC, but you can handle this using the JDBC URL if you do a little setup beforehand.

First, in the SMP, System Administration, Configuration, System Configuration, Namespaces, click on Create New Namespace. Give it a name - let's say "SAMPLE" and have it copy from your original namespace (probably USER).

Second, go into a terminal, switch to the new namespace and use the command:

w $SYSTEM.SQL.Schema.SetDefault("SAMPLE",.oldval,1)

You should get a 1 confirming that the change was made successfully.

At this point, when you make your JDBC connection, connecting to either namespace will work on the same database, but connecting to the SAMPLE namespace will use the sample schema, and the USER namespace will use the SQLUser schema.