Just a suplementary note, not a solution to your problem:
You forgot to close the connection; take care that this is a serious problem
for a web site using jdbc as after a time all the objects will be alocated by the
DBMS and you will get an error.
Always close the connection after a comit or rollback... (and other jdbc objects)
or better use a connection pool.
Ionel Condor.
Pradeep Roy wrote:
> Hi All
>
> I am enclosing a complete code for database accessing. It would help
> beginners to know how to access the database from JSP.
> But I don't want the JSP to access the database. I want Beans to Access the
> database and send ResultSet accross to JSP. It is not working whenever I am
> trying to use the Beans to access it.
>
> Any other suggestion with details will be highly appreciated.
>
> Thanks & Regards
> Pradeep
>
> Could anyone suggest me what is wrong in my Bean which is expected to
> return a result set in the JSP file.
>
> package dbAccess;
>
> import java.util.*;
> import java.sql.*;
> import java.lang.*;
> import java.io.*;
>
> public class jspDbAccess {
>
> Vector names;
> Vector data;
> String firmcode;
> String workid;
> String job;
> String costcode;
> ResultSet resultSet=null;
>
> //
> public jspDbAccess()
> {
> ;
> }
>
> //
> public void setFirmcode(String firm)
> {
> if (firm != null )
> {
> firmcode=firm;
> }
> }
>
> //
> public void setWorkid(String id)
> {
> if (id != null )
> {
> workid=id;
> }
> }
>
> //
> public void setJob(String Jobname)
> {
> if (Jobname != null )
> {
> job=Jobname;
> }
> }
>
> //
> public void setCostcode(String code)
> {
> if (code != null )
> {
> costcode=code;
> }
> }
>
> //
> public void setSql(String sqlStr)
> {
> Connection connect=null;
> Statement statement=null;
>
> String driverName="com.ibm.as400.access.AS400JDBCDriver";
> String url="jdbc:as400://saturn" ;
> String user="xxxx";
> String passwd="xxxx";
>
> Connection connection=null;
>
> // Data to connect to database
>
> try
> {
> //loading the driver
> Class.forName(driverName);
> connection = DriverManager.getConnection(url, user, passwd);
>
> }
> // Error if fail to load the driver
> catch (Exception exp)
> {
> connection=null;
> System.out.println("Error occured at loading the Driver: "
> +exp);
> }
>
> try
> {
> statement=connection.createStatement();
> resultSet=statement.executeQuery(sqlStr);
> }
> catch (Exception exp)
> {
> System.out.println("Error occured at loading the Driver: "
> +exp);
> }
>
> }
>
> public ResultSet getResultset()
> {
> return resultSet;
> }
>
> public void reset()
> {
>
> }
> }
>
> My JSP is as follows:
>
> <%@ page import="java.sql.*,java.io.*, java.lang.*, javax.servlet.
> *,javax.servlet.http.*" %>
>
> <jsp:useBean id="dbConnect" class="dbAccess.jspDbAccess" scope="session">
> <jsp:setProperty name="dbConnect" property="*" />
> </jsp:useBean>
>
> <html>
> <head> <titleEnquiry</title> </head>
> <body text="White"
> bgcolor="#000000"
> leftmargin=0
> topmargin=0
> marginwidth=0
> marginheight=0>
>
> <hr align="left" width=19% size=2 color="White">
>
> <table width="175" border="0" cellspacing="0" cellpadding="0">
>
> <tr>
> <td valign="TOP">
> <img align="middle" src="Logo_3.gif" width=149 height=75 border=0
> alt="Logo"></td>
> </tr>
>
> </table>
>
> <hr align="left" width=19% size=2 color="White">
>
> <img align="middle" src="enquiry.gif" width=780 height=16 border=0 alt
> ="Jobpac Logo">
>
> <% if ((request.getParameter( "Workid" ) != null) &&
> (request.getParameter( "Job" ) != null) &&
> (request.getParameter( "Costcode" ) != null) &&
> (request.getParameter( "Sql" ) != null)) { %>
>
> <%
> String firm = request.getParameter( "Workid" );
> String jobname = request.getParameter( "Job" );
> String cstcode = request.getParameter( "Costcode" );
> String sqlStr = request.getParameter( "Sql" );
>
> String costcodeName="";
> String jobDescript="";
>
> String query1="SELECT OIORDN, OIITEM, OIDESC, OIQORD, OIUNIT, OIGUPR2,
> OITVAL from jp521dtai.jpordoi1 WHERE OIFMCD = '" + firm + "' AND OIJOB = '"
> + jobname + "' AND OICODE = '" + cstcode + "'" ;
>
> ResultSet rs=dbConnect.getResultset();
>
> ( the error I am getting here is the it is not a class method !!!) Please
> help...
>
> try
> {
> int size=rs.getFetchSize();
> %>
>
> <%=size%>
>
> <% }
> catch (Exception exp)
> {
> out.println("what is the error");
> }
>
> %>
>
> <%=sqlStr %>
>
> <br>
>
> <%
> try
> {
> %>
> <hr align="Left" Color="#ffffff" Size="1">
>
> <TABLE border="0" cellspacing="0" cellpadding="0">
>
> <tr>
>
> <td align="right"><b>Job:</b> </td>
> <td BGCOLOR="#FFFFFF"><FONT COLOR="#000000"> <b>
> <%=jobname%></b></FONT></td>
> <td align="right"> </td>
> <td BGCOLOR="#FFFFFF"><FONT COLOR="#000000"> <b>
> <%=jobDescript%></b></FONT></td>
> </tr>
> <tr>
> <td align="right"><b>Cost Code:</b> </td>
> <td BGCOLOR="#FFFFFF"><FONT COLOR="#000000"> <b>
> <%=cstcode%></b></FONT></td>
> <td align="right"> </td>
> <td BGCOLOR="#FFFFFF"><FONT COLOR="#000000"> <b>
> <%=costcodeName%></b></FONT></td>
> </tr>
>
> </TABLE>
>
> <hr align="Left" Color="#ffffff" Size="1">
> <P>
> <TABLE WIDTH=100% BORDER=1 cellspacing=0 cellpadding=0>
> <TR>
> <td BGCOLOR="#000000"><FONT COLOR="#FFFFFF" SIZE="2">ORDER
> NO</FONT></td>
> <td BGCOLOR="#000000"><FONT COLOR="#FFFFFF" SIZE
> ="2">ITEM</FONT></td>
> <td BGCOLOR="#000000"><FONT COLOR="#FFFFFF" SIZE
> ="2">DESCRIPTION</FONT></td>
> <td BGCOLOR="#000000"><FONT COLOR="#FFFFFF" SIZE
> ="2">QUANTITY</FONT></td>
> <td BGCOLOR="#000000"><FONT COLOR="#FFFFFF" SIZE="2">UNIT OF
> MEASURE</FONT></td>
> <td BGCOLOR="#000000"><FONT COLOR="#FFFFFF" SIZE
> ="2">RATE</FONT></td>
> <td BGCOLOR="#000000"><FONT COLOR="#FFFFFF" SIZE
> ="2">AMOUNT</FONT></td>
> </TR>
> <TR>
>
> <%
> while(rs.next()) {
> %>
> <TR>
> <td BGCOLOR="#FFFFFF"><FONT COLOR="#000000" SIZE="2">
> <%=rs.getObject(1) %> </td>
> <td BGCOLOR="#FFFFFF"><FONT COLOR="#000000" SIZE="2">
> <%=rs.getObject(2) %> </td>
> <td BGCOLOR="#FFFFFF"><FONT COLOR="#000000" SIZE="2">
> <%=rs.getObject(3) %> </td>
> <td Align="RIght" BGCOLOR="#FFFFFF"><FONT COLOR="#000000" SIZE="2">
> <%=rs.getObject(4) %> </td>
> <td BGCOLOR="#FFFFFF"><FONT COLOR="#000000" SIZE="2">
> <%=rs.getObject(5) %> </td>
> <td Align="RIght" BGCOLOR="#FFFFFF"><FONT COLOR="#000000" SIZE="2">
> <%=rs.getObject(6) %> </td>
> <td Align="RIght" BGCOLOR="#FFFFFF"><FONT COLOR="#000000" SIZE="2">
> <%=rs.getObject(7) %> </td>
> </TR>
> <% } %>
>
> </TABLE>
>
> <%
> }
> catch (Exception exp)
> {
> out.print("Error at data Processing. Please Try Again.");
> }
> %>
>
> <% } else { %>
>
> <%
> String id;
> String firm;
> String job;
> String jobDescript;
> String costcode;
> String costcodeDescript;
> String dataLibrary;
>
> String query0="SELECT WIWINO, WIMNAM, WIKM1, WIKM2, WIKM2 from r51ACTL.
> @WKIDWIP" ;
>
> %>
>
> -----
> ----- here I have hard code the database connection & resultset accessing
> HERE which is giving the Result Set Correctly
> -----
> -----
>
> <%
> try
> {
> %>
>
> <FORM NAME='login'METHOD=get>
>
> <TABLE border="0" cellspacing="0" cellpadding="0">
> <tr>
> <td align="right">Workid </td>
> <td><select name="id" size="1">
>
> <%
> while(rs.next()) {
> %>
>
> <option value="<%=rs.getObject(1) %>"> <%=rs.getObject(1) %>
> <% }
> }
> catch (Exception exp)
> {
> out.print("Error at data Processing. Please Try Again.");
> }
> %>
> </select>
> </td>
> </tr>
>
> <tr>
> <td align="right">Firm </td>
> <td><input type="TEXT" name="Workid" size="2"></td>
> </tr>
>
> <tr>
> <td align="right">Job </td>
> <td><input type="TEXT" name="Job" size="10"></td>
> </tr>
> <tr>
> <td align="right">Cost Code </td>
> <td><input type="TEXT" name="Costcode" size="10"></td>
> </tr>
> <tr>
> <td><input type="HIDDEN" name="Sql" value="SELECT WIWINO, WIMNAM,
> WIKM1, WIKM2, WIKM2 from r51ACTL.@WKIDWIP" ></td>
> </tr>
> <tr>
> <td align="Left"></td>
> <td align="right">
> <input type="SUBMIT" value=" Start Query ">
> <input type="RESET" value="Clear"></td>
> </tr>
> </TABLE>
> </FORM>
> <hr align="Left" Color="White" Size="1">
>
> Please enter the values & press 'Start Query'...press 'Clear' for new
> entry.
>
> <% } %>
>
> </BODY>
> </HTML>
>
> ===========================================================================
> To unsubscribe: mailto [EMAIL PROTECTED] with body: "signoff JSP-INTEREST".
> Some relevant FAQs on JSP/Servlets can be found at:
>
> http://java.sun.com/products/jsp/faq.html
> http://www.esperanto.org.nz/jsp/jspfaq.html
> http://www.jguru.com/jguru/faq/faqpage.jsp?name=JSP
> http://www.jguru.com/jguru/faq/faqpage.jsp?name=Servlets
===========================================================================
To unsubscribe: mailto [EMAIL PROTECTED] with body: "signoff JSP-INTEREST".
Some relevant FAQs on JSP/Servlets can be found at:
http://java.sun.com/products/jsp/faq.html
http://www.esperanto.org.nz/jsp/jspfaq.html
http://www.jguru.com/jguru/faq/faqpage.jsp?name=JSP
http://www.jguru.com/jguru/faq/faqpage.jsp?name=Servlets
