ij(CONNECTION1)> -- -- 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(CONNECTION1)> insert into t values (3), (4); 2 rows inserted/updated/deleted ij(CONNECTION1)> prepare s as 'select * from t'; ij(CONNECTION1)> execute s; I ----------- 3 4 ij(CONNECTION1)> remove s; ij(CONNECTION1)> -- now it won't find s execute s; IJ ERROR: Unable to establish prepared statement S@CONNECTION1 ij(CONNECTION1)> prepare s as 'select * from t where i=?'; ij(CONNECTION1)> -- fails, needs parameter execute s; ERROR 07000: At least one parameter to the current statement is uninitialized. ij(CONNECTION1)> -- works, finds value execute s using 'values 3'; IJ WARNING: Autocommit may close using result set I ----------- 3 ij(CONNECTION1)> prepare t as 'values 3'; ij(CONNECTION1)> -- same as last execute execute s using t; IJ WARNING: Autocommit may close using result set I ----------- 3 ij(CONNECTION1)> -- same as last execute execute 'select * from t where i=?' using 'values 3'; IJ WARNING: Autocommit may close using result set I ----------- 3 ij(CONNECTION1)> -- same as last execute execute 'select * from t where i=?' using t; IJ WARNING: Autocommit may close using result set I ----------- 3 ij(CONNECTION1)> -- 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(CONNECTION1)> -- 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(CONNECTION1)> -- with autocommit off, extra rows are processed and no warning results autocommit off; ij(CONNECTION1)> execute 'select * from t where i=?' using 'values 3,4'; I ----------- 3 I ----------- 4 ij(CONNECTION1)> execute 'select * from t where i=?' using 'values 3'; I ----------- 3 ij(CONNECTION1)> autocommit on; ij(CONNECTION1)> -- 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(CONNECTION1)> -- 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(CONNECTION1)> -- note that the using part was, however, executed... drop table s; 0 rows inserted/updated/deleted ij(CONNECTION1)> -- 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(CONNECTION1)> insert into t2558 values (3), (4); 2 rows inserted/updated/deleted ij(CONNECTION1)> -- 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(CONNECTION1)> 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(CONNECTION1)> execute 'select * from t2558 where i = ? or i = ?' using 'values (3,4)'; IJ WARNING: Autocommit may close using result set I ----------- 3 4 ij(CONNECTION1)> -- bug 5926 - make sure the using clause result set got closed drop table t; 0 rows inserted/updated/deleted ij(CONNECTION1)> create table t(c1 int); 0 rows inserted/updated/deleted ij(CONNECTION1)> insert into t values(1); 1 row inserted/updated/deleted ij(CONNECTION1)> execute 'select * from t where c1=?' using 'select * from t where c1=1'; IJ WARNING: Autocommit may close using result set C1 ----------- 1 ij(CONNECTION1)> drop table t; 0 rows inserted/updated/deleted ij(CONNECTION1)> create table t(c1 int); 0 rows inserted/updated/deleted ij(CONNECTION1)> insert into t values(1); 1 row inserted/updated/deleted ij(CONNECTION1)> insert into t values(2); 1 row inserted/updated/deleted ij(CONNECTION1)> execute 'select * from t where c1=?' using 'select * from t where c1>=1'; IJ WARNING: Autocommit may close using result set C1 ----------- 1 ij(CONNECTION1)> drop table t; 0 rows inserted/updated/deleted ij(CONNECTION1)> -- Bug 4694 Test automatic rollback with close of connection -- in ij connect 'wombat'; ij(CONNECTION2)> autocommit off; ij(CONNECTION2)> create table a (a int); 0 rows inserted/updated/deleted ij(CONNECTION2)> select count(*) from a; 1 ----------- 0 ij(CONNECTION2)> disconnect; ij> set connection connection0; WARNING 01J01: Database 'wombat' not created, connection made to existing database instead. ij(CONNECTION0)> select count(*) from a; ERROR 42X05: Table/View 'A' does not exist. ij(CONNECTION0)> create table t ( c char(50)); 0 rows inserted/updated/deleted ij(CONNECTION0)> insert into t values('hello'); 1 row inserted/updated/deleted ij(CONNECTION0)> select cast(c as varchar(20)) from t; 1 -------------------- hello ij(CONNECTION0)> drop table t; 0 rows inserted/updated/deleted ij(CONNECTION0)> -- DERBY-3408: Unknown command error should suggest referring to server docs: show schema; ERROR 42X01: Syntax error: Encountered "show" at line 2, column 1. 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(CONNECTION0)> xxx; ERROR 42X01: Syntax error: Encountered "xxx" at line 1, column 1. 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(CONNECTION0)>