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
https://dev.community.intersystems.com/post/switch-schema
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
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:
.png)
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
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
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:
Returns:
NOTES:
Beware that $System.SQL.Schema.SetDefault() sets the default schema (optionally) for a namespace systemwide, that is, for all processes, not only current process.
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
That's not a schema, this part is database
and IRIS does not support switching databases or schemas during connection
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.