empty resultset

[MauBady]

Hello,
I'm new in java. my actual problem is :
how can i test if a resultset is empty?

I have tested:

rs == null
rs.next()
rs.isAfterLast()
rs.last()

but the result is the exception java.lang.NullPointerException

[r035198x]

Hello,
I'm new in java. my actual problem is :
how can i test if a resultset is empty?

I have tested:

rs == null
rs.next()
rs.isAfterLast()
rs.last()

but the result is the exception java.lang.NullPointerException

From the docs

"The next method moves the cursor to the next row, and because it returns false when there are no more rows in the ResultSet object, it can be used in a while loop to iterate through the result set"

[MauBady]

Thanks
but if i try to iterate i obtain the java.lang.NullPointerException. It is possible test the resutset if empty or no without throw the exception?

[r035198x]

Thanks
but if i try to iterate i obtain the java.lang.NullPointerException. It is possible test the resutset if empty or no without throw the exception?

Let's see your code. Are you initializing the resultset properly? executeQuery never returns null.

[MauBady]

this is the code:

ResultSet rs = dbConn.EseguiLettura(strSql);
dbConn.closeConn();
while (rs.next())
{
System.out.println("not empty");
}

and the method call is

public ResultSet EseguiLettura(String sSqlToExecute) throws SQLException
{
Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSI TIVE, ResultSet.CONCUR_UPDATABLE);
ResultSet rs = stmt.executeQuery(sSqlToExecute);
rs.close();
stmt.close();
return rs;
}

[r035198x]

this is the code:

ResultSet rs = dbConn.EseguiLettura(strSql);
dbConn.closeConn();
while (rs.next())
{
System.out.println("not empty");
}

and the method call is

public ResultSet EseguiLettura(String sSqlToExecute) throws SQLException
{
Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSI TIVE, ResultSet.CONCUR_UPDATABLE);
ResultSet rs = stmt.executeQuery(sSqlToExecute);
rs.close();
stmt.close();
return rs;
}

1.)Why are you returning a closed resultset?
2.)You should really post the full code for it to be obvious where the error is. I can remove it afterwards if you want.
3.)When posting the code please remember to use code tags.

[MauBady]

Thanks

This is the code

 
import java.util.*;
import java.sql.*;
public class EseguiProcesso 
{
  private static String msgInfo="Inizio EseguiProcesso.";
  private static String strSql=null;
 
 public static void main(String args[]) throws OwnException
    {
      Configurazione config;
      DbConn dbConn;
      Processo processo;
      String sDataElab=null;
      String sNomeProcesso=null;
      String sFlagForzatura=null;
      LeggiStringaSQL leggiStrSql;
      try 
      {
         System.out.println(msgInfo);
         config = new Configurazione();
        if (args.length!=0)
        {
          config.Valorizza(args[0]);
        }
        else
        {
          throw new OwnException("Attenzione occorre fornire il nome del file di configurazine.");
        }
         msgInfo="Lettura Parametri.";
        if (args.length==config.getnumParam() )
        {
          for (int i=0; i < args.length; i++)
          {
           if (i==1)
           {
             sNomeProcesso = args[i];
           }
           if (i==2)
           {
             verificaData(args[i]);
             sDataElab = args[i];
           }
           if (i==3)
           {
             sFlagForzatura = args[i];
           }
 
          }
        }
        else
        {
          throw new OwnException("Numero di parametri attesi errato.");
        }
         msgInfo="Connessione al database.";
         dbConn = new DbConn(config.gettipoDB(), config.geturlDB(),config.getusrDB(),config.getpwdDB());
        dbConn.openConn();
        leggiStrSql = new LeggiStringaSQL();
        String arrParmSql[] = {sDataElab,sNomeProcesso};
          msgInfo="Lettura Select per Processo.";
        strSql=leggiStrSql.EstraiStrSQL("[PROCESSO]", arrParmSql);
         msgInfo="Lettura Recordset per Processo.";
         ResultSet rs = dbConn.EseguiLettura(strSql);
        dbConn.closeConn();
        while (rs.next())
        {
          System.out.println("nullo");
        }
        processo = new Processo(sNomeProcesso,sDataElab,dbConn);
        System.out.println("Fine EseguiProcesso.");
        System.exit(0);    
      }
      catch (SQLException exc) 
      {
        System.out.println(msgInfo + " Errore : " + exc.getErrorCode() + " :"+ exc.getMessage());
        System.exit(1);    
      }
    }
 
