How can I connect to an h2 database via odbc / postgres driver where one table contains blob fields?

Question

I'm trying to access an h2 database via odbc. The database itself is running with h2 in server mode. Later on, I try to create an odbc datasource with the postgres unicode sql driver (psqlodbc). If I test my connection with a valid username an password, the h2 console reports an exception and the server process is killed.

Is there another way or parameter to allow the connection via odbc? I tried to append ;MODE=PostgreSQL to the database name but it didn't help.

The following exception is shown (field names and ip addresses removed):

CP server running at tcp://...:9092 (only local connections)
PG server running at pg://...:5435 (only local connections)
Web Console server running at http://...:8082 (only local connections)
org.h2.jdbc.JdbcSQLNonTransientException: Unbekannter Datentyp: "BLOB"
Unknown data type: "BLOB"; SQL statement:
CREATE CACHED TABLE "..."."...."(
    "ID" VARCHAR(40) NOT NULL, ..
    "SOME_DATA" BLOB, ...
) [50004-200]
        at org.h2.message.DbException.getJdbcSQLException(DbException.java:505)
        at org.h2.message.DbException.getJdbcSQLException(DbException.java:429)
        at org.h2.message.DbException.get(DbException.java:205)
        at org.h2.message.DbException.get(DbException.java:181)
        at org.h2.command.Parser.parseColumnWithType(Parser.java:5971)
        at org.h2.command.Parser.parseColumnForTable(Parser.java:5697)
        at org.h2.command.Parser.parseTableColumnDefinition(Parser.java:8442)
        at org.h2.command.Parser.parseCreateTable(Parser.java:8379)
        at org.h2.command.Parser.parseCreate(Parser.java:6276)
        at org.h2.command.Parser.parsePrepared(Parser.java:903)
        at org.h2.command.Parser.parse(Parser.java:843)
        at org.h2.command.Parser.parse(Parser.java:815)
        at org.h2.command.Parser.prepare(Parser.java:722)
        at org.h2.engine.Session.prepare(Session.java:622)
        at org.h2.engine.Session.prepare(Session.java:606)
        at org.h2.engine.MetaRecord.execute(MetaRecord.java:58)
        at org.h2.engine.Database.open(Database.java:759)
        at org.h2.engine.Database.openDatabase(Database.java:307)
        at org.h2.engine.Database.<init>(Database.java:301)
        at org.h2.engine.Engine.openSession(Engine.java:74)
        at org.h2.engine.Engine.openSession(Engine.java:192)
        at org.h2.engine.Engine.createSessionAndValidate(Engine.java:171)
        at org.h2.engine.Engine.createSession(Engine.java:166)
        at org.h2.engine.Engine.createSession(Engine.java:29)
        at org.h2.engine.SessionRemote.connectEmbeddedOrServer(SessionRemote.java:340)
        at org.h2.jdbc.JdbcConnection.<init>(JdbcConnection.java:173)
        at org.h2.server.pg.PgServerThread.process(PgServerThread.java:235)
        at org.h2.server.pg.PgServerThread.run(PgServerThread.java:101)
        at java.lang.Thread.run(Unknown Source)

I'm experiencing the same problem, have you found anything on this? — Lorelorelore
– Lorelorelore, Commented Aug 23, 2021 at 9:19
Unfortunately not. Now I'm using the jdbc driver, export the data to csv (built-in function of h2 database) and import the data into my working db afterwards... Not nice, but it works for my usage scenario. — ventiseis
– ventiseis, Commented Aug 24, 2021 at 6:20
Ok, thank you, just for information, which version of h2 are you using? — Lorelorelore
– Lorelorelore, Commented Aug 24, 2021 at 6:29
h2-1.4.200.jar and sqlline-1.11.0-jar-with-dependencies.jar. I did neither file a bug, nor did I contact the devs... — ventiseis
– ventiseis, Commented Aug 24, 2021 at 13:11

eku-code · Accepted Answer · 2023-01-06 10:56:01Z

I experienced the same problem. I had a test application properties with wrong dialect:

spring:
   jpa:
    database-platform: org.hibernate.dialect.H2Dialect
    open-in-view: false
    hibernate:
      ddl-auto: create-drop
      naming:
        physical-strategy: org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl
        implicit-strategy: org.hibernate.boot.model.naming.ImplicitNamingStrategyLegacyJpaImpl
    properties:
      hibernate:
        dialect: org.hibernate.dialect.H2Dialect
        default_schema: schema

I changed in this way. Everything is working fine.

spring:
   jpa:
    database-platform: org.hibernate.dialect.PostgreSQL10Dialect
    open-in-view: false
    hibernate:
      ddl-auto: create-drop
      naming:
        physical-strategy: org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl
        implicit-strategy: org.hibernate.boot.model.naming.ImplicitNamingStrategyLegacyJpaImpl
    properties:
      hibernate:
        dialect: org.hibernate.dialect.PostgreSQL10Dialect
        default_schema: schema

To check database version I used:

select version();

Collectives™ on Stack Overflow

How can I connect to an h2 database via odbc / postgres driver where one table contains blob fields?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related