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

@Nullable
private static String lookupDefaultSchema(DatabaseMetaData databaseMetaData) {
	try {
		CallableStatement cstmt = null;
		try {
			Connection con = databaseMetaData.getConnection();
			if (con == null) {
				logger.debug("Cannot check default schema - no Connection from DatabaseMetaData");
				return null;
			}
			cstmt = con.prepareCall("{? = call sys_context('USERENV', 'CURRENT_SCHEMA')}");
			cstmt.registerOutParameter(1, Types.VARCHAR);
			cstmt.execute();
			return cstmt.getString(1);
		}
		finally {
			if (cstmt != null) {
				cstmt.close();
			}
		}
	}
	catch (SQLException ex) {
		logger.debug("Exception encountered during default schema lookup", ex);
		return null;
	}
}
 

public void testThree() throws SQLException {

        Connection conn = newConnection();
        Statement  st   = conn.createStatement();

        st.execute("declare varone int default 0;");
        st.execute(
            "create procedure proc_inout_result (inout intp int) "
            + " language java reads sql data external name 'CLASSPATH:org.hsqldb.test.TestStoredProcedure.procWithResultOne'");

        CallableStatement cs =
            conn.prepareCall("call proc_inout_result(varone)");
        boolean isResult = cs.execute();

        assertFalse(isResult);
        cs.getMoreResults();

        ResultSet rs = cs.getResultSet();

        rs.next();
        assertEquals(rs.getString(1), "SYSTEM_LOBS");
        assertEquals(rs.getString(2), "LOB_IDS");
        rs.close();
    }
 

protected void sendRequest(Document request) throws IOException {
    try {
        StringWriter writer = new StringWriter();
        (new XMLWriter(writer,OutputFormat.createPrettyPrint())).write(request);
        writer.flush(); writer.close();
        SessionImplementor session = (SessionImplementor)new _RootDAO().getSession();
        Connection connection = session.getJdbcConnectionAccess().obtainConnection();
        try {
            CallableStatement call = connection.prepareCall(iRequestSql);
            call.setString(1, writer.getBuffer().toString());
            call.execute();
            call.close();
        } finally {
        	session.getJdbcConnectionAccess().releaseConnection(connection);
        }
    } catch (Exception e) {
        sLog.error("Unable to send request: "+e.getMessage(),e);
    } finally {
        _RootDAO.closeCurrentThreadSessions();
    }
}
 

protected void createListener(Connection conn, String tableName) throws SQLException {
	String[] str = tableName.split("\\.");
	String schema = str[0];
	String table = str[1];
	String tableListener = table.substring(0, 1).toUpperCase() + table.substring(1) + "Listener";
CallableStatement cs = conn.prepareCall("CALL SYS.ADD_LISTENER(?,?,?,?,?,?)");
cs.setString(1, tableListener);
cs.setString(2, schema);
cs.setString(3, table);
cs.setString(4, "sql.sqlCallback.listener." + tableListener);
cs.setString(5, backendDB_url);
cs.setString(6, "");
cs.execute();  	
	Log.getLogWriter().info("add the listener " + tableListener 
			+ " for " + tableName);
}
 

/**
 * call SYSCS_UTIL.INPLACE_COMPRESS_TABLE() system procedure.
 * <p>
 * Utility test function to call the system procedure.
 *
 **/
protected void callCompress(
Connection  conn,
String      schemaName,
String      tableName,
boolean     purgeRows,
boolean     defragmentRows,
boolean     truncateEnd,
boolean     commit_operation)
    throws SQLException
{
    CallableStatement cstmt = 
        conn.prepareCall(
            "call SYSCS_UTIL.INPLACE_COMPRESS_TABLE(?, ?, ?, ?, ?)");
    cstmt.setString(1, schemaName);
    cstmt.setString(2, tableName);
    cstmt.setInt   (3, purgeRows      ? 1 : 0);
    cstmt.setInt   (4, defragmentRows ? 1 : 0);
    cstmt.setInt   (5, truncateEnd    ? 1 : 0);

    cstmt.execute();

    if (commit_operation)
        conn.commit();
}
 

public static Runnable stopAsyncEventListener(final String id) {

    CacheSerializableRunnable stopWBCL = new CacheSerializableRunnable(
        "Start WBCL") {
      @Override
      public void run2() throws CacheException {
        try {
          Connection conn = TestUtil.jdbcConn;
          CallableStatement cs = conn
              .prepareCall("call SYS.STOP_ASYNC_EVENT_LISTENER (?)");
          cs.setString(1, id);
          cs.execute();
        }
        catch (SQLException sqle) {
          throw GemFireXDRuntimeException.newRuntimeException(null, sqle);
        }
      }
    };
    return stopWBCL;
  }
 

protected Document receiveResponse() throws IOException, DocumentException {
    try {
        SessionImplementor session = (SessionImplementor)new _RootDAO().getSession();
        Connection connection = session.getJdbcConnectionAccess().obtainConnection();
        String response = null;
        try {
            CallableStatement call = connection.prepareCall(iResponseSql);
            call.registerOutParameter(1, java.sql.Types.CLOB);
            call.execute();
            response = call.getString(1);
            call.close();
        } finally {
        	session.getJdbcConnectionAccess().releaseConnection(connection);
        }
        if (response==null || response.length()==0) return null;
        StringReader reader = new StringReader(response);
        Document document = (new SAXReader()).read(reader);
        reader.close();
        return document;
    } catch (Exception e) {
        sLog.error("Unable to receive response: "+e.getMessage(),e);
        return null;
    } finally {
        _RootDAO.closeCurrentThreadSessions();
    }
}
 

public static RuntimeStatisticsParser executeAndGetRuntimeStatistics(Connection conn, String sql ) throws SQLException
{
    Statement s = conn.createStatement();
    Statement s2 = conn.createStatement();
    CallableStatement cs = conn.prepareCall("call SYSCS_UTIL.SET_RUNTIMESTATISTICS(1)");
    cs.execute();
    cs.close();
    s.execute(sql);
    ResultSet rs = s.getResultSet();
    if (rs != null)
        JDBC.assertDrainResults(rs);
    RuntimeStatisticsParser parser = getRuntimeStatisticsParser(s2);
    s.close();
    s2.close();
    return parser;
}
 

/**
 * Method for a Java procedure that calls another procedure
 * and just passes on the dynamic results from that call.
 */
public static void nestedDynamicResultSets(String procedureText,
        ResultSet[] rs1, ResultSet[] rs2, ResultSet[] rs3, ResultSet[] rs4,
        ResultSet[] rs5, ResultSet[] rs6)
throws SQLException
{
    Connection c = DriverManager.getConnection("jdbc:default:connection");

    CallableStatement cs = c.prepareCall("CALL " + procedureText);

    cs.execute();

    // Mix up the order of the result sets in the returned
    // parameters, ensures order is defined by creation
    // and not parameter order.
    rs6[0] = cs.getResultSet();
    if (!cs.getMoreResults(Statement.KEEP_CURRENT_RESULT))
        return;
    rs3[0] = cs.getResultSet();
    if (!cs.getMoreResults(Statement.KEEP_CURRENT_RESULT))
        return;
    rs4[0] = cs.getResultSet();
    if (!cs.getMoreResults(Statement.KEEP_CURRENT_RESULT))
        return;
    rs2[0] = cs.getResultSet();
    if (!cs.getMoreResults(Statement.KEEP_CURRENT_RESULT))
        return;
    rs1[0] = cs.getResultSet();
    if (!cs.getMoreResults(Statement.KEEP_CURRENT_RESULT))
        return;
    rs5[0] = cs.getResultSet();

}
 

@Override
public void run(IDatabaseTranslator translator, Connection c) {
    final String proc = DataDefinitionUtil.getQualifiedName(schemaName, "ADD_RESOURCE_TYPE");
    final String sql = "CALL " + proc + "(?, ?)";

    try (CallableStatement cs = c.prepareCall(sql)) {
        cs.setString(1, resourceType);
        cs.registerOutParameter(2, Types.INTEGER);
        cs.execute();
        this.resourceTypeId = cs.getInt(2);
    } catch (SQLException x) {
        throw translator.translate(x);
    }

}
 

public static void addListener(String listenerID, String schemaName,
    String tableName, String functionStr, String initInfoStr, String serverGroups) throws SQLException {
  Connection conn = getConnection();
  CallableStatement cs = conn
      .prepareCall("CALL SYS.ADD_LISTENER(?,?,?,?,?,?)");
  cs.setString(1, listenerID);
  cs.setString(2, schemaName);
  cs.setString(3, tableName);
  cs.setString(4, functionStr);
  cs.setString(5, initInfoStr);
  cs.setString(6, serverGroups);
  cs.execute();
}
 

protected void createWriter(Connection conn, String tableName) throws SQLException {
	String[] str = tableName.split("\\.");
	String schema = str[0];
	String table = str[1];
	String tableWriter = table.substring(0, 1).toUpperCase() + table.substring(1) + "Writer";
CallableStatement cs = conn.prepareCall("CALL SYS.ATTACH_WRITER(?,?,?,?,?)");
cs.setString(1, schema);
cs.setString(2, table);
cs.setString(3, "sql.sqlCallback.writer." + tableWriter);
cs.setString(4, backendDB_url);
cs.setString(5, "");
cs.execute();  	
	Log.getLogWriter().info("attach the writer " + tableWriter 
			+ " for " + tableName);
}
 

