Running Jasper reports against in-memory h2 data source?

Question

I am trying to run jasper reports against a live and reporting database, but any reports running against live databases throw an exception due to the lack of proper tables (although the default PUBLIC scheme is found). It seems that the main DataSource connection does not match the settings for the H2 connection, which indicate IGNORECASE=true , since the generated columns and tables are header, are not my queries.

DataSource.groovy dataSource:

 dataSource { hibernate { cache.use_second_level_cache = false cache.use_query_cache = false } dbCreate = "create-drop" // one of 'create', 'create-drop','update' pooled = true driverClassName = "org.h2.Driver" username = "sa" password = "" url = "jdbc:h2:mem:testDb;MODE=PostgreSQL;IGNORECASE=TRUE;DATABASE_TO_UPPER=false" jndiName = null dialect = null } 

Datasources.groovy dataSource:

 datasource(name: 'reporting') { environments(['development', 'test']) domainClasses([SomeClass]) readOnly(false) driverClassName('org.h2.Driver') url('jdbc:h2:mem:testReportingDb;MODE=PostgreSQL;IGNORECASE=TRUE;DATABASE_TO_UPPER=false') username('sa') password('') dbCreate('create-drop') logSql(false) dialect(null) pooled(true) hibernate { cache { use_second_level_cache(false) use_query_cache(false) } } } 

What fails:

 JasperPrint print = JasperFillManager.fillReport(compiledReport, params,dataSource.getConnection()) 

During debugging, the only difference I found is that the live dataSource when entering or searching using DatasourcesUtils.getDataSource(null) is TransactionAwareDatasourceProxy , and DatasourcesUtils.getDataSource('reporting') is BasicDataSource

What do I need to do for Jasper to work with an active database in H2 memory?

This error does not reproduce in the real postgres database.

Answer 1

Just don't run reports on data sources in memory, and that won't be a problem.

Answer 2

Perhaps you are opening another database. Using the jdbc:h2:mem:testDb database jdbc:h2:mem:testDb will open the database in memory as part of a single process and class loader .

Have you tried using a persistent persistent database already using the jdbc:h2:~/testDb database jdbc:h2:~/testDb ?

To use an open in-memory database that runs in another process or class loading, you need to use server mode. This means that you need to start the server where the database is running and connect to it using jdbc:h2:tcp://localhost/mem:testDb .

See also an overview of the database URL .

Answer 3

H2 does not currently support case insensitive identifiers (table names, column names). I know that other databases support it, but currently H2 uses the usual java.util.HashMap<String, ..> for metadata, and this case is case sensitive (whether or not IGNORECASE is IGNORECASE ).

In this case, the identifier names are case sensitive. I tried using the jdbc:h2:mem:testReportingDb;MODE=PostgreSQL;IGNORECASE=TRUE;DATABASE_TO_UPPER=false using the H2 console:

 DROP TABLE IF EXISTS UPPER; DROP TABLE IF EXISTS lower; CREATE TABLE UPPER(NAME VARCHAR(255)); CREATE TABLE lower(name VARCHAR(255)); -- ok: SELECT * FROM UPPER; SELECT * FROM lower; -- fail (table not found): SELECT * FROM upper; SELECT * FROM LOWER; 

So the question is: when creating tables, were they created using uppercase identifiers or another database URL? Can this be changed? If not: is it possible to use a different database URL?