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

private int updateFromTable(PreparedStatement stmt,  int whichUpdate, int qty,
    int tid, int sid, int sid2) throws SQLException {
  Log.getLogWriter().info("update statement is " + uniqUpdate[whichUpdate]);
  int rowCount = 0;
  switch (whichUpdate) {
  case 0:  
    Log.getLogWriter().info("updating record where sid between " + sid + 
        " and " + sid2 + " and tid is " +tid);
    stmt.setInt(1, qty);
    stmt.setInt(2,tid);
    stmt.setInt(3, sid);
    stmt.setInt(4, sid2);
    rowCount = stmt.executeUpdate();
    break;
  default:
    throw new TestException("incorrect update statement, should not happen");
  }  
  SQLWarning warning = stmt.getWarnings(); //test to see there is a warning
  if (warning != null) {
    SQLHelper.printSQLWarning(warning);
  } 
  return rowCount;
}
 

protected int deleteFromTableTx(PreparedStatement stmt, int cid, int cid1,
    int whichDelete) throws SQLException {
  
  int txId = (Integer) SQLDistTxTest.curTxId.get();
  String database =  SQLHelper.isDerbyConn(stmt.getConnection()) ? "Derby - " : "gemfirexd - TXID:" + txId + " " ;
  String query = " QUERY: " + delete[whichDelete];
  String completionMessage="";
  
  
  switch (whichDelete) {
  case 0:   
    Log.getLogWriter().info(database + "deleting from trade.networth with CID: " + cid + query);  
    stmt.setInt(1, cid);       
    completionMessage =  " rows from trade.networth with CID: " + cid + query;
    break;
  case 1: 
    Log.getLogWriter().info(database + "deleting from trade.networth with 1_CID: " + cid + ",2_CID:" + cid1 +  query);
   
    stmt.setInt(1, cid);
    stmt.setInt(2, cid1);
    completionMessage =  " rows from trade.networth with 1_CID: " + cid + ",2_CID:" + cid1 +  query;
    break;
  default:
    throw new TestException("incorrect delete statement, should not happen");
  }  
   int rowCount = stmt.executeUpdate();
   Log.getLogWriter().info(database + " deleted " + rowCount + completionMessage);
  SQLWarning warning = stmt.getWarnings(); //test to see there is a warning
  if (warning != null) {
    SQLHelper.printSQLWarning(warning);
  } 
  return rowCount;
}
 

protected int deleteFromTable(PreparedStatement stmt, int cid, int tid,
    int whichDelete) throws SQLException {
  String database = SQLHelper.isDerbyConn(stmt.getConnection())?"Derby - " :"gemfirexd - " ;
  String query = " QUERY: " + delete[whichDelete];
     
  switch (whichDelete) {
  case 0:   
    //"delete from trade.customers where cid = ?", 
    Log.getLogWriter().info(database +  "deleting trade.networth with CID:" + cid + query );        
    stmt.setInt(1, cid); //the cid to be deleted is inserted by this thread
    break;
  case 1:
    //"delete from trade.customers where cid = ?", 
    Log.getLogWriter().info(database +  "deleting trade.networth with CID:" + cid + query );  
    stmt.setInt(1, cid);
    break;
  default:
    throw new TestException("incorrect delete statement, should not happen");
  }  
  int rowCount = stmt.executeUpdate();
  Log.getLogWriter().info(database + "deleted " + rowCount + " rows in trade.networth with CID:" + cid + query );  
  SQLWarning warning = stmt.getWarnings(); //test to see there is a warning
  if (warning != null) {
    SQLHelper.printSQLWarning(warning);
  } 
  return rowCount;
}
 

protected int deleteFromTable(PreparedStatement stmt, int cid, String cust_name, int tid, 
    int whichDelete) throws SQLException {
  
  String database = SQLHelper.isDerbyConn(stmt.getConnection())?"Derby - " :"gemfirexd - ";  
  String query = " QUERY: " + delete[whichDelete];
  String successString = "";
  switch (whichDelete) {
  case 0:   
    Log.getLogWriter().info( database + "deleting trade.customers with CUST_NAME:" + cust_name+ ",CID:" + cid + ",TID:" + tid + query);
    stmt.setString(1, cust_name);
    stmt.setInt(2, cid);
    stmt.setInt(3,tid);
    successString = " rows in trade.customers CUST_NAME:" + cust_name+ ",CID:" + cid + ",TID:" + tid + query;
    //stmt.executeUpdate();
    break;
  case 1:
    Log.getLogWriter().info(database + "deleting trade.customers with CID:" + cid  +",TID:" + tid + query); 
    stmt.setInt(1, cid);
    successString = " rows in trade.customers CID:" + cid + ",TID:" + tid + query;
    //stmt.executeUpdate();
    break;
  case 2:
    Log.getLogWriter().info(database + "deleting trade.customers with CID:" + cid + query); 
    stmt.setInt(1, cid);
    successString = " rows in trade.customers CID:" + cid  + query;
    //stmt.executeUpdate();
    break;
  default:
    throw new TestException("incorrect delete statement, should not happen");
  }
  int rowCount = stmt.executeUpdate();
  Log.getLogWriter().info(database + "deleted " + rowCount + successString );
  SQLWarning warning = stmt.getWarnings(); //test to see there is a warning
  if (warning != null) {
    SQLHelper.printSQLWarning(warning);
  } 
  return rowCount;
}
 

