| /* |
| |
| Derby - Class org.apache.derbyTesting.functionTests.tests.jdbcapi.StatementJdbc20Test |
| |
| 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. |
| |
| */ |
| |
| package org.apache.derbyTesting.functionTests.tests.jdbcapi; |
| |
| import java.sql.CallableStatement; |
| import java.sql.Connection; |
| import java.sql.DriverManager; |
| import java.sql.PreparedStatement; |
| import java.sql.ResultSet; |
| import java.sql.SQLException; |
| import java.sql.Statement; |
| import junit.framework.Test; |
| import org.apache.derbyTesting.junit.BaseJDBCTestCase; |
| import org.apache.derbyTesting.junit.BaseTestSuite; |
| import org.apache.derbyTesting.junit.CleanDatabaseTestSetup; |
| import org.apache.derbyTesting.junit.JDBC; |
| import org.apache.derbyTesting.junit.TestConfiguration; |
| |
| /** |
| * Test of additional methods in JDBC2.0 methods in statement and |
| * resultset classes. |
| * This test converts the old jdbcapi/statementJdbc20.java test |
| * to JUnit. |
| */ |
| public class StatementJdbc20Test extends BaseJDBCTestCase { |
| |
| private static final String METHOD_NOT_ALLOWED = "XJ016"; |
| private static final String CLOSED_STATEMENT = "XJ012"; |
| |
| /** |
| * Create a test with the given name. |
| * |
| * @param name name of the test. |
| */ |
| public StatementJdbc20Test(String name) { |
| super(name); |
| } |
| |
| /** |
| * Create suite containing client and embedded tests and to run |
| * all tests in this class |
| */ |
| public static Test suite() { |
| BaseTestSuite suite = new BaseTestSuite("StatementJdbc20Test"); |
| suite.addTest(baseSuite("StatementJdbc20Test:embedded")); |
| suite.addTest( |
| TestConfiguration.clientServerDecorator( |
| baseSuite("StatementJdbc20Test:client"))); |
| |
| return suite; |
| } |
| |
| private static Test baseSuite(String name) { |
| BaseTestSuite suite = new BaseTestSuite(name); |
| |
| suite.addTestSuite(StatementJdbc20Test.class); |
| |
| return new CleanDatabaseTestSetup(suite) { |
| /** |
| * Creates the tables used in the test |
| * cases. |
| * |
| * @exception SQLException if a database error occurs |
| */ |
| protected void decorateSQL(Statement stmt) throws SQLException { |
| |
| Connection conn = getConnection(); |
| |
| /** |
| * Creates the table used in the test cases. |
| * |
| */ |
| stmt.execute("create table tab1 (i int, s smallint, r real)"); |
| stmt.executeUpdate("insert into tab1 values(1, 2, 3.1)"); |
| |
| stmt.execute |
| ( |
| "create procedure dynamic_results() " + |
| "language java parameter style java external name '" + |
| StatementJdbc20Test.class.getName() + ".dynamicResults' " + |
| "dynamic result sets 2" |
| ); |
| |
| stmt.execute( "create table t_autogen( a int generated always as identity, b int )" ); |
| } |
| }; |
| } |
| |
| /** |
| * Testing wrong values for setFetchSize |
| * and setFetchDirection. |
| * |
| * @exception SQLException if error occurs |
| */ |
| public void testWrongVaues() throws SQLException { |
| |
| Statement stmt = createStatement(); |
| |
| stmt.setFetchSize(25); |
| stmt.setFetchDirection(ResultSet.FETCH_REVERSE); |
| stmt.setEscapeProcessing(true); |
| |
| //Error testing : set wrong values .. |
| try { |
| stmt.setFetchSize(-1000); |
| fail("setFetchSize(-1000) expected to fail"); |
| } catch(SQLException e) { |
| assertSQLState("XJ065", e); |
| } |
| |
| try { |
| stmt.setFetchDirection(-1000); |
| fail("setFetchDirection(-1000) expected to fail"); |
| } catch(SQLException e){ |
| assertSQLState("XJ064", e); |
| } |
| |
| assertEquals(stmt.getFetchSize(), 25); |
| assertEquals(stmt.getFetchDirection(), ResultSet.FETCH_REVERSE); |
| |
| stmt.close(); |
| } |
| /** |
| * Tests reading data from database |
| * |
| * @exception SQLException if error occurs |
| */ |
| public void testReadingData() throws SQLException { |
| |
| Statement stmt = createStatement(); |
| ResultSet rs; |
| |
| // read the data just for the heck of it |
| rs = stmt.executeQuery("select * from tab1"); |
| while (rs.next()) { |
| assertEquals(rs.getInt(1), 1); |
| assertEquals(rs.getShort(2), 2); |
| assertEquals(rs.getDouble(3), 3.1, 0.01); |
| } |
| |
| rs.close(); |
| stmt.close(); |
| } |
| /** |
| * Tests values local to result set and get them back |
| * |
| * @exception SQLException if error occurs |
| */ |
| public void testLocalValuesOfResultSet() throws SQLException { |
| |
| Statement stmt = createStatement(); |
| ResultSet rs; |
| |
| stmt.setFetchSize(25); |
| stmt.setFetchDirection(ResultSet.FETCH_REVERSE); |
| stmt.setEscapeProcessing(true); |
| |
| rs = stmt.executeQuery("select * from tab1"); |
| // Get the constatnts for a result set |
| assertEquals(rs.getFetchSize(), 25); |
| assertEquals(rs.getFetchDirection(), ResultSet.FETCH_REVERSE); |
| |
| // change values local to result set and get them back |
| rs.setFetchSize(250); |
| try{ |
| rs.setFetchDirection(ResultSet.FETCH_FORWARD); |
| }catch(SQLException e){ |
| |
| if (usingEmbedded()) |
| assertSQLState("XJ061", e); |
| else |
| assertSQLState("XJ125", e); |
| } |
| |
| assertEquals(rs.getFetchSize(), 250); |
| assertEquals(rs.getFetchDirection(), ResultSet.FETCH_REVERSE); |
| |
| // Verify that fetch size can be set larger than maxRows |
| stmt.setMaxRows(10); |
| rs.setFetchSize(100); |
| |
| //Error testing : set wrong values .. |
| try{ |
| rs.setFetchSize(-2000); |
| fail("setFetchSize(-2000) expected to fail"); |
| } catch(SQLException e){ |
| assertSQLState("XJ062", e); |
| } |
| |
| try{ |
| rs.setFetchDirection(-2000); |
| } catch(SQLException e){ |
| |
| if (usingEmbedded()) |
| assertSQLState("XJ061", e); |
| else |
| assertSQLState("XJ125", e); |
| } |
| |
| // set the fetch size values to zero .. to ensure |
| // error condtions are correct ! |
| |
| rs.setFetchSize(0); |
| stmt.setFetchSize(0); |
| |
| rs.close(); |
| } |
| /** |
| * Tests creating tables with executeQuery which is |
| * not allowed on statements that return a row count |
| * |
| * @exception SQLException |
| * if error occurs |
| */ |
| public void testCreateTableWithExecuteQuery() throws SQLException { |
| |
| Statement stmt = createStatement(); |
| ResultSet rs; |
| |
| //RESOLVE - uncomment tests in 3.5 |
| // executeQuery() not allowed on statements |
| // that return a row count |
| try { |
| stmt.executeQuery("create table trash(c1 int)"); |
| } catch (SQLException e) { |
| if (usingEmbedded()) |
| assertSQLState("X0Y78", e); |
| else |
| assertSQLState("XJ207", e); |
| } |
| |
| // verify that table was not created |
| try { |
| rs = stmt.executeQuery("select * from trash"); |
| System.out.println("select from trash expected to fail"); |
| } catch (SQLException e) { |
| assertSQLState("42X05", e); |
| } |
| |
| // executeUpdate() not allowed on statements |
| // that return a ResultSet |
| try { |
| stmt.executeUpdate("values 1"); |
| } catch (SQLException e) { |
| assertSQLState("X0Y79", e); |
| } |
| |
| stmt.close(); |
| commit(); |
| } |
| |
| /** |
| * Test the following clarification made by JDBC 4.1: You should |
| * raise an exception when addBatch(String) is called on a PreparedStatement |
| * or a CallableStatement. |
| */ |
| public void testAddBatchClarification_jdbc4_1() throws SQLException |
| { |
| PreparedStatement ps = prepareStatement( "select * from sys.systables" ); |
| CallableStatement cs = prepareCall( "CALL SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1)" ); |
| |
| try { |
| ps.addBatch( "select * from sys.systables" ); |
| fail( "Oops. ps.addBatch() worked." ); |
| } |
| catch (SQLException se) |
| { |
| assertSQLState( METHOD_NOT_ALLOWED, se ); |
| } |
| |
| try { |
| cs.addBatch( "CALL SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1)" ); |
| fail( "Oops. cs.addBatch() worked." ); |
| } |
| catch (SQLException se) |
| { |
| assertSQLState( METHOD_NOT_ALLOWED, se ); |
| } |
| } |
| |
| /** |
| * Test the closeOnCompletion() and isCloseOnCompletion() methods |
| * added by JDBC 4.1. |
| */ |
| public void testCompletionClosure_jdbc4_1() throws Exception |
| { |
| if ( JDBC.vmSupportsJSR169() ) { return; } |
| |
| vetClosure( false, false ); |
| vetClosure( false, true ); |
| vetClosure( true, false ); |
| vetClosure( true, true ); |
| } |
| private void vetClosure( boolean closeOnCompletion, boolean delayClosureCall ) throws Exception |
| { |
| vetClosedSelect( closeOnCompletion, delayClosureCall ); |
| vetClosedCall( closeOnCompletion, delayClosureCall ); |
| vetClosedPS( closeOnCompletion, delayClosureCall ); |
| vetClosedAutoGen( closeOnCompletion, delayClosureCall ); |
| } |
| private void vetClosedSelect( boolean closeOnCompletion, boolean delayClosureCall ) throws Exception |
| { |
| Statement stmt = createStatement(); |
| ResultSet rs; |
| |
| println( "Verifying SELECT wrapper on " + stmt.getClass().getName() + |
| " with closeOnCompletion = " + closeOnCompletion + |
| " and delayClosureCall = " + delayClosureCall ); |
| Wrapper41Statement wrapper = new Wrapper41Statement( stmt ); |
| |
| if ( !delayClosureCall ) |
| { setCloseOnCompletion( wrapper, closeOnCompletion ); } |
| |
| rs = stmt.executeQuery( "select * from tab1" ); |
| |
| if ( delayClosureCall ) |
| { setCloseOnCompletion( wrapper, closeOnCompletion ); } |
| |
| rs.close(); |
| |
| assertEquals( closeOnCompletion, wrapper.isClosed() ); |
| |
| vetSuccessfulClosure( wrapper, closeOnCompletion ); |
| } |
| private void vetClosedCall( boolean closeOnCompletion, boolean delayClosureCall ) throws Exception |
| { |
| Statement stmt = createStatement(); |
| ResultSet rs; |
| |
| println( "Verifying CALL wrapper on " + stmt.getClass().getName() + |
| " with closeOnCompletion = " + closeOnCompletion + |
| " and delayClosureCall = " + delayClosureCall ); |
| Wrapper41Statement wrapper = new Wrapper41Statement( stmt ); |
| |
| if ( !delayClosureCall ) |
| { setCloseOnCompletion( wrapper, closeOnCompletion ); } |
| |
| assertTrue( stmt.execute( "call dynamic_results()" ) ); |
| |
| assertFalse( wrapper.isClosed() ); |
| |
| ResultSet rs1 = stmt.getResultSet(); |
| |
| if ( delayClosureCall ) |
| { setCloseOnCompletion( wrapper, closeOnCompletion ); } |
| |
| assertTrue( stmt.getMoreResults() ); // implicitly closes rs1 |
| assertFalse( wrapper.isClosed() ); |
| |
| ResultSet rs2 = stmt.getResultSet(); |
| rs2.close(); |
| assertEquals( closeOnCompletion, wrapper.isClosed() ); |
| |
| vetSuccessfulClosure( wrapper, closeOnCompletion ); |
| } |
| private void vetClosedPS( boolean closeOnCompletion, boolean delayClosureCall ) throws Exception |
| { |
| PreparedStatement ps = getConnection().prepareStatement( "select * from tab1" ); |
| ResultSet rs; |
| |
| println( "Verifying PreparedStatement wrapper on " + ps.getClass().getName() + |
| " with closeOnCompletion = " + closeOnCompletion + |
| " and delayClosureCall = " + delayClosureCall ); |
| Wrapper41Statement wrapper = new Wrapper41Statement( ps ); |
| |
| if ( !delayClosureCall ) |
| { setCloseOnCompletion( wrapper, closeOnCompletion ); } |
| |
| rs = ps.executeQuery(); |
| |
| if ( delayClosureCall ) |
| { setCloseOnCompletion( wrapper, closeOnCompletion ); } |
| |
| rs.close(); |
| |
| assertEquals( closeOnCompletion, wrapper.isClosed() ); |
| |
| vetSuccessfulClosure( wrapper, closeOnCompletion ); |
| |
| if ( !wrapper.isClosed() ) { ps.close(); } |
| } |
| private void vetClosedAutoGen( boolean closeOnCompletion, boolean delayClosureCall ) throws Exception |
| { |
| Statement stmt = createStatement(); |
| ResultSet rs; |
| |
| println( "Verifying AUTOGENERATED KEYS wrapper on " + stmt.getClass().getName() + |
| " with closeOnCompletion = " + closeOnCompletion + |
| " and delayClosureCall = " + delayClosureCall ); |
| Wrapper41Statement wrapper = new Wrapper41Statement( stmt ); |
| |
| if ( !delayClosureCall ) |
| { setCloseOnCompletion( wrapper, closeOnCompletion ); } |
| |
| stmt.executeUpdate( "insert into t_autogen( b ) values ( 1 )", Statement.RETURN_GENERATED_KEYS ); |
| rs = stmt.getGeneratedKeys(); |
| |
| if ( delayClosureCall ) |
| { setCloseOnCompletion( wrapper, closeOnCompletion ); } |
| |
| assertFalse( wrapper.isClosed() ); |
| |
| rs.close(); |
| |
| assertEquals( closeOnCompletion, wrapper.isClosed() ); |
| |
| vetSuccessfulClosure( wrapper, closeOnCompletion ); |
| } |
| |
| private void setCloseOnCompletion( Wrapper41Statement wrapper, boolean closeOnCompletion ) |
| throws Exception |
| { |
| assertFalse( wrapper.isCloseOnCompletion() ); |
| if ( closeOnCompletion ) { wrapper.closeOnCompletion(); } |
| assertEquals( closeOnCompletion, wrapper.isCloseOnCompletion() ); |
| } |
| private void vetSuccessfulClosure |
| ( Wrapper41Statement wrapper, boolean closeOnCompletion ) |
| throws Exception |
| { |
| if ( closeOnCompletion ) |
| { |
| try { |
| wrapper.closeOnCompletion(); |
| fail( "Expected closed statement failure." ); |
| } |
| catch (SQLException se) |
| { |
| assertSQLState( CLOSED_STATEMENT, se ); |
| } |
| |
| try { |
| wrapper.isCloseOnCompletion(); |
| fail( "Expected closed statement failure." ); |
| } |
| catch (SQLException se) |
| { |
| assertSQLState( CLOSED_STATEMENT, se ); |
| } |
| } |
| } |
| |
| /** |
| * <p> |
| * Spec clarifications in JDBC 4.1. Verify that an exception is raised if a PreparedStatement |
| * or CallableStatement tries to execute one of the methods which compiles SQL text. |
| * </p> |
| */ |
| public void test_clarification_jdbc4_1() throws Exception |
| { |
| Connection conn = getConnection(); |
| |
| PreparedStatement ps = conn.prepareStatement( "select * from sys.syscolumns" ); |
| CallableStatement cs = conn.prepareCall( "call dynamic_results()" ); |
| |
| vetNotAllowed( ps ); |
| vetNotAllowed( cs ); |
| |
| ps.close(); |
| cs.close(); |
| } |
| private void vetNotAllowed( Statement stmt ) throws Exception |
| { |
| println( "Vetting a " + stmt.getClass().getName() ); |
| |
| try { |
| stmt.execute( "select * from sys.systables" ); |
| failVNA(); |
| } |
| catch (SQLException se) |
| { |
| assertSQLState( METHOD_NOT_ALLOWED, se ); |
| } |
| |
| try { |
| stmt.execute( "select * from sys.systables", Statement.NO_GENERATED_KEYS ); |
| failVNA(); |
| } |
| catch (SQLException se) |
| { |
| assertSQLState( METHOD_NOT_ALLOWED, se ); |
| } |
| |
| try { |
| stmt.execute( "select * from sys.systables", new int[] { 1 } ); |
| failVNA(); |
| } |
| catch (SQLException se) |
| { |
| assertSQLState( METHOD_NOT_ALLOWED, se ); |
| } |
| |
| try { |
| stmt.execute( "select * from sys.systables", new String[] { "COLUMNNAME" } ); |
| failVNA(); |
| } |
| catch (SQLException se) |
| { |
| assertSQLState( METHOD_NOT_ALLOWED, se ); |
| } |
| |
| try { |
| stmt.executeQuery( "select * from sys.systables" ); |
| failVNA(); |
| } |
| catch (SQLException se) |
| { |
| assertSQLState( METHOD_NOT_ALLOWED, se ); |
| } |
| |
| try { |
| stmt.executeUpdate( "insert into tab1 values(1, 2, 3.1)" ); |
| failVNA(); |
| } |
| catch (SQLException se) |
| { |
| assertSQLState( METHOD_NOT_ALLOWED, se ); |
| } |
| |
| try { |
| stmt.executeUpdate( "insert into tab1 values(1, 2, 3.1)", Statement.NO_GENERATED_KEYS ); |
| failVNA(); |
| } |
| catch (SQLException se) |
| { |
| assertSQLState( METHOD_NOT_ALLOWED, se ); |
| } |
| |
| try { |
| stmt.executeUpdate( "insert into tab1 values(1, 2, 3.1)", new int[] { 1 } ); |
| failVNA(); |
| } |
| catch (SQLException se) |
| { |
| assertSQLState( METHOD_NOT_ALLOWED, se ); |
| } |
| |
| try { |
| stmt.executeUpdate( "insert into tab1 values(1, 2, 3.1)", new String[] { "COLUMNNAME" } ); |
| failVNA(); |
| } |
| catch (SQLException se) |
| { |
| assertSQLState( METHOD_NOT_ALLOWED, se ); |
| } |
| } |
| private void failVNA() throws Exception |
| { |
| fail( "Should have failed when run on a PreparedStatement or CallableStatement" ); |
| } |
| |
| /////////////////////////////////////////////////////////////////////// |
| // |
| // PROCEDURES |
| // |
| /////////////////////////////////////////////////////////////////////// |
| |
| |
| /** |
| * Stored procedure which returns 2 ResultSets. |
| */ |
| public static void dynamicResults |
| ( ResultSet[] rs1, ResultSet[] rs2 ) |
| throws SQLException |
| { |
| Connection c = DriverManager.getConnection("jdbc:default:connection"); |
| rs1[0] = c.createStatement().executeQuery("VALUES(1)"); |
| rs2[0] = c.createStatement().executeQuery("VALUES(2)"); |
| } |
| |
| } |