blobclob4blob.java

derby database source code.good for you.

			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 String T8insert(PreparedStatement ps, int id, char[] base, int bl, char[] pattern, int pl, int pos, boolean addPattern)
		throws SQLException {

		StringBuffer sb = new StringBuffer();
		sb.append(base, 0, bl);

		// Assume the pattern looks like Abcdefgh
		// put together a block of misleading matches such as
		// AAbAbcAbcdAbcde

		int last = addPatternPrefix(sb, pattern, pl, 5, 10);

		if (last >= (pos / 2))
			pos = (last + 10) * 2;

		// now a set of misleading matches up to half the pattern width
		last = addPatternPrefix(sb, pattern, pl, pl/2, pos/2);

		if (last >= pos)
			pos = last + 13;

		// now a complete set of misleading matches
		pos = addPatternPrefix(sb, pattern, pl, pl - 1, pos);

		if (addPattern) {
			// and then the pattern
			sb.insert(pos, pattern, 0, pl);
		} else {
			pos = -1;
		}


		String dd = sb.toString();
		String pstr = new String(pattern, 0, pl);

		if (pos != dd.indexOf(pstr)) {
			System.out.println("FAIL - test confused pattern not at expected location");

			System.out.println("POS = " + pos + " index " + dd.indexOf(pstr));
			System.out.println("LENG " + dd.length());
			// System.out.println(sb.toString());
		}


		// JDBC uses 1 offset for first character
		if (pos != -1)
			pos = pos + 1;

		ps.setInt(1, id);
		ps.setString(2, dd);
		ps.setInt(3, pos); 
		ps.setInt(4, dd.length());
		ps.executeUpdate();

		return pstr;

	}

	private static int addPatternPrefix(StringBuffer sb, char[] pattern, int pl, int fakeCount, int pos) {

		for (int i = 0; i < fakeCount && i < (pl - 1); i++) {

			sb.insert(pos, pattern, 0, i + 1);
			pos += i + 1;
		}

		return pos;
	}

    /* advanced tests */

    // make sure clob is still around after we go to the next row,
    // after we close the result set, and after we close the statement
	private static void clobTest91(Connection conn)
    {
		ResultSet rs;
		Statement stmt;
		System.out.println(START + "clobTest91");
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery("select a,b from testCLOB_MAIN");
			byte[] buff = new byte[128];
            Clob[] clobArray = new Clob[numRows];
            int[] clobLengthArray = new int[numRows];
            int j = 0;
			while (rs.next())
            {
                clobArray[j] = rs.getClob(1);
                clobLengthArray[j++] = rs.getInt(2);
            }
            rs.close();
            stmt.close();

            for (int i = 0; i < numRows; i++)
            {
                if (clobArray[i] == null)
                    {
                        System.out.println("row " + i + " is null, skipped");
                        continue;
                    }
				InputStream fin = clobArray[i].getAsciiStream();
				int columnSize = 0;
				for (;;) {
					int size = fin.read(buff);
					if (size == -1)
						break;
					columnSize += size;
				}
                if (columnSize != clobLengthArray[i])
					System.out.println("test failed, columnSize should be " +
                        clobLengthArray[i] + ", but it is " + columnSize + ", i = " + i);
                if (columnSize != clobArray[i].length())
					System.out.println("test failed, clobArray[i].length() should be " +  columnSize
					   + ", but it is " + clobArray[i].length() + ", i = " + i);
                System.out.println("done row " + i + ", length was " + clobLengthArray[i]);
            }
            System.out.println("clobTest91 finished");
        }
		catch (SQLException e) {
			TestUtil.dumpSQLExceptions(e);
		}
		catch (Throwable e) {
			System.out.println("FAIL -- unexpected exception:" + e.toString());
			if (debug) e.printStackTrace();
		}
    }


    /*
        test locking
        need to run prepareCLOBMAIN fverirst
    */
	private static void clobTest92(Connection conn)
    {
		ResultSet rs;
		Statement stmt,stmt2;
		System.out.println(START + "clobTest92");
        try
        {
			stmt = conn.createStatement();
			rs = stmt.executeQuery("select a,b from testCLOB_MAIN");
			// fetch row back, get the column as a clob.
            Clob clob = null, shortClob = null;
            int clobLength;
			while (rs.next())
            {
                clobLength = rs.getInt(2);
                if (clobLength == 10000)
                    clob = rs.getClob(1);
                if (clobLength == 26)
                    shortClob = rs.getClob(1);
			}
            rs.close();

            Connection conn2 = ij.startJBMS();
            // turn off autocommit, otherwise blobs/clobs cannot hang around
            // until end of transaction
            conn2.setAutoCommit(false);

            // update should go through since we don't get any locks on clobs
            // that are not long columns
            stmt2 = conn2.createStatement();
            stmt2.executeUpdate("update testCLOB_MAIN set a = 'foo' where b = 26");
            if (shortClob.length() != 26)
                System.out.println("FAILED: clob length changed to " + shortClob.length());
            // should timeout waiting for the lock to do this
            stmt2 = conn2.createStatement();
            stmt2.executeUpdate("update testCLOB_MAIN set b = b + 1 where b = 10000");

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


    /*
        test locking with a long row + long column
    */
	private static void clobTest93(Connection conn)
    {
		ResultSet rs;
		Statement stmt, stmt2;
		System.out.println(START + "clobTest93");
        try
        {
			stmt = conn.createStatement();
			// creating table to fit within default 4k table size, then add large columns
			stmt.execute("create table testLongRowClob (a varchar(2000))");
			stmt.execute("alter table testLongRowClob add column b varchar(3000)");
			stmt.execute("alter table testLongRowClob add column c varchar(2000)");
			stmt.execute("alter table testLongRowClob add column d varchar(3000)");
			stmt.execute("alter table testLongRowClob add column e CLOB(400k)");
            PreparedStatement ps = conn.prepareStatement(
                "insert into testLongRowClob values(?,?,?,?,?)");
            ps.setString(1,Formatters.padString("blaaa",2000));
            ps.setString(2,Formatters.padString("tralaaaa",3000));
            ps.setString(3,Formatters.padString("foodar",2000));
            ps.setString(4,Formatters.padString("moped",3000));
            File file = new File(fileName[1]);
            if (file.length() < 10000)
                System.out.println("ERROR: wrong file tested");
            InputStream fileIn = new FileInputStream(file);
            ps.setAsciiStream(5, fileIn, (int)file.length());
            ps.executeUpdate();
            fileIn.close();
            conn.commit();

			stmt = conn.createStatement();
			rs = stmt.executeQuery("select e from testLongRowClob");
			// fetch row back, get the column as a clob.
            Clob clob = null;
			while (rs.next())
                clob = rs.getClob(1);
            rs.close();

            Connection conn2 = ij.startJBMS();
            // turn off autocommit, otherwise blobs/clobs cannot hang around
            // until end of transaction
            conn2.setAutoCommit(false);
            // the following should timeout
            stmt2 = conn2.createStatement();
            stmt2.executeUpdate("update testLongRowClob set e = 'smurfball' where a = 'blaaa'");

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

    
    /*
        test accessing clob after commit
        need to run prepareCLOBMAIN first
    */
	private static void clobTest94(Connection conn)
    {
		ResultSet rs;
		Statement stmt;
		System.out.println(START + "clobTest94");
        try
        {
			stmt = conn.createStatement();
			rs = stmt.executeQuery("select a,b from testCLOB_MAIN");
			// fetch row back, get the column as a clob.
            Clob clob = null, shortClob = null;
            int clobLength;
            int i = 0;
			while (rs.next())
            {
				//System.out.println("ACCESSING ROW:" + i++);
                clobLength = rs.getInt(2);
                if (clobLength == 10000)
                    clob = rs.getClob(1);
                if (clobLength == 26)
                    shortClob = rs.getClob(1);
			}
            rs.close();
            conn.commit();

            // no problem accessing this after commit since it is in memory
            System.out.println("shortClob length after commit is " + shortClob.length());
            // these should all give blob/clob data unavailable exceptions
            try
            {
                clob.length();
            }
            catch (SQLException e)
            {
    			TestUtil.dumpSQLExceptions(e);
	    	}
            try
            {
                clob.getSubString(2,3);
            }
            catch (SQLException e)
            {
    			TestUtil.dumpSQLExceptions(e);
	    	}
            try
            {
                clob.getAsciiStream();
            }
            catch (SQLException e)
            {
    			TestUtil.dumpSQLExceptions(e);
	    	}
            try
            {
                clob.position("foo",2);
            }
            catch (SQLException e)
            {
    			TestUtil.dumpSQLExceptions(e);
	    	}
            try
            {
                clob.position(clob,2);
            }
            catch (SQLException e)
            {
    			TestUtil.dumpSQLExceptions(e);
	    	}

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


    /*
        test accessing clob after closing the connection
        need to run prepareCLOBMAIN first
    */
	private static void clobTest95(Connection conn)
    {
		ResultSet rs;
		Statement stmt;
		System.out.println(START + "clobTest95");
        try
        {
			stmt = conn.createStatement();
			rs = stmt.executeQuery("select a,b from testCLOB_MAIN");
			// fetch row back, get the column as a clob.
            Clob clob = null, shortClob = null;
            int clobLength;
			while (rs.next())
            {
                clobLength = rs.getInt(2);
                if (clobLength == 10000)
                    clob = rs.getClob(1);
                if (clobLength == 26)
                    shortClob = rs.getClob(1);
			}
            rs.close();
			conn.commit();
            conn.close();

			try {
            // no problem accessing this after commit since it is in memory
            System.out.println("shortClob length after closing connection is " + shortClob.length());
			}
			catch (SQLException e)
			{
				if (isDerbyNet)
					System.out.println("EXPECTED SQL Exception: " + e.getMessage());
				else
					TestUtil.dumpSQLExceptions(e);
				
			}
            // these should all give blob/clob data unavailable exceptions
            try
            {
                clob.length();
            }
            catch (SQLException e)
            {
				if (isDerbyNet)
					System.out.println("EXPECTED SQL Exception: " + e.getMessage());
				else
					TestUtil.dumpSQLExceptions(e);
	    	}
            try
            {
                clob.getSubString(2,3);
            }
            catch (SQLException e)
            {
				if (isDerbyNet)
					System.out.println("EXPECTED SQL Exception: " + e.getMessage());
				else
					TestUtil.dumpSQLExceptions(e);
	    	}
            try
            {
                clob.getAsciiStream();
            }
            catch (SQLException e)
            {
				if (isDerbyNet)
					System.out.println("EXPECTED SQL Exception: " + e.getMessage());
				else
					TestUtil.dumpSQL