protected int selectIntoTable(PreparedStatement stmt, int maxOid, int whichOne, int tid, boolean isPut) 
  throws SQLException {
  Log.getLogWriter().info((isPut ? putinto[whichOne] : selectinto[whichOne]) + " maxOid is " + maxOid + " tid is " + tid);
  stmt.setInt(1, tid);
  if (whichOne == 0) stmt.setInt(2, maxOid);
  else if (whichOne == 1) stmt.setInt(2, tid);
  int rowCount = stmt.executeUpdate();
  SQLWarning warning = stmt.getWarnings(); //test to see there is a warning
  if (warning != null) {
    SQLHelper.printSQLWarning(warning);
  } 
  return rowCount;
}
 

private void doPut(Connection conn, String tableName) throws SQLException {
  String status = HydraUtil.getRandomElement(new String[] { "cancelled", "open", "filled" });
  int tid = RemoteTestModule.getCurrentThread().getThreadId();
  int size=1;
  int cids[] = new int[size];
  int sids[] = new int[size];
  getCustomerAndPortFolio(conn, tid, size, cids, sids);
  String insetStmt = SELLORDER_PUT.replace("{0}", tableName);
  Log.getLogWriter().info("Trying to execute : " + insetStmt);
  PreparedStatement stmt = conn.prepareStatement(insetStmt);
  for (int i = 0; i < size; i++) {
    int cid = cids[i];
    int sid = sids[i];
    int oid = getOrderId(conn, cid, sid);
    int qty = HydraUtil.getnextNonZeroRandomInt(15);
    
    BigDecimal ask = new BigDecimal(TestConfig.tab().getRandGen().nextDouble());
    Timestamp order_time = new Timestamp(System.currentTimeMillis());
    stmt.setInt(1, oid);
    stmt.setInt(2, cid);
    stmt.setInt(3, sid);
    stmt.setInt(4, qty);
    stmt.setBigDecimal(5, ask);
    stmt.setTimestamp(6, order_time);
    stmt.setString(7, status);
    stmt.setInt(8, tid);
    int rowCount = stmt.executeUpdate();
    Log.getLogWriter().info("Params used for put query : cid :" + cid + ", sid : " + sid + ", oid : " + oid + " and qty : " + qty);
    Log.getLogWriter().info("Rows Inserted for " + insetStmt + " is " + rowCount);
    SQLWarning warning = stmt.getWarnings(); // test to see there is a warning
    if (warning != null) {
      SQLHelper.printSQLWarning(warning);
    }
    stmt.close();
    incrementCounter(COUNTER_SELLORDER_INSERTS);
  }
}
 

protected int deleteFromTable(PreparedStatement stmt, long cid, int tid, 
    int whichDelete) throws SQLException {
  
  int txId = (Integer) SQLDistTxTest.curTxId.get();
  String database =  SQLHelper.isDerbyConn(stmt.getConnection()) ? "Derby - " : "gemfirexd - TXID:" + txId + " " ;
  String query = " QUERY: " + delete[whichDelete];
  
  int rowCount=0;
  switch (whichDelete) {
  case 0:   
    //"delete from trade.customersv1 where cid=?",
    Log.getLogWriter().info(database + "deleting from trade.customersv1 " +
    		"with CID:" +cid + query);
    stmt.setLong(1, cid);
    rowCount = stmt.executeUpdate();
    Log.getLogWriter().info(database + "deleted " + rowCount + " rows from " +
    		"trade.customersv1 with CID:" +cid + query);
    break;
  case 1:
    //"delete from trade.customersv1 where cid in (? , ?) ", 
    Log.getLogWriter().info(database + "deleting from trade.customersv1 with" +
    		" CID:" + cid +",CID:" + (cid-tid) + query);
    stmt.setLong(1, cid);
    stmt.setLong(2, cid-tid);
    rowCount = stmt.executeUpdate();
    Log.getLogWriter().info(database + "deleted " + rowCount + " rows from " +
    		"trade.customersv1 with CID:" + cid +",CID:" + (cid-tid) + query);
    break;
  default:
    throw new TestException("incorrect delete statement, should not happen");
  }  
  SQLWarning warning = stmt.getWarnings(); //test to see there is a warning
  if (warning != null) {
    SQLHelper.printSQLWarning(warning);
  } 
  return rowCount;
}
 

protected int deleteToTableTidListTx(PreparedStatement stmt, int oid1, int oid2,
		int oid, BigDecimal bid, int whichDelete, int tid) throws SQLException {
   int rowCount = 0;
   switch (whichDelete) {
   case 0: 
		//	    "delete from trade.buyorders where oid=? and tid=? and bid <?",
     Log.getLogWriter().info("deleting from buyorders for oid: " + oid 
     		+ " and tid: " + tid + " and bid<" + bid);
     stmt.setInt(1, oid);
     stmt.setInt(2, tid);
     stmt.setBigDecimal(3, bid);
     rowCount = stmt.executeUpdate();    
     break;
   case 1: 
		//"delete from trade.buyorders where oid>? and oid<? and status IN ('cancelled', 'filled') and tid=? ", 
     Log.getLogWriter().info("deleting from buyorders for oid > " + oid1 
     		+ " oid < " + oid2 + " and status IN ('cancelled', 'filled') and tid: " + tid);
     stmt.setInt(1, oid1);
     stmt.setInt(2, oid2);
     stmt.setInt(3, tid);
     rowCount = stmt.executeUpdate();       
     break;
   default:
    throw new TestException ("Wrong delete sql string here");
   }
   SQLWarning warning = stmt.getWarnings(); //test to see there is a warning
   if (warning != null) {
     SQLHelper.printSQLWarning(warning);
   } 
   return rowCount;		
}
 

