Support for Oracle collections about vertx-sql-client HOT 14 OPEN

Thanks for bringing this up @DavideD

It's not possible currently unfortunately

from vertx-sql-client.

@DavideD I've read about Oracle collections in https://docs.oracle.com/en/database/oracle/oracle-database/21/jjdbc/Oracle-collections.html#GUID-338038DB-AE3A-4C82-BB8F-8C87F2D130C4

It seems an OracleArray object needs to be created. How is this implemented with Hibernates ORM?

from vertx-sql-client.

The JDBC driver takes care of it.
You can check the OracleArrayJdbcType class in Hibernate ORM.

The class in the JDBC driver is called oracle.jdbc.OracleArray.
There's also a method in java.sql.Connection for the creation of arrays:

Array createArrayOf(String typeName, Object[] elements) throws
SQLException;

At first glance, I'd say the Vert.x client needs something similar.

from vertx-sql-client.

The JDBC driver takes care of it.
You can check the OracleArrayJdbcType class in Hibernate ORM.

Thanks for the pointer. I understand something must create an OracleArray instance, and from what I see Hibernate ORM does it in OracleArrayJdbcType. Shouldn't Hibernate Reactive do the same ?

from vertx-sql-client.

I'm not sure I understand, OracleArray is an interface in the JDBC driver. The Vert.x SQL client doesn't have a similar interface (or does it?).

We create an ArrayAdaptor that works for all the other databases, though

from vertx-sql-client.

Anyway, maybe you can show me an example that only use Vert.x and I can adapt it to Hibernate Reactive.

from vertx-sql-client.

I'm not sure I understand, OracleArray is an interface in the JDBC driver. The Vert.x SQL client doesn't have a similar interface (or does it?).

The Vert.x Reactive Oracle, unlike other Vert.x Reactive SQL clients, is based on the Oracle JDBC driver and its Reactive extensions (the Oracle DB protocol is not open so we can't create our own codec).

So when you execute a preparedQuery with the Vert.x API, the client creates a JDBC PreparedStatement and executes it with Oracle Reactive extensions.

So yes, I think in Hibernate Reactive you need to create an OracleArray instance like in Hibernate ORM and submit it in the Tuple

from vertx-sql-client.

The Vert.x Reactive Oracle, unlike other Vert.x Reactive SQL clients, is based on the Oracle JDBC driver

So it shouldn't be hard to delegate the creation of the array to the underlying driver by calling the createOracleArray method. Why should a user duplicate the logic that already exists in the driver? And how a user would be aware of this?
I don't think this is a Hibernate Reactive issue only.

Hibernate Reactive you need to create an OracleArray instance like in Hibernate ORM

Hibernate ORM doesn't create an instance of OracleArray, it's the JDBC driver that creates it. Hibernate ORM just receive an object of type java.sql.Array. The driver just so happen to know which one is the correct implementation to use.

You can see this by checking the signature of the method oracle.jdbc.OracleConnection#createOracleArray:

Array createOracleArray(String var1, Object var2) throws SQLException;

from vertx-sql-client.

I understand Hibernate ORM doesn't create an instance of OracleArray itself, but it asks the driver to do so in OracleArrayJdbcType with all the knowledge required (array type name).

This, is what I think should be done in Hibernate Reactive too. And then the Tuple submitted to the Vert.x Client may contain the the sql array instance.

from vertx-sql-client.

@tsegismont I added a simple Oracle string array test and a backing public OracleConnection.createArray(...) method which we can access in HR to support Oracle arrays. The test, however, is failing in vertx.

Can you take a look at it?

Note the exception is:

Caused by: java.sql.SQLException: ORA-17074: Invalid name pattern: StringArrayType
https://docs.oracle.com/error-help/db/ora-17074/
	at oracle.jdbc.oracore.OracleTypeADT.initMetadata12(OracleTypeADT.java:636)
	at oracle.jdbc.oracore.OracleTypeADT.initMetadata(OracleTypeADT.java:571)
	at oracle.jdbc.oracore.OracleTypeADT.init(OracleTypeADT.java:500)
	at oracle.sql.ArrayDescriptor.initPickler(ArrayDescriptor.java:1722)
	at oracle.sql.ArrayDescriptor.<init>(ArrayDescriptor.java:381)
	at oracle.sql.ArrayDescriptor.<init>(ArrayDescriptor.java:357)
	at oracle.sql.ArrayDescriptor.createDescriptor(ArrayDescriptor.java:217)
	at oracle.sql.ArrayDescriptor.createDescriptor(ArrayDescriptor.java:102)
	at oracle.jdbc.driver.PhysicalConnection.createARRAY(PhysicalConnection.java:7755)
	at io.vertx.oracleclient.impl.OracleJdbcConnection.createArray(OracleJdbcConnection.java:62)
	... 30 more

from vertx-sql-client.

@blafond are you creating the new type in the test? Didn't you have queries similar to the one I've included in this issue?

from vertx-sql-client.

@blafond are you creating the new type in the test? Didn't you have queries similar to the one I've included in this issue?

Nevermind, I see that that import.sql is included. Sorry

from vertx-sql-client.

@blafond sure, would you mind putting together a draft PR ?

from vertx-sql-client.

sure! I'll do that today. thx

from vertx-sql-client.