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

/**
 * Test Sybase ASE 15+ bigint data type.
 * @throws Exception
 */
public void testBigint() throws Exception {
    if (!isVersion15orHigher()) {
        return;
    }
    Statement stmt = con.createStatement();
    stmt.execute("CREATE TABLE #TEST (val bigint primary key, val2 bigint null)");
    PreparedStatement pstmt = con.prepareStatement("INSERT INTO #TEST VALUES(?,?)");
    pstmt.setLong(1, Long.MAX_VALUE);
    pstmt.setLong(2, Long.MIN_VALUE);
    pstmt.executeUpdate();
    ResultSet rs = stmt.executeQuery("SELECT * FROM #TEST");
    rs.next();
    assertEquals(Long.MAX_VALUE, rs.getLong(1));
    assertEquals(Long.MIN_VALUE, rs.getLong(2));
    ResultSetMetaData rsmd = rs.getMetaData();
    assertEquals("bigint", rsmd.getColumnTypeName(1));
    assertEquals("bigint", rsmd.getColumnTypeName(2));
    assertEquals(Types.BIGINT, rsmd.getColumnType(1));
    assertEquals(Types.BIGINT, rsmd.getColumnType(2));
}
 

public boolean deleteApply(String siteUserId, String siteFriendId) throws SQLException {
	long startTime = System.currentTimeMillis();
	int result = 0;
	String sql = "DELETE FROM " + FRIEND_APPLY_TABLE + " WHERE site_user_id=? AND site_friend_id=?;";

	Connection conn = null;
	PreparedStatement ps = null;
	try {
		conn = DatabaseConnection.getConnection();
		ps = conn.prepareStatement(sql);
		ps.setString(1, siteUserId);
		ps.setString(2, siteFriendId);

		result = ps.executeUpdate();
	} catch (Exception e) {
		throw e;
	} finally {
		DatabaseConnection.returnConnection(conn, ps);
	}

	LogUtils.dbDebugLog(logger, startTime, result, sql, siteUserId);
	return result > 0;
}
 

@Test
public void testMetadataOnlySelectAfterExecution() throws Exception {
    connection.createStatement().execute(
        "DROP TABLE IF EXISTS test.mymetadata");
    connection.createStatement().execute(
        "CREATE TABLE IF NOT EXISTS test.mymetadata "
      + "(idx Int32, s String) "
      + "ENGINE = TinyLog"
    );
    PreparedStatement insertStmt = connection.prepareStatement(
        "INSERT INTO test.mymetadata (idx, s) VALUES (?, ?)");
    insertStmt.setInt(1, 42);
    insertStmt.setString(2, "foo");
    insertStmt.executeUpdate();
    PreparedStatement metaStmt = connection.prepareStatement(
        "SELECT idx, s FROM test.mymetadata WHERE idx = ?");
    metaStmt.setInt(1, 42);
    metaStmt.executeQuery();
    ResultSetMetaData metadata = metaStmt.getMetaData();
    Assert.assertEquals(metadata.getColumnCount(), 2);
    Assert.assertEquals(metadata.getColumnName(1), "idx");
    Assert.assertEquals(metadata.getColumnName(2), "s");
}
 

protected static void insertEvents(int numEvents, boolean cleanExistingRows, int startRowId)
{
  try (Connection con = DriverManager.getConnection(URL); Statement stmt = con.createStatement()) {
    if (cleanExistingRows) {
      String cleanTable = "delete from " + TABLE_POJO_NAME;
      stmt.executeUpdate(cleanTable);
    }

    String insert = "insert into " + TABLE_POJO_NAME + " values (?,?,?,?,?,?)";
    PreparedStatement pStmt = con.prepareStatement(insert);
    con.prepareStatement(insert);

    for (int i = 0; i < numEvents; i++) {
      pStmt.setInt(1, startRowId + i);
      pStmt.setString(2, "name" + i);
      pStmt.setDate(3, new Date(2016, 1, 1));
      pStmt.setTime(4, new Time(2016, 1, 1));
      pStmt.setTimestamp(5, new Timestamp(2016, 1, 1, 0, 0, 0, 0));
      pStmt.setDouble(6, new Double(55.4));
      pStmt.executeUpdate();
    }

  } catch (SQLException e) {
    throw new RuntimeException(e);
  }
}
 

