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

The following examples show how to use java.sql.CallableStatement#executeUpdate() . These examples are extracted from open source projects. You can vote up the ones you like or vote down the ones you don't like, and go to the original project or source file by following the links above each example. You may check out the related API usage on the sidebar.
Example 1
/**
 * 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();
    }
}
 
Example 2
/**
 * 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();
}
 
Example 3
/**
 * 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();
}
 
Example 4
/**
 * 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();
}
 
Example 5
/**
    * 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();
   }
 
Example 6
Source Project: orcas   File: WrapperExecuteStatement.java    License: Apache License 2.0 5 votes vote down vote up
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();
}
 
Example 7
/**
     * 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();
        }
    }
 
Example 8
/**
 * 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();
}
 
Example 9
/**
 * 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();
}
 
Example 10
@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();
		}
	}
}
 
Example 11
/**
 * 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);
  }
}
 
Example 12
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();
}
 
Example 13
/**
 * 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();
}
 
Example 14
/**
 * 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();
}
 
Example 15
/**
 * 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();
}
 
Example 16
/**
 * 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();
}
 
Example 17
/**
 * 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();
}
 
Example 18
/**
 * 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();
}
 
Example 19
/**
 * 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();
}
 
Example 20
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();
}