1

I am trying to run this SQL prepared statement from code.

select
    COUNT(*)
from table1 ed, table2 e
where ed.id = e.id
  and e.status_cd = ?
  and ed.active_ind = 1
  and {in}
  and systimestamp < FROM_TZ(cast(ed.end_effective_dt_tm as TIMESTAMP), ?)
  and FROM_TZ(cast(? as TIMESTAMP), ?) between TIMESTAMP ? and TIMESTAMP ?

The JDBC code that reads this and executes against an Oracle database reads something like this :

int parameterIndex = 0;
      stmt.setDouble(
          ++parameterIndex, doubleValue);
stmt.setInClause(
          ++parameterIndex, inClauseColumns.toArray(new InClauseColumn[inClauseColumns.size()]));
      stmt.setString(
          ++parameterIndex, tz_Id); /* Setting time zone for casting ed.end_effective_dt_tm */
      stmt.setString(++parameterIndex, timeFilterColumn);
      stmt.setString(++parameterIndex, tz_Id); // timeFilter
      stmt.setTimestamp(
          ++parameterIndex, new Timestamp(startTime), calculationTimeZone); // startTime
      stmt.setTimestamp(++parameterIndex, new Timestamp(endTime), calculationTimeZone); // endTime
      ResultSet rs = null;
      try {
        while (stmt.hasNext()) {
          rs = stmt.next();
   // do stuff

This gives the following error in JDBC :

Error Msg = ORA-00905: missing keyword

But the same query when run from SQL developer returns the expected rows from the database.

Example query ran from SQL developer :

select
    COUNT(*)
from table1 ed, table2 e
where ed.id = e.id
  and e.status_cd = 854 /*Prameter 1*/
  and ed.active_ind = 1
  and ed.facility_cd in (1.7241194E7) /*in clause parameter 2 */
  and
  systimestamp < FROM_TZ(cast(ed.end_effective_dt_tm as TIMESTAMP), 'America/Chicago' /*parameter 3 */)
  and
  FROM_TZ(cast(e.updt_dt_tm /*parameter 4 */ as TIMESTAMP), 'America/Chicago') /*parameter 5 */
  between
  TIMESTAMP '2021-06-30 02:23:20.0' /*parameter 6 */
  and TIMESTAMP '2021-11-10 18:09:24.774' /*parameter 7 */

Can someone please provide some suggestion on how to make this work with JDBC? I can't seem to figure out the issue here. Thanks.

allocated
  • 1,295
  • 3
  • 13
  • 16
  • 1
    Are you sure `stmt.setInClause()` is converting `{in}` to `ed.facility_cd in (1.7241194E7)`? Can you see the actual SQL being executed in the log? And what exactly is `stmt` here - it looks like your own wrapper around a real JDBC statement, with additional helper methods; so including the code for `setInClause()` might shed some light. – Alex Poole Aug 09 '22 at 10:54
  • 1
    There's likely something off with the final SQL statement construction - probably a missing space or some such. What does the actual SQL submitted to the database - that generates the error - look like? – pmdba Aug 09 '22 at 10:54
  • 1
    `timestamp ?` is invalid. Timestamp **literal** has this format: `TIMESTAMP ''`. Literal is not a function, it is a constant value. To parse string as timestamp use `to_timestamp` **function** – astentx Aug 09 '22 at 11:34
  • If I'm not mistaken, the setinclause allows you to specify the list of values, not the column that needs to be checked. – gsalem Aug 09 '22 at 12:41
  • @astentx, I changed the query based on your suggestion, from :- `between TIMESTAMP ? and TIMESTAMP ?` to `between TO_TIMESTAMP(?, 'RRRR-MM-DD HH24:MI:SS.FF') and TO_TIMESTAMP (?, 'RRRR-MM-DD HH24:MI:SS.FF')` This again works fine in SQL dev but doesn't work through JDBC. It says : `ORA-01858: a non-numeric character was found where a numeric was expected` To remedy this, I changed the `stmt.setTimeStamp` to `stmt.setString(++parameterIndex, new Timestamp(startTime).toString())` Hoping that i'd get a string literal which would get converted to the format specified in TO_TIMSTAMP. – allocated Aug 09 '22 at 12:53
  • @AlexPoole, I saw the query, and it is not `ed.facility_cd in (1.7241194E7`, rather `ed.facility_cd = ?` – allocated Aug 09 '22 at 13:00
  • @allocated - if you're using `setTimestamp()` then the query should just have `?`, not `TIMESTAMP ?` or `to_timestamp(?, '...')` - no point converting to a string and back? – Alex Poole Aug 09 '22 at 13:16
  • @AlexPoole Once I do that, it says `ORA-01849: hour must be between 1 and 12` – allocated Aug 09 '22 at 13:43
  • 1
    @allocated So you are passing not a string. As pointed out in the previous comment, if you are passing a timestamp, you obviously do not need to perform extra converion to timestamp. `setX` passes the data to the database in the internal format. It is not a human-readable representation of something that is embedded into the statement, it is a pure data and is processed as is (with appropriate conversion of application language object into some *data* in the database memory area) – astentx Aug 09 '22 at 13:44
  • @astentx makes sense, but how to deal with the date format issue which is expecting hours to be in 12 hours format? Is there a way to manually specify it without invoking TO_TIMESTAMP as we discovered previously that doesn't work in my case. – allocated Aug 09 '22 at 14:16

1 Answers1

2

Query problems:

  • incorrect in clause and {in} just ed.facility_cd in ( ? ) and setting parameter for each array element
  • in case setting specific java.sql.Timestamp type into JDBC statement you do not need to perform parameter cast in the query between TIMESTAMP ? and TIMESTAMP ? just between ? and ?

DB structure:

CREATE TABLE TABLE1 (
 ID NUMBER,
 active_ind NUMBER,
 end_effective_dt_tm TIMESTAMP,
 facility_cd FLOAT
);

CREATE TABLE TABLE2 (
 ID NUMBER,
 status_cd FLOAT,
 updt_dt_tm TIMESTAMP
);

Example of working JDBC statement:

    public Long execute(Connection connection) throws SQLException {
        long count = 0L;

        double doubleValue = 854D;
        Double[] inClauseValues = new Double[]{1.7241194E7, 1.7241194E8};
        String tz_Id = "America/Chicago";
        Timestamp startTime = Timestamp.valueOf("2021-06-30 02:23:20.0");
        Timestamp endTime = Timestamp.valueOf("2021-11-10 18:09:24.774");
        String timeFilterColumn = "e.updt_dt_tm";
        String inClauseColumn = "ed.facility_cd";

        String sqlQuery = " select COUNT(*) " +
                " from table1 ed, table2 e " +
                "  where ed.id = e.id " +
                "  and e.status_cd = ? " +
                "  and ed.active_ind = 1 " +
                "  and ? in ( " + Arrays.stream(inClauseValues).map(v -> "?").collect(Collectors.joining(", ")) + " ) " +
                "  and systimestamp < FROM_TZ(cast(ed.end_effective_dt_tm as TIMESTAMP), ? ) " +
                "  and FROM_TZ(cast( ? as TIMESTAMP), ? ) between ? and ? ";

        try (PreparedStatement stmt = connection.prepareStatement(sqlQuery)) {
            int parameterIndex = 0;

            stmt.setDouble(++parameterIndex, doubleValue); // Setting e.status_cd
            stmt.setString(++parameterIndex, inClauseColumn); //Set dynamic column for in cluase
            for (Double value : inClauseValues) { //Setting ed.facility_cd in
                stmt.setDouble(++parameterIndex, value);
            }
            stmt.setString(++parameterIndex, tz_Id); /* Setting time zone for casting ed.end_effective_dt_tm */
            stmt.setString(++parameterIndex, timeFilterColumn); //Setting timeFilterColumn e.updt_dt_tm
            stmt.setString(++parameterIndex, tz_Id); /* Setting time zone for casting e.updt_dt_tm  */
            stmt.setTimestamp(++parameterIndex, startTime); // startTime
            stmt.setTimestamp(++parameterIndex, endTime); // endTime

            try (ResultSet rs = stmt.executeQuery()) {
                while (rs.next()) {
                    count = rs.getLong(1);
                }
            }
        }
        return count;
    }

UPDATE
Another solution, you can prebuild query string with dynamic columns:

    public Long execute(Connection connection) throws SQLException {
        long count = 0L;

        double doubleValue = 854D;
        Double[] inClauseValues = new Double[]{1.7241194E7, 1.7241194E8};
        String tz_Id = "America/Chicago";
        Timestamp startTime = Timestamp.valueOf("2021-06-30 02:23:20.0");
        Timestamp endTime = Timestamp.valueOf("2021-11-10 18:09:24.774");
        String timeFilterColumn = "e.updt_dt_tm";
        String inClauseColumn = "ed.facility_cd";

        String sqlQuery = " select COUNT(*) " +
                " from table1 ed, table2 e " +
                "  where ed.id = e.id " +
                "  and e.status_cd = ? " +
                "  and ed.active_ind = 1 " +
                "  and " + inClauseColumn + " in ( " + Arrays.stream(inClauseValues).map(v -> "?").collect(Collectors.joining(", ")) + " ) " +
                "  and systimestamp < FROM_TZ(cast(ed.end_effective_dt_tm as TIMESTAMP), ? ) " +
                "  and FROM_TZ(cast( " + timeFilterColumn + " as TIMESTAMP), ? ) between ? and ? ";

        try (PreparedStatement stmt = connection.prepareStatement(sqlQuery)) {
            int parameterIndex = 0;

            stmt.setDouble(++parameterIndex, doubleValue); // Setting e.status_cd
            for (Double value : inClauseValues) { //Setting ed.facility_cd in
                stmt.setDouble(++parameterIndex, value);
            }
            stmt.setString(++parameterIndex, tz_Id); /* Setting time zone for casting ed.end_effective_dt_tm */
            stmt.setString(++parameterIndex, tz_Id); /* Setting time zone for casting e.updt_dt_tm  */
            stmt.setTimestamp(++parameterIndex, startTime); // startTime
            stmt.setTimestamp(++parameterIndex, endTime); // endTime

            try (ResultSet rs = stmt.executeQuery()) {
                while (rs.next()) {
                    count = rs.getLong(1);
                }
            }
        }
        return count;
    }
Eugene
  • 5,269
  • 2
  • 14
  • 22
  • This is an existing query that is written and interpreted by JDBC in the way I described earlier and it is functioning correctly. I am just adding an additional time filter which will basically filter out results that fall between 2 specific timestamps of a particular dt_tm column. (AND between and is just what I want here). It is not always facility_cd, could be room_cd as well (some backend logic decides which column/value it is). I just wanted to know how I can append a query string that filters results between 2 timestamp values. – allocated Aug 12 '22 at 12:35
  • 1
    I have added examples for dynamic columns. Please review `timeFilterColumn` and `inClauseColumn`. In this way, you can specify any columns for your query. Also, another solution is to add columns directly to the query string, not via JDBC paramters. – Eugene Aug 12 '22 at 14:07
  • In solution 1 columns setting up via JDBC parameters, in solution 2 query string is prebuilt with required columns. – Eugene Aug 12 '22 at 14:11
  • Yes Eugene I agree, passing the timeFilter column that way was bad idea. Should have just used it by directly appending it to the query string? Also thanks for the solutions, the thing that finally worked was the `Timestamp.valueOf(java.time.LocalDateTime.ofInstant(Instant.ofEpochMilli(startTime)` and passing that with the zone id to setTimeStamp() as well as removing the explicit `TIMESTAMP` cast in the SQL query. – allocated Aug 13 '22 at 12:12
  • I think, yes, just append to query string. In case you handle dynamic columns via internal server logic then just append them to the query string it is clear and simple. But in case of parameters come from the client, for example, user-supplied values then it is better to use JDBC parameters because they protect from SQL injections. – Eugene Aug 13 '22 at 13:13