@Override
public void delete(Generator generator, ValueTuple tuple) {
	PreparedStatement preparedStatement;
	String[] values = CSVLineParse.parseTrimmed(tuple.toCSV());
	StringBuffer line = new StringBuffer("delete from " + meta.getTable() + " where ");
	try {
		ResultSet resultSet = statement.executeQuery("select * from " + meta.getTable());
		for (int i = 1; i <= resultSet.getMetaData().getColumnCount(); i++) {
			int type = resultSet.getMetaData().getColumnType(i);
			line.append(resultSet.getMetaData().getColumnName(i) + "=");
			if (type == Types.CHAR || type == Types.VARCHAR || type == Types.LONGVARCHAR || type == Types.NCHAR || type == Types.NVARCHAR)
				line.append("\'" + values[i - 1] + "\' AND ");
			else
				line.append(values[i - 1] + " AND ");
		}
		preparedStatement = connect.prepareStatement(line.substring(0, line.length() - 5) + ";");
		preparedStatement.executeUpdate();

	} catch (SQLException e) {
		System.out.println("TableJDBC[3]: error " + e);
	}
}
 

/**
 * 更新字符字段
 * @param id
 * @param field
 * @param value
 * @return
 */
@Override
public int updateStringField(String id, String field, String value) {
    Connection conn = ConnectionProvider.getConnection();
    if (conn == null) {
        throw new DAOException(CONNECTION_NOT_FOUND_EXCEPTION);
    }
    StringBuilder sql = new StringBuilder(256);
    sql.append("UPDATE ").append(CHANNEL_TABLE).append(" SET ");
    sql.append(field).append("=? WHERE id=?");
    PreparedStatement stmt = null;
    try {
        stmt = conn.prepareStatement(sql.toString());
        stmt.setString(1, value);
        stmt.setString(2, id);
        return stmt.executeUpdate();
    } catch (SQLException e) {
        throw new DAOException(e);
    } finally {
        DbUtils.closeStatement(stmt);
    }
}
 

public static void delete(String model){
     try {
         Connection con = DriverManager.getConnection("jdbc:sqlite:DBs/kidsDB.db");
         PreparedStatement ps = con.prepareStatement("DELETE FROM kids WHERE mmodel=?");
         ps.setString(1, model);
         if(ps.executeUpdate()==0)
             JOptionPane.showMessageDialog(null, "Entry does not exist!");
         else
             JOptionPane.showMessageDialog(null, "Entry deleted successfully!");
         
     } catch (SQLException ex) {
         Logger.getLogger(ElectronicsDB.class.getName()).log(Level.SEVERE, null, ex);
     }
         
}
 

@Test
public void dropTableInvalidates() throws Exception {
    PreparedStatement pScan = newScan();
    PreparedStatement pDrop = newDropTable();
    assertEquals("Row count from scan1", ROW_COUNT, countRows(pScan.executeQuery()));
    int count = pDrop.executeUpdate();
    assertEquals("Count from drop table", 0, count);
    expectStale(pScan);
    pScan.close();
    pDrop.close();
}
 

private int executeSql(
    String sql, Connection c, UnittypeParameter unittypeParameter, String value, String unitId)
    throws SQLException {
  PreparedStatement pp = c.prepareStatement(sql);
  pp.setString(1, value);
  pp.setString(2, unitId);
  pp.setInt(3, unittypeParameter.getId());
  pp.setQueryTimeout(60);
  int rowsupdated = pp.executeUpdate();
  pp.close();
  return rowsupdated;
}
 

