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

/** This method is a wrapper for the CallableStatement method getBigDecimal(int parameterIndex).
 * The wrapper method needs the parameterType as an input since ParameterMetaData is not available in JSR169.
 * 
 * @param cs CallableStatement 
 * @param parameterIndex Parameter Index
 * @param parameterType Parameter Type
 * @return String value of getXXX(parameterIndex)method on the CallableStatement
 * @throws SQLException
 */
public static String getBigDecimalString(CallableStatement cs, int parameterIndex, int parameterType) throws SQLException{
	String bigDecimalString = null;
	
	switch(representation){
		case BIGDECIMAL_REPRESENTATION:
			//Call toString() only for non-null values, else return null
			if(cs.getBigDecimal(parameterIndex) != null)
				bigDecimalString = cs.getBigDecimal(parameterIndex).toString();
			break;
		case STRING_REPRESENTATION:
			bigDecimalString = cs.getString(parameterIndex);
			if((bigDecimalString != null) && !canConvertToDecimal(parameterType))
				throw new SQLException("Invalid data conversion. Method not called.");
			break;
		default:	
			new Exception("Failed: Invalid Big Decimal representation").printStackTrace();
	}
	return bigDecimalString;
}
 

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

/** This method is a wrapper for the CallableStatement method getBigDecimal(int parameterIndex).
 * The wrapper method needs the parameterType as an input since ParameterMetaData is not available in JSR169.
 * 
 * @param cs CallableStatement 
 * @param parameterIndex Parameter Index
 * @param parameterType Parameter Type
 * @return String value of getXXX(parameterIndex)method on the CallableStatement
 * @throws SQLException
 */
public static String getBigDecimalString(CallableStatement cs, int parameterIndex, int parameterType) throws SQLException{
	String bigDecimalString = null;
	
	switch(representation){
		case BIGDECIMAL_REPRESENTATION:
			//Call toString() only for non-null values, else return null
			if(cs.getBigDecimal(parameterIndex) != null)
				bigDecimalString = cs.getBigDecimal(parameterIndex).toString();
			break;
		case STRING_REPRESENTATION:
			bigDecimalString = cs.getString(parameterIndex);
			if((bigDecimalString != null) && !canConvertToDecimal(parameterType))
				throw new SQLException("Invalid data conversion. Method not called.");
			break;
		default:	
			new Exception("Failed: Invalid Big Decimal representation").printStackTrace();
	}
	return bigDecimalString;
}
 

private void lookupDefaultSchema(DatabaseMetaData databaseMetaData) {
	try {
		CallableStatement cstmt = null;
		try {
			cstmt = databaseMetaData.getConnection().prepareCall("{? = call sys_context('USERENV', 'CURRENT_SCHEMA')}");
			cstmt.registerOutParameter(1, Types.VARCHAR);
			cstmt.execute();
			this.defaultSchema = cstmt.getString(1);
		}
		finally {
			if (cstmt != null) {
				cstmt.close();
			}
		}
	}
	catch (Exception ignore) {
	}
}
 

/** This method is a wrapper for the CallableStatement method getBigDecimal(int parameterIndex).
 * The wrapper method needs the parameterType as an input since ParameterMetaData is not available in JSR169.
 * 
 * @param cs CallableStatement 
 * @param parameterIndex Parameter Index
 * @param parameterType Parameter Type
 * @return String value of getXXX(parameterIndex)method on the CallableStatement
 * @throws SQLException
 */
public static String getBigDecimalString(CallableStatement cs, int parameterIndex, int parameterType) throws SQLException{
	String bigDecimalString = null;
	
	switch(representation){
		case BIGDECIMAL_REPRESENTATION:
			//Call toString() only for non-null values, else return null
			if(cs.getBigDecimal(parameterIndex) != null)
				bigDecimalString = cs.getBigDecimal(parameterIndex).toString();
			break;
		case STRING_REPRESENTATION:
			bigDecimalString = cs.getString(parameterIndex);
			if((bigDecimalString != null) && !canConvertToDecimal(parameterType))
				throw new SQLException("Invalid data conversion. Method not called.");
			break;
		default:	
			new Exception("Failed: Invalid Big Decimal representation").printStackTrace();
	}
	return bigDecimalString;
}
 

@Override
public JsonNode getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
    String jsonSource = cs.getString(columnIndex);
    if (jsonSource == null) {
        return null;
    }
    try {
        JsonNode jsonNode = mapper.readTree(jsonSource);
        return jsonNode;
    } catch (IOException e) {
        e.printStackTrace();
    }
    return null;
}
 

