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

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

public static CallableStatement getStoreProcedureInvocation(Connection conn,int qNum, int startNum, int hopNum, boolean  runStats, boolean distinct, boolean indices, boolean runStatsSampledIndexAll) throws SQLException {
	String procName;// = runStats?"DB2INST2.GENPROC1" : "DB2INST2.GENPROC3";
	
	if (runStats) {
		if (indices) {
			procName = runStatsSampledIndexAll? "DB2INST2.GENPROC4" :"DB2INST2.GENPROC5";
		} else {
			procName =  "DB2INST2.GENPROC1";
		}
	} else {
		procName = "DB2INST2.GENPROC3";
	}	
	CallableStatement cstmt = conn.prepareCall("CALL "+procName+"(?, ?, ?,?,  ?)");
       cstmt.setString(1, getStartingPointStmt(qNum, startNum, hopNum));
       cstmt.setString(2, getRecursionStmt("SESSION.DELTAT", "SESSION.OLDDELTAT", distinct));
       cstmt.setString(3, getRecursionStmt("SESSION.OLDDELTAT", "SESSION.DELTAT", distinct));
     
       
       cstmt.setString(4, getFinalStmt());
       cstmt.setInt(5, hopNum);
       cstmt.execute();
       return cstmt;
}
 

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

/**
 * Helper method for testing getCrossReference - calls the ODBC procedure
 * @throws SQLException
 */
private ResultSet getCrossReferenceODBC(
        String parentcatalog, String parentschema, String parenttable,
        String foreigncatalog, String foreignschema, String foreigntable)
    throws SQLException
{
    CallableStatement cs = prepareCall("CALL SYSIBM.SQLFOREIGNKEYS(" +
            "?, ?, ?, ?, ?, ?, 'DATATYPE=''ODBC''')");

    cs.setString(1, parentcatalog);
    cs.setString(2, parentschema);
    cs.setString(3, parenttable);
    cs.setString(4, foreigncatalog);
    cs.setString(5, foreignschema);
    cs.setString(6, foreigntable);
    cs.execute();
    return cs.getResultSet();
}
 

protected final void executeCallableStatement() throws Exception {

        final int expectedResultSize = 1;
        final int expectedTotalCount = 3;
        final int expectedMatchingId = 2;
        final String outputParamCountName = "outputParamCount";

        Connection conn = null;
        CallableStatement cs = null;
        ResultSet rs = null;
        try {
            conn = DriverManager.getConnection(JDBC_URL, "root", null);

            cs = conn.prepareCall(CALLABLE_STATEMENT_QUERY);
            cs.setString(1, CALLABLE_STATEMENT_INPUT_PARAM);
            cs.registerOutParameter(2, CALLABLE_STATMENT_OUTPUT_PARAM_TYPE);

            rs = cs.executeQuery();
            int resultCount = 0;
            while (rs.next()) {
                ++resultCount;
                if (resultCount > expectedResultSize) {
                    fail();
                }
                assertEquals(expectedMatchingId, rs.getInt(1));
                assertEquals(CALLABLE_STATEMENT_INPUT_PARAM, rs.getString(2));
            }
            assertEquals(expectedResultSize, resultCount);

            final int totalCount = cs.getInt(outputParamCountName);
            assertEquals(expectedTotalCount, totalCount);

        } finally {
            closeResultSet(rs);
            closeStatement(cs);
            closeConnection(conn);
        }
    }
 

public void insertData(String sql, String id, String value) throws SQLException {
    CallableStatement preparedStatement = connection.prepareCall(sql);
    preparedStatement.setString(1, id);
    preparedStatement.setString(2, value);
    preparedStatement.execute();
    preparedStatement.close();
}
 

/**
 * 将TMX的header接点的主要属性写入到mheader表中
 * @throws SQLException
 */
