Test execution H2 SELECT query error Table not found and this database is empty

Question

I have subject error in my Spring-boot application when running SELECT sql as part of integration tests.

However Spring-boot application start-up has no issues. I can see all the tables in H2 console after the application start-up.

Further despite getting said error in integration test when run the same SQL query in the H2 console it gives results as expected.

Error:

    org.h2.jdbc.JdbcSQLSyntaxErrorException: Table "MY_TABLE1" not found (this database is empty); SQL statement:
    SELECT PHD.ID, PRS.ID, PHD.*, PDT.*, PRS.*, PAD.* FROM MY_TABLE1 PHD JOIN MY_TABLE2 PRS ON PHD.REFERENCE_ID = PRS.REFERENCE_ID JOIN MY_TABLE3 PDT ON PDT.HEADER_ID = PHD.ID JOIN MY_TABLE4 PAD ON PAD.MY_HEADER_ID = PHD.ID AND PHD.REFERENCE_ID IN ('ABC6317000802') ORDER BY PHD.CREATED_TS, PDT.CREATED_TS, PRS.CREATED_TS, PAD.CREATED_TS, PAD.ID ASC [42104-214]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:502)
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:477)
    at org.h2.message.DbException.get(DbException.java:223)
    at org.h2.message.DbException.get(DbException.java:199)
    at org.h2.command.Parser.getTableOrViewNotFoundDbException(Parser.java:8385)
    at org.h2.command.Parser.getTableOrViewNotFoundDbException(Parser.java:8369)
    at org.h2.command.Parser.readTableOrView(Parser.java:8358)
    at org.h2.command.Parser.readTablePrimary(Parser.java:1863)
    at org.h2.command.Parser.readTableReference(Parser.java:2334)
    at org.h2.command.Parser.parseSelectFromPart(Parser.java:2772)
    at org.h2.command.Parser.parseSelect(Parser.java:2878)
    at org.h2.command.Parser.parseQueryPrimary(Parser.java:2762)
    at org.h2.command.Parser.parseQueryTerm(Parser.java:2633)
    at org.h2.command.Parser.parseQueryExpressionBody(Parser.java:2612)
    at org.h2.command.Parser.parseQueryExpressionBodyAndEndOfQuery(Parser.java:2605)
    at org.h2.command.Parser.parseQueryExpression(Parser.java:2598)
    at org.h2.command.Parser.parseQuery(Parser.java:2567)
    at org.h2.command.Parser.parsePrepared(Parser.java:724)
    at org.h2.command.Parser.parse(Parser.java:689)
    at org.h2.command.Parser.parse(Parser.java:661)
    at org.h2.command.Parser.prepareCommand(Parser.java:569)
    at org.h2.engine.SessionLocal.prepareLocal(SessionLocal.java:631)
    at org.h2.engine.SessionLocal.prepareCommand(SessionLocal.java:554)
    at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1116)
    at org.h2.jdbc.JdbcStatement.executeQuery(JdbcStatement.java:92)

my application.yaml

spring:
  cache:
    enabled: false

  datasource:
    url: jdbc:h2:mem:myDB;DB_CLOSE_DELAY=-1;MODE=Oracle;IGNORECASE=TRUE 
    username: sa
    password: 123
    driverClassName: org.h2.Driver
 
  flyway:
    enabled: true
    username: sa
    password: 123
    url: jdbc:h2:mem:myDB;DB_CLOSE_DELAY=-1;MODE=Oracle
    baseline-on-migrate: true
    locations: filesystem:C:\Repo\MyAPII\api\src\integrationTest

  h2:
    console:
      enabled: true
      path: /h2-console

I use below code to connect to the H2 db for data assertion during test execution. Perhaps here it can't see tables in H2 tables?

Test H2 db connectiond and SELECT query code:

  Connection conn = null; 
  Statement stmt = null; 
  try { 
     Class.forName(JDBC_DRIVER);  
     conn = DriverManager.getConnection("h2:mem:myDB","sa","123");  
 
 stmt = conn.createStatement(); 
 String sql = "SELECT PHD.ID, PRS.ID, PHD.*, PDT.*, PRS.*, PAD.*...."; 
 ResultSet rs = stmt.executeQuery(sql);

 String metadataValue = "";
 String strMetadata = "Status";
 String intMetadata = 1;

 while (rs.next()) {
     metadataValue = strMetadata.isEmpty() || strMetadata.isBlank() ? rs.getString(intMetadata) : rs.getString(strMetadata);
}
 statement.close();
 conn.close();

Apparently this is kind of common issue. I tried almost all the solutions in below two threads

Question 1

Question 2

I would use exactly the same connection parameters for all your connections. Especially ignore case. — Mar-Z, Apr 09 '23 at 09:12
@Mar-Z I tried that as well. But no luck. How do I make sure in test execution, I am connecting to the same same DB where all the tables created and populated? Becuz as I specified in the quesion, I can see everyting there in the H2 console after application start-up — Shabar, Apr 09 '23 at 09:36
Your application is using an in-memory DB, meaning that everything is discarded after every run. Since you are running a standalone test, chances are that your test is not properly running flyway migrations to create the tables for you. You likely want to learn about `@DataJpaTest`, and maybe inject a `DataSouce`, or even better, the repositories that you want to test in your test suite. This way Spring will do all of the heavy lifiting for you. — Anthony Accioly, Apr 09 '23 at 09:46
@AnthonyAccioly I am running integration tests (API tests) here. Normally it is indepedent of the development code. Still you reckon I have the said option only? Similar to what described here, https://www.baeldung.com/spring-jpa-test-in-memory-database — Shabar, Apr 09 '23 at 10:03
BTW, my integration tests are using `SerenityBDD` with cucumber — Shabar, Apr 09 '23 at 10:10
@AnthonyAccioly Further just wanted to mention, I run my integration tests after start-up the application (i.e side by side) So since H2 database is still up and running while running the tests I can see data has been inserted via tests scenario. So I need to connect to same databse from IT test to verify inserted data. If that make sense. — Shabar, Apr 09 '23 at 10:28
Shabar. For the first question, yes, you have this option, not only this option, but it's a good strategy (and h2 is excellent for this exact use case). For the second question, this is not how it works. Your tests and main application will run on different processes, each with their own in-memory database. Your tests won't have access to the data in your main application in-memory database or vice-versa (this would be a very bad idea anyway). — Anthony Accioly, Apr 09 '23 at 10:40
@AnthonyAccioly I tried H2 file mode apparently same result. Can't we connect to same DB created during aplication start-up, since the file still there in the location? So that can see the newly inserted results for test validation. — Shabar, Apr 11 '23 at 09:28
The easiest way to accept multiple connections from different processes simultaneously would be to use `AUTO_SERVER=true` (see https://www.h2database.com/html/features.html#auto_mixed_mode). Having said that, as previously stated, I wouldn't recommend this strategy at all. To me all of this feels like an [XY problem](https://en.wikipedia.org/wiki/XY_problem), my recommendation would be to rethink the testing strategy so that you don't need a shared database to begin with. — Anthony Accioly, Apr 11 '23 at 14:25
@AnthonyAccioly I get your point. However, I tried that option and here is the details of the result. https://stackoverflow.com/questions/75984701/is-that-possible-to-connect-to-same-h2-file-database-during-test-execution — Shabar, Apr 11 '23 at 14:38

Test execution H2 SELECT query error Table not found and this database is empty

0 Answers0