multiple prepareStatements

G

GVR_Mike

Hi. I'm new to Java and am having problems with a prepareStatement
using the ? as placeholders. Can anyone help me understand why I'm
getting an error?

Here is the error referring to: pstmtUpdate.setInt(2, limit);

java.lang.ArrayIndexOutOfBoundsException: 1
at
sun.jdbc.odbc.JdbcOdbcPreparedStatement.clearParameter(JdbcOdbcPreparedStatement.java:
1023)
at
sun.jdbc.odbc.JdbcOdbcPreparedStatement.setChar(JdbcOdbcPreparedStatement.java:
3057)
at
sun.jdbc.odbc.JdbcOdbcPreparedStatement.setString(JdbcOdbcPreparedStatement.java:
766)
at alarmfilter.AlarmFilter.filterAlarms(AlarmFilter.java:110)
at alarmfilter.AlarmFilter.main(AlarmFilter.java:143)


Here is my code:


package alarmfilter;

import java.sql.Connection;
import java.sql.Statement;
import java.sql.PreparedStatement;
import java.sql.DriverManager;
import java.sql.ResultSet;

public class AlarmFilter {

public void filterAlarms() throws Exception {
int site = 0, notify = 0, limit = 0, hours = 0;
int sCount = 0;
String cat = "", type = "", dev = "", dispStat = "", dispCom =
"";
Connection conn = null;
Statement stmt = null;
PreparedStatement pstmtCount = null;
PreparedStatement pstmtUpdate = null;
ResultSet rsUnack = null;
ResultSet rsSite = null;

try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
conn = DriverManager.getConnection(
"jdbc:eek:dbc:GVR2Test", <username>, <password>);
stmt = conn.createStatement();
rsUnack = stmt.executeQuery(
"SELECT VRSC.ALARMS_INCOMING.ALI_SITE_ID, "
+ "VRSC.ALARMS_INCOMING.ALI_ALARM_NOTIFY_NBR, "
+ "VRSC.ALARMS_INCOMING.ALI_ALARM_CATEGORY, "
+ "VRSC.ALARMS_INCOMING.ALI_ALARM_TYPE, "
+ "VRSC.ALARMS_INCOMING.ALI_DEVICE_NBR, "
+ "VRSC.ALARMS_INCOMING.ALI_DISPOSITION_STATUS, "
+ "VRSC.ALARMS_INCOMING.ALI_DISPOSITION_COMMENT, "
+ "VRSC.ALARM_TYPES.ALT_FILTER_LIMIT, "
+ "VRSC.ALARM_TYPES.ALT_FILTER_HOURS "
+ "FROM VRSC.ALARMS_INCOMING INNER JOIN
VRSC.ALARM_TYPES "
+ "ON (VRSC.ALARMS_INCOMING.ALI_ALARM_CATEGORY = "
+ "VRSC.ALARM_TYPES.ALT_ALARM_CAT) AND "
+ "(VRSC.ALARMS_INCOMING.ALI_ALARM_TYPE = "
+ "VRSC.ALARM_TYPES.ALT_ALARM_TYPE) "
+ "WHERE (((VRSC.ALARMS_INCOMING.ALI_STATUS) = 1)
AND "
+ "((VRSC.ALARM_TYPES.ALT_FILTER_LIMIT) <> '0')
AND "
+ "((VRSC.ALARMS_INCOMING.ALI_MC_ID) In "
+ "('000001','000004'))) "
+ "GROUP BY VRSC.ALARMS_INCOMING.ALI_SITE_ID, "
+ "VRSC.ALARMS_INCOMING.ALI_ALARM_NOTIFY_NBR, "
+ "VRSC.ALARMS_INCOMING.ALI_ALARM_CATEGORY, "
+ "VRSC.ALARMS_INCOMING.ALI_ALARM_TYPE, "
+ "VRSC.ALARMS_INCOMING.ALI_DEVICE_NBR, "
+ "VRSC.ALARMS_INCOMING.ALI_DISPOSITION_STATUS, "
+ "VRSC.ALARMS_INCOMING.ALI_DISPOSITION_COMMENT, "
+ "VRSC.ALARM_TYPES.ALT_FILTER_LIMIT, "
+ "VRSC.ALARM_TYPES.ALT_FILTER_HOURS;");

pstmtCount = conn.prepareStatement(
"SELECT
Count(VRSC.ALARMS_INCOMING.ALI_ALARM_NOTIFY_NBR) "
+ "AS CountOfALI_ALARM_NOTIFY_NBR "
+ "FROM VRSC.ALARMS_INCOMING "
+ "WHERE (((VRSC.ALARMS_INCOMING.ALI_SITE_ID) = ?)
" //setInt 1
+ "AND ((VRSC.ALARMS_INCOMING.ALI_ALARM_CATEGORY)
= ?) " //setString 2
+ "AND ((VRSC.ALARMS_INCOMING.ALI_ALARM_TYPE) = ?)
" //setString 3
+ "AND ((VRSC.ALARMS_INCOMING.ALI_DEVICE_NBR) = ?)
" //setString 4
+ "AND ((VRSC.ALARMS_INCOMING.ALI_UNACK_DATE_TIME)
"
Click to expand...
+ "(SYSDATE-(?/24))));"); //setInt 5

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
+ " - Closing per filter, does not exceed ? " //
setInt 2
+ "alarms in ? hours.', " //setInt 3
+ "VRSC_ALARMS_INCOMING.ALI_CLOSE_DATE_TIME =
SYSDATE, "
+ "VRSC_ALARMS_INCOMING.ALI_DISPOSITION_COMMENT =
'? " //setString 4
+ " - Closing per filter, does not exceed ? " //
setInt 5
+ "alarms in ? hours.', " //setInt 6
+ "VRSC_ALARMS_INCOMING.ALI_STATUS = 3, "
+ "VRSC_ALARMS_INCOMING.ALI_OPEN_DATE_TIME =
SYSDATE "
+ "WHERE
(((VRSC_ALARMS_INCOMING.ALI_ALARM_NOTIFY_NBR) = "
+ "?));"); //setInt 7

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");
if (sCount < limit) {
pstmtUpdate.setString(1, dispStat);
pstmtUpdate.setInt(2, limit);
pstmtUpdate.setInt(3, hours);
pstmtUpdate.setString(4, dispCom);
pstmtUpdate.setInt(5, limit);
pstmtUpdate.setInt(6, hours);
pstmtUpdate.setInt(7, notify);
pstmtUpdate.executeUpdate();
}
}
} catch (Exception e) {
e.printStackTrace();
} finally {
if (rsUnack != null) {
rsUnack.close();
}
if (rsSite != null) {
rsSite.close();
}
if (stmt != null) {
stmt.close();
}
if (pstmtCount != null) {
pstmtCount.close();
}
}
}

