[h2] Problem handling a lot of parameters - query does not come to an end

[Markus Fengler]

Hi,

I hava a query with a lot of Parameters. If I use it with inlined Paramters 
it works. But if I use a prepared Statement the execute does not come to an 
end and the cpu goes to 100%.

Note: for the sql-generation I use the jooq library. I also posted it as 
issue (https://github.com/jOOQ/jOOQ/issues/2768) but it is h2database 
specific.

Here is an extract of the example. You can download the full example code 
at http://jmlp.volans.uberspace.de/ftp/issueExample.zip as maven project:

  @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 h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.