@Test
public void testUpsertSelectLongToInt() throws Exception {
    byte[][] splits = new byte[][] { PInteger.INSTANCE.toBytes(1), PInteger.INSTANCE.toBytes(2),
            PInteger.INSTANCE.toBytes(3), PInteger.INSTANCE.toBytes(4)};
    long ts = nextTimestamp();
    ensureTableCreated(getUrl(),"IntKeyTest",splits, ts-2);
    Properties props = new Properties();
    props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 1));
    Connection conn = DriverManager.getConnection(getUrl(), props);
    String upsert = "UPSERT INTO IntKeyTest VALUES(1)";
    PreparedStatement upsertStmt = conn.prepareStatement(upsert);
    int rowsInserted = upsertStmt.executeUpdate();
    assertEquals(1, rowsInserted);
    conn.commit();
    conn.close();
    
    props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 5));
    conn = DriverManager.getConnection(getUrl(), props);
    upsert = "UPSERT INTO IntKeyTest select i+1 from IntKeyTest";
    upsertStmt = conn.prepareStatement(upsert);
    rowsInserted = upsertStmt.executeUpdate();
    assertEquals(1, rowsInserted);
    conn.commit();
    conn.close();
    
    props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 10));
    conn = DriverManager.getConnection(getUrl(), props);
    String select = "SELECT i FROM IntKeyTest";
    ResultSet rs = conn.createStatement().executeQuery(select);
    assertTrue(rs.next());
    assertEquals(1,rs.getInt(1));
    assertTrue(rs.next());
    assertEquals(2,rs.getInt(1));
    assertFalse(rs.next());
    conn.close();
}
 

public void testDropStore() throws Exception {
  Connection conn = TestUtil.getConnection();
  Statement st = conn.createStatement();
  ResultSet rs = null;

  checkDirExistence("./myhdfs");
  st.execute("create hdfsstore myhdfs namenode 'hdfs://127.0.0.1:" + port + "' homedir './myhdfs'");
  st.execute("create table t1 (col1 int primary key, col2 int) hdfsstore (myhdfs)");
  try {
    st.execute("drop hdfsstore myhdfs");
    fail("Should have received an exception");
  } catch(SQLException exected) {
    //do nothing
  }
  
  //insert some data
  PreparedStatement ps = conn.prepareStatement("insert into t1 values (?, ?)");
  int NUM_ROWS = 100;
  for (int i = 0; i < NUM_ROWS; i++) {
    ps.setInt(1, i);
    ps.setInt(2, i + 1);
    ps.executeUpdate();
  }
  
  st.execute("drop table t1");
  //Now this should work
  st.execute("drop hdfsstore myhdfs");
}
 

public void testNotNullCheck_Bug40018_3() throws Exception
{
  startVMs(1, 3);
  clientSQLExecute(
      1,
      "create table Child ( id2 int primary key, sector_id2 int not null, subsector_id2 int not null)"+getOverflowSuffix());

  try {
    String updateQuery = "update Child set subsector_id2 = ?, sector_id2 = sector_id2 + ? where subsector_id2 = ?";     
    clientSQLExecute(1, "insert into Child values (1,1,1)");
    clientSQLExecute(1, "insert into Child values (2,1,1)");
    clientSQLExecute(1, "insert into Child values (3,1,1)");
    TestUtil.setupConnection();
    PreparedStatement ps = TestUtil.jdbcConn.prepareStatement(updateQuery);
    ps.setNull(1, Types.INTEGER);
    ps.setInt(2, 2);
    ps.setInt(3, 1);
    try {
      ps.executeUpdate();
      fail("Update should not have occured as not null column should not get assigned null value");
    }
    catch (SQLException sqle) {
      this.getLogWriter().info("Expected exception=" + sqle.getMessage());
      assertTrue(sqle.getMessage().indexOf("cannot accept a NULL value") != -1);
    }
  }
  finally {
    GemFireXDQueryObserverHolder
        .setInstance(new GemFireXDQueryObserverAdapter());
    clientSQLExecute(1, "Drop table Child ");     
    //invokeInEveryVM(this.getClass(), "reset");
  }
}
 

/**
 * <p>
 * Delete the cron trigger data for a trigger.
 * </p>
 * 
 * @param conn
 *          the DB Connection
 * @param triggerName
 *          the name of the trigger
 * @param groupName
 *          the group containing the trigger
 * @return the number of rows deleted
 */
public int deleteBlobTrigger(Connection conn, String triggerName,
        String groupName) throws SQLException {
    PreparedStatement ps = null;

    try {
        ps = conn.prepareStatement(rtp(DELETE_BLOB_TRIGGER));
        ps.setString(1, triggerName);
        ps.setString(2, groupName);

        return ps.executeUpdate();
    } finally {
        closeStatement(ps);
    }
}
 