    public static void verificaData(String dataToVerify) throws OwnException
    {
      int anno;
      int mese;
      int giorno;
      String msg=null;
      int daysPerMonth[] = {0, 31, 28, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31 };
 
      try
      {
        msg="Funzione verificaData. Valorizzazione anno :"+dataToVerify+" ";
        anno = Integer.valueOf(dataToVerify.substring(0,4)).intValue();
        msg="Funzione verificaData. Valorizzazione mese :"+dataToVerify+" ";
        mese = Integer.valueOf(dataToVerify.substring(4,6)).intValue();
        msg="Funzione verificaData. Valorizzazione giorno :"+dataToVerify+" ";
        giorno = Integer.valueOf(dataToVerify.substring(6,8)).intValue();
        msg="Funzione verificaData. Verifica mese :"+dataToVerify+" ";
        if ((mese<1)|| (mese>12))
        {
           throw new OwnException("Mese errato.");
        }
        msg="Funzione verificaData. Verifica giorno :"+dataToVerify+" ";        
 
        if (!( giorno > 0 && giorno <= daysPerMonth[ mese ] ))
        {
         if (mese==2)
         {
           if (!( mese == 2 && giorno == 29 && ( anno % 400 == 0 || ( anno % 4 == 0 && anno % 100 != 0 ) ) ))
            {
              throw new OwnException("Numero di giorni errato (anno bisestile).");
            }
          }
          else
          {
            throw new OwnException("numero di giorni errato.");
          }
        }
 
 
      }
      catch (Exception exc)
      {
       throw new OwnException(msg +exc.getMessage());
      }
    }
}
 
// DbConn Class
 
import java.sql.*;
import java.io.*;
import oracle.jdbc.*;
import oracle.jdbc.pool.*;
public class DbConn 
{
  private String sDBType;
  private String sServerURL;
  private String sUser;
  private String sPwd;
  private String sMsg;
  private String nome="Classe DbConn.";
  private Connection conn;
  private OracleDataSource ods;
  public DbConn(String dbType, String serverUrl, String user, String pwd)
  {
      sMsg="Funzione conn. Valorizzazione Parametri.";
      sDBType=dbType;
      sServerURL=serverUrl;
      sUser=user;
      sPwd=pwd;
  }
 
  public void openConn() throws SQLException,OwnException
  {
    try
    {
      sMsg="Funzione conn. Creazione connessione.";
      if ((sDBType.trim()).compareTo("ORACLE")==0 )
      {
        ods = new OracleDataSource();
        ods.setURL(sServerURL);
        conn = ods.getConnection(sUser,sPwd);
      }
      else
      {
        throw new OwnException("Attenzione!!! Tipo di Database non Gestito. "+sDBType);
      }
    }
    catch(SQLException e)
    {
      throw new OwnException(nome+" "+sMsg+ " " + e.getErrorCode() + " :"+e.getMessage());
    }
  }
  public void closeConn() throws SQLException,OwnException
  {
    try
    {
      sMsg="Funzione closeConn. Chiusura connessione.";
      conn.close();
    }
    catch(SQLException e)
    {
      throw new OwnException(nome+" "+sMsg+ " "+ e.getErrorCode() + " :"+e.getMessage());
    }
  }
  public ResultSet EseguiLettura(String sSqlToExecute) throws SQLException
  {
      Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
      ResultSet rs = stmt.executeQuery(sSqlToExecute);
//      rs.close();
      stmt.close();
      return rs;
  }
 
 
 

[r035198x]

Thanks

This is the code

 
import java.util.*;
import java.sql.*;
public class EseguiProcesso 
{
  private static String msgInfo="Inizio EseguiProcesso.";
  private static String strSql=null;
 
