Re: Two statement in while

[Eduardo S.E. de Castro]

Hello Bryan,

   The code in use is

    private Map<Integer, List<OntologyDefinedValueString>>
getSetConstituents(String tblConst, String tblSet) throws
SQLException{
        Map<Integer, List<OntologyDefinedValueString>> mapResult = new
HashMap<Integer, List<OntologyDefinedValueString>>();

        String strQuery = "SELECT PETRO_ID FROM CONSTITUENT_SET US
ORDER BY ORDERING";

        PreparedStatement pstmt =
DataSourceManager.getPetroKnowDS().getConnection().prepareStatement(strQuery);
        ResultSet rs = pstmt.executeQuery();

        while (rs.next()){
            Integer key = rs.getInt("PETRO_ID");

            String strQuery2 = "SELECT PETRO_ID, " + KEY_VALUE_ENUS +
", " + KEY_VALUE_PTBR + ", ORDERING FROM CONSTITUENT_NAME " +
                    " US WHERE ID_CONSTITUENT_SET = ? ORDER BY ORDERING";

            PreparedStatement pstmt2 =
DataSourceManager.getPetroKnowDS().getConnection().prepareStatement(strQuery2);
            pstmt2.setInt(1, key);
            ResultSet rs2 = pstmt2.executeQuery();

            List<OntologyDefinedValueString> lstConstituents = new
ArrayList<OntologyDefinedValueString>();

            while (rs2.next()){
                OntologyDefinedValueString otValue =
createOntologyDefinedValue(rs2);
                lstConstituents.add(otValue);
            }

            mapResult.put(key, lstConstituents);

            rs2.close();
            pstmt2.close();
        }
        rs.close();
        pstmt.close();

        return mapResult;
    }

   I tried with derby 10.3.2.1, 10.2.2.0, 10.1.3.1

Thanks,
Eduardo

2007/12/17, Bryan Pendleton <[EMAIL PROTECTED]>:
> > Just a stab in the dark, but I think you can only have one resultset
> > open in a connection at a time.
>
> No, that's not true. Some databases have that limitation, but not Derby.
>
> I routinely have multiple result sets open on the same connection
> in my Derby applications.
>
> >       I have a question about using two PreparedStatements in the same
> >     connection. When I use the following line, I get an exception
> >     java.sql.SQLException: ResultSet not open. Operation 'next' not
> >     permitted. Verify that autocommit is OFF.
>
> Can you post a complete working example of the code that is causing
> you problems? I didn't see anything wrong with your skeleton pseudo-code,
> so it must be something wrong in the actual code.
>
> thanks,
>
> bryan
>
>
>