public static void main(String[] args) throws Exception {
AlarmFilter af = new AlarmFilter();
af.filterAlarms();
}
}

The queries work unless my problem is with the formatting of the ?'s.
Help is greatly appreciated.
 
F

Filip Larsen

GVR_Mike skrev:
Hi. I'm new to Java and am having problems with a prepareStatement
using the ? as placeholders. Can anyone help me understand why I'm
getting an error?

Here is the error referring to: pstmtUpdate.setInt(2, limit);
Click to expand...

What I could spot from a quick glance:

I think you miss a single-quote in the start of the line " - Closing per
filter, does not exceed ?". Also, is it really "legal" to interpolate a
string into the middle of a verbatim string like that in a statement?

I don't think statements have to end with a semicolon. Not sure if its
legal to have them there or not though.


Regards,
 
M

Martin Gregorie

GVR_Mike skrev:


What I could spot from a quick glance:

I think you miss a single-quote in the start of the line " - Closing per
filter, does not exceed ?". Also, is it really "legal" to interpolate a
string into the middle of a verbatim string like that in a statement?
Click to expand...
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.
 
R

Roedy Green

Hi. I'm new to Java and am having problems with a prepareStatement
using the ? as placeholders. Can anyone help me understand why I'm
getting an error?
Click to expand...

try comp.lang.java.databases for your SQL/JDBC problems.
 
R

Roedy Green

+ "VRSC_ALARMS_INCOMING.ALI_DISPOSITION_STATUS =
'? " //setString 1
+ " - Closing per filter, does not exceed ? "
Click to expand...


I think you want something like this:

+ "VRSC_ALARMS_INCOMING.ALI_DISPOSITION_STATUS ="
+ "CONCAT(? , ' - Closing per filter, does not exceed ', ? ,' ')"

I have never used the function myself, so I trust you will tweak it to
get it right.
 
G

GVR_Mike

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.
Click to expand...

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

Filip Larsen

GVR_Mike skrev:
I'm getting a
"java.sql.SQLException: [Oracle][ODBC][Ora]ORA-00971: missing SET
keyword"
at this prepareStatement when I go to executeUpdate().
Click to expand...

You'd probably want your update statement to look like "UPDATE .. SET
.... WHERE ..." or something similar (note the word SET).


Regards,
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Retrieving one autoincremented key value... 0
Connected SQLite to my java program but information are not submitted 2
Accessing datatable row.... 3
Survey details won't go through using php, ajax, Mysql 0
prepared statement 2
This is my problem... 0
All CRUD operations work except POST. Why? 2
Minimum Total Difficulty 0

Members online

Total: 50 (members: 1, guests: 49)
Robots: 447

Forum statistics

Threads
473,969
Messages
2,570,161
Members
46,709
Latest member
AustinMudi

Latest Threads

Top