private void checkPrepareBindExecuteFetchDate(Connection connection) throws Exception {
    final String sql0 = "select cast(? as varchar(20)) as c\n" + "from (values (1, 'a'))";
    final String sql1 = "select ? + interval '2' day as c from (values (1, 'a'))";

    final Date date = Date.valueOf("2015-04-08");
    final long time = date.getTime();

    PreparedStatement ps;
    ParameterMetaData parameterMetaData;
    ResultSet resultSet;

    ps = connection.prepareStatement(sql0);
    parameterMetaData = ps.getParameterMetaData();
    assertThat(parameterMetaData.getParameterCount(), equalTo(1));
    ps.setDate(1, date);
    resultSet = ps.executeQuery();
    assertThat(resultSet.next(), is(true));
    assertThat(resultSet.getString(1), is("2015-04-08"));

    ps.setTimestamp(1, new Timestamp(time));
    resultSet = ps.executeQuery();
    assertThat(resultSet.next(), is(true));
    assertThat(resultSet.getString(1), is("2015-04-08 00:00:00.0"));

    ps.setTime(1, new Time(time));
    resultSet = ps.executeQuery();
    assertThat(resultSet.next(), is(true));
    assertThat(resultSet.getString(1), is("00:00:00"));
    ps.close();

    ps = connection.prepareStatement(sql1);
    parameterMetaData = ps.getParameterMetaData();
    assertThat(parameterMetaData.getParameterCount(), equalTo(1));

    ps.setDate(1, date);
    resultSet = ps.executeQuery();
    assertTrue(resultSet.next());
    assertThat(resultSet.getDate(1), equalTo(new Date(time + TimeUnit.DAYS.toMillis(2))));
    assertThat(resultSet.getTimestamp(1), equalTo(new Timestamp(time + TimeUnit.DAYS.toMillis(2))));

    ps.setTimestamp(1, new Timestamp(time));
    resultSet = ps.executeQuery();
    assertTrue(resultSet.next());
    assertThat(resultSet.getTimestamp(1), equalTo(new Timestamp(time + TimeUnit.DAYS.toMillis(2))));
    assertThat(resultSet.getTimestamp(1), equalTo(new Timestamp(time + TimeUnit.DAYS.toMillis(2))));

    ps.setObject(1, new java.util.Date(time));
    resultSet = ps.executeQuery();
    assertTrue(resultSet.next());
    assertThat(resultSet.getDate(1), equalTo(new Date(time + TimeUnit.DAYS.toMillis(2))));
    assertThat(resultSet.getTimestamp(1), equalTo(new Timestamp(time + TimeUnit.DAYS.toMillis(2))));

    resultSet.close();
    ps.close();
    connection.close();
  }
 public ParameterMetaData getParameterMetaData() throws SQLException {
   try {
     return wrapped.getParameterMetaData();
   } catch (SQLException e) {
     throw new UcanaccessSQLException(e);
   }
 }
  public synchronized int getIndexOf(
      WebSocketMessageDTO criteria, List<Integer> opcodes, List<Integer> inScopeChannelIds)
      throws DatabaseException {
    try {
      String query =
          "SELECT COUNT(m.message_id) "
              + "FROM websocket_message AS m "
              + "LEFT OUTER JOIN websocket_message_fuzz f "
              + "ON m.message_id = f.message_id AND m.channel_id = f.channel_id "
              + "<where> AND m.message_id < ?";
      PreparedStatement stmt =
          buildMessageCriteriaStatement(query, criteria, opcodes, inScopeChannelIds);

      int paramsCount = stmt.getParameterMetaData().getParameterCount();
      stmt.setInt(paramsCount, criteria.id);

      try {
        return executeAndGetSingleIntValue(stmt);
      } finally {
        stmt.close();
      }
    } catch (SQLException e) {
      throw new DatabaseException(e);
    }
  }
 private void checkStatementExecuteQuery(Connection connection, boolean prepare)
     throws SQLException {
   final String sql =
       "select * from (\n" + "  values (1, 'a'), (null, 'b'), (3, 'c')) as t (c1, c2)";
   final Statement statement;
   final ResultSet resultSet;
   final ParameterMetaData parameterMetaData;
   if (prepare) {
     final PreparedStatement ps = connection.prepareStatement(sql);
     statement = ps;
     parameterMetaData = ps.getParameterMetaData();
     resultSet = ps.executeQuery();
   } else {
     statement = connection.createStatement();
     parameterMetaData = null;
     resultSet = statement.executeQuery(sql);
   }
   if (parameterMetaData != null) {
     assertThat(parameterMetaData.getParameterCount(), equalTo(0));
   }
   final ResultSetMetaData metaData = resultSet.getMetaData();
   assertEquals(2, metaData.getColumnCount());
   assertEquals("C1", metaData.getColumnName(1));
   assertEquals("C2", metaData.getColumnName(2));
   assertTrue(resultSet.next());
   assertTrue(resultSet.next());
   assertTrue(resultSet.next());
   assertFalse(resultSet.next());
   resultSet.close();
   statement.close();
   connection.close();
 }
