blobclob4blob.java

derby database source code.good for you.

/*

   Derby - Class org.apache.derbyTesting.functionTests.tests.jdbcapi.blobclob4BLOB

   Copyright 2003, 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.jdbcapi;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.FileReader;
import java.io.FileWriter;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.OutputStream;
import java.io.OutputStreamWriter;
import java.io.Reader;
import java.io.Writer;
import java.sql.Blob;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.io.UnsupportedEncodingException;
import java.sql.Statement;
import java.sql.Types;

import org.apache.derby.tools.JDBCDisplayUtil;
import org.apache.derby.tools.ij;
import org.apache.derbyTesting.functionTests.util.Formatters;
import org.apache.derbyTesting.functionTests.util.TestUtil;

/**
 * Test of JDBC blob and clob
 *
 * @author paulat
 */

public class blobclob4BLOB { 

	static String[] fileName;
	static long[] fileLength;
    static int numFiles;
    static int numRows;
    static int numStrings;
	static String[] unicodeStrings;
    static int numRowsUnicode;
    static String unicodeFileName;

	static boolean isDerbyNet = false;
	static boolean debug = true;
	private static final String START = "\nSTART: ";

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

		fileName[0] = "extin/short.utf";	// set up a short (fit in one page) blob/clob
		fileName[1] = "extin/littleclob.utf"; // set up a long (longer than a page) blob/clob
		fileName[2] = "extin/empty.utf"; // set up a blob/clob with nothing in it
		fileName[3] = "extin/searchclob.utf"; // set up a blob/clob to search with
		fileName[4] = "extin/aclob.utf"; // set up a really long (over 300K) blob/clob

        numRows = 10;

        numStrings = 3;
        unicodeStrings = new String[numStrings];
        unicodeStrings[0] = "\u0061\u0062\u0063";    // abc
        unicodeStrings[1] = "\u0370\u0371\u0372";
        unicodeStrings[2] = "\u05d0\u05d1\u05d2";
        numRowsUnicode = 6;

