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

/**
 * Call passed importSentence and process the error.
 * @param importSentence a call sentence to to import data from a nonexistent file.
 */
private void realTestRollBackWhenImportOnNonexistentFile(
        String importSentence) throws SQLException {
  //import a non-existing file will certainly fail
    CallableStatement cSt = prepareCall(importSentence);
    
    try {
        cSt.executeUpdate();
        fail("a SQLException should be thrown " +
                "as we import data from a nonexistent file");
    } catch (SQLException e) {            
        assertSQLState("XIE0M", e);
        assertSQLState("XJ058", e.getNextException());            
    } finally {
        cSt.close();
    }
}
 

/**
    * Perform export using SYSCS_UTIL.SYSCS_EXPORT_TABLE procedure.
    */
   protected void doExportTable(
   String      schemaName, 
   String      tableName, 
   String      fileName, 
   String      colDel , 
   String      charDel, 
   String      codeset) 
       throws SQLException 
   {
//DERBY-2925: need to delete existing files first.
       SupportFilesSetup.deleteFile(fileName);

       CallableStatement ps = 
           prepareCall(
               "call SYSCS_UTIL.SYSCS_EXPORT_TABLE (? , ? , ? , ?, ? , ?)");
       ps.setString(1, schemaName);
       ps.setString(2, tableName);
       ps.setString(3, fileName);
       ps.setString(4, colDel);
       ps.setString(5, charDel);
       ps.setString(6, codeset);
       ps.executeUpdate();
       ps.close();
   }
 

/**
 * Change of class due to testReplaceJar that
 * changes the application to run checks on the e-mail
 * to ensure it is valid (in this case by seeing if
 *  it simply includes 'spam' in the title).
 * @throws SQLException
 */
public void testReplacedClass() throws SQLException {
    // This version checks the e-mail address.
    CallableStatement cs = prepareCall("CALL EMC.ADDCONTACT(?, ?)");
    cs.setInt(1, 4);
    cs.setString(2, "[email protected]");
    cs.executeUpdate();
    cs.setInt(1, 5);
    cs.setString(2, "[email protected]");
    cs.executeUpdate();
    
    Statement s = createStatement();
    JDBC.assertFullResultSet(
            s.executeQuery("SELECT id, e_mail, ok from EMC.CONTACTS ORDER BY 1"),
            new String[][] {
                {"0", "[email protected]", null},
                {"1", "[email protected]", null},
                {"2", "[email protected]", null},
                {"3", "[email protected]", null},
                {"4", "[email protected]", "0"},
                {"5", "[email protected]", "1"},
                });
  
    s.close();
}
 

/**
 * Test the stored procedure SYSIBM.CLOBGETSUBSTRING
 *
 * @throws an SQLException.
 */
public void testGetSubStringSP() throws SQLException {
    CallableStatement cs  = prepareCall("? = CALL " +
        "SYSIBM.CLOBGETSUBSTRING(?,?,?)");
    cs.registerOutParameter(1, java.sql.Types.VARCHAR);
    cs.setInt(2, 1);
    cs.setLong(3, 1);
    //get sub-string of length 10 from the clob.
    cs.setInt(4, 10);
    cs.executeUpdate();
    String retVal = cs.getString(1);
    //compare the string that is returned to the sub-string obtained directly
    //from the test string. If found to be equal the stored procedure
    //returns valid values.
    if (testStr.substring(0, 10).compareTo(retVal) != 0) {
        fail("Error SYSIBM.CLOBGETSUBSTRING returns the wrong string");
    }
    cs.close();
}
 

/**
 * Tests the SYSIBM.BLOBRELEASELOCATOR stored procedure.
 *
 * @throws SQLException
 */
public void testBlobReleaseLocatorSP() throws SQLException {
    CallableStatement cs  = prepareCall
        ("CALL SYSIBM.BLOBRELEASELOCATOR(?)");
    cs.setInt(1, 1);
    cs.execute();
    cs.close();

    //once the locator has been released the BLOBGETLENGTH on that
    //locator value will throw an SQLException. This assures that
    //the locator has been properly released.

    cs  = prepareCall("? = CALL SYSIBM.BLOBGETLENGTH(?)");
    cs.registerOutParameter(1, java.sql.Types.BIGINT);
    cs.setInt(2, 1);
    try {
        cs.executeUpdate();
    } catch(SQLException sqle) {
        //on expected lines. The test was successful.
        return;
    }
    //The exception was not thrown. The test has failed here.
    fail("Error the locator was not released by SYSIBM.BLOBRELEASELOCATOR");
    cs.close();
}
 

