blobclob4blob.java

derby database source code.good for you.

                if (clob == null)
                    continue;
                clobLength = rs.getInt(2);
                arrayIndex = rs.getInt(3);
                if (clob.getSubString(1,3).equals(unicodeStrings[arrayIndex]))
                    System.out.println("Succeeded");
                else
                    System.out.println("Failed");
                if (clobLength > 5000)
                {
                    if (clob.getSubString(5004,3).equals(unicodeStrings[arrayIndex]))
                        System.out.println("Second time Succeeded");
                    else
                        System.out.println("Second time Failed");
                }
            }
            System.out.println("clobTest22 finished");
        }
		catch (SQLException e)
        {
			TestUtil.dumpSQLExceptions(e);
		}
		catch (Throwable e)
        {
			System.out.println("FAIL -- unexpected exception:" + e.toString());
			if (debug) e.printStackTrace();
		}
    }



    /*
    test position with a String argument
    need to run prepareCLOBMAIN first
    */
	private static void clobTest3(Connection conn)
    {
		ResultSet rs;
		Statement stmt;
		 System.out.println(START + "clobTest3");
		try
        {
			stmt = conn.createStatement();
			rs = stmt.executeQuery("select a,b from testCLOB_MAIN");
            int i = 0, clobLength = 0;
            Clob clob;
			while (rs.next())
            {
                i++;
                clob = rs.getClob(1);
                if (clob == null)
                    continue;
               clobLength = rs.getInt(2);
                if (clobLength > 20000)
                    continue;
                blobclob4BLOB.printPosition(i,"horse",1,clob, clobLength);
                blobclob4BLOB.printPosition(i,"ouch",1,clob, clobLength);
                blobclob4BLOB.printPosition(i,"\n",1,clob, clobLength);
                blobclob4BLOB.printPosition(i,"",1,clob, clobLength);
                blobclob4BLOB.printPosition(i,"Beginning",1,clob, clobLength);
                blobclob4BLOB.printPosition(i,"Beginning",2,clob, clobLength);
                blobclob4BLOB.printPosition(i,"position-69",1,clob, clobLength);
                blobclob4BLOB.printPosition(i,"This-is-position-204",1,clob, clobLength);
                blobclob4BLOB.printPosition(i,"I-am-hiding-here-at-position-5910",1,clob, clobLength);
                blobclob4BLOB.printPosition(i,"I-am-hiding-here-at-position-5910",5910,clob, clobLength);
                blobclob4BLOB.printPosition(i,"I-am-hiding-here-at-position-5910",5911,clob, clobLength);
                blobclob4BLOB.printPosition(i,"Position-9907",1,clob, clobLength);
            }
            System.out.println("clobTest3 finished");
        }
		catch (SQLException e) {
			TestUtil.dumpSQLExceptions(e);
		}
		catch (Throwable e) {
			System.out.println("FAIL -- unexpected exception:" + e.toString());
			if (debug) e.printStackTrace();
		}
    }


    /*
    test position with a unicode String argument
    need to run prepareUnicodeTable first
    */
	private static void clobTest32(Connection conn)
    {
		ResultSet rs;
		Statement stmt;
		System.out.println(START + "clobTest32");
		try
        {
			stmt = conn.createStatement();
			rs = stmt.executeQuery("select a,b,c from testUnicode");
            int i = 0, clobLength = 0, arrayIndex = 0;
            long pos = 0;
            Clob clob;
			while (rs.next())
            {
                i++;
                clob = rs.getClob(1);
                if (clob == null)
                    continue;
                clobLength = rs.getInt(2);
                arrayIndex = rs.getInt(3);

                pos = clob.position(unicodeStrings[arrayIndex],1);
                if (pos == 1)
                    System.out.println("Succeeded: Found unicode string " + arrayIndex +
                    " at position " + pos + ",row " + i);
                else
                    System.out.println("Failed: Found unicode string " + arrayIndex +
                    " at position " + pos + ",row " + i);

                pos = clob.position(unicodeStrings[arrayIndex],4000);
                if (pos == 5004 || (pos == -1 && clobLength < 4000))
                    System.out.println("Succeeded: Found unicode string " + arrayIndex +
                    " at position " + pos + ",row " + i);
                else
                    System.out.println("Failed: Found unicode string " + arrayIndex +
                    " at position " + pos + ",row " + i);
            }
            System.out.println("clobTest32 finished");
        }
		catch (SQLException e)
        {
			TestUtil.dumpSQLExceptions(e);
		}
		catch (Throwable e)
        {
			System.out.println("FAIL -- unexpected exception:" + e.toString());
			if (debug) e.printStackTrace();
		}
    }


    /*
    test position with a Clob argument
    need to run prepareCLOBMAIN and prepareSearchClobTable first
    */
	private static void clobTest4(Connection conn)
    {
		ResultSet rs, rs2;
		Statement stmt, stmt2;
		System.out.println(START + "clobTest4");
		try
        {
			stmt = conn.createStatement();
			rs = stmt.executeQuery("select a,b from testCLOB_MAIN");
            int i = 0, clobLength = 0;
            Clob clob;
			while (rs.next())
            {
                i++;
                clob = rs.getClob(1);
                if (clob == null)
                    continue;
                clobLength = rs.getInt(2);
                if (clobLength > 20000)
                    {
                        System.out.println("testCLOB_MAIN row " + i + " skipped (too large)");
                        continue;
                    }
                // inner loop over table of clobs to search for
                // clobs
			    stmt2 = conn.createStatement();
			    rs2 = stmt2.executeQuery("select a,b from searchClob");
                int j = 0, clobLength2 = 0;
                Clob searchClob;
                String searchStr;
			    while (rs2.next())
                {
                    j++;
                    searchClob = rs2.getClob(1);
                    if (searchClob == null)
                        continue;
                    clobLength2 = rs2.getInt(2);
                    if (clobLength2 > 20000)
                    {
                        System.out.println("searchClob row " + j + " skipped (too large)");
                        continue;
                    }
                    if (clobLength2 < 150)
                        searchStr = rs2.getString(1);
                    else
                        searchStr = null;

                    printPositionClob(i,searchStr,1,clob,j,searchClob);
                }
            }
            System.out.println("clobTest4 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();
		}
    }


    /*
    test position with a Clob argument containing unicode characters
    need to run prepareCLOBMAIN and prepareSearchClobTable first
    */
	private static void clobTest42(Connection conn)
    {
		ResultSet rs;
		Statement stmt;
		 System.out.println(START + "clobTest42");
		try
        {
			stmt = conn.createStatement();
			rs = stmt.executeQuery("select a,b,c from testUnicode");
            Clob[] clobArray = new Clob[numRowsUnicode];
            int i = 0;
            long pos = 0;
			while (rs.next())
            {
                clobArray[i++] = rs.getClob(1);
            }

            for (int j = 0; j < 3; j++)
            {
                pos = clobArray[j+3].position(clobArray[j],1);
                if (pos == 1)
                    System.out.println("Succeeded: Found clob at position " + pos + ",row " + j);
                else
                    System.out.println("Failed: Found clob at position " + pos + ",row " + j);
                // pos = clobArray[i*2].position(clobArray[i*3],1);
            }
            System.out.println("clobTest42 finished");
        }
		catch (SQLException e)
        {
			TestUtil.dumpSQLExceptions(e);
		}
		catch (Throwable e)
        {
			System.out.println("FAIL -- unexpected exception:" + e.toString());
			if (debug) e.printStackTrace();
		}
    }



    private static void printPositionClob(
        int rowNum,
        String searchStr,
        long position,
        Clob clob,
        int searchRowNum,
        Clob searchClob)
    {
        try
        {
            long result = clob.position(searchClob,position);
            if ("".equals(searchStr) && (result == 1)) {
				System.out.println("position(clob) FOUND @ 1 with empty search clob in clob of length " + clob.length());
                return;
			}
            if (result != -1)
            {
                System.out.print("Found ");
                if (searchStr != null)
                    System.out.print(searchStr);
                else
                    System.out.print("clob (row " + searchRowNum + ") ");
                System.out.println(" in row " + rowNum + " at position " + result);
            }
			else {
				System.out.println("position(clob) NOT FOUND " + rowNum + " searchStr " +
					(searchStr != null ? searchStr : ">150chars"));
			}
        }
		catch (SQLException e)
        {
			TestUtil.dumpSQLExceptions(e);
		}
    }


    /* datatype tests */

    // make sure clobs work for small CLOB fields
    // also test length method
	private static void clobTest51(Connection conn) {

		ResultSetMetaData met;
		ResultSet rs;
		Statement stmt;
		System.out.println(START + "clobTest51");
		try {
			stmt = conn.createStatement();
			stmt.execute("create table testCLOB10 (a CLOB(10))");

            PreparedStatement ps = conn.prepareStatement(
                "insert into testCLOB10 values(?)");
            String val = "";
            for (int i = 0; i < 10; i++)
            {
                // insert a string
                ps.setString(1, val);
                ps.executeUpdate();
                val += "x";
            }

			rs = stmt.executeQuery("select a from testCLOB10");
			met = rs.getMetaData();
			byte[] buff = new byte[128];
            int j = 0;
			// fetch all rows back, get the columns as clobs.
			while (rs.next())
            {
				// get the first column as a clob
                Clob clob = rs.getClob(1);
                if (clob == null)
                    continue;
				InputStream fin = clob.getAsciiStream();
				int columnSize = 0;
				for (;;)
                {
					int size = fin.read(buff);
					if (size == -1)
						break;
					columnSize += size;
				}
                if (columnSize != j)
                    System.out.println("FAIL - Expected clob size : " + j +
                        " Got clob size : " + columnSize);
                if (clob.length() != j)
                    System.out.println("FAIL - Expected clob length : " + j +
                        " Got clob length : " + clob.length());
                j++;
			}
            System.out.println("clobTest51 finished");
        }
		catch (SQLException e) {
			if (isDerbyNet)
				System.out.println("EXPECTED SQL Exception: " + e.getMessage());
			else
				TestUtil.dumpSQLExceptions(e);
		}
		catch (Throwable e) {
			System.out.println("FAIL -- unexpected exception:" + e.toString());
			if (debug) e.printStackTrace();
		}
    }


   // make sure cannot get a clob from an int column
	private static void clobTest52(Connection conn) {

		ResultSetMetaData met;
		ResultSet rs;
		Statement stmt;

		try {
			System.out.println(START + "clobTest52");
			stmt = conn.createStatement();
			System.out.println("create table testInteger (a integer)");
			stmt.execute("create table testInteger (a integer)");

            int i = 1;
            System.out.println("insert into testInteger values('158')");
            PreparedStatement ps = conn.prepareStatement("insert into testInteger values(158)");
            ps.executeUpdate();

			System.out.println("select a from testInteger");
			rs = stmt.executeQuery("select a from testInteger");
			met = rs.getMetaData();
			while (rs.next()) {
				// get the first column as a clob
				System.out.println("getClob(1)");
                Clob clob = rs.getClob(1);
                if (clob == null)
                    System.out.println("clob is null");
                else
                    System.out.println("clob is not null");
			}
            System.out.println("clobTest52 finished");
        }
		catch (SQLException e) {
			System.out.println("52: SQLException");
			if (isDerbyNet)
				System.out.println("EXPECTED SQL Exception: " + e.getMessage());
			else
				TestUtil.dumpSQLExceptions(e);
		}
		catch (Throwable e) {
			System.out.println("52: Throwable");
			System.out.println("FAIL -- unexpected exception:" + e.toString());
			if (debug) e.printStackTrace();
		}
    }


   // test creating a clob column, currently this doesn't work since we don't
   // have a clob datatype (get a syntax error on the create table statement) 
	private static void clobTest53(Connection conn) {

		ResultSetMetaData met;
		ResultSet rs;
		Statement stmt;
		System.out.println(START + "clobTest53"); 
		try {
			stmt = conn.createStatement();
			stmt.execute("create table testClobColumn (a clob(1K))");

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