Fun with URIs and Windows: jdbc:h2:C:/ vs. jdbc:h2:/C:/

[Andreas Reichel]

Dear All,

compliments. I have a hard time with URIs under Windows (simply because I do not know this OS very well.)

In our own Java code, all URIs are defined as:

groovy> URI uri = new URI("file:/C:/Users/manticore/.manticore/ifrsbox"); 

groovy> File f = new File(uri); 

Result: C:\Users\manticore\.manticore\ifrsbox <-- OK!

Based on that understanding, I would have specified the H2 connection string as:

jdbc:h2:file:/C:/Users/manticore/.manticore/ifrsbox

But this gives me

General error: "java.nio.file.InvalidPathException: Illegal char <:> at index 2: /C:/Users/manticore/.manticore/ifrsbox" [50000-201] HY000/50000 (Help)

Instead, the following seems to work as H2 connection string:

jdbc:h2:file:C:/Users/manticore/.manticore/ifrsbox

jdbc:h2:C:/Users/manticore/.manticore/ifrsbox (wow!)

but fails as URI:

groovy> URI uri = new URI("file:C:/Users/manticore/.manticore/ifrsbox"); 

groovy> File f = new File(uri); 

Exception thrown

java.lang.IllegalArgumentException: URI is not hierarchical

	at java.io.File.<init>(Unknown Source)

	at ConsoleScript11.run(ConsoleScript11:2)

Now I have two questions please:

1) is that a Bug or a Feature? (in my opinion, "jdbc:h2:file:/C:/Users/manticore/.manticore/ifrsbox" was more logical than "jdbc:h2:file:C:/Users/manticore/.manticore/ifrsbox" and especially "jdbc:h2:C:/Users/manticore/.manticore/ifrsbox")

2) if it was a feature, then what would be the best way to get the DB File Name from a Connection URL, given the three different Schemes like "jdbc:h2:C:/Users/manticore/.manticore/ifrsbox", "jdbc:h2:file:C:/Users/manticore/.manticore/ifrsbox" and "jdbc:h2:tcp://localhost/C:/Users/manticore/.manticore/ifrsbox"

Thank you already and best regards

Andreas

[Evgenij Ryazanov]

Hello.

JDBC connection URLs for H2 aren't related with URIs in any way.

jdbc:h2:<file name>, jdbc:h2:file:<file name>, and jdbc:h2:nio:<file name> mean the same in the current H2 and there are no good reasons to use file: or nio: prefixes. In older versions they were different (and they also were different between PageStore and MVStore engines).

There are other subsystems, such as niomapped:, async:, retry:, split:, encrypt:, zip: and so on and they can also be cascaded.

Not every URL for H2 contains a file name. mem: represents a normal in-memory database, memFS:, memLZF:, nioMemFS:, and nioMemLZF: represent alternative in-memory storages and H2 works with them in entirely different way (more like with with files).

tcp: and ssl: are used for remote connections. TCP server may have some configured base directory where database files can be located, but it also can use the whole file system of the server, it depends on its configuration.

Database files also have .mv.db or h2.db extensions, these extensions aren't specified in the URL. If MV_STORE setting is not specified, there is no way to get this extension from the URL, it depends on the actual file on the disk.

There is no reliable way to get the file name from the JDBC URL, but if you have a connection, you can try to use the DATABASE_PATH() function.

In the current H2 you can use

CALL DATABASE_PATH() || CASE (SELECT CAST(SETTING_VALUE AS BOOLEAN) FROM INFORMATION_SCHEMA.SETTINGS WHERE SETTING_NAME = 'MV_STORE') WHEN TRUE THEN '.mv.db' ELSE '.h2.db' END;

to get the file name if database uses the the default file system without any subsystems.

For 1.4.200 this command needs to be edited, because INFORMATION_SCHEMA.SETTINGS had different names of columns (NAME and VALUE).

If NULL is returned, the database is a normal in-memory database (without special in-memory file systems). To support more special cases, you need to check the result for async: and other prefixes (possibly cascaded).

You can also try to parse the URL itself, but you need to support more special cases and you need to know settings of all your servers, if you want to support the remote URLs properly. Perhaps you don't need the full paths on remote servers anyway, so you can only detect tcp: and ssl: prefixes.

[Andreas Reichel]

Thank you for explaining it, Evgenji.

I understand you argument, after all Oracle or SQL Server would not point to a File either and in fact it is on the discretion of the DB, how to deal with the connection string.

That said:  jdbc:h2:file:<file name> looks a lot like a Url/Uri.

And I would always try  jdbc:h2:file:/C:/foo first, followed by a frustrating trial'n error until I figure out jdbc:h2:file:C:/foo (which simply looks wrong to me!)

So I wonder: should we not try to accept valid Uris at least for the  jdbc:h2:file: and  jdbc:h2:nio: variants?

Would you consider PRs covering that?

Best regards

Andreas

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email to h2-database...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/cf1aeb4a-fd88-46e3-aea4-76b4096881b4n%40googlegroups.com.

[Noel Grandin]

On Tue, 24 Nov 2020 at 18:37, Andreas Reichel <and...@manticore-projects.com> wrote:

So I wonder: should we not try to accept valid Uris at least for the  jdbc:h2:file: and  jdbc:h2:nio: variants?

Would you consider PRs covering that?

Sorry, but noo, a JDBC connection string is not a valid URI, and does not follow that syntax. 

We also don't need to make it's existing syntax any more complicated.

[Andreas Reichel]

On Tue, 2020-11-24 at 19:17 +0200, Noel Grandin wrote:

Sorry, but noo, a JDBC connection string is not a valid URI, and does not follow that syntax. 

We also don't need to make it's existing syntax any more complicated.

Thank you for the clarification, Noel. I understand your argument and there is no merit in wasting effort.

Maybe just point out an example in the documentation:

On Windows, it must be jdbc:h2:file:C:/foo but not jdbc:h2:file:/C:/foo .

We can close this thread.

All the best

Andreas