empty resultset

**r035198x** · Mar 28 '07, 11:15 AM

Originally posted by 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.NullP ointerException

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** · Mar 28 '07, 11:25 AM

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

**r035198x** · Mar 28 '07, 12:11 PM

Originally posted by MauBady

Thanks
but if i try to iterate i obtain the java.lang.NullP ointerException . 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** · Mar 28 '07, 12:25 PM

this is the code:

ResultSet rs = dbConn.EseguiLe ttura(strSql);
dbConn.closeCon n();
while (rs.next())
{
System.out.prin tln("not empty");
}

and the method call is

public ResultSet EseguiLettura(S tring sSqlToExecute) throws SQLException
{
Statement stmt = conn.createStat ement(ResultSet .TYPE_SCROLL_IN SENSITIVE, ResultSet.CONCU R_UPDATABLE);
ResultSet rs = stmt.executeQue ry(sSqlToExecut e);
rs.close();
stmt.close();
return rs;
}

**r035198x** · Mar 28 '07, 12:36 PM

Originally posted by MauBady

this is the code:

ResultSet rs = dbConn.EseguiLe ttura(strSql);
dbConn.closeCon n();
while (rs.next())
{
System.out.prin tln("not empty");
}

and the method call is

public ResultSet EseguiLettura(S tring sSqlToExecute) throws SQLException
{
Statement stmt = conn.createStat ement(ResultSet .TYPE_SCROLL_IN SENSITIVE, ResultSet.CONCU R_UPDATABLE);
ResultSet rs = stmt.executeQue ry(sSqlToExecut e);
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** · Mar 28 '07, 01:07 PM

Thanks

This is the code

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** · Mar 28 '07, 01:21 PM

Originally posted by MauBady

Thanks

This is the code

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** · Mar 28 '07, 01:30 PM

this is the error

Exception in thread "main" java.lang.NullP ointerException
at oracle.jdbc.dri ver.UpdatableRe sultSet.next(Up datableResultSe t.java:251)
at EseguiProcesso. main(EseguiProc esso.java:72)

and this is the line:

Code:

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

**sicarie** · Mar 28 '07, 02:26 PM

Originally posted by MauBady

dbConn.closeCon n();
while (rs.next())
{
System.out.prin tln("not empty");
}

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

**r035198x** · Mar 28 '07, 02:39 PM

Originally posted by sicarie

I think your problem lies in that ResultSet requires a persistent connection - you close the connection (dbConn.closeCo nn(); ) 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** · Mar 28 '07, 02:58 PM

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.

Code:

  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** · Mar 28 '07, 03:04 PM

Originally posted by 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.

Code:

  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).

empty resultset

empty resultset

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment