?
Solved

problem retrieving record from database

Posted on 2003-03-21
4
Medium Priority
?
177 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
Introducing Priority Question, our latest feature.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Suggested Courses

765 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