blobclob4blob.java

derby database source code.good for you.

            for (int i = 0; i < numStrings; i++)
            {
                insertRow(ps,unicodeStrings[i] + largeString + unicodeStrings[i] + "pppppppppp",i);
            }
            conn.commit();

            // set numRows
			rs = stmt.executeQuery("select count(*) from testUnicode");
            int realNumRows = -1;
			if (rs.next())
                realNumRows = rs.getInt(1);
            if (realNumRows <= 0)
                System.out.println("FAIL. No rows in table testUnicode");
            if (realNumRows != numRowsUnicode)
                System.out.println("FAIL. numRowsUnicode is incorrect");

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

    }


    /*
      Tests PreparedStatement.setCharacterStream
    */
    private static void setCharacterStreamTest(Connection conn)
    {
		ResultSet rs;
		Statement stmt;
		 System.out.println(START + "setCharacterStreamTest");
		try
        {
			stmt = conn.createStatement();
			// forcing table with default table space.
			stmt.execute("call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize','4096')");
			stmt.execute("create table testUnicode2 (a CLOB(100k))");
			stmt.execute("call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize','0')");
            PreparedStatement ps = conn.prepareStatement(
                "insert into testUnicode2 values(?)");

            // insert large string using setCharacterStream
            // prepare an InputStream from the file
            File file = new File(unicodeFileName);
            InputStream fileIS = new FileInputStream(file);
            Reader filer = new InputStreamReader(fileIS,"UTF8");
            // insert a streaming column
            ps.setCharacterStream(1, filer, 5009);
            ps.executeUpdate();
            filer.close();
            conn.commit();

			rs = stmt.executeQuery("select a from testUnicode2");
            while (rs.next())
            {
                Clob clob = rs.getClob(1);
                System.out.println("Length of clob is " + clob.length());
                Reader r = clob.getCharacterStream();
                char[] buf = new char[3];
                for (int i = 0; i < numStrings; i++)
                {
                    r.read(buf);
                    if (unicodeStrings[i].equals(new String(buf)))
                        System.out.println("unicode string " + i + " matched");
                    else
                        System.out.println("unicode string " + i + " not matched");
                }
                for (int i = 0; i < 5000; i++)
                {
                    int c = r.read();
                    if (c == -1)
                    {
                        System.out.println("EOF reached at i = " + i);
                        break;
                    }
                    if ((char)c != 'p')
                    {
                        System.out.println("A p was missed, got a " + (char)c);
                    }
                }
                if (r.read() != -1)
                    System.out.println("EOF was missed");
                else
                    System.out.println("EOF matched");
            }
            conn.commit();

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


    /*
      Make a file with unicode stuff in it.
     */
    private static void prepareUnicodeFile(Connection conn)
    {
    	 System.out.println(START + "prepareUnicodeFile");
		try
        {
            File file = new File(unicodeFileName);
            OutputStream fos = new FileOutputStream(file);
            Writer filew = new OutputStreamWriter(fos,"UTF8");
            // FileWriter filew = new FileWriter(file);
            filew.write(unicodeStrings[0]);
            filew.write(unicodeStrings[1]);
            filew.write(unicodeStrings[2]);
            for (int i = 0; i < 5000; i++)
                filew.write('p');
            filew.close();

            InputStream fis = new FileInputStream(file);
            Reader filer = new InputStreamReader(fis,"UTF8");
            // FileReader filer = new FileReader(file);
            char bufs[][] = new char[numStrings][3];
            for (int i = 0; i < numStrings; i++)
            {
                filer.read(bufs[i]);
                String s = new String(bufs[i]);
                if (s.equals(unicodeStrings[i]))
                    System.out.println("unicode string " + i + " correct");
                else
                    System.out.println("FAILED: unicode string " + i + " incorrect");
            }
            for (int i = 0; i < 5000; i++)
                if (filer.read() != 'p')
                    System.out.println("Not a p : i = " + i);
            if (filer.read() != -1)
                System.out.println("Not EOF");
            filer.close();
            System.out.println("Finished prepareUnicodeFile");
        }
		catch (Throwable e)
        {
			System.out.println("FAIL -- unexpected exception:" + e.toString());
			if (debug) e.printStackTrace();
		}
    }



    /*
        basic test of getAsciiStream
        also tests length
        need to run prepareCLOBMAIN first
    */
	private static void clobTest0(Connection conn)
    {
		ResultSet rs;
		Statement stmt;
		 System.out.println(START + "clobTest0");
        try
        {
			stmt = conn.createStatement();
			rs = stmt.executeQuery("select a,b from testCLOB_MAIN");
			byte[] buff = new byte[128];
			// fetch row back, get the column as a clob.
            Clob clob;
            int clobLength, i = 0;
			while (rs.next()) {
                i++;
				// get the first column in select as a 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;
				}
                clobLength = rs.getInt(2);

                if (columnSize != clobLength)
					System.out.println("test failed, columnSize should be " + clobLength
					   + ", but it is " + columnSize + ", i = " + i);
                if (columnSize != clob.length())
				{
					System.out.println("test failed, clob.length() should be " +  columnSize
					   + ", but it is " + clob.length() + ", i = " + i);
				}
			}
			
            conn.commit();
            System.out.println("clobTest0 finished");
        }
		catch (SQLException e) {
			TestUtil.dumpSQLExceptions(e);
		}
		catch (Throwable e) {
			System.out.println("FAIL -- unexpected exception:" + e.toString());
			if (debug) e.printStackTrace();
		}
    }


    /*
        basic test of getCharacterStream
        also tests length
        need to run prepareCLOBMAIN first
    */
	private static void clobTest11(Connection conn) {

		ResultSetMetaData met;
		ResultSet rs;
		Statement stmt;
		 System.out.println(START + "clobTest1");
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery("select a,b from testCLOB_MAIN");
			met = rs.getMetaData();
			char[] buff = new char[128];
			// fetch row back, get the column as a clob.
            int i = 0, clobLength = 0;
			while (rs.next()) {
                i++;
				// get the first column as a clob
                Clob clob = rs.getClob(1);
                if (clob == null)
                    continue;
				Reader reader = clob.getCharacterStream();
				int columnSize = 0;
				for (;;) {
					int size = reader.read(buff);
					if (size == -1)
						break;
                    // System.out.println("the next buffer is :" + buff);
					columnSize += size;
				}
                clobLength = rs.getInt(2);
                if (columnSize != clobLength)
					System.out.println("test failed, columnSize should be " + clobLength
					   + ", but it is " + columnSize + ", i = " + i);
                if (columnSize != clob.length())
					System.out.println("test failed, clob.length() should be " +  columnSize
					   + ", but it is " + clob.length() + ", i = " + i);
				
			}
            conn.commit();
            System.out.println("clobTest11 finished");
        }
		catch (SQLException e) {
			TestUtil.dumpSQLExceptions(e);
		}
		catch (Throwable e) {
			System.out.println("FAIL -- unexpected exception:" + e.toString());
            if (debug) e.printStackTrace();
		}
    }


    /*
        test of getCharacterStream on a table containing unicode characters
        need to run prepareUnicodeTable first
     */
	private static void clobTest12(Connection conn)
    {
		ResultSet rs;
		Statement stmt;
		System.out.println(START + "clobTest12");
		try
        {
			stmt = conn.createStatement();
			rs = stmt.executeQuery("select a,b,c from testUnicode");
            int i = 0, colLength = 0, arrayIndex = 0;
			while (rs.next())
            {
                i++;
                colLength = rs.getInt(2);
                arrayIndex = rs.getInt(3);
                Clob clob = rs.getClob(1);
                if (clob == null)
                {
                    System.out.println("row " + i + " is null, skipped");
                    continue;
                }
                Reader reader = clob.getCharacterStream();

				int columnSize = 0, c;
                String compareString = "";
				for (;;)
                {
					c = reader.read();
					if (c == -1)
						break;
                    if (columnSize < 3)
                        compareString += (char)c;
					columnSize ++;
				}
                if (compareString.equals(unicodeStrings[arrayIndex]))
                    System.out.println("Succeeded to match, row " + i);
                else
                {
                    System.out.println("Failed to match, row " + i +
                    ". compareString = " + compareString + ". arrayIndex = " +
                    arrayIndex + ". unicodeStrings[arrayIndex] = " +
                    unicodeStrings[arrayIndex]);

                }
                if (columnSize != colLength)
					System.out.println("test failed, columnSize should be " + colLength
					   + ", but it is " + columnSize + ", i = " + i);
                else
                    System.out.println("PASSED, row " + i + ", length was " + columnSize);
			}
            conn.commit();
            System.out.println("clobTest12 finished");
        }
		catch (SQLException e)
        {
			TestUtil.dumpSQLExceptions(e);
		}
		catch (Throwable e)
        {
			System.out.println("FAIL -- unexpected exception:" + e.toString());
            if (debug) e.printStackTrace();
		}
    }





    /*
    test getSubString
    need to run prepareCLOBMAIN first
    */
	private static void clobTest2(Connection conn)
    {
		ResultSet rs;
		Statement stmt;
		System.out.println(START + "clobTest2");
		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);
                blobclob4BLOB.printInterval(clob, 9905, 50, 0, i, clobLength);
                blobclob4BLOB.printInterval(clob, 5910, 150, 1, i, clobLength);
                blobclob4BLOB.printInterval(clob, 5910, 50, 2, i, clobLength);
                blobclob4BLOB.printInterval(clob, 204, 50, 3, i, clobLength);
                blobclob4BLOB.printInterval(clob, 68, 50, 4, i, clobLength);
                blobclob4BLOB.printInterval(clob, 1, 50, 5, i, clobLength);
                blobclob4BLOB.printInterval(clob, 1, 1, 6, i, clobLength);
                /*
                System.out.println(i + "(0) " + clob.getSubString(9905,50));
                System.out.println(i + "(1) " + clob.getSubString(5910,150));
                System.out.println(i + "(2) " + clob.getSubString(5910,50));
                System.out.println(i + "(3) " + clob.getSubString(204,50));
                System.out.println(i + "(4) " + clob.getSubString(68,50));
                System.out.println(i + "(5) " + clob.getSubString(1,50));
                System.out.println(i + "(6) " + clob.getSubString(1,1));
                */
                if (clobLength > 100)
                {
                    String res = clob.getSubString(clobLength-99,200);
                    System.out.println(i + "(7) ");
                    if (res.length() != 100)
                        System.out.println("FAIL : length of substring is " +
                            res.length() + " should be 100");
                    else
                        System.out.println(res);
                }
            }
            System.out.println("clobTest2 finished");
        }
		catch (SQLException e) {
			TestUtil.dumpSQLExceptions(e);
		}
		catch (Throwable e) {
			System.out.println("FAIL -- unexpected exception:" + e.toString());
			if (debug) e.printStackTrace();
		}
    }


    /*
    test getSubString with unicode
    need to run prepareUnicodeTable first
    */
	private static void clobTest22(Connection conn)
    {
		ResultSet rs;
		Statement stmt;
		System.out.println(START + "clobTest22");
		try
        {
			stmt = conn.createStatement();
			rs = stmt.executeQuery("select a,b,c from testUnicode");
            int i = 0, clobLength = 0, arrayIndex = 0;
            Clob clob;
			while (rs.next())
            {
                i++;
                System.out.print("Row " + i + " : ");
                clob = rs.getClob(1);