aspose file tools*
The moose likes JDBC and the fly likes Help with prepared statement Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Help with prepared statement" Watch "Help with prepared statement" New topic
Author

Help with prepared statement

Philip Pross
Ranch Hand

Joined: Jan 17, 2001
Posts: 76
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
Greenhorn

Joined: Mar 27, 2002
Posts: 23
your trying to set the value of the second question mark
ie
String sqlSuc = "SELECT * FROM SUCCURSALE WHERE COSU_SUC=?1";
Philip Pross
Ranch Hand

Joined: Jan 17, 2001
Posts: 76
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
Ranch Hand

Joined: Sep 20, 2001
Posts: 49
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 ]

A Quitter Never Wins<br />A Winner Never Quits
Philip Pross
Ranch Hand

Joined: Jan 17, 2001
Posts: 76
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();
}
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Help with prepared statement