public void doRestoreandReEncrypt() throws SQLException {
	log("--------------------- R E S T O R E   S E C T I O N  B E G I N ------------------------");
	String dbType = "RESTORED";
	Connection conn = null;

	long newKey = System.currentTimeMillis();
	String restoreDbURL = NsTest.getDriverURL() + NsTest.RESTOREDIR
			+ File.separator + NsTest.dbName;
	String dbUrl = restoreDbURL + ";" + NsTest.bootPwd + ";restoreFrom="
			+ NsTest.BACKUPDIR + File.separator + NsTest.dbName;
	try {
		conn = DriverManager.getConnection(dbUrl);
		log(getTimestamp() + " Database restored successfully " + dbUrl);
	} catch (SQLException e) {
		log(getTimestamp() + " FAILURE ! to restore database " + dbUrl);
		e.printStackTrace(logger);

	}

	// Consistency check not required everytime
	conn.close();
	dbUrl = restoreDbURL + ";" + NsTest.bootPwd;
	doConsistCheck(dbUrl, dbType);
	// DERBY-1737, hence create a new connection
	log("--------------------- R E S T O R E   S E C T I O N  E N D ----------------------------");

	conn = DriverManager.getConnection(dbUrl);
	// Disable log archival
	// call SYSCS_UTIL.DISABLE_LOG_ARCHIVE_MODE(1);
	CallableStatement cs = conn
			.prepareCall("CALL SYSCS_UTIL.DISABLE_LOG_ARCHIVE_MODE(?)");
	cs.setInt(1, 1);
	cs.execute();
	conn.close();
	log(getTimestamp()
			+ " Disable log archival mode to enable re-encryption " + dbUrl);
	shutDownDB(restoreDbURL, dbType);
	log("--------------------- ENCRYPT AND RECONNECT  S E C T I O N  BEGIN ------------------------");
	String encryptDbURL = restoreDbURL + ";dataEncryption=true;";

	// Try Re-Encrypting Now
	encryptDbURL += ";" + NsTest.bootPwd + ";newBootPassword=" + newKey;

	long start = System.currentTimeMillis();
	log(getTimestamp() + " Encrypting database, url = " + encryptDbURL);
	conn = DriverManager.getConnection(encryptDbURL);
	conn.close();
	long end = System.currentTimeMillis();
	log(getTimestamp()
			+ " Re-encryption completed on restored database in "
			+ (end - start) / 100 + " seconds, url = " + encryptDbURL);
	// Shutdown the db
	dbType = "ENCRYPTED";
	shutDownDB(restoreDbURL, dbType);
	// Attempt to connect with old key should fail
	try {
		conn = DriverManager.getConnection(dbUrl);
		log(getTimestamp()
				+ " FAILURE ! - Attempt to boot with old password/url should have failed, url ="
				+ dbUrl);
	} catch (SQLException sqe) {
		if ((sqe.getSQLState().equalsIgnoreCase("XJ040"))
				|| (sqe.getSQLState().equalsIgnoreCase("XBM06"))) {
			log(getTimestamp()
					+ " PASS - Unsuccessful attempt to boot with old password/url, "
					+ dbUrl);
		} else {
			throw sqe;
		}
	}
	/*
	 * A Shutdown is not needed, since the part gets exected only when a
	 * unsuccessful attempt is made to boot the db with an old password
	 */
	// shutDownDB(restoreDbURL, dbType);
	log("--------------------- ENCRYPT AND RECONNECT  S E C T I O N  END --------------------------");
}
 

