Java Code Examples for java.sql.Connection#createArrayOf()

@Test
public void testArrayIndexUsedInGroupByClause() throws Exception {
    String tenantId = getOrganizationId();
    String table = createTableWithArray(getUrl(),
            getDefaultSplits(tenantId), null);
    initTablesWithArrays(table, tenantId, null, false, getUrl());
    String query = "SELECT a_double_array[2] FROM " + table + "  GROUP BY a_double_array[2]";
    Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
    Connection conn = DriverManager.getConnection(getUrl(), props);
    try {
        PreparedStatement statement = conn.prepareStatement(query);
        Double[] doubleArr = new Double[1];
        doubleArr[0] = 40.0;
        conn.createArrayOf("DOUBLE", doubleArr);
        ResultSet rs = statement.executeQuery();
        assertTrue(rs.next());
        doubleArr = new Double[1];
        doubleArr[0] = 36.763;
        Double result =  rs.getDouble(1);
        assertEquals(doubleArr[0], result);
        assertFalse(rs.next());
    } finally {
        conn.close();
    }
}
 

@Test
public void testArrayFillFunctionWithNestedFunctions1() throws Exception {
    Connection conn = DriverManager.getConnection(getUrl());
    

    ResultSet rs;
    rs = conn.createStatement().executeQuery(
        "SELECT ARRAY_FILL(ARRAY_ELEM(ARRAY[23,45],1),3) FROM " + tableName
            + " WHERE region_name = 'SF Bay Area'");
    assertTrue(rs.next());

    Integer[] integers = new Integer[]{23, 23, 23};

    Array array = conn.createArrayOf("INTEGER", integers);

    assertEquals(array, rs.getArray(1));
    assertFalse(rs.next());
}
 

@Test
public void testArrayFillFunctionWithNestedFunctions3() throws Exception {
    Connection conn = DriverManager.getConnection(getUrl());
    

    ResultSet rs;
    rs = conn.createStatement().executeQuery(
        "SELECT ARRAY_FILL(3.4, ARRAY_LENGTH(ARRAY[34, 45])) FROM " + tableName
            + " WHERE region_name = 'SF Bay Area'");
    assertTrue(rs.next());

    Object[] objects = new Object[]{BigDecimal.valueOf(3.4), BigDecimal.valueOf(3.4)};

    Array array = conn.createArrayOf("DECIMAL", objects);

    assertEquals(array, rs.getArray(1));
    assertFalse(rs.next());
}
 

@Test
public void testArrayAppendFunctionWithUpsert3() throws Exception {
    Connection conn = DriverManager.getConnection(getUrl());

    String tableName = generateUniqueName();
    String ddl = "CREATE TABLE " + tableName + " (region_name VARCHAR PRIMARY KEY,doubles DOUBLE[])";
    conn.createStatement().execute(ddl);

    String dml = "UPSERT INTO " + tableName + "(region_name,doubles) VALUES('SF Bay Area',ARRAY_APPEND(ARRAY[5.67,7.87],9.0))";
    conn.createStatement().execute(dml);
    conn.commit();

    ResultSet rs;
    rs = conn.createStatement().executeQuery("SELECT doubles FROM " + tableName + " WHERE region_name = 'SF Bay Area'");
    assertTrue(rs.next());

    Double[] doubles = new Double[]{5.67, 7.87, new Double(9)};

    Array array = conn.createArrayOf("DOUBLE", doubles);

    assertEquals(array, rs.getArray(1));
    assertFalse(rs.next());
}
 

@Test(expected = IllegalArgumentException.class)
public void testArrayFillFunctionInvalidLength2() throws Exception {
    Connection conn = DriverManager.getConnection(getUrl());
    

    ResultSet rs;
    rs = conn.createStatement().executeQuery(
        "SELECT ARRAY_FILL(\"timestamp\",length1) FROM " + tableName
            + " WHERE region_name = 'SF Bay Area'");
    assertTrue(rs.next());

    Object[] objects = new Object[]{new Timestamp(1432102334184l), new Timestamp(1432102334184l), new Timestamp(1432102334184l)};

    Array array = conn.createArrayOf("TIMESTAMP", objects);
    assertEquals(array, rs.getArray(1));
    assertFalse(rs.next());
}
 

