ij> -- -- Licensed to the Apache Software Foundation (ASF) under one or more -- contributor license agreements. See the NOTICE file distributed with -- this work for additional information regarding copyright ownership. -- The ASF licenses this file to You under the Apache License, Version 2.0 -- (the "License"); you may not use this file except in compliance with -- the License. You may obtain a copy of the License at -- -- http://www.apache.org/licenses/LICENSE-2.0 -- -- Unless required by applicable law or agreed to in writing, software -- distributed under the License is distributed on an "AS IS" BASIS, -- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. -- See the License for the specific language governing permissions and -- limitations under the License. -- -- test for CURRENT SCHEMA and optional DB2 compatible SET SCHEMA statement -- -- test SET SCHEMA syntax variations -- syntax is SET [CURRENT] SCHEMA [=] ( | USER | ? | '') -- SET CURRENT SQLID [=] ( | USER | ? | '') -- values current schema; 1 -------------------------------------------------------------------------------------------------------------------------------- APP ij> set schema sys; 0 rows inserted/updated/deleted ij> values current schema; 1 -------------------------------------------------------------------------------------------------------------------------------- SYS ij> create schema app; ERROR X0Y68: Schema 'APP' already exists. ij> set current schema app; 0 rows inserted/updated/deleted ij> values current schema; 1 -------------------------------------------------------------------------------------------------------------------------------- APP ij> set schema = sys; 0 rows inserted/updated/deleted ij> values current schema; 1 -------------------------------------------------------------------------------------------------------------------------------- SYS ij> set current schema = app; 0 rows inserted/updated/deleted ij> values current schema; 1 -------------------------------------------------------------------------------------------------------------------------------- APP ij> set schema sys; 0 rows inserted/updated/deleted ij> -- user should use default schema if no user set set schema user; 0 rows inserted/updated/deleted ij> values current schema; 1 -------------------------------------------------------------------------------------------------------------------------------- APP ij> -- see what user does when there is a user create schema judy; 0 rows inserted/updated/deleted ij> connect 'wombat;user=judy' as judy; ij(JUDY)> set schema app; 0 rows inserted/updated/deleted ij(JUDY)> values current schema; 1 -------------------------------------------------------------------------------------------------------------------------------- APP ij(JUDY)> set schema user; 0 rows inserted/updated/deleted ij(JUDY)> values current schema; 1 -------------------------------------------------------------------------------------------------------------------------------- JUDY ij(JUDY)> disconnect; ij> set connection connection0; ij> -- check for default values current schema; 1 -------------------------------------------------------------------------------------------------------------------------------- APP ij> -- check that current sqlid works as a synonym values current sqlid; 1 -------------------------------------------------------------------------------------------------------------------------------- APP ij> -- check that sqlid still works as an identifer create table sqlid(sqlid int); 0 rows inserted/updated/deleted ij> drop table sqlid; 0 rows inserted/updated/deleted ij> -- check that set current sqlid works set current sqlid judy; 0 rows inserted/updated/deleted ij> values current schema; 1 -------------------------------------------------------------------------------------------------------------------------------- JUDY ij> -- check that set sqlid doesn't work (not DB2 compatible) - should get error set sqlid judy; ERROR 42X01: Syntax error: Encountered "sqlid" at line 2, column 5. Issue the 'help' command for general information on IJ command syntax. Any unrecognized commands are treated as potential SQL commands and executed directly. Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. ij> -- change schema and make sure that the current schema is correct set schema sys; 0 rows inserted/updated/deleted ij> values current schema; 1 -------------------------------------------------------------------------------------------------------------------------------- SYS ij> set schema app; 0 rows inserted/updated/deleted ij> -- try using ? outside of a prepared statement set schema ?; ERROR 07000: At least one parameter to the current statement is uninitialized. ij> -- use set schema in a prepared statement autocommit off; ij> prepare p1 as 'set schema ?'; ij> -- should get error with no parameters execute p1; ERROR 07000: At least one parameter to the current statement is uninitialized. ij> -- should get error if null is used create table t1(name varchar(128)); 0 rows inserted/updated/deleted ij> insert into t1 values(null); 1 row inserted/updated/deleted ij> execute p1 using 'select name from t1'; ERROR 42815: The replacement value for 'CURRENT SCHEMA' is invalid. ij> -- should get error if schema doesn't exist execute p1 using 'values(''notthere'')'; ERROR 42Y07: Schema 'notthere' does not exist ij> -- should error with empty string execute p1 using 'values('''')'; ERROR 42Y07: Schema '' does not exist ij> -- should get error if wrong case used execute p1 using 'values(''sys'')'; ERROR 42Y07: Schema 'sys' does not exist ij> -- should get error if too many parameters execute p1 using 'values(''sys'',''app'')'; ERROR XCL13: The parameter position '2' is out of range. The number of parameters for this prepared statement is '1'. ij> -- USER should return an error as it is interpreted as a string constant not an -- identifier execute p1 using 'values(''USER'')'; ERROR 42Y07: Schema 'USER' does not exist ij> -- try positive test execute p1 using 'values(''SYS'')'; 0 rows inserted/updated/deleted ij> values current schema; 1 -------------------------------------------------------------------------------------------------------------------------------- SYS ij> rollback; ij> autocommit on; ij> -- -- try current schema in a number of statements types set schema app; 0 rows inserted/updated/deleted ij> create table t1 ( a varchar(128)); 0 rows inserted/updated/deleted ij> -- insert insert into t1 values (current schema); 1 row inserted/updated/deleted ij> select * from t1; A -------------------------------------------------------------------------------------------------------------------------------- APP ij> set schema judy; 0 rows inserted/updated/deleted ij> insert into app.t1 values (current schema); 1 row inserted/updated/deleted ij> select * from app.t1; A -------------------------------------------------------------------------------------------------------------------------------- APP JUDY ij> -- delete where clause delete from app.t1 where a = current schema; 1 row inserted/updated/deleted ij> select * from app.t1; A -------------------------------------------------------------------------------------------------------------------------------- APP ij> set current schema app; 0 rows inserted/updated/deleted ij> -- target list select current schema from t1; 1 -------------------------------------------------------------------------------------------------------------------------------- APP ij> -- where clause select * from t1 where a = current schema; A -------------------------------------------------------------------------------------------------------------------------------- APP ij> -- update statement delete from t1; 1 row inserted/updated/deleted ij> insert into t1 values ('test'); 1 row inserted/updated/deleted ij> select * from t1; A -------------------------------------------------------------------------------------------------------------------------------- test ij> update t1 set a = current schema; 1 row inserted/updated/deleted ij> select * from t1; A -------------------------------------------------------------------------------------------------------------------------------- APP ij> set schema judy; 0 rows inserted/updated/deleted ij> update app.t1 set a = current schema; 1 row inserted/updated/deleted ij> select * from app.t1; A -------------------------------------------------------------------------------------------------------------------------------- JUDY ij> set schema app; 0 rows inserted/updated/deleted ij> drop table t1; 0 rows inserted/updated/deleted ij> -- default set schema APP; 0 rows inserted/updated/deleted ij> create table t1 ( a int, b varchar(128) default current schema); 0 rows inserted/updated/deleted ij> insert into t1 (a) values (1); 1 row inserted/updated/deleted ij> set schema SYS; 0 rows inserted/updated/deleted ij> insert into app.t1 (a) values (1); 1 row inserted/updated/deleted ij> set schema judy; 0 rows inserted/updated/deleted ij> insert into app.t1 (a) values (1); 1 row inserted/updated/deleted ij> set schema APP; 0 rows inserted/updated/deleted ij> select * from t1; A |B -------------------------------------------------------------------------------------------------------------------------------------------- 1 |APP 1 |SYS 1 |JUDY ij> drop table t1; 0 rows inserted/updated/deleted ij> -- check constraint - this should fail create table t1 ( a varchar(128), check (a = current schema)); ERROR 42Y39: 'CURRENT SCHEMA' may not appear in a CHECK CONSTRAINT definition because it may return non-deterministic results. ij> create table t1 ( a varchar(128), check (a = current sqlid)); ERROR 42Y39: 'CURRENT SCHEMA' may not appear in a CHECK CONSTRAINT definition because it may return non-deterministic results. ij> -- try mix case create schema "MiXCase"; 0 rows inserted/updated/deleted ij> set schema "MiXCase"; 0 rows inserted/updated/deleted ij> values current schema; 1 -------------------------------------------------------------------------------------------------------------------------------- MiXCase ij> set schema app; 0 rows inserted/updated/deleted ij> values current schema; 1 -------------------------------------------------------------------------------------------------------------------------------- APP ij> set schema 'MiXCase'; 0 rows inserted/updated/deleted ij> values current schema; 1 -------------------------------------------------------------------------------------------------------------------------------- MiXCase ij> -- following should get error - schema not found set schema 'MIXCASE'; ERROR 42Y07: Schema 'MIXCASE' does not exist ij> set schema mixcase; ERROR 42Y07: Schema 'MIXCASE' does not exist ij> -- try long schema names (maximum schema identifier length has been changed to 30 as part of DB2 compatibility work) create schema t23456789012345678901234567890; 0 rows inserted/updated/deleted ij> values current schema; 1 -------------------------------------------------------------------------------------------------------------------------------- MiXCase ij> set schema app; 0 rows inserted/updated/deleted ij> values current schema; 1 -------------------------------------------------------------------------------------------------------------------------------- APP ij> set schema t23456789012345678901234567890; 0 rows inserted/updated/deleted ij> values current schema; 1 -------------------------------------------------------------------------------------------------------------------------------- T23456789012345678901234567890 ij> set schema app; 0 rows inserted/updated/deleted ij> values current schema; 1 -------------------------------------------------------------------------------------------------------------------------------- APP ij> set schema 'T23456789012345678901234567890'; 0 rows inserted/updated/deleted ij> values current schema; 1 -------------------------------------------------------------------------------------------------------------------------------- T23456789012345678901234567890 ij> set schema app; 0 rows inserted/updated/deleted ij> values current schema; 1 -------------------------------------------------------------------------------------------------------------------------------- APP ij> autocommit off; ij> prepare p1 as 'set schema ?'; ij> execute p1 using 'values(''T23456789012345678901234567890'')'; 0 rows inserted/updated/deleted ij> values current schema; 1 -------------------------------------------------------------------------------------------------------------------------------- T23456789012345678901234567890 ij> -- the following should fail - 129 length create schema TTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTT; ERROR 42622: The name 'TTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTT' is too long. The maximum length is '128'. ij> set schema TTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTT; ERROR 42622: The name 'TTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTT' is too long. The maximum length is '128'. ij> set schema 'TTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTT'; ERROR 42622: The name 'TTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTT' is too long. The maximum length is '128'. ij> execute p1 using 'values(''TTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTT'')'; ERROR 42815: The replacement value for 'CURRENT SCHEMA' is invalid. ij> rollback; ij> autocommit on; ij> -- clean up drop schema judy restrict; 0 rows inserted/updated/deleted ij> drop schema TTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTT restrict; ERROR 42622: The name 'TTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTT' is too long. The maximum length is '128'. ij>