Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » struggling to call a stored procedure with OBJECT parameter...
struggling to call a stored procedure with OBJECT parameter... [message #649070] Mon, 17 January 2011 13:57 Go to next message
Keiran Halcyon is currently offline Keiran Halcyon
Messages: 12
Registered: January 2011
Junior Member
I have this simple OBJECT on Oracle DB:

TYPE O_VDS_ATTRIBUTE AS OBJECT
(
   name  VARCHAR2(100),
   value VARCHAR2(4000)
)


And this test PROCEDURE:

CREATE OR REPLACE PROCEDURE TEST_PROC(attrib O_VDS_ATTRIBUTE) IS
BEGIN
  NULL;
END;


I've defined this Java mapping class:

public class Attribute {
    private String name;
    private String value;

    public Attribute() {
    }

    public Attribute(String name, String value) {
        this.name = name;
        this.value = value;
    }

    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }

    public String getValue() {
        return value;
    }
    public void setValue(String value) {
        this.value = value;
    }
}


I'm registering the O_VDS_ATTRIBUTE related descriptor on session:

        ServerSession session = ((JpaEntityManager)em.getDelegate()).getServerSession();

        ObjectRelationalDataTypeDescriptor descriptor
                = new ObjectRelationalDataTypeDescriptor();

        descriptor.setJavaClass(Attribute.class);
        descriptor.setTableName("VDS.O_VDS_ATTRIBUTE");
        descriptor.setStructureName("VDS.O_VDS_ATTRIBUTE");

        descriptor.addDirectMapping("name", "getName", "setName", "NAME");
        descriptor.addDirectMapping("value", "getValue", "setValue", "VALUE");

        descriptor.addPrimaryKeyFieldName("NAME");

        session.addDescriptor(descriptor);


Then I'm preparing and executing the PLSQLStoredProcedureCall:

        PLSQLrecord record = new PLSQLrecord();
        record.setCompatibleType("VDS.O_VDS_ATTRIBUTE");
        record.setTypeName("VDS.O_VDS_ATTRIBUTE");
        record.setJavaType(Attribute.class);
        record.addField("NAME", JDBCTypes.VARCHAR_TYPE, 100);
        record.addField("VALUE", JDBCTypes.VARCHAR_TYPE, 4000);

        Attribute attribute = new Attribute("nejm", "val");

        PLSQLStoredProcedureCall call = new PLSQLStoredProcedureCall();
        call.setProcedureName("VUM.TEST_PROC");
        call.addNamedArgument("attrib", record);

        DataModifyQuery query = new DataModifyQuery();
        query.addArgument("attrib", Struct.class);
        query.setCall(call);

        List<Object> queryArgs = new ArrayList<Object>();
        queryArgs.add(attribute);

        session.executeQuery(query, queryArgs);


The code actually succesfully sends some SQL to the DB but the compatibility type objects there are not initialized and it's execution throws ORA-06530:

DECLARE
  attrib_TARGET VDS.O_VDS_ATTRIBUTE;
  attrib_COMPAT VDS.O_VDS_ATTRIBUTE := :1;
  FUNCTION EL_SQL2PL_1(aSqlItem VDS.O_VDS_ATTRIBUTE) 
  RETURN VDS.O_VDS_ATTRIBUTE IS
    aPlsqlItem VDS.O_VDS_ATTRIBUTE;
  BEGIN
    aPlsqlItem.NAME := aSqlItem.NAME;
    aPlsqlItem.VALUE := aSqlItem.VALUE;
    RETURN aPlsqlItem;
  END EL_SQL2PL_1;
BEGIN
  attrib_TARGET := EL_SQL2PL_1(attrib_COMPAT);
  VUM.TEST_PROC(attrib=>attrib_TARGET);
END;


Also, I tried to make this work without the compatibleType but throwing the "record.setCompatibleType("VDS.O_VDS_ATTRIBUTE");" line out is provoking a totally different error (nothing gets to the database):

java.lang.NullPointerException
	at org.eclipse.persistence.internal.helper.DatabaseType$DatabaseTypeHelper.translate(DatabaseType.java:137)
	at org.eclipse.persistence.platform.database.oracle.plsql.PLSQLrecord.translate(PLSQLrecord.java:200)
	at org.eclipse.persistence.platform.database.oracle.plsql.PLSQLStoredProcedureCall.translate(PLSQLStoredProcedureCall.java:1012)
	at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:205)
	at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:192)
	at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeNoSelectCall(DatasourceCallQueryMechanism.java:235)
	at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeNoSelect(DatasourceCallQueryMechanism.java:215)
	at org.eclipse.persistence.queries.DataModifyQuery.executeDatabaseQuery(DataModifyQuery.java:85)
	at org.eclipse.persistence.queries.DatabaseQuery.execute(DatabaseQuery.java:675)
	at org.eclipse.persistence.internal.sessions.AbstractSession.internalExecuteQuery(AbstractSession.java:2322)
	at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1225)
	at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1207)
	at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1181)
	at com.visiona.jpa.eclipselink.OracleSpecificDbJPATest.callProcedureWithInAttributeObjectArgument(OracleSpecificDbJPATest.java:61)


