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.
Query problems:
and {in} just ed.facility_cd in ( ? ) and setting parameter for each array elementjava.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;
}