streamingcolumn.java

derby database source code.good for you.

/* 

   Derby - Class org.apache.derbyTesting.functionTests.tests.store.streamingColumn

   Copyright 1999, 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.store;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSetMetaData;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.SQLException;
import java.sql.Types;

import org.apache.derby.tools.ij;
import org.apache.derby.tools.JDBCDisplayUtil;
import org.apache.derbyTesting.functionTests.util.Formatters;
import org.apache.derbyTesting.functionTests.util.TestUtil;
import org.apache.derby.iapi.reference.Limits;
import java.io.*;
import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.util.zip.CRC32;
import java.util.Properties;

/**
 * Test of JDBC result set Stream calls.
 *
 * @author djd
 */

public class streamingColumn { 

			// set up a short (fit in one page) inputstream for insert
	static String[] fileName;
	static long[] fileLength;

	static
	{
		int numFiles = 4;
		fileName = new String[numFiles];
		fileLength = new long[numFiles];

		fileName[0] = "extin/short.data";	// set up a short (fit in one page) inputstream for insert
		fileName[1] = "extin/shortbanner"; // set up a long (longer than a page) inputstream for insert
		fileName[2] = "extin/derby.banner"; // set up a really long (over 300K) inputstream for insert
		fileName[3] = "extin/empty.data"; // set up a file with nothing in it
	}


