Switch schema

Question

Question

qing he · Apr 25

#Java #Caché

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

Robert Cemper · Apr 29

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:

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

Returns:

Status Code

NOTES:

- 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.

0 0

score 0 · Answer 1 · 2024-04-26T03:27:14-04:00

You can do it by hand, specifying in the JDBC url used the schema to connect, so you have to disconnect from the previous connection and connect again with the new **CORRECTION** namespace:

jdbc:CACHE://127.0.0.1:1972/NAMESPACE1--> jdbc:CACHE://127.0.0.1:1972/NAMESPACE2

score 0 · Answer 2 · 2024-04-27T23:15:30-04:00

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

score 0 · Answer 3 · 2024-04-28T04:53:45-04:00

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.

jm2c
Andreas

score 0 · Answer 4 · 2024-04-28T21:46:59-04:00

thanks Andreas，This is certainly an effective approach, but I still want to change it at the code level, and I don't want my users to touch Cache management portal

score 1 · Answer 5 · 2024-04-29T04:33:01-04:00

Beware that $System.SQL.Schema.SetDefault() sets the default schema (optionally) for a namespace systemwide, that is, for all processes, not only current process.

score 0 · Answer 6 · 2024-04-28T04:56:36-04:00

The last element is the namespace not the schema, please see here: Using the JDBC Driver | Using Java with InterSystems Software | InterSystems IRIS Data Platform 2024.1

jdbc:Cache://<host>:<port>/<namespace>

Andreas

score 0 · Answer 7 · 2024-04-28T07:31:51-04:00

That's not a schema, this part is database
and IRIS does not support switching databases or schemas during connection

score 0 · Answer 8 · 2024-04-29T15:17:12-04:00

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.