Esempio n. 1
0
  private static void executeOutput(CallableStatement cs) throws SQLException {

    for (int p = 1; p <= 5; p++) cs.registerOutParameter(p, Types.INTEGER);

    for (int p = 6; p <= 10; p++) cs.registerOutParameter(p, Types.VARCHAR);

    cs.execute();

    for (int p = 1; p <= 5; p++) {
      System.out.println("  " + p + " = " + cs.getInt(p) + " was null " + cs.wasNull());
    }
    for (int p = 6; p <= 10; p++) {
      System.out.println("  " + p + " = " + cs.getString(p) + " was null " + cs.wasNull());
    }
  }
  public void testGetObjectFloat() throws Throwable {
    try {
      Statement stmt = con.createStatement();
      stmt.execute(createDecimalTab);
      stmt.execute(insertDecimalTab);
      boolean ret = stmt.execute(createFloatProc);
    } catch (Exception ex) {
      fail(ex.getMessage());
      throw ex;
    }
    try {
      CallableStatement cstmt = con.prepareCall("{ call float_proc(?,?,?) }");
      cstmt.registerOutParameter(1, java.sql.Types.FLOAT);
      cstmt.registerOutParameter(2, java.sql.Types.FLOAT);
      cstmt.registerOutParameter(3, java.sql.Types.FLOAT);
      cstmt.executeUpdate();
      Double val = (Double) cstmt.getObject(1);
      assertTrue(val.doubleValue() == doubleValues[0]);

      val = (Double) cstmt.getObject(2);
      assertTrue(val.doubleValue() == doubleValues[1]);

      val = (Double) cstmt.getObject(3);
      assertTrue(cstmt.wasNull());
    } catch (Exception ex) {
      fail(ex.getMessage());
    } finally {
      try {
        Statement dstmt = con.createStatement();
        dstmt.execute(dropFloatProc);
      } catch (Exception ex) {
      }
    }
  }
  public List<UserBean> fetchRecommendedArtistforArtist(String artistId)
      throws ConnectionFailureException, SQLException, IOException {
    List<UserBean> recommdArtistForArtistList = new ArrayList<UserBean>();
    Connection conn = DBManager.getConnection();
    CallableStatement callableStatement = conn.prepareCall("call FETCH_ARTIST_LIST_PROC(?,?,?)");
    callableStatement.registerOutParameter(2, OracleTypes.CURSOR);
    callableStatement.registerOutParameter(3, java.sql.Types.VARCHAR);
    callableStatement.setString(1, artistId);
    callableStatement.execute();

    String errMsg = callableStatement.getString(3);
    ResultSet rs = ((OracleCallableStatement) callableStatement).getCursor(2);
    if (!callableStatement.wasNull()) {
      while (rs.next()) {
        UserBean userBean = new UserBean();
        userBean.setUserid(rs.getString(1));
        userBean.setFirstName(rs.getString(2));
        userBean.setLastName(rs.getString(3));
        userBean.setPhoneNo(rs.getDouble(4));
        userBean.setDob(rs.getDate(5));
        userBean.setCity(rs.getString(6));
        userBean.setState(rs.getString(7));
        recommdArtistForArtistList.add(userBean);
      }
    }
    if (null != errMsg && !errMsg.isEmpty()) {
      ErrorLog.logError(errMsg);
    }

    if (null != rs) rs.close();
    if (null != callableStatement) callableStatement.close();
    if (null != conn) conn.close();
    return recommdArtistForArtistList;
  }
  public void testNumeric() throws Throwable {

    CallableStatement call = con.prepareCall("{ call Numeric_Proc(?,?,?) }");

    call.registerOutParameter(1, Types.NUMERIC, 15);
    call.registerOutParameter(2, Types.NUMERIC, 15);
    call.registerOutParameter(3, Types.NUMERIC, 15);

    call.executeUpdate();
    java.math.BigDecimal ret = call.getBigDecimal(1);
    assertTrue(
        "correct return from getNumeric () should be 999999999999999.000000000000000 but returned "
            + ret.toString(),
        ret.equals(new java.math.BigDecimal("999999999999999.000000000000000")));

    ret = call.getBigDecimal(2);
    assertTrue(
        "correct return from getNumeric ()",
        ret.equals(new java.math.BigDecimal("0.000000000000001")));
    try {
      ret = call.getBigDecimal(3);
    } catch (NullPointerException ex) {
      assertTrue("This should be null", call.wasNull());
    }
  }