@BackwardCompatibility
public List<Run> getRuns( int startRecord, int recordsCount, String whereClause, String sortColumn,
                          boolean ascending, int utcTimeOffset ) throws DatabaseAccessException {

    List<Run> runs = new ArrayList<Run>();

    Connection connection = getConnection();

    String sqlLog = new SqlRequestFormatter().add("start record", startRecord)
                                             .add("records", recordsCount)
                                             .add("where", whereClause)
                                             .add("sort by", sortColumn)
                                             .add("asc", ascending)
                                             .format();

    CallableStatement callableStatement = null;
    ResultSet rs = null;
    try {

        callableStatement = connection.prepareCall("{ call sp_get_runs(?, ?, ?, ?, ?) }");
        callableStatement.setString(1, String.valueOf(startRecord));
        callableStatement.setString(2, String.valueOf(recordsCount));
        callableStatement.setString(3, whereClause);
        callableStatement.setString(4, sortColumn);
        callableStatement.setString(5, (ascending
                                                  ? "ASC"
                                                  : "DESC"));

        int numberRecords = 0;
        rs = callableStatement.executeQuery();
        while (rs.next()) {
            Run run = new Run();
            run.runId = rs.getString("runId");
            run.productName = rs.getString("productName");
            run.versionName = rs.getString("versionName");
            run.buildName = rs.getString("buildName");
            run.runName = rs.getString("runName");
            run.os = rs.getString("OS");

            run.hostName = "";
            try {
                @BackwardCompatibility
                int dbInternalVersion = getDatabaseInternalVersion(); // run.hostName introduced in 3.10.0 (internalVersion = 1)

                if (dbInternalVersion >= 1) {
                    run.hostName = rs.getString("hostName");
                }

            } catch (NumberFormatException nfe) {
                run.hostName = "";
                log.getLog4jLogger().warn("Error parsing dbInternalVersion. ", nfe);
            }

            if (rs.getTimestamp("dateStart") != null) {
                run.setStartTimestamp(rs.getTimestamp("dateStart").getTime());
            }
            if (rs.getTimestamp("dateEnd") != null) {
                run.setEndTimestamp(rs.getTimestamp("dateEnd").getTime());
            }
            run.setTimeOffset(utcTimeOffset);

            run.scenariosTotal = rs.getInt("scenariosTotal");
            run.scenariosFailed = rs.getInt("scenariosFailed");
            run.scenariosSkipped = rs.getInt("scenariosSkipped");

            run.testcasesTotal = rs.getInt("testcasesTotal");
            run.testcasesFailed = rs.getInt("testcasesFailed");
            run.testcasesPassedPercent = String.valueOf(rs.getInt("testcasesPassedPercent")) + "%";
            run.testcaseIsRunning = rs.getBoolean("testcaseIsRunning");

            run.total = run.scenariosTotal + "/" + run.testcasesTotal;
            run.failed = run.scenariosFailed + "/" + run.testcasesFailed;

            run.userNote = rs.getString("userNote");
            if (run.userNote == null) {
                run.userNote = "";
            }
            runs.add(run);

            numberRecords++;
        }

        logQuerySuccess(sqlLog, "runs", numberRecords);
    } catch (Exception e) {
        throw new DatabaseAccessException("Error when " + sqlLog, e);
    } finally {
        DbUtils.closeResultSet(rs);
        DbUtils.close(connection, callableStatement);
    }

    return runs;
}
 

@Override
protected CallableStatement newPreparedStatement(Connection connection, String sql) throws SQLException {
    return connection.prepareCall(sql);
}
 

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);
}
 

public CallableStatement prepareCall(String sql) throws SQLException {
  /** get a new connection with default properties */
  Connection conn = TestUtil.getConnection();
  CallableStatement cs = conn.prepareCall(sql);   
  return cs;
}
 

public void testNanoTimerSPs() throws Exception {
  startVMs(1, 3);
  Connection conn = TestUtil.getConnection();

  ResultSet rs = conn.createStatement()
      .executeQuery("values SYS.GET_NATIVE_NANOTIMER_TYPE()");
  assertTrue(rs.next());
  String defaultTimeType = rs.getString(1);
  assertFalse(rs.next());

  setNativeNanoTimer();

  sqlExecuteVerify(new int[] { 1 }, new int[] { 1, 2, 3 },
      "values SYS.GET_IS_NATIVE_NANOTIMER()", null, "true");
  sqlExecuteVerify(new int[] { 1 }, new int[] { 1, 2, 3 },
      "values SYS.GET_NATIVE_NANOTIMER_TYPE()", null,
      "CLOCK_PROCESS_CPUTIME_ID");

  CallableStatement st = conn
      .prepareCall("CALL SYS.SET_NANOTIMER_TYPE(?, ?)");
  st.setBoolean(1, true);
  st.setString(2, "CLOCK_REALTIME");
  st.execute();

  sqlExecuteVerify(new int[] { 1 }, new int[] { 1, 2, 3 },
      "values SYS.GET_IS_NATIVE_NANOTIMER()", null, "true");
  sqlExecuteVerify(new int[] { 1 }, new int[] { 1, 2, 3 },
      "values SYS.GET_NATIVE_NANOTIMER_TYPE()", null, "CLOCK_REALTIME");

  // check reset to default
  st = conn.prepareCall("CALL SYS.SET_NANOTIMER_TYPE(?, ?)");
  st.setBoolean(1, true);
  st.setString(2, "DEFAULT");
  st.execute();

  sqlExecuteVerify(new int[] { 1 }, new int[] { 1, 2, 3 },
      "values SYS.GET_IS_NATIVE_NANOTIMER()", null, "false");
  sqlExecuteVerify(new int[] { 1 }, new int[] { 1, 2, 3 },
      "values SYS.GET_NATIVE_NANOTIMER_TYPE()", null, defaultTimeType);

  // check reset to non-native default MONOTONIC type
  st = conn.prepareCall("CALL SYS.SET_NANOTIMER_TYPE(?, ?)");
  st.setBoolean(1, false);
  st.setString(2, "CLOCK_THREAD_CPUTIME_ID");
  st.execute();

  sqlExecuteVerify(new int[] { 1 }, new int[] { 1, 2, 3 },
      "values SYS.GET_IS_NATIVE_NANOTIMER()", null, "false");
  sqlExecuteVerify(new int[] { 1 }, new int[] { 1, 2, 3 },
      "values SYS.GET_NATIVE_NANOTIMER_TYPE()", null, "CLOCK_MONOTONIC");
}
 