protected int deleteFromTableTx(PreparedStatement stmt, int cid, int cid1,
    int whichDelete) throws SQLException {
  
  int txId = (Integer) SQLDistTxTest.curTxId.get();
  String database =  SQLHelper.isDerbyConn(stmt.getConnection()) ? "Derby - " : "gemfirexd - TXID:" + txId + " " ;
  String query = " QUERY: " + delete[whichDelete];
  String completionMessage="";
  
  
  switch (whichDelete) {
  case 0:   
    Log.getLogWriter().info(database + "deleting from trade.networth with CID: " + cid + query);  
    stmt.setInt(1, cid);       
    completionMessage =  " rows from trade.networth with CID: " + cid + query;
    break;
  case 1: 
    Log.getLogWriter().info(database + "deleting from trade.networth with 1_CID: " + cid + ",2_CID:" + cid1 +  query);
   
    stmt.setInt(1, cid);
    stmt.setInt(2, cid1);
    completionMessage =  " rows from trade.networth with 1_CID: " + cid + ",2_CID:" + cid1 +  query;
    break;
  default:
    throw new TestException("incorrect delete statement, should not happen");
  }  
   int rowCount = stmt.executeUpdate();
   Log.getLogWriter().info(database + " deleted " + rowCount + completionMessage);
  SQLWarning warning = stmt.getWarnings(); //test to see there is a warning
  if (warning != null) {
    SQLHelper.printSQLWarning(warning);
  } 
  return rowCount;
}
 

protected int insertToTable(PreparedStatement stmt, int cid, BigDecimal cash,
    BigDecimal securities, int loanLimit, BigDecimal availLoan, int tid, boolean isPut)
    throws SQLException {
  String jsonLog="";
  String jsontext = "";
  if (SQLTest.hasJSON  & !SQLHelper.isDerbyConn(stmt.getConnection())) {
    jsontext = getJSON(cid, cash, securities, loanLimit, availLoan, tid);
    jsonLog = ", JSON_DETAILS: " + jsontext;
  }
  String txid =  SQLDistTxTest.curTxId.get() == null ? "" : "TXID:" + (Integer)SQLDistTxTest.curTxId.get() + " "; 
  String database = SQLHelper.isDerbyConn(stmt.getConnection())?"Derby - " :"gemfirexd - " + txid + " " ;
  Log.getLogWriter().info( database + (isPut ? "putting" : "inserting") + " into trade.networth with CID:"
      + cid + ",CASH:" + cash + ":SECURITIES," + securities
      + ",LOANLIMIT:" + loanLimit + ",AVAILLOAN:" + availLoan
      + ",TID:" + tid + jsonLog);
  stmt.setInt(1, cid);
  stmt.setBigDecimal(2, cash);
  stmt.setBigDecimal(3, securities);  //insert is 0, will be updated by security through trigger
  stmt.setInt(4, loanLimit); 
  stmt.setBigDecimal(5, availLoan);   //availLoan is the same as loanLimit during insert
  stmt.setInt(6, tid);
  
  if (SQLTest.hasJSON & ! SQLHelper.isDerbyConn(stmt.getConnection())) {
    stmt.setObject(7, jsontext);
  }
  int rowCount = stmt.executeUpdate();
  //update the same row in the parent table customer as  well in case of json
  if (!SQLTest.hasTx && !setCriticalHeap)
  updateJSONToCustomer(stmt.getConnection() ,  cid , getJSONObject(cid, cash, securities, loanLimit, availLoan, tid));
  Log.getLogWriter().info(database + (isPut ? "put" : "inserted ") + rowCount + " rows in trade.networth CID:"
      + cid + ",CASH:" + cash + ":SECURITIES," + securities
      + ",LOANLIMIT:" + loanLimit + ",AVAILLOAN:" + availLoan
      + ",TID:" + tid +  jsonLog);
  
  SQLWarning warning = stmt.getWarnings(); //test to see there is a warning   
  if (warning != null) {
    SQLHelper.printSQLWarning(warning);
  } 
  
  if ( database.contains("gemfirexd") && isPut) {
    if (! SQLTest.ticket49794fixed) {
      //manually update fulldataset table for above entry.
        String deleteStatement = "DELETE FROM TRADE.NETWORTH_FULLDATASET  WHERE  cid = "  + cid ;
        String insertStatement = " INSERT INTO TRADE.NETWORTH_FULLDATASET  VALUES ( " + cid + " ,  " +   cash  + " ,  " + securities + "," +  loanLimit  + " ,  " +  availLoan + "," +   tid +  ")";
        Log.getLogWriter().info(" Trigger behaviour is not defined for putDML hence deleting  the  row  from TRADE.NETWORTH_FULLDATASET with data CID:" +  cid );
        stmt.getConnection().createStatement().execute(deleteStatement);
        Log.getLogWriter().info(" Trigger behaviour is not defined for putDML hence inserting  the  row  into  TRADE.NETWORTH_FULLDATASET with data CID:" +  cid +  ",CASH:" + cash  + ",SECURITIES:" +  securities + " ,LOANLIMIT:" + loanLimit + " ,AVAILLOAN:" + availLoan + ",TID:" + tid );
        stmt.getConnection().createStatement().execute(insertStatement);
      }
       Log.getLogWriter().info( database + (isPut ? "putting" : "inserting") + " into trade.networth with CID:"
        + cid + ",CASH:" + cash + ":SECURITIES," + securities
        + ",LOANLIMIT:" + loanLimit + ",AVAILLOAN:" + availLoan
        + ",TID:" + tid);
      rowCount = stmt.executeUpdate();
      Log.getLogWriter().info(database + (isPut ? "put" : "inserted ") + rowCount + " rows in trade.networth CID:"
          + cid + ",CASH:" + cash + ":SECURITIES," + securities
          + ",LOANLIMIT:" + loanLimit + ",AVAILLOAN:" + availLoan
          + ",TID:" + tid);
      warning = stmt.getWarnings(); //test to see there is a warning   
      if (warning != null) {
        SQLHelper.printSQLWarning(warning);
      } 
  }    
  return rowCount;
}
 