Esempio n. 5
0
 @Override
 public Object getResult(CallableStatement cs, int columnIndex) throws SQLException {
   Object sqlDate = cs.getDate(columnIndex);
   if (cs.wasNull()) {
     return null;
   }
   return sqlDate;
 }
Esempio n. 6
0
 public Object getResult(CallableStatement cs, int columnIndex) throws SQLException {
   java.sql.Timestamp sqlTimestamp = cs.getTimestamp(columnIndex);
   if (cs.wasNull()) {
     return null;
   } else {
     return new java.util.Date(sqlTimestamp.getTime());
   }
 }
 @Override
 public E getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
   int i = cs.getInt(columnIndex);
   if (cs.wasNull()) {
     return null;
   } else {
     return convert(i);
   }
 }
 public boolean wasNull() throws SQLException {
   checkOpen();
   try {
     return _stmt.wasNull();
   } catch (SQLException e) {
     handleException(e);
     return false;
   }
 }
Esempio n. 9
0
 @Override
 public T getResult(CallableStatement cs, int columnIndex) throws SQLException {
   T result = getNullableResult(cs, columnIndex);
   if (cs.wasNull()) {
     return null;
   } else {
     return result;
   }
 }
Esempio n. 10
0
 public Object getResult(CallableStatement cs, int columnIndex)
     throws SQLException {
   short s = cs.getShort(columnIndex);
   if (cs.wasNull()) {
     return null;
   } else {
     return new Short(s);
   }
 }
 @Override
 public Object getResult(CallableStatement cs, int columnIndex) throws SQLException {
   String s = cs.getString(columnIndex);
   if (cs.wasNull()) {
     return null;
   } else {
     Character c = s.charAt(0);
     return new Character(c);
   }
 }
