autogeneratedjdbc30.java

derby database source code.good for you.

/*

   Derby - Class org.apache.derbyTesting.functionTests.tests.jdbcapi.autoGeneratedJdbc30

   Copyright 2002, 2005 The Apache Software Foundation or its licensors, as applicable.

   Licensed 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.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Savepoint;
import java.sql.Statement;
import java.sql.SQLException;

import org.apache.derby.tools.ij;
import org.apache.derby.tools.JDBCDisplayUtil;

/**
 * Test the new Auto Generated Keys feature in jdbc 30 for Statement and PreparedStatement.
 *
 * @author mamta
 */

public class autoGeneratedJdbc30 { 
	public static void main(String[] args) {

		Connection con = null;
		Statement  s;
		PreparedStatement  ps;

		System.out.println("Test autoGeneratedJdbc30 starting");

		try
		{
			ij.getPropertyArg(args);
			con = ij.startJBMS();

			s = con.createStatement();
			/* Create the tables and do any other set-up */
			setUpTest(s);
			con.setAutoCommit(false);

			positiveTests(con);

			doTest1920(s, con);

			negativeTests(con);

			con.close();
		}
		catch (SQLException e) {
			JDBCDisplayUtil.ShowSQLException(System.out,e);
		}
		catch (Throwable e) {
			System.out.println("FAIL -- unexpected exception:");
			e.printStackTrace(System.out);
		}

		System.out.println("Test autoGeneratedJdbc30 finished");
	}

	public static String MyMethodWithNoInsert() throws SQLException {
		System.out.println("Inside server-side method with no insert statement");
		Connection conn = DriverManager.getConnection("jdbc:default:connection");
		Statement s = conn.createStatement();
		s.executeQuery("select * from t11");
		return "true";
	}

	public static String MyMethodWithInsert() throws SQLException {
		System.out.println("Inside server-side method with couple insert statement with various combination of auto generated keys flag");
		Connection conn = DriverManager.getConnection("jdbc:default:connection");
		Statement s = conn.createStatement();
		s.execute("insert into t11(c11) values(999)", Statement.RETURN_GENERATED_KEYS);
		dumpRS(s.getGeneratedKeys());
		s.execute("insert into t11(c11) values(999)", Statement.NO_GENERATED_KEYS);
		try {
			dumpRS(s.getGeneratedKeys());
		} catch (SQLException e) {
			dumpExpectedSQLExceptions(e);
		}
		dumpRS(s.executeQuery("select * from t11"));
		return "true";
	}

	public static int count(Connection con, Statement s) throws SQLException {
		int count = 0;
		ResultSet rs = s.executeQuery("select count(*) from t11");
		rs.next();
		count = rs.getInt(1);
		rs.close();
		return count;
	}

	//Set up the test by creating the table used by the rest of the test.
	public static void setUpTest(Statement s)
					throws SQLException {
		/* Create a table */
		// set by increment not yet supported for create table...
		// does not matter for purpose of this test.
		// s.execute("create table t11 (c11 int, c12 int default set increment by 1)");
		s.execute("create table t11 (c11 int, c12 int generated always as identity)");
		s.execute("alter table t11 alter c12 set increment by 1");
		s.execute("create table t21 (c21 int not null unique, c22 char(5))");
		s.execute("insert into t21 values(21, 'true')");
		s.execute("insert into t21 values(22, 'true')");
		s.execute("create table t31 (c31 int, c32 int generated always as identity, c33 int default 2)");
		s.execute("alter table t31 alter c32 set increment by 1");
	}

	public static void dumpExpectedSQLExceptions (SQLException se) {
		System.out.println("PASS -- expected exception");
		while (se != null)
		{
			System.out.println("SQLSTATE("+se.getSQLState()+"): "+se.getMessage());
			se = se.getNextException();
        }
    }


	// lifted from the metadata test	
	public static void dumpRS(ResultSet s) throws SQLException 
	{
		if (s == null)
		{
			System.out.println("<NULL>");
			return;
		}

		ResultSetMetaData rsmd = s.getMetaData();

		// Get the number of columns in the result set
		int numCols = rsmd.getColumnCount();

		if (numCols <= 0) 
		{
			System.out.println("(no columns!)");
			return;
		}

		StringBuffer heading = new StringBuffer("\t ");
		StringBuffer underline = new StringBuffer("\t ");

		int len;
		// Display column headings
		for (int i=1; i<=numCols; i++) 
		{
			if (i > 1) 
			{
				heading.append(",");
				underline.append(" ");
			}
			len = heading.length();
			heading.append(rsmd.getColumnLabel(i));
			len = heading.length() - len;
			for (int j = len; j > 0; j--)
			{
				underline.append("-");
			}
		}
		System.out.println(heading.toString());
		System.out.println(underline.toString());
		
	
		StringBuffer row = new StringBuffer();
		// Display data, fetching until end of the result set
		while (s.next()) 
		{
			row.append("\t{");
			// Loop through each column, getting the
			// column data and displaying
			for (int i=1; i<=numCols; i++) 
			{
				if (i > 1) row.append(",");
				row.append(s.getString(i));
			}
			row.append("}\n");
		}
		System.out.println(row.toString());
		s.close();
	}

