Java Code Examples for java.sql.CallableStatement#setNull()

/**
 * Test for bug [992715] wasnull() always returns false
 */
public void testCallableRegisterOutParameter6()
   throws Exception
{
   dropProcedure( "rop2" );

   Statement stmt = con.createStatement();
   stmt.execute( "create procedure rop2 @bool bit, @whatever varchar(1) OUTPUT as\r\n " + "begin\r\n" + "set @whatever = null\r\n" + "end" );
   stmt.close();

   CallableStatement cstmt = con.prepareCall( "{call rop2(?,?)}" );

   cstmt.setNull( 1, Types.BOOLEAN );
   cstmt.registerOutParameter( 2, Types.VARCHAR );
   cstmt.execute();

   assertTrue( cstmt.getString( 2 ) == null );
   assertTrue( cstmt.wasNull() );
   cstmt.close();
}
 

/**
 * Test for bug [946171] null boolean in CallableStatement bug
 */
public void testCallableRegisterOutParameter5()
   throws Exception
{
   dropProcedure( "rop1" );

   Statement stmt = con.createStatement();
   stmt.execute( "create procedure rop1 @bool bit, @whatever int OUTPUT as begin set @whatever = 1 end" );
   stmt.close();

   CallableStatement cstmt = con.prepareCall( "{call rop1(?,?)}" );

   cstmt.setNull( 1, Types.BOOLEAN );
   cstmt.registerOutParameter( 2, Types.INTEGER );
   cstmt.execute();

   assertTrue( cstmt.getInt( 2 ) == 1 );
   cstmt.close();
}
 

/**
 * Tests fix for BUG#60816 - Cannot pass NULL to an INOUT procedure parameter
 * 
 * @throws Exception
 */
public void testBug60816() throws Exception {

    createProcedure("test60816_1", "(INOUT x INTEGER)\nBEGIN\nSET x = x + 1;\nEND");
    createProcedure("test60816_2", "(x INTEGER, OUT y INTEGER)\nBEGIN\nSET y = x + 1;\nEND");
    createProcedure("test60816_3", "(INOUT x INTEGER)\nBEGIN\nSET x = 10;\nEND");

    CallableStatement call = this.conn.prepareCall("{ call test60816_1(?) }");
    call.setInt(1, 1);
    call.registerOutParameter(1, Types.INTEGER);
    call.execute();
    assertEquals(2, call.getInt(1));

    call = this.conn.prepareCall("{ call test60816_2(?, ?) }");
    call.setInt(1, 1);
    call.registerOutParameter(2, Types.INTEGER);
    call.execute();
    assertEquals(2, call.getInt(2));

    call = this.conn.prepareCall("{ call test60816_2(?, ?) }");
    call.setNull(1, Types.INTEGER);
    call.registerOutParameter(2, Types.INTEGER);
    call.execute();
    assertEquals(0, call.getInt(2));
    assertTrue(call.wasNull());

    call = this.conn.prepareCall("{ call test60816_1(?) }");
    call.setNull(1, Types.INTEGER);
    call.registerOutParameter(1, Types.INTEGER);
    call.execute();
    assertEquals(0, call.getInt(1));
    assertTrue(call.wasNull());

    call = this.conn.prepareCall("{ call test60816_3(?) }");
    call.setNull(1, Types.INTEGER);
    call.registerOutParameter(1, Types.INTEGER);
    call.execute();
    assertEquals(10, call.getInt(1));
}
 

/**
 * Tests fix for BUG#60816 - Cannot pass NULL to an INOUT procedure parameter
 * 
 * @throws Exception
 */
