SQL Developer Extension Connection/Struct Issue
sggreener opened this issue · 5 comments
(Issue posted from Oracle forum at request of Jeff Smith).
I am developing an extension.
I am currently using SQL Developer 20.4 (and Eclipse).
Given a SQL Developer connection name, I am retrieving a java.sql.Connection as follows:
Connection conn = Connections.getInstance().getConnection(this.connectionName,true);
When I try to use this connection to create a Struct object I get the following error.
"Fail to convert to internal representation...".
(I am using the JGeometry.storeJS method from the Oracle Spatial sdoapi.jar)
If I replace the connection with the following:
Connection conn = (Connection)DriverManager.getConnection("jdbc:oracle:thin:@localhost:1522:GISDB12", "username", "password");
The creation of the Struct now works.
Obviously the latter solution is not sustainable: all connection information is with SQL Developer.
Can anyone identify what I am doing wrong and suggest a solution?
Simon
EDIT: Note that I can create a Struct (not STRUCT) from a SELECT query (that is 98% of what my code does).
What I can't do is create a struct for writing back to the database.
That is what JGeometry.storeJS is supposed to do.
If I try to Connection.createStruct() I also get the same Struct creation error that JGeometry.storeJS throws.
To get me over the hurdle I have hacked up a solution where I convert an JGeometry object to an SDO_GEOMETRY string representation.
I then construct a SQL Select statement using that string and execute it ie "SELECT sdo_geometry(....) FROM DUAL" .
In other words I have the Oracle database (not Java and JGeometry.storeJS) create the Struct which I then access.
It works as expected.
I then wrote my own method to convert a JGeometry object to a STRUCT and it works if I use the deprecated methods and a standalone connection but not if I use the Connection provided by SQL Developer.
I think the issue also applies to ROWID.
So, there is something about the standard SQL Developer Connection that is not handling specific oracle.sql structures like STRUCT (SDO_GEOMETRY) or even ROWID. Or, I am simply working with the standard Connection properly, but I have no idea what that is.
I created a java class called foo that has all the relevant bits that show the conversion of a JGeometry to a Struct (it doesn't have to be a JGeometry it is just that it avoids a lot of datum etc handling).
I then call the java class from a hook on the table node.
The foo code fails.
Here are the relevant bits.
My call from existing controller (replace with yours):
public boolean handleEvent(IdeAction action, Context context) {
try {
foo f = new foo(context);
} catch (Exception e) {
// Do nothing
}
}
A class that encapsulates the implementation problem:
package org.GeoRaptor;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Struct;
import oracle.dbtools.raptor.utils.DBObject;
import oracle.ide.Context;
import oracle.spatial.geometry.JGeometry;
public class foo {
public foo (Context context) {
DBObject dbo = new DBObject(context.getNode());
Connection conn = dbo.getDatabase().getConnection();
JGeometry jGeom = new JGeometry(0.0,0.0,100.0,100.0,0);
try {
System.out.println("About to convert JGeometry to Struct");
Struct stGeom = jGeom.storeJS(jGeom,conn);
System.out.println("Conversion of JGeometry to Struct succeeded");
} catch (SQLException e) {
System.out.println("Conversion of JGeometry to Struct failed ("+e.getMessage());
e.printStackTrace();
}
}
}
Errors throw by test class...
About to convert JGeometry to Struct
Conversion of JGeometry to Struct failed (Fail to convert to internal representation: oracle.jdbc.proxy.oracle$1jdbc$1proxy$1NullProxy$2java$1sql$1Array$$$Proxy@6acc9a38
java.sql.SQLException: Fail to convert to internal representation: oracle.jdbc.proxy.oracle$1jdbc$1proxy$1NullProxy$2java$1sql$1Array$$$Proxy@6acc9a38
at oracle.jdbc.oracore.OracleTypeNUMBER.cArrayToNUMBERArray(OracleTypeNUMBER.java:387)
at oracle.jdbc.oracore.OracleTypeNUMBER.toNUMBERArray(OracleTypeNUMBER.java:285)
at oracle.jdbc.oracore.OracleTypeNUMBER.toDatumArray(OracleTypeNUMBER.java:74)
at oracle.sql.ArrayDescriptor.toOracleArray(ArrayDescriptor.java:1217)
at oracle.jdbc.driver.OracleArray.<init>(OracleArray.java:143)
at oracle.sql.ARRAY.<init>(ARRAY.java:227)
at oracle.jdbc.oracore.OracleTypeCOLLECTION.toDatum(OracleTypeCOLLECTION.java:114)
at oracle.jdbc.oracore.OracleTypeUPT.toDatum(OracleTypeUPT.java:96)
at oracle.jdbc.oracore.OracleType.toDatumInternal(OracleType.java:241)
at oracle.sql.StructDescriptor.toOracleArray(StructDescriptor.java:713)
at oracle.sql.StructDescriptor.toArray(StructDescriptor.java:1193)
at oracle.jdbc.driver.OracleStruct.<init>(OracleStruct.java:154)
at oracle.sql.STRUCT.<init>(STRUCT.java:225)
at oracle.jdbc.driver.PhysicalConnection.createStruct(PhysicalConnection.java:6960)
at oracle.jdbc.proxy.oracle$1dbtools$1raptor$1proxy$1driver$1oracle$1RaptorProxyOJDBCConnection$2oracle$1jdbc$1internal$1OracleConnection$$$Proxy.createStruct(Unknown Source)
at oracle.spatial.geometry.JGeometry.storeJS(JGeometry.java:4031)
at oracle.spatial.geometry.JGeometry.storeJS(JGeometry.java:3879)
at oracle.spatial.geometry.JGeometry.storeJS(JGeometry.java:3660)
at org.GeoRaptor.foo.<init>(foo.java:19)
at org.GeoRaptor.TableContextMenuController.handleEvent(TableContextMenuController.java:69)
at oracle.ideimpl.controller.MetaClassController.handleEvent(MetaClassController.java:52)
Apologies, came here for something else and just noticed this. It is not anything I think I can personally help with but let me ask our data type handling guru to take a look.
(edit to add: Who is, of course, out next week so not sure when to expect a reply.)
I think you need to wrap the connection first as some JDBC datatypes are downcast. Off the top of my head - something like:
import oracle.dbtools.raptor.utils.JDBCProxyUtil;
Connection validConnection = JDBCProxyUtil.getInstance().unwrap(conn);
Closing due to lack of response. Please reopen if the issue persists.