Examples with LoopingAlphabetReader org.apache.derbyTesting.functionTests.util.streams.LoopingAlphabetReader used on opensource projects

Example 56 with LoopingAlphabetReader

use of org.apache.derbyTesting.functionTests.util.streams.LoopingAlphabetReader in project derby by apache.

the class TriggerTest method testClobInTriggerTable.

/**
 * Create a table with after update trigger on non-lob column.
 * Insert clob of size clobSize into table and perform update
 * on str1 column to fire trigger. Helper method called from
 * testClobInTriggerTable
 * @param clobSize size of clob to test
 * @throws SQLException
 * @throws IOException
 */
private void testClobInTriggerTable(int clobSize) throws SQLException, IOException {
    // Alphabet used when inserting a CLOB.
    CharAlphabet a1 = CharAlphabet.singleChar('a');
    // Alphabet used when updating a CLOB.
    CharAlphabet a2 = CharAlphabet.singleChar('b');
    // --- add a clob
    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)";
    Statement s = createStatement();
    s.executeUpdate("create table LOB1 (str1 Varchar(80), c_lob CLOB(50M))");
    s.executeUpdate("create table t_lob1_log(oldvalue varchar(80), newvalue varchar(80), chng_time timestamp default current_timestamp)");
    s.executeUpdate(trig);
    commit();
    PreparedStatement ps = prepareStatement("INSERT INTO LOB1 VALUES (?, ?)");
    ps.setString(1, clobSize + "");
    // - set the value of the input parameter to the input stream
    ps.setCharacterStream(2, new LoopingAlphabetReader(clobSize, a1), clobSize);
    ps.execute();
    closeStatement(ps);
    commit();
    // Now executing update to fire trigger
    s.executeUpdate("update LOB1 set str1 = str1 || ' '");
    s.executeUpdate("drop table lob1");
    s.executeUpdate("drop table t_lob1_log");
    // now referencing the lob column
    trig = " create trigger t_lob1 after update of c_lob 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.c_lob, new.c_lob)";
    s.executeUpdate("create table LOB1 (str1 Varchar(80), c_lob CLOB(50M))");
    s.executeUpdate("create table t_lob1_log(oldvalue CLOB(50M), newvalue  CLOB(50M), chng_time timestamp default current_timestamp)");
    s.executeUpdate(trig);
    commit();
    ps = prepareStatement("INSERT INTO LOB1 VALUES (?, ?)");
    ps.setString(1, clobSize + "");
    // - set the value of the input parameter to the input stream
    ps.setCharacterStream(2, new LoopingAlphabetReader(clobSize, a1), clobSize);
    ps.execute();
    closeStatement(ps);
    commit();
    // Now executing update to fire trigger
    ps = prepareStatement("update LOB1 set c_lob = ?");
    ps.setCharacterStream(1, new LoopingAlphabetReader(clobSize, a2), clobSize);
    ps.execute();
    closeStatement(ps);
    commit();
    s.executeUpdate("drop table lob1");
    s.executeUpdate("drop table t_lob1_log");
    // now referencing the lob column twice
    trig = " create trigger t_lob1 after update of c_lob 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, oldvalue_again, newvalue_again) values (old.c_lob, new.c_lob, old.c_lob, new.c_lob)";
    s.executeUpdate("create table LOB1 (str1 Varchar(80), c_lob CLOB(50M))");
    s.executeUpdate("create table t_lob1_log(oldvalue CLOB(50M), newvalue  CLOB(50M), oldvalue_again CLOB(50M), newvalue_again CLOB(50M), chng_time timestamp default current_timestamp)");
    s.executeUpdate(trig);
    commit();
    ps = prepareStatement("INSERT INTO LOB1 VALUES (?, ?)");
    ps.setString(1, clobSize + "");
    // - set the value of the input parameter to the input stream
    ps.setCharacterStream(2, new LoopingAlphabetReader(clobSize, a1), clobSize);
    ps.execute();
    closeStatement(ps);
    commit();
    // Now executing update to fire trigger
    ps = prepareStatement("update LOB1 set c_lob = ?");
    ps.setCharacterStream(1, new LoopingAlphabetReader(clobSize, a2), clobSize);
    ps.execute();
    closeStatement(ps);
    commit();
    // check log table.
    ResultSet rs = s.executeQuery("SELECT * from t_lob1_log");
    rs.next();
    assertEquals(new LoopingAlphabetReader(clobSize, a1), rs.getCharacterStream(1));
    assertEquals(new LoopingAlphabetReader(clobSize, a2), rs.getCharacterStream(2));
    assertEquals(new LoopingAlphabetReader(clobSize, a1), rs.getCharacterStream(3));
    assertEquals(new LoopingAlphabetReader(clobSize, a2), rs.getCharacterStream(4));
    rs.close();
    s.executeUpdate("drop table lob1");
    s.executeUpdate("drop table t_lob1_log");
}

Example 57 with LoopingAlphabetReader

use of org.apache.derbyTesting.functionTests.util.streams.LoopingAlphabetReader in project derby by apache.

the class Datatypes method update_one_row.

