📄 currentschema.out
字号:
ij> -- test for CURRENT SCHEMA and optional DB2 compatible SET SCHEMA statement---- test SET SCHEMA syntax variations-- syntax is SET [CURRENT] SCHEMA [=] (<identifier> | USER | ? | '<string>')-- SET CURRENT SQLID [=] (<identifier> | USER | ? | '<string>')--values current schema;1 --------------------------------------------------------------------------------------------------------------------------------APP ij> set schema sys;0 rows inserted/updated/deletedij> values current schema;1 --------------------------------------------------------------------------------------------------------------------------------SYS ij> create schema app;ERROR X0Y68: Schema 'APP' already exists.ij> set current schema app;0 rows inserted/updated/deletedij> values current schema;1 --------------------------------------------------------------------------------------------------------------------------------APP ij> set schema = sys;0 rows inserted/updated/deletedij> values current schema;1 --------------------------------------------------------------------------------------------------------------------------------SYS ij> set current schema = app;0 rows inserted/updated/deletedij> values current schema;1 --------------------------------------------------------------------------------------------------------------------------------APP ij> set schema sys;0 rows inserted/updated/deletedij> -- user should use default schema if no user setset schema user;0 rows inserted/updated/deletedij> values current schema;1 --------------------------------------------------------------------------------------------------------------------------------APP ij> -- see what user does when there is a usercreate schema judy;0 rows inserted/updated/deletedij> connect 'jdbc:derby:wombat;user=judy' as judy;ij(JUDY)> set schema app;0 rows inserted/updated/deletedij(JUDY)> values current schema;1 --------------------------------------------------------------------------------------------------------------------------------APP ij(JUDY)> set schema user;0 rows inserted/updated/deletedij(JUDY)> values current schema;1 --------------------------------------------------------------------------------------------------------------------------------JUDY ij(JUDY)> disconnect;ij> set connection connection0;ij> -- check for defaultvalues current schema;1 --------------------------------------------------------------------------------------------------------------------------------APP ij> -- check that current sqlid works as a synonymvalues current sqlid;1 --------------------------------------------------------------------------------------------------------------------------------APP ij> -- check that sqlid still works as an identifercreate table sqlid(sqlid int);0 rows inserted/updated/deletedij> drop table sqlid;0 rows inserted/updated/deletedij> -- check that set current sqlid worksset current sqlid judy;0 rows inserted/updated/deletedij> values current schema;1 --------------------------------------------------------------------------------------------------------------------------------JUDY ij> -- check that set sqlid doesn't work (not DB2 compatible) - should get errorset sqlid judy;ERROR 42X01: Syntax error: Encountered "sqlid" at line 2, column 5.ij> -- change schema and make sure that the current schema is correctset schema sys;0 rows inserted/updated/deletedij> values current schema;1 --------------------------------------------------------------------------------------------------------------------------------SYS ij> set schema app;0 rows inserted/updated/deletedij> -- try using ? outside of a prepared statementset schema ?;ERROR 07000: At least one parameter to the current statement is uninitialized.ij> -- use set schema in a prepared statementautocommit off;ij> prepare p1 as 'set schema ?';ij> -- should get error with no parametersexecute p1;ERROR 07000: At least one parameter to the current statement is uninitialized.ij> -- should get error if null is usedcreate table t1(name varchar(128));0 rows inserted/updated/deletedij> insert into t1 values(null);1 row inserted/updated/deletedij> 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 existexecute p1 using 'values(''notthere'')';ERROR 42Y07: Schema 'notthere' does not existij> -- should error with empty stringexecute p1 using 'values('''')';ERROR 42Y07: Schema '' does not existij> -- should get error if wrong case usedexecute p1 using 'values(''sys'')';ERROR 42Y07: Schema 'sys' does not existij> -- should get error if too many parametersexecute 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-- identifierexecute p1 using 'values(''USER'')';ERROR 42Y07: Schema 'USER' does not existij> -- try positive testexecute p1 using 'values(''SYS'')';0 rows inserted/updated/deletedij> values current schema;1 --------------------------------------------------------------------------------------------------------------------------------SYS ij> rollback;ij> autocommit on;ij> -- -- try current schema in a number of statements typesset schema app;0 rows inserted/updated/deletedij> create table t1 ( a varchar(128));0 rows inserted/updated/deletedij> -- insertinsert into t1 values (current schema);1 row inserted/updated/deletedij> select * from t1;A --------------------------------------------------------------------------------------------------------------------------------APP ij> set schema judy;0 rows inserted/updated/deletedij> insert into app.t1 values (current schema);1 row inserted/updated/deletedij> select * from app.t1;A --------------------------------------------------------------------------------------------------------------------------------APP JUDY ij> -- delete where clausedelete from app.t1 where a = current schema;1 row inserted/updated/deleted⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -