Java Code Examples for java.sql.Statement#getWarnings()

/**
 * Throw an SQLWarningException if we're not ignoring warnings,
 * else log the warnings (at debug level).
 * @param stmt the current JDBC statement
 * @throws SQLWarningException if not ignoring warnings
 * @see org.springframework.jdbc.SQLWarningException
 */
protected void handleWarnings(Statement stmt) throws SQLException {
	if (isIgnoreWarnings()) {
		if (logger.isDebugEnabled()) {
			SQLWarning warningToLog = stmt.getWarnings();
			while (warningToLog != null) {
				logger.debug("SQLWarning ignored: SQL state '" + warningToLog.getSQLState() + "', error code '" +
						warningToLog.getErrorCode() + "', message [" + warningToLog.getMessage() + "]");
				warningToLog = warningToLog.getNextWarning();
			}
		}
	}
	else {
		handleWarnings(stmt.getWarnings());
	}
}
 

public void testGeneratedByDefaultStartWithIncrementBy() throws Exception{
  Connection conn = getConnection();
  Statement stmt = conn.createStatement();
  stmt.execute(
      "create table TESTTABLE (ID int unique not null GENERATED by default AS IDENTITY (START WITH 5, increment by 2) ,"
          + " phone int not null)  ");
  // expect warning for the explicit INCREMENT BY specification
  SQLWarning sw = stmt.getWarnings();
  assertNull(sw);
  
  conn.createStatement().execute("insert into TESTTABLE (id, phone) values (default,1)");
  conn.createStatement().execute("insert into TESTTABLE (phone) values (1)");
  conn.createStatement().execute("insert into TESTTABLE (id, phone) values (default,1)");
  conn.createStatement().execute("insert into TESTTABLE (phone) values (1)");
  
  ResultSet rs = conn.createStatement().executeQuery(
      "select max(id) from TESTTABLE ");
  rs.next();
  assertEquals(11,rs.getInt(1));
  
}
 

protected static void createTriggerProcedure(Connection conn, String procedure) throws SQLException {
  Statement stmt = conn.createStatement();
  stmt.executeUpdate(procedure);    
  SQLWarning warning = stmt.getWarnings(); //test to see there is a warning
  if (warning != null) {
    SQLHelper.printSQLWarning(warning);
  } 
}
 

private final void fillWarnings(UpdateResult ur, Statement stmt)
    throws SQLException {
  SQLWarning warnings = stmt.getWarnings();
  if (warnings != null) {
    ur.setWarnings(gfxdWarning(warnings));
  }
}
 

private final void fillWarnings(UpdateResult ur, Statement stmt)
    throws SQLException {
  SQLWarning warnings = stmt.getWarnings();
  if (warnings != null) {
    ur.setWarnings(gfxdWarning(warnings));
  }
}
 

protected void exeProcedure(Connection conn, String procedure) throws SQLException {
  Statement stmt = conn.createStatement();
  stmt.executeUpdate(procedure);    
  SQLWarning warning = stmt.getWarnings(); //test to see there is a warning
  if (warning != null) {
    SQLHelper.printSQLWarning(warning);
  } 
}
 

protected void exeFunction(Connection conn, String function) throws SQLException {
  Statement stmt = conn.createStatement();
  stmt.executeUpdate(function);    
  SQLWarning warning = stmt.getWarnings(); //test to see there is a warning
  if (warning != null) {
    SQLHelper.printSQLWarning(warning);
  } 
}
 

protected static void createTriggerProcedure(Connection conn, String procedure) throws SQLException {
  Statement stmt = conn.createStatement();
  stmt.executeUpdate(procedure);    
  SQLWarning warning = stmt.getWarnings(); //test to see there is a warning
  if (warning != null) {
    SQLHelper.printSQLWarning(warning);
  } 
}
 

/**
 * Test if the correct warnings are raised.
 * 
 * @throws SQLException
 */