private void updateOrderHolding(Connection conn, int orderID, int holdingID) throws Exception {
    PreparedStatement stmt = getStatement(conn, updateOrderHoldingSQL);

    stmt.setInt(1, holdingID);
    stmt.setInt(2, orderID);
    stmt.executeUpdate();
    stmt.close();
}
 

public int fillAndExecutePreparedInsertStatements(List pstmts, List stmts, int bid)
    throws SQLException {
  int results = 0;
  int numTicketsPerBroker = BrokerPrms.getNumTicketsPerBroker();
  int numTicketPrices = BrokerPrms.getNumTicketPrices();
  PreparedStatement stmt = (PreparedStatement) pstmts.get(0);
  for (int i = 0; i < numTicketsPerBroker; i++) {
    int id = BrokerTicket.getId(i, bid, numTicketsPerBroker);
    stmt.setInt(1, id);
    stmt.setInt(2, bid);
    stmt.setDouble(3, BrokerTicket.getPrice(id, numTicketPrices));
    stmt.setInt(4, BrokerTicket.getQuantity(id));
    stmt.setString(5, BrokerTicket.getTicker(id));
    stmt.setString(6, BrokerTicket.getFiller(id)); // str01
    stmt.setString(7, BrokerTicket.getFiller(id)); // str02
    stmt.setString(8, BrokerTicket.getFiller(id)); // str03
    stmt.setString(9, BrokerTicket.getFiller(id)); // str04
    stmt.setString(10, BrokerTicket.getFiller(id)); // str05
    stmt.setString(11, BrokerTicket.getFiller(id)); // str06
    stmt.setString(12, BrokerTicket.getFiller(id)); // str07
    stmt.setString(13, BrokerTicket.getFiller(id)); // str08
    stmt.setString(14, BrokerTicket.getFiller(id)); // str09
    stmt.setString(15, BrokerTicket.getFiller(id)); // str10
    stmt.setString(16, BrokerTicket.getFiller(id)); // str11
    stmt.setString(17, BrokerTicket.getFiller(id)); // str12
    stmt.setString(18, BrokerTicket.getFiller(id)); // str13
    stmt.setString(19, BrokerTicket.getFiller(id)); // str14
    stmt.setString(20, BrokerTicket.getFiller(id)); // str15
    if (logUpdates) {
      Log.getLogWriter().info("Executing update: " + stmt);
    }
    results += stmt.executeUpdate();
    if (logUpdates) {
      Log.getLogWriter().info("Executed update: " + stmt);
    }
  }
  return results;
}
 

/**
 * After a one-row insert into a table with an auto-generated key, next
 * inserts into a table that does not have an auto-generated key, then
 * requests generated keys.
 * Old harness Test 8.
 * Expected result: ResultSet has one row with a non-NULL key. All four
 * queries in this test return the same result because they fetch the
 * key generated for the previous insert, not the current one.
 * @throws SQLException 
 */
public void testInsertNoGenColAfterOneRowKey() throws SQLException
{
    // Do a one-row insert into a table with an auto-generated key.
    Statement s = createStatement();
    s.execute("insert into t11_AutoGen(c11) values (99)");

    /* The insert into t21_noAutoGen below doesn't insert into a table 
     * with an auto-generated column, so it won't increment the key from 
     * 1 to 2.  The key it fetches will be for the previous insert into 
     * t11_AutoGen.
     */
    int expected=1;

    s.execute("insert into t21_noAutoGen values(27, 'true')", 
        Statement.RETURN_GENERATED_KEYS);
    int keyval = getKeyValue (s.getGeneratedKeys());
    assertEquals("Key value after s.execute()", expected, keyval);

    s.executeUpdate("insert into t21_noAutoGen values(28, 'true')",
        Statement.RETURN_GENERATED_KEYS);
    keyval = getKeyValue (s.getGeneratedKeys());
    assertEquals("Key value after s.executeUpdate()", expected, keyval);

    s.close();

    PreparedStatement ps = prepareStatement(
        "insert into t21_noAutoGen values(29, 'true')", 
        Statement.RETURN_GENERATED_KEYS);
    ps.execute();
    keyval = getKeyValue (ps.getGeneratedKeys());
    assertEquals("Key value after ps.execute()", expected, keyval);

    ps = prepareStatement("insert into t21_noAutoGen values(30, 'true')", 
        Statement.RETURN_GENERATED_KEYS);
    ps.executeUpdate();
    keyval = getKeyValue (ps.getGeneratedKeys());
    assertEquals("Key value after ps.executeUpdate()", expected, keyval);

    ps.close();
}
 