	public static void main(String[] args) {

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

		try {
			// use the ij utility to read the property file and
			// make the initial connection.
			ij.getPropertyArg(args);
			Connection conn = ij.startJBMS();

			streamTest1(conn);

			// test column size 1500 bytes
			streamTest2(conn, 1500);
			// test column size 5000 butes
			streamTest2(conn, 5000);
			streamTest2(conn, 10000);

			streamTest3(conn, 0);
			streamTest3(conn, 1500);
			streamTest3(conn, 5000);
			streamTest3(conn, 10000);

			streamTest4(conn);

			streamTest5(conn, 0);
			streamTest5(conn, 1500);
			streamTest5(conn, 5000);
            // This test fails when running w/ derby.language.logStatementText=true
            // see DERBY-595 
            //streamTest5(conn, 100000);

			streamTest6(conn, 5000);
			streamTest7(conn);

            // test 1st column fit, second column doesn't
            streamTest8(conn, 10, 2500);
            streamTest9(conn, 10, 2500);

            // test 1st column doesn't fit, second column does
            streamTest8(conn, 2500, 10);
            streamTest9(conn, 2500, 10);

			// test compressTable
			streamTest10(conn);

			// bug 5592 test negativte length for the setXXStream methods. Should fail.
			streamTest11(conn);

			// bug 5592 test - only non-blank character truncation should give error for varchars
			streamTest12(conn);

			// bug 5592 test - any character(including blank character) truncation should give error for long varchars
			streamTest13(conn);

            
            
            // Derby500
            // user supplied stream parameter values are not re-used
            derby500Test(conn);

            // currently in case of char,varchar,long varchar types
            // stream paramter value is materialized the first time around
            // and used for executions. Hence verify that the fix to 
            // DERBY-500 did not change the behavior for char,varchar
            // and long varchar types when using streams.
            derby500_verifyVarcharStreams(conn);
            
			// turn autocommit on because in JCC, java.sql.Connection.close() can not be
			// requested while a transaction is in progress on the connection.
			// If autocommit is off in JCC, the transaction remains active, 
			// and the connection cannot be closed.
			// If autocommit is off in Derby, an invalid transaction state SQL exception is thrown.
			conn.setAutoCommit(true);
			conn.close();

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

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

	private static void streamTest1(Connection conn) {

		ResultSetMetaData met;
		ResultSet rs;
		Statement stmt;

		try {
			stmt = conn.createStatement();
			stmt.execute("create table testLongVarChar (a int, b long varchar)");
			// insert a null long varchar
			stmt.execute("insert into testLongVarChar values(1, '')");
			// insert a long varchar with a short text string
			stmt.execute("insert into testLongVarChar values(2, 'test data: a string column inserted as an object')");


			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 testLongVarChar values(?, ?)");
				ps.setInt(1, 100 + i);
				ps.setAsciiStream(2, 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();
			}

			rs = stmt.executeQuery("select a, b from testLongVarChar");
			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.getAsciiStream(2);
				int columnSize = 0;
				for (;;) {
					int size = fin.read(buff);
					if (size == -1)
						break;
					columnSize += size;
				}
				verifyLength(a, columnSize, fileLength);
			}

			rs = stmt.executeQuery("select a, b from testLongVarChar 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.getAsciiStream(2);
				int columnSize = 0;
				for (;;) {
					int size = fin.read(buff);
					if (size == -1)
						break;
					columnSize += size;
				}
				verifyLength(a, columnSize, fileLength);
			}

			rs = stmt.executeQuery("select a, b from testLongVarChar");
			// 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);
				verifyLength(a, resultString.length(), fileLength);
			}

			rs = stmt.executeQuery("select a, b from testLongVarChar 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);
				verifyLength(a, resultString.length(), fileLength);
			}

			rs = stmt.executeQuery(
				"select a, b from testLongVarChar where b like 'test data: a string column inserted as an object'");
			// 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);
				verifyLength(a, resultString.length(), fileLength);
			}

			stmt.executeUpdate("call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', '1024')");
			stmt.executeUpdate("create table foo (a int not null, b long varchar, primary key (a))");
			stmt.executeUpdate("call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', NULL)");
			insertLongString(conn, 10, "ssssssssss", false);


			insertLongString(conn, 0, "", false);
			insertLongString(conn, 1, "1", false);
			insertLongString(conn, -1, null, false);
			insertLongString(conn, 20, "XXXXXXXXXXXXXXXXXXXX", false);

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

			System.out.println("expect to get null string back");
			while(rs.next())
			{
				int a = rs.getInt("a");
				String resultString = rs.getString(2);
				if (resultString == null)
				{
					System.out.println("a = " + a + " got null string back");
				}
				else if (resultString.length() != a)
				{
					System.out.println("FAIL - failed to get string back, expect "+
									   a + " got " + resultString.length());
				}
			}

			updateLongString(conn, 1, 3000);
			updateLongString(conn, 0, 800);
			updateLongString(conn, 3000, 0);
			updateLongString(conn, 0, 51);
			updateLongString(conn, 20, 0);
			rs = stmt.executeQuery("select a, b from foo");
			while(rs.next())
			{
				int a = rs.getInt("a");
				String resultString = rs.getString(2);
				if (resultString == null)
				{
					System.out.println("a = " + a + " got null string back");
				}
				else if (resultString.length() != a)
				{
					System.out.println("FAIL - failed to get string back, expect "+
									   a + " got " + resultString.length() +
									   " " + resultString);
				}
			}

			stmt.executeUpdate("drop table foo");

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

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

	static void streamTest2(Connection conn, long length) throws Exception
	{
		Statement sourceStmt = conn.createStatement();

		sourceStmt.executeUpdate("create table foo (a int not null, b long varchar, primary key (a))");

		insertLongString(conn, 1, pad("Broadway", length), false);
		insertLongString(conn, 2, pad("Franklin", length), false);
		insertLongString(conn, 3, pad("Webster", length), false);

		sourceStmt.executeUpdate("insert into foo select a+100, b from foo");

		verifyExistence(conn, 1, "Broadway", length);
		verifyExistence(conn, 2, "Franklin", length);
		verifyExistence(conn, 3, "Webster", length);
		verifyExistence(conn, 101, "Broadway", length);
		verifyExistence(conn, 102, "Franklin", length);
		verifyExistence(conn, 103, "Webster", length);

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

	static void streamTest3(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);