Esempio n. 5
0
  private boolean checkExistenceByQuery(
      PreparedStatement pstmt, BaseSchema baseSchema, String... params) throws SQLException {
    int paramIdx = 1;
    boolean result = false;

    if (baseSchema.getSchemaName() != null && !baseSchema.getSchemaName().isEmpty()) {
      pstmt.setString(paramIdx++, baseSchema.getSchemaName().toUpperCase());
    }

    for (; paramIdx <= pstmt.getParameterMetaData().getParameterCount(); paramIdx++) {
      pstmt.setString(paramIdx, params[paramIdx - 1].toUpperCase());
    }

    ResultSet rs = null;
    try {
      rs = pstmt.executeQuery();
      while (rs.next()) {
        if (rs.getString(1).toUpperCase().equals(params[params.length - 1].toUpperCase())) {
          result = true;
          break;
        }
      }
    } finally {
      CatalogUtil.closeQuietly(rs);
    }

    return result;
  }
  private void testParameterMetaData(Connection conn) throws SQLException {
    PreparedStatement prep = conn.prepareStatement("SELECT ?, ?, ? FROM DUAL");
    ParameterMetaData pm = prep.getParameterMetaData();
    assertEquals("java.lang.String", pm.getParameterClassName(1));
    assertEquals("VARCHAR", pm.getParameterTypeName(1));
    assertEquals(3, pm.getParameterCount());
    assertEquals(ParameterMetaData.parameterModeIn, pm.getParameterMode(1));
    assertEquals(Types.VARCHAR, pm.getParameterType(1));
    assertEquals(0, pm.getPrecision(1));
    assertEquals(0, pm.getScale(1));
    assertEquals(ResultSetMetaData.columnNullableUnknown, pm.isNullable(1));
    assertEquals(pm.isSigned(1), true);
    assertThrows(ErrorCode.INVALID_VALUE_2, pm).getPrecision(0);
    assertThrows(ErrorCode.INVALID_VALUE_2, pm).getPrecision(4);
    prep.close();
    assertThrows(ErrorCode.OBJECT_CLOSED, pm).getPrecision(1);

    Statement stat = conn.createStatement();
    stat.execute("CREATE TABLE TEST3(ID INT, NAME VARCHAR(255), DATA DECIMAL(10,2))");
    PreparedStatement prep1 = conn.prepareStatement("UPDATE TEST3 SET ID=?, NAME=?, DATA=?");
    PreparedStatement prep2 = conn.prepareStatement("INSERT INTO TEST3 VALUES(?, ?, ?)");
    checkParameter(prep1, 1, "java.lang.Integer", 4, "INTEGER", 10, 0);
    checkParameter(prep1, 2, "java.lang.String", 12, "VARCHAR", 255, 0);
    checkParameter(prep1, 3, "java.math.BigDecimal", 3, "DECIMAL", 10, 2);
    checkParameter(prep2, 1, "java.lang.Integer", 4, "INTEGER", 10, 0);
    checkParameter(prep2, 2, "java.lang.String", 12, "VARCHAR", 255, 0);
    checkParameter(prep2, 3, "java.math.BigDecimal", 3, "DECIMAL", 10, 2);
    PreparedStatement prep3 =
        conn.prepareStatement("SELECT * FROM TEST3 WHERE ID=? AND NAME LIKE ? AND ?>DATA");
    checkParameter(prep3, 1, "java.lang.Integer", 4, "INTEGER", 10, 0);
    checkParameter(prep3, 2, "java.lang.String", 12, "VARCHAR", 0, 0);
    checkParameter(prep3, 3, "java.math.BigDecimal", 3, "DECIMAL", 10, 2);
    stat.execute("DROP TABLE TEST3");
  }
  private void testLikeIndex(Connection conn) throws SQLException {
    Statement stat = conn.createStatement();
    stat.execute("CREATE TABLE TEST(ID INT PRIMARY KEY, NAME VARCHAR(255))");
    stat.execute("INSERT INTO TEST VALUES(1, 'Hello')");
    stat.execute("INSERT INTO TEST VALUES(2, 'World')");
    stat.execute("create index idxname on test(name);");
    PreparedStatement prep, prepExe;

    prep = conn.prepareStatement("EXPLAIN SELECT * FROM TEST WHERE NAME LIKE ?");
    assertEquals(1, prep.getParameterMetaData().getParameterCount());
    prepExe = conn.prepareStatement("SELECT * FROM TEST WHERE NAME LIKE ?");
    prep.setString(1, "%orld");
    prepExe.setString(1, "%orld");
    ResultSet rs = prep.executeQuery();
    rs.next();
    String plan = rs.getString(1);
    assertTrue(plan.indexOf(".tableScan") >= 0);
    rs = prepExe.executeQuery();
    rs.next();
    assertEquals("World", rs.getString(2));
    assertFalse(rs.next());

    prep.setString(1, "H%");
    prepExe.setString(1, "H%");
    rs = prep.executeQuery();
    rs.next();
    String plan1 = rs.getString(1);
    assertTrue(plan1.indexOf("IDXNAME") >= 0);
    rs = prepExe.executeQuery();
    rs.next();
    assertEquals("Hello", rs.getString(2));
    assertFalse(rs.next());

    stat.execute("DROP TABLE IF EXISTS TEST");
  }
 public java.sql.ParameterMetaData getParameterMetaData() throws SQLException {
   checkOpen();
   try {
     return _stmt.getParameterMetaData();
   } catch (SQLException e) {
     handleException(e);
     return null;
   }
 }
