@Test
public void testResultSet() throws Exception {

    // setup the HSQL database with our rules.
    Class.forName("org.hsqldb.jdbcDriver");
    Connection conn = DriverManager.getConnection("jdbc:hsqldb:mem:drools-templates", "sa", "");
    try {
        update("CREATE TABLE cheese_rules ( id INTEGER IDENTITY, persons_age INTEGER, birth_date DATE, cheese_type VARCHAR(256), log VARCHAR(256) )", conn);
        update("INSERT INTO cheese_rules(persons_age,birth_date,cheese_type,log) VALUES(42, '1950-01-01', 'stilton', 'Old man stilton')", conn);
        update("INSERT INTO cheese_rules(persons_age,birth_date,cheese_type,log) VALUES(10, '2009-01-01', 'cheddar', 'Young man cheddar')", conn);
    } catch (SQLException e) {
        throw new IllegalStateException("Could not initialize in memory database", e);
    }
    // query the DB for the rule rows, convert them using the template.
    Statement sta = conn.createStatement();
    ResultSet rs = sta.executeQuery("SELECT persons_age, cheese_type, log " +
                                    " FROM cheese_rules");
    final ResultSetGenerator converter = new ResultSetGenerator();
    final String drl = converter.compile(rs, getRulesStream());
    System.out.println(drl);
    sta.close();
    KnowledgeBuilder kbuilder = KnowledgeBuilderFactory.newKnowledgeBuilder();
    kbuilder.add(ResourceFactory.newByteArrayResource(drl.getBytes()), ResourceType.DRL);
    assertFalse(kbuilder.hasErrors());
    InternalKnowledgeBase kbase = KnowledgeBaseFactory.newKnowledgeBase();
    kbase.addPackages(kbuilder.getKnowledgePackages());
    KieSession kSession = kbase.newKieSession();
    //now create some test data
    kSession.insert(new Cheese("stilton", 42));
    kSession.insert(new Person("michael", "stilton", 42));
    List<String> list = new ArrayList<String>();
    kSession.setGlobal("list", list);
    kSession.fireAllRules();
    assertEquals(1, list.size());
}