Esempio n. 12
0
  /** Returns true if the last out parameter was null. */
  @Override
  public boolean wasNull() throws SQLException {
    try {
      return _cstmt.wasNull();
    } catch (SQLException e) {
      onSqlException(e);

      throw e;
    } catch (RuntimeException e) {
      onRuntimeException(e);

      throw e;
    }
  }
  public void testInOut() throws Throwable {
    try {
      Statement stmt = con.createStatement();
      stmt.execute(createBitTab);
      stmt.execute(insertBitTab);
      boolean ret =
          stmt.execute(
              "create or replace function "
                  + "insert_bit( inout IMAX boolean, inout IMIN boolean, inout INUL boolean)  as "
                  + "'begin "
                  + "insert into bit_tab values( imax, imin, inul);"
                  + "select max_val into imax from bit_tab;"
                  + "select min_val into imin from bit_tab;"
                  + "select null_val into inul from bit_tab;"
                  + " end;' "
                  + "language plpgsql;");
    } catch (Exception ex) {
      fail(ex.getMessage());
      throw ex;
    }
    try {
      CallableStatement cstmt = con.prepareCall("{ call insert_bit(?,?,?) }");
      cstmt.setObject(1, "true", Types.BIT);
      cstmt.setObject(2, "false", Types.BIT);
      cstmt.setNull(3, Types.BIT);
      cstmt.registerOutParameter(1, Types.BIT);
      cstmt.registerOutParameter(2, Types.BIT);
      cstmt.registerOutParameter(3, Types.BIT);
      cstmt.executeUpdate();

      assertTrue(cstmt.getBoolean(1));
      assertFalse(cstmt.getBoolean(2));
      cstmt.getBoolean(3);
      assertTrue(cstmt.wasNull());
    } finally {
      try {
        Statement dstmt = con.createStatement();
        dstmt.execute("drop function insert_bit(boolean, boolean, boolean)");
      } catch (Exception ex) {
      }
    }
  }
 public void testGetInt01() throws Throwable {
   try {
     Statement stmt = con.createStatement();
     stmt.execute("create temp table i_tab ( max_val int, min_val int, null_val int )");
     stmt.execute("insert into i_tab values (2147483647,-2147483648,null)");
     boolean ret =
         stmt.execute(
             "create or replace function "
                 + "int_proc( OUT IMAX int, OUT IMIN int, OUT INUL int)  as "
                 + "'begin "
                 + "select max_val into imax from i_tab;"
                 + "select min_val into imin from i_tab;"
                 + "select null_val into inul from i_tab;"
                 + " end;' "
                 + "language plpgsql;");
   } catch (Exception ex) {
     fail(ex.getMessage());
     throw ex;
   }
   try {
     CallableStatement cstmt = con.prepareCall("{ call int_proc(?,?,?) }");
     cstmt.registerOutParameter(1, java.sql.Types.INTEGER);
     cstmt.registerOutParameter(2, java.sql.Types.INTEGER);
     cstmt.registerOutParameter(3, java.sql.Types.INTEGER);
     cstmt.executeUpdate();
     assertTrue(cstmt.getInt(1) == 2147483647);
     assertTrue(cstmt.getInt(2) == -2147483648);
     cstmt.getInt(3);
     assertTrue(cstmt.wasNull());
   } catch (Exception ex) {
     fail(ex.getMessage());
   } finally {
     try {
       Statement dstmt = con.createStatement();
       dstmt.execute("drop function int_proc()");
     } catch (Exception ex) {
     }
   }
 }
 public void testGetByte01() throws Throwable {
   try {
     Statement stmt = con.createStatement();
     stmt.execute("create temp table byte_tab ( max_val int2, min_val int2, null_val int2 )");
     stmt.execute("insert into byte_tab values (127,-128,null)");
     boolean ret =
         stmt.execute(
             "create or replace function "
                 + "byte_proc( OUT IMAX int2, OUT IMIN int2, OUT INUL int2)  as "
                 + "'begin "
                 + "select max_val into imax from byte_tab;"
                 + "select min_val into imin from byte_tab;"
                 + "select null_val into inul from byte_tab;"
                 + " end;' "
                 + "language plpgsql;");
   } catch (Exception ex) {
     fail(ex.getMessage());
     throw ex;
   }
   try {
     CallableStatement cstmt = con.prepareCall("{ call byte_proc(?,?,?) }");
     cstmt.registerOutParameter(1, java.sql.Types.TINYINT);
     cstmt.registerOutParameter(2, java.sql.Types.TINYINT);
     cstmt.registerOutParameter(3, java.sql.Types.TINYINT);
     cstmt.executeUpdate();
     assertTrue(cstmt.getByte(1) == 127);
     assertTrue(cstmt.getByte(2) == -128);
     cstmt.getByte(3);
     assertTrue(cstmt.wasNull());
   } catch (Exception ex) {
     fail(ex.getMessage());
   } finally {
     try {
       Statement dstmt = con.createStatement();
       dstmt.execute("drop function byte_proc()");
     } catch (Exception ex) {
     }
   }
 }
 public void testGetBoolean01() throws Throwable {
   try {
     Statement stmt = con.createStatement();
     stmt.execute(createBitTab);
     stmt.execute(insertBitTab);
     boolean ret =
         stmt.execute(
             "create or replace function "
                 + "bit_proc( OUT IMAX boolean, OUT IMIN boolean, OUT INUL boolean)  as "
                 + "'begin "
                 + "select max_val into imax from bit_tab;"
                 + "select min_val into imin from bit_tab;"
                 + "select null_val into inul from bit_tab;"
                 + " end;' "
                 + "language plpgsql;");
   } catch (Exception ex) {
     fail(ex.getMessage());
     throw ex;
   }
   try {
     CallableStatement cstmt = con.prepareCall("{ call bit_proc(?,?,?) }");
     cstmt.registerOutParameter(1, java.sql.Types.BIT);
     cstmt.registerOutParameter(2, java.sql.Types.BIT);
     cstmt.registerOutParameter(3, java.sql.Types.BIT);
     cstmt.executeUpdate();
     assertTrue(cstmt.getBoolean(1));
     assertFalse(cstmt.getBoolean(2));
     cstmt.getBoolean(3);
     assertTrue(cstmt.wasNull());
   } catch (Exception ex) {
     fail(ex.getMessage());
   } finally {
     try {
       Statement dstmt = con.createStatement();
       dstmt.execute("drop function bit_proc()");
     } catch (Exception ex) {
     }
   }
 }
 public void testGetLong01() throws Throwable {
   try {
     Statement stmt = con.createStatement();
     stmt.execute("create temp table l_tab ( max_val int8, min_val int8, null_val int8 )");
     stmt.execute("insert into l_tab values (9223372036854775807,-9223372036854775808,null)");
     boolean ret =
         stmt.execute(
             "create or replace function "
                 + "bigint_proc( OUT IMAX int8, OUT IMIN int8, OUT INUL int8)  as "
                 + "'begin "
                 + "select max_val into imax from l_tab;"
                 + "select min_val into imin from l_tab;"
                 + "select null_val into inul from l_tab;"
                 + " end;' "
                 + "language plpgsql;");
   } catch (Exception ex) {
     fail(ex.getMessage());
     throw ex;
   }
   try {
     CallableStatement cstmt = con.prepareCall("{ call bigint_proc(?,?,?) }");
     cstmt.registerOutParameter(1, java.sql.Types.BIGINT);
     cstmt.registerOutParameter(2, java.sql.Types.BIGINT);
     cstmt.registerOutParameter(3, java.sql.Types.BIGINT);
     cstmt.executeUpdate();
     assertTrue(cstmt.getLong(1) == 9223372036854775807L);
     assertTrue(cstmt.getLong(2) == -9223372036854775808L);
     cstmt.getLong(3);
     assertTrue(cstmt.wasNull());
   } catch (Exception ex) {
     fail(ex.getMessage());
   } finally {
     try {
       Statement dstmt = con.createStatement();
       dstmt.execute("drop function bigint_proc()");
     } catch (Exception ex) {
     }
   }
 }
 public void testGetDoubleAsReal() throws Throwable {
   try {
     Statement stmt = con.createStatement();
     stmt.execute("create temp table d_tab ( max_val float, min_val float, null_val float )");
     stmt.execute("insert into d_tab values (3.4E38,1.4E-45,null)");
     boolean ret =
         stmt.execute(
             "create or replace function "
                 + "double_proc( OUT IMAX float, OUT IMIN float, OUT INUL float)  as "
                 + "'begin "
                 + "select max_val into imax from d_tab;"
                 + "select min_val into imin from d_tab;"
                 + "select null_val into inul from d_tab;"
                 + " end;' "
                 + "language plpgsql;");
   } catch (Exception ex) {
     fail(ex.getMessage());
     throw ex;
   }
   try {
     CallableStatement cstmt = con.prepareCall("{ call double_proc(?,?,?) }");
     cstmt.registerOutParameter(1, java.sql.Types.REAL);
     cstmt.registerOutParameter(2, java.sql.Types.REAL);
     cstmt.registerOutParameter(3, java.sql.Types.REAL);
     cstmt.executeUpdate();
     assertTrue(cstmt.getFloat(1) == 3.4E38f);
     assertTrue(cstmt.getFloat(2) == 1.4E-45f);
     cstmt.getFloat(3);
     assertTrue(cstmt.wasNull());
   } catch (Exception ex) {
     fail(ex.getMessage());
   } finally {
     try {
       Statement dstmt = con.createStatement();
       dstmt.execute("drop function double_proc()");
     } catch (Exception ex) {
     }
   }
 }
  /** Test of wasNull method, of inteface java.sql.CallableStatement. */
  public void testWasNull() throws Exception {
    println("wasNull");

    if (!isTestOutParameters()) {
      return;
    }

    CallableStatement stmt = prepareCall("{?= call cast(null as integer)}");

    try {
      boolean expResult = true;

      stmt.registerOutParameter(1, Types.INTEGER);
      stmt.execute();
      stmt.getInt(1);

      boolean result = stmt.wasNull();

      assertEquals(expResult, result);
    } catch (Exception e) {
      fail(e.getMessage());
    }
  }