protected void useCallableStatement( CallableStatement pCallableStatement ) throws SQLException
{
  if( _parameters != null )
  {
    for( int i = 0; i < _parameters.size(); i++ )
    {
      pCallableStatement.setObject( i + 1, _parameters.get( i ) );
    }
  }

  pCallableStatement.executeUpdate();
}
 

/**
 * Tests the SYSIBM.CLOBGETPOSITIONFROMLOCATOR stored procedure.
 *
 * @throws SQLException.
 */
public void testClobGetPositionFromLocatorSP() throws SQLException {
    int locator = 0;

    String newStr = "simple";

    CallableStatement cs  = prepareCall
        ("? = CALL SYSIBM.CLOBCREATELOCATOR()");
    cs.registerOutParameter(1, java.sql.Types.INTEGER);
    cs.executeUpdate();
    locator = cs.getInt(1);
    cs.close();

    cs  = prepareCall("CALL SYSIBM.CLOBSETSTRING(?,?,?,?)");
    cs.setInt(1, locator);
    cs.setInt(2, 1);
    cs.setLong(3, newStr.length());
    cs.setString(4, newStr);
    cs.execute();

    cs.close();
    cs  = prepareCall
        ("? = CALL SYSIBM.CLOBGETPOSITIONFROMLOCATOR(?,?,?)");
    cs.registerOutParameter(1, java.sql.Types.BIGINT);
    cs.setInt(2, 1);
    //find the position of the bytes corresponding to
    //the String simple in the test string.
    cs.setInt(3, locator);
    cs.setLong(4, 1L);
    cs.executeUpdate();
    //check to see that the returned position and the expected position
    //of the substring simple in the string are matching.
    assertEquals("Error SYSIBM.CLOBGETPOSITIONFROMLOCATOR returns " +
        "the wrong value for the position of the Clob", 8, cs.getLong(1));
    cs.close();
}
 

/**
 * Alter the table to add a column, the add contact procedure
 * should still work.
 * @throws SQLException
 */
public void testAlterTable() throws SQLException
{
    Statement s = createStatement();
    s.executeUpdate("ALTER TABLE EMC.CONTACTS ADD COLUMN OK SMALLINT");
    JDBC.assertFullResultSet(
            s.executeQuery("SELECT id, e_mail, ok from EMC.CONTACTS ORDER BY 1"),
            new String[][] {
                {"0", "[email protected]", null},
                {"1", "[email protected]", null},
                {"2", "[email protected]", null},
                });
    
    // well written application, INSERT used explicit column names
    // ok defaults to NULL
    CallableStatement cs = prepareCall("CALL EMC.ADDCONTACT(?, ?)");
    cs.setInt(1, 3);
    cs.setString(2, "[email protected]");
    cs.executeUpdate();
    cs.close();

    JDBC.assertFullResultSet(
            s.executeQuery("SELECT id, e_mail, ok from EMC.CONTACTS ORDER BY 1"),
            new String[][] {
                {"0", "[email protected]", null},
                {"1", "[email protected]", null},
                {"2", "[email protected]", null},
                {"3", "[email protected]", null},
                });
  
    s.close();
}
 

/**
 * Test that a new connection successfully sees the changes.
 * @throws SQLException
 */
public void testAddContact() throws SQLException
{
    CallableStatement cs = prepareCall("CALL EMC.ADDCONTACT(?, ?)");
    cs.setInt(1, 1);
    cs.setString(2, "[email protected]");
    cs.executeUpdate();
    
    cs.setInt(1, 2);
    cs.setString(2, "[email protected]");
    cs.executeUpdate();
    
    cs.close();
    
    Statement s = createStatement();
    ResultSet rs = s.executeQuery(
            "SELECT id, e_mail from EMC.CONTACTS ORDER BY 1");
    
    JDBC.assertFullResultSet(rs,
            new String[][] {
            {"0", "[email protected]"},
            {"1", "[email protected]"},
            {"2", "[email protected]"},
            });
    
    s.close();
}
 

private void replaceJar(String resource, String jarName) throws SQLException, MalformedURLException
{        
    URL jar = SupportFilesSetup.getReadOnlyURL(resource);
    assertNotNull(resource, jar);
    
    CallableStatement cs = prepareCall("CALL SQLJ.REPLACE_JAR(?, ?)");
    cs.setString(1, jar.toExternalForm());
    cs.setString(2, jarName);
    cs.executeUpdate();
    cs.close();
}
 

/**
 * This is intended to change the default eviction percentage to the value specified in the
 * sql.backupAndRestore.BackupAndRestorePrms-evictionHeapPercentage parameter.
 */