public String insertHeader(Hashtable<String, String> params) throws SQLException {
	CallableStatement stmt = null;
	try {
		String sql = dbConfig.getOperateDbSQL("insert-mheader");
		stmt = conn.prepareCall(sql);
		int i = 1;
		stmt.setString(i++, params.get("CREATIONTOOL"));
		stmt.setString(i++, params.get("CTVERSION"));
		stmt.setString(i++, params.get("TMF"));
		stmt.setString(i++, params.get("SRCLANG"));
		stmt.setString(i++, params.get("ADMINLANG"));
		stmt.setString(i++, params.get("DATATYPE"));
		stmt.setString(i++, params.get("SEGTYPE"));
		stmt.setString(i++, params.get("CREATIONID"));
		stmt.setString(i++, params.get("CREATIONDATE"));
		stmt.setString(i++, params.get("CHANGEID"));
		stmt.setString(i++, params.get("CHANGEDATE"));
		stmt.setString(i++, params.get("ENCODING"));
		stmt.registerOutParameter(i++, Types.INTEGER);
		stmt.execute();
		return stmt.getString(i - 1);
	} finally {
		if (stmt != null) {
			stmt.close();
		}
	}
}
 

public int getTestcasesCount( String whereClause ) throws DatabaseAccessException {

        String sqlLog = new SqlRequestFormatter().add("where", whereClause).format();
        Connection connection = getConnection();
        CallableStatement callableStatement = null;
        ResultSet rs = null;
        try {

            callableStatement = connection.prepareCall("{ call sp_get_testcases_count(?) }");
            callableStatement.setString(1, whereClause);

            rs = callableStatement.executeQuery();
            int testcasesCount = 0;
            while (rs.next()) {
                testcasesCount = rs.getInt("testcasesCount");
                logQuerySuccess(sqlLog, "test cases", testcasesCount);
                break;
            }

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

private void removeJar(String jarName) throws SQLException
{
    CallableStatement cs = prepareCall("CALL SQLJ.REMOVE_JAR(?, 0)");       
    cs.setString(1, jarName);       
    cs.executeUpdate();        
    cs.close();
}
 

public static CallableStatement getStoreProcedureInvocation(Connection conn,int qNum, int startNum, int hopNum, boolean  runStats, boolean distinct, boolean indices, boolean runStatsSampledIndexAll) throws SQLException {
	String procName; // = runStats? "DB2INST2.GENPROC2":"DB2INST2.GENPROC0";
	if (runStats) {
		procName =  "DB2INST2.GENPROC2";
	} else {
		procName = "DB2INST2.GENPROC0";
	}
	//String procName = runStats? "DB2INST2.GENPROC2":"DB2INST2.GENPROC0";
	CallableStatement cstmt = conn.prepareCall("CALL "+procName+"(?, ?, ?, ?)"); cstmt.setString(1, getStartingPointStmt(qNum, startNum, hopNum));
       cstmt.setString(2, getRecursionStmt(distinct));
       cstmt.setString(3, getFinalStmt());
       cstmt.setInt(4, hopNum);
       cstmt.execute();
       return cstmt;
}
 

/**
 * 将TMX的header接点的主要属性写入到mheader表中
 * @throws SQLException
 */
public String insertHeader(Hashtable<String, String> params) throws SQLException {
	CallableStatement stmt = null;
	try {
		String sql = dbConfig.getOperateDbSQL("insert-mheader");
		stmt = conn.prepareCall(sql);
		int i = 1;
		stmt.setString(i++, params.get("CREATIONTOOL"));
		stmt.setString(i++, params.get("CTVERSION"));
		stmt.setString(i++, params.get("TMF"));
		stmt.setString(i++, params.get("SRCLANG"));
		stmt.setString(i++, params.get("ADMINLANG"));
		stmt.setString(i++, params.get("DATATYPE"));
		stmt.setString(i++, params.get("SEGTYPE"));
		stmt.setString(i++, params.get("CREATIONID"));
		stmt.setString(i++, params.get("CREATIONDATE"));
		stmt.setString(i++, params.get("CHANGEID"));
		stmt.setString(i++, params.get("CHANGEDATE"));
		stmt.setString(i++, params.get("ENCODING"));
		stmt.registerOutParameter(i++, Types.INTEGER);
		stmt.execute();
		return stmt.getString(i - 1);
	} finally {
		if (stmt != null) {
			stmt.close();
		}
	}
}
 

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

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

/**
 * This method executes a callable statement to set the database property
 * to a given value. It checks that reset to any value other than "true"
 * fails.
 *
 * @param cs CallableStatement object used to set database property
 * @param value value of database property
 * @throws SQLException
 */
private void testPropertyReset(CallableStatement cs, String value) throws SQLException {

  cs.setString(2, value);

  try {
        cs.executeUpdate();
        if(value.compareToIgnoreCase("true") != 0)
          fail("FAIL: Should not be possible to reset sql authorization once it has been turned on");
      } catch (SQLException sqle) {
        assertSQLState(SQLStateConstants.PROPERTY_UNSUPPORTED_CHANGE, sqle);
      }

}
 

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

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

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

private List<String> generateKeyConstraintsCreateScripts( Connection connection, DbTable table ) {

        List<String> indexesNames = getAllIndexesNamesForTable(connection, table);
        List<String> indexesCreateScripts = new ArrayList<>();

        final String scriptFileName = "generateTableKeyConstraintScript.sql";

        String scriptContent = loadScriptFromClasspath(scriptFileName);

        // create the db procedure
        try {
            createDatabaseProcedure(connection, scriptContent);
            for (String indexName : indexesNames) {
                CallableStatement callableStatement = null;
                ResultSet rs = null;
                try {
                    callableStatement = connection.prepareCall("{ call generateTableKeyConstraintScript(?,?,?) }");
                    callableStatement.setString(1, table.getTableSchema());
                    callableStatement.setString(2, table.getTableName());
                    callableStatement.setString(3, indexName);
                    if (LOG.isTraceEnabled()) {
                        LOG.trace("Executing SQL query: " + callableStatement.toString());
                    }
                    rs = callableStatement.executeQuery();
                    String createQuery = new String();
                    if (rs.next()) {
                        createQuery = rs.getString(1);
                    }
                    if (!StringUtils.isNullOrEmpty(createQuery) && !"NULL".equalsIgnoreCase(createQuery)) {
                        indexesCreateScripts.add(createQuery);
                    }

                } finally {
                    DbUtils.closeStatement(callableStatement);
                }
            }
        } catch (SQLException e) {
            throw new DbException(DbUtils.getFullSqlException("Error while generating Key constraints for table '"
                                                              + table.getFullTableName() + "'", e),
                                  e);
        } finally {
            // drop the newly created procedure
            executeUpdate("DROP PROCEDURE generateTableKeyConstraintScript", connection);
        }
        return indexesCreateScripts;
    }
 

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

public void testCallProcedureWithInAndOutParameter()
   throws Exception  {
  
  setup();
  int number=2;
  CallableStatement cs = prepareCall("CALL PROCEDURE_INOUT_PARAMETERS(?, ?, ?)");
  cs.setInt(1, number);
  cs.registerOutParameter(2, java.sql.Types.VARCHAR);
  cs.setString(2, "INOUT_PARAMETER");
  cs.registerOutParameter(3, java.sql.Types.INTEGER);
  cs.execute();
  
  String[][] results=new String[2][1];
  results[0][0]="1";
  results[1][0]="1";
           
  int rsIndex=-1;
  do {
    ++rsIndex;
    int rowIndex=0;
    ResultSet rs = cs.getResultSet();
    ResultSetMetaData metaData = rs.getMetaData();
    int rowCount = metaData.getColumnCount();
    while (rs.next()) {
      String row="";
      for (int i = 1; i <=rowCount; ++i) {
        Object value = rs.getObject(i);
        row+=value.toString();          
      }
      if(rsIndex>1 || rowIndex>1) {
        fail("the result is not correct!");
      }
      if(!row.equals(results[rsIndex][rowIndex])) {
        fail("the result is not correct!");
      }
      ++rowIndex;
    }
  } while (cs.getMoreResults());    
  
  String outValue=cs.getString(2);
  String outParameter="INOUT_PARAMETER"+"Modified";
  if(!outValue.equals(outParameter)) {
    fail("the out parameter is supposed to "+outParameter+" but "+outValue);
  }
  
  int parameter3=cs.getInt(3);    
  if(parameter3!=number) {
    fail("the out parameter is supposed to "+number+" but "+parameter3);
  }
           
}