        unicodeFileName = "extinout/unicodeFile.txt";
    }


	public static void main(String[] args)
    {
		System.out.println("Test blobclob starting");

		isDerbyNet = TestUtil.isNetFramework();

		try
        {
			// use the ij utility to read the property file and
			// make the initial connection.
			ij.getPropertyArg(args);
			Connection conn = ij.startJBMS();
            // turn off autocommit, otherwise blobs/clobs cannot hang around
            // until end of transaction
            conn.setAutoCommit(false);

            prepareCLOBMAIN(conn);
            prepareSearchClobTable(conn);
            prepareUnicodeTable(conn);
            prepareUnicodeFile(conn);
            // prepareBinaryTable(conn);

            setCharacterStreamTest(conn);

            // unicodeTest();
            // clobTestGroupfetch(conn);

            clobTest0(conn);
			clobTest11(conn);
			clobTest12(conn);
			clobTest2(conn);
            clobTest22(conn);
            clobTest3(conn);
            clobTest32(conn);
            clobTest4(conn);
            clobTest42(conn);
			clobTest51(conn);
			clobTest52(conn);
			clobTest53(conn);
			clobTest54(conn);
            clobTest6(conn);
            clobTest7(conn);

			clobTest8(conn);

			clobTest91(conn);
            clobTest92(conn);
            clobTest93(conn);
            clobTest94(conn);
            clobTest95(conn);
  
           // restart the connection
            conn = ij.startJBMS();
            conn.setAutoCommit(false);
            clobTest96(conn);

            prepareBlobTable(conn);
            prepareSearchBlobTable(conn);

            blobTest0(conn);
			blobTest2(conn);
            blobTest3(conn);
            blobTest4(conn);
            blobTest51(conn);
            blobTest52(conn);
            blobTest53(conn);
            blobTest54(conn);
            blobTest6(conn);
            blobTest7(conn);
			blobTest91(conn);
            blobTest92(conn);
            blobTest93(conn);
            blobTest94(conn);
            blobTest95(conn);
     
            // restart the connection
            conn = ij.startJBMS();
            conn.setAutoCommit(false);
            blobTest96(conn);

            clobTestSelfDestructive(conn);
            clobTestSelfDestructive2(conn);

            conn.commit();
            clobNegativeTest_Derby265(conn);
            blobNegativeTest_Derby265(conn);
            conn.close();
            System.out.println("FINISHED TEST blobclob :-)");

		}
        catch (SQLException e)
        {
			TestUtil.dumpSQLExceptions(e);
			if (debug) e.printStackTrace();
		}
        catch (Throwable e)
        {
			System.out.println("xFAIL -- unexpected exception:" + e.toString());
//            e.fillInStackTrace();
            if (debug) e.printStackTrace();
		}
		System.out.println("Test blobclob finished\n");
    }


    private static void insertRow(PreparedStatement ps, String s)
        throws SQLException
    {
		ps.clearParameters();
        ps.setString(1, s);
        ps.setInt(2, s.length());
        ps.executeUpdate();
    }

    private static void insertRow(PreparedStatement ps, String s, int i)
        throws SQLException
    {
        ps.setString(1, s);
        ps.setInt(2, s.length());
        ps.setInt(3, i);
        ps.executeUpdate();
    }

    private static void insertRow(PreparedStatement ps, byte[] b)
        throws SQLException
    {
        ps.setBytes(1, b);
        ps.setInt(2, b.length);
        ps.executeUpdate();
    }

    /*
        Set up a table with all kinds of CLOB values,
        some short (less than 1 page), some long (more than 1 page)
        some very large (many pages).
        Table has 2 cols: the first is the value, the second is the length of
        the value.
        (Also sets the fileLength array.)
    */
    private static void prepareCLOBMAIN(Connection conn)
    {
        System.out.println(START +"prepareCLOBMAIN");
		ResultSet rs;
		Statement stmt;

		try {
			stmt = conn.createStatement();
			stmt.execute(
		// creating table small then add large column - that way forcing table to have default small page size, but have large rows.
                "create table testCLOB_MAIN (b integer)");
		stmt.execute("alter table testCLOB_MAIN add column a CLOB(1M)");
            PreparedStatement ps = conn.prepareStatement(
                "insert into testCLOB_MAIN (a, b) values(?,?)");

            // insert small strings
			insertRow(ps,"");
            insertRow(ps,"you can lead a horse to water but you can't form it into beverage");
            insertRow(ps,"a stitch in time says ouch");
            insertRow(ps,"here is a string with a return \n character");


            // insert larger strings using setAsciiStream
            for (int i = 0; i < numFiles; i++)
            {
                // prepare an InputStream from the file
                File file = new File(fileName[i]);
                fileLength[i] = file.length();
				/*
				System.out.println("inserting filename[" +i +
								   "]" + fileName[i] +
								   " length: " + fileLength[i]);
				*/
                InputStream fileIn = new FileInputStream(file);

                System.out.println("===> inserting " + fileName[i] + " length = "
				    				   + fileLength[i]);

                // insert a streaming column
				ps.setAsciiStream(1, fileIn, (int)fileLength[i]);
                ps.setInt(2, (int)fileLength[i]);
                ps.executeUpdate();
				ps.clearParameters();
                fileIn.close();
            }

            // insert a null
            ps.setNull(1, Types.CLOB);
            ps.setInt(2, 0);
            ps.executeUpdate();

            conn.commit();

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

        }
		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();
		}
        //System.out.println("prepareCLOBMAIN finished");
    }



    /*
        Set up a table with clobs to search for
        most short (less than 1 page), some long (more than 1 page)
        some very large (many pages) ??
    */
    private static void prepareSearchClobTable(Connection conn)
    {
        System.out.println(START + "prepareSearchClobTable");
		ResultSet rs;
		Statement stmt;

		try
        {
			stmt = conn.createStatement();
			// creating table small then add large column - that way forcing table to have default small page size, but have large rows.
			stmt.execute("create table searchClob (b integer)");
			stmt.execute("alter table searchClob add column a CLOB(300k)");
            PreparedStatement ps = conn.prepareStatement(
                "insert into searchClob (a, b) values(?,?)");
            insertRow(ps,"horse");
            insertRow(ps,"ouch");
            insertRow(ps,"\n");
            insertRow(ps,"");
            insertRow(ps,"Beginning");
            insertRow(ps,"position-69");
            insertRow(ps,"I-am-hiding-here-at-position-5910");
            insertRow(ps,"Position-9907");

            // insert larger strings using setAsciiStream
            for (int i = 0; i < numFiles; 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("inserting filename[" +i +
								   "]" + fileName[i] +
								   " length: " + fileLength[i]);
				*/
                System.out.println("===> inserting " + fileName[i] + " length = "
				    				   + fileLength[i]);

                // insert a streaming column

                ps.setAsciiStream(1, fileIn, (int)fileLength[i]);
                ps.setInt(2, (int)fileLength[i]);
                ps.executeUpdate();
				ps.clearParameters();
                fileIn.close();
            }

            // insert a null
            ps.setNull(1, Types.CLOB);
            ps.setInt(2, 0);
            ps.executeUpdate();

            conn.commit();
        }
		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();
		}
        System.out.println("prepareSearchClobTable finished");
    }


    /*
        Set up a table with unicode strings in it
        some short (less than 1 page), some long (more than 1 page)
        Table has 3 cols: the first is the value, the second is the length of
        the value, the third is the array index (or else -1 for the ones from files).
        (Also sets the fileLength array.)
        Try slurping the thing into a String.
    */
    private static void prepareUnicodeTable(Connection conn)
    {
		ResultSet rs;
		Statement stmt;
		 System.out.println(START + "prepareUnicodeTable");
		try {
			stmt = conn.createStatement();
			// creating table small then add large column - that way forcing table to have default small page size, but have large rows.
			stmt.execute("create table testUnicode (b integer, c integer)");
			stmt.execute("alter table testUnicode add column a CLOB(100k)");
            PreparedStatement ps = conn.prepareStatement(
            //    "insert into testUnicode values(?,?,?)");
                "insert into testUnicode (a, b, c)  values(?,?,?)");

            // insert small strings

            for (int i = 0; i < numStrings; i++)
            {
                insertRow(ps,unicodeStrings[i],i);
            }

            StringBuffer sb = new StringBuffer(5000);
            for (int i = 0; i < 5000; i++)
                sb.append('q');
            String largeString = new String(sb);

            // insert larger strings