private static void setEvictionHeapPercentage() {
  try {
    Connection conn = (Connection) threadLocal_gfxdConnection.get();

    String getEvictionHeapPercentageSql = "values SYS.GET_EVICTION_HEAP_PERCENTAGE()";
    ResultSet resultSet = executeSqlQuery(conn, getEvictionHeapPercentageSql);
    resultSet.beforeFirst();
    while (resultSet.next()) {
      logWriter.info("BackupRestoreBigDataTest.setEvictionHeapPercentage-percentage before set=" +
                     resultSet.getDouble(1));
    }

    String setEvictionHeapPercentageSql = "CALL SYS.SET_EVICTION_HEAP_PERCENTAGE(?)";
    double evictionHeapPercentage = BackupAndRestorePrms.getEvictionHeapPercentage();
    logWriter.info("BackupRestoreBigDataTest.setEvictionHeapPercentage-sql=" + setEvictionHeapPercentageSql +
                   " with args " + evictionHeapPercentage);

    CallableStatement cs = conn.prepareCall(setEvictionHeapPercentageSql);
    cs.setDouble(1, evictionHeapPercentage);
    cs.executeUpdate();

    resultSet = executeSqlQuery(conn, getEvictionHeapPercentageSql);
    resultSet.beforeFirst();
    while (resultSet.next()) {
      logWriter.info("BackupRestoreBigDataTest.setEvictionHeapPercentage-percentage after set=" +
                     resultSet.getDouble(1));
    }

  } catch (SQLException se) {
    SQLHelper.handleSQLException(se);
  }
}
 

@Override
public int insertTU(int headerId, String tuId, String creationId, String creationDate, String changeId,
		String changeDate, String creationTool, String creationToolVersion, String client, String projectRef,
		String jobRef) throws SQLException {
	CallableStatement stmt = null;
	ResultSet rs = null;
	try {
		String sql = dbConfig.getOperateDbSQL("insert-tu");
		if (null == conn) {
			return -1;
		}
		stmt = conn.prepareCall(sql);
		int i = 1;
		stmt.setInt(i++, headerId);
		stmt.setString(i++, tuId);
		stmt.setString(i++, creationId);
		stmt.setTimestamp(i++, DateUtils.getTimestampFromUTC(creationDate));
		stmt.setString(i++, changeId);
		stmt.setTimestamp(i++, DateUtils.getTimestampFromUTC(changeDate));
		stmt.setString(i++, creationTool);
		stmt.setString(i++, creationToolVersion);
		stmt.setString(i++, client);
		stmt.setString(i++, projectRef);
		stmt.setString(i++, jobRef);
		stmt.registerOutParameter(i, Types.INTEGER);
		stmt.executeUpdate();
		return  stmt.getInt(i);
	} finally {
		if (rs != null) {
			rs.close();
		}
		if (stmt != null) {
			stmt.close();
		}
	}
}
 

/**
 * Set the classpath to include the MAIL_APP jar.
 * @throws SQLException
 */
public void testSetClasspath() throws SQLException
{
    setDBClasspath("EMC.MAIL_APP");
    
    // Test we don't need a re-boot to see the new classes.
    CallableStatement cs = prepareCall("CALL EMC.ADDCONTACT(?, ?)");
    cs.setInt(1, 0);
    cs.setString(2, "[email protected]");
    cs.executeUpdate();
    cs.close();
    
    derby2035Workaround();
}
 

/**
 * Check that System.gc() can be called directly from a procedure.
 * @throws SQLException
 */
public void testSystemGC() throws SQLException
{
    CallableStatement cs = prepareCall("CALL FORCEGC()");
    cs.executeUpdate();
    cs.close();
}
 

/**
     * Revert the properties to their values prior to the setUp call.
     */
    protected void tearDown()
    throws java.lang.Exception
    {
// GemStone changes BEGIN
        super.preTearDown();
// GemStone changes END
        Connection conn = getConnection();
        conn.setAutoCommit(false);
        CallableStatement setDBP =  conn.prepareCall(
            "CALL SYSCS_UTIL.SET_DATABASE_PROPERTY(?, NULL)");
    	// Clear all the system properties set by the new set
    	// that will not be reset by the old set. Ignore any 
        // invalid property values.
        try {
        	for (Enumeration e = newValues.propertyNames(); e.hasMoreElements();)
        	{
        		String key = (String) e.nextElement();
        		if (oldValues.getProperty(key) == null)
        		{
        			setDBP.setString(1, key);
        			setDBP.executeUpdate();
        		}
        	}
        } catch (SQLException sqle) {
        	if(!sqle.getSQLState().equals(SQLStateConstants.PROPERTY_UNSUPPORTED_CHANGE))
        		throw sqle;
        }
    	// and then reset nay old values which will cause the commit.
    	setProperties(oldValues);
        super.tearDown();
        newValues = null;
        oldValues = null;
        if (staticProperties) {
            TestConfiguration.getCurrent().shutdownDatabase();
        }
    }
 