public void testUpdateDeleteWarning() throws SQLException {
	Statement stmt = createStatement();
	SQLWarning sw;

	stmt.executeUpdate("update t2 set c1 = 2 where c1 = 1");
	sw = stmt.getWarnings();
	assertNull("The update should not return a warning.", sw);

	stmt.executeUpdate("update t2 set c1 = 2 where c1 = 1");
	sw = stmt.getWarnings();
	assertNotNull("The update should return a warning.", sw);
	assertEquals("Wrong sql state.", EXPECTED_SQL_CODE, sw
			.getSQLState());

	stmt.executeUpdate("delete from t2 where c1 = 2");
	sw = stmt.getWarnings();
	assertNull("The delete should not return a warning.", sw);

	stmt.executeUpdate("delete from t2 where c1 = 2");
	sw = stmt.getWarnings();
	assertNotNull("The delete should return a warning.", sw);
	assertEquals("Wrong sql state.", EXPECTED_SQL_CODE, sw
			.getSQLState());

	stmt.executeUpdate("delete from t3");
	sw = stmt.getWarnings();
	assertNotNull("The delete cascade should return a warning.", sw);
	assertEquals("Wrong sql state.", EXPECTED_SQL_CODE, sw
			.getSQLState());

	stmt.close();

	rollback();
}
 

protected void exeFunction(Connection conn, String function) throws SQLException {
  Statement stmt = conn.createStatement();
  stmt.executeUpdate(function);    
  SQLWarning warning = stmt.getWarnings(); //test to see there is a warning
  if (warning != null) {
    SQLHelper.printSQLWarning(warning);
  } 
}
 

protected int updateTable(Statement stmt, int newCid, int cid, String cust_name,
    Date since, String addr, int tid, int whichUpdate) throws SQLException {    
  int rowCount = 0;
  
  String database = SQLHelper.isDerbyConn(stmt.getConnection())?"Derby - " :"gemfirexd - ";
  String query ="";
  String jsonString = "";
  String jsonLog = "";
  HashMap<String , Object> map = new HashMap<String, Object>();
  
  if ( SQLTest.hasJSON &  !SQLHelper.isDerbyConn(stmt.getConnection())) {
    map = (HashMap<String,Object>) getCurrentRowOfUpdate(stmt.getConnection() , cid);
    query = " QUERY: " + updateJSON[whichUpdate];      
  }
  else  query = " QUERY: " + update[whichUpdate];

  switch (whichUpdate) {
  case 0:       
    /*
    rowCount = stmt.executeUpdate("update trade.customers" +
        " set cid =" + newCid +
        " where cid=" + cid +
        " and tid =" + tid);
    */ //uncomment this to produce bug 39313 or 39666
    break;
  case 1:             
    // "update trade.customers set cust_name = ? , addr =? where cid=? and tid =?", 
    
    if ( SQLTest.hasJSON &  !SQLHelper.isDerbyConn(stmt.getConnection())) {
      jsonString = getJSON(cid, tid, (Date) map.get("since"), cust_name, addr);
      jsonLog = ",JSON_DETAILS: " + jsonString;
      }
    
    Log.getLogWriter().info(database + "updating trade.customers with CUST_NAME:" + cust_name + 
        ",ADDR:" + addr + jsonLog + "where CID:" + cid + ",TID:" + tid + query); //use update count to see if update successful of not
    rowCount = stmt.executeUpdate("update trade.customers" +
        " set cust_name ='" + cust_name +
        "' , addr ='" + addr +
        (SQLTest.hasJSON ? "' , json_details = '" + jsonString : "" ) +  
        "' where cid=" + cid +
        " and tid =" + tid); //may or may not be successful, depends on the cid and tid     
    Log.getLogWriter().info(database + "updated " + rowCount + " in trade.customers CUST_NAME:" + cust_name + 
        ",ADDR:" + addr +  jsonLog + "where CID:" + cid + ",TID:" + tid + query);
    break;
  case 2: //update name, addr
    //"update trade.customers set cust_name = ? , addr = ? where cid=? and tid =? ",
    if ( SQLTest.hasJSON &  !SQLHelper.isDerbyConn(stmt.getConnection())) {
      jsonString = getJSON(cid, tid, (Date) map.get("since"), cust_name, addr);
      jsonLog = ",JSON_DETAILS: " + jsonString;
      }
    
      Log.getLogWriter().info(database + "updating trade.customers with CUST_NAME:" + cust_name + 
          ",ADDR:" + addr +  jsonLog + " where CID:" + cid  + ",TID:" + tid + query); //use update count to see if update successful of not
      rowCount = stmt.executeUpdate("update trade.customers" +
          " set cust_name ='" + cust_name +
          "' , addr ='" + addr +
          (SQLTest.hasJSON ? "' , json_details = '" + jsonString : "" ) +
          "' where cid=" + cid +
          " and tid =" + tid);
      Log.getLogWriter().info(database + "updated " + rowCount + "in trade.customers  CUST_NAME:" + cust_name + 
          ",ADDR:" + addr +   jsonLog + " where CID:" + cid  + ",TID:" + tid + query);
    break;
  case 3: //update name, since
    //"update trade.customers set cust_name = ?, since =? where cid=? and tid =? "
    if ( SQLTest.hasJSON &  !SQLHelper.isDerbyConn(stmt.getConnection())) {
      jsonString = getJSON(cid, tid, since, cust_name, (String) map.get("addr"));
      jsonLog = ",JSON_DETAILS: " + jsonString;
      }
      Log.getLogWriter().info(database + "updating trade.customers with CUST_NAME:" + cust_name + 
          ",SINCE:" + since +   jsonLog + " where CID:" + cid  + ",TID:" + tid + query); //use update count to see if update successful of not
      rowCount = stmt.executeUpdate("update trade.customers" +
          " set cust_name ='" + cust_name +
          "' , since ='" + since +
          (SQLTest.hasJSON ? "' , json_details = '" + jsonString : "" ) +
          "' where cid=" + cid +
          " and tid =" + tid);
      Log.getLogWriter().info(database + "updated " + rowCount + " rows in trade.customers CUST_NAME:" + cust_name + 
          ",SINCE:" + since +  jsonLog +  " where CID:" + cid  + ",TID:" + tid + query);
    break;
  default:
   throw new TestException ("Wrong update sql string here");
  }
  SQLWarning warning = stmt.getWarnings(); //test to see there is a warning
  if (warning != null) {
    SQLHelper.printSQLWarning(warning);
  } 
  return rowCount;
}
 

@Test
public void testPreparedStatementConfig() throws SQLException {
    Statement statement = swConnection.createStatement();
    statement.cancel();
    statement.getUpdateCount();
    statement.setFetchDirection(1);
    statement.getFetchDirection();
    statement.getResultSetConcurrency();
    statement.getResultSetType();
    statement.isClosed();
    statement.setPoolable(false);
    statement.isPoolable();
    statement.getWarnings();
    statement.clearWarnings();
    statement.setCursorName("test");
    statement.setMaxFieldSize(11);
    statement.getMaxFieldSize();
    statement.setMaxRows(10);
    statement.getMaxRows();
    statement.setEscapeProcessing(true);
    statement.setFetchSize(1);
    statement.getFetchSize();
    statement.setQueryTimeout(1);
    statement.getQueryTimeout();
    Connection connection = statement.getConnection();

    statement.execute("SELECT * FROM test");
    statement.getMoreResults();
    statement.getMoreResults(1);
    statement.getResultSetHoldability();
    statement.getResultSet();

    statement.close();
    verify(mysqlStatement).getUpdateCount();
    verify(mysqlStatement).getMoreResults();
    verify(mysqlStatement).setFetchDirection(anyInt());
    verify(mysqlStatement).getFetchDirection();
    verify(mysqlStatement).getResultSetType();
    verify(mysqlStatement).isClosed();
    verify(mysqlStatement).setPoolable(anyBoolean());
    verify(mysqlStatement).getWarnings();
    verify(mysqlStatement).clearWarnings();
    verify(mysqlStatement).setCursorName(anyString());
    verify(mysqlStatement).setMaxFieldSize(anyInt());
    verify(mysqlStatement).getMaxFieldSize();
    verify(mysqlStatement).setMaxRows(anyInt());
    verify(mysqlStatement).getMaxRows();
    verify(mysqlStatement).setEscapeProcessing(anyBoolean());
    verify(mysqlStatement).getResultSetConcurrency();
    verify(mysqlStatement).getResultSetConcurrency();
    verify(mysqlStatement).getResultSetType();
    verify(mysqlStatement).getMoreResults(anyInt());
    verify(mysqlStatement).setFetchSize(anyInt());
    verify(mysqlStatement).getFetchSize();
    verify(mysqlStatement).getQueryTimeout();
    verify(mysqlStatement).setQueryTimeout(anyInt());
    verify(mysqlStatement).getResultSet();
    assertThat(connection, CoreMatchers.<Connection>is(swConnection));

    TraceSegment traceSegment = segmentStorage.getTraceSegments().get(0);
    List<AbstractTracingSpan> spans = SegmentHelper.getSpans(traceSegment);
    assertThat(spans.size(), is(1));
    assertDBSpan(spans.get(0), "Mysql/JDBI/Statement/execute", "SELECT * FROM test");
}
 

/**
 * Test that you get cursor operation conflict warning if updating 
 * a row which has been deleted from the table, now using 
 * positioned updates / deletes.
 */
public void testCursorOperationConflictWarning2() 
    throws SQLException 
{
    Statement s = createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                                      ResultSet.CONCUR_UPDATABLE);
    s.setCursorName(getNextCursorName());
    ResultSet rs = s.executeQuery("select * from t1");
    rs.next();
    createStatement().executeUpdate ("delete from t1 where id=" +
                                         rs.getString("ID"));
    
    final int newValue = -3333;
    final int oldValue = rs.getInt(2);
    
    Statement s3 = createStatement();
    int updateCount = s3.executeUpdate
        ("update t1 set A=" + newValue + 
         " where current of " + rs.getCursorName());
    
    rs.relative(0);
    SQLWarning warn = s3.getWarnings();
    assertWarning(warn, CURSOR_OPERATION_CONFLICT);
    assertTrue("Expected rs.rowUpdated() to be false", !rs.rowUpdated());
    assertTrue("Expected rs.rowDeleted() to be false", !rs.rowDeleted());
    assertEquals("Did not expect the resultset to be updated", oldValue, rs.getInt(2));
    assertEquals("Expected update count to be 0", 0, updateCount);
    
    Statement s4 = createStatement();
    updateCount = s4.executeUpdate("delete from t1 where current of " +
                                   rs.getCursorName());
    
    rs.relative(0);
    warn = s4.getWarnings();
    assertWarning(warn, CURSOR_OPERATION_CONFLICT);
    assertTrue("Expected rs.rowUpdated() to be false", !rs.rowUpdated());
    assertTrue("Expected rs.rowDeleted() to be false", !rs.rowDeleted());
    assertEquals("Did not expect the resultset to be updated", oldValue, rs.getInt(2));
    assertEquals("Expected update count to be 0", 0, updateCount);
    
    rs.close();
    s.close();
    s3.close();
    s4.close();
}
 

public void testIdentityGeneratedByDefault() throws Exception {
  // reduce logs
  reduceLogLevelForTest("config");

  Connection conn = getConnection();
  Statement stmt = conn.createStatement();

  // Check for IDENTITY column with INT size
  stmt.execute("create table trade.customers (cid int not null "
      + "GENERATED BY DEFAULT AS IDENTITY (START WITH 8, INCREMENT BY 1), "
      + "tid int, primary key (cid), constraint cust_ck check (cid >= 0))");
  // expect warning for the explicit INCREMENT BY specification
  SQLWarning sw = stmt.getWarnings();
  assertNull(sw);

  final int numRows = 4000;
  // insertion in this table should start with 8
  runIdentityChecksForCustomersTable(conn, numRows, new int[] { 1 },
      new String[] { "CID" }, 1, 8, 0, null,false);

  // No warnings for default start and increment by
  stmt.execute("drop table trade.customers");
  stmt.execute("create table trade.customers (cid int not null "
      + "GENERATED BY DEFAULT AS IDENTITY, "
      + "tid int, primary key (cid), constraint cust_ck check (cid >= 0))");
  // expect no warning for the default start
  sw = stmt.getWarnings();
  assertNull(sw);

  // Now check for IDENTITY column with BIGINT size
  stmt.execute("drop table trade.customers");
  stmt.execute("create table trade.customers (cid bigint not null "
      + "GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 7), "
      + "tid int, primary key (cid), constraint cust_ck check (cid >= 0))");
  // expect warning for the non-default increment
  sw = stmt.getWarnings();
  assertNull(sw);

  runIdentityChecksForCustomersTable(conn, numRows, new int[] { 1 },
      new String[] { "CID" }, 1, 1, 0, null,false);

  stmt.execute("drop table trade.customers");
}
 

public void testAddGeneratedIdentityColumn() throws Exception {
  setupConnection();
  Connection conn = TestUtil.jdbcConn;
  Statement stmt = conn.createStatement();

  // Check for IDENTITY column with INT size added using ALTER TABLE
  stmt.execute("create table trade.customers (tid int, cid int not null, "
      + "primary key (cid), constraint cust_ck check (cid >= 0))");
  // first some inserts with gaps
  final int maxValue = 1000;
  int stepValue = 3;
  PreparedStatement pstmt = conn.prepareStatement("insert into "
      + "trade.customers (tid, cid) values (?, ?)");
  for (int v = 1; v <= maxValue; v += stepValue) {
    pstmt.setInt(1, v * stepValue);
    pstmt.setInt(2, v);
    pstmt.addBatch();
  }
  pstmt.executeBatch();

  // now add the GENERATED IDENTITY column specification
  stmt.execute("alter table trade.customers alter column cid "
      + "SET GENERATED ALWAYS AS IDENTITY");

  SQLWarning sw = stmt.getWarnings();
  assertNull(sw);

  final int numRows = 2000;
  // insertion in this table should start with maxValue
  CreateTableTest.runIdentityChecksForCustomersTable(conn, numRows,
      new int[] { 2 }, new String[] { "CID" }, 1, -maxValue, 0, null,true);

  // Now check for the same with BIGINT size
  stmt.execute("drop table trade.customers");
  stmt.execute("create table trade.customers (tid int, cid bigint not null, "
      + "addr varchar(100), primary key (cid), "
      + "constraint cust_ck check (cid >= 0))");

  stepValue = 2;
  pstmt = conn.prepareStatement(
      "insert into trade.customers (cid, tid) values (?, ?)");
  for (int v = 1; v <= maxValue; v += stepValue) {
    pstmt.setInt(2, v);
    pstmt.setInt(1, v * stepValue);
    pstmt.addBatch();
  }
  pstmt.executeBatch();

  // now add the GENERATED IDENTITY column specification
  stmt.execute("alter table trade.customers alter cid "
      + "SET GENERATED ALWAYS AS IDENTITY");

  assertNull(stmt.getWarnings());

  CreateTableTest.runIdentityChecksForCustomersTable(conn, numRows,
      new int[] { 2 }, new String[] { "CID" }, 1, -(maxValue * stepValue), 0,
      null,true);

  stmt.execute("drop table trade.customers");
}
 

protected int updateTable(Statement stmt, int newCid, int cid, String cust_name,
    Date since, String addr, int tid, int whichUpdate) throws SQLException {    
  int rowCount = 0;
  
  String database = SQLHelper.isDerbyConn(stmt.getConnection())?"Derby - " :"gemfirexd - ";  
  String query = " QUERY: " + update[whichUpdate];
  switch (whichUpdate) {
  case 0:       
    /*
    rowCount = stmt.executeUpdate("update trade.customers" +
        " set cid =" + newCid +
        " where cid=" + cid +
        " and tid =" + tid);
    */ //uncomment this to produce bug 39313 or 39666
    break;
  case 1: 
    // "update trade.customers set cust_name = ? , addr =? where cid=? and tid =?", 
    Log.getLogWriter().info(database + "updating trade.customers with CUST_NAME:" + cust_name + 
        ",ADDR:" + addr + "where CID:" + cid + ",TID:" + tid + query); //use update count to see if update successful of not
    rowCount = stmt.executeUpdate("update trade.customers" +
        " set cust_name ='" + cust_name +
        "' , addr ='" + addr +
        "' where cid=" + cid +
        " and tid =" + tid); //may or may not be successful, depends on the cid and tid     
    Log.getLogWriter().info(database + "updated " + rowCount + " in trade.customers CUST_NAME:" + cust_name + 
        ",ADDR:" + addr + "where CID:" + cid + ",TID:" + tid + query);
    break;
  case 2: //update name, addr
    //"update trade.customers set cust_name = ? , addr = ? where cid=? and tid =? ",
      Log.getLogWriter().info(database + "updating trade.customers with CUST_NAME:" + cust_name + 
          ",ADDR:" + addr +  " where CID:" + cid  + ",TID:" + tid + query); //use update count to see if update successful of not
      rowCount = stmt.executeUpdate("update trade.customers" +
          " set cust_name ='" + cust_name +
          "' , addr ='" + addr +
          "' where cid=" + cid +
          " and tid =" + tid);
      Log.getLogWriter().info(database + "updated " + rowCount + "in trade.customers  CUST_NAME:" + cust_name + 
          ",ADDR:" + addr +  " where CID:" + cid  + ",TID:" + tid + query);
    break;
  case 3: //update name, since
    //"update trade.customers set cust_name = ?, since =? where cid=? and tid =? " 
      Log.getLogWriter().info(database + "updating trade.customers with CUST_NAME:" + cust_name + 
          ",SINCE:" + since +  " where CID:" + cid  + ",TID:" + tid + query); //use update count to see if update successful of not
      rowCount = stmt.executeUpdate("update trade.customers" +
          " set cust_name ='" + cust_name +
          "' , since ='" + since +
          "' where cid=" + cid +
          " and tid =" + tid);
      Log.getLogWriter().info(database + "updated " + rowCount + " rows in trade.customers CUST_NAME:" + cust_name + 
          ",SINCE:" + since +  " where CID:" + cid  + ",TID:" + tid + query);
    break;
  default:
   throw new TestException ("Wrong update sql string here");
  }
  SQLWarning warning = stmt.getWarnings(); //test to see there is a warning
  if (warning != null) {
    SQLHelper.printSQLWarning(warning);
  } 
  return rowCount;
}
 

protected int updateTable(Statement stmt, int newCid, int cid, String cust_name,
    Date since, String addr, int tid, int whichUpdate) throws SQLException {    
  int rowCount = 0;
  
  String database = SQLHelper.isDerbyConn(stmt.getConnection())?"Derby - " :"gemfirexd - ";
  String query ="";
  String jsonString = "";
  String jsonLog = "";
  HashMap<String , Object> map = new HashMap<String, Object>();
  
  if ( SQLTest.hasJSON &  !SQLHelper.isDerbyConn(stmt.getConnection())) {
    map = (HashMap<String,Object>) getCurrentRowOfUpdate(stmt.getConnection() , cid);
    query = " QUERY: " + updateJSON[whichUpdate];      
  }
  else  query = " QUERY: " + update[whichUpdate];

  switch (whichUpdate) {
  case 0:       
    /*
    rowCount = stmt.executeUpdate("update trade.customers" +
        " set cid =" + newCid +
        " where cid=" + cid +
        " and tid =" + tid);
    */ //uncomment this to produce bug 39313 or 39666
    break;
  case 1:             
    // "update trade.customers set cust_name = ? , addr =? where cid=? and tid =?", 
    
    if ( SQLTest.hasJSON &  !SQLHelper.isDerbyConn(stmt.getConnection())) {
      jsonString = getJSON(cid, tid, (Date) map.get("since"), cust_name, addr);
      jsonLog = ",JSON_DETAILS: " + jsonString;
      }
    
    Log.getLogWriter().info(database + "updating trade.customers with CUST_NAME:" + cust_name + 
        ",ADDR:" + addr + jsonLog + "where CID:" + cid + ",TID:" + tid + query); //use update count to see if update successful of not
    rowCount = stmt.executeUpdate("update trade.customers" +
        " set cust_name ='" + cust_name +
        "' , addr ='" + addr +
        (SQLTest.hasJSON ? "' , json_details = '" + jsonString : "" ) +  
        "' where cid=" + cid +
        " and tid =" + tid); //may or may not be successful, depends on the cid and tid     
    Log.getLogWriter().info(database + "updated " + rowCount + " in trade.customers CUST_NAME:" + cust_name + 
        ",ADDR:" + addr +  jsonLog + "where CID:" + cid + ",TID:" + tid + query);
    break;
  case 2: //update name, addr
    //"update trade.customers set cust_name = ? , addr = ? where cid=? and tid =? ",
    if ( SQLTest.hasJSON &  !SQLHelper.isDerbyConn(stmt.getConnection())) {
      jsonString = getJSON(cid, tid, (Date) map.get("since"), cust_name, addr);
      jsonLog = ",JSON_DETAILS: " + jsonString;
      }
    
      Log.getLogWriter().info(database + "updating trade.customers with CUST_NAME:" + cust_name + 
          ",ADDR:" + addr +  jsonLog + " where CID:" + cid  + ",TID:" + tid + query); //use update count to see if update successful of not
      rowCount = stmt.executeUpdate("update trade.customers" +
          " set cust_name ='" + cust_name +
          "' , addr ='" + addr +
          (SQLTest.hasJSON ? "' , json_details = '" + jsonString : "" ) +
          "' where cid=" + cid +
          " and tid =" + tid);
      Log.getLogWriter().info(database + "updated " + rowCount + "in trade.customers  CUST_NAME:" + cust_name + 
          ",ADDR:" + addr +   jsonLog + " where CID:" + cid  + ",TID:" + tid + query);
    break;
  case 3: //update name, since
    //"update trade.customers set cust_name = ?, since =? where cid=? and tid =? "
    if ( SQLTest.hasJSON &  !SQLHelper.isDerbyConn(stmt.getConnection())) {
      jsonString = getJSON(cid, tid, since, cust_name, (String) map.get("addr"));
      jsonLog = ",JSON_DETAILS: " + jsonString;
      }
      Log.getLogWriter().info(database + "updating trade.customers with CUST_NAME:" + cust_name + 
          ",SINCE:" + since +   jsonLog + " where CID:" + cid  + ",TID:" + tid + query); //use update count to see if update successful of not
      rowCount = stmt.executeUpdate("update trade.customers" +
          " set cust_name ='" + cust_name +
          "' , since ='" + since +
          (SQLTest.hasJSON ? "' , json_details = '" + jsonString : "" ) +
          "' where cid=" + cid +
          " and tid =" + tid);
      Log.getLogWriter().info(database + "updated " + rowCount + " rows in trade.customers CUST_NAME:" + cust_name + 
          ",SINCE:" + since +  jsonLog +  " where CID:" + cid  + ",TID:" + tid + query);
    break;
  default:
   throw new TestException ("Wrong update sql string here");
  }
  SQLWarning warning = stmt.getWarnings(); //test to see there is a warning
  if (warning != null) {
    SQLHelper.printSQLWarning(warning);
  } 
  return rowCount;
}
 