public void testBug60816() throws Exception {

    createProcedure("test60816_1", "(INOUT x INTEGER)\nBEGIN\nSET x = x + 1;\nEND");
    createProcedure("test60816_2", "(x INTEGER, OUT y INTEGER)\nBEGIN\nSET y = x + 1;\nEND");
    createProcedure("test60816_3", "(INOUT x INTEGER)\nBEGIN\nSET x = 10;\nEND");

    CallableStatement call = this.conn.prepareCall("{ call test60816_1(?) }");
    call.setInt(1, 1);
    call.registerOutParameter(1, Types.INTEGER);
    call.execute();
    assertEquals(2, call.getInt(1));

    call = this.conn.prepareCall("{ call test60816_2(?, ?) }");
    call.setInt(1, 1);
    call.registerOutParameter(2, Types.INTEGER);
    call.execute();
    assertEquals(2, call.getInt(2));

    call = this.conn.prepareCall("{ call test60816_2(?, ?) }");
    call.setNull(1, Types.INTEGER);
    call.registerOutParameter(2, Types.INTEGER);
    call.execute();
    assertEquals(0, call.getInt(2));
    assertTrue(call.wasNull());

    call = this.conn.prepareCall("{ call test60816_1(?) }");
    call.setNull(1, Types.INTEGER);
    call.registerOutParameter(1, Types.INTEGER);
    call.execute();
    assertEquals(0, call.getInt(1));
    assertTrue(call.wasNull());

    call = this.conn.prepareCall("{ call test60816_3(?) }");
    call.setNull(1, Types.INTEGER);
    call.registerOutParameter(1, Types.INTEGER);
    call.execute();
    assertEquals(10, call.getInt(1));
}
 

@Override
public final void bind(CallableStatement st, J value, String name, WrapperOptions options) throws SQLException {
	final boolean traceEnabled = log.isTraceEnabled();
	if ( value == null ) {
		if ( traceEnabled ) {
			log.trace(
					String.format(
							NULL_BIND_MSG_TEMPLATE,
							name,
							JdbcTypeNameMapper.getTypeName( getSqlDescriptor().getSqlType() )
					)
			);
		}
		st.setNull( name, sqlDescriptor.getSqlType() );
	}
	else {
		if ( traceEnabled ) {
			log.trace(
					String.format(
							BIND_MSG_TEMPLATE,
							name,
							JdbcTypeNameMapper.getTypeName( sqlDescriptor.getSqlType() ),
							getJavaDescriptor().extractLoggableRepresentation( value )
					)
			);
		}
		doBind( st, value, name, options );
	}
}
 

public void _testPRLRUOffHeapPercDestroy() throws Exception {
  Properties extra = new Properties();
  extra.put("gemfire.off-heap-memory-size","128M");
  startVMs(1, 1, 0, null, extra);
  
  clientSQLExecute(1, "create table trade.bigcustomers (cid int not null, cust_addr clob) offheap " +
              "EVICTION BY LRUHEAPPERCENT EVICTACTION destroy");
  Connection conn = TestUtil.getConnection();
  CallableStatement cs = conn.prepareCall("call sys.set_critical_offheap_percentage_sg(?, ?)");
  cs.setInt(1, 90);
  cs.setNull(2, Types.VARCHAR);
  cs.execute();
  cs = conn.prepareCall("call sys.set_eviction_offheap_percentage_sg(?, ?)");
  cs.setInt(1, 25);
  cs.setNull(2, Types.VARCHAR);
  cs.execute();
  float evictionOffHeapPercentage = Misc.getGemFireCache().getResourceManager().getEvictionOffHeapPercentage();
  assertEquals(Float.valueOf(25), evictionOffHeapPercentage);
  VM servervm = this.serverVMs.get(0);
  servervm.invoke(GfxdOffHeapLRUDUnit.class, "assertOffHeapPercentage", new Object[] {Float.valueOf(evictionOffHeapPercentage)});
  PreparedStatement ps = conn.prepareStatement("insert into trade.bigcustomers values(?, ?)");
  
  insertNBigElements2(200, ps, 0);
  Statement s = conn.createStatement();
  
  Thread.sleep(10000);
  s.execute("select count(*) from trade.bigcustomers");
  ResultSet rs = s.getResultSet();
  int cnt = 0;
  if (rs.next()) {
    cnt = rs.getInt(1);
  }
  TestUtil.getLogger().info("cnt: "+cnt);
  assertTrue(cnt < 200);
}
 

