Package org.apache.derbyTesting.functionTests.tests.jdbcapi

Source Code of org.apache.derbyTesting.functionTests.tests.jdbcapi.blobclob4BLOB

/*

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

   Licensed to the Apache Software Foundation (ASF) under one or more
   contributor license agreements.  See the NOTICE file distributed with
   this work for additional information regarding copyright ownership.
   The ASF licenses this file to You 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.ByteArrayInputStream;
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.StringBufferInputStream;
import java.io.StringReader;
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 java.util.zip.CRC32;

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;
import java.util.Arrays;

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

public class blobclob4BLOB {

  static String[] fileName;
    static long[] fileCRC32;
  static String[] basefileName; // for printing messages so that no path info is in .out
  static String filePath;
  static String unicodeFilePath;
  static String sep;
  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;
    filePath = "extin";
    fileName = new String[numFiles];
    basefileName = new String[numFiles];
    fileLength = new long[numFiles];
        fileCRC32 = new long[numFiles];

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

    for (int i = 0 ; i < numFiles; i++)
    {
      basefileName[i] = fileName[i];
    }

        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;

        unicodeFilePath = "extinout";
        unicodeFileName = "unicodeFile.txt";
    }


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

    isDerbyNet = TestUtil.isNetFramework();

    try
        {
            // first check to see if the path to extin/out dir is ok.
            sep = System.getProperty("file.separator");
            boolean exists = (new File(filePath, fileName[0])).exists();
            if (!exists)
            {
                String userDir = System.getProperty("user.dir");
                filePath = userDir + sep + ".." + sep + filePath;
                unicodeFilePath = userDir + sep + ".." + sep + unicodeFilePath;
            }
            for (int i=0; i < numFiles; i++)
            {
                fileName[i] = filePath + sep + fileName[i];
               
                FileInputStream fis = new FileInputStream(fileName[i]);
                fileCRC32[i] = getStreamCheckSum(fis);
            }
            unicodeFileName = unicodeFilePath + sep + unicodeFileName;

      // 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);
            clobTest13Trigger(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();
           
            // do not run these tests with DerbyNet
            if (!TestUtil.isJCCFramework()) {
                clobTest10(conn);
                clobTest14(conn);
            }
 
            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);
            blobTest8Trigger(conn);
      blobTest91(conn);
            blobTest92(conn);
            blobTest93(conn);
            blobTest94(conn);
            blobTest95(conn);
    
            // restart the connection
            conn = ij.startJBMS();
            conn.setAutoCommit(false);
            blobTest96(conn);
           
            // do not run these tests with DerbyNet
            if (!TestUtil.isJCCFramework()) {
                blobTest10(conn);
                blobTest11(conn);
            }

            clobTestSelfDestructive(conn);
            clobTestSelfDestructive2(conn);

            conn.commit();
            clobNegativeTest_Derby265(conn);
            blobNegativeTest_Derby265(conn);

            // restart the connection for cleaning up
            conn = ij.startJBMS();
            String[] testObjects = {"table testclob_main", "table searchclob",
                                  "table testunicode", "table testunicode2",
                                  "table testclob10", "table testinteger",
                                  "table testclobcolumn", "table testclob2",
                                  "table testclob7", "table testlongrowclob",
                                  "table testblob", "table searchblob",
                                  "table testvarbinary", "table testinteger2",
                                  "table testblobcolumn", "table testblob2",
                                  "table testblobx", "table testlongrowblob",
                                  "table maps", "table maps_blob"};
            Statement stmt = conn.createStatement();
            TestUtil.cleanUpTest(stmt, testObjects);
            conn.close();
            System.out.println("FINISHED TEST blobclob :-)");
    }
        catch (SQLException e)
        {
      TestUtil.dumpSQLExceptions(e);
      if (debug) e.printStackTrace(System.out);
    }
        catch (Throwable e)
        {
      System.out.println("xFAIL -- unexpected exception:" + e.toString());
//            e.fillInStackTrace();
            if (debug) e.printStackTrace(System.out);
    }
    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, IOException
    {
        ps.setBytes(1, b);
        ps.setInt(2, b.length);
        ps.setLong(3, getStreamCheckSum(new ByteArrayInputStream(b)));
        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 +
                   "]" + basefileName[i] +
                   " length: " + fileLength[i]);
        */
                InputStream fileIn = new FileInputStream(file);

                System.out.println("===> inserting " + basefileName[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");
            rs.close();

        }
    catch (SQLException e) {
      TestUtil.dumpSQLExceptions(e);
      if (debug) e.printStackTrace(System.out);
    }
    catch (Throwable e) {
      System.out.println("FAIL -- unexpected exception:" + e.toString());
      if (debug) e.printStackTrace(System.out);
    }
        //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 +
                   "]" + basefileName[i] +
                   " length: " + fileLength[i]);
        */
                System.out.println("===> inserting " + basefileName[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(System.out);
    }
    catch (Throwable e) {
      System.out.println("FAIL -- unexpected exception:" + e.toString());
      if (debug) e.printStackTrace(System.out);
    }
        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
            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(System.out);
    }

    }


    /*
      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(System.out);
    }
    }


    /*
      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(System.out);
    }
    }



    /*
        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);
        }
      }
      rs.close();
            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(System.out);
    }
    }


    /*
        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);
       
      }
            rs.close();
            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(System.out);
    }
    }


    /*
        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(System.out);
    }
    }

    /**
     * Test triggers on CLOB columns.
    */
    private static void clobTest13Trigger(Connection conn)
    {
        System.out.println(START + "clobTest13Trigger");
        try {
            Statement stmt = conn.createStatement();
            stmt.executeUpdate("CREATE TABLE clobTest13TriggerA (a CLOB(400k), b int)");
            stmt.executeUpdate("CREATE TABLE clobTest13TriggerB (a CLOB(400k), b int)");
            stmt.executeUpdate(
                    "create trigger T13A after update on testCLOB_MAIN " +
                    "referencing new as n old as o " +
                    "for each row mode db2sql "+
                    "insert into clobTest13TriggerA(a, b) values (n.a, n.b)");
            stmt.executeUpdate(
                    "create trigger T13B after INSERT on clobTest13TriggerA " +
                    "referencing new_table as n " +
                    "for each statement mode db2sql "+
                    "insert into clobTest13TriggerB(a, b) select n.a, n.b from n");           
           
            conn.commit();
            ResultSet rs = stmt.executeQuery(
                  "select a,length(a),b  from testCLOB_MAIN order by b");
           
            showClobContents("testCLOB_MAIN", rs);
           
            rs.close();
            conn.commit();
            int rowCount = stmt.executeUpdate("UPDATE testCLOB_MAIN set b = b + 0");
            System.out.println("main update row count :" + rowCount);
            conn.commit();
            rs = stmt.executeQuery(
               "select a,length(a),b from clobTest13TriggerA order by b");
            showClobContents("row trigger", rs);
            rs.close();
            conn.commit();
           
            rs = stmt.executeQuery(
            "select a,length(a),b from clobTest13TriggerB order by b");
            showClobContents("statement trigger", rs);
            rs.close();
           conn.commit();
        
            stmt.executeUpdate("DROP TRIGGER T13A");
            stmt.executeUpdate("DROP TABLE clobTest13TriggerB");
            stmt.executeUpdate("DROP TABLE clobTest13TriggerA");
           
            stmt.close();
            conn.commit();
            System.out.println("clobTest13Trigger finished");
        }
        catch (SQLException e) {
            TestUtil.dumpSQLExceptions(e);
            do {
            e.printStackTrace(System.out);
            e = e.getNextException();
            } while (e != null);
           
        }
        catch (Throwable e) {
            System.out.println("FAIL -- unexpected exception:" + e.toString());
            if (debug) e.printStackTrace(System.out);
        }
       
    }




    /*
    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);
                blobclob4BLOB.printInterval(
                        clob, 1, 0, 7, i, clobLength); // length 0 at start
                blobclob4BLOB.printInterval(
                        clob, clobLength + 1, 0, 8, i, clobLength); // and end
                /*
                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 + "(9) ");
                    if (res.length() != 100)
                        System.out.println("FAIL : length of substring is " +
                            res.length() + " should be 100");
                    else
                        System.out.println(res);
                }
            }
            rs.close();
            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(System.out);
    }
    }


    /*
    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);
                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(System.out);
    }
    }



    /*
    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);
            }
            rs.close();
            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(System.out);
    }
    }


    /*
    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(System.out);
    }
    }


    /*
    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);
                }
            }
            rs.close();
            System.out.println("clobTest4 finished");
        }
    catch (SQLException e) {
      TestUtil.dumpSQLExceptions(e);
      if (debug) e.printStackTrace(System.out);
    }
    catch (Throwable e) {
      System.out.println("FAIL -- unexpected exception:" + e.toString());
      if (debug) e.printStackTrace(System.out);
    }
    }


    /*
    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(System.out);
    }
    }



    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(System.out);
    }
    }


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


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

   
    /*
        make sure setClob doesn't work on an int column
        need to run prepareCLOBMAIN first
    */
  private static void clobTest54(Connection conn)
    {
    ResultSet rs;
    Statement stmt1, stmt2;
    System.out.println(START + "clobTest54");
        try
        {
      stmt1 = conn.createStatement();
      stmt1.execute("create table testClob2 (a integer, b integer)");
            PreparedStatement ps = conn.prepareStatement(
                "insert into testClob2 values(?,?)");
      stmt2 = conn.createStatement();
      rs = stmt2.executeQuery("select a,b from testCLOB_MAIN");
            Clob clob;
            int clobLength;
      while (rs.next())
            {
        // get the first ncolumn as a clob
                clob = rs.getClob(1);
                if (clob == null)
                    continue;
                clobLength = rs.getInt(2);
                ps.setClob(1,clob);
                ps.setInt(2,clobLength);
                ps.executeUpdate();
      }
            rs.close();
            conn.commit();

            System.out.println("clobTest54 finished");
        }
    catch (SQLException e) {
      // Can't do a setClob on an int column. This is expected
        TestUtil.dumpSQLExceptions(e,true);
    }
    catch (Throwable e) {
      System.out.println("FAIL -- unexpected exception:" + e.toString());
      if (debug) e.printStackTrace(System.out);
    }
    System.out.println("end clobTest54");
    }


    /*
    test raising of exceptions
    need to run prepareCLOBMAIN first
    */
  private static void clobTest6(Connection conn)
    {
    ResultSet rs;
    Statement stmt;
    System.out.println(START + "clobTest6");
    try
        {
      stmt = conn.createStatement();
      rs = stmt.executeQuery("select a,b from testCLOB_MAIN");
            int i = 0, clobLength = 0;
            Clob clob;
      rs.next();
            clob = rs.getClob(1);
            clobLength = rs.getInt(2);
            rs.close();
            if (clob == null)
                return;

           
            // 0 or negative position value
        try
        {
          clob.getSubString(0,5);
          System.out.println("FAIL = clob.getSubString(0,5)");
        }
        catch (SQLException e)
        {
        boolean isExpected = isOutOfBoundException(e);
                            
                TestUtil.dumpSQLExceptions(e, isExpected);
        }
     
            // negative length value
            try
            {
                clob.getSubString(1,-76);
        System.out.println("FAIL = getSubString(1,-76)");
            }
            catch (SQLException e)
            {
                TestUtil.dumpSQLExceptions(e, isOutOfBoundException(e));
            }
            // boundary negative 1 length
            try {
                clob.getSubString(1,-1);
                System.out.println("FAIL = getSubString(1,-1)");
            } catch (SQLException e) {
                 TestUtil.dumpSQLExceptions(e, isOutOfBoundException(e));
             }
            // before start with length zero
            try
            {
                clob.getSubString(0,0);
         System.out.println("FAIL = getSubString(0,0)");
           }
            catch (SQLException e)
            {
                TestUtil.dumpSQLExceptions(e, isOutOfBoundException(e));
            }
            // 2 past end with length 0
            try {
                clob.getSubString(clobLength + 2,0);
                System.out.println("FAIL = getSubString(clobLength + 2,0)");
            catch (SQLException e) {
                TestUtil.dumpSQLExceptions(e, isOutOfBoundException(e));
            }
            // 0 or negative position value
            try
            {
                clob.position("xx",-4000);
        System.out.println("FAIL = position('xx',-4000)");
            }
            catch (SQLException e)
            {
                TestUtil.dumpSQLExceptions(e, isOutOfBoundException(e));
            }
            // null pattern
            try
            {
                clob.position((String) null,5);
        System.out.println("FAIL = position((String) null,5)");
            }
            catch (SQLException e)
            {
                TestUtil.dumpSQLExceptions(e, "XJ072".equals(e.getSQLState()));
            }
            // 0 or negative position value
            try
            {
                clob.position(clob,-42);
        System.out.println("FAIL = position(clob,-42)");
            }
            catch (SQLException e)
            {
                TestUtil.dumpSQLExceptions(e, isOutOfBoundException(e));
            }
            // null pattern
            try
            {
                clob.position((Clob) null,5);
        System.out.println("FAIL = pposition((Clob) null,5)");
            }
            catch (SQLException e)
            {
                TestUtil.dumpSQLExceptions(e, "XJ072".equals(e.getSQLState()));
            }
            System.out.println("clobTest6 finished");
        }
    catch (SQLException e) {
      TestUtil.dumpSQLExceptions(e);
    }
    catch (Throwable e) {
      System.out.println("FAIL -- unexpected exception:" + e.toString());
      if (debug) e.printStackTrace(System.out);
    }
    }


    /*
        test setClob
        need to run prepareCLOBMAIN first
    */
  private static void clobTest7(Connection conn)
    {
    ResultSet rs, rs2;
    Statement stmt1, stmt2;
    System.out.println(START + "clobTest7");
        try
        {
      stmt1 = conn.createStatement();
      stmt1.execute("create table testClob7 (a CLOB(300k), b integer)");
            PreparedStatement ps = conn.prepareStatement(
                "insert into testClob7 values(?,?)");
      stmt2 = conn.createStatement();
      rs = stmt2.executeQuery("select a,b from testCLOB_MAIN");
            Clob clob;
            int clobLength;
      int rownum = 0;
      while (rs.next())
            {
        // get the first column as a clob
                clob = rs.getClob(1);
                if (clob == null)
                    continue;
                clobLength = rs.getInt(2);
                ps.setClob(1,clob);
                ps.setInt(2,clobLength);
                ps.executeUpdate();
      }
            rs.close();
            conn.commit();

      rs2 = stmt2.executeQuery("select a,b from testClob7");
            Clob clob2;
            int clobLength2, j = 0;
      while (rs2.next())
            {
                j++;
        // get the first column as a clob
                clob2 = rs2.getClob(1);
                if (clob2 == null)
                    continue;
                clobLength2 = rs2.getInt(2);
                if (clob2.length() != clobLength2)
                    System.out.println("FAILED at row " + j);
      }
            rs2.close();

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

  /**
    Agressive test of position.
  */
    private static void clobTest8(Connection conn)
    {
      System.out.println(START + "clobTest8");
    try {
      Statement s = conn.createStatement();

      s.execute("CREATE TABLE C8.T8POS(id INT NOT NULL PRIMARY KEY, DD CLOB(1m), pos INT, L INT)");
      s.execute("CREATE TABLE C8.T8PATT(PATT CLOB(300k))");

      // characters used to fill the String
      char[] fill = new char[4];
      fill[0] = 'd';    // 1 byte UTF8 character (ASCII)
      fill[1] = '\u03a9'// 2 byte UTF8 character (Greek)
      fill[2] = '\u0e14'; // 3 byte UTF8 character (Thai)
      fill[3] = 'j';    // 1 byte UTF8 character (ASCII)

      char[] base = new char[256 * 1024];

      for (int i = 0; i < base.length; i += 4) {

        base[i] = fill[0];
        base[i+1] = fill[1];
        base[i+2] = fill[2];
        base[i+3] = fill[3];

      }

      char[]  patternBase = new char[2 * 1024];
      for (int i = 0; i < patternBase.length; i += 8) {

        patternBase[i] = 'p';
        patternBase[i+1] = 'a';
        patternBase[i+2] = 't';
        patternBase[i+3] = '\u03aa';
        patternBase[i+4] = (char) i;   // changed value to keep pattern varyinh
        patternBase[i+5] = 'b';
        patternBase[i+6] = 'm';
        patternBase[i+7] = '\u0e15';

      }

      PreparedStatement ps = conn.prepareStatement("INSERT INTO C8.T8POS VALUES (?, ?, ?, ?)");
      PreparedStatement psp = conn.prepareStatement("INSERT INTO C8.T8PATT VALUES (?)");

      T8insert(ps, 1, base, 256, patternBase, 8, 100, true);
      T8insert(ps, 2, base, 3988, patternBase, 8, 2045, true);
      T8insert(ps, 3, base, 16321, patternBase, 8, 4566, true);
      T8insert(ps, 4, base, 45662, patternBase, 8, 34555, true);
      T8insert(ps, 5, base, 134752, patternBase, 8, 67889, true);
      T8insert(ps, 6, base, 303, patternBase, 8, 80, false);
      T8insert(ps, 7, base, 4566, patternBase, 8, 2086, false);
      T8insert(ps, 8, base, 17882, patternBase, 8, 4426, false);
      T8insert(ps, 9, base, 41567, patternBase, 8, 31455, false);
      String pstr = T8insert(ps, 10, base, 114732, patternBase, 8, 87809, false);     

      conn.commit();

      psp.setString(1, pstr);
      psp.executeUpdate();

      System.out.println("small string pattern");
      checkClob8(s, pstr);
      conn.commit();

      System.out.println("small java.sql.Clob pattern");
      ResultSet rsc = s.executeQuery("SELECT PATT FROM C8.T8PATT");
      rsc.next();
      checkClob8(s, rsc.getClob(1));

      rsc.close();


      conn.commit();

      s.execute("DELETE FROM C8.T8POS");
      s.execute("DELETE FROM C8.T8PATT");


      T8insert(ps, 1, base, 256, patternBase, 134, 100, true);
      T8insert(ps, 2, base, 3988, patternBase, 134, 2045, true);
      T8insert(ps, 3, base, 16321, patternBase, 134, 4566, true);
      T8insert(ps, 4, base, 45662, patternBase, 134, 34555, true);
      T8insert(ps, 5, base, 134752, patternBase, 134, 67889, true);
      T8insert(ps, 6, base, 303, patternBase, 134, 80, false);
      T8insert(ps, 7, base, 4566, patternBase, 134, 2086, false);
      T8insert(ps, 8, base, 17882, patternBase, 134, 4426, false);
      T8insert(ps, 9, base, 41567, patternBase, 134, 31455, false);
      pstr = T8insert(ps, 10, base, 114732, patternBase, 134, 87809, false);

      conn.commit();
      psp.setString(1, pstr);
      psp.executeUpdate();
      conn.commit();


      System.out.println("medium string pattern");
      checkClob8(s, pstr);
      conn.commit();

      System.out.println("medium java.sql.Clob pattern");
      rsc = s.executeQuery("SELECT PATT FROM C8.T8PATT");
      rsc.next();
      checkClob8(s, rsc.getClob(1));

      s.execute("DELETE FROM C8.T8POS");
      s.execute("DELETE FROM C8.T8PATT");

      T8insert(ps, 1, base, 256, patternBase, 679, 100, true);
      T8insert(ps, 2, base, 3988, patternBase, 679, 2045, true);
      T8insert(ps, 3, base, 16321, patternBase, 679, 4566, true);
      T8insert(ps, 4, base, 45662, patternBase, 679, 34555, true);
      T8insert(ps, 5, base, 134752, patternBase, 679, 67889, true);
      T8insert(ps, 6, base, 303, patternBase, 679, 80, false);
      T8insert(ps, 7, base, 4566, patternBase, 679, 2086, false);
      T8insert(ps, 8, base, 17882, patternBase, 679, 4426, false);
      T8insert(ps, 9, base, 41567, patternBase, 679, 31455, false);
      pstr = T8insert(ps, 10, base, 114732, patternBase, 679, 87809, false);

      conn.commit();
      psp.setString(1, pstr);
      psp.executeUpdate();
      conn.commit();


      System.out.println("long string pattern");
      checkClob8(s, pstr);
      conn.commit();

      System.out.println("long java.sql.Clob pattern");
      rsc = s.executeQuery("SELECT PATT FROM C8.T8PATT");
      rsc.next();
      checkClob8(s, rsc.getClob(1));

      s.execute("DELETE FROM C8.T8POS");
      s.execute("DELETE FROM C8.T8PATT");
      ps.close();
      psp.close();
      //

      s.execute("DROP TABLE C8.T8POS");
      s.execute("DROP TABLE C8.T8PATT");

      s.close();

      conn.commit();

      System.out.println("complete clobTest8");


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

  private static void checkClob8(Statement s, String pstr) throws SQLException {
   
    ResultSet rs = s.executeQuery("SELECT ID, DD, POS, L FROM C8.T8POS ORDER BY 1");

    while (rs.next()) {

      int id = rs.getInt(1);

      System.out.print("@" + id + " ");

      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 void checkClob8(Statement s, Clob pstr) throws SQLException {
    ResultSet rs = s.executeQuery("SELECT ID, DD, POS, L FROM C8.T8POS ORDER BY 1");

    while (rs.next()) {

      int id = rs.getInt(1);

      System.out.print("@" + id + " ");

      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(System.out);
    }
    }


    /*
        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(System.out);
    }
    }


    /*
        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(System.out);
    }
    }

   
    /*
        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(System.out);
    }
    }


    /*
        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.dumpSQLExceptions(e);
        }
            try
            {
                clob.position("foo",2);
            }
            catch (SQLException e)
            {
        if (isDerbyNet)
          System.out.println("EXPECTED SQL Exception: " + e.getMessage());
        else
          TestUtil.dumpSQLExceptions(e);
        }
            try
            {
                clob.position(clob,2);
            }
            catch (SQLException e)
            {
       
        if (isDerbyNet)
          System.out.println("EXPECTED SQL Exception: " + e.getMessage());
        else
          TestUtil.dumpSQLExceptions(e);
        }

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


    /*
        test clob finalizer closes the container
        (should only release table and row locks that are read_committed)
        need to run prepareCLOBMAIN first
        NOTE: this test does not produce output since it needs to call the
        garbage collector whose behaviour is unreliable. It is in the test run to
        exercise the code (most of the time).
    */
  private static void clobTest96(Connection conn)
    {
    ResultSet rs;
    Statement stmt;
    System.out.println(START + "clobTest96");
    try {
      stmt = conn.createStatement();
      rs = stmt.executeQuery("select a,b from testCLOB_MAIN");
      byte[]