Hi, I want to get a ResultSet and the query contains a lot of Parameters. If I use an inline Query then it works but if I use a PreparedStatement with indexed Parameters there seems to be a problem. Here is an example for this strange behaviour.
You can download the whole Example as maven project at http://jmlp.volans.uberspace.de/ftp/issueExample.zip Note: For generating the sql I use jooq library. I also posted the issue at their repository but it seems that it is h2 specific. ( https://github.com/jOOQ/jOOQ/issues/2768) @Test public void testBugExample() throws SQLException { /* Preparation of Data */ DSLContext dsl = DSL.using(this.con, SQLDialect.H2); List<Long> activeFolders = dsl.select(FILES.ID).from(FILES).where(FILES.TYP.eq("D"), FILES.AVAILABLE.isTrue()).fetch(FILES.ID); List<Long> activeFiles = dsl.select(FILES.ID).from(FILES).where(FILES.TYP.eq("F"), FILES.AVAILABLE.isTrue()).fetch(FILES.ID); /* the Query with the Bug */ SelectConditionStep<Record1<Long>> qry = dsl.select(FILES.ID).from(FILES).join(FOLDERMAP).on(FILES.ID.eq(FOLDERMAP.ID)) .where(FILES.ID.notIn(activeFiles), FOLDERMAP.PARENT.in(activeFolders)); String qryIndexed = qry.getSQL(ParamType.INDEXED); String qryInline = qry.getSQL(ParamType.INLINED); /* InlineParameter work */ log.info("Query Inline: " + qryInline); { Statement stm = con.createStatement(); ResultSet rs = stm.executeQuery(qryInline); while (rs.next()) rs.getLong(1); rs.close(); stm.close(); } /* InlineParameter work */ log.info("Query Indexed: " + qryIndexed); { PreparedStatement stm = con.prepareStatement(qryIndexed); /* set Parameter */ int pos = 1; for (Long e : activeFiles) stm.setLong(pos++, e); for (Long e : activeFolders) stm.setLong(pos++, e); ResultSet rs = stm.executeQuery(); while (rs.next()) rs.getLong(1); rs.close(); stm.close(); } /* does not finish to this line */ log.info("finish"); } -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/groups/opt_out.
