Re: multiple prepareStatements
On Mar 29, 9:47 am, Martin Gregorie <mar...@see.sig.for.address>
wrote:
Agreed. The template SQL syntax is wrong. The ? symbol is always bare.
It is never surrounded by quotes. Assemble your complete disposition
status message in a String and use that to replace a single UNQUOTED
question mark.
Thanks VERY much for the replies everyone. I took Martin's suggestion
and it makes the sql much cleaner anyway so I like it better, but now
I'm getting a
"java.sql.SQLException: [Oracle][ODBC][Ora]ORA-00971: missing SET
keyword"
at this prepareStatement when I go to executeUpdate(). I even tried
removing the semicolon at the end of the SQl, same error. Is there
still something wrong with the SQL? Am I using the prepareStatements
properly? Are you able to prepare multiple statments like this ahead
of time off the same connection? Here is the code I changed:
pstmtUpdate = conn.prepareStatement(
"UPDATE VRSC_ALARMS_INCOMING INNER JOIN
VRSC_ALARM_TYPES "
+ "ON (VRSC_ALARMS_INCOMING.ALI_ALARM_TYPE = "
+ "VRSC_ALARM_TYPES.ALT_ALARM_TYPE) AND "
+ "(VRSC_ALARMS_INCOMING.ALI_ALARM_CATEGORY = "
+ "VRSC_ALARM_TYPES.ALT_ALARM_CAT) "
+ "SET VRSC_ALARMS_INCOMING.ALI_USER_ID =
'MONITOR', "
+ "VRSC_ALARMS_INCOMING.ALI_DISPOSITION_STATUS
= ?, " //setString 1
+ "VRSC_ALARMS_INCOMING.ALI_CLOSE_DATE_TIME =
SYSDATE, "
+ "VRSC_ALARMS_INCOMING.ALI_DISPOSITION_COMMENT
= ?, " //setString 2
+ "VRSC_ALARMS_INCOMING.ALI_STATUS = 3, "
+ "VRSC_ALARMS_INCOMING.ALI_OPEN_DATE_TIME =
SYSDATE "
+ "WHERE
(((VRSC_ALARMS_INCOMING.ALI_ALARM_NOTIFY_NBR) = ?));"); //setInt 3
while (rsUnack.next()) {
site = rsUnack.getInt("ALI_SITE_ID");
notify = rsUnack.getInt("ALI_ALARM_NOTIFY_NBR");
cat = rsUnack.getString("ALI_ALARM_CATEGORY");
type = rsUnack.getString("ALI_ALARM_TYPE");
dev = rsUnack.getString("ALI_DEVICE_NBR");
dispStat =
rsUnack.getString("ALI_DISPOSITION_STATUS");
dispCom =
rsUnack.getString("ALI_DISPOSITION_COMMENT");
limit = rsUnack.getInt("ALT_FILTER_LIMIT");
hours = rsUnack.getInt("ALT_FILTER_HOURS");
pstmtCount.setInt(1, site);
pstmtCount.setString(2, cat);
pstmtCount.setString(3, type);
pstmtCount.setString(4, dev);
pstmtCount.setInt(5, hours);
rsSite = pstmtCount.executeQuery();
rsSite.next(); //move to first (and only) record
sCount = rsSite.getInt("CountOfALI_ALARM_NOTIFY_NBR");
//System.out.println(site + " count = " + sCount);
if (sCount < limit) {
strDispStat = dispStat
+ " - Closing per filter, does not exceed
"
+ limit
+ " alarms in " + hours + " hours.";
strDispCom = dispCom
+ " - Closing per filter, does not exceed
"
+ limit
+ " alarms in " + hours + " hours.";
pstmtUpdate.setString(1, strDispStat);
pstmtUpdate.setString(2, strDispCom);
pstmtUpdate.setInt(3, notify);
pstmtUpdate.executeUpdate();
System.out.println(notify + " has been
filtered.");
}
}