• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Problem when Retreiving from DataBase

 
vikram nalagampalli
Ranch Hand
Posts: 91
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi all,
I am trying to retreive information based on certain condition.
It is working fine if i use SELECT query excluding the WHERE part,But when i include WHERE part i am getting the following error. I am also enclosing my code after the errors. One more strange thing is that it is working for condition Gpa>=gpa but if i use other two conditions i.e degree and major. It is giving the following error.
Note: Gpa is text box in html file
where as degree is inputbox and major is combo.
Well does this matter by any chance.
Root cause:
java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 2.
at sun.jdbc.odbc.JdbcOdbc.createSQLException(JdbcOdbc.java:6031)
at sun.jdbc.odbc.JdbcOdbc.standardError(JdbcOdbc.java:6188)
at sun.jdbc.odbc.JdbcOdbc.SQLExecDirect(JdbcOdbc.java:2494)
at sun.jdbc.odbc.JdbcOdbcStatement.execute(JdbcOdbcStatement.java:334)
at sun.jdbc.odbc.JdbcOdbcStatement.executeQuery(JdbcOdbcStatement.java:249)
at _0002fsimpleSearch_0002ejspsimpleSearch_jsp_8._jspService(_0002fsimpleSearch_0002ejspsimpleSearch_jsp_8.java:92)
at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:119)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
at org.apache.jasper.servlet.JspServlet$JspCountedServlet.service(JspServlet.java:130)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
at org.apache.jasper.servlet.JspServlet$JspServletWrapper.service(JspServlet.java:282)
at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:429)
at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:500)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
at org.apache.tomcat.core.ServletWrapper.doService(ServletWrapper.java:405)
at org.apache.tomcat.core.Handler.service(Handler.java:287)
at org.apache.tomcat.core.ServletWrapper.service(ServletWrapper.java:372)
at org.apache.tomcat.core.ContextManager.internalService(ContextManager.java:812)
at org.apache.tomcat.core.ContextManager.service(ContextManager.java:758)
at org.apache.tomcat.service.http.HttpConnectionHandler.processConnection(HttpConnectionHandler.java:213)
at org.apache.tomcat.service.TcpWorkerThread.runIt(PoolTcpEndpoint.java:416)
at org.apache.tomcat.util.ThreadPool$ControlRunnable.run(ThreadPool.java:501)
at java.lang.Thread.run(Thread.java:484)
-----------------------------------------------------------------<HTML>
<HEAD><TITLE>SIMPLE SEARCH PAGE</TITLE></HEAD>
<BODY>
<%@ page session="true"%>
<%@ page import="java.sql.*" %>
<%@ page import="java.util.*" %>
<%@ page import="java.io.*" %>
<%@ page import="java.lang.String" %>
<% String degree=request.getParameter("degree");
String major=request.getParameter("major");
String gpa=request.getParameter("gpa");
String driver = "sun.jdbc.odbc.JdbcOdbcDriver";
String url = "jdbc dbc:Resume";
Class.forName(driver);
Connection con=null;
try{
con=DriverManager.getConnection(url);
String query="SELECT LastName, FirstName, Degree, Major1, Gpa FROM Profile WHERE Degree="+degree+" AND Gpa >="+gpa+" AND (Major1="+major+" OR Major2="+major+")";
Statement stmt=con.createStatement();
ResultSet rs=stmt.executeQuery(query);
%>
<table>
<tr>
<th>Last Name</th><th>First Name</th><th>Degree</th><th>Major</th><th>Gpa</th></tr>
<%
while(rs.next()){
String lastname=rs.getString(1);
String firstname=rs.getString(2);
String degree1=rs.getString(3);
String major1=rs.getString(4);
String gpa1=rs.getString(5);
%>
<tr>
<td><%=lastname%></td><td><%=firstname%></td><td><%=degree1%></td><td><%=major1%></td><td><%=gpa1%& gt;</td><td>View Resume</td>
</tr>
<%
}
rs.close();
rs=null;
stmt.close();
stmt=null;
}
finally{
if(con!=null){
con.close();}
}
%>
</table>
</BODY></HTML>

[This message has been edited by vikram nalagampalli (edited November 10, 2001).]
[This message has been edited by vikram nalagampalli (edited November 10, 2001).]
 
Peter den Haan
author
Ranch Hand
Posts: 3252
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I'm willing to bet that some of your parameters are actually strings, but you're putting them into the query without quotes. In general, building queries this way is asking for trouble (what if there are special characters in your strings, for instance?)
Use a PreparedStatement instead. Safer, and faster too.
- Peter
 
