如何通过odbc/postgres驱动程序连接到h2数据库，其中一个表包含blob字段

Question

我正试图通过odbc访问h2数据库。数据库本身在服务器模式下使用h2运行。稍后，我尝试使用postgres unicode sql驱动程序(psqlodbc(创建一个odbc数据源。如果我使用有效的用户名和密码测试我的连接，h2控制台会报告一个异常，服务器进程就会终止。

是否有其他方式或参数允许通过odbc进行连接？我试图将;MODE=PostgreSQL附加到数据库名称，但没有帮助。

显示以下异常(字段名和ip地址已删除(：

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)

Answer 1

我遇到了同样的问题。我用错误的方言测试了应用程序属性：

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

我就这样改变了。一切都很好。

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

检查我使用的数据库版本：

select version();