protected int updateToTableCidRangeTx(PreparedStatement stmt, int cid, int sid, 
		int oid, BigDecimal bid, int qty, Timestamp orderTime, int smallCid, int largeCid,
		int whichUpdate) throws SQLException { 
   int rowCount = 0;
   switch (whichUpdate) {
   case 0: 
 		//"update trade.buyorders set bid = ? where cid = ? and oid= ? and status = 'open' ",        
     Log.getLogWriter().info("updating buyorders table bid to " + bid +" for cid: "
     		+ cid + " and oid: " + oid + " and status = 'open'");
     stmt.setBigDecimal(1, bid);
     stmt.setInt(2, cid);
     stmt.setInt(3, oid);
     rowCount = stmt.executeUpdate();
     break;
   case 1: 
     //"update trade.buyorders set sid = ? , qty=? where cid = ? and oid= ? and bid <? and status = 'open'  ",
     Log.getLogWriter().info("updating buyorders table sid to " + sid +
     		" and  qty to " + qty + " for cid: "+ cid + " and oid: " + oid + 
     		" and bid < " + bid + " and status = 'open'");
     stmt.setInt(1, sid);
     stmt.setInt(2, qty);
     stmt.setInt(3, cid);
     stmt.setInt(4, oid);
     stmt.setBigDecimal(5, bid);
    rowCount =  stmt.executeUpdate();
    break;
   case 2: 
     // "update trade.buyorders set status = 'filled'  where cid>? and cid<? and sid = ? and bid<? and status = 'open' ",  
     Log.getLogWriter().info("updating buyorders table status to 'filled' " + 
     		"for cid: " + smallCid + " to " + largeCid + " and sid: " + sid +
     		" and ask < " + bid + " and status = 'open'");
     stmt.setInt(1, smallCid);
     stmt.setInt(2, largeCid);
     stmt.setInt(3, sid);
     stmt.setBigDecimal(4, bid);
     rowCount = stmt.executeUpdate();
     break; 
   case 3: 
	  // "update trade.buyorders set status = 'cancelled' where cid>? and cid<? and ordertime <? and status = 'open'  ",
     Log.getLogWriter().info("updating buyorders table status to 'cancelled' " + 
     		"for cid: " + smallCid + " to " + largeCid +
     		" and ordertime < '" + orderTime + "' and status = 'open'");
     stmt.setInt(1, smallCid);
     stmt.setInt(2, largeCid);
     stmt.setTimestamp(3, orderTime);
     rowCount = stmt.executeUpdate();
     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 insertToTable(PreparedStatement stmt, int sec_id, 
    String symbol, String exchange, BigDecimal price, int tid, boolean isPut, boolean useSpecialInsert) throws SQLException {

  String txId =  SQLDistTxTest.curTxId.get() == null ? "" : "TXID:" + (Integer)SQLDistTxTest.curTxId.get() + " ";
  String driverName = stmt.getConnection().getMetaData().getDriverName();
  String database = SQLHelper.isDerbyConn(stmt.getConnection())?"Derby - " :"gemfirexd - " + txId;
  
  if (!useSpecialInsert) {
    Log.getLogWriter().info( database +  ( isPut ? "putting" : "inserting") + " on trade.securities with SEC_ID:" + sec_id +
        ",SYMBOL:"+ symbol + ",PRICE:" + price + ",EXCHANGE:" + exchange + ",TID:" + tid);
    stmt.setInt(1, sec_id);
    stmt.setString(2, symbol);
    stmt.setBigDecimal(3, price);
    stmt.setString(4, exchange);       
    stmt.setInt(5, tid);
  }
  int rowCount = stmt.executeUpdate();
  SQLWarning warning = stmt.getWarnings(); //test to see there is a warning
  Log.getLogWriter().info( database + ( isPut ? "put " : "inserted ") + rowCount + " rows in trade.securities SEC_ID:" + sec_id +
      ",SYMBOL:"+ symbol + ",PRICE:" + price + ",EXCHANGE:" + exchange + ",TID:" + tid);
  if (warning != null) {
    SQLHelper.printSQLWarning(warning);
  } 
      
  if ( driverName.toLowerCase().contains("gemfirexd") && isPut) {
    if (! SQLTest.ticket49794fixed) {
      //manually update fulldataset table for above entry.
       insertToSecuritiesFulldataset(stmt.getConnection() , sec_id,symbol,price,exchange,tid);
      } 
    
    /*
     // avoid put again to the securities to avoid 23505 on unique key column
     // it should work as the put replace the original unique column 
     // but it will make to much code changes to detect this special case
     // should document this issue though
    Log.getLogWriter().info( database + ( isPut ? "putting " : "inserting ") + " in trade.securities with SEC_ID:" + sec_id +
        ",SYMBOL:"+ symbol + ",PRICE:" + price + ",EXCHANGE:" + exchange + ",TID:" + tid);
   rowCount = stmt.executeUpdate();
   Log.getLogWriter().info( database + ( isPut ? "put " : "inserted ") + rowCount + " rows in trade.securities SEC_ID:" + sec_id +
       ",SYMBOL:"+ symbol + ",PRICE:" + price + ",EXCHANGE:" + exchange + ",TID:" + tid);
   warning = stmt.getWarnings(); //test to see there is a warning   
   if (warning != null) {
     SQLHelper.printSQLWarning(warning);
   } 
   */
  }    
  return rowCount;
}
 

protected int insertToTable(PreparedStatement stmt, String cust_name, Date since, String addr, int tid, boolean isPut) throws SQLException {  
  String database = SQLHelper.isDerbyConn(stmt.getConnection())?"Derby - " :"gemfirexd - ";  

  Log.getLogWriter().info(database + (isPut ? "putting" : "inserting") + " into trade.customers with CUSTNAME:" 
      + cust_name 
      + ",SINCE:" + since 
      + ",ADDR:" + addr 
      + ",TID:" + tid);
  
  stmt.setString(1, cust_name);
  stmt.setDate(2, since);
  stmt.setString(3, addr);       
  stmt.setInt(4, tid);      
  int rowCount = stmt.executeUpdate();


  Log.getLogWriter().info(database + (isPut ? "put" : "inserted") + rowCount + " rows into trade.customers CUSTNAME:" 
      + cust_name 
      + ",SINCE:" + since 
      + ",ADDR:" + addr 
      + ",TID:" + tid);
  SQLWarning warning = stmt.getWarnings(); //test to see there is a warning
  if (warning != null) {
    SQLHelper.printSQLWarning(warning);
  }
  if ( database.contains("gemfirexd") && isPut) {
    Log.getLogWriter().info(database + (isPut ? "putting" : "inserting") + " into trade.customers with CUSTNAME:" 
        + cust_name 
        + ",SINCE:" + since 
        + ",ADDR:" + addr 
        + ",TID:" + tid);
    
    rowCount = stmt.executeUpdate();
    
    Log.getLogWriter().info(database + (isPut ? "put" : "inserted") + rowCount + " rows into trade.customers CUSTNAME:" 
        + cust_name 
        + ",SINCE:" + since 
        + ",ADDR:" + addr 
        + ",TID:" + tid);
    warning = stmt.getWarnings(); //test to see there is a warning
    if (warning != null) {
      SQLHelper.printSQLWarning(warning);
    } 
  }
 
  return rowCount;
}
 

protected int updateToTableCidRangeTx(PreparedStatement stmt, int cid, int cid2, BigDecimal availLoanDelta, BigDecimal sec,
     BigDecimal cashDelta, int newLoanLimit, int whichUpdate) throws SQLException { 
   int rowCount = 0;
   switch (whichUpdate) {
   case 0: 
 		// "update trade.networth set availloan=availloan-? where cid = ? ", 
     Log.getLogWriter().info("updating networth table availLoan to (availLoan- " + availLoanDelta 
         + ") for cid: " + cid );
     stmt.setBigDecimal(1, availLoanDelta);
     stmt.setInt(2, cid);
     rowCount = stmt.executeUpdate();
     break;
   case 1: 
     //"update trade.networth set securities=? where cid = ?  ",
     Log.getLogWriter().info("updating networth table securities to " +sec + " for cid: " + cid ); 
    stmt.setBigDecimal(1, sec);
    stmt.setInt(2, cid);
    rowCount =  stmt.executeUpdate();
    break;
   case 2: 
     //  "update trade.networth set cash=cash-? where cid = ? ",
     Log.getLogWriter().info("updating networth table cash to (cash- " + cashDelta 
         + ") for cid: " + cid);
     stmt.setBigDecimal(1, cashDelta);
     stmt.setInt(2, cid);
     rowCount =  stmt.executeUpdate();
     break; 
   case 3: 
	  //"update trade.networth set loanLimit=? where cid>? and cid < ? ", 
     Log.getLogWriter().info("updating networth table newLoanLimit with " + newLoanLimit + 
     		", for cid: " + cid + " to " + cid2);
     stmt.setInt(1, newLoanLimit);
     stmt.setInt(2, cid);  
     stmt.setInt(3, cid2);
     rowCount = stmt.executeUpdate();
     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 deleteFromTable(PreparedStatement stmt, long cid, BigDecimal cash, 
    BigDecimal availloan, int whichDelete) throws SQLException {
  
  int txId = (Integer) SQLDistTxTest.curTxId.get();
  int round = (Integer) SQLDistTxTest.iteration.get();
  String database =  SQLHelper.isDerbyConn(stmt.getConnection()) ? "Derby - " : "gemfirexd - TXID:" + txId + " " ;
  String query = " QUERY: " + delete[whichDelete];
  
  int rowCount=0;
  switch (whichDelete) {
  case 0:   
    //"delete from trade.networthv1 where cid = ?", 
    Log.getLogWriter().info(database + "deleteing from trade.networthv1 " +
        "with CID:" +cid + query);
    stmt.setLong(1, cid);
    rowCount = stmt.executeUpdate();
    Log.getLogWriter().info(database + "deleted " + rowCount + " rows from " +
        "trade.networthv1 with CID:" +cid + query);
    break;
  case 1:
    //"delete from trade.networthv1 where cid <=? and cid> ? and (cash>? or availloan <?) and round <?"
    Log.getLogWriter().info(database + "deleteing from trade.networthv1 with" +
        " CID:" + cid +",CID:" + (cid-CIDRANGE) + ",CASH:" + cash +
        ",AVAILLOAN:" + availloan + ",ROUND:" + round + query);
    stmt.setLong(1, cid);
    stmt.setLong(2, cid-CIDRANGE);
    stmt.setBigDecimal(3, cash);
    stmt.setBigDecimal(4, availloan);
    stmt.setInt(5, round);
    rowCount = stmt.executeUpdate();
    Log.getLogWriter().info(database + "deleted " + rowCount + " rows from " +
        "trade.networthv1 with" +
        " CID:" + cid +",CID:" + (cid-CIDRANGE) + ",CASH:" + cash +
        ",AVAILLOAN:" + availloan + ",ROUND:" + round + query);
    break;
  default:
    throw new TestException("incorrect delete statement, should not happen");
  }  
  SQLWarning warning = stmt.getWarnings(); //test to see there is a warning
  if (warning != null) {
    SQLHelper.printSQLWarning(warning);
  } 
  return rowCount;
}
 

protected int insertToTable(PreparedStatement stmt, int cid, BigDecimal cash,
    BigDecimal securities, int loanLimit, BigDecimal availLoan, int tid, boolean isPut)
    throws SQLException {
  String txid =  SQLDistTxTest.curTxId.get() == null ? "" : "TXID:" + (Integer)SQLDistTxTest.curTxId.get() + " "; 
  String database = SQLHelper.isDerbyConn(stmt.getConnection())?"Derby - " :"gemfirexd - " + txid + " " ;
  Log.getLogWriter().info( database + (isPut ? "putting" : "inserting") + " into trade.networth with CID:"
      + cid + ",CASH:" + cash + ":SECURITIES," + securities
      + ",LOANLIMIT:" + loanLimit + ",AVAILLOAN:" + availLoan
      + ",TID:" + tid);
  stmt.setInt(1, cid);
  stmt.setBigDecimal(2, cash);
  stmt.setBigDecimal(3, securities);  //insert is 0, will be updated by security through trigger
  stmt.setInt(4, loanLimit); 
  stmt.setBigDecimal(5, availLoan);   //availLoan is the same as loanLimit during insert
  stmt.setInt(6, tid);
  int rowCount = stmt.executeUpdate();
  Log.getLogWriter().info(database + (isPut ? "put" : "inserted ") + rowCount + " rows in trade.networth CID:"
      + cid + ",CASH:" + cash + ":SECURITIES," + securities
      + ",LOANLIMIT:" + loanLimit + ",AVAILLOAN:" + availLoan
      + ",TID:" + tid);
  
  SQLWarning warning = stmt.getWarnings(); //test to see there is a warning   
  if (warning != null) {
    SQLHelper.printSQLWarning(warning);
  } 
  
  if ( database.contains("gemfirexd") && isPut) {
    if (! SQLTest.ticket49794fixed) {
      //manually update fulldataset table for above entry.
        String deleteStatement = "DELETE FROM TRADE.NETWORTH_FULLDATASET  WHERE  cid = "  + cid ;
        String insertStatement = " INSERT INTO TRADE.NETWORTH_FULLDATASET  VALUES ( " + cid + " ,  " +   cash  + " ,  " + securities + "," +  loanLimit  + " ,  " +  availLoan + "," +   tid +  ")";
        Log.getLogWriter().info(" Trigger behaviour is not defined for putDML hence deleting  the  row  from TRADE.NETWORTH_FULLDATASET with data CID:" +  cid );
        stmt.getConnection().createStatement().execute(deleteStatement);
        Log.getLogWriter().info(" Trigger behaviour is not defined for putDML hence inserting  the  row  into  TRADE.NETWORTH_FULLDATASET with data CID:" +  cid +  ",CASH:" + cash  + ",SECURITIES:" +  securities + " ,LOANLIMIT:" + loanLimit + " ,AVAILLOAN:" + availLoan + ",TID:" + tid );
        stmt.getConnection().createStatement().execute(insertStatement);
      }
       Log.getLogWriter().info( database + (isPut ? "putting" : "inserting") + " into trade.networth with CID:"
        + cid + ",CASH:" + cash + ":SECURITIES," + securities
        + ",LOANLIMIT:" + loanLimit + ",AVAILLOAN:" + availLoan
        + ",TID:" + tid);
      rowCount = stmt.executeUpdate();
      Log.getLogWriter().info(database + (isPut ? "put" : "inserted ") + rowCount + " rows in trade.networth CID:"
          + cid + ",CASH:" + cash + ":SECURITIES," + securities
          + ",LOANLIMIT:" + loanLimit + ",AVAILLOAN:" + availLoan
          + ",TID:" + tid);
      warning = stmt.getWarnings(); //test to see there is a warning   
      if (warning != null) {
        SQLHelper.printSQLWarning(warning);
      } 
  }    
  return rowCount;
}
 

protected int updateTable(PreparedStatement 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: 
    //    "update trade.customers set cid = ? where cid=? ", 
    Log.getLogWriter().info(database + "updating trade.customers with CID:" + newCid + " where CID:" + cid + query);
    stmt.setInt(1, newCid);
    stmt.setInt(2, cid);  //the cid got was inserted by this thread before
    stmt.setInt(3, RemoteTestModule.getCurrentThread().getThreadId());
    // stmt.executeUpdate();    //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 CUSTNAME:" + cust_name + 
        ",ADDR:" + addr + " where CID:" + cid + ",TID:" + tid + query); //use update count to see if update successful of not
    stmt.setString(1, cust_name);
    stmt.setString(2, addr);
    stmt.setInt(3, cid);
    stmt.setInt(4, tid);
    rowCount = stmt.executeUpdate();   //may or may not be successful, depends on the cid and tid
    Log.getLogWriter().info(database + "updated " + rowCount + " in  trade.customers CUSTNAME:" + 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 CUSTNAME:" + cust_name + 
          ",ADDR:" + addr +  " where CID:" + cid  + ",TID:" + tid + query); //use update count to see if update successful of not
      stmt.setString(1, cust_name);
      stmt.setString(2, addr);
      stmt.setInt(3, cid);
      stmt.setInt(4, tid);
      rowCount = stmt.executeUpdate();
      Log.getLogWriter().info(database + "updated " + rowCount + " in  trade.customers with CUSTNAME:" + 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 CUSTNAME:" + cust_name + 
          ",SINCE:" + since +  " where CID:" + cid  + ",TID:" + tid + query); //use update count to see if update successful of not
      stmt.setString(1, cust_name);
      if (testworkaroundFor51519) stmt.setDate(2, since, getCal());
      else stmt.setDate(2, since);
      stmt.setInt(3, cid);
      stmt.setInt(4, tid);  
      rowCount = stmt.executeUpdate();
      Log.getLogWriter().info(database + "updated " + rowCount + " in  trade.customers CUSTNAME:" + 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 deleteFromTable(PreparedStatement stmt, int cid, int sid, 
    int tid, int whichDelete) throws SQLException {
  Log.getLogWriter().info("delete statement is " + delete[whichDelete]);
  int rowCount = 0;
  switch (whichDelete) {
  case 0:  
    //"delete from trade.portfoliov1 where cid=? and sid=? and tid=?",
    Log.getLogWriter().info("deleting record in portfoliov1 where cid is " + cid + " and sid is " +sid +" and tid is " + tid);
    stmt.setInt(1, cid);
    stmt.setInt(2, sid);
    stmt.setInt(3, tid);
    if (reproduceTicket50115) rowCount = stmt.executeUpdate();
    break;
  case 1:
    // "delete from trade.portfoliov1 where sid=? and tid=? and cid = (select max(cid) from trade.portfoliov1 where sid =? and tid= ? and data is not null and length(data) > 1000 and length(data) < 2000)",
    Log.getLogWriter().info("deleting record in portfoliov1 where sid = " + sid + " and tid = " +tid +" and sid is " + sid
        + " and tid = " + tid + " and length(data) > 1000 and length(data) < 2000");
    stmt.setInt(1, sid);
    stmt.setInt(2, tid);
    stmt.setInt(3, sid);
    stmt.setInt(4, tid);
    rowCount = stmt.executeUpdate();
    break;
  case 2:
    Log.getLogWriter().info("deleting record in portfoliov1 where cid is " + cid + " and sid is " +sid);
    stmt.setInt(1, cid);
    stmt.setInt(2, sid);
    rowCount = stmt.executeUpdate();
    break;
  case 3:
    // "delete from trade.portfoliov1 where cid<? and sid=? and data is not null and length(data) > 1000 and length(data) < 2000",
    Log.getLogWriter().info("deleting record in portfoliov1 where cid < " + cid + " and sid = " +sid 
       + " and length(data) > 1000 and length(data) < 2000");
    stmt.setInt(1, cid);
    stmt.setInt(2, sid);
    rowCount = stmt.executeUpdate();
    break;
  default:
    throw new TestException("incorrect delete statement, should not happen");
  }  
  SQLWarning warning = stmt.getWarnings(); //test to see there is a warning
  if (warning != null) {
    SQLHelper.printSQLWarning(warning);
  } 
  return rowCount;
}
 

protected int updateTable(PreparedStatement stmt, int sec_id, String symbol,
    String exchange, BigDecimal price, int tid, String lowEnd, String highEnd,
    int whichUpdate) throws SQLException {
  
  int txId = (Integer) SQLDistTxTest.curTxId.get();
  String database =  SQLHelper.isDerbyConn(stmt.getConnection()) ? "Derby - " : "gemfirexd - TXID:" + txId + " " ;
  String query = " QUERY: " + update[whichUpdate];
  String successString ="" , json="" , jsonString = "";
  HashMap<String,Object> map = new HashMap<String, Object>();
  if ( ! SQLHelper.isDerbyConn(stmt.getConnection()) ) {
    map = (HashMap<String,Object>) getCurrentRowOfUpdate(stmt.getConnection() , sec_id);
    query = " QUERY: " + updateJSON[whichUpdate];      
  }
  
  switch (whichUpdate) {
  case 0: 
    //"update trade.securities set price = ? where sec_id = ? ", 
    Log.getLogWriter().info(database + "updating trade.securities  with SECID:" + sec_id  
        + " where PRICE:" + price.doubleValue() + query);
    stmt.setBigDecimal(1, price); 
    stmt.setInt(2, sec_id);
    successString = "trade.securities table with SECID:" + sec_id  
        + " where PRICE:" + price.doubleValue() + query;
    break;
  case 1: 
    //"update trade.securities set tid =? where sec_id = ? ",
    if (!SQLHelper.isDerbyConn(stmt.getConnection())) {
      json = getJSON(sec_id, (String)map.get("symbol"), (String) map.get("exchange"), (BigDecimal) map.get("price"), tid);
      jsonString = ",JSON_DETAILS: " + json;
      }
    
    Log.getLogWriter().info(database + "updating trade.securities with TID:" + tid + jsonString + " where SEC_ID:" + sec_id + query);
    stmt.setInt(1, tid);
    if (!SQLHelper.isDerbyConn(stmt.getConnection())) {
    stmt.setString(2,json);
    stmt.setInt(3, sec_id); 
    } else{
      stmt.setInt(2, sec_id); 
    }
         
    successString = "trade.securities with SECID:" + sec_id + " where TID:" + tid + query;
    break;
  case 2: 
    //"update trade.securities set symbol = ?, exchange =? where sec_id = ?",
    if (!SQLHelper.isDerbyConn(stmt.getConnection())) {
      json = getJSON(sec_id, symbol, exchange, (BigDecimal) map.get("price"), (Integer)map.get("tid"));
      jsonString = ",JSON_DETAILS: " + json;
      }
    Log.getLogWriter().info(database + "updating trade.securities where  SEC_ID:" + sec_id + ",SYMBOL:" + symbol + 
        ",EXCHANGE:" + exchange + query);
    stmt.setString(1, symbol);
    stmt.setString(2, exchange);
    if (!SQLHelper.isDerbyConn(stmt.getConnection())) {
    stmt.setString(3, json);
    stmt.setInt(4, sec_id);
    } else{
      stmt.setInt(3, sec_id);
    }
    
    successString="trade.securities where  SEC_ID:" + sec_id + ",SYMBOL:" + symbol + 
      ",EXCHANGE:" + exchange + query;
    break;
  case 3:
    //"update trade.securities set price = ? where symbol Between ? AND ? and sec_id < ?"
    Log.getLogWriter().info(database + "updating trade.securities with PRICE:" + price.doubleValue() + 
        " where 1_SYMBOL:" + lowEnd + ",2_SYMBOL:" + highEnd + ",SEC_ID:" + sec_id + query);
    stmt.setBigDecimal(1, price);
    stmt.setString(2, lowEnd);
    stmt.setString(3, highEnd);
    stmt.setInt(4, sec_id);
    successString="trade.securities with PRICE:" + price.doubleValue() + 
      " where 1_SYMBOL:" + lowEnd + ",2_SYMBOL:" + highEnd + ",SEC_ID:" + sec_id + query;
    break;
  default:
   throw new TestException ("Wrong update sql string here");
  }
  int rowCount = stmt.executeUpdate();
  Log.getLogWriter().info(database + "updated " + rowCount + " rows in" + successString) ;
  SQLWarning warning = stmt.getWarnings(); //test to see there is a warning
  if (warning != null) {
    SQLHelper.printSQLWarning(warning);
  } 
  return rowCount;
}
 

protected int deleteFromTable(PreparedStatement stmt, long cid, BigDecimal cash, 
    BigDecimal availloan, int whichDelete) throws SQLException {
  
  int txId = (Integer) SQLDistTxTest.curTxId.get();
  int round = (Integer) SQLDistTxTest.iteration.get();
  String database =  SQLHelper.isDerbyConn(stmt.getConnection()) ? "Derby - " : "gemfirexd - TXID:" + txId + " " ;
  String query = " QUERY: " + delete[whichDelete];
  
  int rowCount=0;
  switch (whichDelete) {
  case 0:   
    //"delete from trade.networthv1 where cid = ?", 
    Log.getLogWriter().info(database + "deleteing from trade.networthv1 " +
        "with CID:" +cid + query);
    stmt.setLong(1, cid);
    rowCount = stmt.executeUpdate();
    Log.getLogWriter().info(database + "deleted " + rowCount + " rows from " +
        "trade.networthv1 with CID:" +cid + query);
    break;
  case 1:
    //"delete from trade.networthv1 where cid <=? and cid> ? and (cash>? or availloan <?) and round <?"
    Log.getLogWriter().info(database + "deleteing from trade.networthv1 with" +
        " CID:" + cid +",CID:" + (cid-CIDRANGE) + ",CASH:" + cash +
        ",AVAILLOAN:" + availloan + ",ROUND:" + round + query);
    stmt.setLong(1, cid);
    stmt.setLong(2, cid-CIDRANGE);
    stmt.setBigDecimal(3, cash);
    stmt.setBigDecimal(4, availloan);
    stmt.setInt(5, round);
    rowCount = stmt.executeUpdate();
    Log.getLogWriter().info(database + "deleted " + rowCount + " rows from " +
        "trade.networthv1 with" +
        " CID:" + cid +",CID:" + (cid-CIDRANGE) + ",CASH:" + cash +
        ",AVAILLOAN:" + availloan + ",ROUND:" + round + query);
    break;
  default:
    throw new TestException("incorrect delete statement, should not happen");
  }  
  SQLWarning warning = stmt.getWarnings(); //test to see there is a warning
  if (warning != null) {
    SQLHelper.printSQLWarning(warning);
  } 
  return rowCount;
}