This week's book giveaway is in the Servlets forum.
We're giving away four copies of Murach's Java Servlets and JSP and have Joel Murach on-line!
See this thread for details.
The moose likes JSP and the fly likes Problem when Retreiving from DataBase Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Java » JSP
Bookmark "Problem when Retreiving from DataBase" Watch "Problem when Retreiving from DataBase" New topic
Author

Problem when Retreiving from DataBase

vikram nalagampalli
Ranch Hand

Joined: Oct 08, 2001
Posts: 91
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

Joined: Apr 20, 2000
Posts: 3252
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

Joined: Oct 08, 2001
Posts: 91
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

Joined: Aug 10, 2001
Posts: 3892
    
    5
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 of Persistence in the Enterprise and Enterprise Java Programming with IBM Websphere, 2nd Edition
See my homepage at http://www.kyle-brown.com/ for other WebSphere information.
Kyle Brown
author
Ranch Hand

Joined: Aug 10, 2001
Posts: 3892
    
    5
*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

Joined: Apr 20, 2000
Posts: 3252
< audience >clap clap clap clap clap clap clap< /audience >
vikram nalagampalli
Ranch Hand

Joined: Oct 08, 2001
Posts: 91
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

Joined: Apr 20, 2000
Posts: 3252
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

Joined: Aug 10, 2001
Posts: 3892
    
    5
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

Joined: Oct 08, 2001
Posts: 91
Hi all,
I really thank you all so much. It really helped me.
Thanks again.
Regards,
vikki
Padmaja Godbole
Greenhorn

Joined: May 10, 2003
Posts: 14
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 ]

Padmaja Godbole<br />---------------------<br />SCJP2
Ajit Kanada
Ranch Hand

Joined: Jan 23, 2001
Posts: 95
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
 
wood burning stoves
 
subject: Problem when Retreiving from DataBase
 
Similar Threads
Pool Manager with MySQL JDBC
Insert Query Not working
Atten : Mr. Yogen Vadnere
java.sql.SQLException: General error
JDBC Error (Missing Operator)