java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/StatementJdbc20Test.java - derby - Git at Google

 /*

 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)");
     }

 }
	/*

	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)");
	}

	}