Resultset Problem ,Please help

[Boon Subra]

Hi All,

I am trying to create new class in Eclipse and trying to insert a value in the DB from java.I am not able to fetch the the resultset .I have been working all these days. I can fetch and update in other tables.Whatever table I create today,I am not able to perform anything.Pl.help.The code is
as follows.
I get a NullPointer Exception
public synchronized boolean addClientInfo()
{
PreparedStatement pstmt= null;
ResultSet rst= null;
PreparedStatement ps= null;
ResultSet rs= null;
boolean status = false;
int max_clientid = 0;

try
{

pstmt = conn.prepareStatement("select CL_id from clientinfonew");

pstmt.executeQuery();
System.out.println("after execute");
//rst.next();
if( ((rst).equals(null)) || (rst == null))
{
System.out.println("IF PART");
max_clientid=0;

}
else
{
System.out.println("else part");
max_clientid = rst.getInt("CL_id");

}
status=true;
int maxcl = max_clientid+1;
System.out.println(maxcl);
pstmt.close();

return true;

}
catch(Exception e)
{
System.out.println(e);

}
finally
{

}
return false;

}


public static void main(String[] args)
{

PoolManager poolMgr=PoolManager.getInstance();
Connection conn = poolMgr.getConnection("myconn");
System.out.println(conn);
com.fms.portal.ClientInfoNew clinfo = new com.fms.portal.ClientInfoNew();
clinfo.setConn(conn);
try
{

clinfo.setCL_id(0);
clinfo.setCL_fname("clientfname");

boolean add = clinfo.addClientInfo();
if(add)
{
System.out.println("added");
}
else
{
System.out.println("Not Added");
}

}
catch(Exception e)
{
System.out.println(e);
e.printStackTrace();
e.getMessage();
}
poolMgr.freeConnection("myconn", conn);
poolMgr.release();
}

[Raj Chila]

pstmt = conn.prepareStatement("select CL_id from clientinfonew");
pstmt.executeQuery();
System.out.println("after execute");
//rst.next();
if( ((rst).equals(null)) || (rst == null))

Keeping all other Typing mistakes " Imagine it is correct "...
Then as far as I see, your SQL statement is not well formed...is it a typo? well if it is not...the statement will probably return all the values in the given tables....
Secondly, you are checking if the resultset is null, but when its not null...you are supposed to call on

rst.next(); 

before you invoke getXXX methods to bring the cursor to the first record.
so I think first you need to correct these two.

[Blake Minghelli]

First of all, I agree with the previous poster, your SQL to get the CL_id looks suspicious. It looks like you are just trying to determine the max CL_id that exists in the table so you can create a new record using the next CL_id possible, correct? If so, then your query should look something like this: "select max(CL_id) from clientinfonew".
Next, the NullPointerException is porbably coming from this line:

if( ((rst).equals(null)) || (rst == null)) 

because rst is never assigned a value. Statement.executeQuery() returns a ResultSet object that you are ignoring in this code:

pstmt.executeQuery();

That code should be something like this:

rst = pstmt.executeQuery(); // Assign result of query to rst var

Then what you want to do is check the result of the query. With ResultSet objects, you must first try to move the cursor to the first record via the next() method which will return true if there is a record. Then check the value of CL_id in the ResultSet and create your new CL_id based off of that. Try modifying your code as follows:

public synchronized boolean addClientInfo() {
  PreparedStatement pstmt= null;
  ResultSet rst= null;
  boolean status = false;
  int max_clientid = 0;
  try {
    pstmt = conn.prepareStatement("select max(CL_id) from clientinfonew");
    rst = pstmt.executeQuery();
    System.out.println("after execute");
    if (rst.next()) {
      int i = rst.getInt(1); // assuming CL_id is an Integer column
      // Now you have to check if the last column read was null
      if (!rst.wasNull()) {
        // If it wasn't null, then i wil be the max CL_id in the table
        max_clientid = i;
      }
    }
    // Now increment the CL_id value
    max_clientid++;
    // Now do something with the new CL_id, like inserting a new record
    ......

Hope that helps