Re: multiple prepareStatements

From:

GVR_Mike <mjbruesch@triad.rr.com>

Newsgroups:

comp.lang.java.programmer

Date:

Sat, 29 Mar 2008 13:57:37 -0700 (PDT)

Message-ID:

<8c6e1827-aa9c-4fda-b9bf-f06a91a17766@b64g2000hsa.googlegroups.com>

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.");
                }
            }