Need help with IKVM 8.7.5 + H2 2.2.220 + ojdbc8.jar + VS2019 + net472

[Coff ee]

Need help with IKVM 8.7.5 + H2 2.2.220 + ojdbc8.jar + VS2019 + net472

I was working on IKVM 7.2.4630.5 + H2 1.4.199.jar + ojdbc6.jar + vs2019 + net472

Everything is fine with these steps:
1. use ikvmc, generated H2.1.4.199.dll by merging ojdbc6.jar into a single dll file.
2. in my project, refer to H2.1.4.199.dll and copy ikvm runtime files into bin folder
3. at app startup, load register oracle driver
java.sql.DriverManager.registerDriver(OJDBC_OracleDatabaseDriver);
4. load h2 driver
5. I could create a database, connect to it using readonly / readwrite mode
6. I can create linked table to another h2 database or oracle 19c, load any table into my h2 database.
7. the production database run in readonly mode. (yes, for data checking only)
8. the project works on H2 data type "int" and "varchar(100)" only, however must be embedded mode.

I'm trying to upgrade my project to
IKVM 8.7.5 + H2 2.2.220 + ojdbc8.jar + vs2019 + net472
(where IKVM 8.7.5 (JDK8u92-B34) supports H2 up to H2 2.2.220)
and wired thing is found.

Problem 1: with IKVM 8.7.5, I can generate the merged dll again, but I'm not sure the IkvmReference setting is correct. Please give any comment and advice.

Detail:
    <ItemGroup>
        <IkvmReference Include="jar\ojdbc8.jar">
            <AssemblyName>o8</AssemblyName>
            <AssemblyVersion>1.0.0.8</AssemblyVersion>
        </IkvmReference>        
       
        <IkvmReference Include="jar\h2-2.2.220.jar">
            <AssemblyName>h2.2.2.220.8</AssemblyName>
            <AssemblyVersion>2.2.220.8</AssemblyVersion>
            <Compile>jar\ojdbc8.jar</Compile>
            <Reference>jar\ojdbc8.jar</Reference>
            <ClassLoader>ikvm.runtime.AppDomainAssemblyClassLoader</ClassLoader>
        </IkvmReference>
    </ItemGroup>

With this setting, I got a single h2.2.2.220.8.dll by merging ojdbc8 into it.
It seems  <Compile> is enough, adding <Reference> and <ClassLoader> did not affect the output dll file size.
The output o8.dll is not needed.

=========================================

using the dll file, I could create a DB using connection string
jdbc:h2:file:C:\WIP\YDB01A;USER=aaaa;PASSWORD=aaaa;ACCESS_MODE_DATA=rw;CASE_INSENSITIVE_IDENTIFIERS=TRUE
create a table DDD, insert some records, then close the database.

If I connect the database again using readonly mode, I can query the table DDD
connection string :jdbc:h2:file:C:\WIP\YDB01A;USER=aaa;PASSWORD=aaaa;ACCESS_MODE_DATA=r;IFEXISTS=TRUE;CASE_INSENSITIVE_IDENTIFIERS=TRUE

"DB_CLOSE_ON_EXIT=TRUE" is also tested.

Problem 2. I'm not sure the step is correct. steps:
a. generate the merged dll file.
b. in bin folder, include ikvm folder, include runtime folder.
c. load oracle driver
d. load h2 driver.
e. anything else to do?
for example java classpath?
load any .dll using Assembly[] assemblies = AppDomain.get_CurrentDomain().GetAssemblies();
or AppDomainAssemblyClassLoader? at startup?

Problem 3. If connecting the database using readwrite mode, the table DDD is dropped.
error: table DDD is not found. and it never go back even using read only mode.

=========================================

I can create linked table to another h2 database if everything set in readonly mode.
Problem 4. I can not create linked table in oracle 19c, the error is java.lang.IndexOutOfBoundsException

General error: java.lang.IndexOutOfBoundsException (Error Type: org.h2.jdbc.JdbcSQLNonTransientException)
at org.h2.util.JdbcUtils.getconnection(String str1, String str2, String str3, String str4, NetworkConnectionInfo nci, Boolean b)
at org.h2.util.JdbcUtils.getconnection(String str1, String str2, String str3, String str4)
at org.h2.table.TableLinkConnection.open()

detail:
CREATE LOCAL TEMPORARY LINKED TABLE IF NOT EXISTS TABLEB (
'',  
'jdbc:oracle:thin:@(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 1.2.3.4)(PORT = 1630)) (ADDRESS = (PROTOCOL = TCP)(HOST = 4.3.2.1)(PORT = 1531))) (SOURCE_ROUTE = yes) (CONNECT_DATA = (SERVICE_NAME = O19CDB)))',  
'userid',  
'password',  
'schema.TABLEA'  
) READONLY;