 public static void main(String args[]) throws OwnException
    {
      Configurazione config;
      DbConn dbConn;
      Processo processo;
      String sDataElab=null;
      String sNomeProcesso=null;
      String sFlagForzatura=null;
      LeggiStringaSQL leggiStrSql;
      try 
      {
         System.out.println(msgInfo);
         config = new Configurazione();
        if (args.length!=0)
        {
          config.Valorizza(args[0]);
        }
        else
        {
          throw new OwnException("Attenzione occorre fornire il nome del file di configurazine.");
        }
         msgInfo="Lettura Parametri.";
        if (args.length==config.getnumParam() )
        {
          for (int i=0; i < args.length; i++)
          {
           if (i==1)
           {
             sNomeProcesso = args[i];
           }
           if (i==2)
           {
             verificaData(args[i]);
             sDataElab = args[i];
           }
           if (i==3)
           {
             sFlagForzatura = args[i];
           }
 
          }
        }
        else
        {
          throw new OwnException("Numero di parametri attesi errato.");
        }
         msgInfo="Connessione al database.";
         dbConn = new DbConn(config.gettipoDB(), config.geturlDB(),config.getusrDB(),config.getpwdDB());
        dbConn.openConn();
        leggiStrSql = new LeggiStringaSQL();
        String arrParmSql[] = {sDataElab,sNomeProcesso};
          msgInfo="Lettura Select per Processo.";
        strSql=leggiStrSql.EstraiStrSQL("[PROCESSO]", arrParmSql);
         msgInfo="Lettura Recordset per Processo.";
         ResultSet rs = dbConn.EseguiLettura(strSql);
        dbConn.closeConn();
        while (rs.next())
        {
          System.out.println("nullo");
        }
        processo = new Processo(sNomeProcesso,sDataElab,dbConn);
        System.out.println("Fine EseguiProcesso.");
        System.exit(0);    
      }
      catch (SQLException exc) 
      {
        System.out.println(msgInfo + " Errore : " + exc.getErrorCode() + " :"+ exc.getMessage());
        System.exit(1);    
      }
    }
 
    public static void verificaData(String dataToVerify) throws OwnException
    {
      int anno;
      int mese;
      int giorno;
      String msg=null;
      int daysPerMonth[] = {0, 31, 28, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31 };
 
      try
      {
        msg="Funzione verificaData. Valorizzazione anno :"+dataToVerify+" ";
        anno = Integer.valueOf(dataToVerify.substring(0,4)).intValue();
        msg="Funzione verificaData. Valorizzazione mese :"+dataToVerify+" ";
        mese = Integer.valueOf(dataToVerify.substring(4,6)).intValue();
        msg="Funzione verificaData. Valorizzazione giorno :"+dataToVerify+" ";
        giorno = Integer.valueOf(dataToVerify.substring(6,8)).intValue();
        msg="Funzione verificaData. Verifica mese :"+dataToVerify+" ";
        if ((mese<1)|| (mese>12))
        {
           throw new OwnException("Mese errato.");
        }
        msg="Funzione verificaData. Verifica giorno :"+dataToVerify+" ";        
 
        if (!( giorno > 0 && giorno <= daysPerMonth[ mese ] ))
        {
         if (mese==2)
         {
           if (!( mese == 2 && giorno == 29 && ( anno % 400 == 0 || ( anno % 4 == 0 && anno % 100 != 0 ) ) ))
            {
              throw new OwnException("Numero di giorni errato (anno bisestile).");
            }
          }
          else
          {
            throw new OwnException("numero di giorni errato.");
          }
        }
 
 
      }
      catch (Exception exc)
      {
       throw new OwnException(msg +exc.getMessage());
      }
    }
}
 
// DbConn Class
 
import java.sql.*;
import java.io.*;
import oracle.jdbc.*;
import oracle.jdbc.pool.*;
public class DbConn 
{
  private String sDBType;
  private String sServerURL;
  private String sUser;
  private String sPwd;
  private String sMsg;
  private String nome="Classe DbConn.";
  private Connection conn;
  private OracleDataSource ods;
  public DbConn(String dbType, String serverUrl, String user, String pwd)
  {
      sMsg="Funzione conn. Valorizzazione Parametri.";
      sDBType=dbType;
      sServerURL=serverUrl;
      sUser=user;
      sPwd=pwd;
  }
 