public void testNonKeyBasedTransactionalUpdatesRollbackAndCommit()
    throws Exception {
  startVMs(1, 1);
  Connection conn = TestUtil.jdbcConn;
  Statement st = conn.createStatement();
  st.execute("create schema trade");

  st.execute("create table trade.securities (sec_id int not null, "
      + "symbol varchar(10) not null, price decimal (30, 20), "
      + "exchange varchar(10) not null, tid int, "
      + "constraint sec_pk primary key (sec_id) ) "
      + " partition by column (tid) "+ getSuffix());

  conn.setTransactionIsolation(getIsolationLevel());
  conn.setAutoCommit(false);
  
  final int numRows = 5;
  PreparedStatement ps = conn
      .prepareStatement("insert into trade.securities values "
          + "(?, ?, ?, ?, ?)");
  for (int i = 0; i < numRows; i++) {
    ps.setInt(1, i);
    ps.setString(2, "XXXX" + i);
    ps.setDouble(3, i);
    ps.setString(4, "nasdaq");
    ps.setInt(5, i);
    ps.executeUpdate();
  }
  conn.commit();

  PreparedStatement psUpdate = conn
      .prepareStatement("update trade.securities "
          + "set symbol = ? where sec_id = ? and tid = ?");
  for (int i = 0; i < numRows; i++) {
    psUpdate.setString(1, "YYY" + i);
    psUpdate.setInt(2, i);
    psUpdate.setInt(3, i);
    psUpdate.executeUpdate();
  }
  // psUpdate.executeUpdate();
  // InternalDistributedSystem.getAnyInstance().getLogWriter().info("XXXX update is done");
  ResultSet rs = st.executeQuery("select * from trade.securities");
  int numRowsReturned = 0;
  while (rs.next()) {
    assertTrue("Got" + rs.getString("SYMBOL").trim(),
        (rs.getString("SYMBOL").trim()).startsWith("YYY"));
    numRowsReturned++;
  }
  assertEquals("Expected " + numRows + " row but found " + numRowsReturned,
      numRows, numRowsReturned);
  conn.rollback();
  // now commit, should be an empty tran.
  conn.commit();
  rs = st.executeQuery("select * from trade.securities");

  int numUpdates = 0;
  while (rs.next()) {
    assertTrue("Got " + rs.getString("SYMBOL").trim(), rs.getString("SYMBOL")
        .trim().startsWith("XXXX"));
    numUpdates++;
  }
  assertEquals(numRows, numUpdates);
  rs.close();

  for (int i = 0; i < numRows; i++) {
    psUpdate.setString(1, "YYY" + i);
    psUpdate.setInt(2, i);
    psUpdate.setInt(3, i);
    psUpdate.executeUpdate();
  }
  conn.commit();

  // verify.
  rs = st.executeQuery("select * from trade.securities");
  numUpdates = 0;
  while (rs.next()) {
    assertTrue("Got " + rs.getString("SYMBOL").trim(), rs.getString("SYMBOL")
        .trim().startsWith("YYY"));
    numUpdates++;
  }
  assertEquals(numRows, numUpdates);

  conn.commit();
  rs.close();
  st.close();
  psUpdate.close();
  ps.close();
  conn.close();
}
 