protected void setHeapPercentage(double percentage)
throws SQLException {
  String sql = "call SYS.SET_EVICTION_HEAP_PERCENTAGE_SG(?,?)";
  CallableStatement cs = this.connection.prepareCall(sql);
  Log.getLogWriter().info("CALL SYS.SET_EVICTION_HEAP_PERCENTAGE_SG(" + percentage + ", null)");
  cs.setDouble(1, percentage);
  cs.setNull(2, Types.VARCHAR);
  cs.executeUpdate();
}
 

@Override
public void transactionMarker() throws Exception {
    CallableStatement callableStatement =
            connection.prepareCall("insert into employee (name, misc) values (?, ?)");
    try {
        callableStatement.setString(1, "jane");
        callableStatement.setNull(2, Types.BINARY);
        callableStatement.execute();
    } finally {
        callableStatement.close();
    }
}
 

/**
 * Private helper for {@link #testTimeAndDateWithCalendar()}. This method
 * calls a procedure that takes Date, Time and Timestamp arguments and
 * returns the exact same values. Call the setters with one calendar and
 * the getters with another calendar, and verify that the expected
 * conversion between time zones has happened.
 *
 * @param cal1 the calendar to use for the setter methods
 * @param cal2 the calendar to use for the getter methods
 */
private void testTimeAndDateWithCalendar(Calendar cal1, Calendar cal2)
        throws SQLException
{
    println("Running " + getName() + "() with " +
            cal1.getTimeZone().getDisplayName() + " and " +
            cal2.getTimeZone().getDisplayName());

    CallableStatement cs = prepareCall(
            "call NON_NUMERIC_TYPES_IN_AND_OUT_PROC(?,?,?,?,?,?,?,?)");

    Date d = Date.valueOf("2010-04-14");
    Time t = Time.valueOf("12:23:24");
    Timestamp ts = new Timestamp(System.currentTimeMillis());
    ts.setNanos(123456789);

    cs.setDate(1, d, cal1);
    cs.setTime(2, t, cal1);
    cs.setTimestamp(3, ts, cal1);
    cs.setNull(4, Types.VARBINARY); // we don't care about VARBINARY here

    cs.registerOutParameter (5, java.sql.Types.DATE);
    cs.registerOutParameter (6, java.sql.Types.TIME);
    cs.registerOutParameter (7, java.sql.Types.TIMESTAMP);
    cs.registerOutParameter (8, java.sql.Types.VARBINARY);

    cs.execute();

    assertSameDate(d, cal1, cs.getDate(5, cal2), cal2);
    assertSameTime(t, cal1, cs.getTime(6, cal2), cal2);
    assertSameTimestamp(ts, cal1, cs.getTimestamp(7, cal2), cal2);
}
 

/**
 * Tests fix for BUG#60816 - Cannot pass NULL to an INOUT procedure parameter
 * 
 * @throws Exception
 */
public void testBug60816() throws Exception {
    createProcedure("test60816_1", "(INOUT x INTEGER)\nBEGIN\nSET x = x + 1;\nEND");
    createProcedure("test60816_2", "(x INTEGER, OUT y INTEGER)\nBEGIN\nSET y = x + 1;\nEND");
    createProcedure("test60816_3", "(INOUT x INTEGER)\nBEGIN\nSET x = 10;\nEND");

    CallableStatement call = this.conn.prepareCall("{ call test60816_1(?) }");
    call.setInt(1, 1);
    call.registerOutParameter(1, Types.INTEGER);
    call.execute();
    assertEquals(2, call.getInt(1));

    call = this.conn.prepareCall("{ call test60816_2(?, ?) }");
    call.setInt(1, 1);
    call.registerOutParameter(2, Types.INTEGER);
    call.execute();
    assertEquals(2, call.getInt(2));

    call = this.conn.prepareCall("{ call test60816_2(?, ?) }");
    call.setNull(1, Types.INTEGER);
    call.registerOutParameter(2, Types.INTEGER);
    call.execute();
    assertEquals(0, call.getInt(2));
    assertTrue(call.wasNull());

    call = this.conn.prepareCall("{ call test60816_1(?) }");
    call.setNull(1, Types.INTEGER);
    call.registerOutParameter(1, Types.INTEGER);
    call.execute();
    assertEquals(0, call.getInt(1));
    assertTrue(call.wasNull());

    call = this.conn.prepareCall("{ call test60816_3(?) }");
    call.setNull(1, Types.INTEGER);
    call.registerOutParameter(1, Types.INTEGER);
    call.execute();
    assertEquals(10, call.getInt(1));

}
 

