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

/**
 * 写MartifHeader节点内容
 * @param hContent
 *            整个节点的内容
 * @param hIdAttr
 *            MartifHeader节点的ID属性;
 * @return
 * @throws SQLException
 */
public int insertBMartifHeader(String hContent, String hIdAttr) throws SQLException {
	CallableStatement stmt = null;
	try {
		String sql = dbConfig.getOperateDbSQL("insert-bmartifheader");
		stmt = conn.prepareCall(sql);
		stmt.setString(1, hIdAttr);
		stmt.setString(2, hContent);
		stmt.registerOutParameter(3, Types.INTEGER);
		stmt.execute();
		return stmt.getInt(3);
	} finally {
		if (stmt != null) {
			stmt.close();
		}
	}
}
 

/**
 * Perform import using SYSCS_UTIL.IMPORT_TABLE procedure.
 */
protected void doImportTable(
String      schemaName, 
String      tableName, 
String      fileName, 
String      colDel, 
String      charDel, 
String      codeset,
int         replace) 
    throws SQLException 
{
    CallableStatement ps = 
        prepareCall(
            "call SYSCS_UTIL.IMPORT_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.setInt(   7, replace);
    ps.executeUpdate();
    ps.close();
}
 

private void rebalanceTables() throws InterruptedException, SQLException {
  final Connection conn = openTmpConnection();
  try {
    CallableStatement cs = conn.prepareCall("call SYS.REBALANCE_ALL_BUCKETS()");
    cs.execute();
    cs.close();

    final ResourceManager rm = CacheFactory.getAnyInstance()
                                           .getResourceManager();
    Log.getLogWriter().info("Waiting for existing rebalance");
    for (RebalanceOperation op : rm.getRebalanceOperations()) {
      op.getResults(); // blocking call
    }
    Log.getLogWriter().info("Waited for existing rebalance");
    /*
    Log.getLogWriter().info("Waiting for follow-on rebalance");
    rm.createRebalanceFactory().start().getResults();
    for (RebalanceOperation op : rm.getRebalanceOperations()) {
      op.getResults(); // blocking call
    }
    Log.getLogWriter().info("Waited for follow-on rebalance");
    */
  } finally {
    closeTmpConnection(conn);
  }
}
 

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

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

/**
        * test ParameterMetaData for Java procedures with INTEGER parameters 
 * 
 * @exception SQLException if error occurs
        */
public void testParameterMetadataWithINTParameters () throws SQLException {

	Statement stmt = createStatement();
      		stmt.execute("CREATE PROCEDURE PMDI(IN pmdI_1 INTEGER, IN pmdI_2 INTEGER, INOUT pmdI_3 INTEGER, OUT pmdI_4 INTEGER) language java parameter style java external name 'com.splicemachine.dbTesting.functionTests.tests.jdbcapi.ParameterMetaDataJdbc30Test.dummyint'");
     		CallableStatement cs = prepareCall("CALL PMDI(?, ?, ?, ?)");

	// parameters 1 and 2 are input only
              	cs.setInt(1,1);
       	cs.setInt(2,1);
	// parameter 3 is input and output
               Object x = new Integer(1);
               cs.setObject(3,x, Types.INTEGER);
               cs.registerOutParameter(3,Types.INTEGER);
              	//parameter 4 is output only
              	cs.registerOutParameter(4,Types.INTEGER);

              	//verify the meta data for the parameters
              	ParameterMetaData paramMetaData = cs.getParameterMetaData();
              	assertEquals("Unexpected parameter count", 4, paramMetaData.getParameterCount());

	//expected values to be stored in a 2dim. array
               String parameterMetaDataArray0 [][] = {
               //isNullable, isSigned, getPrecision, getScale, getParameterType, getParameterTypeName, getParameterClassName, getParameterMode
               {"PARAMETER_NULLABLE", "true", "10", "0", "4", "INTEGER", "java.lang.Integer", "PARAMETER_MODE_IN"},
               {"PARAMETER_NULLABLE", "true", "10", "0", "4", "INTEGER", "java.lang.Integer", "PARAMETER_MODE_IN"},
               {"PARAMETER_NULLABLE", "true", "10", "0", "4", "INTEGER", "java.lang.Integer", "PARAMETER_MODE_IN_OUT"},
               {"PARAMETER_NULLABLE", "true", "10", "0", "4", "INTEGER", "java.lang.Integer", "PARAMETER_MODE_OUT"}};

               testParameterMetaData(cs.getParameterMetaData(), parameterMetaDataArray0);
		
      		cs.close();
      		stmt.execute("DROP PROCEDURE PMDI");
           stmt.close();
}
 

/**
 * Tests the SYSIBM.BLOBGETLENGTH stored procedure.
 *
 * @throws SQLException.
 */
public void testBlobGetLengthSP() throws SQLException {
    CallableStatement cs  = prepareCall
        ("? = CALL SYSIBM.BLOBGETLENGTH(?)");
    cs.registerOutParameter(1, java.sql.Types.BIGINT);
    cs.setInt(2, 1);
    cs.executeUpdate();
    //compare the actual length of the test string and the returned length.
    assertEquals("Error SYSIBM.BLOBGETLENGTH returns " +
        "the wrong value for the length of the Blob", testStrLength, cs.getLong(1));
    cs.close();
}
 

/**
 * 将数据写入到MTU表中
 * @param params
 * @return
 * @throws SQLException
 *             ;
 */
public String insertTU(Hashtable<String, String> params) throws SQLException {
	CallableStatement stmt = null;
	ResultSet rs = null;
	try {
		String sql = dbConfig.getOperateDbSQL("insert-tu");
		stmt = conn.prepareCall(sql);
		int i = 1;
		stmt.setInt(i++, Integer.parseInt(params.get("HEADERID")));
		stmt.setString(i++, params.get("TUID"));
		stmt.setString(i++, params.get("CREATIONID"));
		stmt.setTimestamp(i++, DateUtils.getTimestampFromUTC(params.get("CREATIONDATE")));
		stmt.setString(i++, params.get("CHANGEID"));
		stmt.setTimestamp(i++, DateUtils.getTimestampFromUTC(params.get("CHANGEDATE")));
		stmt.setString(i++, params.get("CREATIONTOOL"));
		stmt.setString(i++, params.get("CREATIONTOOLVERSION"));
		stmt.setString(i++, params.get("CLIENT"));
		stmt.setString(i++, params.get("PROJECTREF"));
		stmt.setString(i++, params.get("JOBREF"));
		stmt.registerOutParameter(i++, Types.INTEGER);
		stmt.execute();
		return stmt.getString(i - 1);
	} finally {
		if (rs != null) {
			rs.close();
		}
		if (stmt != null) {
			stmt.close();
		}
	}
}
 

public void testChangeIsoLevelCallableStatementJDBC() throws SQLException {
    Connection c = getConnection();
    CallableStatement cs = prepareCall("CALL SET_ISOLATION_JDBC(?)");
    for (int i = 0; i < 4; ++i) {
        cs.setInt(1, isoLevels[i].getIsoLevel());
        cs.execute();
        verifyCachedIsolation(c);
    }
    cs.close();
}
 

private void doProcedureCall() throws SQLException {
  int times = HydraUtil.getnextNonZeroRandomInt(10);
  Connection conn = getGFEConnection();
//  printOpenConnection("doProcedureCall");
  CallableStatement cs;
  
  String procName = HydraUtil.getRandomElement(new String[] { "trade.show_customers(?)", "trade.longRunningProcedure(?)" });
  String sql = "CALL  " + procName + " ON ALL";
  cs = conn.prepareCall(sql);
  Log.getLogWriter().info("Executing proc sql " + sql);
  for (int i = 0; i < times; i++) {
    try {
      Log.getLogWriter().info("Running Procedure : " + procName);
      int counter = isEdge ? incrementCounter(COUNTER_MBEAN_PROC_CALLS_IN_PROGRESS_EDGE) : incrementCounter(COUNTER_MBEAN_PROC_CALLS_IN_PROGRESS);
      cs.setInt(1, RemoteTestModule.getCurrentThread().getThreadId());
      cs.execute();
      counter = isEdge ? incrementCounter(COUNTER_MBEAN_PROC_CALLS_EDGE) : incrementCounter(COUNTER_MBEAN_PROC_CALLS);
    } catch(SQLException e) {
      if (e.getSQLState().equals("XCL54")) {
        Log.getLogWriter().warning("Error occurred while executing query : " + sql + ", Low Memory Exception");
      } else {
        throw e;
      }
    } finally {
      if(isEdge) {
        decrementCounter(COUNTER_MBEAN_PROC_CALLS_IN_PROGRESS_EDGE);
      } else {
        decrementCounter(COUNTER_MBEAN_PROC_CALLS_IN_PROGRESS);
      }
      
      Log.getLogWriter().info("Procedure Completed : " + procName);
    }
  }
  
  cs.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 static void setDatabaseProperty(String property, String value) 
throws SQLException
{
    DataSource ds = JDBCDataSource.getDataSource();
    Connection cadmin = ds.getConnection();
    CallableStatement cs = cadmin.prepareCall(
        "CALL SYSCS_UTIL.SET_DATABASE_PROPERTY(?, ?)");
    cs.setString(1, property);
    cs.setString(2, value);
    cs.execute();
    
    cs.close();
    cadmin.close();
}
 

/**
 * Test fix for Bug4810 -Connection.commit() & rollback() do not
 * commit/rollback in auto-commit mode.
 */
public void testBug4810() throws SQLException {
    Connection con = getConnection();

    CallableStatement cs = con
            .prepareCall("CALL SYSCS_UTIL.SET_DATABASE_PROPERTY(?, ?)");
    cs.setString(1, "gemfirexd.locks.deadlockTimeout");
    cs.setString(2, "3");
    cs.execute();
    cs.setString(1, "gemfirexd.locks.waitTimeout");
    cs.setString(2, "3");
    cs.close();
    Statement stmt = con.createStatement();
    stmt.executeUpdate("create table bug4810(i int, b int)");
    stmt
            .executeUpdate("insert into bug4810 values (1,1), (1,2), (1,3), (1,4)");
    stmt
            .executeUpdate("insert into bug4810 values (1,1), (1,2), (1,3), (1,4)");
    con.commit();
    con.setAutoCommit(true);
    con.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ);
    // Just autocommit
    checkLocksForAutoCommitSelect(con, stmt, 0);
    // commit with autocommit
    checkLocksForAutoCommitSelect(con, stmt, 1);
    // rollback with autocommit
    checkLocksForAutoCommitSelect(con, stmt, 2);

    stmt.execute("drop table bug4810");
    con.commit();
    stmt.close();
}
 

/**
 * Test for bug [1052942] Error processing JDBC call escape. (A blank
 * before the final <code>}</code> causes the parser to fail).
 */
public void testCallableStatementParsing5() throws Exception {
    CallableStatement cstmt = con.prepareCall(" { Call Test(?,?) } ");
    cstmt.close();
}
 

/**
 * WL#6406 - Stacked diagnostic areas
 * 
 * "STACKED" in "GET [CURRENT | STACKED] DIAGNOSTICS" syntax was added in 5.7.0. Final behavior was implemented in
 * version 5.7.2, by WL#5928 - Most statements should clear the diagnostic area.
 * 
 * @throws SQLException
 */
public void testGetStackedDiagnostics() throws Exception {
    if (!versionMeetsMinimum(5, 7, 2)) {
        return;
    }

    // test calling GET STACKED DIAGNOSTICS outside an handler
    final Statement locallyScopedStmt = this.stmt;
    assertThrows(SQLException.class, "GET STACKED DIAGNOSTICS when handler not active", new Callable<Void>() {
        public Void call() throws Exception {
            locallyScopedStmt.execute("GET STACKED DIAGNOSTICS @num = NUMBER");
            return null;
        }
    });

    // test calling GET STACKED DIAGNOSTICS inside an handler
    // (stored procedure is based on documentation example)
    createTable("testGetStackedDiagnosticsTbl", "(c VARCHAR(8) NOT NULL)");
    createProcedure("testGetStackedDiagnosticsSP",
            "() BEGIN DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN " + "GET CURRENT DIAGNOSTICS CONDITION 1 @errno = MYSQL_ERRNO, @msg = MESSAGE_TEXT; "
                    + "SELECT 'current DA before insert in handler' AS op, @errno AS errno, @msg AS msg; " // 1st result
                    + "GET STACKED DIAGNOSTICS CONDITION 1 @errno = MYSQL_ERRNO, @msg = MESSAGE_TEXT; "
                    + "SELECT 'stacked DA before insert in handler' AS op, @errno AS errno, @msg AS msg; " // 2nd result
                    + "INSERT INTO testGetStackedDiagnosticsTbl (c) VALUES('gnitset'); " + "GET CURRENT DIAGNOSTICS @num = NUMBER; "
                    + "IF @num = 0 THEN SELECT 'INSERT succeeded, current DA is empty' AS op; " // 3rd result
                    + "ELSE GET CURRENT DIAGNOSTICS CONDITION 1 @errno = MYSQL_ERRNO, @msg = MESSAGE_TEXT; "
                    + "SELECT 'current DA after insert in handler' AS op, @errno AS errno, @msg AS msg; END IF; "
                    + "GET STACKED DIAGNOSTICS CONDITION 1 @errno = MYSQL_ERRNO, @msg = MESSAGE_TEXT; "
                    + "SELECT 'stacked DA after insert in handler' AS op, @errno AS errno, @msg AS msg; END; " // 4th result
                    + "INSERT INTO testGetStackedDiagnosticsTbl (c) VALUES ('testing');INSERT INTO testGetStackedDiagnosticsTbl (c) VALUES (NULL); END");

    CallableStatement cStmt = this.conn.prepareCall("CALL testGetStackedDiagnosticsSP()");
    assertTrue(cStmt.execute());

    // test 1st ResultSet
    this.rs = cStmt.getResultSet();
    assertTrue(this.rs.next());
    assertEquals("current DA before insert in handler", this.rs.getString(1));
    assertEquals(1048, this.rs.getInt(2));
    assertEquals("Column 'c' cannot be null", this.rs.getString(3));
    assertFalse(this.rs.next());
    this.rs.close();

    // test 2nd ResultSet
    assertTrue(cStmt.getMoreResults());
    this.rs = cStmt.getResultSet();
    assertTrue(this.rs.next());
    assertEquals("stacked DA before insert in handler", this.rs.getString(1));
    assertEquals(1048, this.rs.getInt(2));
    assertEquals("Column 'c' cannot be null", this.rs.getString(3));
    assertFalse(this.rs.next());
    this.rs.close();

    // test 3rd ResultSet
    assertTrue(cStmt.getMoreResults());
    this.rs = cStmt.getResultSet();
    assertTrue(this.rs.next());
    assertEquals("INSERT succeeded, current DA is empty", this.rs.getString(1));
    assertFalse(this.rs.next());
    this.rs.close();

    // test 4th ResultSet
    assertTrue(cStmt.getMoreResults());
    this.rs = cStmt.getResultSet();
    assertTrue(this.rs.next());
    assertEquals("stacked DA after insert in handler", this.rs.getString(1));
    assertEquals(1048, this.rs.getInt(2));
    assertEquals("Column 'c' cannot be null", this.rs.getString(3));
    assertFalse(this.rs.next());
    this.rs.close();

    // no more ResultSets
    assertFalse(cStmt.getMoreResults());
    cStmt.close();

    // test table contents
    this.rs = this.stmt.executeQuery("SELECT * FROM testGetStackedDiagnosticsTbl");
    assertTrue(this.rs.next());
    assertEquals("testing", this.rs.getString(1));
    assertTrue(this.rs.next());
    assertEquals("gnitset", this.rs.getString(1));
    assertFalse(this.rs.next());
    this.rs.close();
}
 

/**
 * See DERBY-3875.
 * <p>
 * Steps in the test:
 *  1) Create a database and perform a backup.
 *  2) Shutdown the Derby engine.
 *  3) Corrupt one of the database files.
 *  4) Boot corrupted database.
 *  5) Restore backup.
 * <p>
 * With the bug present, the test failed in step 5.
 * Note that the test did fail only on Windows platforms, which is probably
 * because of differences in the file system code.
 */
public void testDerby3875()
        throws SQLException, IOException {
    // Create the database.
    println("Creating database");
    getConnection();
    // Backup the database.
    println("Backing up database");
    String dbBackup = SupportFilesSetup.getReadWrite("dbbackup").getPath();
    CallableStatement cs = prepareCall(
            "CALL SYSCS_UTIL.SYSCS_BACKUP_DATABASE(?)");
    cs.setString(1, dbBackup);
    cs.execute();
    cs.close();
    // Shutdown the database.
    getTestConfiguration().shutdownEngine();

    // Corrupt one of the database files.
    File dataDir = new File("system/" +
            getTestConfiguration().getDefaultDatabaseName(), "seg0");
    File df = new File(dataDir, "c10.dat");
    assertTrue("File to corrupt doesn't exist: " + df.getPath(),
            PrivilegedFileOpsForTests.exists(df));
    println("Corrupting data file");
    byte[] zeros = new byte[(int)PrivilegedFileOpsForTests.length(df)];
    FileOutputStream fout =
            PrivilegedFileOpsForTests.getFileOutputStream(df);
    fout.write(zeros);
    fout.flush();
    fout.close();

    // Reboot the database, which should fail.
    try {
        println("Rebooting corrupted database");
        getConnection();
        fail("Reboot of currupted database should have failed");
    } catch (SQLException sqle) {
        assertSQLState("XJ040", sqle);
    }

    // Now try to restore database.
    println("Restoring database");
    String tmp[] = Utilities.split(
            getTestConfiguration().getDefaultDatabaseName(), '/');
    final String dbName = tmp[tmp.length -1];
    DataSource ds = JDBCDataSource.getDataSource();
    JDBCDataSource.setBeanProperty(ds, "connectionAttributes", 
            ("restoreFrom=" + dbBackup + "/" + dbName ));
    assertNotNull(ds.getConnection());
}
 

public void testBug43576() throws Exception {
    createTable("TMIX91P",
            "(F01SMALLINT         SMALLINT NOT NULL, F02INTEGER          INTEGER,F03REAL             REAL,"
                    + "F04FLOAT            FLOAT,F05NUMERIC31X4      NUMERIC(31,4), F06NUMERIC16X16     NUMERIC(16,16), F07CHAR_10          CHAR(10),"
                    + " F08VARCHAR_10       VARCHAR(10), F09CHAR_20          CHAR(20), F10VARCHAR_20       VARCHAR(20), F11DATE         DATE,"
                    + " F12DATETIME         DATETIME, PRIMARY KEY (F01SMALLINT))");

    this.stmt.executeUpdate("INSERT INTO TMIX91P VALUES (1,1,1234567.12,1234567.12,111111111111111111111111111.1111,.111111111111111,'1234567890',"
            + "'1234567890','CHAR20CHAR20','VARCHAR20ABCD','2001-01-01','2001-01-01 01:01:01.111')");

    this.stmt.executeUpdate("INSERT INTO TMIX91P VALUES (7,1,1234567.12,1234567.12,22222222222.0001,.99999999999,'1234567896','1234567896','CHAR20',"
            + "'VARCHAR20ABCD','2001-01-01','2001-01-01 01:01:01.111')");

    this.stmt.executeUpdate("INSERT INTO TMIX91P VALUES (12,12,1234567.12,1234567.12,111222333.4444,.1234567890,'2234567891','2234567891','CHAR20',"
            + "'VARCHAR20VARCHAR20','2001-01-01','2001-01-01 01:01:01.111')");

    createProcedure("MSQSPR100",
            "\n( p1_in  INTEGER , p2_in  CHAR(20), OUT p3_out INTEGER, OUT p4_out CHAR(11))\nBEGIN "
                    + "\n SELECT F01SMALLINT,F02INTEGER, F11DATE,F12DATETIME,F03REAL \n FROM TMIX91P WHERE F02INTEGER = p1_in; "
                    + "\n SELECT F02INTEGER,F07CHAR_10,F08VARCHAR_10,F09CHAR_20 \n FROM TMIX91P WHERE  F09CHAR_20 = p2_in ORDER BY F02INTEGER ; "
                    + "\n SET p3_out  = 144; \n SET p4_out  = 'CHARACTER11'; \n SELECT p3_out, p4_out; END");

    String sql = "{call MSQSPR100(1,'CHAR20',?,?)}";

    CallableStatement cs = this.conn.prepareCall(sql);

    cs.registerOutParameter(1, Types.INTEGER);
    cs.registerOutParameter(2, Types.CHAR);

    cs.execute();
    cs.close();

    createProcedure("bug43576_1", "(OUT nfact VARCHAR(100), IN ccuenta VARCHAR(100),\nOUT ffact VARCHAR(100),\nOUT fdoc VARCHAR(100))\nBEGIN"
            + "\nSET nfact = 'ncfact string';\nSET ffact = 'ffact string';\nSET fdoc = 'fdoc string';\nEND");

    createProcedure("bug43576_2", "(IN ccuent1 VARCHAR(100), IN ccuent2 VARCHAR(100),\nOUT nfact VARCHAR(100),\nOUT ffact VARCHAR(100),"
            + "\nOUT fdoc VARCHAR(100))\nBEGIN\nSET nfact = 'ncfact string';\nSET ffact = 'ffact string';\nSET fdoc = 'fdoc string';\nEND");

    Properties props = new Properties();
    props.put("jdbcCompliantTruncation", "true");
    props.put("useInformationSchema", "true");
    Connection conn1 = null;
    conn1 = getConnectionWithProps(props);
    try {
        CallableStatement callSt = conn1.prepareCall("{ call bug43576_1(?, ?, ?, ?) }");
        callSt.setString(2, "xxx");
        callSt.registerOutParameter(1, java.sql.Types.VARCHAR);
        callSt.registerOutParameter(3, java.sql.Types.VARCHAR);
        callSt.registerOutParameter(4, java.sql.Types.VARCHAR);
        callSt.execute();

        assertEquals("ncfact string", callSt.getString(1));
        assertEquals("ffact string", callSt.getString(3));
        assertEquals("fdoc string", callSt.getString(4));

        CallableStatement callSt2 = conn1.prepareCall("{ call bug43576_2(?, ?, ?, ?, ?) }");
        callSt2.setString(1, "xxx");
        callSt2.setString(2, "yyy");
        callSt2.registerOutParameter(3, java.sql.Types.VARCHAR);
        callSt2.registerOutParameter(4, java.sql.Types.VARCHAR);
        callSt2.registerOutParameter(5, java.sql.Types.VARCHAR);
        callSt2.execute();

        assertEquals("ncfact string", callSt2.getString(3));
        assertEquals("ffact string", callSt2.getString(4));
        assertEquals("fdoc string", callSt2.getString(5));

        CallableStatement callSt3 = conn1.prepareCall("{ call bug43576_2(?, 'yyy', ?, ?, ?) }");
        callSt3.setString(1, "xxx");
        // callSt3.setString(2, "yyy");
        callSt3.registerOutParameter(2, java.sql.Types.VARCHAR);
        callSt3.registerOutParameter(3, java.sql.Types.VARCHAR);
        callSt3.registerOutParameter(4, java.sql.Types.VARCHAR);
        callSt3.execute();

        assertEquals("ncfact string", callSt3.getString(2));
        assertEquals("ffact string", callSt3.getString(3));
        assertEquals("fdoc string", callSt3.getString(4));
    } finally {
        conn1.close();
    }
}
 

/**
 * 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();
}
 

public void testZeroArgProcedures() throws SQLException {
    Connection conn = getConnection();

    Statement s = createStatement();
    s
            .execute("create procedure za() language java external name 'org.apache.derbyTesting.functionTests.tests.lang.LangProcedureTest.zeroArg' parameter style java");

    s.execute("call za()");
    assertUpdateCountForProcedureWithNoResults(s);

    PreparedStatement ps = prepareStatement("call za()");
    ps.execute();
    assertUpdateCountForProcedureWithNoResults(ps);
    ps.close();

    ps = prepareStatement("{call za()}");
    ps.execute();
    assertUpdateCountForProcedureWithNoResults(ps);
    ps.close();

    try {
        ps = prepareStatement("call za(?)");
        fail("FAIL - prepareStatement call za(?)");
    } catch (SQLException sqle) {
        assertSQLState("42Y03", sqle);
    }

    CallableStatement cs = prepareCall("call za()");
    cs.execute();
    assertUpdateCountForProcedureWithNoResults(cs);
    cs.close();

    cs = prepareCall("{call za()}");
    cs.execute();
    assertUpdateCountForProcedureWithNoResults(cs);
    cs.close();
    String[] sysAliasDefinition = { "APP.ZA AS org.apache.derbyTesting.functionTests.tests.lang.LangProcedureTest.zeroArg() LANGUAGE JAVA PARAMETER STYLE JAVA MODIFIES SQL DATA" };
    String[] dbMetadataDefinition = { "APP.ZA AS org.apache.derbyTesting.functionTests.tests.lang.LangProcedureTest.zeroArg type procedureNoResult" };
    checkMatchingProcedures(conn, "ZA", sysAliasDefinition,
            dbMetadataDefinition, null);
    s.execute("drop procedure za");
    checkMatchingProcedures(conn, "ZA", null, null, null);

    s.close();

}
 

/**
 * This function deletes a global edge comment from the database.
 *
 * @param provider The provider to access the database.
 * @param edge The edge to which the comment is associated.
 * @param commentId The comment id of the comment to be deleted.
 * @param userId The user id of the currently active user.
 *
 * @throws CouldntDeleteException if the comment could not be deleted from the database.
 */
public static void deleteGlobalEdgeComment(final AbstractSQLProvider provider,
    final INaviEdge edge, final Integer commentId, final Integer userId)
    throws CouldntDeleteException {

  Preconditions.checkNotNull(provider, "IE00505: provider argument can not be null");
  Preconditions.checkNotNull(edge, "IE00506: codeNode argument can not be null");
  Preconditions.checkNotNull(commentId, "IE00507: comment argument can not be null");
  Preconditions.checkNotNull(userId, "IE00508: userId argument can not be null");

  final String function = " { ? = call delete_global_edge_comment(?, ?, ?, ?, ?, ?) } ";

  try {
    final CallableStatement deleteCommentFunction =
        provider.getConnection().getConnection().prepareCall(function);

    try {
      deleteCommentFunction.registerOutParameter(1, Types.INTEGER);
      deleteCommentFunction.setInt(2, getModuleId(edge.getSource()));
      deleteCommentFunction.setInt(3, getModuleId(edge.getTarget()));
      deleteCommentFunction.setObject(
          4, ((INaviCodeNode) edge.getSource()).getAddress().toBigInteger(), Types.BIGINT);
      deleteCommentFunction.setObject(
          5, ((INaviCodeNode) edge.getTarget()).getAddress().toBigInteger(), Types.BIGINT);
      deleteCommentFunction.setInt(6, commentId);
      deleteCommentFunction.setInt(7, userId);

      deleteCommentFunction.execute();

      deleteCommentFunction.getInt(1);
      if (deleteCommentFunction.wasNull()) {
        throw new IllegalArgumentException(
            "Error: the comment id returned from the database was null");
      }
    } finally {
      deleteCommentFunction.close();
    }

  } catch (SQLException | MaybeNullException exception) {
    throw new CouldntDeleteException(exception);
  }
}