	public static void disabledTestsBecauseOfBug5580(Statement s, Connection con, PreparedStatement ps) throws SQLException
	{
			// re-enable following test whenever bug 5580 fixed
			//Test11 - insert select with columnIndexes[] array - bug 5580
			System.out.println("Test11 - insert select with columnIndexes[] array");
			int colPositions[] = new int[1];
      colPositions[0] = 1;
			s.execute("insert into t11(c11) select c21 from t21", colPositions);
			dumpRS(s.getGeneratedKeys());
			s.executeUpdate("insert into t11(c11) select c21 from t21", colPositions);
			dumpRS(s.getGeneratedKeys());

			System.out.println("Test11ps - insert select with columnIndexes[] array");
			ps = con.prepareStatement("insert into t11(c11) select c21 from t21", colPositions);
			ps.execute();
			dumpRS(ps.getGeneratedKeys());
			ps.executeUpdate();
			dumpRS(ps.getGeneratedKeys());

			// BUG 4836 Hey, actually fetch a generated column!!!!!!!!!!!!!
			colPositions[0] = 2;
			s.executeUpdate("insert into t11(c11) select c21 from t21", colPositions);
			try {
				dumpRS(s.getGeneratedKeys());
			} catch (SQLException e) {
				dumpExpectedSQLExceptions(e);
			}
			ps = con.prepareStatement("insert into t11(c11) select c21 from t21", colPositions);
			ps.executeUpdate();
			dumpRS(ps.getGeneratedKeys());

			//Verify data in the table
			if(count(con,s) != 12) {
				System.out.println("Test failed");
				return;
			}
			s.execute("delete from t11");

			//Test12 - insert select with columnIndexes[] array with duplicate column positions
			System.out.println("Test12 - insert select with columnIndexes[] array with duplicate column positions");
			colPositions = new int[2];
      colPositions[0] = 1;
      colPositions[1] = 1;
			s.execute("insert into t11(c11) select c21 from t21", colPositions);
			dumpRS(s.getGeneratedKeys());
			s.executeUpdate("insert into t11(c11) select c21 from t21", colPositions);
			dumpRS(s.getGeneratedKeys());

			System.out.println("Test12ps - insert select with columnIndexes[] array with duplicate column positions");
			ps = con.prepareStatement("insert into t11(c11) select c21 from t21", colPositions);
			ps.execute();
			dumpRS(ps.getGeneratedKeys());
			ps.executeUpdate();
			dumpRS(ps.getGeneratedKeys());

			//Verify data in the table
			if(count(con,s) != 8) {
				System.out.println("Test failed");
				return;
			}
			s.execute("delete from t11");

			//Test13 - insert select with columnIndexes[] array with invalid column position
			System.out.println("Test13 - insert select with columnIndexes[] array with invalid column position");
      colPositions[0] = 3;
			try {
				s.execute("insert into t11(c11) select c21 from t21", colPositions);
			} catch (SQLException e) {
				dumpExpectedSQLExceptions(e);
			}
			try {
				dumpRS(s.getGeneratedKeys());
			} catch (SQLException e) {
				dumpExpectedSQLExceptions(e);
			}
			try {
				s.executeUpdate("insert into t11(c11) select c21 from t21", colPositions);
			} catch (SQLException e) {
				dumpExpectedSQLExceptions(e);
			}
			try {
        dumpRS(s.getGeneratedKeys());
			} catch (SQLException e) {
				dumpExpectedSQLExceptions(e);
			}

			System.out.println("Test13ps - insert select with columnIndexes[] array with invalid column position");
			try {
				ps = con.prepareStatement("insert into t11(c11) select c21 from t21", colPositions);
				ps.execute();
			} catch (SQLException e) {
				dumpExpectedSQLExceptions(e);
			}
			try {
				dumpRS(ps.getGeneratedKeys());
			} catch (SQLException e) {
				dumpExpectedSQLExceptions(e);
			}
			try {
				ps.executeUpdate();
			} catch (SQLException e) {
				dumpExpectedSQLExceptions(e);
			}
			try {
				dumpRS(ps.getGeneratedKeys());
			} catch (SQLException e) {
				dumpExpectedSQLExceptions(e);
			}

			//Verify data in the table
			if(count(con,s) != 0) {
				System.out.println("Test failed");