streamingcolumn.java

derby database source code.good for you.

			stmt.execute("insert into t8 select * from t8");

			stmt.executeUpdate("drop table t8");

			stmt.close();
			conn.commit();
		}
		catch (SQLException e) {
			dumpSQLExceptions(e);
		}
		catch (Throwable e) {
			System.out.println("FAIL -- unexpected exception:" + e.toString());
		}

        System.out.println(
            "Finishing streamTest8(conn, " +
            stream1_len + ", " + stream2_len + ")");
	}

    /**
     * long row test of insert/backout case, using setBinaryStream().
     * <p>
     * The heap tries to make rows all fit on one page if possible.  So it
     * first asks raw store to try inserting without overflowing rows or
     * columns.  If that doesn't work it then asks raw store for a mostly
     * empty page and tries to insert it there with overflow, If that doesn't
     * work then an empty page is picked.
     * <p>
     * If input parameters are conn,10,2500 - then the second row inserted
     * will have the 1st column fit, but the second not fit which caused
     * track #2240.
     *
	 * @exception  StandardException  Standard exception policy.
     **/
	static void streamTest9(Connection conn, int stream1_len, int stream2_len)
	{
        System.out.println(
            "Starting streamTest9(conn, " +
            stream1_len + ", " + stream2_len + ")");

		ResultSetMetaData   met;
		ResultSet           rs;
		Statement           stmt;

		String createsql =
            new String(
                "create table t9(a int, b long varchar for bit data, c long varchar for bit data)");

		String insertsql = new String("insert into t9 values (?, ?, ?) ");


		int numStrings = 10;

		byte[][] stream1_byte_array = new byte[numStrings][];
		byte[][] stream2_byte_array = new byte[numStrings][];

		// make string size match input sizes.
		for (int i = 0; i < numStrings; i++)
		{
			stream1_byte_array[i] = new byte[stream1_len];

			for (int j = 0; j < stream1_len; j++)
				stream1_byte_array[i][j] = (byte)('a'+i);

			stream2_byte_array[i] = new byte[stream2_len];
			for (int j = 0; j < stream2_len; j++)
				stream2_byte_array[i][j] = (byte)('A'+i);
		}

		try
		{
			conn.setAutoCommit(false);
			stmt = conn.createStatement();
			stmt.execute(createsql);
			conn.commit();

			PreparedStatement insert_ps = conn.prepareStatement(insertsql);



			for (int i = 0; i < numStrings; i++)
			{
                // create the stream and insert it
                insert_ps.setInt(1, i);

                // create the stream and insert it
                insert_ps.setBinaryStream(
                    2, new ByteArrayInputStream(stream1_byte_array[i]), stream1_len);

                // create the stream and insert it
                insert_ps.setBinaryStream(
                    3, new ByteArrayInputStream(stream2_byte_array[i]), stream2_len);

				insert_ps.executeUpdate();

                // just force a scan of the table, no insert is done.
				String checkSQL =
                    "insert into t9 select * from t9 where a = -6363";
				stmt.execute(checkSQL);
			}

			insert_ps.close();
			conn.commit();


			rs = stmt.executeQuery("select a, b, c from t9" );

			// should return one row.
			while (rs.next())
			{
				// JDBC columns use 1-based counting

				// get the first column as an int
				int a = rs.getInt("a");

				// get the second column as a string
				byte[] resultString = rs.getBytes(2);

                // compare result with expected
                byte[] canon = stream1_byte_array[a];

                if (!byteArrayEquals(
                        canon,        0, canon.length,
                        resultString, 0, resultString.length))
                {
                    // System.out.println(
                    //   "FAIL -- bad result byte array 1:" +
                    //   "canon: " + ByteArray.hexDump(canon) +
                    //   "resultString: " + ByteArray.hexDump(resultString));
                    System.out.println(
                        "FAIL -- bad result byte array 1:" +
                        "canon: " + canon +
                        "resultString: " + resultString);
                }

				// get the second column as a string
				resultString = rs.getBytes(3);

                // compare result with expected
                canon = stream2_byte_array[a];

                if (!byteArrayEquals(
                        canon,        0, canon.length,
                        resultString, 0, resultString.length))
                {
                    // System.out.println(
                    //   "FAIL -- bad result byte array 2:" +
                    //   "canon: " + ByteArray.hexDump(canon) +
                    //   "resultString: " + ByteArray.hexDump(resultString));
                    System.out.println(
                        "FAIL -- bad result byte array 2:" +
                        "canon: " + canon +
                        "resultString: " + resultString);
                }
			}

			rs.close();

			stmt.execute("insert into t9 select * from t9");

			stmt.executeUpdate("drop table t9");

			stmt.close();
			conn.commit();
		}
		catch (SQLException e) {
			dumpSQLExceptions(e);
		}
		catch (Throwable e) {
			System.out.println("FAIL -- unexpected exception:" + e.toString());
		}

        System.out.println(
            "Finishing streamTest9(conn, " +
            stream1_len + ", " + stream2_len + ")");
	}

    /**
     * table with multiple indexes, indexes share columns
     * table has more than 4 rows, insert stream into table
     * compress table and verify that each index is valid
	 * @exception  StandardException  Standard exception policy.
     **/
	private static void streamTest10(Connection conn) {

		ResultSetMetaData met;
		ResultSet rs;
		Statement stmt;
		System.out.println("Testing 10 starts from here");

		try {
			stmt = conn.createStatement();
            //create the table
			stmt.executeUpdate("call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', '1024')");
			stmt.execute("create table tab10 (a int, b int, c long   varchar)");
			stmt.executeUpdate("call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', NULL)");
            //create the indexes which shares columns
			stmt.executeUpdate("call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', '4096')");
            stmt.execute("create index i_a on tab10 (a)");
			stmt.execute("create index i_ab on tab10 (a, b)");
			stmt.executeUpdate("call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', NULL)");

			// insert a null long varchar
			stmt.execute("insert into tab10 values(1, 1, '')");
			// insert a long varchar with a short text string
			stmt.execute("insert into tab10 values(2, 2, 'test data: a string column inserted as an object')");

			//insert stream into table
			for (int i = 0; i < fileName.length; i++) {
				// prepare an InputStream from the file
				File file = new File(fileName[i]);
				fileLength[i] = file.length();
				InputStream fileIn = new FileInputStream(file);

				System.out.println("===> testing " + fileName[i] + " length = "
								   + fileLength[i]);

				// insert a streaming column
				PreparedStatement ps = conn.prepareStatement("insert into tab10 values(?, ?, ?)");
				ps.setInt(1, 100 + i);
				ps.setInt(2, 100 + i);
				ps.setAsciiStream(3, fileIn, (int)fileLength[i]);
				try {//if trying to insert data > 32700, there will be an exception
					ps.executeUpdate();
					System.out.println("No truncation and hence no error");
				}
				catch (SQLException e) {
					if (fileLength[i] > Limits.DB2_LONGVARCHAR_MAXWIDTH && e.getSQLState().equals("22001")) //was getting data longer than maxValueAllowed
						System.out.println("expected exception for data > " + Limits.DB2_LONGVARCHAR_MAXWIDTH + " in length");
					else
						dumpSQLExceptions(e);
				}
				fileIn.close();
			}

			//execute the compress command
            CallableStatement cs = conn.prepareCall(
                "CALL SYSCS_UTIL.SYSCS_COMPRESS_TABLE(?, ?, ?)");
            cs.setString(1, "APP");
            cs.setString(2, "TESTLONGVARCHAR");
            cs.setInt(3, 0);
            cs.execute();

			//do consistency checking
			stmt.execute("CREATE FUNCTION ConsistencyChecker() RETURNS VARCHAR(128) EXTERNAL NAME 'org.apache.derbyTesting.functionTests.util.T_ConsistencyChecker.runConsistencyChecker' LANGUAGE JAVA PARAMETER STYLE JAVA");
			stmt.execute("VALUES ConsistencyChecker()");

			stmt.close();

		}
		catch (SQLException e) {
			dumpSQLExceptions(e);
		}
		catch (Throwable e) {
			System.out.println("FAIL -- unexpected exception:" + e.toString());
		}
        System.out.println("Testing 10 ends in here");
	}

	private static void streamTest11(Connection conn) {

		Statement stmt;

		System.out.println("Test 11 - Can't pass negative length as the stream length for various setXXXStream methods");
		try {
			stmt = conn.createStatement();
			stmt.execute("create table testLongVarCharInvalidStreamLength (a int, b long varchar, c long varchar for bit data)");
			// prepare an InputStream from the file
			File file = new File("extin/short.data");
			InputStream fileIn = new FileInputStream(file);

			PreparedStatement ps = conn.prepareStatement("insert into testLongVarCharInvalidStreamLength values(?, ?, ?)");
			ps.setInt(1, 100);
			try {
				System.out.println("===> testing using setAsciiStream with -2 as length");
				ps.setAsciiStream(2, fileIn, -2); //test specifically for bug 4250
				System.out.println("FAIL -- should have gotten exception for -2 param value to setAsciiStream");
			}
			catch (SQLException e) {
				if ("XJ025".equals(e.getSQLState()))
					System.out.println("PASS -- expected exception:" + e.toString());
				else
					dumpSQLExceptions(e);
			}

			Reader filer = new InputStreamReader(fileIn, "US-ASCII");
			try {
				System.out.println("===> testing using setCharacterStream with -1 as length");
				ps.setCharacterStream(2, filer, -1);
				System.out.println("FAIL -- should have gotten exception for -1 param value to setCharacterStream");
			}
			catch (SQLException e) {
				if ("XJ025".equals(e.getSQLState()))
					System.out.println("PASS -- expected exception:" + e.toString());
				else
					dumpSQLExceptions(e);
			}

			try {
				System.out.println("===> testing using setBinaryStream with -1 as length");
				ps.setBinaryStream(3, fileIn, -1);
				System.out.println("FAIL -- should have gotten exception for -1 param value to setBinaryStream");
			}
			catch (SQLException e) {
				if ("XJ025".equals(e.getSQLState()))
					System.out.println("PASS -- expected exception:" + e.toString());
				else
					dumpSQLExceptions(e);
			}

			fileIn.close();
		}
		catch (SQLException e) {
				dumpSQLExceptions(e);
		}
		catch (Throwable e) {
			System.out.println("FAIL -- unexpected exception:" + e.toString());
		}
	 	System.out.println("Test 11 - negative stream length tests end in here");
	}

	private static void streamTest12(Connection conn) {

		ResultSet rs;
		Statement stmt;

		//The following 2 files are for testing the truncation in varchar.
		//only non-blank character truncation will throw an exception for varchars.
		//max value allowed in varchars is 32672 characters long
		String fileName1 = "extin/char32675trailingblanks.data"; // set up a file 32675 characters long but with last 3 characters as blanks
		String fileName2 = "extin/char32675.data"; // set up a file 32675 characters long with 3 extra non-blank characters trailing in the end

		System.out.println("Test 12 - varchar truncation tests start from here");
		try {
			stmt = conn.createStatement();
			stmt.executeUpdate("call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', '4096')");
			stmt.execute("create table testVarChar (a int, b varchar(32672))");
			//create a table with 4 varchars. This table will be used to try overflow through concatenation
			stmt.execute("create table testConcatenation (a varchar(16350), b varchar(16350), c varchar(16336), d varchar(16336))");
			stmt.executeUpdate("call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', NULL)");
			String largeStringA16350 = new String(Formatters.repeatChar("a",16350));
			String largeStringA16336 = new String(Formatters.repeatChar("a",16336));
			PreparedStatement ps = conn.prepareStatement("insert into testConcatenation values (?, ?, ?, ?)");
			ps.setString(1, largeStringA16350);
			ps.setString(2, largeStringA16350);
			ps.setString(3, largeStringA16336);
			ps.setString(4, largeStringA16336);
			ps.executeUpdate();

			ps = conn.prepareStatement("insert into testVarChar values(?, ?)");

			// prepare an InputStream from the file which has 3 trailing blanks in the end, so after blank truncation, there won't be any overflow
			// try this using setAsciiStream, setCharacterStream, setString and setObject
			insertDataUsingAsciiStream(ps, 1, fileName1, Limits.DB2_VARCHAR_MAXWIDTH);
			insertDataUsingCharacterStream(ps, 2, fileName1, Limits.DB2_VARCHAR_MAXWIDTH);
			insertDataUsingStringOrObject(ps, 3, Limits.DB2_VARCHAR_MAXWIDTH, true, true);
			insertDataUsingStringOrObject(ps, 4, Limits.DB2_VARCHAR_MAXWIDTH, true, false);
			System.out.println("===> testing trailing blanks using concatenation");
			insertDataUsingConcat(stmt, 5, Limits.DB2_VARCHAR_MAXWIDTH, true, false);

			// prepare an InputStream from the file which has 3 trailing non-blanks in the end, and hence there would be overflow exception
			// try this using setAsciiStream, setCharacterStream, setString and setObject
			insertDataUsingAsciiStream(ps, 6, fileName2, Limits.DB2_VARCHAR_MAXWIDTH);
			insertDataUsingCharacterStream(ps, 7, fileName2, Limits.DB2_VARCHAR_MAXWIDTH);