Help with prepared statement

[Philip Pross]

Howdy,
I need help with this snippet of code, in one table (QUANTITE) I need the quantities for a certain product, also I need to know the name of the client, but it is a number that is index in another table (SUCCURSALE ). Its crashs on the the line that has: preStatSuc.setInt(2, iTemp2);
Connection c = null;
PreparedStatement preStatQte = null;
String sqlQte = "SELECT * FROM QUANTITE WHERE COPQ_NOPR =?";
PreparedStatement preStatSuc = null;
String sqlSuc = "SELECT * FROM SUCCURSALE WHERE COSU_SUC=?";
try
{

preStatQte = c.prepareStatement(sqlQte);
preStatSuc = c.prepareStatement(sqlSuc);
preStatQte.setInt(1, Integer.parseInt(productnumber) );

rs = preStatQte.executeQuery();
while( rs.next() )
{
sOutput = "";
sOutput += rs.getInt("COPQ_DISP#1%") + "~" ;
sOutput += rs.getInt("COPQ_DISP#2%") + "~" ;
sOutput += rs.getInt("COPQ_DISP#3%") + "~" ;
sOutput += rs.getInt("COPQ_DISP#4%") + "~" ;
sOutput += rs.getInt("COPQ_DISP#5%") + "~" ;
sOutput += rs.getInt("COPQ_DISP#6%") + "~" ;
sOutput += rs.getInt("COPQ_DISP#7%") + "~" ;
sOutput += rs.getInt("COPQ_DISP#8%") + "~" ;
sOutput += rs.getInt("COPQ_DISP#9%") + "~" ;
sOutput += rs.getInt("COPQ_DISP#10%") + "~" ;
sOutput += rs.getInt("COPQ_DISP#11%") + "~" ;
sOutput += rs.getInt("COPQ_DISP#12%") + "~" ;
sOutput += rs.getInt("COPQ_DISP#13%") + "~" ;
sOutput += rs.getInt("COPQ_DISP#14%") + "~" ;
sOutput += rs.getInt("COPQ_DISP#15%") + "~" ;
//TO GET THE NUMBER THAT IS THE KEY FOR ANOTHER TABLE
int iTemp1 = rs.getInt("COPQ_SUC#1%");
int iTemp2 = rs.getInt("COPQ_SUC#2%");
int iTemp3 = rs.getInt("COPQ_SUC#3%");
int iTemp4 = rs.getInt("COPQ_SUC#4%");
int iTemp5 = rs.getInt("COPQ_SUC#5%");
int iTemp6 = rs.getInt("COPQ_SUC#6%");
int iTemp7 = rs.getInt("COPQ_SUC#7%");
int iTemp8 = rs.getInt("COPQ_SUC#8%");
int iTemp9 = rs.getInt("COPQ_SUC#9%");
int iTemp10 = rs.getInt("COPQ_SUC#10%");
int iTemp11 = rs.getInt("COPQ_SUC#11%");
int iTemp12 = rs.getInt("COPQ_SUC#12%");
int iTemp13 = rs.getInt("COPQ_SUC#13%");
int iTemp14 = rs.getInt("COPQ_SUC#14%");
int iTemp15 = rs.getInt("COPQ_SUC#15%");

preStatSuc.setInt(1, iTemp1);
preStatSuc.setInt(2, iTemp2); //CRASHS HERE
preStatSuc.setInt(3, iTemp3);
preStatSuc.setInt(4, iTemp4);
preStatSuc.setInt(5, iTemp5);
preStatSuc.setInt(6, iTemp6);
preStatSuc.setInt(7, iTemp7);
preStatSuc.setInt(8, iTemp8);
preStatSuc.setInt(9, iTemp9);
preStatSuc.setInt(10, iTemp10);
preStatSuc.setInt(11, iTemp11);
preStatSuc.setInt(12, iTemp12);
preStatSuc.setInt(13, iTemp13);
preStatSuc.setInt(14, iTemp14);
preStatSuc.setInt(15, iTemp15);
rs1 = preStatSuc.executeQuery();
while( rs1.next() )
{
sOutput += rs1.getString("COSU_NOM") +"~";
}
rs1.close();
out.println(sOutput);
break;
}
}
this is the error I get
->
java.lang.ArrayIndexOutOfBoundsException
at sun.jdbc.odbc.JdbcOdbcPreparedStatement.clearParameter(Unknown Source)
at sun.jdbc.odbc.JdbcOdbcPreparedStatement.setInt(Unknown Source)
at ViDetail.doGet(ViDetail.java:197)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:740)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
at org.apache.tomcat.core.ServletWrapper.handleRequest(ServletWrapper.java:503)
at org.apache.tomcat.core.ServletWrapper.handleRequest(ServletWrapper.java:597)
at org.apache.tomcat.servlets.InvokerServlet.service(InvokerServlet.java:257)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
at org.apache.tomcat.core.ServletWrapper.handleRequest(ServletWrapper.java:503)
at org.apache.tomcat.core.ContextManager.service(ContextManager.java:559)
at org.apache.tomcat.service.http.HttpConnectionHandler.processConnection(HttpConnectionHandler.java:160)
at org.apache.tomcat.service.TcpConnectionThread.run(SimpleTcpEndpoint.java:338)
at java.lang.Thread.run(Unknown Source)
help please .... Phil

[John Williamer]

your trying to set the value of the second question mark
ie
String sqlSuc = "SELECT * FROM SUCCURSALE WHERE COSU_SUC=?1";

[Philip Pross]

I even tryed like this
SELECT * FROM SUCCURSALE WHERE COSU_SUC=?,?,?,?,?,?,?,?,?,?,?,?,?,?,?";
and it then gives me this error ->
pos: 43 ' ... COSU_SUC=?^ ,?,?,?,?,?,?,... - syntax error

[Syam Veerakumar]

once you get the values of iTemp add it into an array, from that loop the values to get the values of PreStatSuc like
for(int i=0; i< array.length ; i++){
preStatSuc.setInt(1,i);
// Get the result here through Resultset
// Store this value here using some collection class or in an array
}
[ July 24, 2002: Message edited by: Syam Veerakumar ]

[Philip Pross]

Thanks alot Syam, it worked great !!
This is what I did with your sample code

for(int i=0; i< iTemp.length ; i++)
{
if(iTemp[i] == 0)
{
sOutput += " ~";
continue;
}
preStatSuc.setInt(1,iTemp[i]);
rs1 = preStatSuc.executeQuery();

while( rs1.next() )
{
sOutput += rs1.getString("COSU_NOM") +" ~";
System.out.println("sOut " +sOutput);
}
rs1.close();
}