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