/**
 * Tests parsing of stored procedures
 * 
 * @throws Exception
 *             if an error occurs.
 */
public void testSPCache() throws Exception {
    if (versionMeetsMinimum(5, 0)) {

        CallableStatement storedProc = null;

        createProcedure("testSpParse", "(IN FOO VARCHAR(15))\nBEGIN\nSELECT 1;\nend\n");

        int numIterations = 10;

        long startTime = System.currentTimeMillis();

        for (int i = 0; i < numIterations; i++) {
            storedProc = this.conn.prepareCall("{call testSpParse(?)}");
            storedProc.close();
        }

        long elapsedTime = System.currentTimeMillis() - startTime;

        System.out.println("Standard parsing/execution: " + elapsedTime + " ms");

        storedProc = this.conn.prepareCall("{call testSpParse(?)}");
        storedProc.setString(1, "abc");
        this.rs = storedProc.executeQuery();

        assertTrue(this.rs.next());
        assertTrue(this.rs.getInt(1) == 1);

        Properties props = new Properties();
        props.setProperty("cacheCallableStmts", "true");

        Connection cachedSpConn = getConnectionWithProps(props);

        startTime = System.currentTimeMillis();

        for (int i = 0; i < numIterations; i++) {
            storedProc = cachedSpConn.prepareCall("{call testSpParse(?)}");
            storedProc.close();
        }

        elapsedTime = System.currentTimeMillis() - startTime;

        System.out.println("Cached parse stage: " + elapsedTime + " ms");

        storedProc = cachedSpConn.prepareCall("{call testSpParse(?)}");
        storedProc.setString(1, "abc");
        this.rs = storedProc.executeQuery();

        assertTrue(this.rs.next());
        assertTrue(this.rs.getInt(1) == 1);

    }
}
 

/**
 * Tests parsing of stored procedures
 * 
 * @throws Exception
 *             if an error occurs.
 */
public void testSPCache() throws Exception {
    CallableStatement storedProc = null;

    createProcedure("testSpParse", "(IN FOO VARCHAR(15))\nBEGIN\nSELECT 1;\nend\n");

    int numIterations = 10;

    long startTime = System.currentTimeMillis();

    for (int i = 0; i < numIterations; i++) {
        storedProc = this.conn.prepareCall("{call testSpParse(?)}");
        storedProc.close();
    }

    long elapsedTime = System.currentTimeMillis() - startTime;

    System.out.println("Standard parsing/execution: " + elapsedTime + " ms");

    storedProc = this.conn.prepareCall("{call testSpParse(?)}");
    storedProc.setString(1, "abc");
    this.rs = storedProc.executeQuery();

    assertTrue(this.rs.next());
    assertTrue(this.rs.getInt(1) == 1);

    Properties props = new Properties();
    props.setProperty(PropertyKey.cacheCallableStmts.getKeyName(), "true");

    Connection cachedSpConn = getConnectionWithProps(props);

    startTime = System.currentTimeMillis();

    for (int i = 0; i < numIterations; i++) {
        storedProc = cachedSpConn.prepareCall("{call testSpParse(?)}");
        storedProc.close();
    }

    elapsedTime = System.currentTimeMillis() - startTime;

    System.out.println("Cached parse stage: " + elapsedTime + " ms");

    storedProc = cachedSpConn.prepareCall("{call testSpParse(?)}");
    storedProc.setString(1, "abc");
    this.rs = storedProc.executeQuery();

    assertTrue(this.rs.next());
    assertTrue(this.rs.getInt(1) == 1);
}