@Override
public void deleteTable(final String name) throws CatalogException {
  Connection conn = null;
  PreparedStatement pstmt = null;

  try {
    conn = getConnection();
    conn.setAutoCommit(false);

    StringBuilder sql = new StringBuilder();
    sql.append("DELETE FROM ").append(TB_COLUMNS);
    sql.append(" WHERE ").append(C_TABLE_ID).append(" = ? ");

    if (LOG.isDebugEnabled()) {
      LOG.debug(sql.toString());
    }

    pstmt = conn.prepareStatement(sql.toString());
    pstmt.setString(1, name);
    pstmt.executeUpdate();
    pstmt.close();

    sql.delete(0, sql.length());
    sql.append("DELETE FROM ").append(TB_OPTIONS);
    sql.append(" WHERE ").append(C_TABLE_ID).append(" = ? ");

    if (LOG.isDebugEnabled()) {
      LOG.debug(sql.toString());
    }

    pstmt = conn.prepareStatement(sql.toString());
    pstmt.setString(1, name);
    pstmt.executeUpdate();
    pstmt.close();

    sql.delete(0, sql.length());
    sql.append("DELETE FROM ").append(TB_STATISTICS);
    sql.append(" WHERE ").append(C_TABLE_ID).append(" = ? ");

    if (LOG.isDebugEnabled()) {
      LOG.debug(sql.toString());
    }

    pstmt = conn.prepareStatement(sql.toString());
    pstmt.setString(1, name);
    pstmt.executeUpdate();
    pstmt.close();

    sql.delete(0, sql.length());
    sql.append("DELETE FROM ").append(TB_PARTTIONS);
    sql.append(" WHERE ").append(C_TABLE_ID).append(" = ? ");

    if (LOG.isDebugEnabled()) {
      LOG.debug(sql.toString());
    }

    pstmt = conn.prepareStatement(sql.toString());
    pstmt.setString(1, name);
    pstmt.executeUpdate();
    pstmt.close();

    sql.delete(0, sql.length());
    sql.append("DELETE FROM ").append(TB_TABLES);
    sql.append(" WHERE ").append(C_TABLE_ID).append(" = ? ");

    if (LOG.isDebugEnabled()) {
      LOG.debug(sql.toString());
    }

    pstmt = conn.prepareStatement(sql.toString());
    pstmt.setString(1, name);
    pstmt.executeUpdate();

    // If there is no error, commit the changes.
    conn.commit();
  } catch (SQLException se) {
    try {
      // If there is any error, rollback the changes.
      conn.rollback();
    } catch (SQLException se2) {
    }
    throw new CatalogException(se);
  } finally {
    CatalogUtil.closeQuietly(conn, pstmt);
  }
}
 

/**
 * Test for 40234 (Performing updates on all indexes irrespective of 
 * index keys being updated or not)
 */
public void _test40234() throws Exception{
  startVMs(1, 1);
  clientSQLExecute(1, "create table trade.securities (sec_id int not null, " +
      "symbol varchar(10) not null, price decimal (30, 20), " +
      "exchange varchar(10) not null, tid int, constraint sec_pk primary key (sec_id), " +
      "constraint sec_uq unique (symbol, exchange), " +
      "constraint exc_ch check (exchange in ('nasdaq', 'nye', 'amex', 'lse', 'fse', 'hkse', 'tse')))  ");

  clientSQLExecute(1,"create table trade.customers (cid int not null, " +
      "cust_name varchar(100), since date, addr varchar(100), " +
      "tid int, primary key (cid))");

  clientSQLExecute( 1, "create table trade.buyorders(oid int not null constraint buyorders_pk primary key, " +
      "cid int, sid int, qty int, bid decimal (30, 20), " +
      "ordertime timestamp, status varchar(10), tid int, " +
      "constraint bo_cust_fk foreign key (cid) references trade.customers (cid), " +
      "constraint bo_sec_fk foreign key (sid) references trade.securities (sec_id), " +
      "constraint bo_qty_ck check (qty>=0))");

  clientSQLExecute(1,"insert into trade.securities values (1,'IBM', " +
      "25.25, 'nasdaq', 5)");
  java.sql.Date since = new java.sql.Date(System.currentTimeMillis());
  Connection conn = TestUtil.jdbcConn;
  PreparedStatement psInsertCust = conn.prepareStatement("insert into trade.customers values (?,?,?,?,?)");
  psInsertCust.setInt(1, 1);
  psInsertCust.setString(2, "XXXX1");
  psInsertCust.setDate(3, since);
  psInsertCust.setString(4, "XXXX1");
  psInsertCust.setInt(5,1);
  psInsertCust.executeUpdate();

  PreparedStatement psInsertBuy = conn.prepareStatement("insert into trade.buyorders values (?,?,?,?,?,?,?,?)");
  BigDecimal value = new BigDecimal (Double.toString((new Random().nextInt(10000)+1) * .01));
  java.sql.Timestamp tstmp = new java.sql.Timestamp(System.currentTimeMillis());
  psInsertBuy.setInt(1,1);
  psInsertBuy.setInt(2,1);
  psInsertBuy.setInt(3,1);
  psInsertBuy.setInt(4, 10);
  psInsertBuy.setBigDecimal(5, value);
  psInsertBuy.setTimestamp(6, tstmp);
  psInsertBuy.setString(7, "open");
  psInsertBuy.setInt(8, 1);
  psInsertBuy.executeUpdate();
  // clientSQLExecute(1, "insert into trade.buyorders(1, 1, 1, 10, "+
  //     new BigDecimal (Double.toString((new Random().nextInt(10000)+1) * .01))+", ' " +
  //                   ""+new java.sql.Timestamp(System.currentTimeMillis())+" ', 'open', 1)");
  PreparedStatement psUpdateBuy = conn.prepareStatement("update trade.buyorders set status = 'cancelled' " +
      "where (ordertime >? or sid=?) and status = 'open' and tid =?");
  psUpdateBuy.setTimestamp(1, tstmp);
  psUpdateBuy.setInt(2, 1);
  psUpdateBuy.setInt(3,1);
  int numUpdate = psUpdateBuy.executeUpdate();
  assertEquals("Should update one row",1, numUpdate);
}
 