public void _testPRLRUOffHeapPercDestroy() throws Exception {
  Properties extra = new Properties();
  extra.put("gemfire.off-heap-memory-size","128M");
  startVMs(1, 1, 0, null, extra);
  
  clientSQLExecute(1, "create table trade.bigcustomers (cid int not null, cust_addr clob) offheap " +
              "EVICTION BY LRUHEAPPERCENT EVICTACTION destroy");
  Connection conn = TestUtil.getConnection();
  CallableStatement cs = conn.prepareCall("call sys.set_critical_offheap_percentage_sg(?, ?)");
  cs.setInt(1, 90);
  cs.setNull(2, Types.VARCHAR);
  cs.execute();
  cs = conn.prepareCall("call sys.set_eviction_offheap_percentage_sg(?, ?)");
  cs.setInt(1, 25);
  cs.setNull(2, Types.VARCHAR);
  cs.execute();
  float evictionOffHeapPercentage = Misc.getGemFireCache().getResourceManager().getEvictionOffHeapPercentage();
  assertEquals(Float.valueOf(25), evictionOffHeapPercentage);
  VM servervm = this.serverVMs.get(0);
  servervm.invoke(GfxdOffHeapLRUDUnit.class, "assertOffHeapPercentage", new Object[] {Float.valueOf(evictionOffHeapPercentage)});
  PreparedStatement ps = conn.prepareStatement("insert into trade.bigcustomers values(?, ?)");
  
  insertNBigElements2(200, ps, 0);
  Statement s = conn.createStatement();
  
  Thread.sleep(10000);
  s.execute("select count(*) from trade.bigcustomers");
  ResultSet rs = s.getResultSet();
  int cnt = 0;
  if (rs.next()) {
    cnt = rs.getInt(1);
  }
  TestUtil.getLogger().info("cnt: "+cnt);
  assertTrue(cnt < 200);
}
 

protected void setHeapPercentage(double percentage)
throws SQLException {
  String sql = "call SYS.SET_EVICTION_HEAP_PERCENTAGE_SG(?,?)";
  CallableStatement cs = this.connection.prepareCall(sql);
  Log.getLogWriter().info("CALL SYS.SET_EVICTION_HEAP_PERCENTAGE_SG(" + percentage + ", null)");
  cs.setDouble(1, percentage);
  cs.setNull(2, Types.VARCHAR);
  cs.executeUpdate();
}
 

/**
 * Test for reature request [956800] setNull(): Not implemented.
 */
public void testCallableSetNull1()
   throws Exception
{
   dropProcedure( "procCallableSetNull1" );
   dropTable( "callablesetnull1" );

   Statement stmt = con.createStatement();
   stmt.execute( "CREATE TABLE callablesetnull1 (data CHAR(1) NULL)" );
   stmt.close();

   stmt = con.createStatement();
   stmt.execute( "create procedure procCallableSetNull1 @data char(1) as INSERT INTO callablesetnull1 (data) VALUES (@data)" );
   stmt.close();

   CallableStatement cstmt = con.prepareCall( "{call procCallableSetNull1(?)}" );
   // Test CallableStatement.setNull(int,Types.NULL)
   cstmt.setNull( 1, Types.NULL );
   cstmt.execute();
   cstmt.close();

   stmt = con.createStatement();
   ResultSet rs = stmt.executeQuery( "SELECT data FROM callablesetnull1" );

   assertTrue( rs.next() );

   // Test ResultSet.getString()
   assertNull( rs.getString( 1 ) );
   assertTrue( rs.wasNull() );

   assertTrue( !rs.next() );
   stmt.close();
   rs.close();
}
 

public void fillStatement(CallableStatement statement, JsonArray in, JsonArray out) throws SQLException {
  if (in == null) {
    in = EMPTY;
  }

  if (out == null) {
    out = EMPTY;
  }

  int max = Math.max(in.size(), out.size());

  for (int i = 0; i < max; i++) {
    Object value = null;
    boolean set = false;

    if (i < in.size()) {
      value = in.getValue(i);
    }

    // found a in value, use it as a input parameter
    if (value != null) {
      if (value instanceof String) {
        statement.setObject(i + 1, optimisticCast((String) value));
      } else {
        statement.setObject(i + 1, value);
      }
      set = true;
    }

    // reset
    value = null;

    if (i < out.size()) {
      value = out.getValue(i);
    }

    // found a out value, use it as a output parameter
    if (value != null) {
      // We're using the int from the enum instead of the enum itself to allow working with Drivers
      // that have not been upgraded to Java8 yet.
      if (value instanceof String) {
        statement.registerOutParameter(i + 1, JDBCType.valueOf((String) value).getVendorTypeNumber());
      } else if (value instanceof Number) {
        // for cases where vendors have special codes (e.g.: Oracle)
        statement.registerOutParameter(i + 1, ((Number) value).intValue());
      }
      set = true;
    }

    if (!set) {
      // assume null input
      statement.setNull(i + 1, Types.NULL);
    }
  }
}
 

protected void addGateway() {
  //setting gateway queue
  Connection conn = getGFEConnection();    
  boolean enableQueuePersistence = (Boolean)bb.getSharedMap().get("enableQueuePersistence");
  Log.getLogWriter().info("adding Gateway");
 
  for (int i=1; i<= numOfWanSites; i++) {
    if (i != myWanSite) {
      try {
        CallableStatement cs1 = conn.prepareCall("CALL SYS.ADD_GATEWAY(?, ?)");
        CallableStatement cs2 = conn.prepareCall("CALL SYS.CONFIGURE_GATEWAY_QUEUE(?,?,?,?,?,?,?,?,?)");

        cs1.setString(1, "hub_" + myWanSite);
        cs1.setString(2, gatewayID+i);
        cs1.execute();
        commit(conn);
        Log.getLogWriter().info("gateway " + gatewayID+i + " is created for hub_" + myWanSite);
             
        cs2.setString(1, "hub_" + myWanSite);
        cs2.setString(2, gatewayID+i);
        cs2.setNull(3,Types.INTEGER); //BATCH_SIZE
        cs2.setNull(4,Types.INTEGER); //BATCH_TIME_INTERVAL
        cs2.setBoolean(5,enableQueueConflation);  //BATCH_CONFLATION, default to false -- can be used to check listener invocation
        cs2.setNull(6, Types.INTEGER);  //MAXIMUM_QUEUE_MEMORY in megabytes
        if (random.nextBoolean()) {
          cs2.setNull(7, Types.VARCHAR); //DIR_PATH
          Log.getLogWriter().info("no specific diskstore provided");
        }
        else {
          cs2.setString(7, diskStore);
        }
        cs2.setBoolean(8, enableQueuePersistence);  //ENABLE_PERSISTENCE   
        cs2.setNull(9, Types.INTEGER); //ALTRERT_THRESHOLD default is 0
        cs2.execute();          
        commit(conn);
        Log.getLogWriter().info("Gateway and gateway queue are added");
      } catch (SQLException se) {
       SQLHelper.handleSQLException(se);
      }
    }
  }
}
 

@SuppressWarnings("unchecked")
protected void createGatewayHub(Connection conn, String hubID, 
    String hostname, String sg, int portIndex) {
  int hubPort = -1;
  int[] freeTCPPorts = null;
  try {
          
    CallableStatement cs = conn
        .prepareCall("CALL SYS.ADD_GATEWAY_HUB(?,?, ?,?,?,?,?)");
    if (wanBB.getSharedCounters().incrementAndRead(SQLWanBB.synchWanEndPorts) ==1) {
      //only one thread will add wan ports to bb
      freeTCPPorts = AvailablePortHelper.getRandomAvailableTCPPorts(numOfWanSites * numOfDataStoresPerSite);
      SQLWanBB.getBB().getSharedMap().put("wanEndPoints", freeTCPPorts);
      StringBuilder ports = new StringBuilder();
      for (int port: freeTCPPorts) {
        ports.append(port + ", ");
      }
      Log.getLogWriter().info("Free TCP ports are :" + ports.toString());
    } 
    freeTCPPorts = (int[]) SQLWanBB.getBB().getSharedMap().get("wanEndPoints");
    while (freeTCPPorts == null){
      MasterController.sleepForMs(300);
      freeTCPPorts = (int[]) SQLWanBB.getBB().getSharedMap().get("wanEndPoints");
    }
    hubPort = freeTCPPorts[(myWanSite - 1) * numOfDataStoresPerSite +  (portIndex-1)];  
    //myWanSite starts from 1, each data store (different SG) in the site 
    //portIndex starts from 1
    
    //cs.setString(1, serverGroup.toString());
    cs.setString(1, sg);//
    cs.setString(2, hubID);
    cs.setInt(3, hubPort);
    cs.setNull(4, Types.INTEGER);
    cs.setNull(5, Types.INTEGER);
    
    int startupPolicy = random.nextInt(3);
    if (startupPolicy ==0) cs.setNull(6, Types.VARCHAR);
    else if (startupPolicy == 1) cs.setString(6, "primary");
    else {
      cs.setString(6, "secondary");
      Log.getLogWriter().info("trying to become secondaary");
    }
    
    //manual start
    if (random.nextBoolean()) cs.setNull(7, Types.BOOLEAN);
    else {
      cs.setBoolean(7, true);
      Log.getLogWriter().info("Need to manually start hub");
    }
    cs.execute();
  } catch (SQLException se) {
    SQLHelper.handleSQLException(se);
  }
  
  ArrayList<Integer> hubPorts = (ArrayList<Integer>) bb.getSharedMap().get("portID_wanSite"+myWanSite);
  if (hubPorts == null) hubPorts = new ArrayList<Integer>();
  hubPorts.add(hubPort);
  bb.getSharedMap().put("portID_wanSite"+myWanSite, hubPorts); //setup port number
  bb.getSharedMap().put("hostname_wanSite"+myWanSite, hostname);  //for multi hosts
  Log.getLogWriter().info("my wan site is " + myWanSite);
  //Log.getLogWriter().info("server group that hub is created on is " + serverGroup.toString());
  Log.getLogWriter().info("server group that hub is created on is " + sg);
  Log.getLogWriter().info("the hub created on port " + hubPort);
}
 

public void testPRLRUHeapPercDestroy() throws Exception {
  // The test is valid only for transaction isolation level NONE. 
  if (isTransactional) {
    return;
  }

  startVMs(1, 1);
  clientSQLExecute(1, "create table trade.bigcustomers (cid int not null, cust_addr clob) " +
  		"EVICTION BY LRUHEAPPERCENT EVICTACTION destroy");
  Connection conn = TestUtil.getConnection();
  CallableStatement cs = conn.prepareCall("call sys.set_critical_heap_percentage_sg(?, ?)");
  cs.setInt(1, 90);
  cs.setNull(2, Types.VARCHAR);
  cs.execute();
  cs = conn.prepareCall("call sys.set_eviction_heap_percentage_sg(?, ?)");
  cs.setInt(1, 25);
  cs.setNull(2, Types.VARCHAR);
  cs.execute();
  float evictionHeapPercentage = Misc.getGemFireCache().getResourceManager().getEvictionHeapPercentage();
  assertEquals(Float.valueOf(25), evictionHeapPercentage);
  VM servervm = this.serverVMs.get(0);
  servervm.invoke(GfxdLRUDUnit.class, "assertHeapPercentage", new Object[] {Float.valueOf(evictionHeapPercentage)});
  PreparedStatement ps = conn.prepareStatement("insert into trade.bigcustomers values(?, ?)");
  
  insertNBigElements2(200, ps, 0);
  final Statement s = conn.createStatement();

  servervm.invoke(GfxdLRUDUnit.class, "logVMHeapSizeAndCurrentHeapSize");
  final WaitCriterion waitCond = new WaitCriterion() {
    @Override
    public boolean done() {
      try {
        s.execute("select count(*) from trade.bigcustomers");
        ResultSet rs = s.getResultSet();
        int cnt = 0;
        if (rs.next()) {
          cnt = rs.getInt(1);
        }
        TestUtil.getLogger().info("cnt: " + cnt);
        return (cnt < 200);
      } catch (SQLException sqle) {
        fail("unexpected exception " + sqle, sqle);
        return false;
      }
    }

    @Override
    public String description() {
      return "waiting for LRU destroy";
    }
  };
  waitForCriterion(waitCond, 60000, 500, true);
}
 

/**
 * Creates a new type instance in the database.
 *
 * @param connection The connection used to access the database.
 * @param moduleId The id of the module the type instance is associated to.
 * @param name The name of the type instance.
 * @param commentId The id of the comment associated with the type instance.
 * @param typeId The id of the type which is the base type for this type instance.
 * @param sectionId The id of the section where this type instance is located in.
 * @param sectionOffset The offset in the section at which the type instance starts.
 *
 * @return The id of the generated type instance in the database.
 *
 * @throws CouldntSaveDataException if the type instance could not be generated in the database.
 */
public static int createTypeInstance(final Connection connection,
    final int moduleId,
    final String name,
    final Integer commentId,
    final int typeId,
    final int sectionId,
    final long sectionOffset) throws CouldntSaveDataException {

  Preconditions.checkNotNull(connection, "Error: connection argument can not be null");
  Preconditions.checkArgument(moduleId > 0, "Error: module id must be greater than zero");
  Preconditions.checkNotNull(name, "Error: name argument can not be null");
  Preconditions.checkArgument(typeId >= 0, "Error: type id must be greater than zero");
  Preconditions.checkArgument(sectionId >= 0, "Error: section id must be larger than zero");
  Preconditions.checkArgument(sectionOffset >= 0,
      "Error: section offset must be larger or equal to zero");

  try {
    final String query = " { ? = call create_type_instance(?, ?, ?, ?, ?, ?) } ";
    final CallableStatement procedure = connection.prepareCall(query);
    try {
      procedure.registerOutParameter(1, Types.INTEGER);
      procedure.setInt(2, moduleId);
      procedure.setString(3, name);
      if (commentId == null) {
        procedure.setNull(4, Types.INTEGER);
      } else {
        procedure.setInt(4, commentId);
      }
      procedure.setInt(5, typeId);
      procedure.setInt(6, sectionId);
      procedure.setLong(7, sectionOffset);
      procedure.execute();

      final int typeInstanceId = procedure.getInt(1);
      if (procedure.wasNull()) {
        throw new CouldntSaveDataException(
            "Error: the type instance id returned from the database was null");
      }
      return typeInstanceId;
    } finally {
      procedure.close();
    }
  } catch (final SQLException exception) {
    throw new CouldntSaveDataException(exception);
  }
}
 