vikram nalagampalli
Ranch Hand
Posts: 91
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
hi peter,
i used prepared statement, but now i am getting the following error.
I also putting my code after the error
java.lang.ArrayIndexOutOfBoundsException
at sun.jdbc.odbc.JdbcOdbcPreparedStatement.clearParameter(JdbcOdbcPreparedStatement.java:1027)
at sun.jdbc.odbc.JdbcOdbcPreparedStatement.setChar(JdbcOdbcPreparedStatement.java:2766)
at sun.jdbc.odbc.JdbcOdbcPreparedStatement.setString(JdbcOdbcPreparedStatement.java:775)
at _0002fsimpleSearch_0002ejspsimpleSearch_jsp_16._jspService(_0002fsimpleSearch_0002ejspsimpleSearch_jsp_16.java:96)
at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:119)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
-----------------------------------------------------------------
<% String degree=request.getParameter("degree");
String major=request.getParameter("major");
String gpa=request.getParameter("gpa");
String driver = "sun.jdbc.odbc.JdbcOdbcDriver";
String url = "jdbc dbc:Resume";
Class.forName(driver);
Connection con=null;
try{
con=DriverManager.getConnection(url);
String query="SELECT LastName, FirstName, Degree, Major1, Gpa FROM Profile WHERE Degree= ? AND Gpa >= ?";
/* WHERE Degree="+degree+" AND Gpa >="+gpa+" AND (Major1="+major+" OR Major2="+major+")";*/
PreparedStatement pstmt=con.prepareStatement(query);
pstmt.setString(1," +degree+ ");
pstmt.setString(2," +major+ ");
pstmt.setString(3," +major+ ");
pstmt.setString(4," +gpa+ ");

ResultSet rs=pstmt.executeQuery();
%>
<table>
<tr>
<th>Last Name</th><th>First Name</th><th>Degree</th><th>Major</th><th>Gpa</th></tr>
<%
while(rs.next()){
String lastname=rs.getString(1);
String firstname=rs.getString(2);
String degree1=rs.getString(3);
String major1=rs.getString(4);
String gpa1=rs.getString(5);
%>
<tr>
<td><%=lastname%></td><td><%=firstname%></td><td><%=degree1%></td><td><%=major1%></td><td><%=gpa1%& gt;</td><td><form action="viewResume.jsp" method="post"><input type="submit" value="View Resume" name="submit"</td>
</tr>
<%
}
rs.close();
rs=null;
pstmt.close();
pstmt=null;
}
finally{
if(con!=null){
con.close();}
}
%>
 
Kyle Brown
author
Ranch Hand
Posts: 3892
5
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
One problem is that you're not using prepared statements right. The number of substitution parameters (?'s) in the SQL has to match the number of setXXX()'s that you're using. Here you have 2 substitution parameters in the SQL but you're using setString() for four indices... I think you need to either finish the SQL statement you started or comment out the last two setString() methods.
Kyle
------------------
Kyle Brown,
Author of Enterprise Java (tm) Programming with IBM Websphere
See my homepage at http://members.aol.com/kgb1001001 for other WebSphere information.
 
Kyle Brown
author
Ranch Hand
Posts: 3892
5
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
*Getting on soapbox*
Put this in it's own class and call that class from the JSP. JDBC does NOT belong in a JSP.
*Getting off soapbox*
Kyle
------------------
Kyle Brown,
Author of Enterprise Java (tm) Programming with IBM Websphere
See my homepage at http://members.aol.com/kgb1001001 for other WebSphere information.
 
Peter den Haan
author
Ranch Hand
Posts: 3252
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
< audience >clap clap clap clap clap clap clap< /audience >
 
vikram nalagampalli
Ranch Hand
Posts: 91
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,
I did replace the prepare statement, but my query still works for for only condition i.e. with Gpa.
The other two i.e. Degree and major are not retreving if i use them in query.

NOTE: I have a doubt if it is bcos Gpa is a textbox where as Degreee is input type and Major is dropdown.

Can anyone tell me if by any chance ther is any other means to deal with inputboxes and drop down boxes.
Thanks and regards
vikram
 
Peter den Haan
author
Ranch Hand
Posts: 3252
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Look at your setString statements, they are substituting String literals that don't look right
- Peter
[This message has been edited by Peter den Haan (edited November 11, 2001).]
 
Kyle Brown
author
Ranch Hand
Posts: 3892
5
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Try this:
String query="SELECT LastName, FirstName, Degree, Major1, Gpa FROM Profile WHERE Degree= ? AND Gpa >= ? AND (Major1 = ? OR Major2 = ?)"
PreparedStatement pstmt=con.prepareStatement(query);
pstmt.setString(1,degree);
pstmt.setString(2,gpa);
pstmt.setString(3,major);
pstmt.setString(4,major);
Do you see the difference?
Kyle

------------------
Kyle Brown,
Author of Enterprise Java (tm) Programming with IBM Websphere
See my homepage at http://members.aol.com/kgb1001001 for other WebSphere information.
 
vikram nalagampalli
Ranch Hand
Posts: 91
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi all,
I really thank you all so much. It really helped me.
Thanks again.
Regards,
vikki
 
Padmaja Godbole
Greenhorn
Posts: 14
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi!
I m using preparedStatement for my update statement as follows
---------------------
pstmt = con.prepareStatement ("update KIStructure set type= ? , name='?' , description='?', sql='?', othervalues=? where KIStructureID = ? ");
pstmt.setInt (1, keyIndicator.getChartType ());
pstmt.setString(2, keyIndicator.getName ());
pstmt.setString(3, keyIndicator.getDescription ());
pstmt.setString(4, keyIndicator.getSQL());
pstmt.setBoolean(5, keyIndicator.otherValues());
pstmt.setInt(6, kiid);
---------------------
but I am getting following after
pstmt.setString(3, keyIndicator.getDescription ());
---------------------------------
java.lang.ArrayIndexOutOfBoundsException
at sun.jdbc.odbc.JdbcOdbcPreparedStatement.clearParameter(JdbcOdbcPreparedStatement.java:1027)
at sun.jdbc.odbc.JdbcOdbcPreparedStatement.setChar(JdbcOdbcPreparedStatement.java:2788)
at sun.jdbc.odbc.JdbcOdbcPreparedStatement.setString(JdbcOdbcPreparedStatement.java:775)
-----------------------------------
pls suggest...
thanks
padmaja
[ May 11, 2003: Message edited by: Padmaja Godbole ]
 
Ajit Kanada
Ranch Hand
Posts: 95
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi padmaja
See that some of your ? are in single quotes..
Please correct them ...
change all '?' to just ?
And your problem will be solved..
Cheers
Ajit
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic