JSP and Bean Error
Dear All:
Just compiled my bean and tried to use my jsp page to connect ot Oracle and return data to browser.This is the error message:
java.sql.SQLException: invalid arguments in call
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java)
Here is my bean:
package com.abc.query;
import java.sql.*;
import java.io.*;
import java.lang.*;
import java.text.*;
public class QueryBean{
private String dbURL="jdbc:oracle:thin:@myhost:1521:orcl";
private String dbDriver="oracle.jdbc.driver.OracleDriver";
private String username="me";
private String password="ItsASecret";
private Connection dbCon;
private String county;
private String year;
SimpleDateFormat sdf = new SimpleDateFormat("yyyy");//for Oracle Date format
public QueryBean(){
super(); //references super class
}
public String getCounty(){
return this.county;
}
public String getYear(){
return this.year;
}
//data from form
public void setCounty(String tcounty){
this.county=tcounty;
}
public void setYear(String tyear){
this.year=tyear;
}
public String doQuery() throws
ClassNotFoundException, SQLException{
Class.forName(dbDriver);
dbCon=DriverManager.getConnection(dbURL+username+password);
Statement s=dbCon.createStatement();
String sql= "SELECT * FROM census_county_age_sex_tbl WHERE county_5fips_code='"+this.county+"' and";
sql=sql + "to_char(Year,'YYYY')+ ='"+this.year+"' ORDER BY YEAR, SEX_CODE, AGE_CLASS";
ResultSet rs = s.executeQuery(sql);
String rt = "";
while(rs.next()){
rt = rt + "<tr> <td>" + sdf.format(rs.getDate("year")) + "</td>";
rt = rt + "<td>" + rs.getString("age_class") + "</td>";
rt = rt + "<td>" + rs.getString("sex_code") + "</td>";
rt = rt + "<td>" + rs.getString("nbr") + "</td> </tr>";
}
dbCon.close();
rs.close();
s.close();
return rt;
}
}
Here are my jsp pages:
<html>
<body>
This is the Query page.
<p>
<form method=post action="queryBean2.jsp">
County: <input type=text name=tcounty>
Year: <input type=text name=tyear>
<input type=submit>
</form>
</body>
</html>
queryBean2.jsp:
%@ page language="java" %>
<html>
<body>
<jsp:useBean id="queryBean" scope="request" class="mdh.query.QueryBean" />
<% queryBean.setCounty(request.getParameter("tcounty")); %>
<% queryBean.setYear(request.getParameter("tyear")); %>
The result of the query is:
<table>
<tr>
<th width="15%"> Year </th>
<th width="15%"> Age </th>
<th width="15%"> Sex </th>
<th width="15%"> Number</th>
</tr>
<%= queryBean.doQuery() %>
</table>
</body>
</html>
Any help would be greatly appreciated.
R
Try this -- it seems you have the connection mostly right...
....
Driver driver = (Driver)Class.forname(dbDriver).newInstance();
Connection conn = DriverManager.getConnection(dbURL,username,password);
Statement stmt = conn.createStatement();
....
Does it help any?
Actually what I meant to say is replace:
Class.forName(dbDriver);
dbCon=DriverManager.getConnection(dbURL+username+password);
with:
Driver driver = (Driver)Class.forname(dbDriver).newInstance();
dbCon = DriverManager.getConnection(dbURL,username,password);
It should help...
Thank you for the quick reply.
This this my new error:
unreported exception java.Lang.InstiationException; must be caught or declare to be thrown.
The compiler is pointer to this line of code:
Driver driver = (Driver)Class.forName(dbDriver).newInstance();
Thank you very much.
R
You need to do the following:try { // Do all your connection and query stuff here} catch (Exception ex) {// Do something with exception}Helps more?
Thanks agian for your help...
I've tried the try/catch in the past but did not succeed. Here is the bottom portion of the code w/ a try and catch.
public String doQuery(){
try{
Driver driver = (Driver)Class.forName(dbDriver).newInstance();
dbCon = DriverManager.getConnection(dbURL,username,password);
Statement s=dbCon.createStatement();
String sql= "SELECT * FROM census_county_age_sex_tbl WHERE county_5fips_code='"+this.county+"' and";
sql=sql + "to_char(Year,'YYYY')+ ='"+this.year+"' ORDER BY YEAR, SEX_CODE, AGE_CLASS";
ResultSet rs = s.executeQuery(sql);
String rt = "";
while(rs.next()){
rt = rt + "<tr> <td>" + sdf.format(rs.getDate("year")) + "</td>";
rt = rt + "<td>" + rs.getString("age_class") + "</td>";
rt = rt + "<td>" + rs.getString("sex_code") + "</td>";
rt = rt + "<td>" + rs.getString("nbr") + "</td> </tr>";
}
dbCon.close();
rs.close();
s.close();
return rt;
}
catch (ClassNotFoundException e){
System.out.println("An Error has occurred:"+e);
}
catch (java.sql.SQLException sqle){
System.out.println("An Error has occurred:"+sqle);
}
}
I now get two error:
"unreported exception java.lang.InstantiationException; must be caught or declared to be thrown" for this line
Driver driver = (Driver)Class.forName(dbDriver).newInstance();
"missing return statement" for this line
public String doQuery(){
Thank you very much for your attention.
R
Try this,
public String doQuery(){
String rt = "";
try{
Driver driver = (Driver)Class.forName(dbDriver).newInstance();
dbCon = DriverManager.getConnection(dbURL,username,password);
Statement s=dbCon.createStatement();
String sql= "SELECT * FROM census_county_age_sex_tbl WHERE county_5fips_code='"+this.county+"' and";
sql=sql + "to_char(Year,'YYYY')+ ='"+this.year+"' ORDER BY YEAR, SEX_CODE, AGE_CLASS";
ResultSet rs = s.executeQuery(sql);
while(rs.next()){
rt = rt + "<tr> <td>" + sdf.format(rs.getDate("year")) + "</td>";
rt = rt + "<td>" + rs.getString("age_class") + "</td>";
rt = rt + "<td>" + rs.getString("sex_code") + "</td>";
rt = rt + "<td>" + rs.getString("nbr") + "</td> </tr>";
}
dbCon.close();
rs.close();
s.close();
return rt;
}
catch (ClassNotFoundException e){
System.out.println("An Error has occurred:"+e);
}
catch (java.sql.SQLException sqle){
System.out.println("An Error has occurred:"+sqle);
}
catch (Exception ex){
System.out.println("An Error has occurred:"+exf);
}
return rt;
}
Sudha
sudha_mp:
Thanks for the help. The bean compiled and I now longer receive the error message. But, My results are empty except for my html code...I'll add a couple more duke dollars if you can help solve this problem. My jsp form and action page are quite simple..
Here is the query page:
<html>
<body>
This is the Query page.
<p>
<form method=post action="queryBean2.jsp">
County: <input type=text name=tcounty>
Year: <input type=text name=tyear>
<input type=submit>
</form>
</body>
</html>
queryBean2.jsp:
<html>
<body>
<jsp:useBean id="queryBean" scope="request" class="mdh.query.QueryBean" />
<% queryBean.setCounty(request.getParameter("tcounty")); %>
<% queryBean.setYear(request.getParameter("tyear")); %>
The result of the query is:
<table>
<tr>
<th width="15%"> Year </th>
<th width="15%"> Age </th>
<th width="15%"> Sex </th>
<th width="15%"> Number</th>
</tr>
<%= queryBean.doQuery() %>
</table>
<p>
</body>
</html>
Thank you very much.
R
One small change in your sql query,String sql= "SELECT * FROM census_county_age_sex_tbl WHERE county_5fips_code='"+this.county+"' and";sql += " to_char(Year,'YYYY') ='"+this.year+"' ORDER BY YEAR, SEX_CODE, AGE_CLASS";Hope this will work.Sudha
Dear People:
Here is my sql statement that works:
ResultSet rs = s.executeQuery("SELECT * FROM census_county_age_sex_tbl WHERE county_5fips_code='"+this.county+"' and to_char(Year,'YYYY')= '"+this.year+"' ORDER BY YEAR, SEX_CODE, AGE_CLASS");
I isolated my problem to this part of my original sql string and specifically the equal sign:
"to_char(Year,'YYYY')+ ='"+this.year+"'
First, I no longer concatenate my sql statement but now have one long string. This makes debugging those pesky double and single quotes, much easier.
Second, went from character to character to check the placement of the single and double quotes.
Thank you very much for your all your help.
R
Second,
