Difference between revisions of "EclipseLink/Examples/JPA/nonJDBCArgsToStoredProcedures"

From Eclipsepedia

Jump to: navigation, search
(How to handle nonJDBC arguments for Oracle Stored Procedures)
(Mixing regular JDBC arguments with nonJDBC arguments)
Line 82: Line 82:
 
  <font color="#000000">'''<font color="#800000">procedure</font>''' two_arg_test<font color="#808030">(</font>x '''<font color="#800000">in</font>''' '''<font color="#800000">boolean</font>'''<font color="#808030">,</font> y '''<font color="#800000">in</font>''' '''<font color="#800000">varchar</font>'''<font color="#808030">)</font>
 
  <font color="#000000">'''<font color="#800000">procedure</font>''' two_arg_test<font color="#808030">(</font>x '''<font color="#800000">in</font>''' '''<font color="#800000">boolean</font>'''<font color="#808030">,</font> y '''<font color="#800000">in</font>''' '''<font color="#800000">varchar</font>'''<font color="#808030">)</font>
 
  </font>
 
  </font>
 +
 +
<font color="#000000">
 +
...
 +
        PLSQLStoredProcedureCall call = '''<font color="#7f0055">new</font>''' PLSQLStoredProcedureCall();
 +
        call.setProcedureName(<font color="#2a00ff">"niji"</font>);
 +
        call.addNamedArgument(<font color="#2a00ff">"X"</font>, PLSQLBoolean);
 +
        call.addNamedArgument(<font color="#2a00ff">"Y"</font>, JDBCTypes.VARCHAR_TYPE, 40);
 +
        DataModifyQuery query = '''<font color="#7f0055">new</font>''' DataModifyQuery();
 +
        query.addArgument(<font color="#2a00ff">"X"</font>);
 +
        query.addArgument(<font color="#2a00ff">"Y"</font>);
 +
        query.setCall(call);
 +
        '''<font color="#7f0055">Vector</font>''' queryArgs = '''<font color="#7f0055">new</font>''' NonSynchronizedVector();
 +
        queryArgs.add('''<font color="#7f0055">Integer</font>'''.valueOf(0));
 +
        queryArgs.add(<font color="#2a00ff">"test"</font>);
 +
        '''<font color="#7f0055">boolean</font>''' worked = '''<font color="#7f0055">false</font>'''<nowiki>;
 +
        </nowiki>'''<font color="#7f0055">String</font>''' msg = '''<font color="#7f0055">null</font>'''<nowiki>;
 +
        s.executeQuery(query, queryArgs);
 +
</nowiki></font>
  
 
<pre>
 
<pre>
[EPS Fine]: 2007.11.23 02:47:38.562--DatabaseSessionImpl(15055830)--Connection(23438274)--Thread(Thread[main,5,main])--
+
[EPS Fine]: 2007.11.23 02:54:46.109--DatabaseSessionImpl(15674464)--Connection(5807702)--Thread(Thread[main,5,main])--
 
DECLARE
 
DECLARE
 
   X_TARGET BOOLEAN := SYS.SQLJUTL.INT2BOOL(:1);
 
   X_TARGET BOOLEAN := SYS.SQLJUTL.INT2BOOL(:1);
   Y_TARGET VARCHAR(255) := :2;
+
   Y_TARGET VARCHAR(40) := :2;
 
BEGIN
 
BEGIN
   two_args_test(X=>X_TARGET, Y=>Y_TARGET);
+
   two_arg_test(X=>X_TARGET, Y=>Y_TARGET);
 
END;
 
END;
 +
bind => [:1 => 0, :2 => test]
 
</pre>
 
</pre>

Revision as of 15:57, 23 November 2007

{available as of EclipseLink 1.0M2}

How to handle nonJDBC arguments for Oracle Stored Procedures

The standard way of handling a Stored Procedure is to build an instance of org.eclipse.persistence.queries.StoredProcedureCall. However, the arguments must be compatible with the JDBC specification.

To handle nonJDBC arguments (e.g. BOOLEAN, PLS_INTEGER, PL/SQL record, etc.) a new sub-class has been created: org.eclipse.persistence.platform.database.oracle.PLSQLStoredProcedureCall:

For the target procedure:

procedure bool_in_test(x in boolean)

the EclipseLink code would be:

package test;

 // javase imports
import java.util.Vector;

// EclipseLink imports
import org.eclipse.persistence.internal.helper.NonSynchronizedVector;
import org.eclipse.persistence.logging.SessionLog;
import org.eclipse.persistence.platform.database.oracle.Oracle10Platform;
import org.eclipse.persistence.platform.database.oracle.PLSQLStoredProcedureCall;
import org.eclipse.persistence.queries.DataModifyQuery;
import org.eclipse.persistence.sessions.DatabaseLogin;
import org.eclipse.persistence.sessions.DatabaseSession;
import org.eclipse.persistence.sessions.Project;
import org.eclipse.persistence.sessions.Session;
import static org.eclipse.persistence.platform.database.oracle.OraclePLSQLTypes.PLSQLBoolean;

public class TestClass {

    public static String DATABASE_USERNAME = "scott";
     public static String DATABASE_PASSWORD = "tiger";
     public static String DATABASE_URL = "jdbc:oracle:thin:@localhost:1521:ORCL";
     public static String DATABASE_DRIVER = "oracle.jdbc.driver.OracleDriver";
     
     public static void main(String[] args) {

        Project project = new Project();
        DatabaseLogin login = new DatabaseLogin();
        login.setUserName(DATABASE_USERNAME);
        login.setPassword(DATABASE_PASSWORD);
        login.setConnectionString(DATABASE_URL);
        login.setDriverClassName(DATABASE_DRIVER);
        login.setDatasourcePlatform(new Oracle10Platform());
        project.setDatasourceLogin(login);
        Session s = project.createDatabaseSession();
        s.setLogLevel(SessionLog.FINE);
        ((DatabaseSession)s).login();

        PLSQLStoredProcedureCall call = new PLSQLStoredProcedureCall();
        call.setProcedureName("bool_in_test");
        call.addNamedArgument("X", PLSQLBoolean);
        DataModifyQuery query = new DataModifyQuery();
        query.addArgument("X");
        query.setCall(call);
        Vector queryArgs = new NonSynchronizedVector();
        queryArgs.add(Integer.valueOf(1));
        s.executeQuery(query, queryArgs);
    }
}

The following snippet of the EclipseLink log shows the target procedure being invoked via an anonymous PL/SQL block:

...
[EPS Info]: 2007.11.23 01:03:23.890--DatabaseSessionImpl(15674464)--Thread(Thread[main,5,main])-- login successful
[EPS Fine]: 2007.11.23 01:03:23.968--DatabaseSessionImpl(15674464)--Connection(5807702)--Thread(Thread[main,5,main])--
DECLARE
  X_TARGET BOOLEAN := SYS.SQLJUTL.INT2BOOL(:1);
BEGIN
  bool_in_test(X=>X_TARGET);
END;
	bind => [:1 => 1]

Mixing regular JDBC arguments with nonJDBC arguments

A Stored Procedure may have a mix of regular and nonJDBC arguments. EclipseLink supports scenarios where all the arguments are regular JDBC types; however, when at least one argument is a nonJDBC type, then the PLSQLStoredProcedureCall class must be used. Additionally, because the target procedure is being invoked via an anonymous PL/SQL block, some additional information may be required for the JDBC type (length, scale or precision):

procedure two_arg_test(x in boolean, y in varchar)


	...
        PLSQLStoredProcedureCall call = new PLSQLStoredProcedureCall();
        call.setProcedureName("niji");
        call.addNamedArgument("X", PLSQLBoolean);
        call.addNamedArgument("Y", JDBCTypes.VARCHAR_TYPE, 40);
        DataModifyQuery query = new DataModifyQuery();
        query.addArgument("X");
        query.addArgument("Y");
        query.setCall(call);
        Vector queryArgs = new NonSynchronizedVector();
        queryArgs.add(Integer.valueOf(0));
        queryArgs.add("test");
        boolean worked = false;
         String msg = null;
         s.executeQuery(query, queryArgs);
 
[EPS Fine]: 2007.11.23 02:54:46.109--DatabaseSessionImpl(15674464)--Connection(5807702)--Thread(Thread[main,5,main])--
DECLARE
  X_TARGET BOOLEAN := SYS.SQLJUTL.INT2BOOL(:1);
  Y_TARGET VARCHAR(40) := :2;
BEGIN
  two_arg_test(X=>X_TARGET, Y=>Y_TARGET);
END;
	bind => [:1 => 0, :2 => test]