Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

problem retrieving record from database

Posted on 2003-03-21
4
Medium Priority
?
179 Views
Last Modified: 2010-05-18
I'm having problem retrieving record from database using Vector. I modify this code from JSP tutorial. Running this code return me

 java.lang.NullPointerException
     at org.postgresql.jdbc2.ResultSet.next(ResultSet.java:113)


Below are the codes:

Please consult me. Thank you


----------
select.jsp
----------
<%@ page import="rfq.DataBaseSelect"%>
<%@ page import="java.sql.*" %>


<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>Select everything from a database</title>
</head>
<body>

<jsp:useBean id="select" class="rfq.DataBaseSelect" scope="session">
</jsp:useBean>

<% out.print(select.connect()); %>
<br>
<% String val = "zamir1";%>
<% ResultSet rs = select.selectItemList(val); // 001 is a parameter passed to java %>

<p>Format results
<br>
<table>
<% while (rs.next()) { %>
   <tr>
        <td><% out.print(rs.getString("qid")); %></td>
        <td><% out.print(rs.getString("validity")); %></td>
        <td><% out.print(rs.getFloat("date_send")); %></td>
   </tr>
<% } %>
</table>

</body>
</html>

-------------------
DatabaseSelect.java
-------------------
package rfq;

import java.sql.*;
import java.util.Vector;

/**
 * DataBaseSelect.java
 * Written by Morgan Catlin
 *   August 19, 1999
 *
 * Variables:
 *   Vector result = where I put the results of a select query
 *
 * Methods:
 *   Vector getResult() = returns result
 *   void setResult() = sets result
 *   String connect() = connects to a database
 *   String select() = selects information from a database
 */

public class DataBaseSelect {

   private Vector result;

   public DataBaseSelect() {
      result = new Vector();
   } // constructor DataBaseSelect



   public String connect() {
      try {
         Class.forName("org.postgresql.Driver").newInstance();
         return "Driver Loaded!";
      } catch (Exception E) {
         return "Unable to load driver.";
      }
   }

   public ResultSet selectItemList(String qid) {

     String loginUser = "b2bscm";
     String loginPasswd = "b2bscm54xcll";
     String loginUrl = "jdbc:postgresql://localhost/b2bscm";

    Connection C = null;
    Statement Stmt = null;
    ResultSet myResult = null;
     try {
         C = DriverManager.getConnection(loginUrl, loginUser, loginPasswd);
         Stmt = C.createStatement();
         myResult = Stmt.executeQuery("SELECT qid FROM quotation WHERE userid_to='"+ qid +"'");

         // Clean up
         myResult.close();
         Stmt.close();
         C.close();
        }
    catch (SQLException E) {
        System.err.println(E.toString());
    }
    catch (Exception e) {
        System.err.println(e.toString());
    }
    finally {
        // ALWAYS, ALWAYS close the connections to the db
        try {
                Stmt.close();
                C.close();
        }       catch (Exception E) {
        }
    }
    return myResult;
   }

   /**
    * Accessor for result
    **/
   public Vector getResult() {
      return result;
   }

   /**
    * Mutator for result
    **/
   public void setResult(Vector avector) {
     result = avector;
   }

} // class DataBaseSelect


0
Comment
Question by:zzamir
  • 2
  • 2
4 Comments
 
LVL 3

Expert Comment

by:paskal
ID: 8179781
I guess the problem is that you try to access the ResultSet in the jsp while the connection and statement it is based on are already closed in the bean.
So instead of looping the ResultSet in the jsp you should loop it in your bean and put the values you want to have in the Vector. When looping is finished you can safely close your database connection.
In your JSP you can access the vector and loop over his content to show the database result.

So in your bean you will get something like:


C = DriverManager.getConnection(loginUrl, loginUser, loginPasswd);
Stmt = C.createStatement();
 myResult = Stmt.executeQuery("SELECT qid FROM quotation WHERE userid_to='"+ qid +"'");

// Define an object that contain your return values per row
// Like I use returnObject

while (rs.next()) {
  returnObject.setId(rs.getString("qid"));
  returnObject.setValidity(rs.getString("validity"));
  returnObject.setDate(rs.getFloat("date_send"));

  result.add(returnObject)
}
  // Clean up
  myResult.close();
  Stmt.close();
  C.close();
.....

Hope this will give you a direction.      
0
 

Author Comment

by:zzamir
ID: 8192413
thanks pascal for your response.. but can i use this method to put data into vector? if so, then how can i get to display more than one record?

ResultSet myResult = Stmt.executeQuery("SELECT acolumn from atable");
     
      while (myResult.next()) {
         result.addElement(myResult.getString(1));
      }
     
         // Clean up
         myResult.close();
         Stmt.close();
         C.close();
      return "Connection Success!";
      } catch (SQLException E) {
      return "SQLException: " + E.getMessage();
      }
0
 
LVL 3

Accepted Solution

by:
paskal earned 80 total points
ID: 8193422
Yes, you can use that method as I tried to explain before.

In you're JSP you will get something like:

<jsp:useBean id="select" class="rfq.DataBaseSelect" scope="session">
</jsp:useBean>
.....
<%
Enumeration elements = select.getResult().getEnumeration();
while( elements.hasMoreElements() ) {
  out.println(elements.nextElement())
}
%>

0
 

Author Comment

by:zzamir
ID: 8199562
thank you paskal. Take your points. :)
0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Choosing the right mix of apps is very much necessary for CPAs for making the most of the latest technology through which they can boost their growth.
How do you create a user-centered user experience on your website? And what are some things you should consider in the process?
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…
Suggested Courses
Course of the Month14 days, 10 hours left to enroll

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question