/**
   * @param timOutOnCallableStmt - time out (in seconds) set on the outer 
   * callable stmt
   */
  public static void myProc2(int timOutOnCallableStmt, 
      int[] count, ResultSet[] resultSet1,
      ResultSet[] resultSet2,
      ProcedureExecutionContext ctx) 
          throws SQLException, InterruptedException {
    Connection conn = ctx.getConnection();
//    Connection conn = DriverManager.getConnection("jdbc:default:connection");
    
    Statement stmt = conn.createStatement();
    
    // make sure that by default the timeout for statement in proc is same as 
    // that of outer callable statement's time out
    assertEquals(timOutOnCallableStmt, stmt.getQueryTimeout());
    
    // timeout cannot be more than the outer callable stmt's timeout, so the 
    // time out will be set to outer statement's timeout
    stmt.setQueryTimeout(timOutOnCallableStmt + 1);
    SQLWarning sw = stmt.getWarnings();
    if (sw != null) {
      if (!sw.getSQLState().equals("01509")) {
//        fail("Expected warning state 01509. Received warning:" + sw.getSQLState());
        throw sw;
      } // else ignore
    }
    else {
      fail("This test should have thrown a warning(01509) as query time out "
          + "for statement in stored procedure can not be more "
          + "than outer callable statement's time out");
    }
    assertEquals(timOutOnCallableStmt, stmt.getQueryTimeout());

    // set different(lesser) timeout for stmt in sproc
    stmt.setQueryTimeout(1); 
    assertEquals(1, stmt.getQueryTimeout());
    
    stmt.execute("select * from mytable");
    resultSet1[0] = stmt.getResultSet();
    
    Statement stmt3 = conn.createStatement();
    stmt3 .execute("select count(*) from mytable");
    stmt3.getResultSet().next();
    Integer cnt = stmt3.getResultSet().getInt(1);
    count[0] = cnt;
    
    Statement stmt2 = conn.createStatement();
    stmt2.execute("select count(*) from mytable");
    resultSet2[0] = stmt2.getResultSet();
  }
 

/**
 * Test that you get cursor operation conflict warning if updating 
 * a row which has been deleted from the table, now using 
 * positioned updates / deletes.
 */
public void testCursorOperationConflictWarning2() 
    throws SQLException 
{
    Statement s = createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                                      ResultSet.CONCUR_UPDATABLE);
    s.setCursorName(getNextCursorName());
    ResultSet rs = s.executeQuery("select * from t1");
    rs.next();
    createStatement().executeUpdate ("delete from t1 where id=" +
                                         rs.getString("ID"));
    
    final int newValue = -3333;
    final int oldValue = rs.getInt(2);
    
    Statement s3 = createStatement();
    int updateCount = s3.executeUpdate
        ("update t1 set A=" + newValue + 
         " where current of " + rs.getCursorName());
    
    rs.relative(0);
    SQLWarning warn = s3.getWarnings();
    assertWarning(warn, CURSOR_OPERATION_CONFLICT);
    assertTrue("Expected rs.rowUpdated() to be false", !rs.rowUpdated());
    assertTrue("Expected rs.rowDeleted() to be false", !rs.rowDeleted());
    assertEquals("Did not expect the resultset to be updated", oldValue, rs.getInt(2));
    assertEquals("Expected update count to be 0", 0, updateCount);
    
    Statement s4 = createStatement();
    updateCount = s4.executeUpdate("delete from t1 where current of " +
                                   rs.getCursorName());
    
    rs.relative(0);
    warn = s4.getWarnings();
    assertWarning(warn, CURSOR_OPERATION_CONFLICT);
    assertTrue("Expected rs.rowUpdated() to be false", !rs.rowUpdated());
    assertTrue("Expected rs.rowDeleted() to be false", !rs.rowDeleted());
    assertEquals("Did not expect the resultset to be updated", oldValue, rs.getInt(2));
    assertEquals("Expected update count to be 0", 0, updateCount);
    
    rs.close();
    s.close();
    s3.close();
    s4.close();
}
 

public void testAddGeneratedIdentityColumn() throws Exception {
  setupConnection();
  Connection conn = TestUtil.jdbcConn;
  Statement stmt = conn.createStatement();

  // Check for IDENTITY column with INT size added using ALTER TABLE
  stmt.execute("create table trade.customers (tid int, cid int not null, "
      + "primary key (cid), constraint cust_ck check (cid >= 0))");
  // first some inserts with gaps
  final int maxValue = 1000;
  int stepValue = 3;
  PreparedStatement pstmt = conn.prepareStatement("insert into "
      + "trade.customers (tid, cid) values (?, ?)");
  for (int v = 1; v <= maxValue; v += stepValue) {
    pstmt.setInt(1, v * stepValue);
    pstmt.setInt(2, v);
    pstmt.addBatch();
  }
  pstmt.executeBatch();

  // now add the GENERATED IDENTITY column specification
  stmt.execute("alter table trade.customers alter column cid "
      + "SET GENERATED ALWAYS AS IDENTITY");

  SQLWarning sw = stmt.getWarnings();
  assertNull(sw);

  final int numRows = 2000;
  // insertion in this table should start with maxValue
  CreateTableTest.runIdentityChecksForCustomersTable(conn, numRows,
      new int[] { 2 }, new String[] { "CID" }, 1, -maxValue, 0, null,true);

  // Now check for the same with BIGINT size
  stmt.execute("drop table trade.customers");
  stmt.execute("create table trade.customers (tid int, cid bigint not null, "
      + "addr varchar(100), primary key (cid), "
      + "constraint cust_ck check (cid >= 0))");

  stepValue = 2;
  pstmt = conn.prepareStatement(
      "insert into trade.customers (cid, tid) values (?, ?)");
  for (int v = 1; v <= maxValue; v += stepValue) {
    pstmt.setInt(2, v);
    pstmt.setInt(1, v * stepValue);
    pstmt.addBatch();
  }
  pstmt.executeBatch();

  // now add the GENERATED IDENTITY column specification
  stmt.execute("alter table trade.customers alter cid "
      + "SET GENERATED ALWAYS AS IDENTITY");

  assertNull(stmt.getWarnings());

  CreateTableTest.runIdentityChecksForCustomersTable(conn, numRows,
      new int[] { 2 }, new String[] { "CID" }, 1, -(maxValue * stepValue), 0,
      null,true);

  stmt.execute("drop table trade.customers");
}