background image
<< set isolation serializable | TRUNCATE TABLE statement >>

SET SCHEMA statement

<< set isolation serializable | TRUNCATE TABLE statement >>
Derby Reference Manual
72
// These examples show the use of SET ROLE in JDBC statements.
// The case normal form is visible in the SYS.SYSROLES system table.
stmt.execute("SET ROLE admin"); -- case normal form: ADMIN
stmt.execute("SET ROLE \"admin\""); -- case normal form: admin
stmt.execute("SET ROLE none"); -- special case
PreparedStatement ps = conn.prepareStatement("SET ROLE ?");
ps.setString(1, " admin "); -- on execute: case normal form: ADMIN
ps.setString(1, "\"admin\""); -- on execute: case normal form: admin
ps.setString(1, "none"); -- on execute: syntax error
ps.setString(1, "\"none\""); -- on execute: case normal form: none
SET SCHEMA statement
The SET SCHEMA statement sets the default schema for a connection's session to the
designated schema. The default schema is used as the target schema for all statements
issued from the connection that do not explicitly specify a schema name.
The target schema must exist for the SET SCHEMA statement to succeed. If the schema
doesn't exist an error is returned. See
CREATE SCHEMA statement
.
The SET SCHEMA statement is not transactional: If the SET SCHEMA statement is part
of a transaction that is rolled back, the schema change remains in effect.
Syntax
SET [CURRENT] SCHEMA [=]
{
schemaName
|
USER | ? | '<string-constant>' } | SET CURRENT SQLID [=]
{
schemaName
| USER | ? | '<string-constant>' }
schemaName
is an identifier with a maximum length of 128. It is case insensitive unless
enclosed in double quotes. (For example, SYS is equivalent to sYs, SYs, sys, etcetera.)
USER is the current user. If no current user is defined, the current schema defaults the
APP schema. (If a user name was specified upon connection, the user's name is the
default schema for the connection, if a schema with that name exists.)
? is a dynamic parameter specification that can be used in prepared statements. The
SET SCHEMA statement can be prepared once and then executed with different schema
values. The schema values are treated as string constants so they are case sensitive.
For example, to designate the APP schema, use the string "APP" rather than "app".
Examples
-- The following are all equivalent and will work
-- assuming a schema called HOTEL
SET SCHEMA HOTEL
SET SCHEMA hotel
SET CURRENT SCHEMA hotel
SET CURRENT SQLID hotel
SET SCHEMA = hotel
SET CURRENT SCHEMA = hotel
SET CURRENT SQLID = hotel
SET SCHEMA "HOTEL" -- quoted identifier
SET SCHEMA 'HOTEL' -- quoted string-- This example produces an error
because
-- lower case hotel won't be found
SET SCHEMA = 'hotel'
-- This example produces an error because SQLID is not
-- allowed without CURRENT
SET SQLID hotel
-- This sets the schema to the current user id
SET CURRENT SCHEMA USER
// Here's an example of using SET SCHEMA in an Java program
PreparedStatement ps = conn.PrepareStatement("set schema ?");