Support executing CREATE DATABASE statements [JDBC598]

[firebird-automations]

Submitted by: @mrotteveel

In all existing versions of Jaybird it is not possible to execute CREATE DATABASE using a JDBC statement. This will yield a "java.sql.SQLSyntaxErrorException: Dynamic SQL Error; SQL error code = -530; Cannot prepare a CREATE DATABASE/SCHEMA statement [SQLState:42000, ISC error code:335544597]". However dropping to the low-level API to try and use executeImmediate will simply do nothing. And connecting but not attach allows to create a database using a native connection, but not using a pure-java connection.

We need to investigate whether it is possible to execute CREATE DATABASE when attached to a database, and if so: how.

See also #27

[firebird-automations]

Modified by: @mrotteveel

description: In all existing versions of Jaybird it is not possible to execute CREATE DATABASE using a JDBC statement. This will yield a "java.sql.SQLSyntaxErrorException: Dynamic SQL Error; SQL error code = -530; Cannot prepare a CREATE DATABASE/SCHEMA statement [SQLState:42000, ISC error code:335544597]". However dropping to the low-level API to try and use executeImmediate will simply do nothing. And connecting but not attach allows to create a database using a native connection, but not using a pure-java connection.

We need to investigate whether it is possible to execute CREATE DATABASE when attached to a database, and if so: how.

=>

In all existing versions of Jaybird it is not possible to execute CREATE DATABASE using a JDBC statement. This will yield a "java.sql.SQLSyntaxErrorException: Dynamic SQL Error; SQL error code = -530; Cannot prepare a CREATE DATABASE/SCHEMA statement [SQLState:42000, ISC error code:335544597]". However dropping to the low-level API to try and use executeImmediate will simply do nothing. And connecting but not attach allows to create a database using a native connection, but not using a pure-java connection.

We need to investigate whether it is possible to execute CREATE DATABASE when attached to a database, and if so: how.

See also #27

[mrotteveel]

The appropriate approach would seem to be to parse the create statement client side, populate the appropriate DPB items (maybe inheriting some from the current connection) and then creating a new connection and using FbDatabase.createDatabase().

[AlexPeshkoff]

We have IUtil::executeCreateDatabase which does >90% of described actions

[mrotteveel]

We have IUtil::executeCreateDatabase which does >90% of described actions

Jaybird is - by default - a pure Java driver, so I don't want to call functions from fbclient. It might be helpful for reference though.

[prrvchr]

Perhaps the best way to support database creation would be an option in the connection url as is the case for many JDBC drivers.
In any case, this makes it possible to create a new database when connecting and without code...

[mrotteveel]

@prrvchr Could you also add your opinion to this thread on firebird-java: https://groups.google.com/g/firebird-java/c/N1PHCM8TnPk ?

[mrotteveel]

See also https://github.com/FirebirdSQL/jaybird/blob/master/devdoc/jdp/jdp-2024-02-create-database-through-jdbc-url.adoc

[prrvchr]

@mrotteveel thank you for this work.
Can you tell me where to find a pre-release archive?

[mrotteveel]

@prrvchr If you use Maven or Gradle, make sure you have the Sonatype OSS snapshot repository (https://oss.sonatype.org/content/repositories/snapshots) in your configuration, and you can get it with the Maven coordinates org.firebirdsql.jdbc:jaybird:6.0.0-SNAPSHOT, and otherwise look at the most recent snapshot in https://oss.sonatype.org/content/repositories/snapshots/org/firebirdsql/jdbc/jaybird/6.0.0-SNAPSHOT/ (currently the latest is from May 12, 2024).

[prrvchr]

@mrotteveel I can't find a version 6.x for Java 11, if it exists it would save me from having to compile. Thanks.

[mrotteveel]

@prrvchr It doesn't exist. Jaybird 6 requires Java 17 or higher, as announced in the Jaybird 5 release notes: Dropping support for Java 8 and 11

[mrotteveel]

And trying to compile yourself will also fail, because it uses features of Java 17.

[prrvchr]

I just understood that I need java 17 mini, I'm going to install it... sorry for the inconvenience...

[prrvchr]

Well actually it works much better with Java 17.
On the other hand, I am not sure if it is possible to create the database when connecting in embedded mode or is it necessary to have additional java components?

[mrotteveel]

Since Jaybird 6, the native support is in the artifact jaybird-native (https://oss.sonatype.org/content/repositories/snapshots/org/firebirdsql/jdbc/jaybird-native/), which needs to be on the classpath in addition to jaybird.

But to be clear, these snapshots are only for early access/testing, they are not ready for "production" use.

[mrotteveel]

Also consider reading the release notes on master: https://github.com/FirebirdSQL/jaybird/blob/master/src/docs/asciidoc/release_notes.adoc

[prrvchr]

Thanks Mark, I allready put the native jar in the classpath, but I am facing a classpath problem with the loading of JNI services because the java part (the jar archive) of the jdbcDriverOOo extension is itself loaded from an URL classloader by LibreOffice itself. I'm afraid it's not that simple...

[prrvchr]

That's it, everything works perfectly, the creation of the database is carried out during the first connection if necessary.

On the other hand it is necessary to have in the classpath the jaybird-native-6.0.0 archive and also the jaybird-6.0.0 archive (the driver) otherwise I have a ClassNotFound error on the class org.firebirdsql.gds .impl.BaseGDSFactoryPlugin.

It's a shame because until now I had managed not to put the jdbc drivers in the classpath in order to be able to choose the version of the driver during the first load (ie: I can manage a JavaDriverClassPath connection property which allows me to fetch the driver wherever I want...). Besides, I don't understand why we lose classloader inheritance unless java services are used (ie: java.util.ServiceLoader) ?

Otherwise JaybirdOOo will soon be available, it will work with jaybird-6.0.0-20240512.161600-27 and I would stipulate that it is a pre version. Many thanks for making this possible...

[prrvchr]

After further investigation to better understand what is happening, I realize that Java services (java.util.ServiceLoader) are being used.

Classloader propagation is well implemented except for the call in FirebirdEmbeddedLookup.java.

Being just a beginner in Java, I can't determine if this is the source of the problem (ie: being forced to put the jdbc driver in the classpath) and I suppose that there is a good reason for let it be like this?

[mrotteveel]

That is only for loading the Firebird Embedded provider, which is an experimental feature to load the Firebird Embedded files from the classpath. Are you even using that?

That said, I think this really falls out of the scope of this ticket. Could you start a discussion on firebird-java instead?

[prrvchr]

Ok I just opened a new ticket to continue this conversation.