resultset.java

derby database source code.good for you.

					dumpRS(s.executeQuery(completeNullIfString + ") from AllDataTypesTable"));
				} catch (SQLException e)
				{
					dumpSQLExceptions(e);
				}
			}
		}
	}

	public static void tablesForTestingAllDatatypesCombinations( Connection conn) throws Throwable
	{
		System.out.println("Set up by creating table for testing all datatypes combinations");

		Statement s = conn.createStatement();

		try {
			s.executeUpdate("DROP TABLE AllDataTypesTable");
		}
		catch(SQLException se) {}

		StringBuffer createSQL = new StringBuffer("create table AllDataTypesTable (");
		for (int type = 0; type < SQLTypes.length - 1; type++)
		{
			createSQL.append(ColumnNames[type] + " " + SQLTypes[type] + ",");
		}
		createSQL.append(ColumnNames[SQLTypes.length - 1] + " " + SQLTypes[SQLTypes.length - 1] + ")");
		System.out.println(createSQL);
		s.executeUpdate(createSQL.toString());

		for (int row = 0; row < SQLData[0].length; row++)
		{
			createSQL = new StringBuffer("insert into AllDataTypesTable values(");
			for (int type = 0; type < SQLTypes.length - 1; type++)
			{
				createSQL.append(SQLData[type][row] + ",");
			}
			createSQL.append(SQLData[SQLTypes.length - 1][row]+")");
			System.out.println(createSQL);
			s.executeUpdate(createSQL.toString());
		}

		s.close();
		conn.commit();
	}

	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("COL"+i);
			heading.append("(datatype : " + rsmd.getColumnTypeName(i));
			heading.append(", precision : " + rsmd.getPrecision(i));
			heading.append(", scale : " + rsmd.getScale(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(",");
				try{
				row.append(s.getString(i));
				} catch(SQLException ex){
					if (ex.getSQLState().equals("22005")) 
						row.append("Invalid Conversion Error\n");
					else throw ex;
				}
			}
			row.append("}\n");
		}
		System.out.println(row.toString());
		s.close();
	}


	static private void testCorrelationNamesAndMetaDataCalls(Connection conn) throws Exception
	{
		Statement stmt = conn.createStatement();
		stmt.executeUpdate("create table s (a int, b int, c int, d int, e int, f int)");
		stmt.executeUpdate("insert into s values (0,1,2,3,4,5)");
		stmt.executeUpdate("insert into s values (10,11,12,13,14,15)");
		System.out.println("Run select * from s ss (f, e, d, c, b, a) where f = 0 and then try getTableName and getSchemaName on columns");
		ResultSet rs = stmt.executeQuery("select * from s ss (f, e, d, c, b, a) where f = 0");
    rs.next();
    ResultSetMetaData met = rs.getMetaData();
		System.out.println("getTableName(1): "+met.getTableName(1));
		System.out.println("getSchemaName(1): "+met.getSchemaName(1));

		System.out.println("Run select * from (select * from s) a and then try getTableName and getSchemaName on columns");
		rs = stmt.executeQuery("select * from (select * from s) a");
    rs.next();
    met = rs.getMetaData();
		System.out.println("getTableName(1): "+met.getTableName(1));
		System.out.println("getSchemaName(1): "+met.getSchemaName(1));

		stmt.executeUpdate("create schema s1");
		stmt.executeUpdate("create table s1.t1 (c11 int, c12 int)");
		stmt.executeUpdate("insert into s1.t1 values (11, 12), (21, 22)");
		System.out.println("Run select * from s1.t1 as abc and then try getTableName and getSchemaName on columns");
		rs = stmt.executeQuery("select * from s1.t1 as abc");
		met = rs.getMetaData();
		System.out.println("Table name of first column is " + met.getTableName(1));
		System.out.println("Schema name of first column is " + met.getSchemaName(1));
		System.out.println("Table name of second column is " + met.getTableName(2));
		System.out.println("Schema name of second column is " + met.getSchemaName(2));
		System.out.println("Run select abc.c11 from s1.t1 as abc and then try getTableName and getSchemaName on columns");
		rs = stmt.executeQuery("select abc.c11 from s1.t1 as abc");
		met = rs.getMetaData();
		System.out.println("Table name of first column is " + met.getTableName(1));
		System.out.println("Schema name of first column is " + met.getSchemaName(1));
		System.out.println("Run select bcd.a, abc.c11 from s1.t1 as abc, s as bcd and then try getTableName and getSchemaName on columns");
		rs = stmt.executeQuery("select bcd.a, abc.c11 from s1.t1 as abc, s as bcd");
		met = rs.getMetaData();
		System.out.println("Table name of first column is " + met.getTableName(1));
		System.out.println("Schema name of first column is " + met.getSchemaName(1));
		System.out.println("Table name of second column is " + met.getTableName(2));
		System.out.println("Schema name of second column is " + met.getSchemaName(2));

		stmt.executeUpdate("create schema app1");
		stmt.executeUpdate("create table app1.t1 (c11 int, c12 int)");
		stmt.executeUpdate("insert into app1.t1 values (11, 12), (21, 22)");
		stmt.executeUpdate("create schema app2");
		stmt.executeUpdate("create table app2.t1 (c11 int, c12 int)");
		stmt.executeUpdate("insert into app2.t1 values (11, 12), (21, 22)");
		System.out.println("Run select app1.t1.c11, app2.t1.c11 from app1.t1, app2.t1 and then try getTableName and getSchemaName on columns");
		rs = stmt.executeQuery("select app1.t1.c11, app2.t1.c11 from app1.t1, app2.t1");
		met = rs.getMetaData();
		System.out.println("Table name of first column is " + met.getTableName(1));
		System.out.println("Schema name of first column is " + met.getSchemaName(1));
		System.out.println("Table name of second column is " + met.getTableName(2));
		System.out.println("Schema name of second column is " + met.getSchemaName(2));
	}

	static private void doTheTests() throws Exception
	{

	}

	static private void showLocksForAutoCommitSelect(Connection conn, Statement stmt, int action) throws Exception {

		ResultSet rs = stmt.executeQuery("select i,b from bug4810");
		rs.next();
		System.out.println("  bug4810 " + rs.getInt(1) + ", " + rs.getInt(2));
		rs.next();
		System.out.println("  bug4810 " + rs.getInt(1) + ", " + rs.getInt(2));

		if (action == 1) {
			System.out.println("commit");
			conn.commit();
		} else if (action == 2) {
			System.out.println("rollback");
			conn.rollback();
		}

		showLocks();

		try {

			rs.next();
			System.out.println("  bug4810 " + rs.getInt(1) + ", " + rs.getInt(2));
		} catch (SQLException sqle) {
			JDBCTestDisplayUtil.ShowCommonSQLException(System.out, sqle);		}
		showLocks();
		rs.close();

		showLocks();

	}

	private static void showLocks() throws Exception {
		System.out.println("  LOCK TABLE");
		Connection con2 = ij.startJBMS();
		PreparedStatement ps2 = con2.prepareStatement("select XID, count(*) from new org.apache.derby.diag.LockTable() as L group by XID");
		ResultSet rs2 = ps2.executeQuery();

		while (rs2.next()) {
			if (rs2.getInt(2) > 0) {
				System.out.println("Locks are held");
			} else if (rs2.getInt(2) == 0) {
				System.out.println("No locks to hold");
			}
		}
		
		rs2.close();
		ps2.close();
		con2.close();
	}

	static private void dumpSQLExceptions (SQLException se) {
		while (se != null) {
			JDBCTestDisplayUtil.ShowCommonSQLException(System.out, se);
			se = se.getNextException();
		}
	}

	static private String showBytes(byte[] bytes) {
		if (bytes == null)
			return "null";

		StringBuffer s = new StringBuffer("0x");
		s.ensureCapacity(2+2*bytes.length);
		for (int i=0;i<bytes.length;i++) {
			int hi = (bytes[i] & 0xf0) >>> 4;
			int lo = (bytes[i] & 0x0f);
			s.append(representation[hi]);
			s.append(representation[lo]);
		}
		return s.toString();
	}

	static final char[] representation =
		{ '0', '1', '2', '3', '4', '5', '6', '7', '8', '9',
		  'A', 'B', 'C', 'D', 'E', 'F' } ;


	/**
		Test that for mutable types returned from a ResultSet we do not
		re-use the type, thus conusing any application that holds onto
		the returned value. Possible mutable types are

		byte[]
		java.sql.Date
		java.sql.Timestamp
		java.sql.Time

		The stream types are mutable but they are closed once the appliction
		moves to the next column or row.
	*/
	private static void testMutableValues(Connection conn) throws SQLException 
	{
		System.out.println("START testMutableValues");

		Statement s = conn.createStatement();

		s.execute("CREATE TABLE MUTABLE.T1(C CHAR(10) FOR BIT DATA, V VARCHAR(10) FOR BIT DATA, L LONG VARCHAR FOR BIT DATA, D DATE, T TIME, TS TIMESTAMP)");
		s.execute("INSERT INTO MUTABLE.T1 VALUES (X'34', X'4de5', X'5e3a67', '1992-01-01', '17.05.00', '2003-3-1-17.05.43.123456')");
		s.execute("INSERT INTO MUTABLE.T1 VALUES (X'93', X'4825', X'6e3a64', '1992-01-03', '17.06.00', '2007-3-1-17.05.43.123456')");
		s.execute("INSERT INTO MUTABLE.T1 VALUES (X'34', X'4de5', X'5e3a67', '1992-01-01', '17.05.00', '2003-3-1-17.05.43.123456')");

		{
		ResultSet rs = s.executeQuery("SELECT C,V,L,D,T,TS FROM MUTABLE.T1");
		java.util.ArrayList[] values = new java.util.ArrayList[6];
		for (int i = 0; i < values.length; i++) {
			values[i] = new java.util.ArrayList();
		}
		System.out.println("CHECKING on getXXX()");
		int rc = 0;
		while (rs.next()) {
			rc++;
			System.out.println("ROW " + rc);

			checkMutableValue(values[0], 1, rs.getBytes(1));
			checkMutableValue(values[1], 2, rs.getBytes(2));
			checkMutableValue(values[2], 3, rs.getBytes(3));

			checkMutableValue(values[3], 4, rs.getDate(4));
			checkMutableValue(values[4], 5, rs.getTime(5));
			checkMutableValue(values[5], 6, rs.getTimestamp(6));

		}
		rs.close();
		}
		{
		ResultSet rs = s.executeQuery("SELECT C,V,L,D,T,TS FROM MUTABLE.T1");
		java.util.ArrayList[] values = new java.util.ArrayList[6];
		for (int i = 0; i < values.length; i++) {
			values[i] = new java.util.ArrayList();
		}
		System.out.println("CHECKING on getObject()");
		int rc = 0;
		while (rs.next()) {
			rc++;
			System.out.println("ROW " + rc);

			for (int i = 0; i < 6; i++)
				checkMutableValue(values[i], i+1, rs.getObject(i+1));
		}
		rs.close();
		}

		s.execute("DROP TABLE MUTABLE.T1");

		System.out.println("COMPLETE testMutableValues");
	}

	private static void checkMutableValue(java.util.ArrayList list, int col, Object value) {

		int same = -1;
		int equals = -1;
		for (int i = 0; i < list.size(); i++) {
			Object previous = list.get(i);
			if (previous == value)
				same = i+1;
			if (previous.equals(value))
				equals = i+1;
		}

		if (same != -1)
			System.out.println("FAIL SAME OBJECT RETURNED column " + col + " existing " + same);
		if (equals != -1)
			System.out.println("OK EQUALITY OBJECT RETURNED column " + col + " existing " + equals);

		list.add(value);
	}
}