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. -- -- this test shows some ij abilities against an active database create table t (i int); 0 rows inserted/updated/deleted ij> insert into t values (3), (4); 2 rows inserted/updated/deleted ij> prepare s as 'select * from t'; ij> execute s; I ----------- 3 4 ij> remove s; ij> -- now it won't find s execute s; IJ ERROR: Unable to establish prepared statement S ij> prepare s as 'select * from t where i=?'; ij> -- fails, needs parameter execute s; ERROR 07000: At least one parameter to the current statement is uninitialized. ij> -- works, finds value execute s using 'values 3'; IJ WARNING: Autocommit may close using result set I ----------- 3 ij> prepare t as 'values 3'; ij> -- same as last execute execute s using t; IJ WARNING: Autocommit may close using result set I ----------- 3 ij> -- same as last execute execute 'select * from t where i=?' using 'values 3'; IJ WARNING: Autocommit may close using result set I ----------- 3 ij> -- same as last execute execute 'select * from t where i=?' using t; IJ WARNING: Autocommit may close using result set I ----------- 3 ij> -- param that is not needed gets out of range message execute 'select * from t where i=?' using 'values (3,4)'; IJ WARNING: Autocommit may close using result set ERROR XCL13: The parameter position '2' is out of range. The number of parameters for this prepared statement is '1'. ij> -- ignores rows that are not needed execute 'select * from t where i=?' using 'values 3,4'; IJ WARNING: Autocommit may close using result set I ----------- 3 ij> -- with autocommit off, extra rows are processed and no warning results autocommit off; ij> execute 'select * from t where i=?' using 'values 3,4'; I ----------- 3 I ----------- 4 ij> execute 'select * from t where i=?' using 'values 3'; I ----------- 3 ij> autocommit on; ij> -- will say params not set when no rows in using values execute 'select * from t where i=?' using 'select * from t where i=9'; IJ ERROR: Using clause had no results ij> -- will say params not set when using values is not a query execute 'select * from t where i=?' using 'create table s (i int)'; IJ ERROR: Using clause had no results ij> -- note that the using part was, however, executed... drop table s; 0 rows inserted/updated/deleted ij> -- DERBY-2558: Verify that we get a reasonable message when the 'dimension' -- of the 'using-set' does not match the 'dimension' of the prepared statement: create table t2558 (i int); 0 rows inserted/updated/deleted ij> insert into t2558 values (3), (4); 2 rows inserted/updated/deleted ij> -- First two statements below should fail. Third one should work. execute 'select * from t2558 where i = ?' using 'values (3,4)'; IJ WARNING: Autocommit may close using result set ERROR XCL13: The parameter position '2' is out of range. The number of parameters for this prepared statement is '1'. ij> execute 'select * from t2558 where i in (?,?,?)' using 'values (3,4)'; IJ WARNING: Autocommit may close using result set ERROR 07000: At least one parameter to the current statement is uninitialized. ij> execute 'select * from t2558 where i = ? or i = ?' using 'values (3,4)'; IJ WARNING: Autocommit may close using result set I ----------- 3 4 ij> -- bug 5926 - make sure the using clause result set got closed drop table t; 0 rows inserted/updated/deleted ij> create table t(c1 int); 0 rows inserted/updated/deleted ij> insert into t values(1); 1 row inserted/updated/deleted ij> execute 'select * from t where c1=?' using 'select * from t where c1=1'; IJ WARNING: Autocommit may close using result set C1 ----------- 1 ij> drop table t; 0 rows inserted/updated/deleted ij> create table t(c1 int); 0 rows inserted/updated/deleted ij> insert into t values(1); 1 row inserted/updated/deleted ij> insert into t values(2); 1 row inserted/updated/deleted ij> execute 'select * from t where c1=?' using 'select * from t where c1>=1'; IJ WARNING: Autocommit may close using result set C1 ----------- 1 ij> drop table t; 0 rows inserted/updated/deleted ij> -- Bug 4694 Test automatic rollback with close of connection -- in ij connect 'wombat'; ij(CONNECTION1)> autocommit off; ij(CONNECTION1)> create table a (a int); 0 rows inserted/updated/deleted ij(CONNECTION1)> select count(*) from a; 1 ----------- 0 ij(CONNECTION1)> disconnect; ij> set connection connection0; ij> select count(*) from a; ERROR 42X05: Table/View 'A' does not exist. ij> create table t ( c char(50)); 0 rows inserted/updated/deleted ij> insert into t values('hello'); 1 row inserted/updated/deleted ij> select cast(c as varchar(20)) from t; 1 -------------------- hello ij> drop table t; 0 rows inserted/updated/deleted ij> -- show multiconnect ability; db name is wombat, reuse it... -- assumes ij.protocol is appropriately set... connect 'wombat' as wombat; ij(WOMBAT)> show connections; CONNECTION0 - jdbc:derby:wombat WOMBAT* - jdbc:derby:wombat * = current connection ij(WOMBAT)> set connection connection0; ij(CONNECTION0)> show connections; CONNECTION0* - jdbc:derby:wombat WOMBAT - jdbc:derby:wombat * = current connection ij(CONNECTION0)> set connection wombat; ij(WOMBAT)> disconnect; ij> show connections; CONNECTION0 - jdbc:derby:wombat No current connection ij> set connection connection0; ij> show connections; CONNECTION0* - jdbc:derby:wombat * = current connection ij>