  public void openConn() throws SQLException,OwnException
  {
    try
    {
      sMsg="Funzione conn. Creazione connessione.";
      if ((sDBType.trim()).compareTo("ORACLE")==0 )
      {
        ods = new OracleDataSource();
        ods.setURL(sServerURL);
        conn = ods.getConnection(sUser,sPwd);
      }
      else
      {
        throw new OwnException("Attenzione!!! Tipo di Database non Gestito. "+sDBType);
      }
    }
    catch(SQLException e)
    {
      throw new OwnException(nome+" "+sMsg+ " " + e.getErrorCode() + " :"+e.getMessage());
    }
  }
  public void closeConn() throws SQLException,OwnException
  {
    try
    {
      sMsg="Funzione closeConn. Chiusura connessione.";
      conn.close();
    }
    catch(SQLException e)
    {
      throw new OwnException(nome+" "+sMsg+ " "+ e.getErrorCode() + " :"+e.getMessage());
    }
  }
  public ResultSet EseguiLettura(String sSqlToExecute) throws SQLException
  {
      Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
      ResultSet rs = stmt.executeQuery(sSqlToExecute);
//      rs.close();
      stmt.close();
      return rs;
  }
 
 
 

Now go over the exception trace again and indicate which line threw the exception.

[MauBady]

this is the error

Exception in thread "main" java.lang.NullPointerException
at oracle.jdbc.driver.UpdatableResultSet.next(Updatab leResultSet.java:251)
at EseguiProcesso.main(EseguiProcesso.java:72)


and this is the line:

 
while (rs.next())
        {
          System.out.println("nullo");
        }
 

[sicarie]

dbConn.closeConn();
while (rs.next())
{
System.out.println("not empty");
}

I think your problem lies in that ResultSet requires a persistent connection - you close the connection (dbConn.closeConn(); ) and you will lose the pointer to the ResultSet. You want to close the connection after you parse your rs.

[r035198x]

I think your problem lies in that ResultSet requires a persistent connection - you close the connection (dbConn.closeConn(); ) and you will lose the pointer to the ResultSet. You want to close the connection after you parse your rs.

Yeah the resultset is closed, from the docs

"A ResultSet object is automatically closed by the Statement object that generated it when that Statement object is closed, re-executed, or is used to retrieve the next result from a sequence of multiple results"

[MauBady]

Thank you
Yes, the problem seem to be this.

i have commented also the line related to the closure of the Statement and now it seem correct.

  public ResultSet EseguiLettura(String sSqlToExecute) throws SQLException
  {
      Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
      ResultSet rs = stmt.executeQuery(sSqlToExecute);
//      rs.close();
//      stmt.close();
      return rs;
  }
 
 

Thank you

[sicarie]

Thank you
Yes, the problem seem to be this.

i have commented also the line related to the closure of the Statement and now it seem correct.

  public ResultSet EseguiLettura(String sSqlToExecute) throws SQLException
  {
      Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
      ResultSet rs = stmt.executeQuery(sSqlToExecute);
//      rs.close();
//      stmt.close();
      return rs;
  }
 
 

Thank you

Glad to have helped, keep in mind that it is comsidered good practice to close the rs/con when you are done.

please post again if you have any other troubles (or if you see something you can help with).