@Test
public void testArrayAppendFunctionWithUpsert2() throws Exception {
    Connection conn = DriverManager.getConnection(getUrl());

    String tableName = generateUniqueName();
    String ddl = "CREATE TABLE " + tableName + " (region_name VARCHAR PRIMARY KEY,integers INTEGER[])";
    conn.createStatement().execute(ddl);

    String dml = "UPSERT INTO " + tableName + "(region_name,integers) VALUES('SF Bay Area',ARRAY_APPEND(ARRAY[4,5],6))";
    conn.createStatement().execute(dml);
    conn.commit();

    ResultSet rs;
    rs = conn.createStatement().executeQuery("SELECT integers FROM " + tableName + " WHERE region_name = 'SF Bay Area'");
    assertTrue(rs.next());

    Integer[] integers = new Integer[]{4, 5, 6};

    Array array = conn.createArrayOf("INTEGER", integers);

    assertEquals(array, rs.getArray(1));
    assertFalse(rs.next());
}
 

@Test
public void testArrayAppendFunctionDoublesWithNull() throws Exception {
    Connection conn = DriverManager.getConnection(getUrl());
    String tableName = initTables(conn);

    ResultSet rs;
    rs = conn.createStatement().executeQuery("SELECT ARRAY_APPEND(doubles,NULL) FROM " + tableName + " WHERE region_name = 'SF Bay Area'");
    assertTrue(rs.next());

    Double[] doubles = new Double[]{23.45, 46.345, 23.234, 45.6, 5.78};

    Array array = conn.createArrayOf("DOUBLE", doubles);

    assertEquals(array, rs.getArray(1));
    assertFalse(rs.next());
}
 

@Test
public void testArrayFillFunctionWithNestedFunctions2() throws Exception {
    Connection conn = DriverManager.getConnection(getUrl());
    

    ResultSet rs;
    rs = conn.createStatement().executeQuery(
        "SELECT ARRAY_FILL('hello', ARRAY_LENGTH(ARRAY[34, 45])) FROM " + tableName
            + " WHERE region_name = 'SF Bay Area'");
    assertTrue(rs.next());

    Object[] objects = new Object[]{"hello", "hello"};

    Array array = conn.createArrayOf("VARCHAR", objects);

    assertEquals(array, rs.getArray(1));
    assertFalse(rs.next());
}
 

@Test
public void testArrayConcatFunctionWithNulls2() throws Exception {
    Connection conn = DriverManager.getConnection(getUrl());
    String tableName = initTables(conn);

    ResultSet rs;
    PreparedStatement st = conn.prepareStatement("SELECT ARRAY_CAT(?,?) FROM " + tableName + " WHERE region_name = 'SF Bay Area'");
    Array array1 = conn.createArrayOf("VARCHAR", new Object[]{"a", "b", "c"});
    st.setArray(1, array1);
    Array array2 = conn.createArrayOf("VARCHAR", new Object[]{null, "a", "b", "c"});
    st.setArray(2, array2);
    rs = st.executeQuery();
    assertTrue(rs.next());

    Array expected = conn.createArrayOf("VARCHAR", new Object[]{"a", "b", "c", null, "a", "b", "c"});

    assertEquals(expected, rs.getArray(1));
    assertFalse(rs.next());
}
 

@Test
public void testArrayConcatFunctionWithNestedFunctions2() throws Exception {
    Connection conn = DriverManager.getConnection(getUrl());
    String tableName = initTables(conn);

    ResultSet rs;
    rs = conn.createStatement().executeQuery("SELECT ARRAY_CAT(integers,ARRAY[ARRAY_ELEM(ARRAY[2,4],1),ARRAY_ELEM(ARRAY[2,4],2)]) FROM " + tableName + " WHERE region_name = 'SF Bay Area'");
    assertTrue(rs.next());

    Integer[] integers = new Integer[]{2345, 46345, 23234, 456, 2, 4};

    Array array = conn.createArrayOf("INTEGER", integers);

    assertEquals(array, rs.getArray(1));
    assertFalse(rs.next());
}
 

@Test
public void testArrayConcatFunctionWithNulls4() throws Exception {
    Connection conn = DriverManager.getConnection(getUrl());
    String tableName = initTables(conn);

    ResultSet rs;
    PreparedStatement st = conn.prepareStatement("SELECT ARRAY_CAT(?,?) FROM " + tableName + " WHERE region_name = 'SF Bay Area'");
    Array array1 = conn.createArrayOf("VARCHAR", new Object[]{null, "a", null, "b", "c", null, null});
    st.setArray(1, array1);
    Array array2 = conn.createArrayOf("VARCHAR", new Object[]{null, null, "a", null, "b", null, "c", null});
    st.setArray(2, array2);
    rs = st.executeQuery();
    assertTrue(rs.next());

    Array expected = conn.createArrayOf("VARCHAR", new Object[]{null, "a", null, "b", "c", null, null, null, null, "a", null, "b", null, "c", null});

    assertEquals(expected, rs.getArray(1));
    assertFalse(rs.next());
}
 

@Test
public void testArrayAppendFunctionWithNestedFunctions2() throws Exception {
    Connection conn = DriverManager.getConnection(getUrl());
    String tableName = initTables(conn);

    ResultSet rs;
    rs = conn.createStatement().executeQuery("SELECT ARRAY_APPEND(integers,ARRAY_ELEM(ARRAY[2,4],1)) FROM " + tableName + " WHERE region_name = 'SF Bay Area'");
    assertTrue(rs.next());

    Integer[] integers = new Integer[]{2345, 46345, 23234, 456, 2};

    Array array = conn.createArrayOf("INTEGER", integers);

    assertEquals(array, rs.getArray(1));
    assertFalse(rs.next());
}
 

@Test
public void testArraySelectSingleArrayElemWithCast() throws Exception {
    Connection conn;
    PreparedStatement stmt;
    ResultSet rs;
    long ts = nextTimestamp();
    Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
    props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 10));
    conn = DriverManager.getConnection(getUrl(), props);
    conn.createStatement().execute("CREATE TABLE t ( k VARCHAR PRIMARY KEY, a bigint ARRAY[])");
    conn.close();

    props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 30));
    conn = DriverManager.getConnection(getUrl(), props);
    stmt = conn.prepareStatement("UPSERT INTO t VALUES(?,?)");
    stmt.setString(1, "a");
    Long[] s = new Long[] {1l, 2l};
    Array array = conn.createArrayOf("BIGINT", s);
    stmt.setArray(2, array);
    stmt.execute();
    conn.commit();
    conn.close();
    props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 40));
    conn = DriverManager.getConnection(getUrl(), props);
    rs = conn.createStatement().executeQuery("SELECT k, CAST(a[2] AS DOUBLE) FROM t");
    assertTrue(rs.next());
    assertEquals("a",rs.getString(1));
    Double d = new Double(2.0);
    assertEquals(d, (Double)rs.getDouble(2));
    conn.close();
}
 

@Test
public void testUpdateStatsForTheTable() throws Throwable {
    Connection conn;
    PreparedStatement stmt;
    ResultSet rs;
    Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
    // props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 10));
    conn = DriverManager.getConnection(getUrl(), props);
    conn.createStatement().execute(
            "CREATE TABLE t ( k VARCHAR, a_string_array VARCHAR(100) ARRAY[4], b_string_array VARCHAR(100) ARRAY[4] \n"
                    + " CONSTRAINT pk PRIMARY KEY (k, b_string_array DESC)) \n");
    String[] s;
    Array array;
    conn = upsertValues(props, "t");
    // CAll the update statistics query here. If already major compaction has run this will not get executed.
    stmt = conn.prepareStatement("UPDATE STATISTICS T");
    stmt.execute();
    stmt = upsertStmt(conn, "t");
    stmt.setString(1, "z");
    s = new String[] { "xyz", "def", "ghi", "jkll", null, null, "xxx" };
    array = conn.createArrayOf("VARCHAR", s);
    stmt.setArray(2, array);
    s = new String[] { "zya", "def", "ghi", "jkll", null, null, null, "xxx" };
    array = conn.createArrayOf("VARCHAR", s);
    stmt.setArray(3, array);
    stmt.execute();
    conn.close();
    conn = DriverManager.getConnection(getUrl(), props);
    // This analyze would not work
    stmt = conn.prepareStatement("UPDATE STATISTICS T");
    stmt.execute();
    rs = conn.createStatement().executeQuery("SELECT k FROM T");
    assertTrue(rs.next());
    conn.close();
}
 

@Test
public void testUpsertValuesWithNull() throws Exception {

    String tenantId = getOrganizationId();
    String table = createTableWithArray(getUrl(), getDefaultSplits(tenantId), null);
    String query = "upsert into  " + table + " (ORGANIZATION_ID,ENTITY_ID,a_double_array) values('" + tenantId
            + "','00A123122312312',null)";
    Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
                                                                             // at
    Connection conn = DriverManager.getConnection(getUrl(), props);
    try {
        PreparedStatement statement = conn.prepareStatement(query);
        int executeUpdate = statement.executeUpdate();
        assertEquals(1, executeUpdate);
        conn.commit();
        statement.close();
        conn.close();
        // create another connection
        props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
        conn = DriverManager.getConnection(getUrl(), props);
        query = "SELECT ARRAY_ELEM(a_double_array,2) FROM  " + table;
        statement = conn.prepareStatement(query);
        ResultSet rs = statement.executeQuery();
        assertTrue(rs.next());
        // Need to support primitive
        Double[] doubleArr = new Double[1];
        doubleArr[0] = 0.0d;
        conn.createArrayOf("DOUBLE", doubleArr);
        Double result = rs.getDouble(1);
        assertEquals(doubleArr[0], result);
        assertFalse(rs.next());
    } finally {
        conn.close();
    }
}
 

@Test
public void testVarLengthArrComparisonInWhereClauseWithDiffSizeArrays() throws Exception {
    Connection conn;
    PreparedStatement stmt;
    ResultSet rs;


    Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
    conn = DriverManager.getConnection(getUrl(), props);
    String table = generateUniqueName();
    conn.createStatement()
            .execute(
                    "CREATE TABLE  " + table + "  ( k VARCHAR PRIMARY KEY, a_string_array VARCHAR(100) ARRAY[4], b_string_array VARCHAR(100) ARRAY[4])");
    conn.close();
    conn = DriverManager.getConnection(getUrl(), props);
    stmt = conn.prepareStatement("UPSERT INTO  " + table + "  VALUES(?,?,?)");
    stmt.setString(1, "a");
    String[] s = new String[] { "abc", "def", "ghi", "jkll" };
    Array array = conn.createArrayOf("VARCHAR", s);
    stmt.setArray(2, array);
    s = new String[] { "abc", "def", "ghi", "jklm" };
    array = conn.createArrayOf("VARCHAR", s);
    stmt.setArray(3, array);
    stmt.execute();
    conn.commit();
    conn.close();

    conn = DriverManager.getConnection(getUrl(), props);
    rs = conn.createStatement().executeQuery(
            "SELECT k, a_string_array[2] FROM  " + table + "  where a_string_array<b_string_array");
    assertTrue(rs.next());
    assertEquals("a", rs.getString(1));
    assertEquals("def", rs.getString(2));
    conn.close();
}
 

@Test
public void testArrayConstructorWithMultipleRows2() throws Exception {

    Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
    Connection conn = DriverManager.getConnection(getUrl(), props);
    String table = generateUniqueName();
    String ddl = "CREATE TABLE  " + table + "  (region_name VARCHAR PRIMARY KEY, a INTEGER, b INTEGER)";
    conn.createStatement().execute(ddl);
    conn.commit();
    conn.close();
    conn = DriverManager.getConnection(getUrl(), props);
    PreparedStatement stmt = conn.prepareStatement("UPSERT INTO  " + table + " (region_name, a, b) VALUES('a', 6,3)");
    stmt.execute();
    stmt = conn.prepareStatement("UPSERT INTO  " + table + " (region_name, a, b) VALUES('b', 2,4)");
    stmt.execute();
    stmt = conn.prepareStatement("UPSERT INTO  " + table + " (region_name, a, b) VALUES('c', 6,3)");
    stmt.execute();
    conn.commit();
    conn.close();
    conn = DriverManager.getConnection(getUrl(), props);
    ResultSet rs;
    rs = conn.createStatement().executeQuery("SELECT ARRAY[a,b] from  " + table + " ");
    assertTrue(rs.next());
    Array arr = conn.createArrayOf("INTEGER", new Object[]{6, 3});
    assertEquals(arr, rs.getArray(1));
    rs.next();
    arr = conn.createArrayOf("INTEGER", new Object[]{2, 4});
    assertEquals(arr, rs.getArray(1));
    rs.next();
    arr = conn.createArrayOf("INTEGER", new Object[]{6, 3});
    assertEquals(arr, rs.getArray(1));
    rs.next();
}
 

@Test
public void testArrayWithCast() throws Exception {
    Connection conn;
    PreparedStatement stmt;
    ResultSet rs;
    long ts = nextTimestamp();
    Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
    props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 10));
    conn = DriverManager.getConnection(getUrl(), props);
    conn.createStatement().execute("CREATE TABLE t ( k VARCHAR PRIMARY KEY, a bigint ARRAY[])");
    conn.close();

    props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 30));
    conn = DriverManager.getConnection(getUrl(), props);
    stmt = conn.prepareStatement("UPSERT INTO t VALUES(?,?)");
    stmt.setString(1, "a");
    Long[] s = new Long[] { 1l, 2l };
    Array array = conn.createArrayOf("BIGINT", s);
    stmt.setArray(2, array);
    stmt.execute();
    conn.commit();
    conn.close();
    props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 40));
    conn = DriverManager.getConnection(getUrl(), props);
    rs = conn.createStatement().executeQuery("SELECT CAST(a AS DOUBLE []) FROM t");
    assertTrue(rs.next());
    Double[] d = new Double[] { 1.0, 2.0 };
    array = conn.createArrayOf("DOUBLE", d);
    PhoenixArray arr = (PhoenixArray)rs.getArray(1);
    assertEquals(array, arr);
    conn.close();
}
 