/**
 * Tests the SYSIBM.BLOBGETPOSITIONFROMLOCATOR stored procedure.
 * @throws UnsupportedEncodingException 
 *
 * @throws SQLException.
 */
public void testBlobGetPositionFromLocatorSP() throws SQLException, UnsupportedEncodingException {
    String newString = "simple";
    byte [] newBytes = newString.getBytes("US-ASCII");
    //initialize the locator to a default value.
    int locator = -1;
    //call the stored procedure to return the created locator.
    CallableStatement cs  = prepareCall
        ("? = CALL SYSIBM.BLOBCREATELOCATOR()");
    cs.registerOutParameter(1, java.sql.Types.INTEGER);
    cs.executeUpdate();
    locator = cs.getInt(1);
    cs.close();

    //use this new locator to test the SETBYTES function
    //by inserting the new bytes and testing whether it has
    //been inserted properly.

    //Insert the new substring.
    cs  = prepareCall("CALL SYSIBM.BLOBSETBYTES(?,?,?,?)");
    cs.setInt(1, locator);
    cs.setLong(2, 1L);
    cs.setInt(3, newString.length());
    cs.setBytes(4, newBytes);
    cs.execute();
    cs.close();

    cs  = prepareCall
        ("? = CALL SYSIBM.BLOBGETPOSITIONFROMLOCATOR(?,?,?)");
    cs.registerOutParameter(1, java.sql.Types.BIGINT);
    cs.setInt(2, 1);
    //find the position of the bytes corresponding to
    //the String simple in the test string.
    cs.setInt(3, locator);
    cs.setLong(4, 1L);
    cs.executeUpdate();
    //check to see that the returned position and the expected position
    //of the substring simple in the string are matching.
    assertEquals("Error SYSIBM.BLOBGETPOSITIONFROMLOCATOR returns " +
        "the wrong value for the position of the Blob", 8, cs.getLong(1));
    cs.close();
}
 

/**
 * Tests the stored procedure SYSIBM.CLOBSETSTRING
 *
 * @throws SQLException.
 */
public void testClobSetStringSP() throws SQLException {
    String newString = "123456789012345";
    //initialize the locator to a default value.
    int locator = -1;
    //call the stored procedure to return the created locator.
    CallableStatement cs  = prepareCall
        ("? = CALL SYSIBM.CLOBCREATELOCATOR()");
    cs.registerOutParameter(1, java.sql.Types.INTEGER);
    cs.executeUpdate();
    locator = cs.getInt(1);
    cs.close();

    //use this new locator to test the SETSUBSTRING function
    //by inserting a new sub string and testing whether it has
    //been inserted properly.

    //Insert the new substring.
    cs  = prepareCall("CALL SYSIBM.CLOBSETSTRING(?,?,?,?)");
    cs.setInt(1, locator);
    cs.setInt(2, 1);
    cs.setLong(3, newString.length());
    cs.setString(4, newString);
    cs.execute();
    cs.close();

    //check the new locator to see if the value has been inserted correctly.
    cs  = prepareCall("? = CALL " +
        "SYSIBM.CLOBGETSUBSTRING(?,?,?)");
    cs.registerOutParameter(1, java.sql.Types.VARCHAR);
    cs.setInt(2, locator);
    cs.setLong(3, 1);
    //get sub-string of length 10 from the clob.
    cs.setInt(4, newString.length());
    cs.executeUpdate();
    String retVal = cs.getString(1);
    //compare the new string and the string returned by the stored
    //procedure to see of they are the same.
    if (newString.compareTo(retVal) != 0)
        fail("SYSIBM.CLOBSETSTRING does not insert the right value");
    cs.close();
}
 

/**
 * Tests the stored procedure SYSIBM.BLOBSETBYTES
 * @throws UnsupportedEncodingException 
 *
 * @throws SQLException.
 */