public static synchronized void update_one_row(Connection conn, int thread_id) throws Exception {
    PreparedStatement ps2 = null;
    Statement stmt = conn.createStatement();
    ResultSet rs;
    String column = null;
    int ind = 0;
    long max = 0;
    long min = 0;
    double x;
    long id_to_update;
    InputStream streamIn = null;
    Reader streamReader = null;
    rs = stmt.executeQuery("select max(serialkey) from Datatypes");
    while (rs.next()) max = rs.getLong(1);
    rs = stmt.executeQuery("select min(serialkey) from Datatypes");
    while (rs.next()) min = rs.getLong(1);
    id_to_update = (min + 1) + (Math.abs(Rn.nextLong()) % (max - min));
    if (id_to_update == 0)
        id_to_update = 1;
    ind = Math.abs(Rn.nextInt());
    column = colnames[ind % NUMTYPES];
    try {
        conn.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ);
        ps2 = conn.prepareStatement(" update Datatypes set " + column + " = ? " + " where serialkey = " + id_to_update);
    } catch (SQLException se) {
        if (se.getNextException() == null)
            throw se;
        String m = se.getNextException().getSQLState();
        return;
    }
    String ds = null;
    String ds2 = null;
    String cs = "asdf qwerqwer 12341234 ZXCVZXCVZXCV !@#$!@#$ asdfasdf 1 q a z asdf ASDF qwerasdfzxcvasdfqwer1234asd#";
    double d = 0.0;
    float f = 0;
    BigDecimal bdec = null;
    int type = (ind % NUMTYPES);
    switch(type) {
        case TCHAR:
            ds2 = cs.substring(Math.abs(Rn.nextInt() % 100));
            ps2.setString(1, ds2);
            break;
        case TDATE:
            Date dt = new Date(1);
            dt.setTime(Math.abs(Rn.nextLong() / 150000));
            dt.setTime(Math.abs(Rn.nextLong() / 150000));
            ps2.setDate(1, dt);
            ds2 = dt.toString();
            break;
        case TDECIMAL:
            x = Math.abs(Rn.nextInt() % 18);
            if (x > 5)
                x = 5;
            d = Rn.nextDouble() * Math.pow(10, x);
            bdec = new BigDecimal(d);
            ps2.setBigDecimal(1, bdec);
            ds2 = String.valueOf(d);
            break;
        case TDECIMALNN:
            ds = String.valueOf(d);
            d = Rn.nextDouble();
            bdec = new BigDecimal(d);
            ps2.setBigDecimal(1, bdec);
            ds2 = String.valueOf(d);
            break;
        case TDOUBLE:
            d = Rn.nextDouble() * Math.pow(10, Rn.nextInt() % 300);
            ps2.setDouble(1, d);
            ds2 = String.valueOf(d);
            break;
        case TFLOAT:
            ds = String.valueOf(f);
            f = Rn.nextFloat() * (float) Math.pow(10, Rn.nextInt() % 30);
            ps2.setFloat(1, f);
            ds2 = String.valueOf(f);
            break;
        case TINT:
            int i = Rn.nextInt();
            ds2 = String.valueOf(i);
            ps2.setInt(1, i);
            break;
        case TLONGINT:
            long l = Rn.nextLong();
            ds2 = String.valueOf(l);
            ps2.setLong(1, l);
            break;
        case TNUMERICLARGE:
            ds = String.valueOf(d);
            x = Math.abs(Rn.nextInt() % 30);
            if (x > 30)
                x = 31;
            d = Rn.nextDouble() * Math.pow(10, x);
            bdec = new BigDecimal(d);
            ps2.setBigDecimal(1, bdec);
            ds2 = String.valueOf(d);
            break;
        case TREAL:
            ds = String.valueOf(f);
            f = Rn.nextFloat() * (float) Math.pow(10, Rn.nextInt() % 7);
            ps2.setFloat(1, f);
            ds2 = String.valueOf(f);
            break;
        case TSMALLINT:
            i = Rn.nextInt() % (256 * 128);
            ds = String.valueOf(i);
            short si = (short) i;
            ps2.setShort(1, si);
            ds2 = String.valueOf(si);
            break;
        case TTIME:
            Time tt = new Time(1);
            tt.setTime(Math.abs(Rn.nextInt()));
            ps2.setTime(1, tt);
            ds2 = tt.toString();
            break;
        case TTIMESTAMP:
            Timestamp ts = new Timestamp(1);
            ts.setTime(Math.abs(Rn.nextLong() / 50000));
            ps2.setTimestamp(1, ts);
            ds2 = ts.toString();
            break;
        case TVARCHAR:
            ds2 = cs.substring(Math.abs(Rn.nextInt() % 100));
            ps2.setString(1, ds2);
            break;
        case TBLOB:
            // to create a stream of random length between 0 and 100K
            int blobLength = Rn.nextInt(102400 - 0 + 1) + 0;
            streamIn = new LoopingAlphabetStream(blobLength);
            ps2.setBinaryStream(1, streamIn, blobLength);
            break;
        case TCLOB:
            // to create a stream of random length between 0 and 100K
            int clobLength = Rn.nextInt(102400 - 0 + 1) + 0;
            streamReader = new LoopingAlphabetReader(clobLength, CharAlphabet.modernLatinLowercase());
            ps2.setCharacterStream(1, streamReader, clobLength);
            break;
    }
    int rows = 0;
    boolean cleanuponly = false;
    if (cleanuponly == false) {
        try {
            rows = ps2.executeUpdate();
        } catch (SQLException se) {
            if (se.getNextException() == null)
                throw se;
            String m = se.getNextException().getSQLState();
            System.out.println(se.getNextException().getMessage() + " SQLSTATE: " + m);
        }
        if (rows < 0)
            System.out.println("t" + thread_id + " update failed.");
    }
    if (streamReader != null)
        streamReader.close();
    if (streamIn != null)
        streamIn.close();
    if (ps2 != null)
        try {
            ps2.close();
            rs.close();
        } catch (SQLException se) {
            if (se.getNextException() == null)
                throw se;
            String m = se.getNextException().getSQLState();
            System.out.println(se.getNextException().getMessage() + " SQLSTATE: " + m);
        }
}