@SuppressWarnings("unchecked")
protected void createGatewayHub(Connection conn, String hubID, 
    String hostname, String sg, int portIndex) {
  int hubPort = -1;
  int[] freeTCPPorts = null;
  try {
          
    CallableStatement cs = conn
        .prepareCall("CALL SYS.ADD_GATEWAY_HUB(?,?, ?,?,?,?,?)");
    if (wanBB.getSharedCounters().incrementAndRead(SQLWanBB.synchWanEndPorts) ==1) {
      //only one thread will add wan ports to bb
      freeTCPPorts = AvailablePortHelper.getRandomAvailableTCPPorts(numOfWanSites * numOfDataStoresPerSite);
      SQLWanBB.getBB().getSharedMap().put("wanEndPoints", freeTCPPorts);
      StringBuilder ports = new StringBuilder();
      for (int port: freeTCPPorts) {
        ports.append(port + ", ");
      }
      Log.getLogWriter().info("Free TCP ports are :" + ports.toString());
    } 
    freeTCPPorts = (int[]) SQLWanBB.getBB().getSharedMap().get("wanEndPoints");
    while (freeTCPPorts == null){
      MasterController.sleepForMs(300);
      freeTCPPorts = (int[]) SQLWanBB.getBB().getSharedMap().get("wanEndPoints");
    }
    hubPort = freeTCPPorts[(myWanSite - 1) * numOfDataStoresPerSite +  (portIndex-1)];  
    //myWanSite starts from 1, each data store (different SG) in the site 
    //portIndex starts from 1
    
    //cs.setString(1, serverGroup.toString());
    cs.setString(1, sg);//
    cs.setString(2, hubID);
    cs.setInt(3, hubPort);
    cs.setNull(4, Types.INTEGER);
    cs.setNull(5, Types.INTEGER);
    
    int startupPolicy = random.nextInt(3);
    if (startupPolicy ==0) cs.setNull(6, Types.VARCHAR);
    else if (startupPolicy == 1) cs.setString(6, "primary");
    else {
      cs.setString(6, "secondary");
      Log.getLogWriter().info("trying to become secondaary");
    }
    
    //manual start
    if (random.nextBoolean()) cs.setNull(7, Types.BOOLEAN);
    else {
      cs.setBoolean(7, true);
      Log.getLogWriter().info("Need to manually start hub");
    }
    cs.execute();
  } catch (SQLException se) {
    SQLHelper.handleSQLException(se);
  }
  
  ArrayList<Integer> hubPorts = (ArrayList<Integer>) bb.getSharedMap().get("portID_wanSite"+myWanSite);
  if (hubPorts == null) hubPorts = new ArrayList<Integer>();
  hubPorts.add(hubPort);
  bb.getSharedMap().put("portID_wanSite"+myWanSite, hubPorts); //setup port number
  bb.getSharedMap().put("hostname_wanSite"+myWanSite, hostname);  //for multi hosts
  Log.getLogWriter().info("my wan site is " + myWanSite);
  //Log.getLogWriter().info("server group that hub is created on is " + serverGroup.toString());
  Log.getLogWriter().info("server group that hub is created on is " + sg);
  Log.getLogWriter().info("the hub created on port " + hubPort);
}
 

public void testPRLRUHeapPercDestroy() throws Exception {
  // The test is valid only for transaction isolation level NONE. 
  if (isTransactional) {
    return;
  }

  startVMs(1, 1);
  clientSQLExecute(1, "create table trade.bigcustomers (cid int not null, cust_addr clob) " +
  		"EVICTION BY LRUHEAPPERCENT EVICTACTION destroy");
  Connection conn = TestUtil.getConnection();
  CallableStatement cs = conn.prepareCall("call sys.set_critical_heap_percentage_sg(?, ?)");
  cs.setInt(1, 90);
  cs.setNull(2, Types.VARCHAR);
  cs.execute();
  cs = conn.prepareCall("call sys.set_eviction_heap_percentage_sg(?, ?)");
  cs.setInt(1, 25);
  cs.setNull(2, Types.VARCHAR);
  cs.execute();
  float evictionHeapPercentage = Misc.getGemFireCache().getResourceManager().getEvictionHeapPercentage();
  assertEquals(Float.valueOf(25), evictionHeapPercentage);
  VM servervm = this.serverVMs.get(0);
  servervm.invoke(GfxdLRUDUnit.class, "assertHeapPercentage", new Object[] {Float.valueOf(evictionHeapPercentage)});
  PreparedStatement ps = conn.prepareStatement("insert into trade.bigcustomers values(?, ?)");
  
  insertNBigElements2(200, ps, 0);
  final Statement s = conn.createStatement();

  servervm.invoke(GfxdLRUDUnit.class, "logVMHeapSizeAndCurrentHeapSize");
  final WaitCriterion waitCond = new WaitCriterion() {
    @Override
    public boolean done() {
      try {
        s.execute("select count(*) from trade.bigcustomers");
        ResultSet rs = s.getResultSet();
        int cnt = 0;
        if (rs.next()) {
          cnt = rs.getInt(1);
        }
        TestUtil.getLogger().info("cnt: " + cnt);
        return (cnt < 200);
      } catch (SQLException sqle) {
        fail("unexpected exception " + sqle, sqle);
        return false;
      }
    }

    @Override
    public String description() {
      return "waiting for LRU destroy";
    }
  };
  waitForCriterion(waitCond, 60000, 500, true);
}