/** The ImagePlus, if updated, is saved in the 'tiff_working' column always. */
private void updateInDatabase(Patch patch, String key) throws Exception {

	if (key.equals("tiff_snapshot")) {
		/* // DEPRECATED, now using mipmaps
		InputStream i_stream = null;
		try {
			ImagePlus imp = new ImagePlus("s", snaps.get(patch.getId())); // not calling fetchSnapshot because old code could end in a loop.
			if (null == imp) {
				Utils.log2("DBLoader: snapshot ImagePlus is null!");
				stmt_update_snap.setNull(1, java.sql.Types.BINARY);
			} else {
				i_stream = createZippedStream(imp);
				stmt_update_snap.setBinaryStream(1, i_stream, i_stream.available());
				flush(imp);
			}
			stmt_update_snap.setLong(2, patch.getId());
			stmt_update_snap.executeUpdate();
		} catch (Exception e) {
			IJError.print(e);
		} finally {
			if (null != i_stream) try { i_stream.close(); } catch (Exception e1) { IJError.print(e1); }
		}
		*/
		return;
	}

	StringBuffer sb = new StringBuffer("UPDATE ab_patches SET ");
	boolean update_imp = false;

	if (key.equals("tiff_working")) {
		sb.append("imp_type=").append(patch.getType())
		  .append(", tiff_working=?");
		update_imp = true;
	} else if (key.equals("remove_tiff_working")) {
		sb.append("tiff_working=NULL");
	} else if (key.equals("min_and_max")) {
		sb.append("min=").append(patch.getMin())
		  .append(", max=").append(patch.getMax());
	} else {
		// try the Displayable level
		updateInDatabase((Displayable)patch, key);
		return;
	}

	PreparedStatement st = connection.prepareStatement(sb.append(" WHERE id=").append(patch.getId()).toString());
	int i = 1;
	InputStream i_stream2 = null;
	try {
		if (update_imp) {
			ImagePlus imp = mawts.get(patch.getId()); // WARNING if the cache is very small relative to the size of the images, this strategy may fail
			i_stream2 = createZippedStream(imp);
			st.setBinaryStream(i, i_stream2, i_stream2.available());
			i++; // defensive programming: if later I add any other ..
		}

		st.executeUpdate();

		if (null != i_stream2) i_stream2.close();

	} catch (Exception e) {
		IJError.print(e);
		if (null != i_stream2) try { i_stream2.close(); } catch (Exception e2) { IJError.print(e2); }
	}
}