@Test
public void testArraySelectGetString() throws Exception {
    Connection conn;
    PreparedStatement stmt;

    String tenantId = getOrganizationId();

    // create the table
    String tableName = createTableWithAllArrayTypes(getUrl(), getDefaultSplits(tenantId), null);

    // populate the table with data
    Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
    conn = DriverManager.getConnection(getUrl(), props);
    stmt =
            conn.prepareStatement("UPSERT INTO "
                    + tableName
                    + "(ORGANIZATION_ID, ENTITY_ID, BOOLEAN_ARRAY, BYTE_ARRAY, DOUBLE_ARRAY, FLOAT_ARRAY, INT_ARRAY, LONG_ARRAY, SHORT_ARRAY, STRING_ARRAY)\n"
                    + "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
    stmt.setString(1, tenantId);
    stmt.setString(2, ROW1);
    // boolean array
    Array boolArray = conn.createArrayOf("BOOLEAN", new Boolean[] { true, false });
    int boolIndex = 3;
    stmt.setArray(boolIndex, boolArray);
    // byte array
    Array byteArray = conn.createArrayOf("TINYINT", new Byte[] { 11, 22 });
    int byteIndex = 4;
    stmt.setArray(byteIndex, byteArray);
    // double array
    Array doubleArray = conn.createArrayOf("DOUBLE", new Double[] { 67.78, 78.89 });
    int doubleIndex = 5;
    stmt.setArray(doubleIndex, doubleArray);
    // float array
    Array floatArray = conn.createArrayOf("FLOAT", new Float[] { 12.23f, 45.56f });
    int floatIndex = 6;
    stmt.setArray(floatIndex, floatArray);
    // int array
    Array intArray = conn.createArrayOf("INTEGER", new Integer[] { 5555, 6666 });
    int intIndex = 7;
    stmt.setArray(intIndex, intArray);
    // long array
    Array longArray = conn.createArrayOf("BIGINT", new Long[] { 7777777L, 8888888L });
    int longIndex = 8;
    stmt.setArray(longIndex, longArray);
    // short array
    Array shortArray = conn.createArrayOf("SMALLINT", new Short[] { 333, 444 });
    int shortIndex = 9;
    stmt.setArray(shortIndex, shortArray);
    // create character array
    Array stringArray = conn.createArrayOf("VARCHAR", new String[] { "a", "b" });
    int stringIndex = 10;
    stmt.setArray(stringIndex, stringArray);
    stmt.execute();
    conn.commit();
    conn.close();

    conn = DriverManager.getConnection(getUrl(), props);
    stmt =
            conn.prepareStatement("SELECT organization_id, entity_id, boolean_array, byte_array, double_array, float_array, int_array, long_array, short_array, string_array FROM "
                    + tableName);
    TestUtil.analyzeTable(conn, tableName);

    ResultSet rs = stmt.executeQuery();
    assertTrue(rs.next());

    assertEquals(tenantId, rs.getString(1));
    assertEquals(ROW1, rs.getString(2));
    
    assertArrayGetString(rs, boolIndex, boolArray, "true, false");
    assertArrayGetString(rs, byteIndex, byteArray, "11, 22");
    assertArrayGetString(rs, doubleIndex, doubleArray, "67.78, 78.89");
    assertArrayGetString(rs, floatIndex, floatArray, "12.23, 45.56");
    assertArrayGetString(rs, intIndex, intArray, "5555, 6666");
    assertArrayGetString(rs, longIndex, longArray, "7777777, 8888888");
    assertArrayGetString(rs, shortIndex, shortArray, "333, 444");
    assertArrayGetString(rs, stringIndex, stringArray, "'a', 'b'");
    conn.close();
}
 

private Connection upsertValues(Properties props, String tableName) throws SQLException, IOException,
        InterruptedException {
    Connection conn;
    PreparedStatement stmt;
    conn = getConnection();
    stmt = upsertStmt(conn, tableName);
    stmt.setString(1, "a");
    String[] s = new String[] { "abc", "def", "ghi", "jkll", null, null, "xxx" };
    Array array = conn.createArrayOf("VARCHAR", s);
    stmt.setArray(2, array);
    s = new String[] { "abc", "def", "ghi", "jkll", null, null, null, "xxx" };
    array = conn.createArrayOf("VARCHAR", s);
    stmt.setArray(3, array);
    stmt.execute();
    conn.commit();
    stmt = upsertStmt(conn, tableName);
    stmt.setString(1, "b");
    s = new String[] { "xyz", "def", "ghi", "jkll", null, null, "xxx" };
    array = conn.createArrayOf("VARCHAR", s);
    stmt.setArray(2, array);
    s = new String[] { "zya", "def", "ghi", "jkll", null, null, null, "xxx" };
    array = conn.createArrayOf("VARCHAR", s);
    stmt.setArray(3, array);
    stmt.execute();
    conn.commit();
    stmt = upsertStmt(conn, tableName);
    stmt.setString(1, "c");
    s = new String[] { "xyz", "def", "ghi", "jkll", null, null, "xxx" };
    array = conn.createArrayOf("VARCHAR", s);
    stmt.setArray(2, array);
    s = new String[] { "zya", "def", "ghi", "jkll", null, null, null, "xxx" };
    array = conn.createArrayOf("VARCHAR", s);
    stmt.setArray(3, array);
    stmt.execute();
    conn.commit();
    stmt = upsertStmt(conn, tableName);
    stmt.setString(1, "d");
    s = new String[] { "xyz", "def", "ghi", "jkll", null, null, "xxx" };
    array = conn.createArrayOf("VARCHAR", s);
    stmt.setArray(2, array);
    s = new String[] { "zya", "def", "ghi", "jkll", null, null, null, "xxx" };
    array = conn.createArrayOf("VARCHAR", s);
    stmt.setArray(3, array);
    stmt.execute();
    conn.commit();
    stmt = upsertStmt(conn, tableName);
    stmt.setString(1, "b");
    s = new String[] { "xyz", "def", "ghi", "jkll", null, null, "xxx" };
    array = conn.createArrayOf("VARCHAR", s);
    stmt.setArray(2, array);
    s = new String[] { "zya", "def", "ghi", "jkll", null, null, null, "xxx" };
    array = conn.createArrayOf("VARCHAR", s);
    stmt.setArray(3, array);
    stmt.execute();
    conn.commit();
    stmt = upsertStmt(conn, tableName);
    stmt.setString(1, "e");
    s = new String[] { "xyz", "def", "ghi", "jkll", null, null, "xxx" };
    array = conn.createArrayOf("VARCHAR", s);
    stmt.setArray(2, array);
    s = new String[] { "zya", "def", "ghi", "jkll", null, null, null, "xxx" };
    array = conn.createArrayOf("VARCHAR", s);
    stmt.setArray(3, array);
    stmt.execute();
    conn.commit();
    return conn;
}