public void close() throws SQLException { cstmt.close(); }
@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 closeProcedureStatement() throws KettleDatabaseException { // CHE: close the callable statement involved in the stored // procedure call! try { if ( cstmt != null ) { cstmt.close(); cstmt = null; } } catch ( SQLException ex ) { throw new KettleDatabaseException( BaseMessages.getString( PKG, "Database.Exception.ErrorClosingCallableStatement" ), ex ); } }
if (stmt != null) { try { stmt.close(); } catch (SQLException e) { e.printStackTrace();
@After public void verifyClosed() throws Exception { if (verifyClosedAfter) { verify(callableStatement).close(); verify(connection, atLeastOnce()).close(); } }
private void verifyAddInvoiceWithoutMetaData(boolean isFunction) throws SQLException { if (isFunction) { verify(callableStatement).registerOutParameter(1, 4); verify(callableStatement).setObject(2, 1103, 4); verify(callableStatement).setObject(3, 3, 4); } else { verify(callableStatement).setObject(1, 1103, 4); verify(callableStatement).setObject(2, 3, 4); verify(callableStatement).registerOutParameter(3, 4); } verify(callableStatement).close(); }
@Test public void testCaseInsensitiveResultsMap() throws Exception { given(this.callableStatement.execute()).willReturn(false); given(this.callableStatement.getUpdateCount()).willReturn(-1); given(this.callableStatement.getObject(1)).willReturn("X"); assertTrue("default should have been NOT case insensitive", !this.template.isResultsMapCaseInsensitive()); this.template.setResultsMapCaseInsensitive(true); assertTrue("now it should have been set to case insensitive", this.template.isResultsMapCaseInsensitive()); Map<String, Object> out = this.template.call( conn -> conn.prepareCall("my query"), Collections.singletonList(new SqlOutParameter("a", 12))); assertThat(out, instanceOf(LinkedCaseInsensitiveMap.class)); assertNotNull("we should have gotten the result with upper case", out.get("A")); assertNotNull("we should have gotten the result with lower case", out.get("a")); verify(this.callableStatement).close(); verify(this.connection).close(); }
@Test public void testExecuteClosed() throws Exception { given(this.resultSet.next()).willReturn(true); given(this.callableStatement.execute()).willReturn(true); given(this.callableStatement.getUpdateCount()).willReturn(-1); SqlParameter param = new SqlReturnResultSet("", (RowCallbackHandler) rs -> { throw new InvalidDataAccessApiUsageException(""); }); this.thrown.expect(InvalidDataAccessApiUsageException.class); try { this.template.call(conn -> conn.prepareCall("my query"), Collections.singletonList(param)); } finally { verify(this.resultSet).close(); verify(this.callableStatement).close(); verify(this.connection).close(); } }
private void verifyAddInvoiceWithMetaData(boolean isFunction) throws SQLException { ResultSet proceduresResultSet = databaseMetaData.getProcedures("", "ME", "ADD_INVOICE"); ResultSet procedureColumnsResultSet = databaseMetaData.getProcedureColumns("", "ME", "ADD_INVOICE", null); if (isFunction) { verify(callableStatement).registerOutParameter(1, 4); verify(callableStatement).setObject(2, 1103, 4); verify(callableStatement).setObject(3, 3, 4); } else { verify(callableStatement).setObject(1, 1103, 4); verify(callableStatement).setObject(2, 3, 4); verify(callableStatement).registerOutParameter(3, 4); } verify(callableStatement).close(); verify(proceduresResultSet).close(); verify(procedureColumnsResultSet).close(); }
stmt.close();
@Test public void testAddInvoices() throws Exception { DefaultListableBeanFactory bf = new DefaultListableBeanFactory(); new XmlBeanDefinitionReader(bf).loadBeanDefinitions( new ClassPathResource("org/springframework/jdbc/object/GenericStoredProcedureTests-context.xml")); Connection connection = mock(Connection.class); DataSource dataSource = mock(DataSource.class); given(dataSource.getConnection()).willReturn(connection); CallableStatement callableStatement = mock(CallableStatement.class); TestDataSourceWrapper testDataSource = (TestDataSourceWrapper) bf.getBean("dataSource"); testDataSource.setTarget(dataSource); given(callableStatement.execute()).willReturn(false); given(callableStatement.getUpdateCount()).willReturn(-1); given(callableStatement.getObject(3)).willReturn(4); given(connection.prepareCall("{call " + "add_invoice" + "(?, ?, ?)}")).willReturn(callableStatement); StoredProcedure adder = (StoredProcedure) bf.getBean("genericProcedure"); Map<String, Object> in = new HashMap<>(2); in.put("amount", 1106); in.put("custid", 3); Map<String, Object> out = adder.execute(in); Integer id = (Integer) out.get("newid"); assertEquals(4, id.intValue()); verify(callableStatement).setObject(1, 1106, Types.INTEGER); verify(callableStatement).setObject(2, 3, Types.INTEGER); verify(callableStatement).registerOutParameter(3, Types.INTEGER); verify(callableStatement).close(); }
@Test public void testStoredProcedureReturnValue() { doInJPA( this::entityManagerFactory, entityManager -> { Session session = entityManager.unwrap( Session.class ); session.doWork( connection -> { CallableStatement function = null; try { function = connection.prepareCall("{ ? = call fn_count_phones(?) }"); function.registerOutParameter(1, Types.INTEGER); function.setInt(2, 1); function.execute(); int phoneCount = function.getInt(1); assertEquals(2, phoneCount); } finally { if ( function != null ) { function.close(); } } } ); } ); } }
@Test public void testNoSuchStoredProcedure() throws Exception { final String NO_SUCH_PROC = "x"; SQLException sqlException = new SQLException("Syntax error or access violation exception", "42000"); given(databaseMetaData.getDatabaseProductName()).willReturn("MyDB"); given(databaseMetaData.getDatabaseProductName()).willReturn("MyDB"); given(databaseMetaData.getUserName()).willReturn("me"); given(databaseMetaData.storesLowerCaseIdentifiers()).willReturn(true); given(callableStatement.execute()).willThrow(sqlException); given(connection.prepareCall("{call " + NO_SUCH_PROC + "()}")).willReturn(callableStatement); SimpleJdbcCall sproc = new SimpleJdbcCall(dataSource).withProcedureName(NO_SUCH_PROC); thrown.expect(BadSqlGrammarException.class); thrown.expect(exceptionCause(sameInstance(sqlException))); try { sproc.execute(); } finally { verify(callableStatement).close(); verify(connection, atLeastOnce()).close(); } }
/** * closeCallableStatement * * @param stmt CallableStatement Object passed to be closed */ protected void closeCallableStmt(CallableStatement stmt) { try { if (stmt != null) { stmt.close(); } } catch (SQLException e) { throw new RuntimeException(e); } }
@Test public void binaryExcludedTrue() throws SQLException { // given P6LogOptions.getActiveInstance().setExcludebinary(true); // when String paramValName = "param_val"; String paramIntName = "param_id"; String resultParamName = "result_param"; // execute the statement String query = "{call test_proc_binary(?,?,?)}"; CallableStatement call = connection.prepareCall(query); call.setBytes(paramValName, "foo".getBytes(StandardCharsets.UTF_8)); call.setInt(paramIntName, TEST_IMG_ID); call.registerOutParameter(resultParamName, Types.INTEGER); call.execute(); // out vals not logged anyway https://github.com/p6spy/p6spy/issues/133 // byte[] retVal = call.getBytes(resultParamName); // assertEquals("foo", retVal); call.close(); // then assertTrue( getLastLogEntry().contains("{call test_proc_binary(?,?,?)} " + paramIntName + ":2000, " + paramValName + ":'[binary]'") // || getLastLogEntry().contains("{call test_proc_binary(?,?,?)} " + paramValName + ":'[binary]', " + paramIntName + ":2000")); }
@Test public void binaryExcludedFalse() throws SQLException { // given P6LogOptions.getActiveInstance().setExcludebinary(false); // when String paramValName = "param_val"; String paramIntName = "param_id"; String resultParamName = "result_param"; // execute the statement String query = "{call test_proc_binary(?,?,?)}"; CallableStatement call = connection.prepareCall(query); call.setBytes(paramValName, "foo".getBytes(StandardCharsets.UTF_8)); call.setInt(paramIntName, TEST_IMG_ID); call.registerOutParameter(resultParamName, Types.INTEGER); call.execute(); // out vals not logged anyway https://github.com/p6spy/p6spy/issues/133 // byte[] retVal = call.getBytes(resultParamName); // assertEquals("foo", retVal); call.close(); // then assertTrue( getLastLogEntry().contains("{call test_proc_binary(?,?,?)} " + paramIntName + ":2000, " + paramValName + ":'666F6F'") // || getLastLogEntry().contains("{call test_proc_binary(?,?,?)} " + paramValName + ":'666F6F', " + paramIntName + ":2000")); }
@Test public void testStoredProcedureResultSet() throws SQLException { if( "Oracle".equals(db)) { // Oracle does not support returning a resultset from a store proc via CallableStatement.getResultSet() return; } P6LogOptions.getActiveInstance().setExcludecategories("debug,info,result"); this.clearLogEntries(); // execute the statement String query = "{call test_proc_rs(?)}"; CallableStatement call = connection.prepareCall(query); call.setString(1, "a"); call.execute(); ResultSet rs = call.getResultSet(); if( rs == null ) { // HSQLDB requires you to call ResultSet.getMoreResults() before accessing the resultset. call.getMoreResults(); rs = call.getResultSet(); } while(rs.next()) { rs.getString("name"); rs.getInt("id"); } rs.close(); call.close(); // verify that the result set was logged assertTrue(getLastLogEntry().contains("resultset")); }
@Test public void testStoredProcedureWithNullInputParameter() throws SQLException { this.clearLogEntries(); // execute the statement String query = "{call test_proc(?,?,?)}"; CallableStatement stmt = connection.prepareCall(query); stmt.registerOutParameter(3, Types.INTEGER); stmt.setInt(1, 1); stmt.setNull(2, Types.VARCHAR); stmt.execute(); int retVal = stmt.getInt(3); assertEquals(2, retVal); stmt.close(); // verify that the third parameter is NULL assertTrue(getLastLogEntry().contains("1,NULL")); }
@Test public void testStoredProcedureNoResultSet() throws SQLException { this.clearLogEntries(); // execute the statement String query = "{call test_proc(?,?,?)}"; CallableStatement call = connection.prepareCall(query); call.registerOutParameter(3, Types.INTEGER); call.setInt(1, 1); call.setString(2, "hi"); call.execute(); int retVal = call.getInt(3); assertEquals(2, retVal); call.close(); // the last log message should have the original query assertTrue(getLastLogEntry().contains(query)); // verify that the bind parameters are resolved in the log message assertTrue(getLastLogEntry().contains("1,'hi'")); }
@Test public void testNamedParameters() throws SQLException { this.clearLogEntries(); String param1Name = "param1"; String param2Name = "param2"; String resultParamName = "result_param"; // execute the statement String query = "{call test_proc(?,?,?)}"; CallableStatement call = connection.prepareCall(query); call.setInt(param1Name, 1); call.setString(param2Name, "hi"); call.registerOutParameter(resultParamName, Types.INTEGER); call.execute(); int retVal = call.getInt(resultParamName); assertEquals(2, retVal); call.close(); // the last log message should have the original query assertTrue(getLastLogEntry().contains(query)); assertTrue(getLastLogEntry().contains("{call test_proc(?,?,?)}")); assertTrue(getLastLogEntry().contains(param1Name+":"+"1")); assertTrue(getLastLogEntry().contains(param2Name+":"+"'hi'")); }