Esempio n. 9
0
  public ParameterMetaData getParameterMetaData() throws SQLException {
    Profiler profiler = _profilerPoint.start();

    try {
      return _preparedStatement.getParameterMetaData();
    } finally {
      profiler.finish();
    }
  }
 public ParameterMetaData getParameterMetaData() throws SQLException {
   String methodCall = "getParameterMetaData()";
   try {
     return (ParameterMetaData)
         reportReturn(methodCall, realPreparedStatement.getParameterMetaData());
   } catch (SQLException s) {
     reportException(methodCall, s);
     throw s;
   }
 }
 @Test
 public void test() throws SQLException {
   JdbcTemplate template = new JdbcTemplate();
   DataSource source = template.getDataSource();
   Connection connection = source.getConnection();
   PreparedStatement statement =
       connection.prepareStatement("insert into TM.POINT values (?,?,?,?,?)");
   @SuppressWarnings("unused")
   ParameterMetaData metaData = statement.getParameterMetaData();
   connection.close();
 }
Esempio n. 12
0
 protected void showStatementState(String when, Statement s) throws SQLException {
   super.showStatementState(when, s);
   System.out.println("  getResultSetHoldability() " + rsHoldability(s.getResultSetHoldability()));
   if (s instanceof PreparedStatement) {
     PreparedStatement ps = (PreparedStatement) s;
     ParameterMetaData psmd = ps.getParameterMetaData();
     System.out.println("  Parameter Count " + psmd.getParameterCount());
     for (int i = 1; i <= psmd.getParameterCount(); i++) {
       System.out.println("    " + i + " type " + psmd.getParameterType(i));
     }
   }
 }
Esempio n. 13
0
 private void resolveEnumValueMapper(PreparedStatement st, int index) {
   if (enumValueMapper == null) {
     try {
       resolveEnumValueMapper(st.getParameterMetaData().getParameterType(index));
     } catch (Exception e) {
       // because some drivers do not implement this
       LOG.debugf(
           "JDBC driver threw exception calling java.sql.ParameterMetaData#getParameterType; "
               + "falling back to ordinal-based enum mapping [%s] : %s",
           enumClass.getName(), e.getMessage());
       treatAsOrdinal();
     }
   }
 }
 private void checkParameter(
     PreparedStatement prep,
     int index,
     String className,
     int type,
     String typeName,
     int precision,
     int scale)
     throws SQLException {
   ParameterMetaData meta = prep.getParameterMetaData();
   assertEquals(className, meta.getParameterClassName(index));
   assertEquals(type, meta.getParameterType(index));
   assertEquals(typeName, meta.getParameterTypeName(index));
   assertEquals(precision, meta.getPrecision(index));
   assertEquals(scale, meta.getScale(index));
 }
Esempio n. 15
0
 /**
  * Executes a prepared statement.
  *
  * @param stmt prepared statement
  * @param ctx query context
  * @return result
  * @throws QueryException query exception
  */
 private NodeCache executePrepStmt(final PreparedStatement stmt, final QueryContext ctx)
     throws QueryException {
   // Get parameters for prepared statement
   final ANode params = (ANode) checkType(expr[1].item(ctx, input), NodeType.ELM);
   if (!params.qname().eq(E_PARAMS)) PARWHICH.thrw(input, params.qname());
   try {
     final int placeCount = stmt.getParameterMetaData().getParameterCount();
     // Check if number of parameters equals number of place holders
     if (placeCount != countParams(params)) PARAMS.thrw(input);
     else setParameters(params.children(), stmt);
     final boolean result = stmt.execute();
     return result ? buildResult(stmt.getResultSet()) : new NodeCache();
   } catch (final SQLException ex) {
     throw SQLEXC.thrw(input, ex.getMessage());
   }
 }
  @Test
  public void testPrepareBindExecuteFetchVarbinary() throws Exception {
    final Connection connection = ljs();
    final String sql = "select x'de' || ? as c from (values (1, 'a'))";
    final PreparedStatement ps = connection.prepareStatement(sql);
    final ParameterMetaData parameterMetaData = ps.getParameterMetaData();
    assertThat(parameterMetaData.getParameterCount(), equalTo(1));

    ps.setBytes(1, new byte[] {65, 0, 66});
    final ResultSet resultSet = ps.executeQuery();
    assertTrue(resultSet.next());
    assertThat(resultSet.getBytes(1), equalTo(new byte[] {(byte) 0xDE, 65, 0, 66}));
    resultSet.close();
    ps.close();
    connection.close();
  }
Esempio n. 17
0
  /**
   * Retrieves list of {@link WebSocketMessageDTO}, but loads only parts of the payload.
   *
   * @param criteria
   * @param opcodes
   * @param inScopeChannelIds
   * @param offset
   * @param limit
   * @param payloadPreviewLength
   * @return Messages that fulfill given template.
   * @throws DatabaseException
   */
  public synchronized List<WebSocketMessageDTO> getMessages(
      WebSocketMessageDTO criteria,
      List<Integer> opcodes,
      List<Integer> inScopeChannelIds,
      int offset,
      int limit,
      int payloadPreviewLength)
      throws DatabaseException {
    try {
      String query =
          "SELECT m.message_id, m.channel_id, m.timestamp, m.opcode, m.payload_length, m.is_outgoing, "
              + "m.payload_utf8, m.payload_bytes, "
              + "f.fuzz_id, f.state, f.fuzz "
              + "FROM websocket_message AS m "
              + "LEFT OUTER JOIN websocket_message_fuzz f "
              + "ON m.message_id = f.message_id AND m.channel_id = f.channel_id "
              + "<where> "
              + "ORDER BY m.timestamp, m.channel_id, m.message_id "
              + "LIMIT ? "
              + "OFFSET ?";

      PreparedStatement stmt;
      try {
        stmt = buildMessageCriteriaStatement(query, criteria, opcodes, inScopeChannelIds);
      } catch (SQLException e) {
        if (getConnection().isClosed()) {
          return new ArrayList<>(0);
        }

        throw e;
      }

      try {
        int paramsCount = stmt.getParameterMetaData().getParameterCount();
        stmt.setInt(paramsCount - 1, limit);
        stmt.setInt(paramsCount, offset);

        stmt.execute();

        return buildMessageDTOs(stmt.getResultSet(), true, payloadPreviewLength);
      } finally {
        stmt.close();
      }
    } catch (SQLException e) {
      throw new DatabaseException(e);
    }
  }
  private void checkPrepareBindExecuteFetch(Connection connection) throws SQLException {
    final String sql = "select cast(? as integer) * 3 as c, 'x' as x\n" + "from (values (1, 'a'))";
    final PreparedStatement ps = connection.prepareStatement(sql);
    final ResultSetMetaData metaData = ps.getMetaData();
    assertEquals(2, metaData.getColumnCount());
    assertEquals("C", metaData.getColumnName(1));
    assertEquals("X", metaData.getColumnName(2));
    try {
      final ResultSet resultSet = ps.executeQuery();
      fail("expected error, got " + resultSet);
    } catch (SQLException e) {
      assertThat(e.getMessage(), equalTo("exception while executing query: unbound parameter"));
    }

    final ParameterMetaData parameterMetaData = ps.getParameterMetaData();
    assertThat(parameterMetaData.getParameterCount(), equalTo(1));

    ps.setInt(1, 10);
    final ResultSet resultSet = ps.executeQuery();
    assertTrue(resultSet.next());
    assertThat(resultSet.getInt(1), equalTo(30));
    assertFalse(resultSet.next());
    resultSet.close();

    ps.setInt(1, 20);
    final ResultSet resultSet2 = ps.executeQuery();
    assertFalse(resultSet2.isClosed());
    assertTrue(resultSet2.next());
    assertThat(resultSet2.getInt(1), equalTo(60));
    assertThat(resultSet2.wasNull(), is(false));
    assertFalse(resultSet2.next());
    resultSet2.close();

    ps.setObject(1, null);
    final ResultSet resultSet3 = ps.executeQuery();
    assertTrue(resultSet3.next());
    assertThat(resultSet3.getInt(1), equalTo(0));
    assertThat(resultSet3.wasNull(), is(true));
    assertFalse(resultSet3.next());
    resultSet3.close();

    ps.close();
    connection.close();
  }
 private void testSubquery(Connection conn) throws SQLException {
   Statement stat = conn.createStatement();
   stat.execute("CREATE TABLE TEST(ID INT)");
   stat.execute("INSERT INTO TEST VALUES(1),(2),(3)");
   PreparedStatement prep =
       conn.prepareStatement(
           "select x.id, ? from " + "(select * from test where id in(?, ?)) x where x.id*2 <>  ?");
   assertEquals(4, prep.getParameterMetaData().getParameterCount());
   prep.setInt(1, 0);
   prep.setInt(2, 1);
   prep.setInt(3, 2);
   prep.setInt(4, 4);
   ResultSet rs = prep.executeQuery();
   rs.next();
   assertEquals(1, rs.getInt(1));
   assertEquals(0, rs.getInt(2));
   assertFalse(rs.next());
   stat.execute("DROP TABLE TEST");
 }
  @SuppressWarnings("deprecation")
  private void testUnsupportedOperations(Connection conn) throws Exception {
    PreparedStatement prep = conn.prepareStatement("select ? from dual");
    assertThrows(ErrorCode.METHOD_NOT_ALLOWED_FOR_PREPARED_STATEMENT, prep).addBatch("select 1");

    assertThrows(ErrorCode.METHOD_NOT_ALLOWED_FOR_PREPARED_STATEMENT, prep)
        .executeUpdate("create table test(id int)");
    assertThrows(ErrorCode.METHOD_NOT_ALLOWED_FOR_PREPARED_STATEMENT, prep)
        .executeUpdate("create table test(id int)", new int[0]);
    assertThrows(ErrorCode.METHOD_NOT_ALLOWED_FOR_PREPARED_STATEMENT, prep)
        .executeUpdate("create table test(id int)", new String[0]);
    assertThrows(ErrorCode.METHOD_NOT_ALLOWED_FOR_PREPARED_STATEMENT, prep)
        .executeUpdate("create table test(id int)", Statement.RETURN_GENERATED_KEYS);

    assertThrows(ErrorCode.METHOD_NOT_ALLOWED_FOR_PREPARED_STATEMENT, prep)
        .execute("create table test(id int)");
    assertThrows(ErrorCode.METHOD_NOT_ALLOWED_FOR_PREPARED_STATEMENT, prep)
        .execute("create table test(id int)", new int[0]);
    assertThrows(ErrorCode.METHOD_NOT_ALLOWED_FOR_PREPARED_STATEMENT, prep)
        .execute("create table test(id int)", new String[0]);
    assertThrows(ErrorCode.METHOD_NOT_ALLOWED_FOR_PREPARED_STATEMENT, prep)
        .execute("create table test(id int)", Statement.RETURN_GENERATED_KEYS);

    assertThrows(ErrorCode.METHOD_NOT_ALLOWED_FOR_PREPARED_STATEMENT, prep)
        .executeQuery("select * from dual");

    assertThrows(ErrorCode.FEATURE_NOT_SUPPORTED_1, prep).setURL(1, new URL("http://www.acme.com"));
    assertThrows(ErrorCode.FEATURE_NOT_SUPPORTED_1, prep).setRowId(1, (RowId) null);
    assertThrows(ErrorCode.FEATURE_NOT_SUPPORTED_1, prep)
        .setUnicodeStream(1, (InputStream) null, 0);
    assertThrows(ErrorCode.FEATURE_NOT_SUPPORTED_1, prep).setArray(1, (Array) null);

    ParameterMetaData meta = prep.getParameterMetaData();
    assertTrue(meta.toString(), meta.toString().endsWith("parameterCount=1"));
    assertThrows(ErrorCode.FEATURE_NOT_SUPPORTED_1, meta).isWrapperFor(Object.class);
    assertThrows(ErrorCode.FEATURE_NOT_SUPPORTED_1, meta).unwrap(Object.class);

    assertThrows(ErrorCode.FEATURE_NOT_SUPPORTED_1, conn).isWrapperFor(Object.class);
    assertThrows(ErrorCode.FEATURE_NOT_SUPPORTED_1, conn).unwrap(Object.class);
    assertThrows(ErrorCode.FEATURE_NOT_SUPPORTED_1, conn).createSQLXML();
    assertThrows(ErrorCode.FEATURE_NOT_SUPPORTED_1, conn).createArrayOf("Integer", new Object[0]);
    assertThrows(ErrorCode.FEATURE_NOT_SUPPORTED_1, conn).createStruct("Integer", new Object[0]);
  }
Esempio n. 21
0
  @Override
  public MetaData[] getMetaData() throws DataLinkException {

    try {
      ParameterMetaData meta = stmt.getParameterMetaData();
      int size = meta.getParameterCount();
      MetaData[] metadata = new MetaData[size];

      for (int i = 0; i < size; i++) {
        metadata[i] =
            new MetaData(
                i < params.length ? params[i] : null,
                SQLDataLinkFactory.getKind(meta.getParameterType(i + 1)));
      }

      return metadata;
    } catch (SQLException ex) {
      throw new DataLinkException(ex);
    }
  }
Esempio n. 22
0
 private void sendParameterDescription(Prepared p) throws IOException {
   try {
     PreparedStatement prep = p.prep;
     ParameterMetaData meta = prep.getParameterMetaData();
     int count = meta.getParameterCount();
     startMessage('t');
     writeShort(count);
     for (int i = 0; i < count; i++) {
       int type;
       if (p.paramType != null && p.paramType[i] != 0) {
         type = p.paramType[i];
       } else {
         type = PgServer.PG_TYPE_VARCHAR;
       }
       server.checkType(type);
       writeInt(type);
     }
     sendMessage();
   } catch (Exception e) {
     sendErrorResponse(e);
   }
 }
  public List<Venda> selecionarPorFunc(String nome) throws Exception {
    try {
      String sql;
      if (nome != null && !nome.isEmpty()) {
        sql =
            "SELECT * FROM venda v,funcionario f where v.fun_mat = f.fun_mat and fun_nome LIKE concat(?,'%') ORDER BY ped_num";
      } else {
        sql = "SELECT * FROM venda ORDER BY ped_num";
      }
      PreparedStatement ps = Conexao.getConexao().prepareStatement(sql);
      if (ps.getParameterMetaData().getParameterCount() > 0) {
        ps.setString(1, nome);
      }
      ResultSet rs = ps.executeQuery();

      preencheLista(rs);

      return getLista();
    } catch (Exception erro) {
      throw new Exception("Erro ao localizar Venda pelo Nome: " + erro.getMessage());
    }
  }
  //
  // Find all the methods for java.sql objects in the Connection which raise
  // SQLFeatureNotSupportedException.
  //
  private void connectionWorkhorse(
      Connection conn, HashSet<String> unsupportedList, HashSet<String> notUnderstoodList)
      throws Exception {
    vetSavepoint(conn, unsupportedList, notUnderstoodList);
    vetLargeObjects(conn, unsupportedList, notUnderstoodList);

    DatabaseMetaData dbmd = conn.getMetaData();
    PreparedStatement ps = conn.prepareStatement("select * from sys.systables where tablename = ?");

    ps.setString(1, "foo");

    ParameterMetaData parameterMetaData = ps.getParameterMetaData();
    ResultSet rs = ps.executeQuery();
    ResultSetMetaData rsmd = rs.getMetaData();
    Statement stmt = conn.createStatement();

    CallableStatement cs = conn.prepareCall("CALL SYSCS_UTIL.SET_RUNTIMESTATISTICS(0)");
    ParameterMetaData csmd = cs.getParameterMetaData();

    //
    // The vetObject() method calls all of the methods in these objects
    // in a deterministic order, calling the close() method last.
    // Inspect these objects in an order which respects the fact that
    // the objects are closed as a result of calling vetObject().
    //
    vetObject(dbmd, unsupportedList, notUnderstoodList);
    vetObject(stmt, unsupportedList, notUnderstoodList);
    vetObject(csmd, unsupportedList, notUnderstoodList);
    vetObject(cs, unsupportedList, notUnderstoodList);
    vetObject(rsmd, unsupportedList, notUnderstoodList);
    vetObject(rs, unsupportedList, notUnderstoodList);
    vetObject(parameterMetaData, unsupportedList, notUnderstoodList);
    vetObject(ps, unsupportedList, notUnderstoodList);
    vetObject(conn, unsupportedList, notUnderstoodList);

    // No need to close the objects. They were closed by vetObject().
  }
  /**
   * 填充PreparedStatement
   *
   * @param pstmt
   * @param parameterElements
   * @return 返回下次填充的起始位置
   * @throws SQLException
   * @throws ServiceInvocationException
   */
  public static int fulfillPreparedStatement(
      PreparedStatement pstmt, List<Element> parameterElements, int startIndex)
      throws SQLException, ServiceInvocationException {
    ParameterMetaData parameterMetaData = pstmt.getParameterMetaData();
    int preparedParamCount = parameterMetaData.getParameterCount();

    int paramCount = parameterElements.size();
    int currentParamIndex = 0;
    if (preparedParamCount > 0) {
      for (int i = 0; i < paramCount; i++) {
        currentParamIndex = i + startIndex;
        int type = parameterMetaData.getParameterType(currentParamIndex);
        Element element = parameterElements.get(i);
        String strValue = null;
        NodeList nodeList = element.getChildNodes();
        for (int k = 0; k < nodeList.getLength(); k++) {
          Node node = nodeList.item(k);
          if (node.getNodeType() == Node.TEXT_NODE) {
            strValue = ((Text) node).getNodeValue();
          }
        }

        switch (type) {
          case java.sql.Types.CHAR:
          case java.sql.Types.VARCHAR:
          case java.sql.Types.LONGVARCHAR:
            // case java.sql.Types.NCHAR:
            // case java.sql.Types.NVARCHAR:
            // case java.sql.Types.LONGNVARCHAR:
            pstmt.setString(currentParamIndex, strValue);
            break;

          case java.sql.Types.NUMERIC:
          case java.sql.Types.DECIMAL:
            if (StringUtils.isEmpty(strValue)) {
              pstmt.setNull(currentParamIndex, type);
            } else {
              if (strValue.indexOf(".") == -1) {
                Long theLong = Long.valueOf(strValue);
                pstmt.setBigDecimal(currentParamIndex, BigDecimal.valueOf(theLong));

              } else {
                Double d = Double.valueOf(strValue);
                pstmt.setBigDecimal(currentParamIndex, BigDecimal.valueOf(d));
              }
            }

            break;

          case java.sql.Types.BOOLEAN:
          case java.sql.Types.BIT:
            if (StringUtils.isEmpty(strValue)) {
              pstmt.setNull(currentParamIndex, type);

            } else {
              pstmt.setBoolean(currentParamIndex, Boolean.valueOf(strValue));
            }

            break;

          case java.sql.Types.TINYINT:
          case java.sql.Types.SMALLINT:
          case java.sql.Types.INTEGER:
            if (StringUtils.isEmpty(strValue)) {
              pstmt.setNull(currentParamIndex, type);
            } else {
              pstmt.setInt(currentParamIndex, Integer.valueOf(strValue));
            }

            break;

          case java.sql.Types.BIGINT:
            if (StringUtils.isEmpty(strValue)) {
              pstmt.setNull(currentParamIndex, type);
            } else {
              pstmt.setLong(currentParamIndex, Long.valueOf(strValue));
            }

            break;

          case java.sql.Types.REAL:
            if (StringUtils.isEmpty(strValue)) {
              pstmt.setNull(currentParamIndex, type);
            } else {
              pstmt.setFloat(currentParamIndex, Float.valueOf(strValue));
            }

            break;

          case java.sql.Types.FLOAT:
          case java.sql.Types.DOUBLE:
            if (StringUtils.isEmpty(strValue)) {
              pstmt.setNull(currentParamIndex, type);
            } else {
              pstmt.setDouble(currentParamIndex, Double.valueOf(strValue));
            }

            break;

          case java.sql.Types.BINARY:
          case java.sql.Types.VARBINARY:
          case java.sql.Types.LONGVARBINARY:
          case java.sql.Types.BLOB:
          case java.sql.Types.CLOB:
            if (StringUtils.isEmpty(strValue)) {
              pstmt.setNull(currentParamIndex, type);
            } else {
              byte[] content = null;
              try {
                content = Base64.decodeBase64(strValue.getBytes("UTF-8"));
              } catch (Exception e) {
                e.printStackTrace();
              }
              if (content == null) {
                pstmt.setNull(currentParamIndex, type);
              } else {
                pstmt.setBytes(currentParamIndex, content);
              }
            }

            break;

          case java.sql.Types.DATE:
            if (StringUtils.isEmpty(strValue)) {
              pstmt.setNull(currentParamIndex, type);
            } else {
              java.sql.Date sqlDate = java.sql.Date.valueOf(strValue);
              pstmt.setDate(currentParamIndex, sqlDate);
            }

            break;

          case java.sql.Types.TIME:
            if (StringUtils.isEmpty(strValue)) {
              pstmt.setNull(currentParamIndex, type);
            } else {
              java.sql.Time t = java.sql.Time.valueOf(strValue);
              pstmt.setTime(currentParamIndex, t);
            }

            break;

          case java.sql.Types.TIMESTAMP:
            if (StringUtils.isEmpty(strValue)) {
              pstmt.setNull(currentParamIndex, type);
            } else {
              String dt = strValue;
              if (dt.indexOf("T") >= 0) { // 如果是“YYYY-MM-DDThh:mm:ss”这种格式,则将T替换成空格
                dt = dt.replace("T", " ");
              }
              java.sql.Timestamp ts = Timestamp.valueOf(dt);
              pstmt.setTimestamp(currentParamIndex, ts);
            }

            break;
        }
      }
    }
    return currentParamIndex + 1;
  }
  private void testCasewhen(Connection conn) throws SQLException {
    Statement stat = conn.createStatement();
    stat.execute("CREATE TABLE TEST(ID INT)");
    stat.execute("INSERT INTO TEST VALUES(1),(2),(3)");
    PreparedStatement prep;
    ResultSet rs;
    prep =
        conn.prepareStatement(
            "EXPLAIN SELECT COUNT(*) FROM TEST " + "WHERE CASEWHEN(ID=1, ID, ID)=? GROUP BY ID");
    prep.setInt(1, 1);
    rs = prep.executeQuery();
    rs.next();
    String plan = rs.getString(1);
    trace(plan);
    rs.close();
    prep =
        conn.prepareStatement(
            "EXPLAIN SELECT COUNT(*) FROM TEST "
                + "WHERE CASE ID WHEN 1 THEN ID WHEN 2 THEN ID ELSE ID END=? GROUP BY ID");
    prep.setInt(1, 1);
    rs = prep.executeQuery();
    rs.next();
    plan = rs.getString(1);
    trace(plan);

    prep =
        conn.prepareStatement(
            "SELECT COUNT(*) FROM TEST WHERE CASEWHEN(ID=1, ID, ID)=? GROUP BY ID");
    prep.setInt(1, 1);
    rs = prep.executeQuery();
    assertTrue(rs.next());
    assertEquals(1, rs.getInt(1));
    assertFalse(rs.next());

    prep =
        conn.prepareStatement(
            "SELECT COUNT(*) FROM TEST WHERE CASE ID WHEN 1 THEN ID WHEN 2 THEN ID ELSE ID END=? GROUP BY ID");
    prep.setInt(1, 1);
    rs = prep.executeQuery();
    assertTrue(rs.next());
    assertEquals(1, rs.getInt(1));
    assertFalse(rs.next());

    prep = conn.prepareStatement("SELECT * FROM TEST WHERE ? IS NULL");
    prep.setString(1, "Hello");
    rs = prep.executeQuery();
    assertFalse(rs.next());
    assertThrows(ErrorCode.UNKNOWN_DATA_TYPE_1, conn)
        .prepareStatement("select ? from dual union select ? from dual");
    prep = conn.prepareStatement("select cast(? as varchar) from dual union select ? from dual");
    assertEquals(2, prep.getParameterMetaData().getParameterCount());
    prep.setString(1, "a");
    prep.setString(2, "a");
    rs = prep.executeQuery();
    rs.next();
    assertEquals("a", rs.getString(1));
    assertEquals("a", rs.getString(1));
    assertFalse(rs.next());

    stat.execute("DROP TABLE TEST");
  }
  /** Sets the given parameter Known subclasses: see PostgresStatement.execute */
  protected void setObject(int i, Object param) throws SQLException {
    // See php/4358, php/43b8, php/43d8, and php/43p8.
    ParameterMetaData pmd = _preparedStmt.getParameterMetaData();
    int type = pmd.getParameterType(i);

    switch (type) {
      case Types.OTHER:
        {
          // See php/43b8
          String typeName = pmd.getParameterTypeName(i);
          if (typeName.equals("interval")) {
            _preparedStmt.setObject(i, param);
          } else {
            try {
              Class<?> cl = Class.forName("org.postgresql.util.PGobject");
              Constructor<?> constructor = cl.getDeclaredConstructor();
              Object object = constructor.newInstance();

              Method method = cl.getDeclaredMethod("setType", new Class[] {String.class});
              method.invoke(object, new Object[] {typeName});

              method = cl.getDeclaredMethod("setValue", new Class[] {String.class});
              method.invoke(object, new Object[] {param});

              _preparedStmt.setObject(i, object, type);
            } catch (ClassNotFoundException e) {
              throw new SQLException(e);
            } catch (NoSuchMethodException e) {
              throw new SQLException(e);
            } catch (InvocationTargetException e) {
              throw new SQLException(e.getCause());
            } catch (IllegalAccessException e) {
              throw new SQLException(e);
            } catch (InstantiationException e) {
              throw new SQLException(e);
            }
          }

          break;
        }

      case Types.DOUBLE:
        {
          // See php/43p8.
          String typeName = pmd.getParameterTypeName(i);
          if (typeName.equals("money")) {
            String s = param.toString();

            if (s.length() == 0) {
              throw new IllegalArgumentException(L.l("argument `{0}' cannot be empty", param));
            } else {

              String money = s;

              if (s.charAt(0) == '$') s = s.substring(1);
              else money = "$" + money;

              try {
                // This will throw an exception if not double while
                // trying to setObject() would not. The error would
                // come late, otherwise. See php/43p8.
                Double.parseDouble(s);
              } catch (Exception ex) {
                throw new IllegalArgumentException(
                    L.l("cannot convert argument `{0}' to money", param));
              }

              try {
                Class<?> cl = Class.forName("org.postgresql.util.PGmoney");

                Constructor<?> constructor = cl.getDeclaredConstructor(new Class[] {String.class});

                Object object = constructor.newInstance(new Object[] {money});

                _preparedStmt.setObject(i, object, Types.OTHER);
              } catch (ClassNotFoundException e) {
                throw new SQLException(e);
              } catch (NoSuchMethodException e) {
                throw new SQLException(e);
              } catch (InvocationTargetException e) {
                throw new SQLException(e.getCause());
              } catch (IllegalAccessException e) {
                throw new SQLException(e);
              } catch (InstantiationException e) {
                throw new SQLException(e);
              }

              break;
            }
          }
          // else falls to default case
        }

      default:
        _preparedStmt.setObject(i, param, type);
    }
  }
 public ParameterMetaData getParameterMetaData() throws SQLException {
   return delegate.getParameterMetaData();
 }
Esempio n. 29
0
 public ParameterMetaData getParameterMetaData() throws SQLException {
   this.assertPs();
   return ps.getParameterMetaData();
 }
Esempio n. 30
0
 public ParameterMetaData getParameterMetaData() throws SQLException {
   return statement.getParameterMetaData();
 }