/**
 * 将数据写入到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 static void testPostrgreSqlStoredProcedures(Connection conn) throws SQLException {
CallableStatement upperProc = conn.prepareCall("{ ? = call upper( ? ) }");
upperProc.registerOutParameter(1, Types.VARCHAR);
upperProc.setString(2, "lowercase to uppercase");
upperProc.executeUpdate();
String upperCased = upperProc.getString(1);
upperProc.close();

System.out.println("upperCased: " + upperCased);
   }
 

@Override
public TreeNode getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
    String jsonSource = cs.getString(columnIndex);
    return fromString(jsonSource);
}
 

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

public static void sqlControl4(int sqlc, String[] e1, String[] e2,
        String[] e3, String[] e4, String[] e5, String[] e6, String[] e7,
        String[] e8) throws SQLException {

    Connection conn = DriverManager
            .getConnection("jdbc:default:connection");

    String sql = "CALL SQLC.SQLCONTROL2_" + sqlc
            + " (?, ?, ?, ?, ?, ?, ?) ";

    e1[0] = sql;

    CallableStatement cs1 = conn.prepareCall(sql);
    try {
        for (int rop = 1; rop <= 7; rop++) {
            cs1.registerOutParameter(rop, Types.VARCHAR);
        }
        cs1.execute();

        e2[0] = cs1.getString(1);
        e3[0] = cs1.getString(2);
        e4[0] = cs1.getString(3);
        e5[0] = cs1.getString(4);
        e6[0] = cs1.getString(5);
        e7[0] = cs1.getString(6);
        e8[0] = cs1.getString(7);
    } catch (SQLException sqle) {
        StringBuilder sb = new StringBuilder(128);
        sb.append("STATE");
        do {
            sb.append("-");
            String ss = sqle.getSQLState();
            if (ss == null)
                ss = "?????";
            sb.append(ss);
            sqle = sqle.getNextException();
        } while (sqle != null);
        e2[0] = sb.toString();
    }

    cs1.close();

    conn.close();

}
 

@Override
public String getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
	return cs.getString(columnIndex);
}
 

/**
 * Returns the value of the OUT parameter for a CallableStatement using parameter name.
 * @param callableStatement
 * @param outParameterName
 * @param paramType
 * @return
 * @throws SQLException
 */
public static String callableStatementGetStringValue(CallableStatement callableStatement, String outParameterName,
        String paramType) throws SQLException {

    String outParamValue = null;

    if (paramType.equalsIgnoreCase(AceQLTypes.CHAR) || paramType.equalsIgnoreCase(AceQLTypes.CHARACTER)
    	|| paramType.equalsIgnoreCase(AceQLTypes.VARCHAR)) {
        outParamValue = callableStatement.getString(outParameterName);
    } else if (paramType.equalsIgnoreCase(AceQLTypes.DECIMAL) || paramType.equalsIgnoreCase(AceQLTypes.NUMERIC)) {

        outParamValue = "" + callableStatement.getBigDecimal(outParameterName);

    } else if (paramType.equalsIgnoreCase(AceQLTypes.BIT)) {

        outParamValue = "" + callableStatement.getBoolean(outParameterName);

    } else if (paramType.equalsIgnoreCase(AceQLTypes.TINYINT) || paramType.equalsIgnoreCase(AceQLTypes.SMALLINT)
    	|| paramType.equalsIgnoreCase(AceQLTypes.INTEGER)) {

        outParamValue = "" + callableStatement.getInt(outParameterName);

    }
    // BIGINT Long
    // REAL Float
    // FLOAT Double
    // DOUBLE PRECISION Double
    else if (paramType.equalsIgnoreCase(AceQLTypes.BIGINT)) {

        outParamValue = "" + callableStatement.getLong(outParameterName);

    } else if (paramType.equalsIgnoreCase(AceQLTypes.REAL)) {

        outParamValue = "" + callableStatement.getFloat(outParameterName);

    } else if (paramType.equalsIgnoreCase(AceQLTypes.FLOAT)
    	|| paramType.equalsIgnoreCase(AceQLTypes.DOUBLE_PRECISION)) {

        outParamValue = "" + callableStatement.getDouble(outParameterName);

    }
    // DATE java.sql.Date
    // TIME java.sql.Time
    // TIMESTAMP java.sql.Timestamp
    else if (paramType.equalsIgnoreCase(AceQLTypes.DATE)) {
        outParamValue = "" + callableStatement.getDate(outParameterName);

    } else if (paramType.equalsIgnoreCase(AceQLTypes.TIME)) {
        outParamValue = "" + callableStatement.getTime(outParameterName);

    } else if (paramType.equalsIgnoreCase(AceQLTypes.TIMESTAMP)) {
        outParamValue = "" + callableStatement.getTimestamp(outParameterName);
    } else if (paramType.equalsIgnoreCase(AceQLTypes.URL)) {

        outParamValue = "" + callableStatement.getURL(outParameterName);

    } else {
        throw new IllegalArgumentException(
    	    "Invalid OUT parameter type: " + paramType + " for parameter index " + outParameterName + ".");
    }
    return outParamValue;
}
 

@Override
public String getNullableResult(CallableStatement cs, int columnIndex)
    throws SQLException {
  return cs.getString(columnIndex);
}
 

@Override
public String getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
	System.out.println("MyStringTypeHandler->getNullableResult");
	return cs.getString(columnIndex);
}
 

@Override
public ResourceRef getResult(CallableStatement cs, int columnIndex) throws SQLException {
    String id = cs.getString(columnIndex);
    return new ResourceRef(id);
}
 

public void testDataAwareProcedureWithInAndOutParameter() throws SQLException {

    setup();
    int number = 2;
    CallableStatement cs = prepareCall("CALL PROCEDURE_INOUT_PARAMETERS(?, ?, ?) ON TABLE EMP.PARTITIONTESTTABLE WHERE SECONDID=4 AND THIRDID='3'");
    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);
    }

  }
 

@Override
public E getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
  String s = cs.getString(columnIndex);
  return s == null ? null : Enum.valueOf(type, s);
}
 

@Override
public ByteArrayRef getResult(CallableStatement cs, int columnIndex) throws SQLException {
  String id = cs.getString(columnIndex);
  return new ByteArrayRef(id);
}
 

@Override
public String getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
	return cs.getString(columnIndex);
}