You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-commits@db.apache.org by da...@apache.org on 2006/03/15 22:31:59 UTC
svn commit: r386169 [12/36] - in
/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master:
DerbyNet/jdk16/ DerbyNetClient/jdk16/ jdk16/
Added: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNet/jdk16/testij.out
URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNet/jdk16/testij.out?rev=386169&view=auto
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNet/jdk16/testij.out (added)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNet/jdk16/testij.out Wed Mar 15 13:31:53 2006
@@ -0,0 +1,41 @@
+Testing various ij connections and comments in front of selects
+org.apache.derby.tools.ij xxxFILTERED_TESTIJSOURCExxx
+ij> driver 'com.ibm.db2.jcc.DB2Driver';
+ij> --Bug 4632 Make the db italian to make sure string selects are working
+connect 'jdbc:derby:net://xxxFILTERED_HOSTNAMExxx:1527/wombat;create=true;territory=it:retrieveMessagesFromServerOnGetMessage=true;' USER 'dbadmin' PASSWORD 'dbadmin';
+ij> connect 'jdbc:derby:net://xxxFILTERED_HOSTNAMExxx:1527/wombat' USER 'dbadmin' PASSWORD 'dbadbmin';
+ij(CONNECTION1)> -- this is a comment, a comment in front of a select should not cause an error
+select * from sys.systables where 1=0;
+TABLEID |TABLENAME |& |SCHEMAID |&
+-----
+0 rows selected
+ij(CONNECTION1)> -- this is a comment, a comment in front of a values clauses should not cause an error
+values(1);
+1
+-----
+1
+1 row selected
+ij(CONNECTION1)> -- Try some URL attributes
+disconnect all;
+ij> connect 'jdbc:derby:net://xxxFILTERED_HOSTNAMExxx:1527/junk;create=true:retrieveMessagesFromServerOnGetMessage=true;' USER 'dbadmin' PASSWORD 'dbadbmin';
+ij> select * from APP.notthere;
+ERROR 42X05: Table 'APP.NOTTHERE' does not exist. :
+ij> -- examples from the docs
+connect 'jdbc:derby:net://xxxFILTERED_HOSTNAMExxx:1527/wombat;create=true:user=judy;password=judy;retrieveMessagesFromServerOnGetMessage=true;';
+ij(CONNECTION1)> connect 'jdbc:derby:net://xxxFILTERED_HOSTNAMExxx:1527/"./wombat":user=judy;password=judy;retrieveMessagesFromServerOnGetMessage=true;';
+ij(CONNECTION2)> connect 'jdbc:derby:net://xxxFILTERED_HOSTNAMExxx:1527/toursDB:retrieveMessagesFromServerOnGetMessage=true;';
+ERROR (no SQLState): null userid not supported
+ij(CONNECTION2)> connect 'jdbc:derby:net://xxxFILTERED_HOSTNAMExxx:1527/toursDB:retrieveMessagesFromServerOnGetMessage=true;' USER 'dbadmin' PASSWORD 'dbadbmin';
+ERROR 08004: The application server rejected establishment of the connection. An attempt was made to access a database, toursDB, which was not found.
+ij(CONNECTION2)> connect 'jdbc:derby:net://xxxFILTERED_HOSTNAMExxx:1527/wombat' USER 'APP' PASSWORD 'APP';
+ij(CONNECTION3)> connect 'jdbc:derby:net://xxxFILTERED_HOSTNAMExxx:1527/my-db-name;create=true:user=usr;password=pwd;retrieveMessagesFromServerOnGetMessage=true;';
+ij(CONNECTION4)> connect 'jdbc:derby:net://xxxFILTERED_HOSTNAMExxx:1527/my-db-name;upgrade=true:user=usr;password=pwd;retrieveMessagesFromServerOnGetMessage=true;';
+ij(CONNECTION5)> connect 'jdbc:derby:net://xxxFILTERED_HOSTNAMExxx:1527/my-db-name;shutdown=true:user=usr;password=pwd;'
+----- Quoted db and attributes
+connect 'jdbc:derby:net://xxxFILTERED_HOSTNAMExxx:1527/"./my-dbname;create=true":user=usr;password=pwd;';
+ERROR 08006: DB2 SQL error: SQLCODE: -1, SQLSTATE: 08006, SQLERRMC: Database 'my-db-name' shutdown.
+ij(CONNECTION5)> connect 'jdbc:derby:net://xxxFILTERED_HOSTNAMExxx:1527/"./my-dbname;create=true":user=usr;password=pwd;retrieveMessagesFromServerOnGetMessage=true;';
+ij(CONNECTION6)> -- with no user
+connect 'jdbc:derby:net://xxxFILTERED_HOSTNAMExxx:1527/wombat;create=true:retrieveMessagesFromServerOnGetMessage=true;';
+ERROR (no SQLState): null userid not supported
+ij(CONNECTION6)>
Added: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNet/jdk16/unaryArithmeticDynamicParameter.out
URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNet/jdk16/unaryArithmeticDynamicParameter.out?rev=386169&view=auto
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNet/jdk16/unaryArithmeticDynamicParameter.out (added)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNet/jdk16/unaryArithmeticDynamicParameter.out Wed Mar 15 13:31:53 2006
@@ -0,0 +1,172 @@
+Test using parameters for unary minus and unary plus
+insert into t1 values(?,+?,-?,?)
+? parameter type is INTEGER
+unary +? parameter type is SMALLINT
+unary -? parameter type is DOUBLE
+select * from t1 where -? in (select c21 from t2)
+unary -? parameter type is INTEGER
+ C11,C12,C13,C14
+ --- --- --- ---
+ {1,1,1.1,abc}
+ {-1,-1,-1.0,def}
+select * from t1 where c11 = -? and c12 = +? and c13 = ?
+unary -? parameter type is INTEGER
+unary +? parameter type is SMALLINT
+? parameter type is DOUBLE
+ C11,C12,C13,C14
+ --- --- --- ---
+ {1,1,1.1,abc}
+ C11,C12,C13,C14
+ --- --- --- ---
+ {-1,-1,-1.0,def}
+select * from t1 where -? = ABS_FUNCT(+?)
+unary -? parameter type is INTEGER
+unary +? parameter type is INTEGER
+ C11,C12,C13,C14
+ --- --- --- ---
+ {1,1,1.1,abc}
+ {-1,-1,-1.0,def}
+select * from t1 where -? = max_cni(-5,-1)
+unary -? parameter type is INTEGER
+ C11,C12,C13,C14
+ --- --- --- ---
+ {1,1,1.1,abc}
+ {-1,-1,-1.0,def}
+select * from t1 where -? = max_cni(-?,+?)
+unary -? parameter type is INTEGER
+unary -? parameter type is INTEGER
+unary +? parameter type is INTEGER
+ C11,C12,C13,C14
+ --- --- --- ---
+ {1,1,1.1,abc}
+ {-1,-1,-1.0,def}
+Try the function again. But use, use sqrt(+?) & abs(-?) functions to send params
+select * from t1 where -? = max_cni(abs(-?), sqrt(+?))
+unary -? parameter type is INTEGER
+unary -? parameter type is DOUBLE
+unary +? parameter type is DOUBLE
+ C11,C12,C13,C14
+ --- --- --- ---
+ {1,1,1.1,abc}
+ {-1,-1,-1.0,def}
+select * from t1 where c11 between -? and +?
+unary -? parameter type is INTEGER
+unary +? parameter type is INTEGER
+ C11,C12,C13,C14
+ --- --- --- ---
+ {1,1,1.1,abc}
+select * from t1 where +? not in (-?, +?, 2, ?)
+unary +? parameter type is INTEGER
+unary -? parameter type is INTEGER
+unary +? parameter type is INTEGER
+? parameter type is INTEGER
+ C11,C12,C13,C14
+ --- --- --- ---
+ {1,1,1.1,abc}
+ {-1,-1,-1.0,def}
+select * from t1 where +? < c12
+unary +? parameter type is SMALLINT
+ C11,C12,C13,C14
+ --- --- --- ---
+ {1,1,1.1,abc}
+select * from t1 where -? = c11 + ?
+unary -? parameter type is INTEGER
+? parameter type is INTEGER
+ C11,C12,C13,C14
+ --- --- --- ---
+ {-1,-1,-1.0,def}
+select * from t1 where c11 + ? = -?
+? parameter type is INTEGER
+unary -? parameter type is INTEGER
+ C11,C12,C13,C14
+ --- --- --- ---
+ {-1,-1,-1.0,def}
+select * from t1 where c11 + c12 = -?
+unary -? parameter type is INTEGER
+ C11,C12,C13,C14
+ --- --- --- ---
+ {-1,-1,-1.0,def}
+select * from t1 where -? not in (select c21+? from t2)
+unary -? parameter type is INTEGER
+? parameter type is INTEGER
+ C11,C12,C13,C14
+ --- --- --- ---
+ {1,1,1.1,abc}
+ {-1,-1,-1.0,def}
+select cast(-? as smallint), cast(+? as int) from t1
+unary -? parameter type is SMALLINT
+unary +? parameter type is INTEGER
+ 1,2
+ - -
+ {-2,2}
+ {-2,2}
+select nullif(-?,c11) from t1
+unary -? parameter type is INTEGER
+ 1
+ -
+ {-22}
+ {-22}
+select sqrt(-?) from t1
+unary -? parameter type is DOUBLE
+ 1
+ -
+ {8.0}
+ {8.0}
+select * from t1 where c11 = any (select -? from t2)
+SQL State : 42X34
+Got expected exception There is a ? parameter in the select list. This is not allowed. :
+Negative test - -?/+? at the beginning and/ at the end of where clause
+select * from t1 where -? and c11=c11 or +?
+SQL State : 42X19
+Got expected exception The WHERE or HAVING clause or CHECK CONSTRAINT definition is a 'PARAMETER' expression. It must be a BOOLEAN expression. :
+Negative test - -?/+? in like escape function
+select * from sys.systables where tablename like -? escape +?
+SQL State : 42X37
+Got expected exception The unary '-' operator is not allowed on the 'VARCHAR' type. :
+Negative test - -?/+? in binary timestamp function
+select timestamp(-?,+?) from t1
+SQL State : 42X37
+Got expected exception The unary '-' operator is not allowed on the 'DATE' type. :
+Negative test - -? in unary timestamp function
+select timestamp(-?) from t1
+SQL State : 42X36
+Got expected exception The 'timestamp' operator is not allowed to take a ? parameter as an operand. :
+Negative test - -? in views
+create view v1 as select * from t1 where c11 = -?
+SQL State : 42X98
+Got expected exception Parameters are not allowed in a VIEW definition. :
+Negative test - -? in inner join
+select * from t1 inner join t1 as t333 on -?
+SQL State : 42X37
+Got expected exception The unary '-' operator is not allowed on the 'BOOLEAN' type. :
+Negative test - -? by itself in where clause
+select * from t1 where -?
+SQL State : 42X19
+Got expected exception The WHERE or HAVING clause or CHECK CONSTRAINT definition is a 'PARAMETER' expression. It must be a BOOLEAN expression. :
+Negative test - -? is null not allowed because is null allowed on char types only
+select * from t1 where -? is null
+SQL State : 42X37
+Got expected exception The unary '-' operator is not allowed on the 'VARCHAR' type. :
+select case when -?=c11 then -? else c12 end from t1
+unary -? parameter type is INTEGER
+unary -? parameter type is SMALLINT
+ 1
+ -
+ {1}
+ {-22}
+Negative test - unary plus parameters on both sides of / operator
+select * from t1 where c11 = ?/-?
+SQL State : 42X35
+Got expected exception It is not allowed for both operands of '/' to be ? parameters. :
+Negative test - unary plus in | | operation
+select c11 | | +? from t1
+SQL State : 42X37
+Got expected exception The unary '+' operator is not allowed on the 'VARCHAR' type. :
+Negative test - unary minus for char column
+select * from t1 where c14 = -?
+SQL State : 42X37
+Got expected exception The unary '-' operator is not allowed on the 'CHAR' type. :
+Negative test - unary plus for char column
+select * from t1 where c14 like +?
+SQL State : 42X37
+Got expected exception The unary '+' operator is not allowed on the 'CHAR' type. :
Added: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNet/jdk16/users.out
URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNet/jdk16/users.out?rev=386169&view=auto
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNet/jdk16/users.out (added)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNet/jdk16/users.out Wed Mar 15 13:31:53 2006
@@ -0,0 +1,153 @@
+ij> --
+connect 'wombat;create=true;user=dan;password=MakeItFaster';
+ij(CONNECTION1)> autocommit off;
+ij(CONNECTION1)> prepare p1 as 'CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(?,?)';
+ij(CONNECTION1)> execute p1 using 'values(''derby.database.defaultConnectionMode'', ''noAccess'')';
+Statement executed.
+ij(CONNECTION1)> execute p1 using 'values(''derby.database.fullAccessUsers'', ''francois,jeff,howardR,ames,kreg'')';
+Statement executed.
+ij(CONNECTION1)> remove p1;
+ij(CONNECTION1)> commit;
+ij(CONNECTION1)> autocommit on;
+ij(CONNECTION1)> disconnect;
+ij> connect 'wombat;shutdown=true;user=francois;password=paceesalute';
+ERROR 08006: Database 'wombat' shutdown.
+ij> connect 'myDB;create=true;user=dan;password=MakeItFaster';
+ij(CONNECTION1)> autocommit off;
+ij(CONNECTION1)> prepare p2 as 'CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(?,?)';
+ij(CONNECTION1)> execute p2 using 'values(''derby.database.defaultConnectionMode'', ''noAccess'')';
+Statement executed.
+ij(CONNECTION1)> execute p2 using 'values(''derby.database.fullAccessUsers'', ''jerry,kreg,dan,jamie,ames,francois'')';
+Statement executed.
+ij(CONNECTION1)> remove p2;
+ij(CONNECTION1)> commit;
+ij(CONNECTION1)> autocommit on;
+ij(CONNECTION1)> disconnect;
+ij> connect 'myDB;shutdown=true;user=dan;password=MakeItFaster';
+ERROR 08006: Database 'myDB' shutdown.
+ij> -- beetle 5468
+disconnect all;
+ij> -- Specifically test JBMS users.
+-----
+----- check allowed users in wombat db.
+connect 'wombat;user=kreg;password=visualWhat?';
+ij> connect 'wombat;user=jeff;password=HomeRun61';
+ij(CONNECTION1)> connect 'wombat;user=ames;password=AnyVolunteer?';
+ij(CONNECTION2)> connect 'wombat;user=howardR;password=IamBetterAtTennis';
+ij(CONNECTION3)> connect 'wombat;user=francois;password=paceesalute';
+ij(CONNECTION4)> show connections;
+CONNECTION0 - jdbc:derby:net://xxxFILTERED_HOSTNAMExxx:1527/wombat;user=kreg;password=visualWhat?
+CONNECTION1 - jdbc:derby:net://xxxFILTERED_HOSTNAMExxx:1527/wombat;user=jeff;password=HomeRun61
+CONNECTION2 - jdbc:derby:net://xxxFILTERED_HOSTNAMExxx:1527/wombat;user=ames;password=AnyVolunteer?
+CONNECTION3 - jdbc:derby:net://xxxFILTERED_HOSTNAMExxx:1527/wombat;user=howardR;password=IamBetterAtTennis
+CONNECTION4* - jdbc:derby:net://xxxFILTERED_HOSTNAMExxx:1527/wombat;user=francois;password=paceesalute
+* = current connection
+ij(CONNECTION4)> disconnect all;
+ij> -- check allowed users in myDB db.
+----- also check USER flavors
+connect 'myDB;user=jerry;password=SacreBleu';
+ij> create table APP.t1(c1 char(30) check (UPPER(c1) <> 'JAMIE'));
+0 rows inserted/updated/deleted
+ij> insert into APP.t1 values CURRENT_USER;
+1 row inserted/updated/deleted
+ij> connect 'myDB;user=kreg;password=visualWhat?';
+ij(CONNECTION1)> insert into APP.t1 values USER;
+1 row inserted/updated/deleted
+ij(CONNECTION1)> connect 'myDB;user=ames;password=AnyVolunteer?';
+ij(CONNECTION2)> insert into APP.t1 values SESSION_USER;
+1 row inserted/updated/deleted
+ij(CONNECTION2)> connect 'myDB;user=dan;password=MakeItFaster';
+ij(CONNECTION3)> select * from APP.t1;
+C1
+-----
+JERRY
+KREG
+AMES
+ij(CONNECTION3)> update APP.t1 set c1 = {fn user() };
+3 rows inserted/updated/deleted
+ij(CONNECTION3)> select * from APP.t1;
+C1
+-----
+DAN
+DAN
+DAN
+ij(CONNECTION3)> connect 'myDB;user=francois;password=paceesalute';
+ij(CONNECTION4)> update APP.t1 set c1 = USER;
+3 rows inserted/updated/deleted
+ij(CONNECTION4)> connect 'myDB;user=jamie;password=MrNamePlates';
+ij(CONNECTION5)> select * from APP.t1;
+C1
+-----
+FRANCOIS
+FRANCOIS
+FRANCOIS
+ij(CONNECTION5)> update APP.t1 set c1 = USER;
+ERROR 23513: The check constraint 'xxxxGENERATED-IDxxxx' was violated while performing an INSERT or UPDATE on table 'APP.T1'. :
+ij(CONNECTION5)> show connections;
+CONNECTION0 - jdbc:derby:net://xxxFILTERED_HOSTNAMExxx:1527/myDB;user=jerry;password=SacreBleu
+CONNECTION1 - jdbc:derby:net://xxxFILTERED_HOSTNAMExxx:1527/myDB;user=kreg;password=visualWhat?
+CONNECTION2 - jdbc:derby:net://xxxFILTERED_HOSTNAMExxx:1527/myDB;user=ames;password=AnyVolunteer?
+CONNECTION3 - jdbc:derby:net://xxxFILTERED_HOSTNAMExxx:1527/myDB;user=dan;password=MakeItFaster
+CONNECTION4 - jdbc:derby:net://xxxFILTERED_HOSTNAMExxx:1527/myDB;user=francois;password=paceesalute
+CONNECTION5* - jdbc:derby:net://xxxFILTERED_HOSTNAMExxx:1527/myDB;user=jamie;password=MrNamePlates
+* = current connection
+ij(CONNECTION5)> disconnect all;
+ij> --
+----- some negative cases
+-----
+----- Invalid login's
+connect 'wombat';
+ERROR (no SQLState): Connection authorization failure occurred. Reason: userid invalid.
+ij> connect 'wombat;user=badUser1;password=YeeHa!';
+ERROR (no SQLState): Connection authorization failure occurred. Reason: userid invalid.
+ij> connect 'wombat;user=badUser2;password=YeeHa!';
+ERROR (no SQLState): Connection authorization failure occurred. Reason: userid invalid.
+ij> connect 'myDB;user=dan;password=MakeItSlower';
+ERROR (no SQLState): Connection authorization failure occurred. Reason: userid invalid.
+ij> connect 'myDB;user=jamie;password=LetMeIn';
+ERROR (no SQLState): Connection authorization failure occurred. Reason: userid invalid.
+ij> connect 'wombat;user=francois;password=Corsica';
+ERROR (no SQLState): Connection authorization failure occurred. Reason: userid invalid.
+ij> -- Invalid database users
+connect 'myDB;user=howardR;password=IamBetterAtTennis';
+ERROR 04501: Database connection refused.
+ij> connect 'wombat;user=jerry;password=SacreBleu';
+ERROR 04501: Database connection refused.
+ij> connect 'wombat;user=jamie;password=MrNamePlates';
+ERROR 04501: Database connection refused.
+ij> show connections;
+No connections available.
+ij> connect 'wombat;user=francois;password=paceesalute';
+ij> connect 'myDB;user=jerry;password=SacreBleu';
+ij(CONNECTION1)> -- Database shutdown - check user - should fail
+connect 'myDB;shutdown=true';
+ERROR (no SQLState): Connection authorization failure occurred. Reason: userid invalid.
+ij(CONNECTION1)> connect 'myDB;user=jamie;password=LetMeIn;shutdown=true';
+ERROR (no SQLState): Connection authorization failure occurred. Reason: userid invalid.
+ij(CONNECTION1)> connect 'wombat;user=jerry;password=SacreBleu;shutdown=true';
+ERROR 04501: Database connection refused.
+ij(CONNECTION1)> show connections;
+CONNECTION0 - jdbc:derby:net://xxxFILTERED_HOSTNAMExxx:1527/wombat;user=francois;password=paceesalute
+CONNECTION1* - jdbc:derby:net://xxxFILTERED_HOSTNAMExxx:1527/myDB;user=jerry;password=SacreBleu
+* = current connection
+ij(CONNECTION1)> -- Database shutdown - check user - should succeed
+----- beetle 5367
+connect 'wombat;user=francois;password=paceesalute;shutdown=true';
+ERROR 08006: Database 'wombat' shutdown.
+ij(CONNECTION1)> connect 'myDB;user=jerry;password=SacreBleu;shutdown=true';
+ERROR 08006: Database 'myDB' shutdown.
+ij(CONNECTION1)> show connections;
+CONNECTION0 - jdbc:derby:net://xxxFILTERED_HOSTNAMExxx:1527/wombat;user=francois;password=paceesalute
+CONNECTION1* - jdbc:derby:net://xxxFILTERED_HOSTNAMExxx:1527/myDB;user=jerry;password=SacreBleu
+* = current connection
+ij(CONNECTION1)> -- JBMS System shutdown - check user - should fail
+connect ';user=jamie;password=LetMeIn;shutdown=true';
+ERROR (no SQLState): Connection authorization failure occurred. Reason: userid invalid.
+ij(CONNECTION1)> disconnect all;
+ij> -- JBMS System shutdown - check user - should succeed
+connect ';user=francois;password=paceesalute;shutdown=true';
+ERROR XJ015: Derby system shutdown.
+ij> -- beetle 5390
+----- the server does not shut down properly in network server
+;
+ij>
Added: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNet/jdk16/users2.out
URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNet/jdk16/users2.out?rev=386169&view=auto
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNet/jdk16/users2.out (added)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNet/jdk16/users2.out Wed Mar 15 13:31:53 2006
@@ -0,0 +1,241 @@
+ij> --
+----- Specifically test Derby users using DERBY scheme
+----- and by only looking at database properties for authentication
+----- The only user at the system level is system/manager
+-----
+----- check allowed users in wombat db.
+----- initial connection in sysprop was:
+----- connect 'wombat;create=true;user=system;password=manager';
+-----
+----- Default to: derby.authentication.provider=BUILTIN
+-----
+autocommit off;
+ij> prepare p1 as 'CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(?,?)';
+ij> execute p1 using 'values(''derby.connection.requireAuthentication'', ''true'')';
+Statement executed.
+ij> commit;
+ij> remove p1;
+ij> autocommit on;
+ij> disconnect all;
+ij> connect 'wombat;shutdown=true;user=system;password=manager';
+ERROR 08006: Database 'wombat' shutdown.
+ij> -- beetle 5468
+disconnect all;
+ij> connect 'wombat;user=system;password=manager';
+ij> --
+----- set authentication config for 'wombat' database
+-----
+autocommit off;
+ij> prepare p2 as 'CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(?,?)';
+ij> execute p2 using 'values(''derby.user.kreg'', ''visualWhat?'')';
+Statement executed.
+ij> execute p2 using 'values(''derby.user.jeff'', ''HomeRun61'')';
+Statement executed.
+ij> execute p2 using 'values(''derby.user.ames'', ''AnyVolunteer?'')';
+Statement executed.
+ij> execute p2 using 'values(''derby.user.jamie'', ''MrNamePlates'')';
+Statement executed.
+ij> execute p2 using 'values(''derby.user.howardR'', ''IamBetterAtTennis'')';
+Statement executed.
+ij> execute p2 using 'values(''derby.user.francois'', ''paceesalute'')';
+Statement executed.
+ij> execute p2 using 'values(''derby.database.fullAccessUsers'', ''jeff,howardR,ames,francois,kreg'')';
+Statement executed.
+ij> execute p2 using 'values(''derby.database.readOnlyAccessUsers'', ''jamie'')';
+Statement executed.
+ij> execute p2 using 'values(''derby.database.defaultConnectionMode'', ''noAccess'')';
+Statement executed.
+ij> execute p2 using 'values(''derby.database.propertiesOnly'', ''true'')';
+Statement executed.
+ij> commit;
+ij> autocommit on;
+ij> -- Check that the passwords are encrypted
+----- values getDatabaseProperty('derby.user.francois');
+----- values getDatabaseProperty('derby.user.ames');
+----- values getDatabaseProperty('derby.user.kreg');
+----- values getDatabaseProperty('derby.user.jeff');
+----- values getDatabaseProperty('derby.user.howardR');
+----- values getDatabaseProperty('derby.user.jamie');
+connect 'wombat;user=kreg;password=visualWhat?';
+ij(CONNECTION1)> connect 'wombat;user=jeff;password=HomeRun61';
+ij(CONNECTION2)> connect 'wombat;user=ames;password=AnyVolunteer?';
+ij(CONNECTION3)> connect 'wombat;user=howardR;password=IamBetterAtTennis';
+ij(CONNECTION4)> -- should succeed
+create table APP.t1(c1 char(30));
+0 rows inserted/updated/deleted
+ij(CONNECTION4)> insert into APP.t1 values CURRENT_USER;
+1 row inserted/updated/deleted
+ij(CONNECTION4)> connect 'wombat;user=jamie;password=MrNamePlates';
+ij(CONNECTION5)> -- should fail as readOnly user
+insert into APP.t1 values CURRENT_USER;
+ERROR 25502: An SQL data change is not permitted for a read-only connection, user or database. :
+ij(CONNECTION5)> create table APP.t2(c1 char(30));
+ERROR 25503: DDL is not permitted for a read-only connection, user or database. :
+ij(CONNECTION5)> show connections;
+CONNECTION0 - jdbc:derby:net://xxxFILTERED_HOSTNAMExxx:1527/wombat;user=system;password=manager
+CONNECTION1 - jdbc:derby:net://xxxFILTERED_HOSTNAMExxx:1527/wombat;user=kreg;password=visualWhat?
+CONNECTION2 - jdbc:derby:net://xxxFILTERED_HOSTNAMExxx:1527/wombat;user=jeff;password=HomeRun61
+CONNECTION3 - jdbc:derby:net://xxxFILTERED_HOSTNAMExxx:1527/wombat;user=ames;password=AnyVolunteer?
+CONNECTION4 - jdbc:derby:net://xxxFILTERED_HOSTNAMExxx:1527/wombat;user=howardR;password=IamBetterAtTennis
+CONNECTION5* - jdbc:derby:net://xxxFILTERED_HOSTNAMExxx:1527/wombat;user=jamie;password=MrNamePlates
+* = current connection
+ij(CONNECTION5)> disconnect all;
+ij> -- check allowed users in myDB db.
+-----
+connect 'myDB;create=true;user=system;password=manager';
+ij> autocommit off;
+ij> prepare p3 as 'CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(?,?)';
+ij> execute p3 using 'values(''derby.connection.requireAuthentication'', ''true'')';
+Statement executed.
+ij> remove p3;
+ij> autocommit on;
+ij> disconnect all;
+ij> connect 'myDB;shutdown=true;user=system;password=manager';
+ERROR 08006: Database 'myDB' shutdown.
+ij> -- beetle 5468
+disconnect all;
+ij> connect 'myDB;user=system;password=manager';
+ij> --
+----- set authentication config for 'myDB' database
+-----
+autocommit off;
+ij> prepare p4 as 'CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(?,?)';
+ij> execute p4 using 'values(''derby.user.kreg'', ''visualWhat?'')';
+Statement executed.
+ij> execute p4 using 'values(''derby.user.dan'', ''MakeItFaster'')';
+Statement executed.
+ij> execute p4 using 'values(''derby.user.ames'', ''AnyVolunteer?'')';
+Statement executed.
+ij> execute p4 using 'values(''derby.user.jerry'', ''SacreBleu'')';
+Statement executed.
+ij> execute p4 using 'values(''derby.user.jamie'', ''MrNamePlates'')';
+Statement executed.
+ij> execute p4 using 'values(''derby.user.francois'', ''paceesalute'')';
+Statement executed.
+ij> execute p4 using 'values(''derby.database.fullAccessUsers'', ''jerry,dan,kreg,ames,francois,jamie'')';
+Statement executed.
+ij> execute p4 using 'values(''derby.database.defaultConnectionMode'', ''noAccess'')';
+Statement executed.
+ij> execute p4 using 'values(''derby.database.propertiesOnly'', ''true'')';
+Statement executed.
+ij> commit;
+ij> autocommit on;
+ij> -- Check that the passwords are encrypted
+----- values getDatabaseProperty('derby.user.francois');
+----- values getDatabaseProperty('derby.user.ames');
+----- values getDatabaseProperty('derby.user.kreg');
+----- values getDatabaseProperty('derby.user.dan');
+----- values getDatabaseProperty('derby.user.jerry');
+----- values getDatabaseProperty('derby.user.jamie');
+-----
+----- also check USER flavors
+-----
+connect 'myDB;user=jerry;password=SacreBleu';
+ij(CONNECTION1)> create table APP.t1(c1 char(30) check (UPPER(c1) <> 'JAMIE'));
+0 rows inserted/updated/deleted
+ij(CONNECTION1)> insert into APP.t1 values CURRENT_USER;
+1 row inserted/updated/deleted
+ij(CONNECTION1)> connect 'myDB;user=kreg;password=visualWhat?';
+ij(CONNECTION2)> insert into APP.t1 values USER;
+1 row inserted/updated/deleted
+ij(CONNECTION2)> connect 'myDB;user=ames;password=AnyVolunteer?';
+ij(CONNECTION3)> insert into APP.t1 values SESSION_USER;
+1 row inserted/updated/deleted
+ij(CONNECTION3)> connect 'myDB;user=dan;password=MakeItFaster';
+ij(CONNECTION4)> select * from APP.t1;
+C1
+-----
+JERRY
+KREG
+AMES
+ij(CONNECTION4)> update APP.t1 set c1 = USER;
+3 rows inserted/updated/deleted
+ij(CONNECTION4)> select * from APP.t1;
+C1
+-----
+DAN
+DAN
+DAN
+ij(CONNECTION4)> connect 'myDB;user=francois;password=paceesalute';
+ij(CONNECTION5)> update APP.t1 set c1 = USER;
+3 rows inserted/updated/deleted
+ij(CONNECTION5)> connect 'myDB;user=jamie;password=MrNamePlates';
+ij(CONNECTION6)> select * from APP.t1;
+C1
+-----
+FRANCOIS
+FRANCOIS
+FRANCOIS
+ij(CONNECTION6)> update APP.t1 set c1 = USER;
+ERROR 23513: The check constraint 'xxxxGENERATED-IDxxxx' was violated while performing an INSERT or UPDATE on table 'APP.T1'. :
+ij(CONNECTION6)> show connections;
+CONNECTION0 - jdbc:derby:net://xxxFILTERED_HOSTNAMExxx:1527/myDB;user=system;password=manager
+CONNECTION1 - jdbc:derby:net://xxxFILTERED_HOSTNAMExxx:1527/myDB;user=jerry;password=SacreBleu
+CONNECTION2 - jdbc:derby:net://xxxFILTERED_HOSTNAMExxx:1527/myDB;user=kreg;password=visualWhat?
+CONNECTION3 - jdbc:derby:net://xxxFILTERED_HOSTNAMExxx:1527/myDB;user=ames;password=AnyVolunteer?
+CONNECTION4 - jdbc:derby:net://xxxFILTERED_HOSTNAMExxx:1527/myDB;user=dan;password=MakeItFaster
+CONNECTION5 - jdbc:derby:net://xxxFILTERED_HOSTNAMExxx:1527/myDB;user=francois;password=paceesalute
+CONNECTION6* - jdbc:derby:net://xxxFILTERED_HOSTNAMExxx:1527/myDB;user=jamie;password=MrNamePlates
+* = current connection
+ij(CONNECTION6)> disconnect all;
+ij> --
+----- some negative cases
+-----
+----- Invalid login's
+connect 'wombat';
+ERROR (no SQLState): Connection authorization failure occurred. Reason: userid invalid.
+ij> connect 'wombat;user=badUser1;password=YeeHa!';
+ERROR (no SQLState): Connection authorization failure occurred. Reason: userid invalid.
+ij> connect 'wombat;user=badUser2;password=YeeHa!';
+ERROR (no SQLState): Connection authorization failure occurred. Reason: userid invalid.
+ij> connect 'myDB;user=dan;password=MakeItSlower';
+ERROR (no SQLState): Connection authorization failure occurred. Reason: userid invalid.
+ij> connect 'myDB;user=jamie;password=LetMeIn';
+ERROR (no SQLState): Connection authorization failure occurred. Reason: userid invalid.
+ij> connect 'wombat;user=francois;password=Corsica';
+ERROR (no SQLState): Connection authorization failure occurred. Reason: userid invalid.
+ij> -- Invalid database users
+connect 'myDB;user=howardR;password=IamBetterAtTennis';
+ERROR (no SQLState): Connection authorization failure occurred. Reason: userid invalid.
+ij> connect 'wombat;user=jerry;password=SacreBleu';
+ERROR (no SQLState): Connection authorization failure occurred. Reason: userid invalid.
+ij> connect 'wombat;user=jamie;password=MrNamePlates';
+ij> show connections;
+CONNECTION0* - jdbc:derby:net://xxxFILTERED_HOSTNAMExxx:1527/wombat;user=jamie;password=MrNamePlates
+* = current connection
+ij> connect 'wombat;user=francois;password=paceesalute';
+ij(CONNECTION1)> connect 'myDB;user=jerry;password=SacreBleu';
+ij(CONNECTION2)> -- Database shutdown - check user - should fail
+connect 'myDB;shutdown=true';
+ERROR (no SQLState): Connection authorization failure occurred. Reason: userid invalid.
+ij(CONNECTION2)> connect 'myDB;user=jamie;password=LetMeIn;shutdown=true';
+ERROR (no SQLState): Connection authorization failure occurred. Reason: userid invalid.
+ij(CONNECTION2)> connect 'wombat;user=jerry;password=SacreBleu;shutdown=true';
+ERROR (no SQLState): Connection authorization failure occurred. Reason: userid invalid.
+ij(CONNECTION2)> show connections;
+CONNECTION0 - jdbc:derby:net://xxxFILTERED_HOSTNAMExxx:1527/wombat;user=jamie;password=MrNamePlates
+CONNECTION1 - jdbc:derby:net://xxxFILTERED_HOSTNAMExxx:1527/wombat;user=francois;password=paceesalute
+CONNECTION2* - jdbc:derby:net://xxxFILTERED_HOSTNAMExxx:1527/myDB;user=jerry;password=SacreBleu
+* = current connection
+ij(CONNECTION2)> disconnect all;
+ij> show connections;
+No connections available.
+ij> -- Database shutdown - check user - should succeed
+connect 'wombat;user=francois;password=paceesalute;shutdown=true';
+ERROR 08006: Database 'wombat' shutdown.
+ij> -- beetle 5468
+disconnect all;
+ij> connect 'myDB;user=jerry;password=SacreBleu;shutdown=true';
+ERROR 08006: Database 'myDB' shutdown.
+ij> -- beetle 5468
+disconnect all;
+ij> -- there should be no connections left here
+show connections;
+No connections available.
+ij> -- JBMS System shutdown - check user - should fail
+connect ';user=jamie;password=LetMeIn;shutdown=true';
+ERROR (no SQLState): Connection authorization failure occurred. Reason: userid invalid.
+ij> -- JBMS System shutdown - check user - should succeed
+connect ';user=system;password=manager;shutdown=true';
+ERROR XJ015: Derby system shutdown.
+ij>