Esempio n. 20
0
  public void retrieveOutVariables(int ColIndex, cfSession _Session, CallableStatement _stmt)
      throws SQLException, cfmRunTimeException {
    boolean b;
    byte[] bin;
    int i;
    long l;
    double dbl;
    float flt;
    java.sql.Date dt;
    java.sql.Time t;
    Timestamp ts;
    ResultSet rs;
    String str;
    cfData outData = null;

    if (!isOUT()) return;

    switch (cfSqlType) {
      case CF_SQL_BIT:
        // With the Oracle JDBC driver, if we set the parameters using named parameters then
        // we must retrieve them using named parameters too.
        if (useNamedParameters) {
          b = _stmt.getBoolean(paramName);
        } else {
          b = _stmt.getBoolean(ColIndex);
        }
        if (!_stmt.wasNull()) outData = cfBooleanData.getcfBooleanData(b);
        break;

      case CF_SQL_BINARY:
      case CF_SQL_VARBINARY:
      case CF_SQL_BLOB:
        // With the Oracle JDBC driver, if we set the parameters using named parameters then
        // we must retrieve them using named parameters too.
        if (useNamedParameters) {
          bin = _stmt.getBytes(paramName);
        } else {
          bin = _stmt.getBytes(ColIndex);
        }

        if ((!_stmt.wasNull()) && (bin != null)) {
          outData = new cfBinaryData(bin);
        }
        break;

      case CF_SQL_SMALLINT:
      case CF_SQL_INTEGER:
      case CF_SQL_TINYINT:
        try {
          // With the Oracle JDBC driver, if we set the parameters using named parameters then
          // we must retrieve them using named parameters too.
          if (useNamedParameters) {
            i = _stmt.getInt(paramName);
          } else {
            i = _stmt.getInt(ColIndex);
          }

          if (!_stmt.wasNull()) outData = new cfNumberData(i);
        } catch (NumberFormatException e) {
          // With JDK 1.3 and the JDBC-ODBC bridge, the getInt() method will
          // throw a number format exception for in/out params so just ignore it.
          // Ignoring it allows us to retrieve the out param values.
        }
        break;

      case CF_SQL_BIGINT:
        // With the Oracle JDBC driver, if we set the parameters using named parameters then
        // we must retrieve them using named parameters too.
        if (useNamedParameters) {
          l = _stmt.getLong(paramName);
        } else {
          l = _stmt.getLong(ColIndex);
        }

        if (!_stmt.wasNull()) outData = new cfNumberData(l);
        break;

      case CF_SQL_DECIMAL:
      case CF_SQL_NUMERIC:
        dbl = getBigDecimalAsDouble(_stmt, useNamedParameters, paramName, ColIndex);
        if (!_stmt.wasNull()) outData = new cfNumberData(dbl);
        break;

      case CF_SQL_DOUBLE:
      case CF_SQL_FLOAT:
      case CF_SQL_MONEY:
      case CF_SQL_MONEY4:
        // With the Oracle JDBC driver, if we set the parameters using named parameters then
        // we must retrieve them using named parameters too.
        if (useNamedParameters) {
          dbl = _stmt.getDouble(paramName);
        } else {
          dbl = _stmt.getDouble(ColIndex);
        }

        if (!_stmt.wasNull()) outData = new cfNumberData(dbl);
        break;

      case CF_SQL_REAL:
        // With the Oracle JDBC driver, if we set the parameters using named parameters then
        // we must retrieve them using named parameters too.
        if (useNamedParameters) {
          flt = _stmt.getFloat(paramName);
        } else {
          flt = _stmt.getFloat(ColIndex);
        }

        // For some reason casting a float to a double doesn't return a double
        // that exactly matches the original float so we'll use the less efficient
        // algorithm of converting the float to a string and the string to a double.
        // If for some reason this fails then we'll revert to casting the float to
        // a double.
        if (!_stmt.wasNull()) {
          try {
            dbl = Double.valueOf(Float.toString(flt)).doubleValue();
          } catch (Exception e) {
            dbl = (double) flt;
          }
          outData = new cfNumberData(dbl);
        }
        break;

      case CF_SQL_DATE:
        // With the Oracle JDBC driver, if we set the parameters using named parameters then
        // we must retrieve them using named parameters too.
        if (useNamedParameters) {
          dt = _stmt.getDate(paramName);
        } else {
          dt = _stmt.getDate(ColIndex);
        }

        if ((!_stmt.wasNull()) && (dt != null)) {
          outData = new cfDateData(dt);
        }
        break;

      case CF_SQL_TIME:
        // With the Oracle JDBC driver, if we set the parameters using named parameters then
        // we must retrieve them using named parameters too.
        if (useNamedParameters) {
          t = _stmt.getTime(paramName);
        } else {
          t = _stmt.getTime(ColIndex);
        }

        if ((!_stmt.wasNull()) && (t != null)) {
          outData = new cfDateData(t);
        }
        break;

      case CF_SQL_TIMESTAMP:
        try {
          // With the Oracle JDBC driver, if we set the parameters using named parameters then
          // we must retrieve them using named parameters too.
          if (useNamedParameters) {
            ts = _stmt.getTimestamp(paramName);
          } else {
            ts = _stmt.getTimestamp(ColIndex);
          }

          if ((!_stmt.wasNull()) && (ts != null)) {
            outData = new cfDateData(ts);
          }
        } catch (NullPointerException e) {
          // With JDK 1.3 and the JDBC-ODBC bridge, the getTimestamp() method will
          // throw a null ptr exception when the underlying value is null so just ignore it.
        }
        break;

      case CF_SQL_REFCURSOR:
        // This CF SQL Type is only used with Oracle for result sets returned by a
        // stored procedure.

        // With the Oracle JDBC driver, if we set the parameters using named parameters then
        // we must retrieve them using named parameters too.
        if (useNamedParameters) {
          rs = (ResultSet) _stmt.getObject(paramName);
        } else {
          rs = (ResultSet) _stmt.getObject(ColIndex);
        }

        if ((!_stmt.wasNull()) && (rs != null)) {
          outData = new cfQueryResultData(rs, "Stored Procedure", maxLength);
        }
        break;

      default:
        // With the Oracle JDBC driver, if we set the parameters using named parameters then
        // we must retrieve them using named parameters too.
        if (useNamedParameters) {
          str = _stmt.getString(paramName);
        } else {
          str = _stmt.getString(ColIndex);
        }

        if ((!_stmt.wasNull()) && (str != null)) {
          outData = new cfStringData(str);
        }
        break;
    }

    _Session.setData(outVariable, (outData == null ? cfNullData.NULL : outData));
  }
Esempio n. 21
0
 private static <T> T checkWasNull(CallableStatement statement, T value) throws SQLException {
   return statement.wasNull() ? null : value;
 }
 public boolean wasNull() throws SQLException {
   return passThru.wasNull();
 }
 /** @see java.sql.CallableStatement#wasNull() */
 public boolean wasNull() throws SQLException {
   return original.wasNull();
 }
Esempio n. 24
0
 /** {@inheritDoc} */
 @Override
 public Object get(CallableStatement cs, int index) throws SQLException {
   Object result = cs.getBoolean(index);
   if (cs.wasNull()) return null;
   else return result;
 }