streamingcolumn.java

derby database source code.good for you.

		insertLongString(conn, 2, pad("Franklin", length), false);
		insertLongString(conn, 3, pad("Webster", length), false);
		PreparedStatement ps = conn.prepareStatement(
			"update foo set a=a+1000, b=? where a<99 and a in (select a from foo)");

		File file = new File("extin/short.data");
		InputStream fileIn = new FileInputStream(file);
		ps.setAsciiStream(1, fileIn, (int)(file.length()));
		ps.executeUpdate();
		fileIn.close();

		ps = conn.prepareStatement(
			"update foo set a=a+1000, b=? where a<99 and a in (select a from foo)");
		file = new File("extin/shortbanner");
		fileIn = new FileInputStream(file);
		ps.setAsciiStream(1, fileIn, (int)(file.length()));
		ps.executeUpdate();
		fileIn.close();

		sourceStmt.executeUpdate("drop table foo");
	}

	private static void streamTest4(Connection conn) {

		ResultSetMetaData met;
		ResultSet rs;
		Statement stmt;

		try {
			stmt = conn.createStatement();
			stmt.execute("create table testLongVarBinary (a int, b BLOB(1G))");
			// insert an empty string 
			stmt.execute("insert into testLongVarBinary values(1, CAST (" +
						 TestUtil.stringToHexLiteral("") + "AS BLOB(1G)))");
			// insert a short text string
			stmt.execute("insert into testLongVarBinary values(2,CAST (" +
						 TestUtil.stringToHexLiteral("test data: a string column inserted as an object") + "AS BLOB(1G)))");

			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 testLongVarBinary values(?, ?)");
				ps.setInt(1, 100 + i);
				ps.setBinaryStream(2, fileIn, (int)fileLength[i]);
				ps.executeUpdate();
				fileIn.close();
			}

			rs = stmt.executeQuery("select a, b from testLongVarBinary");
			met = rs.getMetaData();
			byte[] buff = new byte[128];
			// fetch all rows back, get the long varchar columns as streams.
			while (rs.next()) {
				// get the first column as an int
				int a = rs.getInt("a");
				// get the second column as a stream
				InputStream fin = rs.getBinaryStream(2);
				int columnSize = 0;
				for (;;) {
					int size = fin.read(buff, 0, 100);
					if (size == -1)
						break;
					columnSize += size;
				}
			}

			rs = stmt.executeQuery("select a, b from testLongVarBinary order by a");
			met = rs.getMetaData();
			// fetch all rows back in order, get the long varchar columns as streams.
			while (rs.next()) {
				// get the first column as an int
				int a = rs.getInt("a");
				// get the second column as a stream
				InputStream fin = rs.getBinaryStream(2);
				int columnSize = 0;
				for (;;) {
					int size = fin.read(buff);
					if (size == -1)
						break;
					columnSize += size;
				}
			}

			rs = stmt.executeQuery("select a, b from testLongVarBinary");
			// fetch all rows back, get the long varchar columns as Strings.
			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
				String resultString = rs.getString(2);
			}

			rs = stmt.executeQuery("select a, b from testLongVarBinary order by a");
			// fetch all rows back in order, get the long varchar columns as Strings.
			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
				String resultString = rs.getString(2);
			}

			rs.close();
			stmt.close();

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

	static void streamTest5(Connection conn, long length) throws Exception
	{
		Statement sourceStmt = conn.createStatement();
		String binaryType = length > 32700 ? "BLOB(1G)" : "long varchar for bit data";
		sourceStmt.executeUpdate("create table foo (a int not null constraint pk primary key, b " + binaryType + " )");

		insertLongString(conn, 1, pad("Broadway", length), true);
		insertLongString(conn, 2, pad("Franklin", length), true);
		insertLongString(conn, 3, pad("Webster", length), true);
		insertLongString(conn, 4, pad("Broadway", length), true);
		insertLongString(conn, 5, pad("Franklin", length), true);
		insertLongString(conn, 6, pad("Webster", length), true);
		PreparedStatement ps = conn.prepareStatement(
			"update foo set a=a+1000, b=? where a<99 and a in (select a from foo)");
		File file = new File("extin/short.data");
		InputStream fileIn = new FileInputStream(file);
		ps.setBinaryStream(1, fileIn, (int)(file.length()));
		ps.executeUpdate();
		fileIn.close();

		ps = conn.prepareStatement(
			"update foo set a=a+1000, b=? where a<99 and a in (select a from foo)");
		file = new File("extin/shortbanner");
		fileIn = new FileInputStream(file);
		ps.setBinaryStream(1, fileIn, (int)(file.length()));
		ps.executeUpdate();
		ps.close();
		fileIn.close();

		sourceStmt.executeUpdate("drop table foo");
	}

	static void streamTest6(Connection conn, long length) throws Exception
	{
		Statement sourceStmt = conn.createStatement();
		sourceStmt.executeUpdate("create table foo (a int not null constraint pk primary key, b long varchar)");

		insertLongString(conn, 1, pad("Broadway", length), false);
		insertLongString(conn, 2, pad("Franklin", length), false);
		insertLongString(conn, 3, pad("Webster", length), false);
		PreparedStatement ps = conn.prepareStatement(
			"update foo set a=a+1000, b=? where a<99 and a in (select a from foo)");

		streamInLongCol(ps, pad("Grand", length));
		ps.close();
		sourceStmt.close();
	}

	static void streamTest7(Connection conn) throws Exception
	{
		conn.setAutoCommit(false);

		System.out.println("streamTest7");

 		Statement s = conn.createStatement();
		s.executeUpdate("call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', '1024')");
		s.execute("create table testlvc (a int, b char(100), lvc long varchar, d char(100))");
		s.executeUpdate("call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', NULL)");
		s.close();
		conn.commit();

		PreparedStatement ps1 = conn.prepareStatement(
			"insert into testlvc values (?, 'filler for column b on null column', null, 'filler for column d')");

		PreparedStatement ps2 = conn.prepareStatement(
			"insert into testlvc values (?, 'filler for column b on empty string column', ?, 'filler2 for column d')");


		for (int i= 0; i < 100; i++)
		{
			ps1.setInt(1, i);
			ps1.executeUpdate();

			ByteArrayInputStream emptyString = new ByteArrayInputStream(new byte[0]);
			ps2.setInt(1, i);
			ps2.setAsciiStream(2, emptyString, 0);
			ps2.executeUpdate();
		}
		ps1.close();
		ps2.close();

		conn.commit();

		PreparedStatement ps = conn.prepareStatement("update testlvc set lvc = ? where a = ?");

		String longString =
			"this is a relatively long string, hopefully the row will be split or otherwise become long ???  I don't think it will become long but maybe if it rolls back it will become strange";
		for (int i = 0; i < 100; i++)
		{
			ByteArrayInputStream string1 = new ByteArrayInputStream(longString.getBytes("US-ASCII"));
			ps.setAsciiStream(1, string1, longString.length());
			ps.setInt(2, i);
			ps.executeUpdate();
			if ((i % 2) == 0)
				conn.rollback();
			else
				conn.commit();

			ByteArrayInputStream emptyString = new ByteArrayInputStream(new byte[0]);
			ps.setAsciiStream(1, emptyString, 0);
			ps.executeUpdate();
			if ((i%3) == 0)
				conn.rollback();
			else
				conn.commit();
		}

		ps.close();
	}

    /**
     * long row test of insert/backout case, using setAsciiStream().
     * <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 streamTest8(Connection conn, int stream1_len, int stream2_len)
	{
        System.out.println(
            "Starting streamTest8(conn, " +
            stream1_len + ", " + stream2_len + ")");

		ResultSetMetaData   met;
		ResultSet           rs;
		Statement           stmt;

		String createsql =
            new String(
                "create table t8(a int, b long varchar, c long varchar)");

		String insertsql = new String("insert into t8 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.setAsciiStream(
                    2, new ByteArrayInputStream(stream1_byte_array[i]), stream1_len);

                // create the stream and insert it
                insert_ps.setAsciiStream(
                    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 t8 select * from t8 where a = -6363";
				stmt.execute(checkSQL);
			}

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


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

			// 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
				String resultString = rs.getString(2);

                // compare result with expected
                String canon = new String(stream1_byte_array[a], "US-ASCII");

                if (canon.compareTo(resultString) != 0)
                {
                    System.out.println(
                        "FAIL -- bad result string:" +
                        "canon: " + canon +
                        "resultString: " + resultString);
                }

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

                // compare result with expected
                canon = new String(stream2_byte_array[a], "US-ASCII");

                if (canon.compareTo(resultString) != 0)
                {
                    System.out.println(
                        "FAIL -- bad result string:" +
                        "canon: " + canon +
                        "resultString: " + resultString);
                }
			}

			rs.close();