Can somebody help me? I''ve spent so much time trying to get this to work! I searched everywhere and those features are so poorly documented. I saw some examples with RECORDs but those work a bit different than OBJECTs (no need for explicit initialization).
Re: struggling to call a stored procedure with OBJECT parameter... [message #649081 is a reply to message #649070] Mon, 17 January 2011 14:49 Go to previous messageGo to next message
James Sutherland is currently offline James Sutherland
Messages: 1939
Registered: July 2009
Location: Ottawa, Canada
Senior Member

The PLSQLStoredProcedureCall is only for procedures that use PLSQL record or table types. You seem to be using just a normal object type, so should be using a regular StoredProcedureCall.



James : Wiki : Book : Blog : Twitter
Re: struggling to call a stored procedure with OBJECT parameter... [message #649215 is a reply to message #649081] Tue, 18 January 2011 08:57 Go to previous messageGo to next message
Keiran Halcyon is currently offline Keiran Halcyon
Messages: 12
Registered: January 2011
Junior Member
Are there any examples of this? I've managed to accomplish this:

--BEGIN VUM.TEST_PROC(attrib=>?); END;
        bind => [null]


Using StoredProcedureCall and STRUCT:

call.addNamedArgument("attrib", "attrib", Types.STRUCT, "VDS.O_VDS_ATTRIBUTE", Attribute.class);


Let's just say it's not satisfactory. Thanks.
Re: struggling to call a stored procedure with OBJECT parameter... [message #649554 is a reply to message #649215] Wed, 19 January 2011 17:04 Go to previous messageGo to next message
Chris Delahunt is currently offline Chris Delahunt
Messages: 1021
Registered: July 2009
Senior Member
Hello,

Creating an ObjectRelationalDataTypeDescriptor is described here:
http://wiki.eclipse.org/Creating_an_Object-Relational_Data_T ype_Descriptor_(ELUG)
The difference between what it is doing and what you are attempting is that it is setting up the Employee to be written to a table, not through stored procedures- your does not have a "VDS.O_VDS_ATTRIBUTE" table, and so the setTableName should probably not be used. While it might not be related, you might also want to add your descriptor in a customizer rather than dynamically.

Simple example from the EclipseLink tests is:
Descriptor:
ObjectRelationalDataTypeDescriptor descriptor = new ObjectRelationalDataTypeDescriptor();
descriptor.setJavaClass(Address.class);
descriptor.descriptorIsAggregate();
descriptor.setStructureName("ADDRESS_TYPE");

descriptor.addDirectMapping("street", "getStreet", "setStreet", "STREET");
descriptor.addDirectMapping("city", "getCity", "setCity", "CITY");
descriptor.setShouldOrderMappings(false);


call, query and execution:
StoredProcedureCall spcall = new StoredProcedureCall();
spcall.setProcedureName("SProc_Insert");    
//address arg type is only needed should this query be used with a null value
spcall.addUnamedArgument("address", Types.STRUCT, "ADDRESS_TYPE");

DataModifyQuery query = new DataModifyQuery();
query.setShouldBindAllParameters(true);
query.bindAllParameters();
query.setCall(spcall);
query.addArgument("address");

List<Object> queryArgs = new ArrayList<Object>();
queryArgs.add(address);

session.executeQuery(query, queryArgs);


This example runs nightly. One difference other than the use of unnamed parameters is that the type is not specified on the query.addArgument call. It is only required for differentiating cached queries if the argument types are the same and should not be needed here - specifying STRUCT might be wrong, i just haven't stepped through this code to verify.

Best Regards,
Chris
Re: struggling to call a stored procedure with OBJECT parameter... [message #649750 is a reply to message #649554] Thu, 20 January 2011 14:23 Go to previous messageGo to next message
Keiran Halcyon is currently offline Keiran Halcyon
Messages: 12
Registered: January 2011
Junior Member
Thanks for trying!

I did everything like you wrote and still the only thing I get is:

BEGIN VUM.TEST_PROC(?); END;
        bind => [null]


Could you point me at the source code for tests and tell me which test is the source code coming from?

I'm slowly accepting that I'll have to resort to some other solution... Especially when this was supposed to be the first step only - then I'd have to bind lists of such objects.
Re: struggling to call a stored procedure with OBJECT parameter... [message #649774 is a reply to message #649215] Thu, 20 January 2011 15:00 Go to previous messageGo to next message
James Sutherland is currently offline James Sutherland
Messages: 1939
Registered: July 2009
Location: Ottawa, Canada
Senior Member

Include the complete code for your query. You must define the argument on the query and pass the argument value when executing it.


James : Wiki : Book : Blog : Twitter
Re: struggling to call a stored procedure with OBJECT parameter... [message #649960 is a reply to message #649774] Fri, 21 January 2011 08:48 Go to previous messageGo to next message
Keiran Halcyon is currently offline Keiran Halcyon
Messages: 12
Registered: January 2011
Junior Member
I did what Chris suggested so...

Now I'm adding the descriptor in customizer:

public class DBObjectsDescriptorCustomizer implements SessionCustomizer {

    public void customize(Session session) {
        Server server = (Server) session;
        ObjectRelationalDataTypeDescriptor descriptor = new ObjectRelationalDataTypeDescriptor();

        descriptor.setJavaClass(Attribute.class);
        descriptor.descriptorIsAggregate();
        descriptor.setStructureName("VDS.O_VDS_ATTRIBUTE");

        descriptor.addDirectMapping("name", "getName", "setName", "NAME");
        descriptor.addDirectMapping("value", "getValue", "setValue", "VALUE");
        descriptor.setShouldOrderMappings(false);

        server.addDescriptor(descriptor);
    }

}


The descriptor is of course registered in persistence.xml (eclipselink.session.customizer). And this is the code which should use the descriptor:

       session = ((JpaEntityManager)em.getDelegate()).getServerSession();

        Attribute attribute = new Attribute("nejm", "val");

        StoredProcedureCall call = new StoredProcedureCall();
        call.setProcedureName("VUM.TEST_PROC");
        call.addUnamedArgument("attrib", Types.STRUCT, "VDS.O_VDS_ATTRIBUTE");

        DataModifyQuery query = new DataModifyQuery();
        query.setShouldBindAllParameters(true);
        query.bindAllParameters();
        query.setCall(call);
        query.addArgument("attrib");

        List<Object> queryArgs = new ArrayList<Object>();
        queryArgs.add(attribute);

        session.executeQuery(query, queryArgs);


Just to be sure, this is the Attribute POJO:

public class Attribute {

    public String name;
    public String value;

    public Attribute() {
    }

    public Attribute(String name, String value) {
        this.name = name;
        this.value = value;
    }

    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }

    public String getValue() {
        return value;
    }
    public void setValue(String value) {
        this.value = value;
    }

    @Override
    public String toString() {
        return Objects.toStringHelper(this)
                      .add("name", name)
                      .add("value", value)
                      .toString();
    }

}


Effect:

BEGIN VUM.TEST_PROC(?); END;
        bind => [null]


If you'd like any other information, let me know.
Re: struggling to call a stored procedure with OBJECT parameter... [message #650380 is a reply to message #649960] Mon, 24 January 2011 17:07 Go to previous messageGo to next message
James Sutherland is currently offline James Sutherland
Messages: 1939
Registered: July 2009
Location: Ottawa, Canada
Senior Member

Your code looks correct, and I think it is actually working. I think the issue is with the logging.

I debugged our code and saw that we are binding the struct correctly, but are not printing the parameter value correctly in the log, we always print it as null for struct parameters.

Please verify that it worked, and please log a bug to have the logging fixed.



James : Wiki : Book : Blog : Twitter
Re: struggling to call a stored procedure with OBJECT parameter... [message #650482 is a reply to message #650380] Tue, 25 January 2011 08:39 Go to previous messageGo to next message
Keiran Halcyon is currently offline Keiran Halcyon
Messages: 12
Registered: January 2011
Junior Member
Thanks for digging so deep!

I've also played with the debugger a little and noticed that underneath the STRUCT is being built etc.

But did you try to run this with a real DB procedure? Because even if the EclipseLink's logging is incorrect, still on the DB's side I get (interestingly enough) not a NULL object but an existing object with all fields ('name' and 'value') NULLed...
Re: struggling to call a stored procedure with OBJECT parameter... [message #651067 is a reply to message #649070] Thu, 27 January 2011 16:20 Go to previous messageGo to next message
James Sutherland is currently offline James Sutherland
Messages: 1939
Registered: July 2009
Location: Ottawa, Canada
Senior Member

I re-ran our tests for these the Structs are being written and read correctly.

I think your issue is that you are not calling,

descriptor.addFieldOrdering("NAME");
descriptor.addFieldOrdering("VALUE");

in your descriptor, you need to do this.


James : Wiki : Book : Blog : Twitter
Re: struggling to call a stored procedure with OBJECT parameter... [message #651184 is a reply to message #651067] Fri, 28 January 2011 09:31 Go to previous message
Keiran Halcyon is currently offline Keiran Halcyon
Messages: 12
Registered: January 2011
Junior Member
Unfortunately fiddling with fieldOrderings and setShouldOrderMappings (I'm guessing those are related) isn't changing anything.

Let's ditch this subject - I gave up on this, it's not worth the effort. It's easier for me to provide CSV wrappers for procedures taking those objects than struggling to make this work in EclipseLink. If you ask me, this area needs some serious documenting effort...

Thanks for your help anyway!
Previous Topic:How to save data in different tables with one entity
Next Topic:Multiple Mapping-Files in PersistenceUnit
Goto Forum:
  


Current Time: Thu Sep 18 10:05:40 GMT 2014

Powered by FUDForum. Page generated in 0.20560 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software