blobclob4blob.java

derby database source code.good for you.

    
    /*
        make sure setClob doesn't work on an int column
        need to run prepareCLOBMAIN first
    */
	private static void clobTest54(Connection conn)
    {
		ResultSet rs;
		Statement stmt1, stmt2;
		System.out.println(START + "clobTest54");
        try
        {
			stmt1 = conn.createStatement();
			stmt1.execute("create table testClob2 (a integer, b integer)");
            PreparedStatement ps = conn.prepareStatement(
                "insert into testClob2 values(?,?)");
			stmt2 = conn.createStatement();
			rs = stmt2.executeQuery("select a,b from testCLOB_MAIN");
            Clob clob;
            int clobLength;
			while (rs.next())
            {
				// get the first ncolumn as a clob
                clob = rs.getClob(1);
                if (clob == null)
                    continue;
                clobLength = rs.getInt(2);
                ps.setClob(1,clob);
                ps.setInt(2,clobLength);
                ps.executeUpdate();
			}
            rs.close();
            conn.commit();

            System.out.println("clobTest54 finished");
        }
		catch (SQLException e) {
			// Can't do a setClob on an int column. This is expected
				TestUtil.dumpSQLExceptions(e,true);
		}
		catch (Throwable e) {
			System.out.println("FAIL -- unexpected exception:" + e.toString());
			if (debug) e.printStackTrace();
		}
		System.out.println("end clobTest54");
    }


    /*
    test raising of exceptions
    need to run prepareCLOBMAIN first
    */
	private static void clobTest6(Connection conn)
    {
		ResultSet rs;
		Statement stmt;
		System.out.println(START + "clobTest6");
		try
        {
			stmt = conn.createStatement();
			rs = stmt.executeQuery("select a,b from testCLOB_MAIN");
            int i = 0, clobLength = 0;
            Clob clob;
			rs.next();
            clob = rs.getClob(1);
            if (clob == null)
                return;
            clobLength = rs.getInt(2);
            // 0 or negative position value
			if (isDerbyNet)
				System.out.println(" negative tests for clob.getSubstring won't run  for network server  until 5243 is fixed");
			if (! isDerbyNet)
			{

				try
				{
					clob.getSubString(0,5);
					System.out.println("FAIL = clob.getSubString(0,5)");
				}
				catch (SQLException e)
				{
				boolean isExpected = isOutOfBoundException(e);
													   
                TestUtil.dumpSQLExceptions(e, isExpected);
				}
			
            // negative length value
            try
            {
                clob.getSubString(1,-76);
				System.out.println("FAIL = getSubString(1,-76)");
            }
            catch (SQLException e)
            {
                TestUtil.dumpSQLExceptions(e, isOutOfBoundException(e));
            }
            // zero length value
            try
            {
                clob.getSubString(1,0);
 				System.out.println("FAIL = getSubString(1,0)");
           }
            catch (SQLException e)
            {
                TestUtil.dumpSQLExceptions(e, isOutOfBoundException(e));
            }
            // 0 or negative position value
            try
            {
                clob.position("xx",-4000);
				System.out.println("FAIL = position('xx',-4000)");
            }
            catch (SQLException e)
            {
                TestUtil.dumpSQLExceptions(e, isOutOfBoundException(e));
            }
            // null pattern
            try
            {
                clob.position((String) null,5);
				System.out.println("FAIL = position((String) null,5)");
            }
            catch (SQLException e)
            {
                TestUtil.dumpSQLExceptions(e, "XJ072".equals(e.getSQLState()));
            }
            // 0 or negative position value
            try
            {
                clob.position(clob,-42);
				System.out.println("FAIL = position(clob,-42)");
            }
            catch (SQLException e)
            {
                TestUtil.dumpSQLExceptions(e, isOutOfBoundException(e));
            }
            // null pattern
            try
            {
                clob.position((Clob) null,5);
				System.out.println("FAIL = pposition((Clob) null,5)");
            }
            catch (SQLException e)
            {
                TestUtil.dumpSQLExceptions(e, "XJ072".equals(e.getSQLState()));
            }
            System.out.println("clobTest6 finished");
			}
        }
		catch (SQLException e) {
			TestUtil.dumpSQLExceptions(e);
		}
		catch (Throwable e) {
			System.out.println("FAIL -- unexpected exception:" + e.toString());
			if (debug) e.printStackTrace();
		}
    }


    /*
        test setClob
        need to run prepareCLOBMAIN first
    */
	private static void clobTest7(Connection conn)
    {
		ResultSet rs, rs2;
		Statement stmt1, stmt2;
		System.out.println(START + "clobTest7");
        try
        {
			stmt1 = conn.createStatement();
			stmt1.execute("create table testClob7 (a CLOB(300k), b integer)");
            PreparedStatement ps = conn.prepareStatement(
                "insert into testClob7 values(?,?)");
			stmt2 = conn.createStatement();
			rs = stmt2.executeQuery("select a,b from testCLOB_MAIN");
            Clob clob;
            int clobLength;
			int rownum = 0;
			while (rs.next())
            {
				// get the first column as a clob
                clob = rs.getClob(1);
                if (clob == null)
                    continue;
                clobLength = rs.getInt(2);
                ps.setClob(1,clob);
                ps.setInt(2,clobLength);
                ps.executeUpdate();
			}
            rs.close();
            conn.commit();

			rs2 = stmt2.executeQuery("select a,b from testClob7");
            Clob clob2;
            int clobLength2, j = 0;
			while (rs2.next())
            {
                j++;
				// get the first column as a clob
                clob2 = rs2.getClob(1);
                if (clob2 == null)
                    continue;
                clobLength2 = rs2.getInt(2);
                if (clob2.length() != clobLength2)
                    System.out.println("FAILED at row " + j);
			}
            rs2.close();

            conn.commit();
            System.out.println("clobTest7 finished");
        }
		catch (SQLException e) {
			TestUtil.dumpSQLExceptions(e);
			if (debug) e.printStackTrace();
		}
		catch (Throwable e) {
			System.out.println("FAIL -- unexpected exception:" + e.toString());
			if (debug) e.printStackTrace();
		}
    }

	/**
		Agressive test of position. 
	*/
  	private static void clobTest8(Connection conn)
    {
  		System.out.println(START + "clobTest8");
		try {
			Statement s = conn.createStatement();

			s.execute("CREATE TABLE C8.T8POS(id INT NOT NULL PRIMARY KEY, DD CLOB(1m), pos INT, L INT)");
			s.execute("CREATE TABLE C8.T8PATT(PATT CLOB(300k))");

			// characters used to fill the String
			char[] fill = new char[4];
			fill[0] = 'd';		// 1 byte UTF8 character (ASCII)
			fill[1] = '\u03a9';  // 2 byte UTF8 character (Greek)
			fill[2] = '\u0e14'; // 3 byte UTF8 character (Thai)
			fill[3] = 'j';		// 1 byte UTF8 character (ASCII)

			char[] base = new char[256 * 1024];

			for (int i = 0; i < base.length; i += 4) {

				base[i] = fill[0];
				base[i+1] = fill[1];
				base[i+2] = fill[2];
				base[i+3] = fill[3];

			}

			char[]  patternBase = new char[2 * 1024];
			for (int i = 0; i < patternBase.length; i += 8) {

				patternBase[i] = 'p';
				patternBase[i+1] = 'a';
				patternBase[i+2] = 't';
				patternBase[i+3] = '\u03aa';
				patternBase[i+4] = (char) i;   // changed value to keep pattern varyinh
				patternBase[i+5] = 'b';
				patternBase[i+6] = 'm';
				patternBase[i+7] = '\u0e15';

			}

			PreparedStatement ps = conn.prepareStatement("INSERT INTO C8.T8POS VALUES (?, ?, ?, ?)");
			PreparedStatement psp = conn.prepareStatement("INSERT INTO C8.T8PATT VALUES (?)");

			T8insert(ps, 1, base, 256, patternBase, 8, 100, true);
			T8insert(ps, 2, base, 3988, patternBase, 8, 2045, true);
			T8insert(ps, 3, base, 16321, patternBase, 8, 4566, true);
			T8insert(ps, 4, base, 45662, patternBase, 8, 34555, true);
			T8insert(ps, 5, base, 134752, patternBase, 8, 67889, true);
			T8insert(ps, 6, base, 303, patternBase, 8, 80, false);
			T8insert(ps, 7, base, 4566, patternBase, 8, 2086, false);
			T8insert(ps, 8, base, 17882, patternBase, 8, 4426, false);
			T8insert(ps, 9, base, 41567, patternBase, 8, 31455, false);
			String pstr = T8insert(ps, 10, base, 114732, patternBase, 8, 87809, false);			

			conn.commit();

			psp.setString(1, pstr);
			psp.executeUpdate();

			System.out.println("small string pattern");
			checkClob8(s, pstr);
			conn.commit();

			System.out.println("small java.sql.Clob pattern");
			ResultSet rsc = s.executeQuery("SELECT PATT FROM C8.T8PATT");
			rsc.next();
			checkClob8(s, rsc.getClob(1));

			rsc.close();


			conn.commit();

			s.execute("DELETE FROM C8.T8POS");
			s.execute("DELETE FROM C8.T8PATT");


			T8insert(ps, 1, base, 256, patternBase, 134, 100, true);
			T8insert(ps, 2, base, 3988, patternBase, 134, 2045, true);
			T8insert(ps, 3, base, 16321, patternBase, 134, 4566, true);
			T8insert(ps, 4, base, 45662, patternBase, 134, 34555, true);
			T8insert(ps, 5, base, 134752, patternBase, 134, 67889, true);
			T8insert(ps, 6, base, 303, patternBase, 134, 80, false);
			T8insert(ps, 7, base, 4566, patternBase, 134, 2086, false);
			T8insert(ps, 8, base, 17882, patternBase, 134, 4426, false);
			T8insert(ps, 9, base, 41567, patternBase, 134, 31455, false);
			pstr = T8insert(ps, 10, base, 114732, patternBase, 134, 87809, false);

			conn.commit();
			psp.setString(1, pstr);
			psp.executeUpdate();
			conn.commit();


			System.out.println("medium string pattern");
			checkClob8(s, pstr);
			conn.commit();

			System.out.println("medium java.sql.Clob pattern");
			rsc = s.executeQuery("SELECT PATT FROM C8.T8PATT");
			rsc.next();
			checkClob8(s, rsc.getClob(1));

			s.execute("DELETE FROM C8.T8POS");
			s.execute("DELETE FROM C8.T8PATT");

			T8insert(ps, 1, base, 256, patternBase, 679, 100, true);
			T8insert(ps, 2, base, 3988, patternBase, 679, 2045, true);
			T8insert(ps, 3, base, 16321, patternBase, 679, 4566, true);
			T8insert(ps, 4, base, 45662, patternBase, 679, 34555, true);
			T8insert(ps, 5, base, 134752, patternBase, 679, 67889, true);
			T8insert(ps, 6, base, 303, patternBase, 679, 80, false);
			T8insert(ps, 7, base, 4566, patternBase, 679, 2086, false);
			T8insert(ps, 8, base, 17882, patternBase, 679, 4426, false);
			T8insert(ps, 9, base, 41567, patternBase, 679, 31455, false);
			pstr = T8insert(ps, 10, base, 114732, patternBase, 679, 87809, false);

			conn.commit();
			psp.setString(1, pstr);
			psp.executeUpdate();
			conn.commit();


			System.out.println("long string pattern");
			checkClob8(s, pstr);
			conn.commit();

			System.out.println("long java.sql.Clob pattern");
			rsc = s.executeQuery("SELECT PATT FROM C8.T8PATT");
			rsc.next();
			checkClob8(s, rsc.getClob(1));

			s.execute("DELETE FROM C8.T8POS");
			s.execute("DELETE FROM C8.T8PATT");
			ps.close();
			psp.close();
			// 

			s.execute("DROP TABLE C8.T8POS");
			s.execute("DROP TABLE C8.T8PATT");

			s.close();

			conn.commit();

			System.out.println("complete clobTest8");


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

	private static void checkClob8(Statement s, String pstr) throws SQLException {
		
		ResultSet rs = s.executeQuery("SELECT ID, DD, POS, L FROM C8.T8POS ORDER BY 1");

		while (rs.next()) {

			int id = rs.getInt(1);

			System.out.print("@" + id + " ");

			java.sql.Clob cl = rs.getClob(2);

			int pos = rs.getInt(3);
			int len = rs.getInt(4);

			long clobPosition = cl.position(pstr, 1);
			if (clobPosition == (long) pos) {
				System.out.print(" position MATCH("+pos+")");
			} else {
				System.out.print(" position FAIL("+clobPosition+"!=" + pos +")");
			}

			System.out.println("");
		}
		rs.close();
	}
	private static void checkClob8(Statement s, Clob pstr) throws SQLException {
		ResultSet rs = s.executeQuery("SELECT ID, DD, POS, L FROM C8.T8POS ORDER BY 1");

		while (rs.next()) {

			int id = rs.getInt(1);

			System.out.print("@" + id + " ");