the above command works for IKVM 7.2.4630.5 + H2 1.4.199.jar

=========================================

so I want to go back to H2 1.4.199
tested IKVM 8.7.5 + H2 1.4.199 + ojdbc8.jar + vs2019 + net472
this time, the problem is totally different.

Problem 5. Can not created linked table to another h2 database of same version.

2024-02-08 03:35:32 jdbc[3]: exception
org.h2.jdbc.JdbcSQLNonTransientConnectionException: Database may be already in use: null. Possible solutions: close all other connection(s); use the server mode; SQL statement:
 CREATE LOCAL  TEMPORARY  LINKED TABLE IF NOT EXISTS EEE (    'org.h2.Driver',    'jdbc:h2:file:C:/WIP/B1B199;USER=aaa;PASSWORD=aaaa;ACCESS_MODE_DATA=r;IFEXISTS=TRUE;CASE_INSENSITIVE_IDENTIFIERS=TRUE;DB_CLOSE_ON_EXIT=TRUE',    'aaa',    'aaaa',    'DDD'  )  [90020-199]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:617)
at org.h2.message.DbException.getJdbcSQLException(DbException.java:427)
at org.h2.mvstore.db.MVTableEngine$Store.convertIllegalStateException(MVTableEngine.java:193)
at org.h2.mvstore.db.MVTableEngine$Store.open(MVTableEngine.java:173)
at org.h2.mvstore.db.MVTableEngine.init(MVTableEngine.java:96)
at org.h2.engine.Database.getPageStore(Database.java:2739)
at org.h2.engine.Database.open(Database.java:771)
at org.h2.engine.Database.openDatabase(Database.java:320)
at org.h2.engine.Database.<init>(Database.java:314)
at org.h2.engine.Engine.openSession(Engine.java:69)
at org.h2.engine.Engine.openSession(Engine.java:201)
at org.h2.engine.Engine.createSessionAndValidate(Engine.java:178)
at org.h2.engine.Engine.createSession(Engine.java:161)
at org.h2.engine.Engine.createSession(Engine.java:31)
at org.h2.engine.SessionRemote.connectEmbeddedOrServer(SessionRemote.java:336)
at org.h2.jdbc.JdbcConnection.<init>(JdbcConnection.java:169)
at org.h2.jdbc.JdbcConnection.<init>(JdbcConnection.java:149)
at org.h2.Driver.connect(Driver.java:69)
at org.h2.util.JdbcUtils.getConnection(JdbcUtils.java:300)
at org.h2.util.JdbcUtils.getConnection(JdbcUtils.java:274)
at org.h2.table.TableLinkConnection.open(TableLinkConnection.java:89)
at org.h2.table.TableLinkConnection.open(TableLinkConnection.java:79)
at org.h2.engine.Database.getLinkConnection(Database.java:2708)
at org.h2.table.TableLink.connect(TableLink.java:96)
at org.h2.table.TableLink.<init>(TableLink.java:79)
at org.h2.schema.Schema.createTableLink(Schema.java:745)
at org.h2.command.ddl.CreateLinkedTable.update(CreateLinkedTable.java:77)
at org.h2.command.CommandContainer.update(CommandContainer.java:133)
at org.h2.command.Command.executeUpdate(Command.java:267)
at org.h2.jdbc.JdbcStatement.executeUpdateInternal(JdbcStatement.java:169)
at org.h2.jdbc.JdbcStatement.executeUpdate(JdbcStatement.java:126)


It seems I miss some steps, for example set something with java classpath, load any class at the startup phase
these steps is not required in IKVM 7.2.4630.5 + H2 1.4.199.jar + ojdbc6.jar + vs2019 + net472

please give any advice. I got stuck for 2 weeks. T_T

[Coff ee]

Update: 

IKVM 8.7.5 + H2 2.2.220 + ojdbc8.jar + VS2019 + net472 still not work.

I have new founding for IKVM 8.7.5 + H2 1.4.199 + ojdbc8.jar + vs2019 + net472

as H2 1.4.199 still support MV_STORE=FALSE, so I added MV_STORE=FALSE to connection string

Now i can connect to DB1.h2.db using read/write mode and table DDD can be found.

Now I can create linked table to DB2.h2.db table and get table from DB2

Now I can create linked table to Oracle19c table and query them.

When doing CREATE TABLE ABC AS SELECT * FROM Oracle19c.Table, error occurred.

General error: "java.lang.IllegalStateException: The file is locked: nio:C:\Users/..../AppDate/LocalTemp/h2tmp.1903201735.temp.db [1.4.199/7]" [50000-199]

Coff ee 在 2024年2月8日 星期四上午10:27:49 [UTC+8] 的信中寫道：