import java.sql.*;
import java.io.*;

/* table and trigger:
-- table for basic test
   create table LOB1 (str1 Varchar(80), b_lob BLOB(50M))
-- table trigger inserts into
   create table t_lob1_log(oldvalue varchar(80), newvalue varchar(80), chng_time timestamp default current_timestamp);

-- trigger for basic tests: this works OK w/short BLOB but not long BLOBs
   create trigger t_lob1
	after update of str1 on lob1
	REFERENCING OLD AS old NEW AS new
    	FOR EACH ROW MODE DB2SQL
	insert into t_lob1_log(oldvalue, newvalue) 
		 values (old.str1, new.str1);

 - - - w/ 32659 BLOB we get failure
 update LOB1 set str1 = str1 || ' '";
      ERROR XCL30: An IOException was thrown when reading a 'BLOB' from an InputStream.
      ERROR XJ001: Java exception: ': java.io.EOFException'.

-- statement trigger test: Not even a statement level trigger will work:
   create trigger t_lob1
     after update of str1 on lob1
     FOR EACH statement MODE DB2SQL
     insert into t_lob1_log(oldvalue, newvalue) values ('did update', 'statement Trigger fired');
*/
	

public class blob_insert2
{
	public static void main(String[] args) {

		try {
			String url = "jdbc:derby:blobInsertDB;create=true";
			String trig = " create trigger t_lob1 after update of str1 on lob1 ";
			  trig = trig + " REFERENCING OLD AS old NEW AS new FOR EACH ROW MODE DB2SQL ";
			  trig = trig + " insert into t_lob1_log(oldvalue, newvalue) values (old.str1, new.str1)";

			Class.forName("org.apache.derby.jdbc.EmbeddedDriver").newInstance();
			Connection conn = DriverManager.getConnection(url);

            Statement s = conn.createStatement();
              s.executeUpdate("create table LOB1 (str1 Varchar(80), b_lob BLOB(50M))");
              s.executeUpdate("create table t_lob1_log(oldvalue varchar(80), newvalue varchar(80), chng_time timestamp default current_timestamp)");
              s.executeUpdate(trig);
              conn.commit();
              
            testBlob(conn, s, 1024);
            testBlob(conn, s, 16384);
            // largest successful
            testBlob(conn, s, 32658);
            // fails at 32659
            testBlob(conn, s, 32659);
            // other interesting values. Never get here.
            testBlob(conn, s, 1024 *32 -1);
            testBlob(conn, s, 1024 *32);
            testBlob(conn, s, 1024 *32+1);
            testBlob(conn, s, 1024 *64 -1);
            testBlob(conn, s, 1024 *64);
            testBlob(conn, s, 1024 *64+1);
           
        } catch (Exception e) {
            System.out.println("Error! "+e);
            e.printStackTrace();
        }
       
    }

    private static void testBlob(Connection conn, Statement s, int blobSize) throws SQLException, IOException {
        // --- add a blob
        System.out.println("Testing blob of size=" + blobSize);
        PreparedStatement ps = conn.prepareStatement("INSERT INTO LOB1 VALUES (?, ?)");
        
        ps.setString(1, blobSize +"");


   byte[] arr = new byte[blobSize];
for (int i = 0; i < arr.length; i++)
        	arr[i] = (byte)8;

        // - set the value of the input parameter to the input stream
        ps.setBinaryStream(2, new ByteArrayInputStream(arr) , blobSize);
        ps.execute();
        conn.commit();

        // --- reading the columns
        ResultSet rs = s.executeQuery("SELECT * FROM LOB1 WHERE str1 = '" + blobSize +"'");
        while (rs.next()) {
            java.sql.Blob ablob = rs.getBlob(2);
            java.io.InputStream ip = rs.getBinaryStream(2);
            int c = ip.read();
            while (c > 0) {
                System.out.print((char)c);
                c = ip.read();
            }
            System.out.print("\n");
            // ...
        }
   System.out.println(" . . Now executing update to fire the trigger ");
   s.executeUpdate("update LOB1 set str1 = str1 || ' '");
   s.executeUpdate("DELETE FROM LOB1");
   System.out.println("PASSED");
    }
}