public void testBlobSetBytes() throws SQLException, UnsupportedEncodingException {
    String newString = "123456789012345";
    byte [] newBytes = newString.getBytes("US-ASCII");
    //initialize the locator to a default value.
    int locator = -1;
    //call the stored procedure to return the created locator.
    CallableStatement cs  = prepareCall
        ("? = CALL SYSIBM.BLOBCREATELOCATOR()");
    cs.registerOutParameter(1, java.sql.Types.INTEGER);
    cs.executeUpdate();
    locator = cs.getInt(1);
    cs.close();

    //use this new locator to test the SETBYTES function
    //by inserting the new bytes and testing whether it has
    //been inserted properly.

    //Insert the new substring.
    cs  = prepareCall("CALL SYSIBM.BLOBSETBYTES(?,?,?,?)");
    cs.setInt(1, locator);
    cs.setLong(2, 1L);
    cs.setInt(3, newString.length());
    cs.setBytes(4, newBytes);
    cs.execute();
    cs.close();

    //check the new locator to see if the value has been inserted correctly.
    cs  = prepareCall("? = CALL " +
        "SYSIBM.BLOBGETBYTES(?,?,?)");
    cs.registerOutParameter(1, java.sql.Types.VARBINARY);
    cs.setInt(2, locator);
    cs.setLong(3, 1);
    cs.setInt(4, newString.length());
    cs.executeUpdate();
    byte [] retVal = cs.getBytes(1);
    //compare the new bytes and the bytes returned by the stored
    //procedure to see of they are the same.
    for (int i=0;i<newString.length();i++){
        assertEquals
            ("The Stored procedure SYSIBM.BLOBGETBYTES " +
            "returns the wrong bytes"
            , newBytes[i], retVal[i]);
    }
    cs.close();
}
 

private void attemptSetProperties(Properties values, Connection coonn) throws SQLException
{
    Connection conn = getConnection();
    conn.setAutoCommit(false);
    
    PreparedStatement getDBP =  conn.prepareStatement(
        "VALUES SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY(?)");
    CallableStatement setDBP =  conn.prepareCall(
        "CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(?, ?)");
    
    
	for (Enumeration e = values.propertyNames(); e.hasMoreElements();)
	{
		final String key = (String) e.nextElement();
		final String value = values.getProperty(key);
        
        getDBP.setString(1, key);
        ResultSet rs = getDBP.executeQuery();
        rs.next();
        String old = rs.getString(1);
        rs.close();
            		
		boolean change;
		if (old != null)
		{
            // set, might need to be changed.
            change = !old.equals(value);
            
            // If we are not processing the oldValues
            // then store in the oldValues. Reference equality is ok here.
			if (change && (values != oldValues))
			   oldValues.setProperty(key, old);
		}
		else {
			// notset, needs to be set
			change = true;
		}
		
		if (change) {
            setDBP.setString(1, key);
            setDBP.setString(2, value);
            setDBP.executeUpdate();
	    }
	}
    conn.commit();
    getDBP.close();
    setDBP.close();
    conn.close();
}
 

/**
 * Tests the stored procedure SYSIBM.CLOBSETSTRING
 *
 * @throws SQLException.
 */
public void testClobSetStringSP() throws SQLException {
    String newString = "123456789012345";
    //initialize the locator to a default value.
    int locator = -1;
    //call the stored procedure to return the created locator.
    CallableStatement cs  = prepareCall
        ("? = CALL SYSIBM.CLOBCREATELOCATOR()");
    cs.registerOutParameter(1, java.sql.Types.INTEGER);
    cs.executeUpdate();
    locator = cs.getInt(1);
    cs.close();

    //use this new locator to test the SETSUBSTRING function
    //by inserting a new sub string and testing whether it has
    //been inserted properly.

    //Insert the new substring.
    cs  = prepareCall("CALL SYSIBM.CLOBSETSTRING(?,?,?,?)");
    cs.setInt(1, locator);
    cs.setInt(2, 1);
    cs.setLong(3, newString.length());
    cs.setString(4, newString);
    cs.execute();
    cs.close();

    //check the new locator to see if the value has been inserted correctly.
    cs  = prepareCall("? = CALL " +
        "SYSIBM.CLOBGETSUBSTRING(?,?,?)");
    cs.registerOutParameter(1, java.sql.Types.VARCHAR);
    cs.setInt(2, locator);
    cs.setLong(3, 1);
    //get sub-string of length 10 from the clob.
    cs.setInt(4, newString.length());
    cs.executeUpdate();
    String retVal = cs.getString(1);
    //compare the new string and the string returned by the stored
    //procedure to see of they are the same.
    if (newString.compareTo(retVal) != 0)
        fail("SYSIBM.CLOBSETSTRING does not insert the right value");
    cs.close();
}