Discussions

General J2EE: ResultSet number of rows

  1. ResultSet number of rows (6 messages)

    Hi, I want to know the size of a resultSet where I stored a db consult. I'm using the ms sql driver 2000, I try to do this:

    ...

    rs = cs.executeQuery();
    rs.last();
    size = rs.getRow();
    rs.beforeFirst();

    while (rs.next()) {

    //iteration on the values

    }

    That method is on a ejb, when I run this I received this error:

    ******************************************************
    Excepcion en ApasSocioBean: java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]Unsupported method: ResultSet.last
    at model.ApasSocioBean.ejbHomeMethodBuscarSocio(ApasSocioBean.java:211)
    ******************************************************

    I try also with a while

    int size = 0;
    while(rs.next)
    {
       size++;
    }

    but on the end of the while the rs cursor is on the last row, so I can't iterate, and I can't do rs.beforeFirst(), beacause of this:

    ******************************************************
    Excepcion en ApasSocioBean: java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]Unsupported method: ResultSet.beforefirst
    at model.ApasSocioBean.ejbHomeMethodBuscarSocio(ApasSocioBean.java:211)
    ******************************************************

    Someone know how to solve this problem, thanks!

    Regards Daniel

    Threaded Messages (6)

  2. ResultSet number of rows[ Go to top ]

    How are you creating your PreparedStatement?
    PreparedStatement statement = conn.prepareStatement( query,
           ResultSet.TYPE_SCROLL_INSENSITIVE,
           ResultSet.CONCUR_READ_ONLY );
  3. ResultSet number of rows[ Go to top ]

    You must do both (counting and getting the rows) in one pass.

    Kind regards,
    Stefan
  4. ResultSet number of rows[ Go to top ]

    You must do both (counting and getting the rows) in one pass.
    You should do this only when your JDBC driver doesn't support scrollable
    result sets, of course ;)

    Regards,
    Stefan
  5. ResultSet number of rows[ Go to top ]

    Hi thanks for your answers, I not use PreparedStatement because I call a store procedure:
    cs = connection.prepareCall("{call prueba(?,?)}");
    cs.setInt(1, tipIdentificacion.intValue());
    cs.setLong(2, numIdentificacion.longValue());
    rs = cs.executeQuery();

    I can´t do it in one pass, because I need to know the number of rows for create an array of transfer object to store the query:

    SocioTO[] socioTO;
    socioTO = new SocioTO[SIZE];//I need this value
    for (int j = 0; j<SIZE; j++)
    {
       socioTO[j] = new SocioTO();
    }

    int i = 0;
    while (rs.next()) {
            socioTO[i].setTipIdentificacion(new Integer(rs.getInt(1)));
            socioTO[i].setNumIdentificacion(new Long(rs.getLong(2)));
            socioTO[i].setNomApellido1(rs.getString(3));
            socioTO[i].setNomApellido2(rs.getString(4));
            socioTO[i].setNomNombre(rs.getString(5));
            socioTO[i].setCodAsociacionAporte(new Integer(rs.getInt(6)));
            socioTO[i].setCodCambioIdentificacion(new Boolean(rs.getBoolean(7)));
            i++;
    }

    What I can do?

    Regards Daniel
  6. ResultSet number of rows[ Go to top ]

    I not use PreparedStatement because I call a store procedure: cs = connection.prepareCall("{call prueba(?,?)}")
    Hi Daniel,

    first of all: you can also specify the "resultSetType" and "resultSetConcurrency" in prepareCall(). Have a look at the API Javadoc.
    If that doesn't work with your JDBC driver, you have to do it in one pass (I once had that situation with MS SQL Server, but I didn't use the MS JDBC driver then).
    I can´t do it in one pass, because I need to know the number of rows for create an array of transfer object to store the query:
    Create an ArrayList "list" and put your SocioTO objects in that list as you iterate over the result set. When you are done, simply call list.toArray().

    Kind Regards,
    Stefan
  7. ResultSet number of rows[ Go to top ]

    Thanks a lot I solved it like you say.

    Regards Daniel

    PD: thanks